开发者社区> 技术小阿哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

JDBC操作MySQL5日期类型字段的问题解决方法

简介:
+关注继续查看
由于日期数据的特殊性和多样性,以及不同的数据库、编程语言对日期的定义和处理方式差别,导致了日期处理的复杂性,和多样性。
 
流行的Hibernate、iBatis等持久化框架从中解决了各种Java日期与数据库日期之间操作的细节问题,JDBC操作数据库日期的问题已经被淡化了。但是对于纯JDBC开发者来说,这些问题不可小觑。
 
下面以MySQL5和Java5为基础,说明Java处理数据库日期的一些问题及解决方案。
 
认识MySQL的日期类型:
列类型
”值
DATETIME
'0000-00-00 00:00:00'
DATE
'0000-00-00'
TIMESTAMP
00000000000000
TIME
'00:00:00'
YEAR
0000
从零值类型,也可以清晰看出日期的格式,再次,并不想讨论所有的数据类型,二是针对前三种常用类型做以分析。
 
MySQL日期类型的一些说明:
当你需要同时包含日期和时间信息的值时则使用DATETIME类型。MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。 
当你只需要日期值而不需要时间部分时应使用DATE类型。MySQL用'YYYY-MM-DD'格式检索和显示DATE值。 
TIMESTAMP列类型的属性不固定,取决于MySQL版本和服务器运行的SQL模式。 
TIMESTAMP列的显示格式与DATETIME列相同。换句话说,显示宽度固定在19字符,并且格式为YYYY-MM-DD HH:MM:SS。 

在一定程度上,可以将一个日期类型的值分配给一个不同的日期类型。但是,值可能会更改或丢失一些信息: 

·                 如果你为一个DATETIME或TIMESTAMP对象分配一个DATE值,结果值的时间部分被设置为'00:00:00',因为DATE值未包含时间信息。 

·                 如果你为一个DATE对象分配一个DATETIME或TIMESTAMP值,结果值的时间部分被删除,因为DATE值未包含时间信息。 

·                 记住尽管可以使用相同的格式指定DATETIME、DATE和TIMESTAMP值,不同类型的值的范围却不同。例如,TIMESTAMP值不能早于1970或晚于2037。这说明一个日期,例如'1968-01-01',虽然对于DATETIME或DATE值是有效的,但对于TIMESTAMP值却无效,如果分配给这样一个对象将被转换为0。 

当指定日期值时请注意某些缺陷: 

·                 指定为字符串的值允许的非严格格式可能会欺骗。例如,值'10:11:12'由于‘:’间割符看上去可能象时间值,但如果用于日期上下文值则被解释为年'2010-11-12'。值'10:45:15'被转换为'0000-00-00'因为'45'不是合法月。 

·                 在非严格模式,MySQL服务器只对日期的合法性进行基本检查:年、月和日的范围分别是1000到9999、00到12和00到31。任何包含超出这些范围的部分的日期被转换成'0000-00-00'。请注意仍然允许你保存非法日期,例如'2002-04-31'。要想确保不使用严格模式时日期有效,应检查应用程序。
 
 
问题:
对于JDBC写入MySQL数据库类型发生时分秒丢失的问题。
从数据库读出日期,时分秒丢失问题。
增删改查日期类型字段发生传参等错误的问题。
 
对于以上的问题,是非常普遍的。这里从数据库日期类型的精度需求入手,以MySQL5为蓝本,以JDBC为访问手段,研究日期的操作。
 
环境:
Java5
mysql-noinstall-5.1.40-win32.zip
mysql-connector-java-5.1.10.zip
 
一、要求最高精度的日期数据,timestamp类型,这种日期类型带最高精度,但与数据库设置有关系,一般默认即可。
 
 
建表SQL
create database    

use testdb; 

drop table if exists testdate; 

create table testdate ( 
    id bigint(20) not null auto_increment, 
    code varchar(20) default null
    crdate timestamp not null default current_timestamp
    primary key (id) 
) engine=myisam auto_increment=122 default charset=latin1;
 
测试结论:
  ----------插入----------------------------------- 
  //对于插入,静态SQL,字符串型的日期格式是正确的 
  String sql_1 = "insert into testdate(code,crdate) values ('x',CURRENT_TIMESTAMP)";    
  String sql_2 = "insert into testdate(code,crdate) values ('y','2009-11-09 13:00:23')";    

  //对于插入,预定义SQL,java.sql.Timestamp型的日期格式参数是正确的 
  String sql = "insert into testdate(code,crdate) values ('z',?)";    
  PreparedStatement pstmt = conn.prepareStatement(sql);    
  pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));    

  //对于插入,预定义SQL,java.lang.String型的日期格式参数是正确的 
  String sql = "insert into testdate(code,crdate) values ('w',?)";    
  PreparedStatement pstmt = conn.prepareStatement(sql);    
  pstmt.setString(1, "2009-11-09 13:00:23");    

  ----------更新----------------------------------- 
  //对于更新,静态SQL,字符串型的日期格式是正确的 
  String sql = "update testdate set crdate = '2009-12-12 13:33:15' where code = 'z'";    

  //对于更新,预定义SQL,java.lang.String型的日期格式参数是正确的 
  String sql = "update testdate set crdate = ? where code = 'z'";    
  PreparedStatement pstmt = conn.prepareStatement(sql);    
  pstmt.setString(1, "2009-12-12 13:33:15");    

  ----------查询----------------------------------- 

  //对于查询,静态SQL(两种写法),java.lang.String型的日期格式参数是正确的 
  String sql = "select * from testdate where crdate > '2009-11-09 13:00:23' and crdate < '2009-11-10 13:00:23'";    
  String sql = "select * from testdate where date_format(crdate, '%Y-%c-%e %T') between '1900-2-1 00:00:00' and '2010-2-5 00:00:00'"
  Statement stmt = conn.createStatement();    
  ResultSet rs = stmt.executeQuery(sql);    
  while (rs.next()) {    
    System.out.println(">>> id=" + rs.getLong("id") + "; code=" + rs.getString("code") + "; crdate=" + rs.getTimestamp("crdate"));    
  }    


  //但对于查询,预定义SQL,java的字符串和Timestamp类型参数都是错误的,不能执行,会提示语法错误。 
  //但别的数据库就另当别论了。 
  pstmt.setString(1,"2009-11-09 13:00:23"); 
  pstmt.setString(2,"2009-11-10 13:00:23"); 
  pstmt.setTimestamp(1,new Timestamp(sf.parse("2009-11-09 13:00:23").getTime())); 
  pstmt.setTimestamp(2,new Timestamp(sf.parse("2009-11-10 13:00:23").getTime()));
 
可以看出,字符串类型还是支持比较好。在SQL和Java语言之间都能良好的支持。
 
二、要求高精度的日期数据,选择datetime类型,这种日期格式带时分秒,用的较多。
 
对于上面表的crdate类型改下即可。
测试结论是:
  ----------插入----------------------------------- 
  //对于插入,静态SQL,字符串型的日期格式是正确的 
  String sql_1 = "insert into testdate(code,crdate) values ('x',CURRENT_TIMESTAMP)";    
  String sql_2 = "insert into testdate(code,crdate) values ('y','2009-11-09 13:00:23')";    

  //对于插入,预定义SQL,java.sql.Timestamp型的日期格式参数是正确的 
  String sql = "insert into testdate(code,crdate) values ('z',?)";    
  PreparedStatement pstmt = conn.prepareStatement(sql);    
  pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));    

  //对于插入,预定义SQL,java.lang.String型的日期格式参数是正确的 
  String sql = "insert into testdate(code,crdate) values ('w',?)";    
  PreparedStatement pstmt = conn.prepareStatement(sql);    
  pstmt.setString(1, "2009-11-09 13:00:23");    

  ----------更新----------------------------------- 
  //对于更新,静态SQL,字符串型的日期格式是正确的 
  String sql = "update testdate set crdate = '2009-12-12 13:33:15' where code = 'z'";    

  ----------查询----------------------------------- 

  //对于查询,静态SQL(两种写法),java.lang.String型的日期格式参数是正确的 
  String sql = "select * from testdate where crdate > '2009-11-09 13:00:23' and crdate < '2009-11-10 13:00:23'";    
  String sql = "select * from testdate where date_format(crdate, '%Y-%c-%e %T') between '1900-2-1 00:00:00' and '2010-2-5 00:00:00'"
  Statement stmt = conn.createStatement();    
  ResultSet rs = stmt.executeQuery(sql);    
  while (rs.next()) {    
    System.out.println(">>> id=" + rs.getLong("id") + "; code=" + rs.getString("code") + "; crdate=" + rs.getTimestamp("crdate"));    
  }    
 
三、对于date类型,因为不带时分秒,用的较少。
 
四、JDBC操作timestamp和datetime时候应该注意的问题
 
1、JDBC读取时候,应该选择getTimestamp()方法来读取,这样才能保持数据精度。
如果选择了getDate读取,则自动丢弃时分秒,造成精度下降。
 
2、JDBC读取的数据保存为java类型时候,应该定义为java.util.Date,这样可以保持原有的精度,如果设置成java.sql.Date,则造成时分秒丢失。
 
3、java.sql.Date是Java历史的垃圾,尽量避免使用。
 
4、在执行预定义SQL的时候,java.lang.String参数似乎是更好的方式。
 
本文也许是此同类问题的一个开始话题,仅仅演示了MySQL中的高精度日期类型操作一些正确方法,并非全部,但这些结论仅仅局限于MySQL5.


本文转自 leizhimin 51CTO博客,原文链接:http://blog.51cto.com/lavasoft/224370,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL数据库基础:数据类型详解-文本字符串类型
MySQL数据库基础:数据类型详解-文本字符串类型
0 0
MySQL数据库(9):数据类型-时间日期类型
MySQL数据库(9):数据类型-时间日期类型
0 0
MySQL数据库(10):数据类型-字符串类型
MySQL数据库(10):数据类型-字符串类型
0 0
MySQL 日期时间类型怎么选?千万不要乱用!
构建数据库写程序避免不了使用日期和时间,对于数据库来说,有多种日期时间字段可供选择,如 timestamp 和 datetime 以及使用 int 来存储 unix timestamp。
0 0
正确使用MySQL类型字段
字段类型 MySQL的字段类型大致可以分为四种:1、数值类型 2、字符串类型 3、时间类型 4、复合类型 数值类型 MySQL的数值数据类型可以大致划分为两个类别,一个是整数,另一个是浮点数或小数。 1bit 位 1字节=8bit 1k=1024字节 1M=1024k 类型 大小 范围...
4826 0
MySQL数据类型及字段属性
MySQL 数据类型MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型,大致可以分为三类: 数值 日期/时间 字符串(字符) 数值类型 MySQL支持所有标准SQL数值数据类型。 这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE
1598 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
PostgresChina2018_张启程_为什么我们抛弃MongoDB和MySQL,选择PgSQL
立即下载
MySQL表和索引优化实战
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载