通过使用jdbc的PrepareStatement为DATETIME字段插入null [英] insert null for DATETIME Field through PrepareStatement using jdbc
问题描述
我想通过使用JDBC(使用Mysql数据库)通过PrepareStatement插入空值到DATETIME字段我试过以下方式,但没有什么工作。
i want to insert null value into an DATETIME Field through PrepareStatement using JDBC (using Mysql Database) i have tried following ways but nothing is worked.
我有一个方法Util.dateconvertdate它用于将输入日期转换为格式化的字符串类型
i have a method " Util.dateconvertdate " it use to convert input date into a formatted string type
public static String dateconvertdate(Timestamp timestamp) {
Date date=null;
String formattedDate="";
if(timestamp != null){
SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy hh:mm:ss");
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date date1 = new java.util.Date(timestamp.getTime());
formattedDate = formatter.format(date1);
}
else return "NULL";
return formattedDate;
}
我用来检查它是否返回null或date
and i use to check whether it return null or date
if(Util.dateconvertdate(rs.getTimestamp("fld_dob")) !=null){
pmystmt.setString(17, (Util.dateconvertdate(rs.getTimestamp("fld_dob"))));
} else{
pmystmt.setNull(17, java.sql.Types.NULL);
}
我尝试使用
pmystmt.setNull(17, java.sql.Types.DATE);
pmystmt.setNull(17, Types.NULL);
pmystmt.setInt(17, Types.NULL);
pmystmt.setString(17, "NULL");
pmystmt.setNull(17, Types.DATE);
pmystmt.setString(17, null);
pmystmt.setDate(17, null);
但没有什么正在工作,它会抛出异常
but nothing is working it is throwing exception like
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'NULL' for column 'dob' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2936)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1601)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1710)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2436)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1402)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1694)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1608)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1593)
at com.members.App_users.AddRecords(App_users.java:66)
at com.main.Start.main(Start.java:47)
任何帮助将不胜感激。感谢
any help will be appreciated. Thanks
推荐答案
在您的数据库中url include zeroDateTimeBehavior = convertToNull
。
In your database url include zeroDateTimeBehavior=convertToNull
while setting datasource.
jdbc:mysql://ip_address:port/databasename?zeroDateTimeBehavior=convertToNull
这篇关于通过使用jdbc的PrepareStatement为DATETIME字段插入null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!