jdbc—时间处理
- java.util.Date
- 子类：java.sql.Date 表示年月日
- 子类：java.sql.Time 表示时分秒
- 子类：java.sql.Timestamp 表示年月日时分秒
例子：
package com.yf.jdbc.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * 测试Date,Time,Timestamp * @author yangf * */ public class Demo07 { public static void main(String[] args) { Connection con = null; PreparedStatement ps1 = null; try { // 加载数据库驱动 Class.forName("com.mysql.jdbc.Driver"); // 获得connection对象 建立与数据库连接 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc", "root", "123456"); ps1 = con.prepareStatement("insert into t_user (username,pwd,regTime) values (?,?,?)"); ps1.setString(1, "yyf"); ps1.setInt(2, 22222); java.sql.Date date = new java.sql.Date(System.currentTimeMillis()); ps1.setDate(3, date); ps1.execute(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { if (ps1 != null) { try { ps1.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
package com.yf.jdbc.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; /** * 测试Date,Time,Timestamp * @author yangf * */ public class Demo07 { public static void main(String[] args) { Connection con = null; PreparedStatement ps1 = null; try { // 加载数据库驱动 Class.forName("com.mysql.jdbc.Driver"); // 获得connection对象 建立与数据库连接 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc", "root", "123456"); ps1 = con.prepareStatement("insert into t_user (username,pwd,regTime,lastLoginTime) values (?,?,?,?)"); ps1.setString(1, "yangf"); ps1.setInt(2, 123123); java.sql.Date date = new java.sql.Date(System.currentTimeMillis()); // 如果需要插入指定日期，可以使用Calendar，dateFormat类 Timestamp timeStamp = new Timestamp(System.currentTimeMillis()); ps1.setDate(3, date); ps1.setTimestamp(4, timeStamp); ps1.execute(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { if (ps1 != null) { try { ps1.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
- 插入随机日期的数据1000条
package com.yf.jdbc.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Timestamp; import java.util.Random; /** * 测试Date,Time,Timestamp * @author yangf * */ public class Demo07 { public static void main(String[] args) { Connection con = null; PreparedStatement ps1 = null; try { // 加载数据库驱动 Class.forName("com.mysql.jdbc.Driver"); // 获得connection对象 建立与数据库连接 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc", "root", "123456"); for (int i = 0; i < 1000; i++) { ps1 = con.prepareStatement("insert into t_user (username,pwd,regTime,lastLoginTime) values (?,?,?,?)"); ps1.setString(1, "yangf" + i); ps1.setInt(2, 66666); int rand = 100000000 + new Random().nextInt(1000000000); java.sql.Date date = new java.sql.Date(System.currentTimeMillis() - rand); // 如果需要插入指定日期，可以使用Calendar，dateFormat类 Timestamp timeStamp = new Timestamp(System.currentTimeMillis() - rand); ps1.setDate(3, date); ps1.setTimestamp(4, timeStamp); ps1.execute(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { if (ps1 != null) { try { ps1.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
- 取出两个任意时间中间所有的数据
package com.yf.jdbc.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; /** * 测试时间处理，取出时间段的数据 * @author yangf * */ public class Demo08 { /** * 将字符串代表的时间转化为long类型(格式：yyyy-MM-dd hh:mm:ss) * @param strDate 字符串时间 * @return */ public static long stringToDate(String strDate) { DateFormat dt = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); try { return dt.parse(strDate).getTime(); } catch (ParseException e) { e.printStackTrace(); return 0; } } public static void main(String[] args) { Connection con = null; PreparedStatement ps1 = null; ResultSet rs = null; try { // 加载数据库驱动 Class.forName("com.mysql.jdbc.Driver"); // 获得connection对象 建立与数据库连接 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc", "root", "123456"); ps1 = con.prepareStatement("select * from t_user where regTime > ? and regTime < ?"); java.sql.Date startDate = new java.sql.Date(stringToDate("2017-05-05 02:30:16")); java.sql.Date endDate = new java.sql.Date(stringToDate("2017-05-07 16:23:02")); ps1.setDate(1, startDate); ps1.setDate(2, endDate); rs = ps1.executeQuery(); while (rs.next()) { System.out.println(rs.getInt(1) + "--" + rs.getString(2) + "--" + rs.getInt(3) + "--" + rs.getDate(4) + "--" + rs.getTimestamp(5)); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (ps1 != null) { try { ps1.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
