一.JDBC对事务的支持

1.默认管理方式

• JDBC默认会自动管理事务
• 每次调用executeUpdate()时它会自动commit
  

  一个业务内只包含一次DML

2.手动管理方式

• 取消自动: conn.setAutoCommit(false)
• 手动提交: conn.commit()
• 手动回滚: conn.rollback()

3.什么是事务

• 满足如下4个特征的数据库访问叫事务:
• 原子性: 事务是一个完整的过程,要么都成功,要么都失败.
• 一致性: 事务访问前后数据一致,即收支平衡.
• 隔离性: 事务过程中的数据要隔离,不允许别人访问.
• 持久性: 事务一旦达成,就永久有效.
  

  上述4句话是一个整体,构成了事务的特征.

二.批量添加数据


三.获取自动生成的ID

1.反查ID

• insert into user values(seq.nextval,?,?)
• select * from user where username=?

2.记录ID

• select seq.nextval from dual -> id
• insert into user values(id,?,?)

3.让PS对象返回生成的ID(*)

• 详见案例

四.JDBC对分页的支持

1.假分页(内存分页)

• 第1次查询时获取所有数据,并将其存入内存(List)
• 第N次查询时不再访问数据库,而是从内存中(List)取数
• 特点: 第1次查询巨慢,再次查询快,耗内存
  

  适合数据量很小的小项目

2.真分页(物理分页)*

• 每次查询时都是获取一页的数据
• 使用分页的SQL进行查询
• 特点: 每次查询速度都一样,不耗内存
• 适合任意的项目

五.DAO

1.对JDBC封装的思想

2.说明

• 接口,工厂将来再讲
• 当前的案例太简单,也没有完整的项目,不利于阐述这些内容

补充1:JDBC中的日期类型

• 在JDBC中要使用java.sql下的日期类型
• java.sql.Date 年月日
• java.sql.Time 时分秒
• java.sql.Timestamp 年月日时分秒
  

  上述日期都是java.util.Date的子类

补充2:JavaBean

• 满足如下规范的类:
• 必须有包
• 必须有无参构造器
• 必须实现序列化接口
• 通常有get/set方法

---

/jdbc/src/main/resources/db.properties

# db connection parameters
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:xe
user=lhh
pwd=123456
# datasource parameters
initsize=1
maxsize=2

/jdbc/src/main/java/dao/EmpDao.java

package dao;import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;import entity.Emp;
import util.DBUtil;public class EmpDao implements Serializable {
    /*** 增加一个员工*/public void save(Emp emp) {}/*** 根据ID修改员工* */public void update(Emp emp) {Connection conn = null;try {conn = DBUtil.getConnection();String sql = "update emps set "+ "ename=?,"+ "job=?,"+ "mgr=?,"+ "hiredate=?,"+ "sal=?,"+ "comm=?,"+ "deptno=? "+ "where empno=?";PreparedStatement ps = conn.prepareStatement(sql);ps.setString(1, emp.getEname());ps.setString(2, emp.getJob());ps.setInt(3, emp.getMgr());ps.setDate(4, emp.getHiredate());ps.setDouble(5, emp.getSal());ps.setDouble(6, emp.getComm());ps.setInt(7, emp.getDeptno());ps.setInt(8, emp.getEmpno());ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("修改员工失败", e);} finally {DBUtil.close(conn);}}/*** 根据ID删除一个员工* */public void delete(int id) {}/*** 根据ID查询一个员工* */public Emp findById(int id) {Connection conn = null;try {conn = DBUtil.getConnection();String sql = "select * from emps "+ "where empno=?";PreparedStatement ps = conn.prepareStatement(sql);ps.setInt(1, id);ResultSet rs = ps.executeQuery();if(rs.next()) {Emp e = new Emp();e.setEmpno(rs.getInt("empno"));e.setEname(rs.getString("ename"));e.setJob(rs.getString("job"));e.setMgr(rs.getInt("mgr"));e.setHiredate(rs.getDate("hiredate"));e.setSal(rs.getDouble("sal"));e.setComm(rs.getDouble("comm"));e.setDeptno(rs.getInt("deptno"));return e;}} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("查询员工失败", e);} finally {DBUtil.close(conn);}return null;}/*** 根据部门ID查询员工* */public List<Emp> findByDept(int deptno) {return null;}}

/jdbc/src/main/java/entity/Emp.java

package entity;import java.io.Serializable;
import java.sql.Date;public class Emp implements Serializable{
    private Integer empno;private String ename;private String job;private Integer mgr;private Date hiredate;private Double sal;private Double comm;private Integer deptno;public Integer getEmpno() {return empno;}public void setEmpno(Integer empno) {this.empno = empno;}public String getEname() {return ename;}public void setEname(String ename) {this.ename = ename;}public String getJob() {return job;}public void setJob(String job) {this.job = job;}public Integer getMgr() {return mgr;}public void setMgr(Integer mgr) {this.mgr = mgr;}public Date getHiredate() {return hiredate;}public void setHiredate(Date hiredate) {this.hiredate = hiredate;}public Double getSal() {return sal;}public void setSal(Double sal) {this.sal = sal;}public Double getComm() {return comm;}public void setComm(Double comm) {this.comm = comm;}public Integer getDeptno() {return deptno;}public void setDeptno(Integer deptno) {this.deptno = deptno;}}

/jdbc/src/main/java/util/DBTool.java

package util;import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;public class DBTool {
    private static String driver;private static String url;private static String user;private static String pwd;static {Properties p = new Properties();try {//加载配置文件p.load(DBTool.class.getClassLoader().getResourceAsStream("db.properties"));//读取连接参数driver = p.getProperty("driver");url = p.getProperty("url");user = p.getProperty("user");pwd = p.getProperty("pwd");//注册驱动Class.forName(driver);} catch (IOException e) {e.printStackTrace();throw new RuntimeException("加载db.properties失败", e);} catch (ClassNotFoundException e) {e.printStackTrace();throw new RuntimeException("找不到这个驱动", e);}}public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, pwd);}public static void close(Connection conn) {if(conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("关闭连接失败", e);}}}}

/jdbc/src/main/java/util/DBUtil.java

package util;import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;import org.apache.commons.dbcp.BasicDataSource;public class DBUtil {
    //连接池对象-由DBCP提供private static BasicDataSource ds;static {Properties p = new Properties();try {p.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));//读取参数String driver = p.getProperty("driver");String url = p.getProperty("url");String user = p.getProperty("user");String pwd = p.getProperty("pwd");String initsize = p.getProperty("initsize");String maxsize = p.getProperty("maxsize");//创建连接池ds = new BasicDataSource();//设置参数//使用这个参数注册驱动ds.setDriverClassName(driver);//使用这3个参数创建连接ds.setUrl(url);ds.setUsername(user);ds.setPassword(pwd);//使用其他参数管理连接ds.setInitialSize(new Integer(initsize));ds.setMaxActive(new Integer(maxsize));} catch (IOException e) {e.printStackTrace();throw new RuntimeException("加载db.properties失败", e);}}public static Connection getConnection() throws SQLException {return ds.getConnection();}/*** 由连接池创建的连接,连接的close方法* 被连接池重写了,变为了归还连接的逻辑,* 即:连接池会将连接的状态设置为空闲,* 并清空连接中所包含的任何数据.*/public static void close(Connection conn) {if(conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("归还连接失败", e);}}}public static void rollback(Connection conn) {if(conn != null) {try {conn.rollback();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("回滚失败", e);}}}}

/jdbc/src/test/java/day03/TestDay03.java

package day03;import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;import org.junit.Test;import dao.EmpDao;
import entity.Emp;
import util.DBUtil;public class TestDay03 {
    /*** 批量增加108个员工*/@Testpublic void test1() {Connection conn = null;try {conn = DBUtil.getConnection();conn.setAutoCommit(false);String sql = "insert into emps values("+ "emps_seq.nextval,"+ "?,?,?,?,?,?,?)";PreparedStatement ps = conn.prepareStatement(sql);for(int i=1;i<=108;i++) {//将数据暂存到ps对象里ps.setString(1, "好汉"+i);ps.setString(2, "打劫");ps.setInt(3, 0);ps.setDate(4, new Date(System.currentTimeMillis()));ps.setDouble(5, 6000.0);ps.setDouble(6, 8000.0);ps.setInt(7, 3);ps.addBatch();//每50条数据批量提交一次if(i%50==0) {ps.executeBatch();//清空ps中暂存的数据,便于下一批添加ps.clearBatch();}}//为了避免有零头,再多提交一次ps.executeBatch();conn.commit();} catch (SQLException e) {e.printStackTrace();DBUtil.rollback(conn);} finally {DBUtil.close(conn);}}/*** 先增加一个部门,再给此部门增加一个员工.* 要点:增加部门后如何获得生成的部门ID.*/@Testpublic void test2() {//假设页面传入的部门数据是String dname = "测试部";String loc = "杭州";//假设页面传入的员工数据是String ename = "八戒";String job = "取经";int mgr = 0;Date hiredate = new Date(System.currentTimeMillis());double sal = 5000.0;double comm = 1000.0;Connection conn = null;try {conn = DBUtil.getConnection();conn.setAutoCommit(false);//增加一个部门String sql = "insert into depts values("+ "depts_seq.nextval,?,?)";//参数2是一个数组,用来告诉PS,//需要它返回哪些字段.PreparedStatement ps = conn.prepareStatement(sql, new String[]{
   "deptno"});ps.setString(1, dname);ps.setString(2, loc);ps.executeUpdate();//从PS中获取生成的主键//结果集中包含1行1列ResultSet rs = ps.getGeneratedKeys();rs.next();//这种场景下的结果集,只能通过//字段的序号获取值.int deptno = rs.getInt(1);//增加一个员工String sql2 = "insert into emps values("+ "emps_seq.nextval,"+ "?,?,?,?,?,?,?)";PreparedStatement ps2 = conn.prepareStatement(sql2);ps2.setString(1, ename);ps2.setString(2, job);ps2.setInt(3, mgr);ps2.setDate(4, hiredate);ps2.setDouble(5, sal);ps2.setDouble(6, comm);ps2.setInt(7, deptno);ps2.executeUpdate();conn.commit();} catch (SQLException e) {e.printStackTrace();DBUtil.rollback(conn);} finally {DBUtil.close(conn);}}/*** 分页查询员工*/@Testpublic void test3() {//假设需求规定了每页显示10行int size = 10;//假设用户点了第3页int page = 3;Connection conn = null;try {conn = DBUtil.getConnection();String sql = "select * from ("+ " select e.*,rownum r from ("+ " select * from emps "+ " order by empno"+ " ) e"+ ") where r between ? and ?";PreparedStatement ps = conn.prepareStatement(sql);//起始行ps.setInt(1, (page-1)*size+1);//终止行ps.setInt(2, page*size);ResultSet rs = ps.executeQuery();while(rs.next()) {System.out.println(rs.getInt("empno"));System.out.println(rs.getString("ename"));}} catch (SQLException e) {e.printStackTrace();} finally {DBUtil.close(conn);}}/*** 1.先根据ID查询某个员工* 2.修改此员工数据*/@Testpublic void test4() {EmpDao dao = new EmpDao();Emp e = dao.findById(129);if(e != null) {System.out.println(e.getEname());e.setEname("苍老师");dao.update(e);}}}