JAVA JDBC DAY03
1 实现账户转账操作
1.1 问题
使用JDBC连接数据库，实现账号转账业务。从A账户转账给B账户500元钱。
1.2 方案
本案例中，要实现转账业务，需要执行两次更新操作，一是更新A账户的金额为在原有金额基础上减去500元；二是更新B账户的金额为在原有金额的基础上增加500元。这两次更新操作要么全部成功、要么全部失败，来表示转账的成功或失败。如果A账户的金额更新为在原有金额基础上减去500元，而B账户却没有更新为在原有金额的基础上增加500元，这样就造成了数据的不一致。我们可以使用事务来控制两次更新操作要么全部成功、要么全部失败。
本案例中，可以使用事务将两次更新操作封装成一个逻辑单元，要么完全执行，要么完全不执行，保证了数据的完整性。
下列方法可实现JDBC事务的基本操作：
- Connection.setAutoCommit(boolean)当参数为false时，关闭自动提交
- Connection.commit() 提交事务
- Connection.rollback() 回滚事务
使用JDBC控制事务的核心代码如下：
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
// 更新数据的SQL语句
String sql1 = "update account set amount = amount - " + amount
+ " where id = '" + from + "'";
String sql2 = "update account set amount = amount + " + amount
+ " where id = '" + to + "'";
// 关闭自动提交
con.setAutoCommit(false);
// 执行SQL语句
stmt.executeUpdate(sql1);
stmt.executeUpdate(sql2);
// 提交
con.commit();
} catch (SQLException e) {
try {
con.rollback();
} catch (SQLException e1) {
System.out.println("回滚事务异常！");
throw new RuntimeException(e1);
}
System.out.println("数据库访问异常!");
throw new RuntimeException(e);
}
1.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：创建Account表，并插入测试数据
在Oracle数据库中，创建表并插入测试数据，SQL语句如下所示：
create table account (
id char(1),
amount number(10,2)
);
insert into account values('A', 1000);
insert into account values('B', 2000);
commit;
步骤二：准备JDBC操作数据库的基本代码
首先，新建类Trans，在该类中新建transfer方法，方法的声明如下所示：
public void transfer(String from, String to, double amount) {}
该方法表示从账户from转账给账户to，转账金额为amount。
然后，准备数据库连接Connection对象、操作SQL语句的Statement对象并进行异常的处理，代码如下所示：
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class Trans {
public void transfer(String from, String to, double amount) {
Connection con = null;
Statement stmt = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
throw new RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
public static void main(String args[]) {
}
}
步骤三：实现转账功能
使用Connection的setAutoCommit方法、commit方法以及rollback方法来控制事务，以确保转账功能正确实现，代码如下所示：
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class Trans {
public void transfer(String from, String to, double amount) {
Connection con = null;
Statement stmt = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
#cold_bold // 更新数据的SQL语句
#cold_bold String sql1 = "update account set amount = amount - " + amount
#cold_bold + " where id = '" + from + "'";
#cold_bold String sql2 = "update account set amount = amount + " + amount
#cold_bold + " where id = '" + to + "'";
#cold_bold // 关闭自动提交
#cold_bold con.setAutoCommit(false);
#cold_bold // 执行SQL语句
#cold_bold stmt.executeUpdate(sql1);
#cold_bold stmt.executeUpdate(sql2);
#cold_bold // 提交
#cold_bold con.commit();
} catch (SQLException e) {
#cold_bold try {
#cold_bold con.rollback();
#cold_bold } catch (SQLException e1) {
#cold_bold System.out.println("回滚事务异常！");
#cold_bold throw new RuntimeException(e1);
#cold_bold }
System.out.println("数据库访问异常!");
throw new RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
public static void main(String args[]) {
}
}
步骤四：测试
在Trans类的main方法中，调用transfer方法，代码如下所示：
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class Trans {
public void transfer(String from, String to, double amount) {
Connection con = null;
Statement stmt = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
// 插入数据的SQL语句
String sql1 = "update account set amount = amount - " + amount
+ " where id = '" + from + "'";
String sql2 = "update account set amount = amount + " + amount
+ " where id = '" + to + "'";
// 关闭自动提交
con.setAutoCommit(false);
// 执行SQL语句
stmt.executeUpdate(sql1);
stmt.executeUpdate(sql2);
// 提交
con.commit();
} catch (SQLException e) {
try {
con.rollback();
} catch (SQLException e1) {
System.out.println("回滚事务异常！");
throw new RuntimeException(e1);
}
System.out.println("数据库访问异常!");
throw new RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
public static void main(String args[]) {
#cold_bold Trans trans = new Trans();
#cold_bold trans.transfer("A", "B", 500);
}
}
运行Trans类，然后查看Oracle数据库中的account表，会发现A账户的金额减少了500元，B账户的金额增加了500元。
1.4 完整代码
本案例中，类Trans的完整代码如下所示：
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class Trans {
public void transfer(String from, String to, double amount) {
Connection con = null;
Statement stmt = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
// 更新数据的SQL语句
String sql1 = "update account set amount = amount - " + amount
+ " where id = '" + from + "'";
String sql2 = "update account set amount = amount + " + amount
+ " where id = '" + to + "'";
// 关闭自动提交
con.setAutoCommit(false);
// 执行SQL语句
stmt.executeUpdate(sql1);
stmt.executeUpdate(sql2);
// 提交
con.commit();
} catch (SQLException e) {
try {
con.rollback();
} catch (SQLException e1) {
System.out.println("回滚事务异常！");
throw new RuntimeException(e1);
}
System.out.println("数据库访问异常!");
throw new RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
public static void main(String args[]) {
Trans trans = new Trans();
trans.transfer("A", "B", 500);
}
}
db.properties文件的内容与之前案例一样没有变化，该文件完整内容如下所示：
jdbc.driverClassName=oracle.jdbc.OracleDriver jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl jdbc.username=scott jdbc.password=tiger #<!-- 初始化连接 --> dataSource.initialSize=10 #<!-- 最大空闲连接 --> dataSource.maxIdle=20 #<!-- 最小空闲连接 --> dataSource.minIdle=5 #最大连接数量 dataSource.maxActive=50 #<!-- 超时等待时间以毫秒为单位 (6000毫秒/1000等于60秒 )--> dataSource.maxWait=1000
ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
public class ConnectionSource {
private static BasicDataSource dataSource = null;
public ConnectionSource() {
}
public static void init() {
Properties dbProps = new Properties();
// 取配置文件可以根据实际的不同修改
try {
dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
"day01/v4/db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
try {
String driveClassName = dbProps.getProperty("jdbc.driverClassName");
String url = dbProps.getProperty("jdbc.url");
String username = dbProps.getProperty("jdbc.username");
String password = dbProps.getProperty("jdbc.password");
String initialSize = dbProps.getProperty("dataSource.initialSize");
String minIdle = dbProps.getProperty("dataSource.minIdle");
String maxIdle = dbProps.getProperty("dataSource.maxIdle");
String maxWait = dbProps.getProperty("dataSource.maxWait");
String maxActive = dbProps.getProperty("dataSource.maxActive");
dataSource = new BasicDataSource();
dataSource.setDriverClassName(driveClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// 初始化连接数
if (initialSize != null)
dataSource.setInitialSize(Integer.parseInt(initialSize));
// 最小空闲连接
if (minIdle != null)
dataSource.setMinIdle(Integer.parseInt(minIdle));
// 最大空闲连接
if (maxIdle != null)
dataSource.setMaxIdle(Integer.parseInt(maxIdle));
// 超时回收时间(以毫秒为单位)
if (maxWait != null)
dataSource.setMaxWait(Long.parseLong(maxWait));
// 最大连接数
if (maxActive != null) {
if (!maxActive.trim().equals("0"))
dataSource.setMaxActive(Integer.parseInt(maxActive));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("创建连接池失败!请检查设置!!!");
}
}
public static synchronized Connection getConnection() throws SQLException {
if (dataSource == null) {
init();
}
Connection conn = null;
if (dataSource != null) {
conn = dataSource.getConnection();
}
return conn;
}
}
2 批量插入Emp数据
2.1 问题
向Emp表中批量插入100条数据，需要插入数据的列为empno、ename以及sal。这三个字段对应的数据分别为empno列的数据通过序列emp_seq自动生成、ename列的数据为字符串“name”+循环次数i组成、sal的数据由随机生成的10000以内的整数构成。
2.2 方案
每循环一次，向数据库插入一条数据，频繁的访问数据库，效率很低。
在Java中专门提供的批处理的API。在对数据库频繁操作时，可以使用JDBC批处理方式提高程序的效率。批处理的主要特点如下：
- 使用同一Connection资源，一次发送多条SQL语句执行。
- 提高应用程序与DB之间的吞吐量，缩短DB的响应时间。
- 与逐条执行SQL的方式相比，需要处理的数据量越大，批处理的优势越明显。
Statement和PreparedStatement都提供了addBatch、executeBatch方法，用于实现缓存SQL语句和批量执行。使用Statement实现批处理的核心代码如下：
for (int i = 0; i < 100; i++) {
// 插入数据的SQL语句
sql = "insert into emp(empno, ename, sal) values("
+ "emp_seq.nextval, 'name" + i + "', "
+ new Random().nextInt(10000) + ")";
// 将SQL语句加入到Batch中
stmt.addBatch(sql);
}
// 执行批处理
stmt.executeBatch();
2.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：在Oracle数据库中创建序列emp_seq
在Oracle数据中创建序列名为emp_seq，该序列的起始值为1、步进为1，SQL语句如下所示：
create sequence emp_seq start with 1 increment by 1;
步骤二：准备JDBC操作数据库的基本代码
首先，新建类Batch，在该类中新建batchAdd方法；然后，准备数据库连接Connection对象、操作SQL语句的Statement对象以及设置事务管理；最后进行异常的处理，代码如下所示：
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
publicclass Batch {
publicvoid batchAdd() {
Connection con = null;
Statement stmt = null;
String sql = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
// 关闭自动提交
con.setAutoCommit(false);
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
}
}
步骤三：批量向Emp表中插入数据
使用Statement的addBatch方法和executeBatch方法，批量向Emp表中插入数据，代码如下所示：
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
publicclass Batch {
publicvoid batchAdd() {
Connection con = null;
Statement stmt = null;
String sql = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
// 关闭自动提交
con.setAutoCommit(false);
#cold_bold
#cold_bold for (int i = 0; i < 100; i++) {
#cold_bold // 插入数据的SQL语句
#cold_bold sql = "insert into emp(empno, ename, sal) values("
#cold_bold + "emp_seq.nextval, 'name" + i + "', "
#cold_bold + new Random().nextInt(10000) + ")";
#cold_bold // 将SQL语句加入到Batch中
#cold_bold stmt.addBatch(sql);
#cold_bold }
#cold_bold // 执行批处理
#cold_bold stmt.executeBatch();
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
}
}
步骤四：测试是否批量插入数据成功
在Batch类的main方法中，调用batchAdd方法，代码如下所示：
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
publicclass Batch {
publicvoid batchAdd() {
Connection con = null;
Statement stmt = null;
String sql = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
// 关闭自动提交
con.setAutoCommit(false);
for (int i = 0; i < 100; i++) {
// 插入数据的SQL语句
sql = "insert into emp(empno, ename, sal) values("
+ "emp_seq.nextval, 'name" + i + "', "
+ new Random().nextInt(10000) + ")";
// 将SQL语句加入到Batch中
stmt.addBatch(sql);
}
// 执行批处理
stmt.executeBatch();
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
Batch batch = new Batch();
batch.batchAdd();
}
}
运行Batch类，然后去查看Oracle数据库中的Emp表，会发现批量向该表中插入了100条记录。
2.4 完整代码
本案例中，Batch类的完整代码如下所示：
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
publicclass Batch {
publicvoid batchAdd() {
Connection con = null;
Statement stmt = null;
String sql = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
// 关闭自动提交
con.setAutoCommit(false);
for (int i = 0; i < 100; i++) {
// 插入数据的SQL语句
sql = "insert into emp(empno, ename, sal) values("
+ "emp_seq.nextval, 'name" + i + "', "
+ new Random().nextInt(10000) + ")";
// 将SQL语句加入到Batch中
stmt.addBatch(sql);
}
// 执行批处理
stmt.executeBatch();
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
Batch batch = new Batch();
batch.batchAdd();
}
}
db.properties文件的内容与之前案例一样没有变化，该文件完整内容如下所示：
jdbc.driverClassName=oracle.jdbc.OracleDriver jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl jdbc.username=scott jdbc.password=tiger #<!-- 初始化连接 --> dataSource.initialSize=10 #<!-- 最大空闲连接 --> dataSource.maxIdle=20 #<!-- 最小空闲连接 --> dataSource.minIdle=5 #最大连接数量 dataSource.maxActive=50 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> dataSource.maxWait=1000
ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
public class ConnectionSource {
private static BasicDataSource dataSource = null;
public ConnectionSource() {
}
public static void init() {
Properties dbProps = new Properties();
// 取配置文件可以根据实际的不同修改
try {
dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
"day01/v4/db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
try {
String driveClassName = dbProps.getProperty("jdbc.driverClassName");
String url = dbProps.getProperty("jdbc.url");
String username = dbProps.getProperty("jdbc.username");
String password = dbProps.getProperty("jdbc.password");
String initialSize = dbProps.getProperty("dataSource.initialSize");
String minIdle = dbProps.getProperty("dataSource.minIdle");
String maxIdle = dbProps.getProperty("dataSource.maxIdle");
String maxWait = dbProps.getProperty("dataSource.maxWait");
String maxActive = dbProps.getProperty("dataSource.maxActive");
dataSource = new BasicDataSource();
dataSource.setDriverClassName(driveClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// 初始化连接数
if (initialSize != null)
dataSource.setInitialSize(Integer.parseInt(initialSize));
// 最小空闲连接
if (minIdle != null)
dataSource.setMinIdle(Integer.parseInt(minIdle));
// 最大空闲连接
if (maxIdle != null)
dataSource.setMaxIdle(Integer.parseInt(maxIdle));
// 超时回收时间(以毫秒为单位)
if (maxWait != null)
dataSource.setMaxWait(Long.parseLong(maxWait));
// 最大连接数
if (maxActive != null) {
if (!maxActive.trim().equals("0"))
dataSource.setMaxActive(Integer.parseInt(maxActive));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("创建连接池失败!请检查设置!!!");
}
}
public static synchronized Connection getConnection() throws SQLException {
if (dataSource == null) {
init();
}
Connection conn = null;
if (dataSource != null) {
conn = dataSource.getConnection();
}
return conn;
}
}
3 插入Dept及其关联的Emp信息
3.1 问题
向Dept表中插入如图-1所示的数据：
图-1
向Emp表中插入如图-2所示的数据：
图-2
其中，Dept表的主键为deptno，Emp表的主键为empno。Emp表的deptno列为外键，该列的数据引用Dept表的主键列deptno的数据。
要求向Dept表插入数据的同时，向Emp表插入数据。Emp表的deptno列的数据为之前插入Dept表的主键列deptno的数据。另外，Dept表的主键列deptno的数据通过序列dept_seq获得，Emp表的主键列empno的数据通过序列emp_seq获得。
3.2 方案
本案例中，向Dept表插入数据的同时，向Emp表插入数据。Emp表的deptno列的数据为之前插入Dept表的主键列deptno的数据。首先，将两次插入操作放在同一个事务中。另外，要获取使用序列dept_seq自动生成主键列deptno的数据，为向Emp表中插入数据时deptno列的数据。
数据库表的主键一般情况下与业务无关，而且通常采用自动生成的方式。Oracle数据库采用sequence的方式产生主键；而其他的一些数据库（如SQLServer、MySQL）具有自动增长主键功能。
在实际开发中，经常需要在插入一条记录后获得刚刚生成记录的主键。
PreparedStatement对象在执行insert操作后会保存数据库产生的自动主键，可调用PreparedStatement的getGeneratedKeys方法获得。getGeneratedKeys的返回值为结果集对象，可以通过结果集获取刚刚生成的主键。使用PreparedStatement获取插入操作时数据库自动生成的主键列数据的核心代码如下：
sql = "insert into dept (deptno, dname, loc) values(dept_seq.nextval,?,?)";
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql, new String[] { "deptno" });
stmt.setString(1, dept.getDname());
stmt.setString(2, dept.getLoc());
// 执行SQL语句
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
int deptno = 0;
if (rs.next()) {
deptno = rs.getInt(1);
}
System.out.print("id:" + deptno);
其中prepareStatement(sql, new String[] { "deptno" })方法的第一个参数表示要执行的SQL语句，第二参数表示SQL语句操作的表的主键列的列名字，并且SQL语句操作的表包含应该返回的自动生成键。如果 SQL 语句不是insert语句，则驱动程序将忽略该数组。
3.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：创建序列dept_seq
在Oracle数据库中创建序列名为dept_seq，该序列的起始值为1、步进为1，SQL语句如下所示：
create sequence dept_seq start with 1 increment by 1;
另外，序列emp_seq可以使用案例“批量插入Emp数据”中创建的该名字的序列。
步骤二：创建Dept类
Dept类为实体类和数据表dept之间的映射，该类的代码如下所示：
public class Dept {
private int deptno;
private String dname;
private String loc;
public Dept() {
super();
}
public Dept(int deptno, String dname, String loc) {
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
步骤三：准备JDBC操作数据库的基本代码
首先，新建类DeptJoinEmp，在该类中新建addEmp方法；然后，准备数据库连接Connection对象、操作SQL语句的Statement对象以及设置事务管理；最后进行异常的处理，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
publicclass DeptJoinEmp {
publicvoid addEmp(Emp emp, Dept dept) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = null;
try {
con = ConnectionSource.getConnection();
// 关闭自动提交
con.setAutoCommit(false);
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
}
}
步骤四：向Dept表中插入数据并获取自动生成的主键
向Dept表中插入数据，并使用PreparedStatement的getGeneratedKeys方法获得刚刚生成的主键，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
publicclass DeptJoinEmp {
publicvoid addEmp(Emp emp, Dept dept) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = null;
try {
con = ConnectionSource.getConnection();
// 关闭自动提交
con.setAutoCommit(false);
#cold_bold // 插入主表
#cold_bold sql = "insert into dept (deptno, dname, loc) values(dept_seq.nextval,?,?)";
#cold_boldstmt = con.prepareStatement(sql, new String[] { "deptno" });
#cold_bold stmt.setString(1, dept.getDname());
#cold_bold stmt.setString(2, dept.getLoc());
#cold_bold
#cold_bold // 执行SQL语句
#cold_bold stmt.executeUpdate();
#cold_bold rs = stmt.getGeneratedKeys();
#cold_bold int deptno = 0;
#cold_bold if (rs.next()) {
#cold_bold deptno = rs.getInt(1);
#cold_bold }
#cold_bold System.out.print("id:" + deptno);
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
}
}
步骤五：向Emp表中插入数据
将上一步骤中获取到的Dept表的主键列数据，作为Emp表的外键列deptno的数据，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
publicclass DeptJoinEmp {
publicvoid addEmp(Emp emp, Dept dept) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = null;
try {
con = ConnectionSource.getConnection();
// 关闭自动提交
con.setAutoCommit(false);
// 插入主表
sql = "insert into dept (deptno, dname, loc) values(dept_seq.nextval,?,?)";
stmt = con.prepareStatement(sql, new String[] { "deptno" });
stmt.setString(1, dept.getDname());
stmt.setString(2, dept.getLoc());
// 执行SQL语句
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
int deptno = 0;
if (rs.next()) {
deptno = rs.getInt(1);
}
System.out.print("id:" + deptno);
// 插入从表
#cold_bold sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)" +
#cold_bold " values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
#cold_bold stmt = con.prepareStatement(sql);
#cold_bold stmt.setString(1, emp.getEname());
#cold_bold stmt.setString(2, emp.getJob());
#cold_bold stmt.setInt(3, emp.getMgr());
#cold_bold stmt.setString(4, emp.getHiredate());
#cold_bold stmt.setDouble(5, emp.getSal());
#cold_bold stmt.setDouble(6, emp.getComm());
#cold_bold stmt.setInt(7, deptno);
#cold_bold stmt.executeUpdate();
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
}
}
步骤六：测试
按照图-1、图-2中数据构造Dept对象和Emp对象，作为参数传递给addEmp方法，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
publicclass DeptJoinEmp {
publicvoid addEmp(Emp emp, Dept dept) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = null;
try {
// 插入主表
sql = "insert into dept (deptno, dname, loc) values(dept_seq.nextval,?,?)";
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql, new String[] { "deptno" });
stmt.setString(1, dept.getDname());
stmt.setString(2, dept.getLoc());
// 关闭自动提交
con.setAutoCommit(false);
// 执行SQL语句
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
int deptno = 0;
if (rs.next()) {
deptno = rs.getInt(1);
}
System.out.print("id:" + deptno);
// 插入从表
sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)" +
" values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
stmt = con.prepareStatement(sql);
stmt.setString(1, emp.getEname());
stmt.setString(2, emp.getJob());
stmt.setInt(3, emp.getMgr());
stmt.setString(4, emp.getHiredate());
stmt.setDouble(5, emp.getSal());
stmt.setDouble(6, emp.getComm());
stmt.setInt(7, deptno);
stmt.executeUpdate();
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
#cold_bold Dept dept = new Dept();
#cold_bold dept.setDname("开发部");
#cold_bold dept.setLoc("beijing");
#cold_bold
#cold_bold Emp emp = new Emp();
#cold_bold emp.setEname("donna");
#cold_bold emp.setJob("clerk");
#cold_bold emp.setHiredate("2014-03-12");
#cold_bold emp.setSal(5000.00);
#cold_bold emp.setComm(400.00);
#cold_bold
#cold_bold DeptJoinEmp operation = new DeptJoinEmp();
#cold_bold operation.addEmp(emp, dept);
}
}
运行上述代码，向Dept表插入数据的同时，也向Emp表插入了数据。Emp表的deptno列的数据为刚刚插入Dept表的主键列deptno的数据。
3.4 完整代码
本案例中，Dept类的完整代码如下所示：
public class Dept {
private int deptno;
private String dname;
private String loc;
public Dept() {
super();
}
public Dept(int deptno, String dname, String loc) {
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
Emp类的完整代码如下：
public class Emp {
private int empNo;
private String ename;
private String job;
private int mgr;
private String hiredate;
private double sal;
private double comm;
private int deptno;
public Emp() {
super();
}
public Emp(int empNo, String ename, String job, int mgr, String hiredate,
double sal, double comm, int deptno) {
super();
this.empNo = empNo;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpNo() {
return empNo;
}
public void setEmpNo(int empNo) {
this.empNo = empNo;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
DeptJoinEmp类的完整代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
publicclass DeptJoinEmp {
publicvoid addEmp(Emp emp, Dept dept) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = null;
try {
con = ConnectionSource.getConnection();
// 关闭自动提交
con.setAutoCommit(false);
// 插入主表
sql = "insert into dept (deptno, dname, loc) values(dept_seq.nextval,?,?)";
stmt = con.prepareStatement(sql, new String[] { "deptno" });
stmt.setString(1, dept.getDname());
stmt.setString(2, dept.getLoc());
// 执行SQL语句
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
int deptno = 0;
if (rs.next()) {
deptno = rs.getInt(1);
}
System.out.print("id:" + deptno);
// 插入从表
sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)" +
" values(emp_seq.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
stmt = con.prepareStatement(sql);
stmt.setString(1, emp.getEname());
stmt.setString(2, emp.getJob());
stmt.setInt(3, emp.getMgr());
stmt.setString(4, emp.getHiredate());
stmt.setDouble(5, emp.getSal());
stmt.setDouble(6, emp.getComm());
stmt.setInt(7, deptno);
stmt.executeUpdate();
// 提交
con.commit();
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicstaticvoid main(String args[]) {
Dept dept = new Dept();
dept.setDname("开发部");
dept.setLoc("beijing");
Emp emp = new Emp();
emp.setEname("donna");
emp.setJob("clerk");
emp.setHiredate("2014-03-12");
emp.setSal(5000.00);
emp.setComm(400.00);
DeptJoinEmp operation = new DeptJoinEmp();
operation.addEmp(emp, dept);
}
}
db.properties文件的内容与之前案例一样没有变化，该文件完整内容如下所示：
jdbc.driverClassName=oracle.jdbc.OracleDriver jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl jdbc.username=scott jdbc.password=tiger #<!-- 初始化连接 --> dataSource.initialSize=10 #<!-- 最大空闲连接 --> dataSource.maxIdle=20 #<!-- 最小空闲连接 --> dataSource.minIdle=5 #最大连接数量 dataSource.maxActive=50 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> dataSource.maxWait=1000
ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
public class ConnectionSource {
private static BasicDataSource dataSource = null;
public ConnectionSource() {
}
public static void init() {
Properties dbProps = new Properties();
// 取配置文件可以根据实际的不同修改
try {
dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
"day01/v4/db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
try {
String driveClassName = dbProps.getProperty("jdbc.driverClassName");
String url = dbProps.getProperty("jdbc.url");
String username = dbProps.getProperty("jdbc.username");
String password = dbProps.getProperty("jdbc.password");
String initialSize = dbProps.getProperty("dataSource.initialSize");
String minIdle = dbProps.getProperty("dataSource.minIdle");
String maxIdle = dbProps.getProperty("dataSource.maxIdle");
String maxWait = dbProps.getProperty("dataSource.maxWait");
String maxActive = dbProps.getProperty("dataSource.maxActive");
dataSource = new BasicDataSource();
dataSource.setDriverClassName(driveClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// 初始化连接数
if (initialSize != null)
dataSource.setInitialSize(Integer.parseInt(initialSize));
// 最小空闲连接
if (minIdle != null)
dataSource.setMinIdle(Integer.parseInt(minIdle));
// 最大空闲连接
if (maxIdle != null)
dataSource.setMaxIdle(Integer.parseInt(maxIdle));
// 超时回收时间(以毫秒为单位)
if (maxWait != null)
dataSource.setMaxWait(Long.parseLong(maxWait));
// 最大连接数
if (maxActive != null) {
if (!maxActive.trim().equals("0"))
dataSource.setMaxActive(Integer.parseInt(maxActive));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("创建连接池失败!请检查设置!!!");
}
}
public static synchronized Connection getConnection() throws SQLException {
if (dataSource == null) {
init();
}
Connection conn = null;
if (dataSource != null) {
conn = dataSource.getConnection();
}
return conn;
}
}
4 实现Emp数据的分页查询（Oracle和MySQL）
4.1 问题
使用JDBC分别连接Oracle数据库和MySQL数据库，实现对Emp表数据的分页查询功能。
4.2 方案
对于较大的数据量，通常采用分页查询的方式。不同的数据库产品有不同的数据库级的分页查询策略。例如：Oracle通常使用rownum的方式；而MySQL使用limit的方式。
Oracle采用rownum和子查询实现分页查询，SQL语句如下：
select * from (select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from (select * from emp order by empno)) where rn between 6 and 10;
上述SQL语句的功能为按照员工编号升序排列员工信息，获取排序后第6至10位之间的5条员工信息。
实现上述功能的MySQL数据库的SQL语句如下：
select * from emp order by empno limit 5,5；
MySQL中使用limit关键字实现分页查询。其中，limit后第一个参数为开始获取数据的行号(从0开始)，第二个参数为获取记录的行数。第二个参数可省略，表示从第一个参数开始，获取后续所有记录。
4.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：添加方法findByPageOracle方法，并构建该方法的骨架代码
首先，在EmpDAO类中添加方法findByPageOracle，该方法的声明如下所示：
publicvoid findByPageOracle(int page, int pageSize) {}
其中，参数page表示要查询的页数、参数pageSize表示每页显示的记录数。
然后，构建findByPageOracle方法的骨架代码，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import day02.ConnectionSource;
import day02.Emp;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
emp.setSal(4500.00);
// dao.update(emp);
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
步骤二：定义SQL语句
在findByPageOracle方法中，定义变量sql_total以及sql来表示两条SQL语句，一条用于查询Emp表的总记录数，另一条作为分页的SQL语句，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import day02.ConnectionSource;
import day02.Emp;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
emp.setSal(4500.00);
// dao.update(emp);
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
#cold_bold String sql_total = "select count(*) from emp";
#cold_bold String sql = "select * from "
#cold_bold + "(select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from "
#cold_bold + "(select * from emp order by empno) )"
#cold_bold + " where rn between ? and ?";
try {
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
步骤三：查询Emp表的总记录数
获取数据库连接，使用PreparedStatement执行SQL语句（sql_total变量定义的SQL语句），获取数据库中Emp表的总记录数，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import day02.ConnectionSource;
import day02.Emp;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
emp.setSal(4500.00);
// dao.update(emp);
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
#cold_bold int total = -1;// 总记录数
String sql_total = "select count(*) from emp";
String sql = "select * from "
+ "(select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from "
+ "(select * from emp order by empno) )"
+ " where rn between ? and ?";
try {
#cold_bold con = ConnectionSource.getConnection();
#cold_bold stmt = con.prepareStatement(sql_total);
#cold_bold
#cold_bold // 获得总的记录数
#cold_bold rs = stmt.executeQuery();
#cold_bold if (rs.next()) {
#cold_bold total = rs.getInt(1);
#cold_bold }
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
步骤四：计算总页数
将总记录数与每页记录数取余数，如果余数为0，则总页数等于总记录数除以每页记录数的商；如果余数不为0，则总页数等于总记录数除以每页记录数的商的基础上加1，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import day02.ConnectionSource;
import day02.Emp;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
emp.setSal(4500.00);
// dao.update(emp);
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int total = -1;// 总记录数
int pages = -1;// 总页数
String sql_total = "select count(*) from emp";
String sql = "select * from "
+ "(select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from "
+ "(select * from emp order by empno) )"
+ " where rn between ? and ?";
try {
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql_total);
// 获得总的记录数
rs = stmt.executeQuery();
if (rs.next()) {
total = rs.getInt(1);
}
// 计算总共需要多少页
#cold_bold int mod = total % pageSize;
#cold_bold if (mod == 0)
#cold_bold pages = total / pageSize;
#cold_bold else
#cold_bold pages = total / pageSize + 1;
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
步骤五：边界判断
如果要查看的页码大于总页数，则要查看的页码等于总页数；如果要查看的页码小于1，则要查看的页码等于1，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import day02.ConnectionSource;
import day02.Emp;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
emp.setSal(4500.00);
// dao.update(emp);
// 4.findByPage
dao.findByPageOracle(2, 3);// 查看第二页，每页3条
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int total = -1;// 总记录数
int pages = -1;// 总页数
String sql_total = "select count(*) from emp";
String sql = "select * from "
+ "(select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from "
+ "(select * from emp order by empno) )"
+ " where rn between ? and ?";
try {
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql_total);
// 获得总的记录数
rs = stmt.executeQuery();
if (rs.next()) {
total = rs.getInt(1);
}
// 计算总共需要多少页
int mod = total % pageSize;
if (mod == 0)
pages = total / pageSize;
else
pages = total / pageSize + 1;
// 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
#cold_bold if (page > pages){
#cold_bold page = pages;
#cold_bold }elseif (page < 1) {
#cold_bold page = 1;
#cold_bold }
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
步骤六：计算取记录的起始位置和结束位置
起始位置（begin）的计算公式如下：
int begin = (page - 1) * pageSize + 1;
结束位置（end）的计算公式如下：
int end = begin + pageSize - 1;
在findByPageOracle方法中的实现代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import day02.ConnectionSource;
import day02.Emp;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
emp.setSal(4500.00);
// dao.update(emp);
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int total = -1;// 总记录数
int pages = -1;// 总页数
String sql_total = "select count(*) from emp";
String sql = "select * from "
+ "(select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from "
+ "(select * from emp order by empno) )"
+ " where rn between ? and ?";
try {
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql_total);
// 获得总的记录数
rs = stmt.executeQuery();
if (rs.next()) {
total = rs.getInt(1);
}
// 计算总共需要多少页
int mod = total % pageSize;
if (mod == 0)
pages = total / pageSize;
else
pages = total / pageSize + 1;
// 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
if (page > pages)
page = pages;
elseif (page < 1) {
page = 1;
}
#cold_bold int begin = (page - 1) * pageSize + 1;
#cold_bold int end = begin + pageSize - 1;
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
步骤七：执行分页查询SQL语句
在findByPageOracle方法中执行分页查询的代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import day02.ConnectionSource;
import day02.Emp;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
emp.setSal(4500.00);
// dao.update(emp);
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int total = -1;// 总记录数
int pages = -1;// 总页数
String sql_total = "select count(*) from emp";
String sql = "select * from "
+ "(select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from "
+ "(select * from emp order by empno) )"
+ " where rn between ? and ?";
try {
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql_total);
// 获得总的记录数
rs = stmt.executeQuery();
if (rs.next()) {
total = rs.getInt(1);
}
// 计算总共需要多少页
int mod = total / pageSize;
if (mod == 0)
pages = total / pageSize;
else
pages = total / pageSize + 1;
// 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
if (page > pages)
page = pages;
elseif (page < 1) {
page = 1;
}
int begin = (page - 1) * pageSize + 1;
int end = begin + pageSize - 1;
#cold_bold stmt = con.prepareStatement(sql);
#cold_bold stmt.setInt(1, begin);
#cold_bold stmt.setInt(2, end);
#cold_bold rs = stmt.executeQuery();
#cold_bold while (rs.next()) {
#cold_bold System.out.println(rs.getInt("empno") + ","
#cold_bold + rs.getString("ename") + ","
#cold_bold + rs.getDouble("sal") + "," + rs.getDate("hiredate"));
#cold_bold }
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
步骤八：测试
在EmpDAO类的main方法中调用findByPageOracle方法，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import day02.ConnectionSource;
import day02.Emp;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
//Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
//emp.setSal(4500.00);
// dao.update(emp);
// 4.findByPageOracle
#cold_bold dao.findByPageOracle(2, 3);// 查看第二页，每页3条
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int total = -1;// 总记录数
int pages = -1;// 总页数
String sql_total = "select count(*) from emp";
String sql = "select * from "
+ "(select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from "
+ "(select * from emp order by empno) )"
+ " where rn between ? and ?";
try {
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql_total);
// 获得总的记录数
rs = stmt.executeQuery();
if (rs.next()) {
total = rs.getInt(1);
}
// 计算总共需要多少页
int mod = total / pageSize;
if (mod == 0)
pages = total / pageSize;
else
pages = total / pageSize + 1;
// 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
if (page > pages)
page = pages;
elseif (page < 1) {
page = 1;
}
int begin = (page - 1) * pageSize + 1;
int end = begin + pageSize - 1;
stmt = con.prepareStatement(sql);
stmt.setInt(1, begin);
stmt.setInt(2, end);
rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("empno") + ","
+ rs.getString("ename") + ","
+ rs.getDouble("sal") + "," + rs.getDate("hiredate"));
}
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
运行EmpDAO类，在控制台会输出第二页的三条数据。
步骤九：连接MySQL数据库，实现对Emp表中数据的分页查询
连接MySQL数据库，实现对Emp表中数据的分页查询，与连接Oracle是类似的。需要注意的是将db.properties文件中连接数据库的信息改为与MySQL数据库相关的，该文件内容如下：
#jdbc.driverClassName=oracle.jdbc.OracleDriver #jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl #jdbc.username=scott #jdbc.password=tiger jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/tts7 jdbc.username=root jdbc.password=root #<!-- 初始化连接 --> dataSource.initialSize=10 #<!-- 最大空闲连接 --> dataSource.maxIdle=20 #<!-- 最小空闲连接 --> dataSource.minIdle=5 #最大连接数量 dataSource.maxActive=50 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> dataSource.maxWait=1000
在EmpDAO类中添加findByPageMySQL方法，实现连接MySQL数据库，实现对Emp表中数据的分页查询，代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
//Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
//emp.setSal(4500.00);
// dao.update(emp);
// 4.findByPageOracle
//dao.findByPageOracle(2, 3);// 查看第二页，每页3条
// 5.findByPageMySQL
dao.findByPageMySQL(2, 3);// 查看第二页，每页3条
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
... ...
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
#cold_bold publicvoid findByPageMySQL(int page, int pageSize) {
#cold_bold Connection con = null;
#cold_bold PreparedStatement stmt = null;
#cold_bold ResultSet rs = null;
#cold_bold int total = -1;// 总记录数
#cold_bold int pages = -1;// 总页数
#cold_bold
#cold_bold String sql_total = "select count(*) from emp";
#cold_bold String sql = "select * from emp order by empno limit ?,?";
#cold_bold
#cold_bold try {
#cold_bold con = ConnectionSource.getConnection();
#cold_bold stmt = con.prepareStatement(sql_total);
#cold_bold
#cold_bold // 获得总的记录数
#cold_bold rs = stmt.executeQuery();
#cold_bold if (rs.next()) {
#cold_bold total = rs.getInt(1);
#cold_bold }
#cold_bold System.out.println(total);
#cold_bold // 计算总共多少页
#cold_bold int mod = total % pageSize;
#cold_bold if (mod == 0)
#cold_bold pages = total / pageSize;
#cold_bold else
#cold_bold pages = total / pageSize + 1;
#cold_bold
#cold_bold // 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
#cold_bold if (page > pages){
#cold_bold page = pages;
#cold_bold }elseif (page < 1) {
#cold_bold page = 1;
#cold_bold }
#cold_bold System.out.println(sql);
#cold_bold int start = (page - 1) * pageSize;
#cold_bold stmt = con.prepareStatement(sql);
#cold_bold stmt.setInt(1, start);
#cold_bold stmt.setInt(2, pageSize);
#cold_bold rs = stmt.executeQuery();
#cold_bold while (rs.next()) {
#cold_bold System.out.println(rs.getInt("empno") + ","
#cold_bold + rs.getString("ename") + ","
#cold_bold + rs.getDouble("sal") + "," + rs.getDate("hiredate"));
#cold_bold }
#cold_bold } catch (SQLException e) {
#cold_bold System.out.println("数据库访问异常!");
#cold_bold thrownew RuntimeException(e);
#cold_bold } finally {
#cold_bold try {
#cold_bold if (rs != null) {
#cold_bold rs.close();
#cold_bold }
#cold_bold if (stmt != null) {
#cold_bold stmt.close();
#cold_bold }
#cold_bold if (con != null) {
#cold_bold con.close();
#cold_bold }
#cold_bold } catch (SQLException e) {
#cold_bold System.out.println("释放资源时发生异常");
#cold_bold }
#cold_bold }
#cold_bold }
publicvoid findAll() {
... ...
}
publicvoid add(Emp emp) {
... ...
}
publicvoid update(Emp emp) {
... ...
}
}
4.4 完整代码
本案例中，EmpDAO类的完整代码如下所示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
publicclass EmpDAO {
publicstaticvoid main(String[] args) {
// 1.select
EmpDAO dao = new EmpDAO();
// dao.findAll();
// 2.insert
//Emp emp = new Emp(1001, "rose", "Analyst", 7901, "2014-05-01", 3000.00,
500.00, 10);
// dao.add(emp);
// 3.update
//emp.setSal(4500.00);
// dao.update(emp);
// 4.findByPageOracle
//dao.findByPageOracle(2, 3);// 查看第二页，每页3条
// 5.findByPageMySQL
dao.findByPageMySQL(2, 3);// 查看第二页，每页3条
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageOracle(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int total = -1;// 总记录数
int pages = -1;// 总页数
String sql_total = "select count(*) from emp";
String sql = "select * from "
+ "(select rownum rn, empno, ename, job,mgr, hiredate, sal, comm, deptno from "
+ "(select * from emp order by empno) )"
+ " where rn between ? and ?";
try {
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql_total);
// 获得总的记录数
rs = stmt.executeQuery();
if (rs.next()) {
total = rs.getInt(1);
}
// 计算总共需要多少页
int mod = total % pageSize;
if (mod == 0)
pages = total / pageSize;
else
pages = total / pageSize + 1;
// 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
if (page > pages)
page = pages;
elseif (page < 1) {
page = 1;
}
int begin = (page - 1) * pageSize + 1;
int end = begin + pageSize - 1;
stmt = con.prepareStatement(sql);
stmt.setInt(1, begin);
stmt.setInt(2, end);
rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("empno") + ","
+ rs.getString("ename") + "," + ","
+ rs.getDouble("sal") + "," + rs.getDate("hiredate"));
}
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
/**
*
* @param page
* 要查看第几页
* @param pageSize
* 每页记录数
*/
publicvoid findByPageMySQL(int page, int pageSize) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int total = -1;// 总记录数
int pages = -1;// 总页数
String sql_total = "select count(*) from emp";
String sql = "select * from emp order by empno limit ?,?";
try {
con = ConnectionSource.getConnection();
stmt = con.prepareStatement(sql_total);
// 获得总的记录数
rs = stmt.executeQuery();
if (rs.next()) {
total = rs.getInt(1);
}
System.out.println(total);
// 计算总共需要多少页
int mod = total % pageSize;
if (mod == 0)
pages = total / pageSize;
else
pages = total / pageSize + 1;
// 如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页
if (page > pages)
page = pages;
elseif (page < 1) {
page = 1;
}
System.out.println(sql);
int start = (page - 1) * pageSize;
stmt = con.prepareStatement(sql);
stmt.setInt(1, start);
stmt.setInt(2, pageSize);
rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("empno") + ","
+ rs.getString("ename") + ","
+ rs.getDouble("sal") + "," + rs.getDate("hiredate"));
}
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源时发生异常");
}
}
}
publicvoid findAll() {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
rs = stmt
.executeQuery("select empno, ename, sal, hiredate from emp");
while (rs.next()) {
System.out.println(rs.getInt("empno") + ","
+ rs.getString("ename") + ","
+ rs.getDouble("sal") + "," + rs.getDate("hiredate"));
}
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源发生异常");
}
}
}
publicvoid add(Emp emp) {
Connection con = null;
Statement stmt = null;
int flag = -1;
String sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values("
+ emp.getEmpNo()
+ ", "
+ " '"
+ emp.getEname()
+ "', "
+ " '"
+ emp.getJob()
+ "', "
+ emp.getMgr()
+ ","
+ "to_date('"
+ emp.getHiredate()
+ "','yyyy-mm-dd'), "
+ emp.getSal()
+ ", "
+ emp.getComm() + ", " + emp.getDeptno() + ")";
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
flag = stmt.executeUpdate(sql);
if (flag > 0) {
System.out.println("新增记录成功!");
}
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源发生异常");
}
}
}
publicvoid update(Emp emp) {
Connection con = null;
Statement stmt = null;
int flag = -1;
String sql = "update emp set sal = " + emp.getSal() + "," + " comm = "
+ emp.getComm() + " where empno = " + emp.getEmpNo();
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
flag = stmt.executeUpdate(sql);
if (flag > 0) {
System.out.println("更新记录成功!");
}
} catch (SQLException e) {
System.out.println("数据库访问异常!");
thrownew RuntimeException(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
System.out.println("释放资源发生异常");
}
}
}
}
db.properties文件的完整内容如下所示：
#jdbc.driverClassName=oracle.jdbc.OracleDriver #jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl #jdbc.username=scott #jdbc.password=tiger jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/tts7 jdbc.username=root jdbc.password=root #<!-- 初始化连接 --> dataSource.initialSize=10 #<!-- 最大空闲连接 --> dataSource.maxIdle=20 #<!-- 最小空闲连接 --> dataSource.minIdle=5 #最大连接数量 dataSource.maxActive=50 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> dataSource.maxWait=1000
ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
public class ConnectionSource {
private static BasicDataSource dataSource = null;
public ConnectionSource() {
}
public static void init() {
Properties dbProps = new Properties();
// 取配置文件可以根据实际的不同修改
try {
dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
"day01/v4/db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
try {
String driveClassName = dbProps.getProperty("jdbc.driverClassName");
String url = dbProps.getProperty("jdbc.url");
String username = dbProps.getProperty("jdbc.username");
String password = dbProps.getProperty("jdbc.password");
String initialSize = dbProps.getProperty("dataSource.initialSize");
String minIdle = dbProps.getProperty("dataSource.minIdle");
String maxIdle = dbProps.getProperty("dataSource.maxIdle");
String maxWait = dbProps.getProperty("dataSource.maxWait");
String maxActive = dbProps.getProperty("dataSource.maxActive");
dataSource = new BasicDataSource();
dataSource.setDriverClassName(driveClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// 初始化连接数
if (initialSize != null)
dataSource.setInitialSize(Integer.parseInt(initialSize));
// 最小空闲连接
if (minIdle != null)
dataSource.setMinIdle(Integer.parseInt(minIdle));
// 最大空闲连接
if (maxIdle != null)
dataSource.setMaxIdle(Integer.parseInt(maxIdle));
// 超时回收时间(以毫秒为单位)
if (maxWait != null)
dataSource.setMaxWait(Long.parseLong(maxWait));
// 最大连接数
if (maxActive != null) {
if (!maxActive.trim().equals("0"))
dataSource.setMaxActive(Integer.parseInt(maxActive));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("创建连接池失败!请检查设置!!!");
}
}
public static synchronized Connection getConnection() throws SQLException {
if (dataSource == null) {
init();
}
Connection conn = null;
if (dataSource != null) {
conn = dataSource.getConnection();
}
return conn;
}
}
5 完成NetCTOSS项目中，账务账号的DAO设计及实现
5.1 问题
完成NetCTOSS项目中，账务账号的DAO设计及实现,详细要求如下：
1. 新增账务账号。
2. 修改某个账务账号的信息。
3. 修改某个账务账号的状态。
4. 查询所有的账务账号。
5. 根据某个账务账号ID查询该账务账号的全部信息。
5.2 方案
在企业开发时，通常采用分层模式，常用的层次划分为表现层+控制层+业务层+持久层+数据源。持久层的功能是通过某些技术或框架将数据库的内容映射成对象，通过操作这些对象实现对数据库的操作。其主要目的是弥补业务对象和数据源关系表之间的差异，便于对数据库操作。持久层可采用JDBC、Hibernate、Mybatis等技术实现。
本案例采用DAO模式通过JDBC来实现持久层。DAO模式就是Data Access Object，即数据访问对象。它存在于数据源和业务层之间，封装了对数据库的访问细节，例如数据库连接、发送执行SQL语句和连接资源的关闭等。DAO的主要目的是将底层数据访问操作与高层业务逻辑操作完全分开，为业务层提供透明的数据访问服务，增强程序的灵活性。DAO组件封装了对数据表的操作，为业务组件提供数据访问服务。在业务组件中，有些简单的业务处理，仅需要使用某一个DAO组件的一个方法就可以完成；但有些业务处理比较复杂，需要使用若干个DAO组件的方法完成。为了保障业务的完整性和DAO组件的重用性，因此不能将事务控制写在DAO组件的每个方法内，而应该是将事务控制独立封装，然后在业务方法中应用。本案例的工程结构如图-3所示。
图-3
对图-3中的工程文件的介绍如下：
1. Account.java为数据库中的Account表和Java对象的映射；
2. db.properties封装了连接数据的信息；
3. BaseBAO.java 该类中封装了创建数据库连接方法以及事务处理；
4. AccountDAO.java 为一个接口，该接口中定义了五个方法，该五个方法的声明如下所示：
package com.tarena.netctoss;
import java.sql.SQLException;
import java.util.List;
import com.tarena.netctoss.entity.Account;
public interface AccountDAO {
/**
* 根据某个账务账号ID查询该账务账号的全部信息
* @param id 账务账号ID
* @return 某个账务账号的全部信息，为一个Account对象
* @throws SQLException
*/
Account findById(Integer id) throws SQLException;
/**
* 查询所有的账务账号
* @return 所有账务账号返回List集合
* @throws SQLException
*/
List<Account> findAll() throws SQLException;
/**
* 新增账务账号
* @param account 要添加的账务账号
* @return 添加后账务账号，包含账号账号ID
* @throws SQLException
*/
Account save(Account account) throws SQLException;
/**
* 修改某个账务账号的信息
* @param account 要修改的账务账号
* @return 返回修改后的账务账号
* @throws SQLException
*/
Account modify(Account account) throws SQLException;
/**
* 修改某个账务账号的状态
* @param account 要修改状态的账务账号
* @return 返回修改状态后的账务账号
* @throws SQLException
*/
Account modifyStatus(Account account) throws SQLException;
}
以上五个方法的作用，请参考注释部分。
5. AccountDAOImpl.java该类继承自BaseDAO，来直接获取创建连接的方法；另外，该类实现了AccountDAO接口，将该接口中的方法做出实现。
6. TestAccountDAO.java该类用于测试AccountDAOImpl所实现的方法的正确性。
5.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：准备环境
首先，创建名为netctoss的工程；然后，在该工程下创建lib目录，存储实现该工程的功能所需的jar包；最后，将所需的jar导入到该工程环境中。
步骤二：创建序列、表以及向表中插入数据
首先，创建名为account_seq的序列；然后，创建名为Account的表并向该表中插入测试数据，SQL语句如下所示：
create sequence account_seq;
create table account(
account_id number(9) constraint account_id_pk primary key,
recommender_id number(9) constraint account_recommender_id_fk
references account(account_id),
login_name varchar2(30) not null
constraint account_login_name_uk unique,
login_passwd varchar2(30) not null,
status char(1) constraint account_status_ck
check (status in (0,1,2)) not null,
create_date date default sysdate,
pause_date date,
close_date date,
real_name varchar2(20) not null,
idcard_no char(18) not null
constraint account_incard_no unique,
birthdate date,
gender char(1) constraint account_gender_ck
check (gender in (0,1)) not null,
occupation varchar2(50),
telephone varchar2(15) not null,
email varchar2(50),
mailaddress varchar2(200),
zipcode char(6),
qq varchar2(15),
last_login_time date,
last_login_ip varchar2(15)
);
INSERT INTO ACCOUNT(ACCOUNT_ID, RECOMMENDER_ID, LOGIN_NAME, LOGIN_PASSWD,
STATUS, CREATE_DATE, PAUSE_DATE, CLOSE_DATE, REAL_NAME, IDCARD_NO, BIRTHDATE,
GENDER,OCCUPATION,TELEPHONE,EMAIL,MAILADDRESS,ZIPCODE,QQ,LAST_LOGIN_TIME,LAST_LOGIN_IP)
VALUES (ACCOUNT_SEQ.NEXTVAL,null, 'a', '123', '0', SYSDATE, NULL, NULL,'zhangsan', '1234',
to_date('1981-01-01','yyyy-mm-dd'), '0', 'job','123' , 'a@b.c', 'test avenue', '1223', '1234', SYSDATE, '192')
步骤三：创建据库中的Account表和Java对象的映射类Account
代码如下所示：
package com.tarena.netctoss.entity;
import java.util.Date;
publicclass Account {
privateint id;
privateint recommenderId;
private String loginName;
private String loginPasswd;
private String status;
private Date createDate;
private Date pauseDate;
private Date closeDate;
private String realName;
private String idcardNo;
private Date birthdate;
private String gender;
private String occupation;
private String telephone;
private String email;
private String mailaddress;
private String zipcode;
private String qq;
private Date lastLoginTime;
private String lastLoginIp;
public Account() {
super();
}
public Account(int id, int recommenderId, String loginName,
String loginPasswd, String status, Date createDate, Date pauseDate,
Date closeDate, String realName, String idcardNo, Date birthdate,
String gender, String occupation, String telephone, String email,
String mailaddress, String zipcode, String qq, Date lastLoginTime,
String lastLoginIp) {
super();
this.id = id;
this.recommenderId = recommenderId;
this.loginName = loginName;
this.loginPasswd = loginPasswd;
this.status = status;
this.createDate = createDate;
this.pauseDate = pauseDate;
this.closeDate = closeDate;
this.realName = realName;
this.idcardNo = idcardNo;
this.birthdate = birthdate;
this.gender = gender;
this.occupation = occupation;
this.telephone = telephone;
this.email = email;
this.mailaddress = mailaddress;
this.zipcode = zipcode;
this.qq = qq;
this.lastLoginTime = lastLoginTime;
this.lastLoginIp = lastLoginIp;
}
publicint getId() {
return id;
}
publicvoid setId(int id) {
this.id = id;
}
publicint getRecommenderId() {
return recommenderId;
}
publicvoid setRecommenderId(int recommenderId) {
this.recommenderId = recommenderId;
}
public String getLoginName() {
return loginName;
}
publicvoid setLoginName(String loginName) {
this.loginName = loginName;
}
public String getLoginPasswd() {
return loginPasswd;
}
publicvoid setLoginPasswd(String loginPasswd) {
this.loginPasswd = loginPasswd;
}
public String getStatus() {
return status;
}
publicvoid setStatus(String status) {
this.status = status;
}
public Date getCreateDate() {
return createDate;
}
publicvoid setCreateDate(Date createDate) {
this.createDate = createDate;
}
public Date getPauseDate() {
return pauseDate;
}
publicvoid setPauseDate(Date pauseDate) {
this.pauseDate = pauseDate;
}
public Date getCloseDate() {
return closeDate;
}
publicvoid setCloseDate(Date closeDate) {
this.closeDate = closeDate;
}
public String getRealName() {
return realName;
}
publicvoid setRealName(String realName) {
this.realName = realName;
}
public String getIdcardNo() {
return idcardNo;
}
publicvoid setIdcardNo(String idcardNo) {
this.idcardNo = idcardNo;
}
public Date getBirthdate() {
return birthdate;
}
publicvoid setBirthdate(Date birthdate) {
this.birthdate = birthdate;
}
public String getGender() {
return gender;
}
publicvoid setGender(String gender) {
this.gender = gender;
}
public String getOccupation() {
return occupation;
}
publicvoid setOccupation(String occupation) {
this.occupation = occupation;
}
public String getTelephone() {
return telephone;
}
publicvoid setTelephone(String telephone) {
this.telephone = telephone;
}
public String getEmail() {
return email;
}
publicvoid setEmail(String email) {
this.email = email;
}
public String getMailaddress() {
return mailaddress;
}
publicvoid setMailaddress(String mailaddress) {
this.mailaddress = mailaddress;
}
public String getZipcode() {
return zipcode;
}
publicvoid setZipcode(String zipcode) {
this.zipcode = zipcode;
}
public String getQq() {
return qq;
}
publicvoid setQq(String qq) {
this.qq = qq;
}
public Date getLastLoginTime() {
return lastLoginTime;
}
publicvoid setLastLoginTime(Date lastLoginTime) {
this.lastLoginTime = lastLoginTime;
}
public String getLastLoginIp() {
return lastLoginIp;
}
publicvoid setLastLoginIp(String lastLoginIp) {
this.lastLoginIp = lastLoginIp;
}
public String toString(){
System.out.println("id=" + id);
System.out.println("recommenderId=" + recommenderId);
System.out.println("loginName=" + loginName);
System.out.println("loginPasswd=" + loginPasswd);
System.out.println("status=" + status);
System.out.println("createDate=" + createDate);
System.out.println("pauseDate=" + pauseDate);
System.out.println("closeDate=" + closeDate);
System.out.println("realName=" + realName);
System.out.println("idcardNo=" + idcardNo);
System.out.println("birthdate=" + birthdate);
System.out.println("gender=" + gender);
System.out.println("occupation=" + occupation);
System.out.println("telephone=" + telephone);
System.out.println("email=" + email);
System.out.println("mailaddress=" + mailaddress);
System.out.println("zipcode=" + zipcode);
System.out.println("qq=" + qq);
System.out.println("lastLoginTime=" + lastLoginTime);
System.out.println("lastLoginIp=" + lastLoginIp);
returnnull;
}
}
步骤四：创建db.properties文件，该文件封装了连接数据的信息
该文件内容如下所示：
jdbc.driverClassName=oracle.jdbc.OracleDriver jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl jdbc.username=scott jdbc.password=tiger #jdbc.driverClassName=com.mysql.jdbc.Driver #jdbc.url=jdbc:mysql://localhost:3306/tts7 #jdbc.username=root #jdbc.password=root #<!-- 初始化连接 --> dataSource.initialSize=10 #<!-- 最大空闲连接 --> dataSource.maxIdle=20 #<!-- 最小空闲连接 --> dataSource.minIdle=5 #最大连接数量 dataSource.maxActive=500 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> dataSource.maxWait=1000
步骤五：创建BaseDAO类，在该类中封装创建数据库连接方法以及事务处理
代码如下所示：
package com.tarena.netctoss;
import org.apache.commons.dbcp.BasicDataSource;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.Properties;
public class BaseDAO {
private static BasicDataSource dataSource = null;
public static Properties properties = new Properties();
public BaseDAO() {
}
// 用于装载db.propertis获得数据库参数
public static void init() {
Properties dbProps = new Properties();
// 取配置文件可以根据实际的不同修改
try {
dbProps.load(BaseDAO.class.getClassLoader().getResourceAsStream(
"com/tarena/netctoss/db.properties"));
} catch (IOException e1) {
e1.printStackTrace();
}
try {
String driveClassName = dbProps.getProperty("jdbc.driverClassName");
String url = dbProps.getProperty("jdbc.url");
String username = dbProps.getProperty("jdbc.username");
String password = dbProps.getProperty("jdbc.password");
String initialSize = dbProps.getProperty("dataSource.initialSize");
String minIdle = dbProps.getProperty("dataSource.minIdle");
String maxIdle = dbProps.getProperty("dataSource.maxIdle");
String maxWait = dbProps.getProperty("dataSource.maxWait");
String maxActive = dbProps.getProperty("dataSource.maxActive");
dataSource = new BasicDataSource();
dataSource.setDriverClassName(driveClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// 初始化连接数
if (initialSize != null)
dataSource.setInitialSize(Integer.parseInt(initialSize));
// 最小空闲连接
if (minIdle != null)
dataSource.setMinIdle(Integer.parseInt(minIdle));
// 最大空闲连接
if (maxIdle != null)
dataSource.setMaxIdle(Integer.parseInt(maxIdle));
// 超时回收时间(以毫秒为单位)
if (maxWait != null)
dataSource.setMaxWait(Long.parseLong(maxWait));
// 最大连接数
if (maxActive != null) {
if (!maxActive.trim().equals("0"))
dataSource.setMaxActive(Integer.parseInt(maxActive));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("创建连接池失败!请检查设置!!!");
}
}
public static synchronized Connection getConnection() throws SQLException {
if (dataSource == null) {
init();
}
Connection conn = null;
if (dataSource != null) {
conn = dataSource.getConnection();
}
return conn;
}
public static void begin() {
try {
Connection conn = getConnection();
if (conn != null)
conn.setAutoCommit(false);
} catch (Exception e) {
System.out.println("开启事务失败!");
e.printStackTrace();
}
}
public static void commit() {
try {
Connection conn = getConnection();
if (conn != null)
conn.commit();
} catch (Exception e) {
System.out.println("提交事务失败!");
e.printStackTrace();
}
}
public static void rollback() {
try {
Connection conn = getConnection();
if (conn != null)
conn.rollback();
} catch (Exception e) {
System.out.println("回滚事务失败!");
e.printStackTrace();
}
}
}
步骤六：创建AccountDAO接口，该接口中定义了对数据的增删改查的功能
代码如下所示：
package com.tarena.netctoss;
import java.sql.SQLException;
import java.util.List;
import com.tarena.netctoss.entity.Account;
public interface AccountDAO {
/**
* 根据某个账务账号ID查询该账务账号的全部信息
* @param id 账务账号ID
* @return 某个账务账号的全部信息，为一个Account对象
* @throws SQLException
*/
Account findById(Integer id) throws SQLException;
/**
* 查询所有的账务账号
* @return 所有账务账号返回List集合
* @throws SQLException
*/
List<Account> findAll() throws SQLException;
/**
* 新增账务账号
* @param account 要添加的账务账号
* @return 添加后账务账号，包含账号账号ID
* @throws SQLException
*/
Account save(Account account) throws SQLException;
/**
* 修改某个账务账号的信息
* @param account 要修改的账务账号
* @return 返回修改后的账务账号
* @throws SQLException
*/
Account modify(Account account) throws SQLException;
/**
* 修改某个账务账号的状态
* @param account 要修改状态的账务账号
* @return 返回修改状态后的账务账号
* @throws SQLException
*/
Account modifyStatus(Account account) throws SQLException;
}
步骤七：创建AccountDAOImpl类
创建AccountDAOImpl类，该类继承自BaseDAO，来直接获取创建连接的方法；另外，该类实现了AccountDAO接口，将该接口中的方法做出实现，代码如下所示：
package com.tarena.netctoss.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import com.tarena.netctoss.entity.Account;
import com.tarena.netctoss.AccountDAO;
import com.tarena.netctoss.BaseDAO;
public class AccountDAOImpl extends BaseDAO implements AccountDAO {
private static final String SELECT_BY_ID = "SELECT ACCOUNT_ID, RECOMMENDER_ID, LOGIN_NAME, LOGIN_PASSWD,"
+ " STATUS, CREATE_DATE, PAUSE_DATE, CLOSE_DATE, REAL_NAME, IDCARD_NO, BIRTHDATE, GENDER, OCCUPATION,"
+ "TELEPHONE,EMAIL,MAILADDRESS,ZIPCODE,QQ,LAST_LOGIN_TIME,LAST_LOGIN_IP "
+ "FROM ACCOUNT WHERE ACCOUNT_ID=?";
private static final String FIND_ALL = "SELECT ACCOUNT_ID, RECOMMENDER_ID, LOGIN_NAME, LOGIN_PASSWD,"
+ " STATUS, CREATE_DATE, PAUSE_DATE, CLOSE_DATE, REAL_NAME, IDCARD_NO, BIRTHDATE, GENDER, OCCUPATION,"
+ "TELEPHONE,EMAIL,MAILADDRESS,ZIPCODE,QQ,LAST_LOGIN_TIME,LAST_LOGIN_IP "
+ "FROM ACCOUNT";
private static final String MODIFY = "UPDATE ACCOUNT SET LOGIN_PASSWD = ?, REAL_NAME = ?, GENDER = ?, OCCUPATION = ?,"
+ "TELEPHONE = ?,EMAIL = ?,MAILADDRESS = ?,ZIPCODE = ?,QQ = ?"
+ "WHERE ACCOUNT_ID=?";
private static final String UPDATE_STATUS = "UPDATE ACCOUNT SET STATUS = ? WHERE ACCOUNT_ID=?";
private static final String INSERT = "INSERT INTO ACCOUNT(ACCOUNT_ID, RECOMMENDER_ID, LOGIN_NAME, LOGIN_PASSWD,"
+ " STATUS, CREATE_DATE, PAUSE_DATE, CLOSE_DATE, REAL_NAME, IDCARD_NO, BIRTHDATE, GENDER, OCCUPATION,"
+ "TELEPHONE,EMAIL,MAILADDRESS,ZIPCODE,QQ,LAST_LOGIN_TIME,LAST_LOGIN_IP) "
+ " VALUES (ACCOUNT_SEQ.NEXTVAL,?, ?, ?, '0', SYSDATE, NULL, NULL,?, ?, to_date(?,'yyyy-mm-dd'), ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?)";
@Override
public Account findById(Integer id) throws SQLException {
Connection conn = getConnection();
String sql = SELECT_BY_ID;
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
Account account = null;
while (rs.next()) {
account = new Account();
account.setId(rs.getInt("ACCOUNT_ID"));
account.setRecommenderId(rs.getInt("RECOMMENDER_ID"));
account.setLoginName(rs.getString("LOGIN_NAME"));
account.setLoginPasswd(rs.getString("LOGIN_PASSWD"));
account.setStatus(rs.getString("STATUS"));
account.setCreateDate(rs.getDate("CREATE_DATE"));
account.setPauseDate(rs.getDate("PAUSE_DATE"));
account.setCloseDate(rs.getDate("CLOSE_DATE"));
account.setRealName(rs.getString("REAL_NAME"));
account.setIdcardNo(rs.getString("IDCARD_NO"));
account.setBirthdate(rs.getDate("BIRTHDATE"));
account.setGender(rs.getString("GENDER"));
account.setOccupation(rs.getString("OCCUPATION"));
account.setTelephone(rs.getString("TELEPHONE"));
account.setEmail(rs.getString("EMAIL"));
account.setMailaddress(rs.getString("MAILADDRESS"));
account.setZipcode(rs.getString("ZIPCODE"));
account.setQq(rs.getString("QQ"));
account.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
account.setLastLoginIp(rs.getString("LAST_LOGIN_IP"));
}
return account;
}
@Override
public List<Account> findAll() throws SQLException {
// TODO Auto-generated method stub
Connection conn = getConnection();
String sql = FIND_ALL;
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
Account account = null;
List<Account> list = new ArrayList<Account>();
while (rs.next()) {
account = new Account();
account.setId(rs.getInt("ACCOUNT_ID"));
account.setRecommenderId(rs.getInt("RECOMMENDER_ID"));
account.setLoginName(rs.getString("LOGIN_NAME"));
account.setLoginPasswd(rs.getString("LOGIN_PASSWD"));
account.setStatus(rs.getString("STATUS"));
account.setCreateDate(rs.getDate("CREATE_DATE"));
account.setPauseDate(rs.getDate("PAUSE_DATE"));
account.setCloseDate(rs.getDate("CLOSE_DATE"));
account.setRealName(rs.getString("REAL_NAME"));
account.setIdcardNo(rs.getString("IDCARD_NO"));
account.setBirthdate(rs.getDate("BIRTHDATE"));
account.setGender(rs.getString("GENDER"));
account.setOccupation(rs.getString("OCCUPATION"));
account.setTelephone(rs.getString("TELEPHONE"));
account.setEmail(rs.getString("EMAIL"));
account.setMailaddress(rs.getString("MAILADDRESS"));
account.setZipcode(rs.getString("ZIPCODE"));
account.setQq(rs.getString("QQ"));
account.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
account.setLastLoginIp(rs.getString("LAST_LOGIN_IP"));
list.add(account);
}
return list;
}
@Override
public Account save(Account account) throws SQLException {
Connection conn = getConnection();
String sql = INSERT;
PreparedStatement ps = conn.prepareStatement(sql, new String[] { "account_id" });
Calendar c = Calendar.getInstance();
c.setTime(account.getBirthdate());
String birth = c.get(Calendar.YEAR) + "-" + c.get(Calendar.MONTH) + "-" + c.get(Calendar.DATE);
ps.setInt(1, account.getRecommenderId());
ps.setString(2, account.getLoginName());
ps.setString(3, account.getLoginPasswd());
ps.setString(4, account.getRealName());
ps.setString(5, account.getIdcardNo());
ps.setString(6, birth);
ps.setString(7, account.getGender());
ps.setString(8, account.getOccupation());
ps.setString(9, account.getTelephone());
ps.setString(10, account.getEmail());
ps.setString(11, account.getMailaddress());
ps.setString(12, account.getZipcode());
ps.setString(13, account.getQq());
ps.setString(14, account.getLastLoginIp());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
int id = rs.getInt(1);
account.setId(id);
return account;
}
@Override
public Account modify(Account account) throws SQLException {
Connection conn = getConnection();
String sql = MODIFY; // 预先定义好的SQL语句
//如果输入的id不存在，直接返回null
Account account_fromdb = this.findById(account.getId());
if (account_fromdb == null){
return null;
}
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, account.getLoginPasswd());// 传入参数
ps.setString(2, account.getRealName());
ps.setString(3, account.getGender());
ps.setString(4, account.getOccupation());
ps.setString(5, account.getTelephone());
ps.setString(6, account.getEmail());
ps.setString(7, account.getMailaddress());
ps.setString(8, account.getZipcode());
ps.setString(9, account.getQq());
ps.setInt(10, account.getId());
int flag = ps.executeUpdate();
return (flag > 0) ? account : null;
}
/**
* private static final String UPDATE_STATUS = "UPDATE ACCOUNT SET STUTAS = ? WHERE ACCOUNT_ID=?";
*/
@Override
public Account modifyStatus(Account account) throws SQLException {
// TODO Auto-generated method stub
Connection conn = getConnection();
String sql = UPDATE_STATUS; // 预先定义好的SQL语句
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, account.getStatus());// 传入参数
ps.setInt(2, account.getId());
int flag = ps.executeUpdate();
return (flag > 0) ? account : null;
}
}
步骤八：创建TestAccountDAO类，用于测试功能是否实现
创建TestAccountDAO类，该类用于测试AccountDAOImpl所实现的方法的正确性，代码如下所示：
package com.tarena.netctoss;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import com.tarena.netctoss.entity.Account;
import com.tarena.netctoss.impl.AccountDAOImpl;
public class TestAccountDAO {
public static void main(String[] args) {
TestAccountDAO test = new TestAccountDAO();
//test.testSave();
//test.testFindById(3);
//test.testFindAll();
//test.testModify();
test.testModifyStatus();
}
public void testModifyStatus() {
AccountDAO dao = new AccountDAOImpl();
Account account = new Account();
account.setId(3);
account.setStatus("2");//0：开通；1：暂停；2：删除
try {
dao.modifyStatus(account);
//检查更新结果
Account a = dao.findById(3);
System.out.println(a.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
public void testModify() {
Calendar c = Calendar.getInstance();
c.set(1988, 6, 17);
Date birth = c.getTime();
System.out.println(birth);
Account account = new Account();
account.setId(3);
account.setRecommenderId(3);
account.setLoginName("hitmo");
account.setLoginPasswd("123mo");
account.setRealName("zsmo");
account.setIdcardNo("007mo");
account.setBirthdate(birth);
account.setGender("1");
account.setOccupation("studentmo");
account.setTelephone("tel1234mo");
account.setEmail("a@b.cmo");
account.setMailaddress("126 avenue mo");
account.setZipcode("1071mo");
account.setQq("1111mo");
account.setLastLoginIp("192.mo");
AccountDAO dao = new AccountDAOImpl();
try {
account = dao.modify(account);
System.out.println(account.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
public void testSave() {
Calendar c = Calendar.getInstance();
c.set(1980, 3, 12);
Date birth = c.getTime();
System.out.println(birth);
Account account = new Account();
account.setRecommenderId(1);
account.setLoginName("hit");
account.setLoginPasswd("123");
account.setRealName("zs");
account.setIdcardNo("007");
account.setBirthdate(birth);
account.setGender("0");
account.setOccupation("student");
account.setTelephone("tel1234");
account.setEmail("a@b.c");
account.setMailaddress("126 avenue");
account.setZipcode("1071");
account.setQq("1111");
account.setLastLoginIp("192");
AccountDAO dao = new AccountDAOImpl();
try {
dao.save(account);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void testFindById(Integer id) {
AccountDAO dao = new AccountDAOImpl();
try {
Account account = dao.findById(id);
account.toString();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void testFindAll() {
AccountDAO dao = new AccountDAOImpl();
try {
List<Account> list = dao.findAll();
for(Account account : list){
account.toString();
System.out.println("---------------------------------");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
测试的过程中需要注意的是修改信息、修改状态以及根据ID查询，这些方法都需要传入已存在的账务账号ID。
5.4 完整代码
本案例中，创建account_seq序列、Account的表以及插入SQL语句如下所示：
create sequence account_seq;
create table account(
account_id number(9) constraint account_id_pk primary key,
recommender_id number(9) constraint account_recommender_id_fk
references account(account_id),
login_name varchar2(30) not null
constraint account_login_name_uk unique,
login_passwd varchar2(30) not null,
status char(1) constraint account_status_ck
check (status in (0,1,2)) not null,
create_date date default sysdate,
pause_date date,
close_date date,
real_name varchar2(20) not null,
idcard_no char(18) not null
constraint account_incard_no unique,
birthdate date,
gender char(1) constraint account_gender_ck
check (gender in (0,1)) not null,
occupation varchar2(50),
telephone varchar2(15) not null,
email varchar2(50),
mailaddress varchar2(200),
zipcode char(6),
qq varchar2(15),
last_login_time date,
last_login_ip varchar2(15)
);
INSERT INTO ACCOUNT(ACCOUNT_ID, RECOMMENDER_ID, LOGIN_NAME, LOGIN_PASSWD,
STATUS, CREATE_DATE, PAUSE_DATE, CLOSE_DATE, REAL_NAME, IDCARD_NO, BIRTHDATE,
GENDER,OCCUPATION,TELEPHONE,EMAIL,MAILADDRESS,ZIPCODE,QQ,LAST_LOGIN_TIME,LAST_LOGIN_IP)
VALUES (ACCOUNT_SEQ.NEXTVAL,null, 'a', '123', '0', SYSDATE, NULL, NULL,'zhangsan', '1234',
to_date('1981-01-01','yyyy-mm-dd'), '0', 'job','123' , 'a@b.c', 'test avenue', '1223', '1234', SYSDATE, '192');
类Account的完整代码如下所示：
package com.tarena.netctoss.entity;
import java.util.Date;
publicclass Account {
privateint id;
privateint recommenderId;
private String loginName;
private String loginPasswd;
private String status;
private Date createDate;
private Date pauseDate;
private Date closeDate;
private String realName;
private String idcardNo;
private Date birthdate;
private String gender;
private String occupation;
private String telephone;
private String email;
private String mailaddress;
private String zipcode;
private String qq;
private Date lastLoginTime;
private String lastLoginIp;
public Account() {
super();
}
public Account(int id, int recommenderId, String loginName,
String loginPasswd, String status, Date createDate, Date pauseDate,
Date closeDate, String realName, String idcardNo, Date birthdate,
String gender, String occupation, String telephone, String email,
String mailaddress, String zipcode, String qq, Date lastLoginTime,
String lastLoginIp) {
super();
this.id = id;
this.recommenderId = recommenderId;
this.loginName = loginName;
this.loginPasswd = loginPasswd;
this.status = status;
this.createDate = createDate;
this.pauseDate = pauseDate;
this.closeDate = closeDate;
this.realName = realName;
this.idcardNo = idcardNo;
this.birthdate = birthdate;
this.gender = gender;
this.occupation = occupation;
this.telephone = telephone;
this.email = email;
this.mailaddress = mailaddress;
this.zipcode = zipcode;
this.qq = qq;
this.lastLoginTime = lastLoginTime;
this.lastLoginIp = lastLoginIp;
}
publicint getId() {
return id;
}
publicvoid setId(int id) {
this.id = id;
}
publicint getRecommenderId() {
return recommenderId;
}
publicvoid setRecommenderId(int recommenderId) {
this.recommenderId = recommenderId;
}
public String getLoginName() {
return loginName;
}
publicvoid setLoginName(String loginName) {
this.loginName = loginName;
}
public String getLoginPasswd() {
return loginPasswd;
}
publicvoid setLoginPasswd(String loginPasswd) {
this.loginPasswd = loginPasswd;
}
public String getStatus() {
return status;
}
publicvoid setStatus(String status) {
this.status = status;
}
public Date getCreateDate() {
return createDate;
}
publicvoid setCreateDate(Date createDate) {
this.createDate = createDate;
}
public Date getPauseDate() {
return pauseDate;
}
publicvoid setPauseDate(Date pauseDate) {
this.pauseDate = pauseDate;
}
public Date getCloseDate() {
return closeDate;
}
publicvoid setCloseDate(Date closeDate) {
this.closeDate = closeDate;
}
public String getRealName() {
return realName;
}
publicvoid setRealName(String realName) {
this.realName = realName;
}
public String getIdcardNo() {
return idcardNo;
}
publicvoid setIdcardNo(String idcardNo) {
this.idcardNo = idcardNo;
}
public Date getBirthdate() {
return birthdate;
}
publicvoid setBirthdate(Date birthdate) {
this.birthdate = birthdate;
}
public String getGender() {
return gender;
}
publicvoid setGender(String gender) {
this.gender = gender;
}
public String getOccupation() {
return occupation;
}
publicvoid setOccupation(String occupation) {
this.occupation = occupation;
}
public String getTelephone() {
return telephone;
}
publicvoid setTelephone(String telephone) {
this.telephone = telephone;
}
public String getEmail() {
return email;
}
publicvoid setEmail(String email) {
this.email = email;
}
public String getMailaddress() {
return mailaddress;
}
publicvoid setMailaddress(String mailaddress) {
this.mailaddress = mailaddress;
}
public String getZipcode() {
return zipcode;
}
publicvoid setZipcode(String zipcode) {
this.zipcode = zipcode;
}
public String getQq() {
return qq;
}
publicvoid setQq(String qq) {
this.qq = qq;
}
public Date getLastLoginTime() {
return lastLoginTime;
}
publicvoid setLastLoginTime(Date lastLoginTime) {
this.lastLoginTime = lastLoginTime;
}
public String getLastLoginIp() {
return lastLoginIp;
}
publicvoid setLastLoginIp(String lastLoginIp) {
this.lastLoginIp = lastLoginIp;
}
public String toString(){
System.out.println("id=" + id);
System.out.println("recommenderId=" + recommenderId);
System.out.println("loginName=" + loginName);
System.out.println("loginPasswd=" + loginPasswd);
System.out.println("status=" + status);
System.out.println("createDate=" + createDate);
System.out.println("pauseDate=" + pauseDate);
System.out.println("closeDate=" + closeDate);
System.out.println("realName=" + realName);
System.out.println("idcardNo=" + idcardNo);
System.out.println("birthdate=" + birthdate);
System.out.println("gender=" + gender);
System.out.println("occupation=" + occupation);
System.out.println("telephone=" + telephone);
System.out.println("email=" + email);
System.out.println("mailaddress=" + mailaddress);
System.out.println("zipcode=" + zipcode);
System.out.println("qq=" + qq);
System.out.println("lastLoginTime=" + lastLoginTime);
System.out.println("lastLoginIp=" + lastLoginIp);
returnnull;
}
}
db.properties文件内容如下所示：
jdbc.driverClassName=oracle.jdbc.OracleDriver jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl jdbc.username=scott jdbc.password=tiger #jdbc.driverClassName=com.mysql.jdbc.Driver #jdbc.url=jdbc:mysql://localhost:3306/tts7 #jdbc.username=root #jdbc.password=root #<!-- 初始化连接 --> dataSource.initialSize=10 #<!-- 最大空闲连接 --> dataSource.maxIdle=20 #<!-- 最小空闲连接 --> dataSource.minIdle=5 #最大连接数量 dataSource.maxActive=500 #<!-- 超时等待时间以毫秒为单位 (6000毫秒/1000等于60秒) --> dataSource.maxWait=1000
BaseDAO类的完整代码如下所示：
package com.tarena.netctoss;
import org.apache.commons.dbcp.BasicDataSource;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.Properties;
public class BaseDAO {
private static BasicDataSource dataSource = null;
public static Properties properties = new Properties();
public BaseDAO() {
}
// 用于装载db.propertis获得数据库参数
public static void init() {
Properties dbProps = new Properties();
// 取配置文件可以根据实际的不同修改
try {
dbProps.load(BaseDAO.class.getClassLoader().getResourceAsStream(
"com/tarena/netctoss/db.properties"));
} catch (IOException e1) {
e1.printStackTrace();
}
try {
String driveClassName = dbProps.getProperty("jdbc.driverClassName");
String url = dbProps.getProperty("jdbc.url");
String username = dbProps.getProperty("jdbc.username");
String password = dbProps.getProperty("jdbc.password");
String initialSize = dbProps.getProperty("dataSource.initialSize");
String minIdle = dbProps.getProperty("dataSource.minIdle");
String maxIdle = dbProps.getProperty("dataSource.maxIdle");
String maxWait = dbProps.getProperty("dataSource.maxWait");
String maxActive = dbProps.getProperty("dataSource.maxActive");
dataSource = new BasicDataSource();
dataSource.setDriverClassName(driveClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// 初始化连接数
if (initialSize != null)
dataSource.setInitialSize(Integer.parseInt(initialSize));
// 最小空闲连接
if (minIdle != null)
dataSource.setMinIdle(Integer.parseInt(minIdle));
// 最大空闲连接
if (maxIdle != null)
dataSource.setMaxIdle(Integer.parseInt(maxIdle));
// 超时回收时间(以毫秒为单位)
if (maxWait != null)
dataSource.setMaxWait(Long.parseLong(maxWait));
// 最大连接数
if (maxActive != null) {
if (!maxActive.trim().equals("0"))
dataSource.setMaxActive(Integer.parseInt(maxActive));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("创建连接池失败!请检查设置!!!");
}
}
public static synchronized Connection getConnection() throws SQLException {
if (dataSource == null) {
init();
}
Connection conn = null;
if (dataSource != null) {
conn = dataSource.getConnection();
}
return conn;
}
public static void begin() {
try {
Connection conn = getConnection();
if (conn != null)
conn.setAutoCommit(false);
} catch (Exception e) {
System.out.println("开启事务失败!");
e.printStackTrace();
}
}
public static void commit() {
try {
Connection conn = getConnection();
if (conn != null)
conn.commit();
} catch (Exception e) {
System.out.println("提交事务失败!");
e.printStackTrace();
}
}
public static void rollback() {
try {
Connection conn = getConnection();
if (conn != null)
conn.rollback();
} catch (Exception e) {
System.out.println("回滚事务失败!");
e.printStackTrace();
}
}
}
AccountDAO接口的完整代码如下所示：
package com.tarena.netctoss;
import java.sql.SQLException;
import java.util.List;
import com.tarena.netctoss.entity.Account;
public interface AccountDAO {
/**
* 根据某个账务账号ID查询该账务账号的全部信息
* @param id 账务账号ID
* @return 某个账务账号的全部信息，为一个Account对象
* @throws SQLException
*/
Account findById(Integer id) throws SQLException;
/**
* 查询所有的账务账号
* @return 所有账务账号 返回List集合
* @throws SQLException
*/
List<Account> findAll() throws SQLException;
/**
* 新增账务账号
* @param account 要添加的账务账号
* @return 添加后账务账号，包含账号账号ID
* @throws SQLException
*/
Account save(Account account) throws SQLException;
/**
* 修改某个账务账号的信息
* @param account 要修改的账务账号
* @return 返回修改后的账务账号
* @throws SQLException
*/
Account modify(Account account) throws SQLException;
/**
* 修改某个账务账号的状态
* @param account 要修改状态的账务账号
* @return 返回修改状态后的账务账号
* @throws SQLException
*/
Account modifyStatus(Account account) throws SQLException;
}
AccountDAOImpl类的完整代码如下所示：
package com.tarena.netctoss.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import com.tarena.netctoss.entity.Account;
import com.tarena.netctoss.AccountDAO;
import com.tarena.netctoss.BaseDAO;
public class AccountDAOImpl extends BaseDAO implements AccountDAO {
private static final String SELECT_BY_ID = "SELECT ACCOUNT_ID, RECOMMENDER_ID, LOGIN_NAME, LOGIN_PASSWD,"
+ " STATUS, CREATE_DATE, PAUSE_DATE, CLOSE_DATE, REAL_NAME, IDCARD_NO, BIRTHDATE, GENDER, OCCUPATION,"
+ "TELEPHONE,EMAIL,MAILADDRESS,ZIPCODE,QQ,LAST_LOGIN_TIME,LAST_LOGIN_IP "
+ "FROM ACCOUNT WHERE ACCOUNT_ID=?";
private static final String FIND_ALL = "SELECT ACCOUNT_ID, RECOMMENDER_ID, LOGIN_NAME, LOGIN_PASSWD,"
+ " STATUS, CREATE_DATE, PAUSE_DATE, CLOSE_DATE, REAL_NAME, IDCARD_NO, BIRTHDATE, GENDER, OCCUPATION,"
+ "TELEPHONE,EMAIL,MAILADDRESS,ZIPCODE,QQ,LAST_LOGIN_TIME,LAST_LOGIN_IP "
+ "FROM ACCOUNT";
private static final String MODIFY = "UPDATE ACCOUNT SET LOGIN_PASSWD = ?, REAL_NAME = ?, GENDER = ?, OCCUPATION = ?,"
+ "TELEPHONE = ?,EMAIL = ?,MAILADDRESS = ?,ZIPCODE = ?,QQ = ?"
+ "WHERE ACCOUNT_ID=?";
private static final String UPDATE_STATUS = "UPDATE ACCOUNT SET STATUS = ? WHERE ACCOUNT_ID=?";
private static final String INSERT = "INSERT INTO ACCOUNT(ACCOUNT_ID, RECOMMENDER_ID, LOGIN_NAME, LOGIN_PASSWD,"
+ " STATUS, CREATE_DATE, PAUSE_DATE, CLOSE_DATE, REAL_NAME, IDCARD_NO, BIRTHDATE, GENDER, OCCUPATION,"
+ "TELEPHONE,EMAIL,MAILADDRESS,ZIPCODE,QQ,LAST_LOGIN_TIME,LAST_LOGIN_IP) "
+ " VALUES (ACCOUNT_SEQ.NEXTVAL,?, ?, ?, '0', SYSDATE, NULL, NULL,?, ?, to_date(?,'yyyy-mm-dd'), ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?)";
@Override
public Account findById(Integer id) throws SQLException {
Connection conn = getConnection();
String sql = SELECT_BY_ID;
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
Account account = null;
while (rs.next()) {
account = new Account();
account.setId(rs.getInt("ACCOUNT_ID"));
account.setRecommenderId(rs.getInt("RECOMMENDER_ID"));
account.setLoginName(rs.getString("LOGIN_NAME"));
account.setLoginPasswd(rs.getString("LOGIN_PASSWD"));
account.setStatus(rs.getString("STATUS"));
account.setCreateDate(rs.getDate("CREATE_DATE"));
account.setPauseDate(rs.getDate("PAUSE_DATE"));
account.setCloseDate(rs.getDate("CLOSE_DATE"));
account.setRealName(rs.getString("REAL_NAME"));
account.setIdcardNo(rs.getString("IDCARD_NO"));
account.setBirthdate(rs.getDate("BIRTHDATE"));
account.setGender(rs.getString("GENDER"));
account.setOccupation(rs.getString("OCCUPATION"));
account.setTelephone(rs.getString("TELEPHONE"));
account.setEmail(rs.getString("EMAIL"));
account.setMailaddress(rs.getString("MAILADDRESS"));
account.setZipcode(rs.getString("ZIPCODE"));
account.setQq(rs.getString("QQ"));
account.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
account.setLastLoginIp(rs.getString("LAST_LOGIN_IP"));
}
return account;
}
@Override
public List<Account> findAll() throws SQLException {
// TODO Auto-generated method stub
Connection conn = getConnection();
String sql = FIND_ALL;
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
Account account = null;
List<Account> list = new ArrayList<Account>();
while (rs.next()) {
account = new Account();
account.setId(rs.getInt("ACCOUNT_ID"));
account.setRecommenderId(rs.getInt("RECOMMENDER_ID"));
account.setLoginName(rs.getString("LOGIN_NAME"));
account.setLoginPasswd(rs.getString("LOGIN_PASSWD"));
account.setStatus(rs.getString("STATUS"));
account.setCreateDate(rs.getDate("CREATE_DATE"));
account.setPauseDate(rs.getDate("PAUSE_DATE"));
account.setCloseDate(rs.getDate("CLOSE_DATE"));
account.setRealName(rs.getString("REAL_NAME"));
account.setIdcardNo(rs.getString("IDCARD_NO"));
account.setBirthdate(rs.getDate("BIRTHDATE"));
account.setGender(rs.getString("GENDER"));
account.setOccupation(rs.getString("OCCUPATION"));
account.setTelephone(rs.getString("TELEPHONE"));
account.setEmail(rs.getString("EMAIL"));
account.setMailaddress(rs.getString("MAILADDRESS"));
account.setZipcode(rs.getString("ZIPCODE"));
account.setQq(rs.getString("QQ"));
account.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
account.setLastLoginIp(rs.getString("LAST_LOGIN_IP"));
list.add(account);
}
return list;
}
@Override
public Account save(Account account) throws SQLException {
Connection conn = getConnection();
String sql = INSERT;
PreparedStatement ps = conn.prepareStatement(sql, new String[] { "account_id" });
Calendar c = Calendar.getInstance();
c.setTime(account.getBirthdate());
String birth = c.get(Calendar.YEAR) + "-" + c.get(Calendar.MONTH) + "-" + c.get(Calendar.DATE);
ps.setInt(1, account.getRecommenderId());
ps.setString(2, account.getLoginName());
ps.setString(3, account.getLoginPasswd());
ps.setString(4, account.getRealName());
ps.setString(5, account.getIdcardNo());
ps.setString(6, birth);
ps.setString(7, account.getGender());
ps.setString(8, account.getOccupation());
ps.setString(9, account.getTelephone());
ps.setString(10, account.getEmail());
ps.setString(11, account.getMailaddress());
ps.setString(12, account.getZipcode());
ps.setString(13, account.getQq());
ps.setString(14, account.getLastLoginIp());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
int id = rs.getInt(1);
account.setId(id);
return account;
}
@Override
public Account modify(Account account) throws SQLException {
Connection conn = getConnection();
String sql = MODIFY; // 预先定义好的SQL语句
//如果输入的id不存在，直接返回null
Account account_fromdb = this.findById(account.getId());
if (account_fromdb == null){
return null;
}
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, account.getLoginPasswd());// 传入参数
ps.setString(2, account.getRealName());
ps.setString(3, account.getGender());
ps.setString(4, account.getOccupation());
ps.setString(5, account.getTelephone());
ps.setString(6, account.getEmail());
ps.setString(7, account.getMailaddress());
ps.setString(8, account.getZipcode());
ps.setString(9, account.getQq());
ps.setInt(10, account.getId());
int flag = ps.executeUpdate();
return (flag > 0) ? account : null;
}
/**
* private static final String UPDATE_STATUS = "UPDATE ACCOUNT SET STUTAS = ? WHERE ACCOUNT_ID=?";
*/
@Override
public Account modifyStatus(Account account) throws SQLException {
// TODO Auto-generated method stub
Connection conn = getConnection();
String sql = UPDATE_STATUS; // 预先定义好的SQL语句
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, account.getStatus());// 传入参数
ps.setInt(2, account.getId());
int flag = ps.executeUpdate();
return (flag > 0) ? account : null;
}
}
TestAccountDAO类的完整代码如下所示：
package com.tarena.netctoss;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import com.tarena.netctoss.entity.Account;
import com.tarena.netctoss.impl.AccountDAOImpl;
public class TestAccountDAO {
public static void main(String[] args) {
TestAccountDAO test = new TestAccountDAO();
//test.testSave();
//test.testFindById(3);
//test.testFindAll();
//test.testModify();
test.testModifyStatus();
}
public void testModifyStatus() {
AccountDAO dao = new AccountDAOImpl();
Account account = new Account();
account.setId(3);
account.setStatus("2");//0：开通；1：暂停；2：删除
try {
dao.modifyStatus(account);
//检查更新结果
Account a = dao.findById(3);
System.out.println(a.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
public void testModify() {
Calendar c = Calendar.getInstance();
c.set(1988, 6, 17);
Date birth = c.getTime();
System.out.println(birth);
Account account = new Account();
account.setId(3);
account.setRecommenderId(3);
account.setLoginName("hitmo");
account.setLoginPasswd("123mo");
account.setRealName("zsmo");
account.setIdcardNo("007mo");
account.setBirthdate(birth);
account.setGender("1");
account.setOccupation("studentmo");
account.setTelephone("tel1234mo");
account.setEmail("a@b.cmo");
account.setMailaddress("126 avenue mo");
account.setZipcode("1071mo");
account.setQq("1111mo");
account.setLastLoginIp("192.mo");
AccountDAO dao = new AccountDAOImpl();
try {
account = dao.modify(account);
System.out.println(account.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
public void testSave() {
Calendar c = Calendar.getInstance();
c.set(1980, 3, 12);
Date birth = c.getTime();
System.out.println(birth);
Account account = new Account();
account.setRecommenderId(1);
account.setLoginName("hit");
account.setLoginPasswd("123");
account.setRealName("zs");
account.setIdcardNo("007");
account.setBirthdate(birth);
account.setGender("0");
account.setOccupation("student");
account.setTelephone("tel1234");
account.setEmail("a@b.c");
account.setMailaddress("126 avenue");
account.setZipcode("1071");
account.setQq("1111");
account.setLastLoginIp("192");
AccountDAO dao = new AccountDAOImpl();
try {
dao.save(account);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void testFindById(Integer id) {
AccountDAO dao = new AccountDAOImpl();
try {
Account account = dao.findById(id);
account.toString();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void testFindAll() {
AccountDAO dao = new AccountDAOImpl();
try {
List<Account> list = dao.findAll();
for(Account account : list){
account.toString();
System.out.println("---------------------------------");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}