数据库与日期

1. 在mysql中建立数据库expense
① 对于date类型

可以直接使用sql语句插入数据：
insert into expense(number,date) values('123',CURRENT_TIMESTAMP());

使用JDBC代码：
public class Expense {
private int id;// 自增长
private String number;// 工号
private Date date;// 票据时间
...此处省略set和get方法...
}
Expense expense = new Expense();
expense.setNumber("123");
expense.setDate(new java.sql.Date(System.currentTimeMillis()));
expenseDAO.addExpense(expense);

② 对于datetime类型

也可以直接使用sql语句插入数据：
insert into expense(number,date) values('123',CURRENT_TIMESTAMP());
使用JDBC时：
public void addExpense(Expense expense) {
//1.获得连接对象
Connection conn = DataBaseManager.getConnection();
//2.SQL语句
String addSql = "insert into expense(number,date) values (?,CURRENT_TIMESTAMP());";
//3.通过连接对象来创建一个预处理语句对象
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(addSql);
//4.设置参数
pstmt.setString(1, expense.getNumber());
//5.执行插入
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally{
DataBaseManager.close(pstmt);
DataBaseManager.close(conn);
}
}
或者：
public void addExpense(Expense expense) {
//1.获得连接对象
Connection conn = DataBaseManager.getConnection();
//2.SQL语句
String addSql = "insert into expense(number,date)" +
" values(?,?);";
//3.通过连接对象来创建一个预处理语句对象
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(addSql);
//4.设置参数
pstmt.setString(1, expense.getNumber());
java.util.Date date=new java.util.Date();
Timestamp ts=new Timestamp(date.getTime());
pstmt.setTimestamp(2, ts);
//5.执行插入
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally{
DataBaseManager.close(pstmt);
DataBaseManager.close(conn);
}
}
2. 在sql server 2005中建立数据库expense
建表expense：

使用JDBC时：
public void addExpense(Expense expense) {
//1.获得连接对象
Connection conn = DataBaseManager.getConnection();
//2.SQL语句
String addSql = "insert into expense(number,date) values(?,getdate());";
//3.通过连接对象来创建一个预处理语句对象
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(addSql);
//4.设置参数
pstmt.setString(1, expense.getNumber());
//5.执行插入
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally{
DataBaseManager.close(pstmt);
DataBaseManager.close(conn);
}
}
或者：
public void addExpense(Expense expense) {
//1.获得连接对象
Connection conn = DataBaseManager.getConnection();
//2.SQL语句
String addSql = "insert into expense(number,date) values(?,?);";
//3.通过连接对象来创建一个预处理语句对象
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(addSql);
//4.设置参数
pstmt.setString(1, expense.getNumber());
java.util.Date date=new java.util.Date();
Timestamp ts=new Timestamp(date.getTime());
pstmt.setTimestamp(2, ts);
//5.执行插入
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally{
DataBaseManager.close(pstmt);
DataBaseManager.close(conn);
}
}
3. java比较时间大小
求时间差：
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Date now = df.parse("2004-03-26 13:31:40");
java.util.Date date=df.parse("2004-01-02 11:30:24");
long l=now.getTime()-date.getTime();
long day=l/(24*60*60*1000);
long hour=(l/(60*60*1000)-day*24);
long min=((l/(60*1000))-day*24*60-hour*60);
long s=(l/1000-day*24*60*60-hour*60*60-min*60);
System.out.println(""+day+"天"+hour+"小时"+min+"分"+s+"秒");
比较时间大小：
String s1="2008-01-25 09:12:09";
String s2="2008-01-29 09:12:11";
java.text.DateFormat df=new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Calendar c1=java.util.Calendar.getInstance();
java.util.Calendar c2=java.util.Calendar.getInstance();
try
{
c1.setTime(df.parse(s1));
c2.setTime(df.parse(s2));
}catch(java.text.ParseException e){
System.err.println("格式不正确");
}
int result=c1.compareTo(c2);
if(result==0)
System.out.println("c1相等c2");
else if(result<0)
System.out.println("c1小于c2");
else
System.out.println("c1大于c2");