1.mysql数据库中有这样一个表:
mysql> select * from test_table;
+----------+---------------------+
| username | date |
+----------+---------------------+
| chengyu | 1990-10-04 00:00:00 |
| chengpei | 1980-09-12 12:23:01 |
+----------+---------------------+
其中date字段是datetime类型的;从数据库中将date字段取出来:
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?user=root&password=root");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from test_table");
while(rs.next()){
Date date = rs.getDate("date");
System.out.println(date);
}
}
Date取出来是java.sql.Date类型的;打印但是Date的toString()方法;显示如下:
1990-10-04
1980-09-12
现在将date取出来,转化为字符串,再次打印出来:
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?user=root&password=root");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from test_table");
while(rs.next()){
Date date = rs.getDate("date");
String date2 = new SimpleDateFormat("yyyy年MM月dd日").format(date);
System.out.println(date2);
}
}
打印结果如下:
1990年10月04日
1980年09月12日
2.从数据库中取出日期和时间:
在数据库中有这样的表:
public static void main(String[] args) throwsException{
Class.forName("com.mysql.jdbc.Driver");
Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?user=root&password=root");
Statement stmt=conn.createStatement();
ResultSet rs= stmt.executeQuery("select * from test_table");while(rs.next()){/** java.sql.Date只能精确到年月日; Date类型只能够代表日期;
* 这里采用java.sql.Timestamp;
* 要取出毫秒来,创建数据库字段date timestamp(3) 意思是保留3为毫秒数*/Timestamp date= rs.getTimestamp("date");
Calendar calendar=Calendar.getInstance();
calendar.setTime(date);//拿到年份:
int year =calendar.get(Calendar.YEAR);//拿到月份:
int month = calendar.get(Calendar.MONTH) + 1;//拿到日:
int day =calendar.get(Calendar.DAY_OF_MONTH);//拿到时:
int hour_24 =calendar.get(Calendar.HOUR_OF_DAY);int hour_12 =calendar.get(Calendar.HOUR);//拿到分:
int minute =calendar.get(Calendar.MINUTE);//拿到秒:
int second =calendar.get(Calendar.SECOND);//拿到毫秒:
int millisecond =calendar.get(Calendar.MILLISECOND);
System.out.println("日期和时间:"+date);
System.out.println("年: " +year);
System.out.println("月: " +month);
System.out.println("日: " +day);
System.out.println("时(24): " +hour_24);
System.out.println("时(12): " +hour_12);
System.out.println("分: " +minute);
System.out.println("秒: " +second);
System.out.println("毫秒: " +millisecond);
}
}
打印如下:
日期和时间:1990-10-04 23:30:55.86
年: 1990
月: 10
日: 4
时(24): 23
时(12): 11
分: 30
秒: 55
毫秒: 860
2)resultSet.getTimestamp("date")后,将这个时间转化为String:
数据库中已存在的数据:
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?user=root&password=root");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select date from test_table");
while(rs.next()){
Timestamp timestamp = rs.getTimestamp("date");
String strdate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(timestamp);
System.out.println(strdate);
}
}
打印出来:
1990-10-04 00:00:00.000
1989-10-04 23:59:55.086
3)将一个String类型的字符串存进数据库, 以java.sql.Date类型存进去:
public static void main(String[] args) throwsException{
Class.forName("com.mysql.jdbc.Driver");
Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?user=root&password=root");
String sql= "insert into test_table values(?, ?)";
PreparedStatement pstmt=conn.prepareStatement(sql);/** 将一个String类型的时间存储到数据库
* java.sql.Date和java.util.Date之间可以互相转化
* new SimpleDateFormat("yyyy-MM-dd hh:mm:ss") HH-24小时 hh-12小时
* 不过插入数据库后,由于是sql Date类型的,只能存储日期,就没有时间*/String strDate= "1990-10-04 23:30:55.86";
java.util.Date date= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").parse(strDate);
java.sql.Date date2= newjava.sql.Date(date.getTime());
pstmt.setString(1, "cy");
pstmt.setDate(2, date2);
pstmt.executeUpdate();
}
数据库显示:
4)将String类型的。连带时间分钟。毫秒一起存进数据库:
public static void main(String[] args) throwsException{
Class.forName("com.mysql.jdbc.Driver");
Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?user=root&password=root");
String sql= "insert into test_table values(?, ?)";
PreparedStatement pstmt=conn.prepareStatement(sql);/** java.sql.Timestamp/java.sql.Date都是 java.util.Date的子类;*/String strDate= "1989-10-04 23:59:55.86";
java.util.Date date= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").parse(strDate);
Timestamp date2= newTimestamp(date.getTime());
pstmt.setString(1, "cp");
pstmt.setTimestamp(2, date2);
pstmt.executeUpdate();
}