1 实现将某一个部门(Dept)的员工(Emp)工资的提升
本案例的详细要求如下:
1.如果职位(job)为“ANALYST”的员工,那么工资提升20%。
2.如果职位(job)为“MANAGER”的员工,那么工资提升30%。
3. 要求某部门下的以上两个职位的员工工资,要么工资全部提升成功,要么工资全部提升失败。
参考答案
实现此案例需要按照如下步骤进行。
步骤一:准备JDBC操作数据库的基本代码
首先,在EmpDAO类中新建updateSalByDeptno方法,方法的声明如下所示:
- publicvoidupdateSalByDeptno(int deptno){}
该方法实现将部门编号为deptno的、职位为“ANALYST”和“MANAGER”的员工的工资进行提升。
然后,准备数据库连接的Connection对象、操作SQL语句的Statement对象并进行异常的处理,代码如下所示:
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- publicclass EmpDAO {
- publicstaticvoid main(String[] args) {
- EmpDAO dao = new EmpDAO();
- // dao.findSalByEname("CLARK");
- // dao.findSalByEname("a' OR 'b'='b");
- // dao.findSalByEname1("CLARK");
- // dao.findSalByEname1("a' OR 'b'='b");
- }
- publicvoidupdateSalByDeptno(int deptno) {
- Connection con = null;
- Statement stmt = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findSalByEname(String ename) {
- ... ...
- }
- publicvoid findSalByEname1(String ename) {
- ... ...
- }
- }
步骤二:实现员工工资的提升
使用Connection的setAutoCommit方法、commit方法以及rollback方法来控制事务,以正确实现员工工资的提升,代码如下所示:
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- publicclass EmpDAO {
- publicstaticvoid main(String[] args) {
- EmpDAO dao = new EmpDAO();
- // dao.findSalByEname("CLARK");
- // dao.findSalByEname("a' OR 'b'='b");
- // dao.findSalByEname1("CLARK");
- // dao.findSalByEname1("a' OR 'b'='b");
- }
- publicvoidupdateSalByDeptno(int deptno) {
- Connection con = null;
- Statement stmt = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- // 插入数据的SQL语句
- String sql1 = "update emp set sal =sal*1.2" + " where deptno="
- + deptno + " and job='ANALYST'";
- String sql2 = "update emp set sal =sal*1.3" + " where deptno="
- + deptno + " and job='MANAGER'";
- // 关闭自动提交
- con.setAutoCommit(false);
- // 执行SQL语句
- stmt.executeUpdate(sql1);
- stmt.executeUpdate(sql2);
- // 提交
- con.commit();
- } catch (SQLException e) {
- try {
- con.rollback();
- } catch (SQLException e1) {
- System.out.println("回滚事务异常!");
- throw new RuntimeException(e1);
- }
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findSalByEname(String ename) {
- ... ...
- }
- publicvoid findSalByEname1(String ename) {
- ... ...
- }
- }
步骤三:测试
在EmpDAO类的main方法中,调用updateSalByDeptno方法,代码如下所示:
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- publicclass EmpDAO {
- publicstaticvoid main(String[] args) {
- EmpDAO dao = new EmpDAO();
- // dao.findSalByEname("CLARK");
- // dao.findSalByEname("a' OR 'b'='b");
- // dao.findSalByEname1("CLARK");
- // dao.findSalByEname1("a' OR 'b'='b");
- // 员工工资提升
- dao.updateSalByDeptno(20);
- }
- publicvoidupdateSalByDeptno(int deptno) {
- Connection con = null;
- Statement stmt = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- // 插入数据的SQL语句
- String sql1 = "update emp set sal =sal*1.2" + " where deptno="
- + deptno + " and job='ANALYST'";
- String sql2 = "update emp set sal =sal*1.3" + " where deptno="
- + deptno + " and job='MANAGER'";
- // 关闭自动提交
- con.setAutoCommit(false);
- // 执行SQL语句
- stmt.executeUpdate(sql1);
- stmt.executeUpdate(sql2);
- // 提交
- con.commit();
- } catch (SQLException e) {
- try {
- con.rollback();
- } catch (SQLException e1) {
- System.out.println("回滚事务异常!");
- thrownew RuntimeException(e1);
- }
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findSalByEname(String ename) {
- ... ...
- }
- publicvoid findSalByEname1(String ename) {
- ... ...
- }
- }
运行EmpDAO类,然后查看Oracle数据库中的emp表,会发现部门编号为20的、职位为“ANALYST”和“MANAGER”的员工的工资进行了提升。
本案例中,类EmpDAO的完整代码如下所示:
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- publicclass EmpDAO {
- publicstaticvoid main(String[] args) {
- EmpDAO dao = new EmpDAO();
- // dao.findSalByEname("CLARK");
- // dao.findSalByEname("a' OR 'b'='b");
- // dao.findSalByEname1("CLARK");
- // dao.findSalByEname1("a' OR 'b'='b");
- // 事务管理
- // dao.updateSalByDeptno(20);
- }
- publicvoid updateSalByDeptno(int deptno) {
- Connection con = null;
- Statement stmt = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- // 插入数据的SQL语句
- String sql1 = "update emp set sal =sal*1.2" + " where deptno="
- + deptno + " and job='ANALYST'";
- String sql2 = "update emp set sal =sal*1.3" + " where deptno="
- + deptno + " and job='MANAGER'";
- // 关闭自动提交
- con.setAutoCommit(false);
- // 执行SQL语句
- stmt.executeUpdate(sql1);
- stmt.executeUpdate(sql2);
- // 提交
- con.commit();
- } catch (SQLException e) {
- try {
- con.rollback();
- } catch (SQLException e1) {
- System.out.println("回滚事务异常!");
- thrownew RuntimeException(e1);
- }
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findSalByEname(String ename) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- String sql = null;
- try {
- sql = "select sal from emp where ename = ?";
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql);
- stmt.setString(1, ename);
- rs = stmt.executeQuery();
- while (rs.next()) {
- double sal = rs.getDouble("sal");
- System.out.println(sal);
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源发生异常");
- }
- }
- }
- publicvoid findSalByEname1(String ename) {
- Connection con = null;
- Statement stmt = null;
- ResultSet rs = null;
- String sql = null;
- try {
- sql = "select sal from emp where ename = '" + ename + "'";
- System.out.println(sql);
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- rs = stmt.executeQuery(sql);
- while (rs.next()) {
- double sal = rs.getDouble("sal");
- System.out.println(sal);
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源发生异常");
- }
- }
- }
- }
db.properties文件的内容与之前案例一样没有变化,该文件完整内容如下所示:
- jdbc.driverClassName=oracle.jdbc.OracleDriver
- jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
- jdbc.username=scott
- jdbc.password=tiger
- #<!-- 初始化连接 -->
- dataSource.initialSize=10
- #<!-- 最大空闲连接 -->
- dataSource.maxIdle=20
- #<!-- 最小空闲连接 -->
- dataSource.minIdle=5
- #最大连接数量
- dataSource.maxActive=50
- #<!-- 超时等待时间以毫秒为单位 (6000毫秒/1000等于60秒 )-->
- dataSource.maxWait=1000
ConnectionSource类与之前案例一样没有变化,该类完整内容如下所示:
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.util.Properties;
- import org.apache.commons.dbcp.BasicDataSource;
- public class ConnectionSource {
- private static BasicDataSource dataSource = null;
- public ConnectionSource() {
- }
- public static void init() {
- Properties dbProps = new Properties();
- // 取配置文件可以根据实际的不同修改
- try {
- dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
- "day01/v4/db.properties"));
- } catch (IOException e) {
- e.printStackTrace();
- }
- try {
- String driveClassName = dbProps.getProperty("jdbc.driverClassName");
- String url = dbProps.getProperty("jdbc.url");
- String username = dbProps.getProperty("jdbc.username");
- String password = dbProps.getProperty("jdbc.password");
- String initialSize = dbProps.getProperty("dataSource.initialSize");
- String minIdle = dbProps.getProperty("dataSource.minIdle");
- String maxIdle = dbProps.getProperty("dataSource.maxIdle");
- String maxWait = dbProps.getProperty("dataSource.maxWait");
- String maxActive = dbProps.getProperty("dataSource.maxActive");
- dataSource = new BasicDataSource();
- dataSource.setDriverClassName(driveClassName);
- dataSource.setUrl(url);
- dataSource.setUsername(username);
- dataSource.setPassword(password);
- // 初始化连接数
- if (initialSize != null)
- dataSource.setInitialSize(Integer.parseInt(initialSize));
- // 最小空闲连接
- if (minIdle != null)
- dataSource.setMinIdle(Integer.parseInt(minIdle));
- // 最大空闲连接
- if (maxIdle != null)
- dataSource.setMaxIdle(Integer.parseInt(maxIdle));
- // 超时回收时间(以毫秒为单位)
- if (maxWait != null)
- dataSource.setMaxWait(Long.parseLong(maxWait));
- // 最大连接数
- if (maxActive != null) {
- if (!maxActive.trim().equals("0"))
- dataSource.setMaxActive(Integer.parseInt(maxActive));
- }
- } catch (Exception e) {
- e.printStackTrace();
- System.out.println("创建连接池失败!请检查设置!!!");
- }
- }
- public static synchronized Connection getConnection() throws SQLException {
- if (dataSource == null) {
- init();
- }
- Connection conn = null;
- if (dataSource != null) {
- conn = dataSource.getConnection();
- }
- return conn;
- }
- }
2 批量插入Dept数据
向Dept表中批量插入100条数据,需要插入数据的列为deptno、dname,这两列的数据要求如下:
1. deptno列的数据通过序列dept_seq自动生成;
2. dname列的数据为字符串,格式为:“name”+循环次数i。
参考答案
实现此案例需要按照如下步骤进行。
步骤一:在Oracle数据库中创建序列dept_seq
在Oracle数据库中创建名为dept_seq的序列,该序列的起始值为1、步进为1,SQL语句如下所示:
- create sequence dept_seq start with 1 increment by 1;
步骤二:准备JDBC操作数据库的基本代码
首先,新建类Batch,在该类中新建batchAdd方法;然后,准备数据库连接Connection对象、操作SQL语句的Statement对象以及设置事务管理;最后进行异常的处理,代码如下所示:
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.Random;
- publicclass Batch {
- publicvoid batchAdd() {
- Connection con = null;
- Statement stmt = null;
- String sql = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- // 关闭自动提交
- con.setAutoCommit(false);
- // 提交
- con.commit();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicstaticvoid main(String args[]) {
- }
- }
步骤三:批量向Dept表中插入数据
使用Statement的addBatch方法和executeBatch方法,批量向Dept表中插入数据,代码如下所示:
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.Random;
- publicclass Batch {
- publicvoid batchAdd() {
- Connection con = null;
- Statement stmt = null;
- String sql = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- // 关闭自动提交
- con.setAutoCommit(false);
- for (int i = 0; i < 100; i++) {
- // 插入数据的SQL语句
- sql = "insert into dept(deptno, dname, loc) values("
- + "dept_seq.nextval, 'name" + i + "', "
- + new Random().nextInt(10000) + ")";
- System.out.println(sql);
- // 将SQL语句加入到Batch中
- stmt.addBatch(sql);
- }
- // 执行批处理
- stmt.executeBatch();
- // 提交
- con.commit();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicstaticvoid main(String args[]) {
- }
- }
步骤四:测试是否批量插入数据成功
在Batch类的main方法中,调用batchAdd方法,代码如下所示:
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.Random;
- publicclass Batch {
- publicvoid batchAdd() {
- Connection con = null;
- Statement stmt = null;
- String sql = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- // 关闭自动提交
- con.setAutoCommit(false);
- for (int i = 0; i < 100; i++) {
- // 插入数据的SQL语句
- sql = "insert into dept(deptno, dname, loc) values("
- + "dept_seq.nextval, 'name" + i + "', "
- + new Random().nextInt(10000) + ")";
- System.out.println(sql);
- // 将SQL语句加入到Batch中
- stmt.addBatch(sql);
- }
- // 执行批处理
- stmt.executeBatch();
- // 提交
- con.commit();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicstaticvoid main(String args[]) {
- Batch batch = new Batch();
- batch.batchAdd();
- }
- }
运行Batch类,然后去查看Oracle数据库中的Dept表,会发现批量向该表中插入了100条记录。
本案例中,Batch类的完整代码如下所示:
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.Random;
- publicclass Batch {
- publicvoid batchAdd() {
- Connection con = null;
- Statement stmt = null;
- String sql = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- // 关闭自动提交
- con.setAutoCommit(false);
- for (int i = 0; i < 100; i++) {
- // 插入数据的SQL语句
- sql = "insert into dept(deptno, dname, loc) values("
- + "dept_seq.nextval, 'name" + i + "', "
- + new Random().nextInt(10000) + ")";
- System.out.println(sql);
- // 将SQL语句加入到Batch中
- stmt.addBatch(sql);
- }
- // 执行批处理
- stmt.executeBatch();
- // 提交
- con.commit();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicstaticvoid main(String args[]) {
- Batch batch = new Batch();
- batch.batchAdd();
- }
- }
db.properties文件的内容与之前案例一样没有变化,该文件完整内容如下所示:
- jdbc.driverClassName=oracle.jdbc.OracleDriver
- jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
- jdbc.username=scott
- jdbc.password=tiger
- #<!-- 初始化连接 -->
- dataSource.initialSize=10
- #<!-- 最大空闲连接 -->
- dataSource.maxIdle=20
- #<!-- 最小空闲连接 -->
- dataSource.minIdle=5
- #最大连接数量
- dataSource.maxActive=50
- #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
- dataSource.maxWait=1000
ConnectionSource类与之前案例一样没有变化,该类完整内容如下所示:
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.util.Properties;
- import org.apache.commons.dbcp.BasicDataSource;
- public class ConnectionSource {
- private static BasicDataSource dataSource = null;
- public ConnectionSource() {
- }
- public static void init() {
- Properties dbProps = new Properties();
- // 取配置文件可以根据实际的不同修改
- try {
- dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
- "day01/v4/db.properties"));
- } catch (IOException e) {
- e.printStackTrace();
- }
- try {
- String driveClassName = dbProps.getProperty("jdbc.driverClassName");
- String url = dbProps.getProperty("jdbc.url");
- String username = dbProps.getProperty("jdbc.username");
- String password = dbProps.getProperty("jdbc.password");
- String initialSize = dbProps.getProperty("dataSource.initialSize");
- String minIdle = dbProps.getProperty("dataSource.minIdle");
- String maxIdle = dbProps.getProperty("dataSource.maxIdle");
- String maxWait = dbProps.getProperty("dataSource.maxWait");
- String maxActive = dbProps.getProperty("dataSource.maxActive");
- dataSource = new BasicDataSource();
- dataSource.setDriverClassName(driveClassName);
- dataSource.setUrl(url);
- dataSource.setUsername(username);
- dataSource.setPassword(password);
- // 初始化连接数
- if (initialSize != null)
- dataSource.setInitialSize(Integer.parseInt(initialSize));
- // 最小空闲连接
- if (minIdle != null)
- dataSource.setMinIdle(Integer.parseInt(minIdle));
- // 最大空闲连接
- if (maxIdle != null)
- dataSource.setMaxIdle(Integer.parseInt(maxIdle));
- // 超时回收时间(以毫秒为单位)
- if (maxWait != null)
- dataSource.setMaxWait(Long.parseLong(maxWait));
- // 最大连接数
- if (maxActive != null) {
- if (!maxActive.trim().equals("0"))
- dataSource.setMaxActive(Integer.parseInt(maxActive));
- }
- } catch (Exception e) {
- e.printStackTrace();
- System.out.println("创建连接池失败!请检查设置!!!");
- }
- }
- public static synchronized Connection getConnection() throws SQLException {
- if (dataSource == null) {
- init();
- }
- Connection conn = null;
- if (dataSource != null) {
- conn = dataSource.getConnection();
- }
- return conn;
- }
- }
3 向Emp表中插入一个团队成员
向Emp表中插入一个团队成员,该团队的成员信息如图-1所示。
图-1
从图-1可以看出tom为这个团队的管理者,其他三位员工的管理者ID(mgr) 都为1,而1是管理者tom的员工编号(empno)。
要求向Emp表插入以上四个员工的信息。职员marry、terry、jim的管理者ID(mgr)为刚刚插入Emp表的管理者tom的员工编号(empno)的数据。另外,Emp表的主键列empno的数据通过序列emp_seq获得。
参考答案
实现此案例需要按照如下步骤进行。
步骤一:创建序列emp_seq
在Oracle数据库中创建序列名为emp_seq,该序列的起始值为1、步进为1,SQL语句如下所示:
- create sequence emp_seq start with 1 increment by 1;
步骤二:创建Emp类
Emp类为实体类和数据表emp之间的映射,该类的代码如下所示:
- public class Emp {
- private int empNo;
- private String ename;
- private String job;
- private int mgr;
- private String hiredate;
- private double sal;
- private double comm;
- private int deptno;
- public Emp() {
- super();
- }
- public Emp(int empNo, String ename, String job, int mgr, String hiredate,
- double sal, double comm, int deptno) {
- super();
- this.empNo = empNo;
- this.ename = ename;
- this.job = job;
- this.mgr = mgr;
- this.hiredate = hiredate;
- this.sal = sal;
- this.comm = comm;
- this.deptno = deptno;
- }
- public int getEmpNo() {
- return empNo;
- }
- public void setEmpNo(int 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 int getMgr() {
- return mgr;
- }
- public void setMgr(int mgr) {
- this.mgr = mgr;
- }
- public String getHiredate() {
- return hiredate;
- }
- public void setHiredate(String 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 int getDeptno() {
- return deptno;
- }
- public void setDeptno(int deptno) {
- this.deptno = deptno;
- }
- }
步骤三:准备JDBC操作数据库的基本代码
首先,在EmpDAO类中新建addTeam方法,该方法的声明如下:
- publicvoid addTeam(List<Emp> emps)
该方法用于实现向Emp表插入一个团队的数据。其中参数emps表示该团队的所有员工,在集合emps中的索引为0的位置存储管理者,索引为1、2、3的位置存储该管理者下属的三名员工。
然后,准备数据库连接Connection对象、操作SQL语句的PreparedStatement对象以及设置事务管理;最后进行异常的处理,代码如下所示:
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- publicclass EmpDAO {
- publicstaticvoid main(String[] args) {
- EmpDAO dao = new EmpDAO();
- // dao.findSalByEname("CLARK");
- // dao.findSalByEname("a' OR 'b'='b");
- // dao.findSalByEname1("CLARK");
- // dao.findSalByEname1("a' OR 'b'='b");
- // 事务管理
- // dao.updateSalByDeptno(20);
- }
- publicvoid addTeam(List<Emp> emps) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- String sql = null;
- try {
- con = ConnectionSource.getConnection();
- // 关闭自动提交
- con.setAutoCommit(false);
- con.commit();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid updateSalByDeptno(int deptno) {
- ... ...
- }
- publicvoid findSalByEname(String ename) {
- ... ...
- }
- publicvoid findSalByEname1(String ename) {
- ... ...
- }
- }
步骤四:实现向Emp表中插入一个团队
向Emp表中插入数据,并使用PreparedStatement的getGeneratedKeys方法获得刚刚生成的主键,代码如下所示:
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- publicclass EmpDAO {
- publicstaticvoid main(String[] args) {
- EmpDAO dao = new EmpDAO();
- // dao.findSalByEname("CLARK");
- // dao.findSalByEname("a' OR 'b'='b");
- // dao.findSalByEname1("CLARK");
- // dao.findSalByEname1("a' OR 'b'='b");
- // 事务管理
- // dao.updateSalByDeptno(20);
- }
- publicvoid addTeam(List<Emp> emps) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- String sql = null;
- try {
- con = ConnectionSource.getConnection();
- // 关闭自动提交
- con.setAutoCommit(false);
- sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)"
- + " values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
- stmt = con.prepareStatement(sql, new String[] { "empno" });
- int mgr = 0;
- for (int i = 0; i < emps.size(); i++) {
- Emp emp = (Emp) emps.get(i);
- stmt.setString(1, emp.getEname());
- stmt.setString(2, emp.getJob());
- if (i == 0) {
- stmt.setInt(3, emp.getMgr());
- } else {
- stmt.setInt(3, mgr);
- }
- stmt.setString(4, emp.getHiredate());
- stmt.setDouble(5, emp.getSal());
- stmt.setDouble(6, emp.getComm());
- stmt.setInt(7, emp.getDeptno());
- stmt.executeUpdate();
- if (i == 0) {
- rs = stmt.getGeneratedKeys();
- if (rs.next()) {
- mgr = rs.getInt(1);
- }
- }
- }
- con.commit();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid updateSalByDeptno(int deptno) {
- ... ...
- }
- publicvoid findSalByEname(String ename) {
- ... ...
- }
- publicvoid findSalByEname1(String ename) {
- ... ...
- }
- }
步骤五:测试
按照图-1所示的数据构造四个Emp对象,并将这四个对象按照步骤三所描述的顺序存储到List集合中;然后将List集合对象作为参数传递给addTeam方法,代码如下所示:
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- publicclass EmpDAO {
- publicstaticvoid main(String[] args) {
- EmpDAO dao = new EmpDAO();
- // dao.findSalByEname("CLARK");
- // dao.findSalByEname("a' OR 'b'='b");
- // dao.findSalByEname1("CLARK");
- // dao.findSalByEname1("a' OR 'b'='b");
- // 事务管理
- // dao.updateSalByDeptno(20);
- // 将管理者放置在索引为0的位置
- List<Emp> emps = new ArrayList<Emp>();
- Emp emp1=new Emp();
- emp1.setEname("tom");
- emp1.setJob("manager");
- emp1.setMgr(7839);
- emp1.setHiredate("2014-05-01");
- emp1.setSal(5000);
- emp1.setComm(300);
- emp1.setDeptno(30);
- Emp emp2=new Emp();
- emp2.setEname("marry");
- emp2.setJob("clerk");
- emp2.setMgr(1);
- emp2.setHiredate("2014-05-28");
- emp2.setSal(2000);
- emp2.setDeptno(30);
- Emp emp3=new Emp();
- emp3.setEname("terry");
- emp3.setJob("salesman");
- emp3.setMgr(1);
- emp3.setHiredate("2014-05-29");
- emp3.setSal(2500);
- emp3.setComm(200);
- emp3.setDeptno(30);
- Emp emp4=new Emp();
- emp4.setEname("jim");
- emp4.setJob("salesman");
- emp4.setMgr(1);
- emp4.setHiredate("2014-05-26");
- emp4.setSal(2500);
- emp4.setComm(200);
- emp4.setDeptno(30);
- emps.add(emp1);
- emps.add(emp2);
- emps.add(emp3);
- emps.add(emp4);
- dao.addTeam(emps);
- }
- publicvoid addTeam(List<Emp> emps) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- String sql = null;
- try {
- con = ConnectionSource.getConnection();
- // 关闭自动提交
- con.setAutoCommit(false);
- // 插入主表
- sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)"
- + " values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
- stmt = con.prepareStatement(sql, new String[] { "empno" });
- int mgr = 0;
- for (int i = 0; i < emps.size(); i++) {
- Emp emp = (Emp) emps.get(i);
- stmt.setString(1, emp.getEname());
- stmt.setString(2, emp.getJob());
- if (i == 0) {
- stmt.setInt(3, emp.getMgr());
- } else {
- stmt.setInt(3, mgr);
- }
- stmt.setString(4, emp.getHiredate());
- stmt.setDouble(5, emp.getSal());
- stmt.setDouble(6, emp.getComm());
- stmt.setInt(7, emp.getDeptno());
- stmt.executeUpdate();
- if (i == 0) {
- rs = stmt.getGeneratedKeys();
- if (rs.next()) {
- mgr = rs.getInt(1);
- }
- }
- }
- con.commit();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid updateSalByDeptno(int deptno) {
- ... ...
- }
- publicvoid findSalByEname(String ename) {
- ... ...
- }
- publicvoid findSalByEname1(String ename) {
- ... ...
- }
- }
运行上述代码,向Emp表插入了四条记录。其中一条记录为管理者信息,如果管理者的员工ID为1,那个其余三条员工记录的管理者ID为1。
本案例中,Emp类的完整代码如下:
- public class Emp {
- private int empNo;
- private String ename;
- private String job;
- private int mgr;
- private String hiredate;
- private double sal;
- private double comm;
- private int deptno;
- public Emp() {
- super();
- }
- public Emp(int empNo, String ename, String job, int mgr, String hiredate,
- double sal, double comm, int deptno) {
- super();
- this.empNo = empNo;
- this.ename = ename;
- this.job = job;
- this.mgr = mgr;
- this.hiredate = hiredate;
- this.sal = sal;
- this.comm = comm;
- this.deptno = deptno;
- }
- public int getEmpNo() {
- return empNo;
- }
- public void setEmpNo(int 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 int getMgr() {
- return mgr;
- }
- public void setMgr(int mgr) {
- this.mgr = mgr;
- }
- public String getHiredate() {
- return hiredate;
- }
- public void setHiredate(String 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 int getDeptno() {
- return deptno;
- }
- public void setDeptno(int deptno) {
- this.deptno = deptno;
- }
- }
EmpDAO类的完整代码如下所示:
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- publicclass EmpDAO {
- publicstaticvoid main(String[] args) {
- EmpDAO dao = new EmpDAO();
- // dao.findSalByEname("CLARK");
- // dao.findSalByEname("a' OR 'b'='b");
- // dao.findSalByEname1("CLARK");
- // dao.findSalByEname1("a' OR 'b'='b");
- // 事务管理
- // dao.updateSalByDeptno(20);
- // 将管理者放置在emps(0)位置
- List<Emp> emps = new ArrayList<Emp>();
- Emp emp1=new Emp();
- emp1.setEname("tom");
- emp1.setJob("manager");
- emp1.setMgr(7839);
- emp1.setHiredate("2014-05-01");
- emp1.setSal(5000);
- emp1.setComm(300);
- emp1.setDeptno(30);
- Emp emp2=new Emp();
- emp2.setEname("marry");
- emp2.setJob("clerk");
- emp2.setMgr(1);
- emp2.setHiredate("2014-05-28");
- emp2.setSal(2000);
- emp2.setDeptno(30);
- Emp emp3=new Emp();
- emp3.setEname("terry");
- emp3.setJob("salesman");
- emp3.setMgr(1);
- emp3.setHiredate("2014-05-29");
- emp3.setSal(2500);
- emp3.setComm(200);
- emp3.setDeptno(30);
- Emp emp4=new Emp();
- emp4.setEname("jim");
- emp4.setJob("salesman");
- emp4.setMgr(1);
- emp4.setHiredate("2014-05-26");
- emp4.setSal(2500);
- emp4.setComm(200);
- emp4.setDeptno(30);
- emps.add(emp1);
- emps.add(emp2);
- emps.add(emp3);
- emps.add(emp4);
- dao.addTeam(emps);
- }
- publicvoid addTeam(List<Emp> emps) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- String sql = null;
- try {
- con = ConnectionSource.getConnection();
- // 关闭自动提交
- con.setAutoCommit(false);
- // 插入主表
- sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)"
- + " values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
- stmt = con.prepareStatement(sql, new String[] { "empno" });
- int mgr = 0;
- for (int i = 0; i < emps.size(); i++) {
- Emp emp = (Emp) emps.get(i);
- stmt.setString(1, emp.getEname());
- stmt.setString(2, emp.getJob());
- if (i == 0) {
- stmt.setInt(3, emp.getMgr());
- } else {
- stmt.setInt(3, mgr);
- }
- stmt.setString(4, emp.getHiredate());
- stmt.setDouble(5, emp.getSal());
- stmt.setDouble(6, emp.getComm());
- stmt.setInt(7, emp.getDeptno());
- stmt.executeUpdate();
- if (i == 0) {
- rs = stmt.getGeneratedKeys();
- if (rs.next()) {
- mgr = rs.getInt(1);
- }
- }
- }
- con.commit();
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid updateSalByDeptno(int deptno) {
- Connection con = null;
- Statement stmt = null;
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- // 插入数据的SQL语句
- String sql1 = "update emp set sal =sal*1.2" + " where deptno="
- + deptno + " and job='ANALYST'";
- String sql2 = "update emp set sal =sal*1.3" + " where deptno="
- + deptno + " and job='MANAGER'";
- // 关闭自动提交
- con.setAutoCommit(false);
- // 执行SQL语句
- stmt.executeUpdate(sql1);
- stmt.executeUpdate(sql2);
- // 提交
- con.commit();
- } catch (SQLException e) {
- try {
- con.rollback();
- } catch (SQLException e1) {
- System.out.println("回滚事务异常!");
- thrownew RuntimeException(e1);
- }
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findSalByEname(String ename) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- String sql = null;
- try {
- sql = "select sal from emp where ename = ?";
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql);
- stmt.setString(1, ename);
- rs = stmt.executeQuery();
- while (rs.next()) {
- double sal = rs.getDouble("sal");
- System.out.println(sal);
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源发生异常");
- }
- }
- }
- publicvoid findSalByEname1(String ename) {
- Connection con = null;
- Statement stmt = null;
- ResultSet rs = null;
- String sql = null;
- try {
- sql = "select sal from emp where ename = '" + ename + "'";
- System.out.println(sql);
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- rs = stmt.executeQuery(sql);
- while (rs.next()) {
- double sal = rs.getDouble("sal");
- System.out.println(sal);
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源发生异常");
- }
- }
- }
- }
db.properties文件的内容与之前案例一样没有变化,该文件完整内容如下所示:
- jdbc.driverClassName=oracle.jdbc.OracleDriver
- jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
- jdbc.username=scott
- jdbc.password=tiger
- #<!-- 初始化连接 -->
- dataSource.initialSize=10
- #<!-- 最大空闲连接 -->
- dataSource.maxIdle=20
- #<!-- 最小空闲连接 -->
- dataSource.minIdle=5
- #最大连接数量
- dataSource.maxActive=50
- #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
- dataSource.maxWait=1000
ConnectionSource类与之前案例一样没有变化,该类完整内容如下所示:
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.util.Properties;
- import org.apache.commons.dbcp.BasicDataSource;
- public class ConnectionSource {
- private static BasicDataSource dataSource = null;
- public ConnectionSource() {
- }
- public static void init() {
- Properties dbProps = new Properties();
- // 取配置文件可以根据实际的不同修改
- try {
- dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
- "day01/v4/db.properties"));
- } catch (IOException e) {
- e.printStackTrace();
- }
- try {
- String driveClassName = dbProps.getProperty("jdbc.driverClassName");
- String url = dbProps.getProperty("jdbc.url");
- String username = dbProps.getProperty("jdbc.username");
- String password = dbProps.getProperty("jdbc.password");
- String initialSize = dbProps.getProperty("dataSource.initialSize");
- String minIdle = dbProps.getProperty("dataSource.minIdle");
- String maxIdle = dbProps.getProperty("dataSource.maxIdle");
- String maxWait = dbProps.getProperty("dataSource.maxWait");
- String maxActive = dbProps.getProperty("dataSource.maxActive");
- dataSource = new BasicDataSource();
- dataSource.setDriverClassName(driveClassName);
- dataSource.setUrl(url);
- dataSource.setUsername(username);
- dataSource.setPassword(password);
- // 初始化连接数
- if (initialSize != null)
- dataSource.setInitialSize(Integer.parseInt(initialSize));
- // 最小空闲连接
- if (minIdle != null)
- dataSource.setMinIdle(Integer.parseInt(minIdle));
- // 最大空闲连接
- if (maxIdle != null)
- dataSource.setMaxIdle(Integer.parseInt(maxIdle));
- // 超时回收时间(以毫秒为单位)
- if (maxWait != null)
- dataSource.setMaxWait(Long.parseLong(maxWait));
- // 最大连接数
- if (maxActive != null) {
- if (!maxActive.trim().equals("0"))
- dataSource.setMaxActive(Integer.parseInt(maxActive));
- }
- } catch (Exception e) {
- e.printStackTrace();
- System.out.println("创建连接池失败!请检查设置!!!");
- }
- }
- public static synchronized Connection getConnection() throws SQLException {
- if (dataSource == null) {
- init();
- }
- Connection conn = null;
- if (dataSource != null) {
- conn = dataSource.getConnection();
- }
- return conn;
- }
- }
4 实现对Dept数据的分页查询(Oracle和MySQL)
使用JDBC分别连接Oracle数据库和MySQL数据库,实现对Dept表数据的分页查询功能。
参考答案
实现此案例需要按照如下步骤进行。
步骤一:添加方法findByPageOracle方法,并构建该方法的骨架代码
首先,在DeptDAO类中添加方法findByPageOracle,该方法的声明如下所示:
- publicvoid findByPageOracle(int page, int pageSize) {}
其中,参数page表示要查询的页码、参数pageSize表示每页显示的记录数。
然后,构建findByPageOracle方法的骨架代码,代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
步骤二:定义SQL语句
在findByPageOracle方法中,定义变量sql_total以及sql来表示两条SQL语句,一条用于查询Emp表的总记录数,另一条作为分页的SQL语句,代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
步骤三:查询Dept表的总记录数
获取数据库连接,使用PreparedStatement执行SQL语句(sql_total变量定义的SQL语句),获取数据库中Dept表的总记录数,代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
步骤四:计算总页数
将总记录数与每页记录数取余数,如果余数为0,则总页数等于总记录数除以每页记录数的商;如果余数不为0,则总页数等于总记录数除以每页记录数的商的基础上加1,代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
步骤五:边界判断
如果要查看的页码大于总页数,则要查看的页码等于总页数;如果要查看的页码小于1,则要查看的页码等于1,代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- // 如果要查看的页数大于最大页,或者小于1,则取最后一页或第一页
- if (page > pages)
- page = pages;
- elseif (page < 1) {
- page = 1;
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
步骤六:计算取记录的起始位置和结束位置
起始位置(begin)的计算公式如下:
- int begin = (page - 1) * pageSize + 1;
结束位置(end)的计算公式如下:
- int end = begin + pageSize - 1;
在findByPageOracle方法中的实现代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- // 如果要查看的页数大于最大页,或者小于1,则取最后一页或第一页
- if (page > pages)
- page = pages;
- elseif (page < 1) {
- page = 1;
- }
- int begin = (page - 1) * pageSize + 1;
- int end = begin + pageSize - 1;
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
步骤七:执行分页查询SQL语句
在findByPageOracle方法中执行分页查询的代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- // 如果要查看的页数大于最大页,或者小于1,则取最后一页或第一页
- if (page > pages)
- page = pages;
- elseif (page < 1) {
- page = 1;
- }
- int begin = (page - 1) * pageSize + 1;
- int end = begin + pageSize - 1;
- stmt = con.prepareStatement(sql);
- stmt.setInt(1, begin);
- stmt.setInt(2, end);
- rs = stmt.executeQuery();
- while (rs.next()) {
- System.out.println(rs.getInt("deptno") + ","
- + rs.getString("dname") + ","
- + rs.getString("loc"));
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
步骤八:测试
在DeptDAO类的main方法中调用findByPageOracle方法,代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- // 4.findByPageOracle
- dao.findByPageOracle(2, 3);// 查看第二页,每页3条
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- // 如果要查看的页数大于最大页,或者小于1,则取最后一页或第一页
- if (page > pages)
- page = pages;
- elseif (page < 1) {
- page = 1;
- }
- int begin = (page - 1) * pageSize + 1;
- int end = begin + pageSize - 1;
- stmt = con.prepareStatement(sql);
- stmt.setInt(1, begin);
- stmt.setInt(2, end);
- rs = stmt.executeQuery();
- while (rs.next()) {
- System.out.println(rs.getInt("deptno") + ","
- + rs.getString("dname") + ","
- + rs.getString("loc"));
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
运行DeptDAO类,在控制台会输出第二页的三条数据。
步骤九:连接MySQL数据库,实现对Dept表中数据的分页查询
连接MySQL数据库,实现对Dept表中数据的分页查询,与连接Oracle是类似的。需要注意的是将db.properties文件中连接数据库的信息改为与MySQL数据库相关的,该文件内容如下:
- #jdbc.driverClassName=oracle.jdbc.OracleDriver
- #jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
- #jdbc.username=scott
- #jdbc.password=tiger
- jdbc.driverClassName=com.mysql.jdbc.Driver
- jdbc.url=jdbc:mysql://localhost:3306/tts7
- jdbc.username=root
- jdbc.password=root
- #<!-- 初始化连接 -->
- dataSource.initialSize=10
- #<!-- 最大空闲连接 -->
- dataSource.maxIdle=20
- #<!-- 最小空闲连接 -->
- dataSource.minIdle=5
- #最大连接数量
- dataSource.maxActive=50
- #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
- dataSource.maxWait=1000
在DeptDAO类中添加findByPageMySQL方法,实现连接MySQL数据库,实现对Dept表中数据的分页查询,代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- // 4.findByPageOracle
- //dao.findByPageOracle(2, 3);// 查看第二页,每页3条
- // 5.findByPageMySQL
- dao.findByPageMySQL(2, 3);// 查看第二页,每页3条
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- // 如果要查看的页数大于最大页,或者小于1,则取最后一页或第一页
- if (page > pages)
- page = pages;
- elseif (page < 1) {
- page = 1;
- }
- int begin = (page - 1) * pageSize + 1;
- int end = begin + pageSize - 1;
- stmt = con.prepareStatement(sql);
- stmt.setInt(1, begin);
- stmt.setInt(2, end);
- rs = stmt.executeQuery();
- while (rs.next()) {
- System.out.println(rs.getInt("deptno") + ","
- + rs.getString("dname") + ","
- + rs.getString("loc"));
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- /**
- *
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageMySQL(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from dept order by deptno limit ?,?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- System.out.println(total);
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- // 如果要查看的页数大于最大页,或者小于1,则取最后一页或第一页
- if (page > pages)
- page = pages;
- elseif (page < 1) {
- page = 1;
- }
- int start = (page - 1) * pageSize;
- stmt = con.prepareStatement(sql);
- stmt.setInt(1, start);
- stmt.setInt(2, pageSize);
- rs = stmt.executeQuery();
- while (rs.next()) {
- System.out.println(rs.getInt("deptno") + ","
- + rs.getString("dname") + ","
- + rs.getString("loc"));
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findAll() {
- ... ...
- }
- publicvoid add(Dept dept) {
- ... ...
- }
- publicvoid update(Dept dept) {
- ... ...
- }
- }
本案例中,DeptDAO类的完整代码如下所示:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- publicclass DeptDAO {
- publicstaticvoid main(String[] args) {
- // 1.select
- DeptDAO dao = new DeptDAO();
- //dao.findAll();
- // 2.insert
- Dept dept = new Dept(50, "developer", "Beijing");
- // dao.add(dept);
- // 3.update
- dept.setLoc("ShangHai");
- //dao.update(dept);
- // 4.findByPageOracle
- //dao.findByPageOracle(2, 3);// 查看第二页,每页3条
- // 5.findByPageMySQL
- dao.findByPageMySQL(2, 3);// 查看第二页,每页3条
- }
- /**
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageOracle(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from "
- + "(select rownum rn, deptno, dname ,loc from "
- + "(select * from dept order by deptno) )"
- + " where rn between ? and ?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- // 如果要查看的页数大于最大页,或者小于1,则取最后一页或第一页
- if (page > pages)
- page = pages;
- elseif (page < 1) {
- page = 1;
- }
- int begin = (page - 1) * pageSize + 1;
- int end = begin + pageSize - 1;
- stmt = con.prepareStatement(sql);
- stmt.setInt(1, begin);
- stmt.setInt(2, end);
- rs = stmt.executeQuery();
- while (rs.next()) {
- System.out.println(rs.getInt("deptno") + ","
- + rs.getString("dname") + ","
- + rs.getString("loc"));
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- /**
- *
- * @param page
- * 要查看第几页
- * @param pageSize
- * 每页记录数
- */
- publicvoid findByPageMySQL(int page, int pageSize) {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int total = -1;// 总记录数
- int pages = -1;// 总页数
- String sql_total = "select count(*) from dept";
- String sql = "select * from dept order by deptno limit ?,?";
- try {
- con = ConnectionSource.getConnection();
- stmt = con.prepareStatement(sql_total);
- // 获得总的记录数
- rs = stmt.executeQuery();
- if (rs.next()) {
- total = rs.getInt(1);
- }
- System.out.println(total);
- // 计算总共需要多少页
- int mod = total % pageSize;
- if (mod == 0)
- pages = total / pageSize;
- else
- pages = total / pageSize + 1;
- // 如果要查看的页数大于最大页,或者小于1,则取最后一页或第一页
- if (page > pages)
- page = pages;
- elseif (page < 1) {
- page = 1;
- }
- int start = (page - 1) * pageSize;
- stmt = con.prepareStatement(sql);
- stmt.setInt(1, start);
- stmt.setInt(2, pageSize);
- rs = stmt.executeQuery();
- while (rs.next()) {
- System.out.println(rs.getInt("deptno") + ","
- + rs.getString("dname") + "," + ","
- + rs.getString("loc"));
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源时发生异常");
- }
- }
- }
- publicvoid findAll() {
- Connection con = null;
- Statement stmt = null;
- ResultSet rs = null;
- try {
- Class.forName("oracle.jdbc.OracleDriver");
- con = DriverManager.getConnection(
- "jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
- stmt = con.createStatement();
- rs = stmt.executeQuery("select deptno,dname,loc from dept");
- while (rs.next()) {
- System.out.println(rs.getInt("deptno") + ","
- + rs.getString("dname") + "," + rs.getString("loc"));
- }
- } catch (ClassNotFoundException e) {
- System.out.println("驱动类无法找到!");
- thrownew RuntimeException(e);
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("关闭连接时发生异常");
- }
- }
- }
- publicvoid add(Dept dept) {
- Connection con = null;
- Statement stmt = null;
- int flag = -1;
- String sql = "insert into dept(deptno,dname,loc) " + "values("
- + dept.getDeptno() + ",'" + dept.getDname() + "','"
- + dept.getLoc() + "')";
- System.out.println(sql);
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- flag = stmt.executeUpdate(sql);
- if (flag > 0) {
- System.out.println("新增记录成功!");
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源发生异常");
- }
- }
- }
- publicvoid update(Dept dept) {
- Connection con = null;
- Statement stmt = null;
- int flag = -1;
- String sql = "update dept set dname = '" + dept.getDname() + "',"
- + "loc = '" + dept.getLoc() + "' where deptno = "
- + dept.getDeptno();
- try {
- con = ConnectionSource.getConnection();
- stmt = con.createStatement();
- flag = stmt.executeUpdate(sql);
- if (flag > 0) {
- System.out.println("更新记录成功!");
- }
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- thrownew RuntimeException(e);
- } finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (SQLException e) {
- System.out.println("释放资源发生异常");
- }
- }
- }
- }
db.properties文件的完整内容如下所示:
- #jdbc.driverClassName=oracle.jdbc.OracleDriver
- #jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
- #jdbc.username=scott
- #jdbc.password=tiger
- jdbc.driverClassName=com.mysql.jdbc.Driver
- jdbc.url=jdbc:mysql://localhost:3306/tts7
- jdbc.username=root
- jdbc.password=root
- #<!-- 初始化连接 -->
- dataSource.initialSize=10
- #<!-- 最大空闲连接 -->
- dataSource.maxIdle=20
- #<!-- 最小空闲连接 -->
- dataSource.minIdle=5
- #最大连接数量
- dataSource.maxActive=50
- #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
- dataSource.maxWait=1000
ConnectionSource类与之前案例一样没有变化,该类完整内容如下所示:
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.util.Properties;
- import org.apache.commons.dbcp.BasicDataSource;
- public class ConnectionSource {
- private static BasicDataSource dataSource = null;
- public ConnectionSource() {
- }
- public static void init() {
- Properties dbProps = new Properties();
- // 取配置文件可以根据实际的不同修改
- try {
- dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
- "day01/v4/db.properties"));
- } catch (IOException e) {
- e.printStackTrace();
- }
- try {
- String driveClassName = dbProps.getProperty("jdbc.driverClassName");
- String url = dbProps.getProperty("jdbc.url");
- String username = dbProps.getProperty("jdbc.username");
- String password = dbProps.getProperty("jdbc.password");
- String initialSize = dbProps.getProperty("dataSource.initialSize");
- String minIdle = dbProps.getProperty("dataSource.minIdle");
- String maxIdle = dbProps.getProperty("dataSource.maxIdle");
- String maxWait = dbProps.getProperty("dataSource.maxWait");
- String maxActive = dbProps.getProperty("dataSource.maxActive");
- dataSource = new BasicDataSource();
- dataSource.setDriverClassName(driveClassName);
- dataSource.setUrl(url);
- dataSource.setUsername(username);
- dataSource.setPassword(password);
- // 初始化连接数
- if (initialSize != null)
- dataSource.setInitialSize(Integer.parseInt(initialSize));
- // 最小空闲连接
- if (minIdle != null)
- dataSource.setMinIdle(Integer.parseInt(minIdle));
- // 最大空闲连接
- if (maxIdle != null)
- dataSource.setMaxIdle(Integer.parseInt(maxIdle));
- // 超时回收时间(以毫秒为单位)
- if (maxWait != null)
- dataSource.setMaxWait(Long.parseLong(maxWait));
- // 最大连接数
- if (maxActive != null) {
- if (!maxActive.trim().equals("0"))
- dataSource.setMaxActive(Integer.parseInt(maxActive));
- }
- } catch (Exception e) {
- e.printStackTrace();
- System.out.println("创建连接池失败!请检查设置!!!");
- }
- }
- public static synchronized Connection getConnection() throws SQLException {
- if (dataSource == null) {
- init();
- }
- Connection conn = null;
- if (dataSource != null) {
- conn = dataSource.getConnection();
- }
- return conn;
- }
- }
5 完成NetCTOSS项目中,权限管理模块的角色的DAO设计及实现
详细要求如下:
1. 查询所有角色信息。
2. 添加某个角色。
3. 修改某个角色的角色名称。
参考答案
在课上案例的基础上,完成当前案例。工程结构如图-2所示。
图-2
本案例,在课上案例实现的基础上添加了如下内容:
1. RoleInfo.java为数据库中的role_info表和Java对象的映射;
2. RoleInfoDAO.java 为一个接口,该接口中定义了三个方法,该三个方法的声明如下所示:
- package com.tarena.netctoss;
- import java.sql.SQLException;
- import java.util.List;
- import com.tarena.netctoss.entity.RoleInfo;
- publicinterface RoleInfoDAO {
- /**
- * 查询所有的角色
- * @return所有角色返回List集合
- */
- List<RoleInfo> findAll() throws SQLException;
- /**
- * 新增角色
- * @paramrole要添加角色
- * @return添加后角色,包含角色ID
- */
- RoleInfo save(RoleInfo role) throws SQLException;
- /**
- * 修改某个角色
- * @paramrole要修改的角色
- * @return返回修改后的角色
- */
- RoleInfo modify(RoleInfo role) throws SQLException;
- }
以上三个方法的作用,请参考注释部分。
3. RoleInfoDAOImpl.java 该类继承自BaseDAO,来直接获取创建连接的方法;另外,该类实现了RoleInfoDAO接口,将该接口中的方法做出实现。
4.TestRoleInfoDAO.java 该类用于测试RoleInfoDAOImpl所实现的方法的正确性。
实现此案例需要按照如下步骤进行。
步骤一:创建序列、表以及向表中插入数据
首先,创建名为role_seq的序列;然后,创建名为role_info的表并向该表中插入测试数据,SQL语句如下所示:
- create sequence role_seq;
- create table role_info(
- role_id number(4) constraint role_info_id_pk primary key,
- name varchar2(50) not null
- );
- insert into role_info(role_id,name)values(role_seq.nextval,'管理员');
- insert into role_info(role_id,name)values(role_seq.nextval,'柜台人员');
- insert into role_info(role_id,name)values(role_seq.nextval,'中层领导');
- commit;
步骤二:创建数据库中的role_info表和Java对象的映射类RoleInfo
代码如下所示:
- package com.tarena.netctoss.entity;
- public class RoleInfo {
- private int roleId;
- private String name;
- public int getRoleId() {
- return roleId;
- }
- public void setRoleId(int roleId) {
- this.roleId = roleId;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- @Override
- public String toString() {
- return "RoleInfo [name=" + name + ", roleId=" + roleId + "]";
- }
- }
步骤三:创建RoleInfoDAO接口,该接口中定义了对数据的增改查的功能
代码如下所示:
- package com.tarena.netctoss;
- import java.sql.SQLException;
- import java.util.List;
- import com.tarena.netctoss.entity.RoleInfo;
- publicinterface RoleInfoDAO {
- /**
- * 查询所有的角色
- * @return所有角色返回List集合
- */
- List<RoleInfo> findAll() throws SQLException;
- /**
- * 新增角色
- * @paramrole要添加角色
- * @return添加后角色,包含角色ID
- */
- RoleInfo save(RoleInfo role) throws SQLException;
- /**
- * 修改某个角色
- * @paramrole要修改的角色
- * @return返回修改后的角色
- */
- RoleInfo modify(RoleInfo role) throws SQLException;
- }
步骤四:创建RoleInfoDAOImpl类
创建RoleInfoDAOImpl类,该类继承自BaseDAO,来直接获取创建连接的方法;另外,该类实现了RoleInfoDAO接口,将该接口中的方法做出实现,代码如下所示:
- package com.tarena.netctoss.impl;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.List;
- import com.tarena.netctoss.BaseDAO;
- import com.tarena.netctoss.RoleInfoDAO;
- import com.tarena.netctoss.entity.RoleInfo;
- publicclass RoleInfoDAOImpl extends BaseDAO implements RoleInfoDAO {
- privatestaticfinal String FIND_ALL = "SELECT role_id,name FROM ROLE_INFO";
- privatestaticfinal String MODIFY = "UPDATE ROLE_INFO SET name = ? WHERE role_id=?";
- privatestaticfinal String INSERT = "INSERT INTO ROLE_INFO(role_id, name) "
- + " VALUES (ROLE_SEQ.NEXTVAL,?)";
- @Override
- public List<RoleInfo> findAll() throws SQLException {
- Connection conn = getConnection();
- String sql = FIND_ALL;
- PreparedStatement ps = conn.prepareStatement(sql);
- ResultSet rs = ps.executeQuery();
- RoleInfo role = null;
- List<RoleInfo> list = new ArrayList<RoleInfo>();
- while (rs.next()) {
- role = new RoleInfo();
- role.setRoleId(rs.getInt("role_id"));
- role.setName(rs.getString("name"));
- list.add(role);
- }
- return list;
- }
- @Override
- public RoleInfo save(RoleInfo role) throws SQLException {
- Connection conn = getConnection();
- String sql = INSERT;
- PreparedStatement ps = conn.prepareStatement(sql,
- new String[] { "role_id" });
- ps.setString(1, role.getName());
- ps.executeUpdate();
- ResultSet rs = ps.getGeneratedKeys();
- rs.next();
- int id = rs.getInt(1);
- role.setRoleId(id);
- return role;
- }
- @Override
- public RoleInfo modify(RoleInfo role) throws SQLException {
- Connection conn = getConnection();
- String sql = MODIFY; // 预先定义好的SQL语句
- PreparedStatement ps = conn.prepareStatement(sql);
- ps.setString(1, role.getName());
- ps.setInt(2, role.getRoleId());
- int flag = ps.executeUpdate();
- return (flag > 0) ? role : null;
- }
- }
步骤五:创建TestRoleInfoDAO类,用于测试功能是否实现
创建TestRoleInfoDAO类,该类用于测试RoleInfoDAOImpl所实现的方法的正确性,代码如下所示:
- package com.tarena.netctoss;
- import java.sql.SQLException;
- import java.util.List;
- import com.tarena.netctoss.entity.RoleInfo;
- import com.tarena.netctoss.impl.RoleInfoDAOImpl;
- public class TestRoleInfoDAO {
- public static void main(String[] args) {
- TestRoleInfoDAO test=new TestRoleInfoDAO();
- //test.testFindAll();
- //test.testSave();
- test.testModify();
- }
- public void testModify() {
- RoleInfo role=new RoleInfo();
- role.setRoleId(10);
- role.setName("common");
- RoleInfoDAO dao = new RoleInfoDAOImpl();
- try {
- role=dao.modify(role);
- System.out.println(role.toString());
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- public void testSave() {
- RoleInfo role=new RoleInfo();
- role.setName("admin");
- RoleInfoDAO dao = new RoleInfoDAOImpl();
- try {
- role=dao.save(role);
- System.out.println(role.toString());
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- public void testFindAll() {
- RoleInfoDAO dao = new RoleInfoDAOImpl();
- try {
- List<RoleInfo> list = dao.findAll();
- for(RoleInfo role : list){
- System.out.println(role.toString());
- System.out.println("---------------------------------");
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }