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();
#cold_bold // 插入数据的SQL语句
#cold_bold String sql1 = "update emp set sal =sal*1.2" + " where deptno="
#cold_bold + deptno + " and job='ANALYST'";
#cold_bold String sql2 = "update emp set sal =sal*1.3" + " where deptno="
#cold_bold + deptno + " and job='MANAGER'";
#cold_bold // 关闭自动提交
#cold_bold con.setAutoCommit(false);
#cold_bold // 执行SQL语句
#cold_bold stmt.executeUpdate(sql1);
#cold_bold stmt.executeUpdate(sql2);
#cold_bold // 提交
#cold_bold con.commit();
} catch (SQLException e) {
#cold_bold try {
#cold_bold con.rollback();
#cold_bold } catch (SQLException e1) {
#cold_bold System.out.println("回滚事务异常！");
#cold_bold throw new RuntimeException(e1);
#cold_bold }
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");
// 员工工资提升
#cold_bold 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);
#cold_bold for (int i = 0; i < 100; i++) {
#cold_bold // 插入数据的SQL语句
#cold_bold sql = "insert into dept(deptno, dname, loc) values("
#cold_bold + "dept_seq.nextval, 'name" + i + "', "
#cold_bold + new Random().nextInt(10000) + ")";
#cold_bold System.out.println(sql);
#cold_bold // 将SQL语句加入到Batch中
#cold_bold stmt.addBatch(sql);
#cold_bold }
#cold_bold // 执行批处理
#cold_bold 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[]) {
#cold_bold Batch batch = new Batch();
#cold_bold 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);
#cold_bold
#cold_bold sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)"
#cold_bold + " values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
#cold_bold stmt = con.prepareStatement(sql, new String[] { "empno" });
#cold_bold int mgr = 0;
#cold_bold for (int i = 0; i < emps.size(); i++) {
#cold_bold Emp emp = (Emp) emps.get(i);
#cold_bold stmt.setString(1, emp.getEname());
#cold_bold stmt.setString(2, emp.getJob());
#cold_bold if (i == 0) {
#cold_bold stmt.setInt(3, emp.getMgr());
#cold_bold } else {
#cold_bold stmt.setInt(3, mgr);
#cold_bold }
#cold_bold stmt.setString(4, emp.getHiredate());
#cold_bold stmt.setDouble(5, emp.getSal());
#cold_bold stmt.setDouble(6, emp.getComm());
#cold_bold stmt.setInt(7, emp.getDeptno());
#cold_bold stmt.executeUpdate();
#cold_bold if (i == 0) {
#cold_bold rs = stmt.getGeneratedKeys();
#cold_bold if (rs.next()) {
#cold_bold mgr = rs.getInt(1);
#cold_bold }
#cold_bold }
#cold_bold }
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);
#cold_bold // 将管理者放置在索引为0的位置
#cold_bold List<Emp> emps = new ArrayList<Emp>();
#cold_bold Emp emp1=new Emp();
#cold_bold emp1.setEname("tom");
#cold_bold emp1.setJob("manager");
#cold_bold emp1.setMgr(7839);
#cold_bold emp1.setHiredate("2014-05-01");
#cold_bold emp1.setSal(5000);
#cold_bold emp1.setComm(300);
#cold_bold emp1.setDeptno(30);
#cold_bold
#cold_bold Emp emp2=new Emp();
#cold_bold emp2.setEname("marry");
#cold_bold emp2.setJob("clerk");
#cold_bold emp2.setMgr(1);
#cold_bold emp2.setHiredate("2014-05-28");
#cold_bold emp2.setSal(2000);
#cold_bold emp2.setDeptno(30);
#cold_bold
#cold_bold Emp emp3=new Emp();
#cold_bold emp3.setEname("terry");
#cold_bold emp3.setJob("salesman");
#cold_bold emp3.setMgr(1);
#cold_bold emp3.setHiredate("2014-05-29");
#cold_bold emp3.setSal(2500);
#cold_bold emp3.setComm(200);
#cold_bold emp3.setDeptno(30);
#cold_bold
#cold_bold Emp emp4=new Emp();
#cold_bold emp4.setEname("jim");
#cold_bold emp4.setJob("salesman");
#cold_bold emp4.setMgr(1);
#cold_bold emp4.setHiredate("2014-05-26");
#cold_bold emp4.setSal(2500);
#cold_bold emp4.setComm(200);
#cold_bold emp4.setDeptno(30);
#cold_bold
#cold_bold emps.add(emp1);
#cold_bold emps.add(emp2);
#cold_bold emps.add(emp3);
#cold_bold emps.add(emp4);
#cold_bold
#cold_bold 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
* 每页记录数
*/
#cold_bold publicvoid findByPageOracle(int page, int pageSize) {
#cold_bold
#cold_bold }
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) {
#cold_bold String sql_total = "select count(*) from dept";
#cold_bold String sql = "select * from "
#cold_bold + "(select rownum rn, deptno, dname ,loc from "
#cold_bold + "(select * from dept order by deptno) )"
#cold_bold + " 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) {
#cold_bold Connection con = null;
#cold_bold PreparedStatement stmt = null;
#cold_bold ResultSet rs = null;
#cold_bold 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 ?";
#cold_bold try {
#cold_bold con = ConnectionSource.getConnection();
#cold_bold stmt = con.prepareStatement(sql_total);
#cold_bold
#cold_bold // 获得总的记录数
#cold_bold rs = stmt.executeQuery();
#cold_bold if (rs.next()) {
#cold_bold total = rs.getInt(1);
#cold_bold }
#cold_bold } catch (SQLException e) {
#cold_bold System.out.println("数据库访问异常!");
#cold_bold thrownew RuntimeException(e);
#cold_bold } finally {
#cold_bold try {
#cold_bold if (rs != null) {
#cold_bold rs.close();
#cold_bold }
#cold_bold if (stmt != null) {
#cold_bold stmt.close();
#cold_bold }
#cold_bold if (con != null) {
#cold_bold con.close();
#cold_bold }
#cold_bold } catch (SQLException e) {
#cold_bold System.out.println("释放资源时发生异常");
#cold_bold }
#cold_bold }
}
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;// 总记录数
#cold_bold 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);
}
#cold_bold // 计算总共需要多少页
#cold_bold int mod = total % pageSize;
#cold_bold if (mod == 0)
#cold_bold pages = total / pageSize;
#cold_bold else
#cold_bold 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;
#cold_bold // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
#cold_bold if (page > pages)
#cold_bold page = pages;
#cold_bold elseif (page < 1) {
#cold_bold page = 1;
#cold_bold }
} 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;
}
#cold_bold int begin = (page - 1) * pageSize + 1;
#cold_bold 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;
#cold_bold stmt = con.prepareStatement(sql);
#cold_bold stmt.setInt(1, begin);
#cold_bold stmt.setInt(2, end);
#cold_bold rs = stmt.executeQuery();
#cold_bold while (rs.next()) {
#cold_bold System.out.println(rs.getInt("deptno") + ","
#cold_bold + rs.getString("dname") + ","
#cold_bold + rs.getString("loc"));
#cold_bold }
} 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
#cold_bold 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条
#cold_bold // 5.findByPageMySQL
#cold_bold 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
* 每页记录数
*/
#cold_bold publicvoid findByPageMySQL(int page, int pageSize) {
#cold_bold Connection con = null;
#cold_bold PreparedStatement stmt = null;
#cold_bold ResultSet rs = null;
#cold_bold int total = -1;// 总记录数
#cold_bold int pages = -1;// 总页数
#cold_bold
#cold_bold String sql_total = "select count(*) from dept";
#cold_bold String sql = "select * from dept order by deptno limit ?,?";
#cold_bold
#cold_bold try {
#cold_bold con = ConnectionSource.getConnection();
#cold_bold stmt = con.prepareStatement(sql_total);
#cold_bold
#cold_bold // 获得总的记录数
#cold_bold rs = stmt.executeQuery();
#cold_bold if (rs.next()) {
#cold_bold total = rs.getInt(1);
#cold_bold }
#cold_bold System.out.println(total);
#cold_bold // 计算总共需要多少页
#cold_bold int mod = total % pageSize;
#cold_bold if (mod == 0)
#cold_bold pages = total / pageSize;
#cold_bold else
#cold_bold pages = total / pageSize + 1;
#cold_bold
#cold_bold // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
#cold_bold if (page > pages)
#cold_bold page = pages;
#cold_bold elseif (page < 1) {
#cold_bold page = 1;
#cold_bold }
#cold_bold int start = (page - 1) * pageSize;
#cold_bold stmt = con.prepareStatement(sql);
#cold_bold stmt.setInt(1, start);
#cold_bold stmt.setInt(2, pageSize);
#cold_bold rs = stmt.executeQuery();
#cold_bold while (rs.next()) {
#cold_bold System.out.println(rs.getInt("deptno") + ","
#cold_bold + rs.getString("dname") + ","
#cold_bold + rs.getString("loc"));
#cold_bold }
#cold_bold } catch (SQLException e) {
#cold_bold System.out.println("数据库访问异常!");
#cold_bold thrownew RuntimeException(e);
#cold_bold } finally {
#cold_bold try {
#cold_bold if (rs != null) {
#cold_bold rs.close();
#cold_bold }
#cold_bold if (stmt != null) {
#cold_bold stmt.close();
#cold_bold }
#cold_bold if (con != null) {
#cold_bold con.close();
#cold_bold }
#cold_bold } catch (SQLException e) {
#cold_bold System.out.println("释放资源时发生异常");
#cold_bold }
#cold_bold }
#cold_bold }
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();
}
}
}