JDBC1-4时间处理

测试时间处理(java.sql.Date,Time,Timestamp)

/**
 * 测试时间处理(java.sql.Date,Time,Timestamp)
 */

public class Demo07 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        String URL = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        try {

            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(URL, user, password);
            for (int i = 0; i <1000 ; i++) {

                ps=conn.prepareStatement("insert into t_user (username,pwd,regTime,lastLoginTime) values (?,?,?,?)");
                int rand=100000000+new Random().nextInt(100000000);
                Date date=new Date(System.currentTimeMillis()-rand);
                Timestamp timestamp=new Timestamp(System.currentTimeMillis()-rand);
                ps.setObject(1,"小明"+i);
                ps.setObject(2,"1234565");
                ps.setObject(3,date);
                ps.setObject(4,timestamp);
                ps.execute();
            }



        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            try {
                if (ps!=null){
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn!=null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

取出指定时间段的数据

public class Demo08 {
    /**
     * 将日期转为LONG数字(yyyy-mm-dd hh:mm:ss)
     */
    public static long str2Date(String dateStr) {
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            return format.parse(dateStr).getTime();
        } catch (ParseException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs;
        String URL = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";


        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL, user, password);
            String sql = "SELECT * FROM t_user where regTime>? and regTime<?";
            String sql2 = "SELECT * FROM t_user where lastLoginTime>? and lastLoginTime<?";
            ps = connection.prepareStatement(sql2);
            long data1=str2Date("2020-6-22 10:22:45");
            System.out.println("LONG"+data1);
            Date start = new Date(str2Date("2020-6-22 00:00:01"));
            Date end = new Date(str2Date("2020-6-23 22:22:45"));
            ps.setObject(1, start);
            ps.setObject(2, end);
            rs = ps.executeQuery();
            while (rs.next()) {
               // System.out.println(rs.getInt("id")+"--"+rs.getString("username")+"--"+rs.getDate("regTime"));
                System.out.println(rs.getInt("id")+"--"+rs.getString("username")+"--"+rs.getTimestamp("lastLoginTime"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}