问题：
在Spring Boot中使用JdbcTemplate
程旭尧
我使用Spring初始化器、嵌入式Tomcat、Thymeleaf模板引擎和作为可执行JAR文件的包生成了一个Spring Boot web应用程序。
使用的技术：
public class DeviceEvent {
public DeviceEvent(Device device) {
this.device = device;
}
private Device device;
private Long id;
private Double latitude;
private Double longitude;
private Date dateReceived;
private String message;
private Float rssi;
private Integer battery;
private Boolean alarm;
private Boolean processed;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Device getDevice() {
return device;
}
public void setDevice(Device device) {
this.device = device;
}
public Double getLatitude() {
return latitude;
}
public void setLatitude(Double latitude) {
this.latitude = latitude;
}
public Double getLongitude() {
return longitude;
}
public void setLongitude(Double longitude) {
this.longitude = longitude;
}
public Date getDateReceived() {
return dateReceived;
}
public void setDateReceived(Date dateReceived) {
this.dateReceived = dateReceived;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public Float getRssi() {
return rssi;
}
public void setRssi(Float rssi) {
this.rssi = rssi;
}
public Integer getBattery() {
return battery;
}
public void setBattery(Integer battery) {
this.battery = battery;
}
public Boolean getAlarm() {
return alarm;
}
public void setAlarm(Boolean alarm) {
this.alarm = alarm;
}
public Boolean getProcessed() {
return processed;
}
public void setProcessed(Boolean processed) {
this.processed = processed;
}
}
but I got this error executing this test:
Device device = new Device("C380F");
DeviceEvent deviceEvent = new DeviceEvent(device);
deviceEvent.setId(Sequencer.getNextVal());
deviceEvent.setAlarm(Boolean.FALSE);
deviceEvent.setBattery(78);
deviceEvent.setDateReceived(new Date());
deviceEvent.setLatitude(50.834015);
deviceEvent.setLongitude(4.377885);
deviceEvent.setMessage("FAKE_MSG");
deviceEvent.setProcessed(Boolean.FALSE);
deviceEvent.setRssi((float)80);
repository.insert(deviceEvent);
...
@Override
public long insert(DeviceEvent newDeviceEvent) {
long id = Sequencer.getNextVal();
int numOfRowsAffected = jdbcTemplate.update(
"insert into T_DEVICE_EVENT (ID, DEVICE_ID, LATITUDE, LONGITUDE, MESSAGE, DATE_RECEIVED, RSSI, BATTERY, ALARM, PROCESSED) " +
" values (?,?,?,?,?,?,?,?,?,?);",
id,
newDeviceEvent.getDevice().getId(),
newDeviceEvent.getLatitude(),
newDeviceEvent.getLongitude(),
newDeviceEvent.getMessage(),
newDeviceEvent.getRssi(),
newDeviceEvent.getBattery(),
newDeviceEvent.getAlarm(),
0);
if (numOfRowsAffected==1) return id;
else return -1;
}
CREATE TABLE IF NOT EXISTS t_device_event (
id bigint PRIMARY KEY,
device_id bigint NOT NULL,
latitude decimal NULL,
longitude decimal NULL,
message varchar(100) ,
date_received timestamp,
rssi float,
battery int,
alarm boolean,
processed boolean,
FOREIGN KEY (device_id) REFERENCES public.t_device(id));
错误：
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into T_DEVICE_EVENT (ID, DEVICE_ID, LATITUDE, LONGITUDE, MESSAGE, DATE_RECEIVED, RSSI, BATTERY, ALARM, PROCESSED) values (?,?,?,?,?,?,?,?,?,?);]; nested exception is java.sql.SQLSyntaxErrorException: incompatible data type in conversion
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:941)
at com.ideefecloud.iot.repository.JdbcDeviceEventRepository.insert(JdbcDeviceEventRepository.java:44)
at com.ideefecloud.iot.repository.JdbcDeviceEventRepositoryTests.testInsert(JdbcDeviceEventRepositoryTests.java:54)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setObject(Unknown Source)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:454)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:238)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:169)
at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:66)
at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:47)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:875)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
... 34 more
Caused by: org.hsqldb.HsqlException: incompatible data type in conversion
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.types.DateTimeType.convertJavaToSQL(Unknown Source)
... 44 more
共有1个答案
韩嘉祯
我认为您的列/参数未对齐。date_received似乎没有相应的参数，所以它试图将float(RSSI)转换为timestamp
类似资料：
-
使用的技术： Spring Boot 1.4.2.Release，Spring 4.3.4.Release，Tymeleaf 2.1.5.Release，Tomcat Embeded 8.5.6、Maven 3、Java 8 我创建了这个服务来发送电子邮件
-
我想把@AutoWired注释变成一个“方面”。我想在我的方面中注入一个存储库，但是当我试图调用autowired类的方法时，出现了NullPointException。 我已经尝试在aspect类上添加，但我出现了同样的错误。 如果我不使用aspect类，而是使用，我可以毫无问题地调用存储库。 一些文档谈到了spring的xml文件配置，但在spring boot，我没有这些文件。 这里是pom
-
我在GET api中有多个查询参数（如姓名、年龄、性别、位置等…n个数字）。现在我需要使用这些查询值来查询我的mongo数据库。现在用户可以发送从0到n的查询参数。 我正在尝试使用类似的东西 或者 但问题是，考虑到用户可以发送的所有排列和组合，我将不得不编写多个查询。有没有更好的方法来做到这一点？
-
目前，我正在尝试将JWT身份验证集成到现有的Spring Boot Webflux项目中。作为模板，我使用了这篇媒体文章：https://medium.com/@ard333/authentication-and-authorization-using-jwt-on-spring-webflux-29b81f813e78。如果我将注释@EnableWebFluxSecurity放在我的WebSec
-
我是kubernetes的新手，需要在openshift平台上使用k8s confimap将springboot应用程序的属性文件外部化。我已将属性文件保存在git repo中，作为“greeter.message=Spring Bootmyapplication.properties已在库伯内特斯上挂载为卷！”并使用“oc create confimap myconfig--from-file=
-
我正在使用与类似 我有在我的主应用程序类上 但是，不起作用。如果我的属性是false，并且我注释掉yaml文件中的CloudBucket对象，它在启动时失败，因为它不能绑定云桶属性。如果属性是false，那么该对象不应该是必需的，然后bean应该是null。我如何使这个工作？
-
我有Kafka Streams java应用程序启动并运行。我试图使用KSQL创建简单的查询，并使用Kafka流来实现复杂的解决方案。我希望将KSQL和Kafka流作为Java应用程序运行。 我打算通过https://github.com/confluentinc/ksql/blob/master/ksqldb-examples/src/main/java/io/confluent/ksql/em