JDBC高级编程 DAO

1 实现将某一个部门（Dept）的员工（Emp）工资的提升

本案例的详细要求如下：

1.如果职位（job）为“ANALYST”的员工，那么工资提升20%。

2.如果职位（job）为“MANAGER”的员工，那么工资提升30%。

3. 要求某部门下的以上两个职位的员工工资，要么工资全部提升成功，要么工资全部提升失败。

参考答案

实现此案例需要按照如下步骤进行。

步骤一：准备JDBC操作数据库的基本代码

首先，在EmpDAO类中新建updateSalByDeptno方法，方法的声明如下所示：

1. publicvoidupdateSalByDeptno(int deptno){}

该方法实现将部门编号为deptno的、职位为“ANALYST”和“MANAGER”的员工的工资进行提升。

然后，准备数据库连接的Connection对象、操作SQL语句的Statement对象并进行异常的处理，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.PreparedStatement;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
6. import java.util.ArrayList;
7. import java.util.List;
9. publicclass EmpDAO {
10.     publicstaticvoid main(String[] args) {
11.         EmpDAO dao = new EmpDAO();
12.         // dao.findSalByEname("CLARK");
13.         // dao.findSalByEname("a' OR 'b'='b");
14.         // dao.findSalByEname1("CLARK");
15.         // dao.findSalByEname1("a' OR 'b'='b");
16.     }
18.     publicvoidupdateSalByDeptno(int deptno) {
19.         Connection con = null;
20.         Statement stmt = null;
21.         try {
22.             con = ConnectionSource.getConnection();
23.             stmt = con.createStatement();
26.         } catch (SQLException e) {
27.             
28.             System.out.println("数据库访问异常!");
29.             thrownew RuntimeException(e);
30.         } finally {
31.             try {
32.                 if (stmt != null) {
33.                     stmt.close();
34.                 }
35.                 if (con != null) {
36.                     con.close();
37.                 }
38.             } catch (SQLException e) {
39.                 System.out.println("释放资源时发生异常");
40.             }
41.         }
42.     }
44.     publicvoid findSalByEname(String ename) {
45.         ... ...
46.     }
48.     publicvoid findSalByEname1(String ename) {
49.         ... ...
50.     }
51. }

步骤二：实现员工工资的提升

使用Connection的setAutoCommit方法、commit方法以及rollback方法来控制事务，以正确实现员工工资的提升，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.PreparedStatement;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
6. import java.util.ArrayList;
7. import java.util.List;
9. publicclass EmpDAO {
10.     publicstaticvoid main(String[] args) {
11.         EmpDAO dao = new EmpDAO();
12.         // dao.findSalByEname("CLARK");
13.         // dao.findSalByEname("a' OR 'b'='b");
14.         // dao.findSalByEname1("CLARK");
15.         // dao.findSalByEname1("a' OR 'b'='b");
16.     }
18.     publicvoidupdateSalByDeptno(int deptno) {
19.         Connection con = null;
20.         Statement stmt = null;
21.         try {
22.             con = ConnectionSource.getConnection();
23.             stmt = con.createStatement();
25.             // 插入数据的SQL语句
26.             String sql1 = "update emp set sal =sal*1.2" + " where deptno="
27.                     + deptno + " and job='ANALYST'";
28.             String sql2 = "update emp set sal =sal*1.3" + " where deptno="
29.                     + deptno + " and job='MANAGER'";
30.             // 关闭自动提交
31.             con.setAutoCommit(false);
32.             // 执行SQL语句
33.             stmt.executeUpdate(sql1);
34.             stmt.executeUpdate(sql2);
35.             // 提交
36.             con.commit();
37.         } catch (SQLException e) {
38.             try {
39.                 con.rollback();
40.             } catch (SQLException e1) {
41.                 System.out.println("回滚事务异常！");
42.                 throw new RuntimeException(e1);
43.             }
44.             System.out.println("数据库访问异常!");
45.             thrownew RuntimeException(e);
46.         } finally {
47.             try {
48.                 if (stmt != null) {
49.                     stmt.close();
50.                 }
51.                 if (con != null) {
52.                     con.close();
53.                 }
54.             } catch (SQLException e) {
55.                 System.out.println("释放资源时发生异常");
56.             }
57.         }
58.     }
60.     publicvoid findSalByEname(String ename) {
61.         ... ...
62.     }
64.     publicvoid findSalByEname1(String ename) {
65.         ... ...
66.     }
67. }

步骤三：测试

在EmpDAO类的main方法中，调用updateSalByDeptno方法，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.PreparedStatement;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
6. import java.util.ArrayList;
7. import java.util.List;
9. publicclass EmpDAO {
10.     publicstaticvoid main(String[] args) {
11.         EmpDAO dao = new EmpDAO();
12.         // dao.findSalByEname("CLARK");
13.         // dao.findSalByEname("a' OR 'b'='b");
14.         // dao.findSalByEname1("CLARK");
15.         // dao.findSalByEname1("a' OR 'b'='b");
16.         // 员工工资提升
17.          dao.updateSalByDeptno(20);
19.     }
21.     publicvoidupdateSalByDeptno(int deptno) {
22.         Connection con = null;
23.         Statement stmt = null;
24.         try {
25.             con = ConnectionSource.getConnection();
26.             stmt = con.createStatement();
28.             // 插入数据的SQL语句
29.             String sql1 = "update emp set sal =sal*1.2" + " where deptno="
30.                     + deptno + " and job='ANALYST'";
31.             String sql2 = "update emp set sal =sal*1.3" + " where deptno="
32.                     + deptno + " and job='MANAGER'";
33.             // 关闭自动提交
34.             con.setAutoCommit(false);
35.             // 执行SQL语句
36.             stmt.executeUpdate(sql1);
37.             stmt.executeUpdate(sql2);
38.             // 提交
39.             con.commit();
40.         } catch (SQLException e) {
41.             try {
42.                 con.rollback();
43.             } catch (SQLException e1) {
44.                 System.out.println("回滚事务异常！");
45.                 thrownew RuntimeException(e1);
46.             }
47.             System.out.println("数据库访问异常!");
48.             thrownew RuntimeException(e);
49.         } finally {
50.             try {
51.                 if (stmt != null) {
52.                     stmt.close();
53.                 }
54.                 if (con != null) {
55.                     con.close();
56.                 }
57.             } catch (SQLException e) {
58.                 System.out.println("释放资源时发生异常");
59.             }
60.         }
61.     }
63.     publicvoid findSalByEname(String ename) {
64.         ... ...
65.     }
67.     publicvoid findSalByEname1(String ename) {
68.         ... ...
69.     }
70. }

运行EmpDAO类，然后查看Oracle数据库中的emp表，会发现部门编号为20的、职位为“ANALYST”和“MANAGER”的员工的工资进行了提升。

本案例中，类EmpDAO的完整代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.PreparedStatement;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
6. import java.util.ArrayList;
7. import java.util.List;
9. publicclass EmpDAO {
10.     publicstaticvoid main(String[] args) {
11.         EmpDAO dao = new EmpDAO();
12.         // dao.findSalByEname("CLARK");
13.         // dao.findSalByEname("a' OR 'b'='b");
14.         // dao.findSalByEname1("CLARK");
15.         // dao.findSalByEname1("a' OR 'b'='b");
16.         // 事务管理
17.         // dao.updateSalByDeptno(20);
19.     }
20.     publicvoid updateSalByDeptno(int deptno) {
21.         Connection con = null;
22.         Statement stmt = null;
23.         try {
24.             con = ConnectionSource.getConnection();
25.             stmt = con.createStatement();
27.             // 插入数据的SQL语句
28.             String sql1 = "update emp set sal =sal*1.2" + " where deptno="
29.                     + deptno + " and job='ANALYST'";
30.             String sql2 = "update emp set sal =sal*1.3" + " where deptno="
31.                     + deptno + " and job='MANAGER'";
32.             // 关闭自动提交
33.             con.setAutoCommit(false);
34.             // 执行SQL语句
35.             stmt.executeUpdate(sql1);
36.             stmt.executeUpdate(sql2);
37.             // 提交
38.             con.commit();
39.         } catch (SQLException e) {
40.             try {
41.                 con.rollback();
42.             } catch (SQLException e1) {
43.                 System.out.println("回滚事务异常！");
44.                 thrownew RuntimeException(e1);
45.             }
46.             System.out.println("数据库访问异常!");
47.             thrownew RuntimeException(e);
48.         } finally {
49.             try {
50.                 if (stmt != null) {
51.                     stmt.close();
52.                 }
53.                 if (con != null) {
54.                     con.close();
55.                 }
56.             } catch (SQLException e) {
57.                 System.out.println("释放资源时发生异常");
58.             }
59.         }
60.     }
62.     publicvoid findSalByEname(String ename) {
63.         Connection con = null;
64.         PreparedStatement stmt = null;
65.         ResultSet rs = null;
66.         String sql = null;
68.         try {
69.             sql = "select sal from emp where ename = ?";
70.             con = ConnectionSource.getConnection();
71.             stmt = con.prepareStatement(sql);
72.             stmt.setString(1, ename);
73.             rs = stmt.executeQuery();
75.             while (rs.next()) {
76.                 double sal = rs.getDouble("sal");
77.                 System.out.println(sal);
78.             }
79.         } catch (SQLException e) {
80.             System.out.println("数据库访问异常!");
81.             thrownew RuntimeException(e);
82.         } finally {
83.             try {
84.                 if (rs != null) {
85.                     rs.close();
86.                 }
87.                 if (stmt != null) {
88.                     stmt.close();
89.                 }
90.                 if (con != null) {
91.                     con.close();
92.                 }
93.             } catch (SQLException e) {
94.                 System.out.println("释放资源发生异常");
95.             }
96.         }
97.     }
99.     publicvoid findSalByEname1(String ename) {
100.         Connection con = null;
101.         Statement stmt = null;
102.         ResultSet rs = null;
103.         String sql = null;
105.         try {
106.             sql = "select sal from emp where ename = '" + ename + "'";
107.             System.out.println(sql);
108.             con = ConnectionSource.getConnection();
109.             stmt = con.createStatement();
110.             rs = stmt.executeQuery(sql);
112.             while (rs.next()) {
113.                 double sal = rs.getDouble("sal");
114.                 System.out.println(sal);
115.             }
116.         } catch (SQLException e) {
117.             System.out.println("数据库访问异常!");
118.             thrownew RuntimeException(e);
119.         } finally {
120.             try {
121.                 if (rs != null) {
122.                     rs.close();
123.                 }
124.                 if (stmt != null) {
125.                     stmt.close();
126.                 }
127.                 if (con != null) {
128.                     con.close();
129.                 }
130.             } catch (SQLException e) {
131.                 System.out.println("释放资源发生异常");
132.             }
133.         }
134.     }
135. }

隐藏

db.properties文件的内容与之前案例一样没有变化，该文件完整内容如下所示：

 

1. jdbc.driverClassName=oracle.jdbc.OracleDriver
2. jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
3. jdbc.username=scott
4. jdbc.password=tiger
6. #<!-- 初始化连接 -->
7. dataSource.initialSize=10
8. #<!-- 最大空闲连接 -->
9. dataSource.maxIdle=20
10. #<!-- 最小空闲连接 -->
11. dataSource.minIdle=5
12. #最大连接数量
13. dataSource.maxActive=50
14. #<!-- 超时等待时间以毫秒为单位 (6000毫秒/1000等于60秒 )-->
15. dataSource.maxWait=1000

隐藏

ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：

 

1. import java.io.IOException;
2. import java.sql.Connection;
3. import java.sql.SQLException;
4. import java.util.Properties;
6. import org.apache.commons.dbcp.BasicDataSource;
8. public class ConnectionSource {
9.     private static BasicDataSource dataSource = null;
11.     public ConnectionSource() {
12.     }
13.     public static void init() {
15.         Properties dbProps = new Properties();
16.         // 取配置文件可以根据实际的不同修改
17.         try {
18.             dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
19.                     "day01/v4/db.properties"));
20.         } catch (IOException e) {
21.             e.printStackTrace();
22.         }
24.         try {
25.             String driveClassName = dbProps.getProperty("jdbc.driverClassName");
26.             String url = dbProps.getProperty("jdbc.url");
27.             String username = dbProps.getProperty("jdbc.username");
28.             String password = dbProps.getProperty("jdbc.password");
30.             String initialSize = dbProps.getProperty("dataSource.initialSize");
31.             String minIdle = dbProps.getProperty("dataSource.minIdle");
32.             String maxIdle = dbProps.getProperty("dataSource.maxIdle");
33.             String maxWait = dbProps.getProperty("dataSource.maxWait");
34.             String maxActive = dbProps.getProperty("dataSource.maxActive");
36.             dataSource = new BasicDataSource();
37.             dataSource.setDriverClassName(driveClassName);
38.             dataSource.setUrl(url);
39.             dataSource.setUsername(username);
40.             dataSource.setPassword(password);
42.             // 初始化连接数
43.             if (initialSize != null)
44.                 dataSource.setInitialSize(Integer.parseInt(initialSize));
46.             // 最小空闲连接
47.             if (minIdle != null)
48.                 dataSource.setMinIdle(Integer.parseInt(minIdle));
50.             // 最大空闲连接
51.             if (maxIdle != null)
52.                 dataSource.setMaxIdle(Integer.parseInt(maxIdle));
54.             // 超时回收时间(以毫秒为单位)
55.             if (maxWait != null)
56.                 dataSource.setMaxWait(Long.parseLong(maxWait));
58.             // 最大连接数
59.             if (maxActive != null) {
60.                 if (!maxActive.trim().equals("0"))
61.                     dataSource.setMaxActive(Integer.parseInt(maxActive));
62.             }
63.         } catch (Exception e) {
64.             e.printStackTrace();
65.             System.out.println("创建连接池失败!请检查设置!!!");
66.         }
67.     }
69.     public static synchronized Connection getConnection() throws SQLException {
70.         if (dataSource == null) {
71.             init();
72.         }
73.         Connection conn = null;
74.         if (dataSource != null) {
75.             conn = dataSource.getConnection();
76.         }
77.         return conn;
78.     }
79. }

隐藏

2 批量插入Dept数据

向Dept表中批量插入100条数据，需要插入数据的列为deptno、dname，这两列的数据要求如下：

1. deptno列的数据通过序列dept_seq自动生成；

2. dname列的数据为字符串，格式为：“name”+循环次数i。

参考答案

实现此案例需要按照如下步骤进行。

步骤一：在Oracle数据库中创建序列dept_seq

在Oracle数据库中创建名为dept_seq的序列，该序列的起始值为1、步进为1，SQL语句如下所示：

 

1. create sequence dept_seq start with 1 increment by 1;

步骤二：准备JDBC操作数据库的基本代码

首先，新建类Batch，在该类中新建batchAdd方法；然后，准备数据库连接Connection对象、操作SQL语句的Statement对象以及设置事务管理；最后进行异常的处理，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.SQLException;
3. import java.sql.Statement;
4. import java.util.Random;
7. publicclass Batch {
9.     publicvoid batchAdd() {
10.         Connection con = null;
11.         Statement stmt = null;
12.         String sql = null;
13.         try {
14.             con = ConnectionSource.getConnection();
15.             stmt = con.createStatement();
16.             // 关闭自动提交
17.             con.setAutoCommit(false);
19.             // 提交
20.             con.commit();
21.         } catch (SQLException e) {
22.             System.out.println("数据库访问异常!");
23.             thrownew RuntimeException(e);
24.         } finally {
25.             try {
26.                 if (stmt != null) {
27.                     stmt.close();
28.                 }
29.                 if (con != null) {
30.                     con.close();
31.                 }
32.             } catch (SQLException e) {
33.                 System.out.println("释放资源时发生异常");
34.             }
35.         }
36.     }
37.     publicstaticvoid main(String args[]) {
38.     }
39. }

步骤三：批量向Dept表中插入数据

使用Statement的addBatch方法和executeBatch方法，批量向Dept表中插入数据，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.SQLException;
3. import java.sql.Statement;
4. import java.util.Random;
7. publicclass Batch {
9.     publicvoid batchAdd() {
10.         Connection con = null;
11.         Statement stmt = null;
12.         String sql = null;
13.         try {
14.             con = ConnectionSource.getConnection();
15.             stmt = con.createStatement();
16.             // 关闭自动提交
17.             con.setAutoCommit(false);
19.             for (int i = 0; i < 100; i++) {
20.                 // 插入数据的SQL语句
21.                 sql = "insert into dept(deptno, dname, loc) values("
22.                         + "dept_seq.nextval, 'name" + i + "', "
23.                         + new Random().nextInt(10000) + ")";
24.                 System.out.println(sql);
25.                 // 将SQL语句加入到Batch中
26.                 stmt.addBatch(sql);
27.             }
28.             // 执行批处理
29.             stmt.executeBatch();
30.             // 提交
31.             con.commit();
32.         } catch (SQLException e) {
33.             System.out.println("数据库访问异常!");
34.             thrownew RuntimeException(e);
35.         } finally {
36.             try {
37.                 if (stmt != null) {
38.                     stmt.close();
39.                 }
40.                 if (con != null) {
41.                     con.close();
42.                 }
43.             } catch (SQLException e) {
44.                 System.out.println("释放资源时发生异常");
45.             }
46.         }
47.     }
48.     publicstaticvoid main(String args[]) {
49.     }
50. }

步骤四：测试是否批量插入数据成功

在Batch类的main方法中，调用batchAdd方法，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.SQLException;
3. import java.sql.Statement;
4. import java.util.Random;
7. publicclass Batch {
9.     publicvoid batchAdd() {
10.         Connection con = null;
11.         Statement stmt = null;
12.         String sql = null;
13.         try {
14.             con = ConnectionSource.getConnection();
15.             stmt = con.createStatement();
16.             // 关闭自动提交
17.             con.setAutoCommit(false);
19.             for (int i = 0; i < 100; i++) {
20.                 // 插入数据的SQL语句
21.                 sql = "insert into dept(deptno, dname, loc) values("
22.                         + "dept_seq.nextval, 'name" + i + "', "
23.                         + new Random().nextInt(10000) + ")";
24.                 System.out.println(sql);
25.                 // 将SQL语句加入到Batch中
26.                 stmt.addBatch(sql);
27.             }
28.             // 执行批处理
29.             stmt.executeBatch();
30.             // 提交
31.             con.commit();
32.         } catch (SQLException e) {
33.             System.out.println("数据库访问异常!");
34.             thrownew RuntimeException(e);
35.         } finally {
36.             try {
37.                 if (stmt != null) {
38.                     stmt.close();
39.                 }
40.                 if (con != null) {
41.                     con.close();
42.                 }
43.             } catch (SQLException e) {
44.                 System.out.println("释放资源时发生异常");
45.             }
46.         }
47.     }
48.     publicstaticvoid main(String args[]) {
49.         Batch batch = new Batch();
50.         batch.batchAdd();
51.     }
52. }

运行Batch类，然后去查看Oracle数据库中的Dept表，会发现批量向该表中插入了100条记录。

本案例中，Batch类的完整代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.SQLException;
3. import java.sql.Statement;
4. import java.util.Random;
7. publicclass Batch {
9.     publicvoid batchAdd() {
10.         Connection con = null;
11.         Statement stmt = null;
12.         String sql = null;
13.         try {
14.             con = ConnectionSource.getConnection();
15.             stmt = con.createStatement();
16.             // 关闭自动提交
17.             con.setAutoCommit(false);
19.             for (int i = 0; i < 100; i++) {
20.                 // 插入数据的SQL语句
21.                 sql = "insert into dept(deptno, dname, loc) values("
22.                         + "dept_seq.nextval, 'name" + i + "', "
23.                         + new Random().nextInt(10000) + ")";
24.                 System.out.println(sql);
25.                 // 将SQL语句加入到Batch中
26.                 stmt.addBatch(sql);
27.             }
28.             // 执行批处理
29.             stmt.executeBatch();
30.             // 提交
31.             con.commit();
32.         } catch (SQLException e) {
33.             System.out.println("数据库访问异常!");
34.             thrownew RuntimeException(e);
35.         } finally {
36.             try {
37.                 if (stmt != null) {
38.                     stmt.close();
39.                 }
40.                 if (con != null) {
41.                     con.close();
42.                 }
43.             } catch (SQLException e) {
44.                 System.out.println("释放资源时发生异常");
45.             }
46.         }
47.     }
48.     publicstaticvoid main(String args[]) {
49.         Batch batch = new Batch();
50.         batch.batchAdd();
51.     }
52. }

隐藏

db.properties文件的内容与之前案例一样没有变化，该文件完整内容如下所示：

 

1. jdbc.driverClassName=oracle.jdbc.OracleDriver
2. jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
3. jdbc.username=scott
4. jdbc.password=tiger
6. #<!-- 初始化连接 -->
7. dataSource.initialSize=10
8. #<!-- 最大空闲连接 -->
9. dataSource.maxIdle=20
10. #<!-- 最小空闲连接 -->
11. dataSource.minIdle=5
12. #最大连接数量
13. dataSource.maxActive=50
14. #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
15. dataSource.maxWait=1000

隐藏

ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：

 

1. import java.io.IOException;
2. import java.sql.Connection;
3. import java.sql.SQLException;
4. import java.util.Properties;
6. import org.apache.commons.dbcp.BasicDataSource;
8. public class ConnectionSource {
9.     private static BasicDataSource dataSource = null;
11.     public ConnectionSource() {
12.     }
13.     public static void init() {
15.         Properties dbProps = new Properties();
16.         // 取配置文件可以根据实际的不同修改
17.         try {
18.             dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
19.                     "day01/v4/db.properties"));
20.         } catch (IOException e) {
21.             e.printStackTrace();
22.         }
24.         try {
25.             String driveClassName = dbProps.getProperty("jdbc.driverClassName");
26.             String url = dbProps.getProperty("jdbc.url");
27.             String username = dbProps.getProperty("jdbc.username");
28.             String password = dbProps.getProperty("jdbc.password");
30.             String initialSize = dbProps.getProperty("dataSource.initialSize");
31.             String minIdle = dbProps.getProperty("dataSource.minIdle");
32.             String maxIdle = dbProps.getProperty("dataSource.maxIdle");
33.             String maxWait = dbProps.getProperty("dataSource.maxWait");
34.             String maxActive = dbProps.getProperty("dataSource.maxActive");
36.             dataSource = new BasicDataSource();
37.             dataSource.setDriverClassName(driveClassName);
38.             dataSource.setUrl(url);
39.             dataSource.setUsername(username);
40.             dataSource.setPassword(password);
42.             // 初始化连接数
43.             if (initialSize != null)
44.                 dataSource.setInitialSize(Integer.parseInt(initialSize));
46.             // 最小空闲连接
47.             if (minIdle != null)
48.                 dataSource.setMinIdle(Integer.parseInt(minIdle));
50.             // 最大空闲连接
51.             if (maxIdle != null)
52.                 dataSource.setMaxIdle(Integer.parseInt(maxIdle));
54.             // 超时回收时间(以毫秒为单位)
55.             if (maxWait != null)
56.                 dataSource.setMaxWait(Long.parseLong(maxWait));
58.             // 最大连接数
59.             if (maxActive != null) {
60.                 if (!maxActive.trim().equals("0"))
61.                     dataSource.setMaxActive(Integer.parseInt(maxActive));
62.             }
63.         } catch (Exception e) {
64.             e.printStackTrace();
65.             System.out.println("创建连接池失败!请检查设置!!!");
66.         }
67.     }
69.     public static synchronized Connection getConnection() throws SQLException {
70.         if (dataSource == null) {
71.             init();
72.         }
73.         Connection conn = null;
74.         if (dataSource != null) {
75.             conn = dataSource.getConnection();
76.         }
77.         return conn;
78.     }
79. }

隐藏

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语句如下所示：

 

1. create sequence emp_seq start with 1 increment by 1;

步骤二：创建Emp类

Emp类为实体类和数据表emp之间的映射，该类的代码如下所示：

 

1. public class Emp {
2.     private int empNo;
3.     private String ename;
4.     private String job;
5.     private int mgr;
6.     private String hiredate;
7.     private double sal;
8.     private double comm;
9.     private int deptno;
10.     
11.     
12.     
13.     public Emp() {
14.         super();
15.     }
16.     public Emp(int empNo, String ename, String job, int mgr, String hiredate,
17.             double sal, double comm, int deptno) {
18.         super();
19.         this.empNo = empNo;
20.         this.ename = ename;
21.         this.job = job;
22.         this.mgr = mgr;
23.         this.hiredate = hiredate;
24.         this.sal = sal;
25.         this.comm = comm;
26.         this.deptno = deptno;
27.     }
28.     public int getEmpNo() {
29.         return empNo;
30.     }
31.     public void setEmpNo(int empNo) {
32.         this.empNo = empNo;
33.     }
34.     public String getEname() {
35.         return ename;
36.     }
37.     public void setEname(String ename) {
38.         this.ename = ename;
39.     }
40.     public String getJob() {
41.         return job;
42.     }
43.     public void setJob(String job) {
44.         this.job = job;
45.     }
46.     public int getMgr() {
47.         return mgr;
48.     }
49.     public void setMgr(int mgr) {
50.         this.mgr = mgr;
51.     }
52.     public String getHiredate() {
53.         return hiredate;
54.     }
55.     public void setHiredate(String hiredate) {
56.         this.hiredate = hiredate;
57.     }
58.     public double getSal() {
59.         return sal;
60.     }
61.     public void setSal(double sal) {
62.         this.sal = sal;
63.     }
64.     public double getComm() {
65.         return comm;
66.     }
67.     public void setComm(double comm) {
68.         this.comm = comm;
69.     }
70.     public int getDeptno() {
71.         return deptno;
72.     }
73.     public void setDeptno(int deptno) {
74.         this.deptno = deptno;
75.     }
76. }

步骤三：准备JDBC操作数据库的基本代码

首先，在EmpDAO类中新建addTeam方法，该方法的声明如下：

1.     publicvoid addTeam(List<Emp> emps)

该方法用于实现向Emp表插入一个团队的数据。其中参数emps表示该团队的所有员工，在集合emps中的索引为0的位置存储管理者，索引为1、2、3的位置存储该管理者下属的三名员工。

然后，准备数据库连接Connection对象、操作SQL语句的PreparedStatement对象以及设置事务管理；最后进行异常的处理，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.PreparedStatement;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
6. import java.util.ArrayList;
7. import java.util.List;
9. publicclass EmpDAO {
10.     publicstaticvoid main(String[] args) {
11.         EmpDAO dao = new EmpDAO();
12.         // dao.findSalByEname("CLARK");
13.         // dao.findSalByEname("a' OR 'b'='b");
14.         // dao.findSalByEname1("CLARK");
15.         // dao.findSalByEname1("a' OR 'b'='b");
16.         // 事务管理
17.         // dao.updateSalByDeptno(20);
19.         
20.     }
22.     publicvoid addTeam(List<Emp> emps) {
23.         Connection con = null;
24.         PreparedStatement stmt = null;
25.         ResultSet rs = null;
26.         String sql = null;
28.         try {
29.             con = ConnectionSource.getConnection();
30.             // 关闭自动提交
31.             con.setAutoCommit(false);
33.             
35.             con.commit();
37.         } catch (SQLException e) {
38.             System.out.println("数据库访问异常!");
39.             thrownew RuntimeException(e);
40.         } finally {
41.             try {
42.                 if (rs != null) {
43.                     rs.close();
44.                 }
45.                 if (stmt != null) {
46.                     stmt.close();
47.                 }
48.                 if (con != null) {
49.                     con.close();
50.                 }
51.             } catch (SQLException e) {
52.                 System.out.println("释放资源时发生异常");
53.             }
54.         }
55.     }
57.     publicvoid updateSalByDeptno(int deptno) {
58.         ... ...
59.     }
61.     publicvoid findSalByEname(String ename) {
62.         ... ...
63.     }
65.     publicvoid findSalByEname1(String ename) {
66.         ... ...
67.     }
68. }

步骤四：实现向Emp表中插入一个团队

向Emp表中插入数据，并使用PreparedStatement的getGeneratedKeys方法获得刚刚生成的主键，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.PreparedStatement;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
6. import java.util.ArrayList;
7. import java.util.List;
9. publicclass EmpDAO {
10.     publicstaticvoid main(String[] args) {
11.         EmpDAO dao = new EmpDAO();
12.         // dao.findSalByEname("CLARK");
13.         // dao.findSalByEname("a' OR 'b'='b");
14.         // dao.findSalByEname1("CLARK");
15.         // dao.findSalByEname1("a' OR 'b'='b");
16.         // 事务管理
17.         // dao.updateSalByDeptno(20);
19.     
21.     }
23.     publicvoid addTeam(List<Emp> emps) {
24.         Connection con = null;
25.         PreparedStatement stmt = null;
26.         ResultSet rs = null;
27.         String sql = null;
29.         try {
30.             con = ConnectionSource.getConnection();
31.             // 关闭自动提交
32.             con.setAutoCommit(false);
34.         
35.             sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)"
36.                     + " values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
37.             stmt = con.prepareStatement(sql, new String[] { "empno" });
38.             int mgr = 0;
39.             for (int i = 0; i < emps.size(); i++) {
40.                 Emp emp = (Emp) emps.get(i);
41.                 stmt.setString(1, emp.getEname());
42.                 stmt.setString(2, emp.getJob());
43.                 if (i == 0) {
44.                     stmt.setInt(3, emp.getMgr());
45.                 } else {
46.                     stmt.setInt(3, mgr);
47.                 }
48.                 stmt.setString(4, emp.getHiredate());
49.                 stmt.setDouble(5, emp.getSal());
50.                 stmt.setDouble(6, emp.getComm());
51.                 stmt.setInt(7, emp.getDeptno());
52.                 stmt.executeUpdate();
53.                 if (i == 0) {
54.                     rs = stmt.getGeneratedKeys();
55.                     if (rs.next()) {
56.                         mgr = rs.getInt(1);
57.                     }
58.                 }
59.             }
61.             con.commit();
63.         } catch (SQLException e) {
64.             System.out.println("数据库访问异常!");
65.             thrownew RuntimeException(e);
66.         } finally {
67.             try {
68.                 if (rs != null) {
69.                     rs.close();
70.                 }
71.                 if (stmt != null) {
72.                     stmt.close();
73.                 }
74.                 if (con != null) {
75.                     con.close();
76.                 }
77.             } catch (SQLException e) {
78.                 System.out.println("释放资源时发生异常");
79.             }
80.         }
81.     }
83.     publicvoid updateSalByDeptno(int deptno) {
84.         ... ...
85.     }
87.     publicvoid findSalByEname(String ename) {
88.         ... ...
89.     }
91.     publicvoid findSalByEname1(String ename) {
92.         ... ...
93.     }
94. }

步骤五：测试

按照图-1所示的数据构造四个Emp对象，并将这四个对象按照步骤三所描述的顺序存储到List集合中；然后将List集合对象作为参数传递给addTeam方法，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.PreparedStatement;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
6. import java.util.ArrayList;
7. import java.util.List;
9. publicclass EmpDAO {
10.     publicstaticvoid main(String[] args) {
11.         EmpDAO dao = new EmpDAO();
12.         // dao.findSalByEname("CLARK");
13.         // dao.findSalByEname("a' OR 'b'='b");
14.         // dao.findSalByEname1("CLARK");
15.         // dao.findSalByEname1("a' OR 'b'='b");
16.         // 事务管理
17.         // dao.updateSalByDeptno(20);
19.         // 将管理者放置在索引为0的位置
20.         List<Emp> emps = new ArrayList<Emp>();
21.         Emp emp1=new Emp();
22.         emp1.setEname("tom");
23.         emp1.setJob("manager");
24.         emp1.setMgr(7839);
25.         emp1.setHiredate("2014-05-01");
26.         emp1.setSal(5000);
27.         emp1.setComm(300);
28.         emp1.setDeptno(30);
29.         
30.         Emp emp2=new Emp();
31.         emp2.setEname("marry");
32.         emp2.setJob("clerk");
33.         emp2.setMgr(1);
34.         emp2.setHiredate("2014-05-28");
35.         emp2.setSal(2000);
36.         emp2.setDeptno(30);
37.         
38.         Emp emp3=new Emp();
39.         emp3.setEname("terry");
40.         emp3.setJob("salesman");
41.         emp3.setMgr(1);
42.         emp3.setHiredate("2014-05-29");
43.         emp3.setSal(2500);
44.         emp3.setComm(200);
45.         emp3.setDeptno(30);
46.         
47.         Emp emp4=new Emp();
48.         emp4.setEname("jim");
49.         emp4.setJob("salesman");
50.         emp4.setMgr(1);
51.         emp4.setHiredate("2014-05-26");
52.         emp4.setSal(2500);
53.         emp4.setComm(200);
54.         emp4.setDeptno(30);
55.     
56.      emps.add(emp1);
57.      emps.add(emp2);
58.      emps.add(emp3);
59.      emps.add(emp4);
60.     
61.         dao.addTeam(emps);
62.     }
64.     publicvoid addTeam(List<Emp> emps) {
65.         Connection con = null;
66.         PreparedStatement stmt = null;
67.         ResultSet rs = null;
68.         String sql = null;
70.         try {
71.             con = ConnectionSource.getConnection();
72.             // 关闭自动提交
73.             con.setAutoCommit(false);
75.             // 插入主表
76.             sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)"
77.                     + " values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
78.             stmt = con.prepareStatement(sql, new String[] { "empno" });
79.             int mgr = 0;
80.             for (int i = 0; i < emps.size(); i++) {
81.                 Emp emp = (Emp) emps.get(i);
82.                 stmt.setString(1, emp.getEname());
83.                 stmt.setString(2, emp.getJob());
84.                 if (i == 0) {
85.                     stmt.setInt(3, emp.getMgr());
86.                 } else {
87.                     stmt.setInt(3, mgr);
88.                 }
89.                 stmt.setString(4, emp.getHiredate());
90.                 stmt.setDouble(5, emp.getSal());
91.                 stmt.setDouble(6, emp.getComm());
92.                 stmt.setInt(7, emp.getDeptno());
93.                 stmt.executeUpdate();
94.                 if (i == 0) {
95.                     rs = stmt.getGeneratedKeys();
96.                     if (rs.next()) {
97.                         mgr = rs.getInt(1);
98.                     }
99.                 }
100.             }
101.             con.commit();
103.         } catch (SQLException e) {
104.             System.out.println("数据库访问异常!");
105.             thrownew RuntimeException(e);
106.         } finally {
107.             try {
108.                 if (rs != null) {
109.                     rs.close();
110.                 }
111.                 if (stmt != null) {
112.                     stmt.close();
113.                 }
114.                 if (con != null) {
115.                     con.close();
116.                 }
117.             } catch (SQLException e) {
118.                 System.out.println("释放资源时发生异常");
119.             }
120.         }
121.     }
123.     publicvoid updateSalByDeptno(int deptno) {
124.         ... ...
125.     }
127.     publicvoid findSalByEname(String ename) {
128.         ... ...
129.     }
131.     publicvoid findSalByEname1(String ename) {
132.         ... ...
133.     }
134. }

运行上述代码，向Emp表插入了四条记录。其中一条记录为管理者信息，如果管理者的员工ID为1，那个其余三条员工记录的管理者ID为1。

本案例中，Emp类的完整代码如下：

 

1. public class Emp {
2.     private int empNo;
3.     private String ename;
4.     private String job;
5.     private int mgr;
6.     private String hiredate;
7.     private double sal;
8.     private double comm;
9.     private int deptno;
10.     
11.     
12.     
13.     public Emp() {
14.         super();
15.     }
16.     public Emp(int empNo, String ename, String job, int mgr, String hiredate,
17.             double sal, double comm, int deptno) {
18.         super();
19.         this.empNo = empNo;
20.         this.ename = ename;
21.         this.job = job;
22.         this.mgr = mgr;
23.         this.hiredate = hiredate;
24.         this.sal = sal;
25.         this.comm = comm;
26.         this.deptno = deptno;
27.     }
28.     public int getEmpNo() {
29.         return empNo;
30.     }
31.     public void setEmpNo(int empNo) {
32.         this.empNo = empNo;
33.     }
34.     public String getEname() {
35.         return ename;
36.     }
37.     public void setEname(String ename) {
38.         this.ename = ename;
39.     }
40.     public String getJob() {
41.         return job;
42.     }
43.     public void setJob(String job) {
44.         this.job = job;
45.     }
46.     public int getMgr() {
47.         return mgr;
48.     }
49.     public void setMgr(int mgr) {
50.         this.mgr = mgr;
51.     }
52.     public String getHiredate() {
53.         return hiredate;
54.     }
55.     public void setHiredate(String hiredate) {
56.         this.hiredate = hiredate;
57.     }
58.     public double getSal() {
59.         return sal;
60.     }
61.     public void setSal(double sal) {
62.         this.sal = sal;
63.     }
64.     public double getComm() {
65.         return comm;
66.     }
67.     public void setComm(double comm) {
68.         this.comm = comm;
69.     }
70.     public int getDeptno() {
71.         return deptno;
72.     }
73.     public void setDeptno(int deptno) {
74.         this.deptno = deptno;
75.     }
76. }

隐藏

EmpDAO类的完整代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.PreparedStatement;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
6. import java.util.ArrayList;
7. import java.util.List;
9. publicclass EmpDAO {
10.     publicstaticvoid main(String[] args) {
11.         EmpDAO dao = new EmpDAO();
12.         // dao.findSalByEname("CLARK");
13.         // dao.findSalByEname("a' OR 'b'='b");
14.         // dao.findSalByEname1("CLARK");
15.         // dao.findSalByEname1("a' OR 'b'='b");
16.         // 事务管理
17.         // dao.updateSalByDeptno(20);
19.         // 将管理者放置在emps(0)位置
20.         List<Emp> emps = new ArrayList<Emp>();
21.         Emp emp1=new Emp();
22.         emp1.setEname("tom");
23.         emp1.setJob("manager");
24.         emp1.setMgr(7839);
25.         emp1.setHiredate("2014-05-01");
26.         emp1.setSal(5000);
27.         emp1.setComm(300);
28.         emp1.setDeptno(30);
29.         
30.         Emp emp2=new Emp();
31.         emp2.setEname("marry");
32.         emp2.setJob("clerk");
33.         emp2.setMgr(1);
34.         emp2.setHiredate("2014-05-28");
35.         emp2.setSal(2000);
36.         emp2.setDeptno(30);
37.         
38.         Emp emp3=new Emp();
39.         emp3.setEname("terry");
40.         emp3.setJob("salesman");
41.         emp3.setMgr(1);
42.         emp3.setHiredate("2014-05-29");
43.         emp3.setSal(2500);
44.         emp3.setComm(200);
45.         emp3.setDeptno(30);
46.         
47.         Emp emp4=new Emp();
48.         emp4.setEname("jim");
49.         emp4.setJob("salesman");
50.         emp4.setMgr(1);
51.         emp4.setHiredate("2014-05-26");
52.         emp4.setSal(2500);
53.         emp4.setComm(200);
54.         emp4.setDeptno(30);
55.     
56.      emps.add(emp1);
57.      emps.add(emp2);
58.      emps.add(emp3);
59.      emps.add(emp4);
60.     
61.         dao.addTeam(emps);
62.     }
64.     publicvoid addTeam(List<Emp> emps) {
65.         Connection con = null;
66.         PreparedStatement stmt = null;
67.         ResultSet rs = null;
68.         String sql = null;
70.         try {
71.             con = ConnectionSource.getConnection();
72.             // 关闭自动提交
73.             con.setAutoCommit(false);
75.             // 插入主表
76.             sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)"
77.                     + " values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
78.             stmt = con.prepareStatement(sql, new String[] { "empno" });
79.             int mgr = 0;
80.             for (int i = 0; i < emps.size(); i++) {
81.                 Emp emp = (Emp) emps.get(i);
82.                 stmt.setString(1, emp.getEname());
83.                 stmt.setString(2, emp.getJob());
84.                 if (i == 0) {
85.                     stmt.setInt(3, emp.getMgr());
86.                 } else {
87.                     stmt.setInt(3, mgr);
88.                 }
89.                 stmt.setString(4, emp.getHiredate());
90.                 stmt.setDouble(5, emp.getSal());
91.                 stmt.setDouble(6, emp.getComm());
92.                 stmt.setInt(7, emp.getDeptno());
93.                 stmt.executeUpdate();
94.                 if (i == 0) {
95.                     rs = stmt.getGeneratedKeys();
96.                     if (rs.next()) {
97.                         mgr = rs.getInt(1);
98.                     }
99.                 }
100.             }
101.             con.commit();
103.         } catch (SQLException e) {
104.             System.out.println("数据库访问异常!");
105.             thrownew RuntimeException(e);
106.         } finally {
107.             try {
108.                 if (rs != null) {
109.                     rs.close();
110.                 }
111.                 if (stmt != null) {
112.                     stmt.close();
113.                 }
114.                 if (con != null) {
115.                     con.close();
116.                 }
117.             } catch (SQLException e) {
118.                 System.out.println("释放资源时发生异常");
119.             }
120.         }
121.     }
123.     publicvoid updateSalByDeptno(int deptno) {
124.         Connection con = null;
125.         Statement stmt = null;
126.         try {
127.             con = ConnectionSource.getConnection();
128.             stmt = con.createStatement();
130.             // 插入数据的SQL语句
131.             String sql1 = "update emp set sal =sal*1.2" + " where deptno="
132.                     + deptno + " and job='ANALYST'";
133.             String sql2 = "update emp set sal =sal*1.3" + " where deptno="
134.                     + deptno + " and job='MANAGER'";
135.             // 关闭自动提交
136.             con.setAutoCommit(false);
137.             // 执行SQL语句
138.             stmt.executeUpdate(sql1);
139.             stmt.executeUpdate(sql2);
140.             // 提交
141.             con.commit();
142.         } catch (SQLException e) {
143.             try {
144.                 con.rollback();
145.             } catch (SQLException e1) {
146.                 System.out.println("回滚事务异常！");
147.                 thrownew RuntimeException(e1);
148.             }
149.             System.out.println("数据库访问异常!");
150.             thrownew RuntimeException(e);
151.         } finally {
152.             try {
153.                 if (stmt != null) {
154.                     stmt.close();
155.                 }
156.                 if (con != null) {
157.                     con.close();
158.                 }
159.             } catch (SQLException e) {
160.                 System.out.println("释放资源时发生异常");
161.             }
162.         }
163.     }
165.     publicvoid findSalByEname(String ename) {
166.         Connection con = null;
167.         PreparedStatement stmt = null;
168.         ResultSet rs = null;
169.         String sql = null;
171.         try {
172.             sql = "select sal from emp where ename = ?";
173.             con = ConnectionSource.getConnection();
174.             stmt = con.prepareStatement(sql);
175.             stmt.setString(1, ename);
176.             rs = stmt.executeQuery();
178.             while (rs.next()) {
179.                 double sal = rs.getDouble("sal");
180.                 System.out.println(sal);
181.             }
182.         } catch (SQLException e) {
183.             System.out.println("数据库访问异常!");
184.             thrownew RuntimeException(e);
185.         } finally {
186.             try {
187.                 if (rs != null) {
188.                     rs.close();
189.                 }
190.                 if (stmt != null) {
191.                     stmt.close();
192.                 }
193.                 if (con != null) {
194.                     con.close();
195.                 }
196.             } catch (SQLException e) {
197.                 System.out.println("释放资源发生异常");
198.             }
199.         }
200.     }
202.     publicvoid findSalByEname1(String ename) {
203.         Connection con = null;
204.         Statement stmt = null;
205.         ResultSet rs = null;
206.         String sql = null;
208.         try {
209.             sql = "select sal from emp where ename = '" + ename + "'";
210.             System.out.println(sql);
211.             con = ConnectionSource.getConnection();
212.             stmt = con.createStatement();
213.             rs = stmt.executeQuery(sql);
215.             while (rs.next()) {
216.                 double sal = rs.getDouble("sal");
217.                 System.out.println(sal);
218.             }
219.         } catch (SQLException e) {
220.             System.out.println("数据库访问异常!");
221.             thrownew RuntimeException(e);
222.         } finally {
223.             try {
224.                 if (rs != null) {
225.                     rs.close();
226.                 }
227.                 if (stmt != null) {
228.                     stmt.close();
229.                 }
230.                 if (con != null) {
231.                     con.close();
232.                 }
233.             } catch (SQLException e) {
234.                 System.out.println("释放资源发生异常");
235.             }
236.         }
237.     }
238. }

隐藏

db.properties文件的内容与之前案例一样没有变化，该文件完整内容如下所示：

 

1. jdbc.driverClassName=oracle.jdbc.OracleDriver
2. jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
3. jdbc.username=scott
4. jdbc.password=tiger
6. #<!-- 初始化连接 -->
7. dataSource.initialSize=10
8. #<!-- 最大空闲连接 -->
9. dataSource.maxIdle=20
10. #<!-- 最小空闲连接 -->
11. dataSource.minIdle=5
12. #最大连接数量
13. dataSource.maxActive=50
14. #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
15. dataSource.maxWait=1000

隐藏

ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：

 

1. import java.io.IOException;
2. import java.sql.Connection;
3. import java.sql.SQLException;
4. import java.util.Properties;
6. import org.apache.commons.dbcp.BasicDataSource;
8. public class ConnectionSource {
9.     private static BasicDataSource dataSource = null;
11.     public ConnectionSource() {
12.     }
13.     public static void init() {
15.         Properties dbProps = new Properties();
16.         // 取配置文件可以根据实际的不同修改
17.         try {
18.             dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
19.                     "day01/v4/db.properties"));
20.         } catch (IOException e) {
21.             e.printStackTrace();
22.         }
24.         try {
25.             String driveClassName = dbProps.getProperty("jdbc.driverClassName");
26.             String url = dbProps.getProperty("jdbc.url");
27.             String username = dbProps.getProperty("jdbc.username");
28.             String password = dbProps.getProperty("jdbc.password");
30.             String initialSize = dbProps.getProperty("dataSource.initialSize");
31.             String minIdle = dbProps.getProperty("dataSource.minIdle");
32.             String maxIdle = dbProps.getProperty("dataSource.maxIdle");
33.             String maxWait = dbProps.getProperty("dataSource.maxWait");
34.             String maxActive = dbProps.getProperty("dataSource.maxActive");
36.             dataSource = new BasicDataSource();
37.             dataSource.setDriverClassName(driveClassName);
38.             dataSource.setUrl(url);
39.             dataSource.setUsername(username);
40.             dataSource.setPassword(password);
42.             // 初始化连接数
43.             if (initialSize != null)
44.                 dataSource.setInitialSize(Integer.parseInt(initialSize));
46.             // 最小空闲连接
47.             if (minIdle != null)
48.                 dataSource.setMinIdle(Integer.parseInt(minIdle));
50.             // 最大空闲连接
51.             if (maxIdle != null)
52.                 dataSource.setMaxIdle(Integer.parseInt(maxIdle));
54.             // 超时回收时间(以毫秒为单位)
55.             if (maxWait != null)
56.                 dataSource.setMaxWait(Long.parseLong(maxWait));
58.             // 最大连接数
59.             if (maxActive != null) {
60.                 if (!maxActive.trim().equals("0"))
61.                     dataSource.setMaxActive(Integer.parseInt(maxActive));
62.             }
63.         } catch (Exception e) {
64.             e.printStackTrace();
65.             System.out.println("创建连接池失败!请检查设置!!!");
66.         }
67.     }
69.     public static synchronized Connection getConnection() throws SQLException {
70.         if (dataSource == null) {
71.             init();
72.         }
73.         Connection conn = null;
74.         if (dataSource != null) {
75.             conn = dataSource.getConnection();
76.         }
77.         return conn;
78.     }
79. }

隐藏

4 实现对Dept数据的分页查询（Oracle和MySQL）

使用JDBC分别连接Oracle数据库和MySQL数据库，实现对Dept表数据的分页查询功能。

参考答案

实现此案例需要按照如下步骤进行。

步骤一：添加方法findByPageOracle方法，并构建该方法的骨架代码

首先，在DeptDAO类中添加方法findByPageOracle，该方法的声明如下所示：

 

1. publicvoid findByPageOracle(int page, int pageSize) {}

其中，参数page表示要查询的页码、参数pageSize表示每页显示的记录数。

然后，构建findByPageOracle方法的骨架代码，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
21.     }
23.     /**
24.      * @param page
25.      * 要查看第几页
26.      * @param pageSize
27.      * 每页记录数
28.      */
29.     publicvoid findByPageOracle(int page, int pageSize) {
30.         
31.     }
33.     publicvoid findAll() {
34.         ... ...
35.     }
37.     publicvoid add(Dept dept) {
38.         ... ...
39.     }
41.     publicvoid update(Dept dept) {
42.         ... ...
43.     }
44. }

步骤二：定义SQL语句

在findByPageOracle方法中，定义变量sql_total以及sql来表示两条SQL语句，一条用于查询Emp表的总记录数，另一条作为分页的SQL语句，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
21.     }
23.     /**
24.      * @param page
25.      * 要查看第几页
26.      * @param pageSize
27.      * 每页记录数
28.      */
29.     publicvoid findByPageOracle(int page, int pageSize) {
30.         String sql_total = "select count(*) from dept";
31.         String sql = "select * from "
32.                 + "(select rownum rn, deptno, dname ,loc from "
33.                 + "(select * from dept order by deptno) )"
34.                 + " where rn between ? and ?";
36.     }
38.     publicvoid findAll() {
39.         ... ...
40.     }
42.     publicvoid add(Dept dept) {
43.         ... ...
44.     }
46.     publicvoid update(Dept dept) {
47.         ... ...
48.     }
49. }

步骤三：查询Dept表的总记录数

获取数据库连接，使用PreparedStatement执行SQL语句（sql_total变量定义的SQL语句），获取数据库中Dept表的总记录数，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
21.     }
23.     /**
24.      * @param page
25.      * 要查看第几页
26.      * @param pageSize
27.      * 每页记录数
28.      */
29.     publicvoid findByPageOracle(int page, int pageSize) {
30.         Connection con = null;
31.         PreparedStatement stmt = null;
32.         ResultSet rs = null;
33.         int total = -1;// 总记录数
35.         String sql_total = "select count(*) from dept";
36.         String sql = "select * from "
37.                 + "(select rownum rn, deptno, dname ,loc from "
38.                 + "(select * from dept order by deptno) )"
39.                 + " where rn between ? and ?";
41.         try {
42.             con = ConnectionSource.getConnection();
43.             stmt = con.prepareStatement(sql_total);
45.             // 获得总的记录数
46.             rs = stmt.executeQuery();
47.             if (rs.next()) {
48.                 total = rs.getInt(1);
49.             }
51.         } catch (SQLException e) {
52.             System.out.println("数据库访问异常!");
53.             thrownew RuntimeException(e);
54.         } finally {
55.             try {
56.                 if (rs != null) {
57.                     rs.close();
58.                 }
59.                 if (stmt != null) {
60.                     stmt.close();
61.                 }
62.                 if (con != null) {
63.                     con.close();
64.                 }
65.             } catch (SQLException e) {
66.                 System.out.println("释放资源时发生异常");
67.             }
68.         }
70.     }
72.     publicvoid findAll() {
73.         ... ...
74.     }
76.     publicvoid add(Dept dept) {
77.         ... ...
78.     }
80.     publicvoid update(Dept dept) {
81.         ... ...
82.     }
83. }

步骤四：计算总页数

将总记录数与每页记录数取余数，如果余数为0，则总页数等于总记录数除以每页记录数的商；如果余数不为0，则总页数等于总记录数除以每页记录数的商的基础上加1，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
21.     }
23.     /**
24.      * @param page
25.      * 要查看第几页
26.      * @param pageSize
27.      * 每页记录数
28.      */
29.     publicvoid findByPageOracle(int page, int pageSize) {
30.         Connection con = null;
31.         PreparedStatement stmt = null;
32.         ResultSet rs = null;
33.         int total = -1;// 总记录数
34.         int pages = -1;// 总页数
36.         String sql_total = "select count(*) from dept";
37.         String sql = "select * from "
38.                 + "(select rownum rn, deptno, dname ,loc from "
39.                 + "(select * from dept order by deptno) )"
40.                 + " where rn between ? and ?";
42.         try {
43.             con = ConnectionSource.getConnection();
44.             stmt = con.prepareStatement(sql_total);
46.             // 获得总的记录数
47.             rs = stmt.executeQuery();
48.             if (rs.next()) {
49.                 total = rs.getInt(1);
50.             }
52.             // 计算总共需要多少页
53.             int mod = total % pageSize;
54.             if (mod == 0)
55.                 pages = total / pageSize;
56.             else
57.                 pages = total / pageSize + 1;
59.         } catch (SQLException e) {
60.             System.out.println("数据库访问异常!");
61.             thrownew RuntimeException(e);
62.         } finally {
63.             try {
64.                 if (rs != null) {
65.                     rs.close();
66.                 }
67.                 if (stmt != null) {
68.                     stmt.close();
69.                 }
70.                 if (con != null) {
71.                     con.close();
72.                 }
73.             } catch (SQLException e) {
74.                 System.out.println("释放资源时发生异常");
75.             }
76.         }
78.     }
80.     publicvoid findAll() {
81.         ... ...
82.     }
84.     publicvoid add(Dept dept) {
85.         ... ...
86.     }
88.     publicvoid update(Dept dept) {
89.         ... ...
90.     }
91. }

步骤五：边界判断

如果要查看的页码大于总页数，则要查看的页码等于总页数；如果要查看的页码小于1，则要查看的页码等于1，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
21.     }
23.     /**
24.      * @param page
25.      * 要查看第几页
26.      * @param pageSize
27.      * 每页记录数
28.      */
29.     publicvoid findByPageOracle(int page, int pageSize) {
30.         Connection con = null;
31.         PreparedStatement stmt = null;
32.         ResultSet rs = null;
33.         int total = -1;// 总记录数
34.         int pages = -1;// 总页数
36.         String sql_total = "select count(*) from dept";
37.         String sql = "select * from "
38.                 + "(select rownum rn, deptno, dname ,loc from "
39.                 + "(select * from dept order by deptno) )"
40.                 + " where rn between ? and ?";
42.         try {
43.             con = ConnectionSource.getConnection();
44.             stmt = con.prepareStatement(sql_total);
46.             // 获得总的记录数
47.             rs = stmt.executeQuery();
48.             if (rs.next()) {
49.                 total = rs.getInt(1);
50.             }
52.             // 计算总共需要多少页
53.             int mod = total % pageSize;
54.             if (mod == 0)
55.                 pages = total / pageSize;
56.             else
57.                 pages = total / pageSize + 1;
59.             // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
60.             if (page > pages)
61.                 page = pages;
62.             elseif (page < 1) {
63.                 page = 1;
64.             }
66.         } catch (SQLException e) {
67.             System.out.println("数据库访问异常!");
68.             thrownew RuntimeException(e);
69.         } finally {
70.             try {
71.                 if (rs != null) {
72.                     rs.close();
73.                 }
74.                 if (stmt != null) {
75.                     stmt.close();
76.                 }
77.                 if (con != null) {
78.                     con.close();
79.                 }
80.             } catch (SQLException e) {
81.                 System.out.println("释放资源时发生异常");
82.             }
83.         }
85.     }
87.     publicvoid findAll() {
88.         ... ...
89.     }
91.     publicvoid add(Dept dept) {
92.         ... ...
93.     }
95.     publicvoid update(Dept dept) {
96.         ... ...
97.     }
98. }

步骤六：计算取记录的起始位置和结束位置

起始位置（begin）的计算公式如下：

 

1. int begin = (page - 1) * pageSize + 1;

结束位置（end）的计算公式如下：

1. int end = begin + pageSize - 1;

在findByPageOracle方法中的实现代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
21.     }
23.     /**
24.      * @param page
25.      * 要查看第几页
26.      * @param pageSize
27.      * 每页记录数
28.      */
29.     publicvoid findByPageOracle(int page, int pageSize) {
30.         Connection con = null;
31.         PreparedStatement stmt = null;
32.         ResultSet rs = null;
33.         int total = -1;// 总记录数
34.         int pages = -1;// 总页数
36.         String sql_total = "select count(*) from dept";
37.         String sql = "select * from "
38.                 + "(select rownum rn, deptno, dname ,loc from "
39.                 + "(select * from dept order by deptno) )"
40.                 + " where rn between ? and ?";
42.         try {
43.             con = ConnectionSource.getConnection();
44.             stmt = con.prepareStatement(sql_total);
46.             // 获得总的记录数
47.             rs = stmt.executeQuery();
48.             if (rs.next()) {
49.                 total = rs.getInt(1);
50.             }
52.             // 计算总共需要多少页
53.             int mod = total % pageSize;
54.             if (mod == 0)
55.                 pages = total / pageSize;
56.             else
57.                 pages = total / pageSize + 1;
59.             // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
60.             if (page > pages)
61.                 page = pages;
62.             elseif (page < 1) {
63.                 page = 1;
64.             }
66.             int begin = (page - 1) * pageSize + 1;
67.             int end = begin + pageSize - 1;
69.         } catch (SQLException e) {
70.             System.out.println("数据库访问异常!");
71.             thrownew RuntimeException(e);
72.         } finally {
73.             try {
74.                 if (rs != null) {
75.                     rs.close();
76.                 }
77.                 if (stmt != null) {
78.                     stmt.close();
79.                 }
80.                 if (con != null) {
81.                     con.close();
82.                 }
83.             } catch (SQLException e) {
84.                 System.out.println("释放资源时发生异常");
85.             }
86.         }
88.     }
90.     publicvoid findAll() {
91.         ... ...
92.     }
94.     publicvoid add(Dept dept) {
95.         ... ...
96.     }
98.     publicvoid update(Dept dept) {
99.         ... ...
100.     }
101. }

步骤七：执行分页查询SQL语句

在findByPageOracle方法中执行分页查询的代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
21.     }
23.     /**
24.      * @param page
25.      * 要查看第几页
26.      * @param pageSize
27.      * 每页记录数
28.      */
29.     publicvoid findByPageOracle(int page, int pageSize) {
30.         Connection con = null;
31.         PreparedStatement stmt = null;
32.         ResultSet rs = null;
33.         int total = -1;// 总记录数
34.         int pages = -1;// 总页数
36.         String sql_total = "select count(*) from dept";
37.         String sql = "select * from "
38.                 + "(select rownum rn, deptno, dname ,loc from "
39.                 + "(select * from dept order by deptno) )"
40.                 + " where rn between ? and ?";
42.         try {
43.             con = ConnectionSource.getConnection();
44.             stmt = con.prepareStatement(sql_total);
46.             // 获得总的记录数
47.             rs = stmt.executeQuery();
48.             if (rs.next()) {
49.                 total = rs.getInt(1);
50.             }
52.             // 计算总共需要多少页
53.             int mod = total % pageSize;
54.             if (mod == 0)
55.                 pages = total / pageSize;
56.             else
57.                 pages = total / pageSize + 1;
59.             // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
60.             if (page > pages)
61.                 page = pages;
62.             elseif (page < 1) {
63.                 page = 1;
64.             }
66.             int begin = (page - 1) * pageSize + 1;
67.             int end = begin + pageSize - 1;
69.             stmt = con.prepareStatement(sql);
70.             stmt.setInt(1, begin);
71.             stmt.setInt(2, end);
72.             rs = stmt.executeQuery();
73.             while (rs.next()) {
74.                 System.out.println(rs.getInt("deptno") + ","
75.                         + rs.getString("dname") + ","
76.                         + rs.getString("loc"));
77.             }
78.         } catch (SQLException e) {
79.             System.out.println("数据库访问异常!");
80.             thrownew RuntimeException(e);
81.         } finally {
82.             try {
83.                 if (rs != null) {
84.                     rs.close();
85.                 }
86.                 if (stmt != null) {
87.                     stmt.close();
88.                 }
89.                 if (con != null) {
90.                     con.close();
91.                 }
92.             } catch (SQLException e) {
93.                 System.out.println("释放资源时发生异常");
94.             }
95.         }
97.     }
99.     publicvoid findAll() {
100.         ... ...
101.     }
103.     publicvoid add(Dept dept) {
104.         ... ...
105.     }
107.     publicvoid update(Dept dept) {
108.         ... ...
109.     }
110. }

步骤八：测试

在DeptDAO类的main方法中调用findByPageOracle方法，代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
22.         // 4.findByPageOracle
23.         dao.findByPageOracle(2, 3);// 查看第二页，每页3条
24.     }
26.     /**
27.      * @param page
28.      * 要查看第几页
29.      * @param pageSize
30.      * 每页记录数
31.      */
32.     publicvoid findByPageOracle(int page, int pageSize) {
33.         Connection con = null;
34.         PreparedStatement stmt = null;
35.         ResultSet rs = null;
36.         int total = -1;// 总记录数
37.         int pages = -1;// 总页数
39.         String sql_total = "select count(*) from dept";
40.         String sql = "select * from "
41.                 + "(select rownum rn, deptno, dname ,loc from "
42.                 + "(select * from dept order by deptno) )"
43.                 + " where rn between ? and ?";
45.         try {
46.             con = ConnectionSource.getConnection();
47.             stmt = con.prepareStatement(sql_total);
49.             // 获得总的记录数
50.             rs = stmt.executeQuery();
51.             if (rs.next()) {
52.                 total = rs.getInt(1);
53.             }
55.             // 计算总共需要多少页
56.             int mod = total % pageSize;
57.             if (mod == 0)
58.                 pages = total / pageSize;
59.             else
60.                 pages = total / pageSize + 1;
62.             // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
63.             if (page > pages)
64.                 page = pages;
65.             elseif (page < 1) {
66.                 page = 1;
67.             }
69.             int begin = (page - 1) * pageSize + 1;
70.             int end = begin + pageSize - 1;
72.             stmt = con.prepareStatement(sql);
73.             stmt.setInt(1, begin);
74.             stmt.setInt(2, end);
75.             rs = stmt.executeQuery();
76.             while (rs.next()) {
77.                 System.out.println(rs.getInt("deptno") + ","
78.                         + rs.getString("dname") + ","
79.                         + rs.getString("loc"));
80.             }
81.         } catch (SQLException e) {
82.             System.out.println("数据库访问异常!");
83.             thrownew RuntimeException(e);
84.         } finally {
85.             try {
86.                 if (rs != null) {
87.                     rs.close();
88.                 }
89.                 if (stmt != null) {
90.                     stmt.close();
91.                 }
92.                 if (con != null) {
93.                     con.close();
94.                 }
95.             } catch (SQLException e) {
96.                 System.out.println("释放资源时发生异常");
97.             }
98.         }
100.     }
102.     publicvoid findAll() {
103.         ... ...
104.     }
106.     publicvoid add(Dept dept) {
107.         ... ...
108.     }
110.     publicvoid update(Dept dept) {
111.         ... ...
112.     }
113. }

运行DeptDAO类，在控制台会输出第二页的三条数据。

步骤九：连接MySQL数据库，实现对Dept表中数据的分页查询

连接MySQL数据库，实现对Dept表中数据的分页查询，与连接Oracle是类似的。需要注意的是将db.properties文件中连接数据库的信息改为与MySQL数据库相关的，该文件内容如下：

1. #jdbc.driverClassName=oracle.jdbc.OracleDriver
2. #jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
3. #jdbc.username=scott
4. #jdbc.password=tiger
6. jdbc.driverClassName=com.mysql.jdbc.Driver
7. jdbc.url=jdbc:mysql://localhost:3306/tts7
8. jdbc.username=root
9. jdbc.password=root
11. #<!-- 初始化连接 -->
12. dataSource.initialSize=10
13. #<!-- 最大空闲连接 -->
14. dataSource.maxIdle=20
15. #<!-- 最小空闲连接 -->
16. dataSource.minIdle=5
17. #最大连接数量
18. dataSource.maxActive=50
19. #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
20. dataSource.maxWait=1000

在DeptDAO类中添加findByPageMySQL方法，实现连接MySQL数据库，实现对Dept表中数据的分页查询，代码如下所示：

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
22.         // 4.findByPageOracle
23.         //dao.findByPageOracle(2, 3);// 查看第二页，每页3条
25.         // 5.findByPageMySQL
26.         dao.findByPageMySQL(2, 3);// 查看第二页，每页3条
28.     }
30.     /**
31.      * @param page
32.      * 要查看第几页
33.      * @param pageSize
34.      * 每页记录数
35.      */
36.     publicvoid findByPageOracle(int page, int pageSize) {
37.         Connection con = null;
38.         PreparedStatement stmt = null;
39.         ResultSet rs = null;
40.         int total = -1;// 总记录数
41.         int pages = -1;// 总页数
43.         String sql_total = "select count(*) from dept";
44.         String sql = "select * from "
45.                 + "(select rownum rn, deptno, dname ,loc from "
46.                 + "(select * from dept order by deptno) )"
47.                 + " where rn between ? and ?";
49.         try {
50.             con = ConnectionSource.getConnection();
51.             stmt = con.prepareStatement(sql_total);
53.             // 获得总的记录数
54.             rs = stmt.executeQuery();
55.             if (rs.next()) {
56.                 total = rs.getInt(1);
57.             }
59.             // 计算总共需要多少页
60.             int mod = total % pageSize;
61.             if (mod == 0)
62.                 pages = total / pageSize;
63.             else
64.                 pages = total / pageSize + 1;
66.             // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
67.             if (page > pages)
68.                 page = pages;
69.             elseif (page < 1) {
70.                 page = 1;
71.             }
73.             int begin = (page - 1) * pageSize + 1;
74.             int end = begin + pageSize - 1;
76.             stmt = con.prepareStatement(sql);
77.             stmt.setInt(1, begin);
78.             stmt.setInt(2, end);
79.             rs = stmt.executeQuery();
80.             while (rs.next()) {
81.                 System.out.println(rs.getInt("deptno") + ","
82.                         + rs.getString("dname") + ","
83.                         + rs.getString("loc"));
84.             }
85.         } catch (SQLException e) {
86.             System.out.println("数据库访问异常!");
87.             thrownew RuntimeException(e);
88.         } finally {
89.             try {
90.                 if (rs != null) {
91.                     rs.close();
92.                 }
93.                 if (stmt != null) {
94.                     stmt.close();
95.                 }
96.                 if (con != null) {
97.                     con.close();
98.                 }
99.             } catch (SQLException e) {
100.                 System.out.println("释放资源时发生异常");
101.             }
102.         }
104.     }
106.     /**
107.      *
108.      * @param page
109.      * 要查看第几页
110.      * @param pageSize
111.      * 每页记录数
112.      */
113.     publicvoid findByPageMySQL(int page, int pageSize) {
114.         Connection con = null;
115.         PreparedStatement stmt = null;
116.         ResultSet rs = null;
117.         int total = -1;// 总记录数
118.         int pages = -1;// 总页数
120.         String sql_total = "select count(*) from dept";
121.         String sql = "select * from dept order by deptno limit ?,?";
123.         try {
124.             con = ConnectionSource.getConnection();
125.             stmt = con.prepareStatement(sql_total);
127.             // 获得总的记录数
128.             rs = stmt.executeQuery();
129.             if (rs.next()) {
130.                 total = rs.getInt(1);
131.             }
132.             System.out.println(total);
133.             // 计算总共需要多少页
134.             int mod = total % pageSize;
135.             if (mod == 0)
136.                 pages = total / pageSize;
137.             else
138.                 pages = total / pageSize + 1;
140.             // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
141.             if (page > pages)
142.                 page = pages;
143.             elseif (page < 1) {
144.                 page = 1;
145.             }
146.             int start = (page - 1) * pageSize;
147.             stmt = con.prepareStatement(sql);
148.             stmt.setInt(1, start);
149.             stmt.setInt(2, pageSize);
150.             rs = stmt.executeQuery();
151.             while (rs.next()) {
152.                 System.out.println(rs.getInt("deptno") + ","
153.                         + rs.getString("dname") + ","
154.                         + rs.getString("loc"));
155.             }
156.         } catch (SQLException e) {
157.             System.out.println("数据库访问异常!");
158.             thrownew RuntimeException(e);
159.         } finally {
160.             try {
161.                 if (rs != null) {
162.                     rs.close();
163.                 }
164.                 if (stmt != null) {
165.                     stmt.close();
166.                 }
167.                 if (con != null) {
168.                     con.close();
169.                 }
170.             } catch (SQLException e) {
171.                 System.out.println("释放资源时发生异常");
172.             }
173.         }
174.     }
176.     publicvoid findAll() {
177.         ... ...
178.     }
180.     publicvoid add(Dept dept) {
181.         ... ...
182.     }
184.     publicvoid update(Dept dept) {
185.         ... ...
186.     }
187. }

本案例中，DeptDAO类的完整代码如下所示：

 

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.PreparedStatement;
4. import java.sql.ResultSet;
5. import java.sql.SQLException;
6. import java.sql.Statement;
8. publicclass DeptDAO {
9.     publicstaticvoid main(String[] args) {
10.         // 1.select
11.         DeptDAO dao = new DeptDAO();
12.         //dao.findAll();
14.         // 2.insert
15.         Dept dept = new Dept(50, "developer", "Beijing");
16.         // dao.add(dept);
18.         // 3.update
19.         dept.setLoc("ShangHai");
20.         //dao.update(dept);
22.         // 4.findByPageOracle
23.         //dao.findByPageOracle(2, 3);// 查看第二页，每页3条
25.         // 5.findByPageMySQL
26.         dao.findByPageMySQL(2, 3);// 查看第二页，每页3条
28.     }
30.     /**
31.      * @param page
32.      * 要查看第几页
33.      * @param pageSize
34.      * 每页记录数
35.      */
36.     publicvoid findByPageOracle(int page, int pageSize) {
37.         Connection con = null;
38.         PreparedStatement stmt = null;
39.         ResultSet rs = null;
40.         int total = -1;// 总记录数
41.         int pages = -1;// 总页数
43.         String sql_total = "select count(*) from dept";
44.         String sql = "select * from "
45.                 + "(select rownum rn, deptno, dname ,loc from "
46.                 + "(select * from dept order by deptno) )"
47.                 + " where rn between ? and ?";
49.         try {
50.             con = ConnectionSource.getConnection();
51.             stmt = con.prepareStatement(sql_total);
53.             // 获得总的记录数
54.             rs = stmt.executeQuery();
55.             if (rs.next()) {
56.                 total = rs.getInt(1);
57.             }
59.             // 计算总共需要多少页
60.             int mod = total % pageSize;
61.             if (mod == 0)
62.                 pages = total / pageSize;
63.             else
64.                 pages = total / pageSize + 1;
66.             // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
67.             if (page > pages)
68.                 page = pages;
69.             elseif (page < 1) {
70.                 page = 1;
71.             }
73.             int begin = (page - 1) * pageSize + 1;
74.             int end = begin + pageSize - 1;
76.             stmt = con.prepareStatement(sql);
77.             stmt.setInt(1, begin);
78.             stmt.setInt(2, end);
79.             rs = stmt.executeQuery();
80.             while (rs.next()) {
81.                 System.out.println(rs.getInt("deptno") + ","
82.                         + rs.getString("dname") + ","
83.                         + rs.getString("loc"));
84.             }
85.         } catch (SQLException e) {
86.             System.out.println("数据库访问异常!");
87.             thrownew RuntimeException(e);
88.         } finally {
89.             try {
90.                 if (rs != null) {
91.                     rs.close();
92.                 }
93.                 if (stmt != null) {
94.                     stmt.close();
95.                 }
96.                 if (con != null) {
97.                     con.close();
98.                 }
99.             } catch (SQLException e) {
100.                 System.out.println("释放资源时发生异常");
101.             }
102.         }
104.     }
106.     /**
107.      *
108.      * @param page
109.      * 要查看第几页
110.      * @param pageSize
111.      * 每页记录数
112.      */
113.     publicvoid findByPageMySQL(int page, int pageSize) {
114.         Connection con = null;
115.         PreparedStatement stmt = null;
116.         ResultSet rs = null;
117.         int total = -1;// 总记录数
118.         int pages = -1;// 总页数
120.         String sql_total = "select count(*) from dept";
121.         String sql = "select * from dept order by deptno limit ?,?";
123.         try {
124.             con = ConnectionSource.getConnection();
125.             stmt = con.prepareStatement(sql_total);
127.             // 获得总的记录数
128.             rs = stmt.executeQuery();
129.             if (rs.next()) {
130.                 total = rs.getInt(1);
131.             }
132.             System.out.println(total);
133.             // 计算总共需要多少页
134.             int mod = total % pageSize;
135.             if (mod == 0)
136.                 pages = total / pageSize;
137.             else
138.                 pages = total / pageSize + 1;
140.             // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
141.             if (page > pages)
142.                 page = pages;
143.             elseif (page < 1) {
144.                 page = 1;
145.             }
146.             int start = (page - 1) * pageSize;
147.             stmt = con.prepareStatement(sql);
148.             stmt.setInt(1, start);
149.             stmt.setInt(2, pageSize);
150.             rs = stmt.executeQuery();
151.             while (rs.next()) {
152.                 System.out.println(rs.getInt("deptno") + ","
153.                         + rs.getString("dname") + "," + ","
154.                         + rs.getString("loc"));
155.             }
156.         } catch (SQLException e) {
157.             System.out.println("数据库访问异常!");
158.             thrownew RuntimeException(e);
159.         } finally {
160.             try {
161.                 if (rs != null) {
162.                     rs.close();
163.                 }
164.                 if (stmt != null) {
165.                     stmt.close();
166.                 }
167.                 if (con != null) {
168.                     con.close();
169.                 }
170.             } catch (SQLException e) {
171.                 System.out.println("释放资源时发生异常");
172.             }
173.         }
174.     }
176.     publicvoid findAll() {
177.         Connection con = null;
178.         Statement stmt = null;
179.         ResultSet rs = null;
181.         try {
182.             Class.forName("oracle.jdbc.OracleDriver");
183.             con = DriverManager.getConnection(
184.                     "jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
185.             stmt = con.createStatement();
186.             rs = stmt.executeQuery("select deptno,dname,loc from dept");
187.             while (rs.next()) {
188.                 System.out.println(rs.getInt("deptno") + ","
189.                         + rs.getString("dname") + "," + rs.getString("loc"));
190.             }
191.         } catch (ClassNotFoundException e) {
192.             System.out.println("驱动类无法找到!");
193.             thrownew RuntimeException(e);
194.         } catch (SQLException e) {
195.             System.out.println("数据库访问异常!");
196.             thrownew RuntimeException(e);
197.         } finally {
198.             try {
199.                 if (rs != null) {
200.                     rs.close();
201.                 }
202.                 if (stmt != null) {
203.                     stmt.close();
204.                 }
205.                 if (con != null) {
206.                     con.close();
207.                 }
208.             } catch (SQLException e) {
209.                 System.out.println("关闭连接时发生异常");
210.             }
211.         }
212.     }
214.     publicvoid add(Dept dept) {
215.         Connection con = null;
216.         Statement stmt = null;
217.         int flag = -1;
218.         String sql = "insert into dept(deptno,dname,loc) " + "values("
219.                 + dept.getDeptno() + ",'" + dept.getDname() + "','"
220.                 + dept.getLoc() + "')";
221.         System.out.println(sql);
222.         try {
223.             con = ConnectionSource.getConnection();
224.             stmt = con.createStatement();
226.             flag = stmt.executeUpdate(sql);
227.             if (flag > 0) {
228.                 System.out.println("新增记录成功!");
229.             }
230.         } catch (SQLException e) {
231.             System.out.println("数据库访问异常!");
232.             thrownew RuntimeException(e);
233.         } finally {
234.             try {
235.                 if (stmt != null) {
236.                     stmt.close();
237.                 }
238.                 if (con != null) {
239.                     con.close();
240.                 }
241.             } catch (SQLException e) {
242.                 System.out.println("释放资源发生异常");
243.             }
244.         }
245.     }
247.     publicvoid update(Dept dept) {
248.         Connection con = null;
249.         Statement stmt = null;
250.         int flag = -1;
251.         String sql = "update dept set dname = '" + dept.getDname() + "',"
252.                 + "loc = '" + dept.getLoc() + "' where deptno = "
253.                 + dept.getDeptno();
254.         try {
255.             con = ConnectionSource.getConnection();
256.             stmt = con.createStatement();
258.             flag = stmt.executeUpdate(sql);
259.             if (flag > 0) {
260.                 System.out.println("更新记录成功!");
261.             }
262.         } catch (SQLException e) {
263.             System.out.println("数据库访问异常!");
264.             thrownew RuntimeException(e);
265.         } finally {
266.             try {
267.                 if (stmt != null) {
268.                     stmt.close();
269.                 }
270.                 if (con != null) {
271.                     con.close();
272.                 }
273.             } catch (SQLException e) {
274.                 System.out.println("释放资源发生异常");
275.             }
276.         }
277.     }
278. }

隐藏

db.properties文件的完整内容如下所示：

 

1. #jdbc.driverClassName=oracle.jdbc.OracleDriver
2. #jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
3. #jdbc.username=scott
4. #jdbc.password=tiger
6. jdbc.driverClassName=com.mysql.jdbc.Driver
7. jdbc.url=jdbc:mysql://localhost:3306/tts7
8. jdbc.username=root
9. jdbc.password=root
11. #<!-- 初始化连接 -->
12. dataSource.initialSize=10
13. #<!-- 最大空闲连接 -->
14. dataSource.maxIdle=20
15. #<!-- 最小空闲连接 -->
16. dataSource.minIdle=5
17. #最大连接数量
18. dataSource.maxActive=50
19. #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
20. dataSource.maxWait=1000

隐藏

ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：

 

1. import java.io.IOException;
2. import java.sql.Connection;
3. import java.sql.SQLException;
4. import java.util.Properties;
6. import org.apache.commons.dbcp.BasicDataSource;
8. public class ConnectionSource {
9.     private static BasicDataSource dataSource = null;
11.     public ConnectionSource() {
12.     }
13.     public static void init() {
15.         Properties dbProps = new Properties();
16.         // 取配置文件可以根据实际的不同修改
17.         try {
18.             dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
19.                     "day01/v4/db.properties"));
20.         } catch (IOException e) {
21.             e.printStackTrace();
22.         }
24.         try {
25.             String driveClassName = dbProps.getProperty("jdbc.driverClassName");
26.             String url = dbProps.getProperty("jdbc.url");
27.             String username = dbProps.getProperty("jdbc.username");
28.             String password = dbProps.getProperty("jdbc.password");
30.             String initialSize = dbProps.getProperty("dataSource.initialSize");
31.             String minIdle = dbProps.getProperty("dataSource.minIdle");
32.             String maxIdle = dbProps.getProperty("dataSource.maxIdle");
33.             String maxWait = dbProps.getProperty("dataSource.maxWait");
34.             String maxActive = dbProps.getProperty("dataSource.maxActive");
36.             dataSource = new BasicDataSource();
37.             dataSource.setDriverClassName(driveClassName);
38.             dataSource.setUrl(url);
39.             dataSource.setUsername(username);
40.             dataSource.setPassword(password);
42.             // 初始化连接数
43.             if (initialSize != null)
44.                 dataSource.setInitialSize(Integer.parseInt(initialSize));
46.             // 最小空闲连接
47.             if (minIdle != null)
48.                 dataSource.setMinIdle(Integer.parseInt(minIdle));
50.             // 最大空闲连接
51.             if (maxIdle != null)
52.                 dataSource.setMaxIdle(Integer.parseInt(maxIdle));
54.             // 超时回收时间(以毫秒为单位)
55.             if (maxWait != null)
56.                 dataSource.setMaxWait(Long.parseLong(maxWait));
58.             // 最大连接数
59.             if (maxActive != null) {
60.                 if (!maxActive.trim().equals("0"))
61.                     dataSource.setMaxActive(Integer.parseInt(maxActive));
62.             }
63.         } catch (Exception e) {
64.             e.printStackTrace();
65.             System.out.println("创建连接池失败!请检查设置!!!");
66.         }
67.     }
69.     public static synchronized Connection getConnection() throws SQLException {
70.         if (dataSource == null) {
71.             init();
72.         }
73.         Connection conn = null;
74.         if (dataSource != null) {
75.             conn = dataSource.getConnection();
76.         }
77.         return conn;
78.     }
79. }

隐藏

5 完成NetCTOSS项目中，权限管理模块的角色的DAO设计及实现

详细要求如下：

1. 查询所有角色信息。

2. 添加某个角色。

3. 修改某个角色的角色名称。

参考答案

在课上案例的基础上，完成当前案例。工程结构如图-2所示。

图-2

本案例，在课上案例实现的基础上添加了如下内容：

1. RoleInfo.java为数据库中的role_info表和Java对象的映射；

2. RoleInfoDAO.java 为一个接口，该接口中定义了三个方法，该三个方法的声明如下所示：

 

1. package com.tarena.netctoss;
3. import java.sql.SQLException;
4. import java.util.List;
5. import com.tarena.netctoss.entity.RoleInfo;
7. publicinterface RoleInfoDAO {
8.     /**
9.      * 查询所有的角色
10.      * @return所有角色返回List集合
11.      */
12.     List<RoleInfo> findAll() throws SQLException;
13.     /**
14.      * 新增角色
15.      * @paramrole要添加角色
16.      * @return添加后角色，包含角色ID
17.      */
18.     RoleInfo save(RoleInfo role) throws SQLException;
19.     /**
20.      * 修改某个角色
21.      * @paramrole要修改的角色
22.      * @return返回修改后的角色
23.      */
24.     RoleInfo modify(RoleInfo role) throws SQLException;
25. }

以上三个方法的作用，请参考注释部分。

3. RoleInfoDAOImpl.java 该类继承自BaseDAO，来直接获取创建连接的方法；另外，该类实现了RoleInfoDAO接口，将该接口中的方法做出实现。

4.TestRoleInfoDAO.java 该类用于测试RoleInfoDAOImpl所实现的方法的正确性。

实现此案例需要按照如下步骤进行。

步骤一：创建序列、表以及向表中插入数据

首先，创建名为role_seq的序列；然后，创建名为role_info的表并向该表中插入测试数据，SQL语句如下所示：

 

1. create sequence role_seq;
3. create table role_info(
4.         role_id        number(4)         constraint role_info_id_pk primary key,
5.         name         varchar2(50)     not null
6. );
8. insert into role_info(role_id,name)values(role_seq.nextval,'管理员');
9. insert into role_info(role_id,name)values(role_seq.nextval,'柜台人员');
10. insert into role_info(role_id,name)values(role_seq.nextval,'中层领导');
12. commit;

步骤二：创建数据库中的role_info表和Java对象的映射类RoleInfo

代码如下所示：

 

1. package com.tarena.netctoss.entity;
3. public class RoleInfo {
4.     private int roleId;
5.     private String name;
6.     public int getRoleId() {
7.         return roleId;
8.     }
9.     public void setRoleId(int roleId) {
10.         this.roleId = roleId;
11.     }
12.     public String getName() {
13.         return name;
14.     }
15.     public void setName(String name) {
16.         this.name = name;
17.     }
18.     @Override
19.     public String toString() {
20.         return "RoleInfo [name=" + name + ", roleId=" + roleId + "]";
21.     }
22. }

步骤三：创建RoleInfoDAO接口，该接口中定义了对数据的增改查的功能

代码如下所示：

 

1. package com.tarena.netctoss;
3. import java.sql.SQLException;
4. import java.util.List;
5. import com.tarena.netctoss.entity.RoleInfo;
7. publicinterface RoleInfoDAO {
8.     /**
9.      * 查询所有的角色
10.      * @return所有角色返回List集合
11.      */
12.     List<RoleInfo> findAll() throws SQLException;
13.     /**
14.      * 新增角色
15.      * @paramrole要添加角色
16.      * @return添加后角色，包含角色ID
17.      */
18.     RoleInfo save(RoleInfo role) throws SQLException;
19.     /**
20.      * 修改某个角色
21.      * @paramrole要修改的角色
22.      * @return返回修改后的角色
23.      */
24.     RoleInfo modify(RoleInfo role) throws SQLException;
25. }

步骤四：创建RoleInfoDAOImpl类

创建RoleInfoDAOImpl类，该类继承自BaseDAO，来直接获取创建连接的方法；另外，该类实现了RoleInfoDAO接口，将该接口中的方法做出实现，代码如下所示：

 

1. package com.tarena.netctoss.impl;
3. import java.sql.Connection;
4. import java.sql.PreparedStatement;
5. import java.sql.ResultSet;
6. import java.sql.SQLException;
7. import java.util.ArrayList;
8. import java.util.List;
10. import com.tarena.netctoss.BaseDAO;
11. import com.tarena.netctoss.RoleInfoDAO;
12. import com.tarena.netctoss.entity.RoleInfo;
14. publicclass RoleInfoDAOImpl extends BaseDAO implements RoleInfoDAO {
16.     privatestaticfinal String FIND_ALL = "SELECT role_id,name FROM ROLE_INFO";
18.     privatestaticfinal String MODIFY = "UPDATE ROLE_INFO SET name = ? WHERE role_id=?";
20.     privatestaticfinal String INSERT = "INSERT INTO ROLE_INFO(role_id, name) "
21.             + " VALUES (ROLE_SEQ.NEXTVAL,?)";
23.     @Override
24.     public List<RoleInfo> findAll() throws SQLException {
25.         Connection conn = getConnection();
26.         String sql = FIND_ALL;
27.         PreparedStatement ps = conn.prepareStatement(sql);
28.         ResultSet rs = ps.executeQuery();
29.         RoleInfo role = null;
30.         List<RoleInfo> list = new ArrayList<RoleInfo>();
31.         while (rs.next()) {
32.             role = new RoleInfo();
33.             role.setRoleId(rs.getInt("role_id"));
34.             role.setName(rs.getString("name"));
36.             list.add(role);
37.         }
38.         return list;
39.     }
41.     @Override
42.     public RoleInfo save(RoleInfo role) throws SQLException {
43.         Connection conn = getConnection();
44.         String sql = INSERT;
45.         PreparedStatement ps = conn.prepareStatement(sql,
46.                 new String[] { "role_id" });
48.         ps.setString(1, role.getName());
50.         ps.executeUpdate();
51.         ResultSet rs = ps.getGeneratedKeys();
52.         rs.next();
53.         int id = rs.getInt(1);
54.         role.setRoleId(id);
56.         return role;
57.     }
58.     @Override
59.     public RoleInfo modify(RoleInfo role) throws SQLException {
60.         Connection conn = getConnection();
61.         String sql = MODIFY; // 预先定义好的SQL语句
63.         PreparedStatement ps = conn.prepareStatement(sql);
64.         ps.setString(1, role.getName());
65.         ps.setInt(2, role.getRoleId());
67.         int flag = ps.executeUpdate();
68.         return (flag > 0) ? role : null;
69.     }
70. }

步骤五：创建TestRoleInfoDAO类，用于测试功能是否实现

创建TestRoleInfoDAO类，该类用于测试RoleInfoDAOImpl所实现的方法的正确性，代码如下所示：

 

1. package com.tarena.netctoss;
3. import java.sql.SQLException;
4. import java.util.List;
6. import com.tarena.netctoss.entity.RoleInfo;
7. import com.tarena.netctoss.impl.RoleInfoDAOImpl;
9. public class TestRoleInfoDAO {
10.     public static void main(String[] args) {
11.         TestRoleInfoDAO test=new TestRoleInfoDAO();
12.         //test.testFindAll();
13.         //test.testSave();
14.         test.testModify();
15.     }
16.     public void testModify() {
17.      RoleInfo role=new RoleInfo();
18.      role.setRoleId(10);
19.      role.setName("common");
20.         RoleInfoDAO dao = new RoleInfoDAOImpl();
21.         try {
22.             role=dao.modify(role);
23.             System.out.println(role.toString());
24.         } catch (SQLException e) {
25.             e.printStackTrace();
26.         }
27.     }
28.     public void testSave() {
29.      RoleInfo role=new RoleInfo();
30.      role.setName("admin");
31.         RoleInfoDAO dao = new RoleInfoDAOImpl();
32.         try {
33.             role=dao.save(role);
34.             System.out.println(role.toString());
35.         } catch (SQLException e) {
36.             e.printStackTrace();
37.         }
38.     }
39.     public void testFindAll() {
40.         RoleInfoDAO dao = new RoleInfoDAOImpl();
41.         try {
42.             List<RoleInfo> list = dao.findAll();
43.             for(RoleInfo role : list){
44.                 System.out.println(role.toString());
45.                 System.out.println("---------------------------------");
46.             }
47.         } catch (SQLException e) {
48.             e.printStackTrace();
49.         }
50.     }
51. }