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();
                }
            }
        }
    }
}