java的知识点45——事务||测试时间处理(java.sql.date,time,timestamp)

事务

事务基本概念： 一组要么同时执行成功，要么同时执行失败的SQL语句。是数据库操作的一个执行单元！

事务开始于： 连接到数据库上，并执行一条DML语句(INSERT、UPDATE或DELETE)。

事务结束于： 执行COMMIT或ROLLBACK语句

 测试事务的基本概念和用法

package cn.dym01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * 测试事务的基本概念和用法
 *
 */
public class Demo06 {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		try {
			//加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","123456");
			
			conn.setAutoCommit(false); //JDBC中默认是true,自动提交事务
			
			ps1 = conn.prepareStatement("insert into t_user (username,pwd) values (?,?)"); //?占位符
			ps1.setObject(1,"代止兮");  
			ps1.setObject(2, "7458964");
			ps1.execute();
			System.out.println("插入一个用户，代止兮");
			
			try {
				Thread.sleep(6000);
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			ps2 = conn.prepareStatement("insert into t_user (username,pwd) values (?,?,?)"); //?占位符
			ps2.setObject(1,"苍凌");  
			ps2.setObject(2, "123456");
			ps2.execute();
			System.out.println("插入一个用户，苍凌");
			
			
			conn.commit();
			
			
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			try {
				conn.rollback();   //回滚
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
			try {
				if(ps1!=null){
					ps1.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

---

时间类型

测试时间处理(java.sql.date,time,timestamp)

package cn.dym01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Random;
/**
 * 测试时间处理(java.sql.date,time,timestamp)
 *
 */
public class Demo07 {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		try {
			//加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","123456");
			
			for(int i=0;i<1000;i++) {
				ps1 = conn.prepareStatement("insert into t_user (username,pwd,regTime,lastLoginTime) values (?,?,?,?)"); //?占位符
				ps1.setObject(1,"苍小凌"+i);  
				ps1.setObject(2, "7458964");
				
				int rand=100000000+new Random().nextInt(100000000);
				
				java.sql.Date date=new java.sql.Date(System.currentTimeMillis()-rand);
				ps1.setDate(3, date);
				Timestamp stamp=new Timestamp(System.currentTimeMillis()-rand);  //如果需要插入指定日期，可以使用calendar、DateFormat类
				ps1.setTimestamp(4,stamp);
				ps1.execute();
			}
			
			System.out.println("插入一个用户，苍小凌");
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

---

取出指定时间段的数据

package cn.dym01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
 * 测试时间处理(java.sql.date,time,timestamp)  取出指定时间段的数据
 *
 */
public class Demo08 {
	/**
	 * 将字符串代表的日期转为long数字（格式：yyyy-MM-dd hh:mm:ss)
	 */
	
	public static  long  str2Date(String dateStr){
		DateFormat s = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		try {
			return s.parse(dateStr).getTime();
		} catch (ParseException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		try {
			//加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","123456");
			
//			ps = conn.prepareStatement("select * from t_user where regTime>? and regTime<?");
//			
//			java.sql.Date start=new java.sql.Date(str2Date("2019-3-05 08:54:21"));
//			java.sql.Date end=new java.sql.Date(str2Date("2019-3-07 12:54:21"));
//			
//			ps.setObject(1, start);
//			ps.setObject(2, end);
			
			ps=conn.prepareStatement("select * from t_user where lastLoginTime>? and lastLoginTime<?");
			Timestamp start=new Timestamp(str2Date("2019-3-5 21:46:51"));
			Timestamp end=new Timestamp(str2Date("2019-3-6 00:00:00"));
			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();
		}
	}
}