JDBC相关
- 1、前言
- 2、JDBC
- 3、数据库连接池
1、前言
我们在Java程序或JavaWeb程序中如果想要连接到数据库，那么该怎么实现呢？Java提供了JDBC API来帮助用户进行数据库连接操作。如下图：
据图可以看到，JDBC API是连接Java程序和各个数据库产品的桥梁。想要连接上数据库，必须要通过JDBC API接口，JDBC API是Java程序提供的连接第三方数据库的唯一途径。
2、JDBC
2.1、JDBC的概念
JDBC的全称是Java DataBase Connectivity，即Java数据库连接，它是一套面向对象的应用程序接口，指定了统一的访问各种关系型数据库的标准接口。JDBC是一种底层的API，因此访问数据库时必须在业务逻辑层中嵌入SQL语句。因为SQL语句时面向关系的，依赖于关系模型，所以通过JDBC技术访问数据库也是面向关系的。
JDBC技术主要完成的任务如下：
- 与数据库建立一个连接。
- 向数据库发送SQL语句。
- 处理从数据库返回的结果集。
提示：JDBC并不能直接访问数据库，它只是提供统一访问的接口，至于实现，完全交给数据库厂商自己来实现，也就是各个厂商提供的JDBC驱动程序。简单来说，就是数据库厂商你想要支持我的Java语言，那么必须把JDBC中的接口给我实现了，这是想要用我的东西的一个前提，不实现也可以，那么对不起，Java不支持你的数据库产品。
2.2、JDBC中的核心类和接口
核心类和接口如下：
- DriverManager类：用于管理一组JDBC驱动程序的基本服务。管理jdbc驱动，提供Connection连接。
- Connection接口：与特定数据库的连接（会话）。 执行SQL语句并在连接的上下文中返回结果。负责DB连接，提供Statement。
- Statement接口：用于执行静态SQL语句并返回其生成的结果的对象。
- PreparedStatement接口：它是Statement接口的子接口，表示预编译的SQL语句的对象。
- CallableStatement接口：它是PreparedStatement接口的子接口，用于执行SQL存储过程的界面。
- ResultSet接口：表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。
JDBC中最核心的就是这1个类和另外的5个接口了，可以说非常常用，因此有必要十分熟悉。以下会对它们进行逐一说明。
2.3、DriverManager类
这个类是用来管理JDBC驱动程序的。它是JDBC的管理层，作用于用户和驱动程序之间。直接看api文档说明：
提供的方法如下：
这个类中我们常用的方法只有一个，getConnection，一般是用有3个参数的，说明如下：
- getConnection(String url, String user, String password) ：尝试建立与给定数据库URL的连接，给定的3个参数分别是：与数据库连接的url、用户名、密码。这个是一个静态方法，调用这个方法，会返回一个Connection接口对象。
2.4、Connection接口
这个接口是关于数据库连接的。它代表与特定的数据库的连接，在连接上下文中执行SQL语句并返回结果。api文档说明如下：
提供的方法如下：
标记的是常用方法，说明如下：
- close()：关闭Connection连接对象。
- commit()：使自上次提交/回滚以来所做的所有更改都将永久性，并释放此 Connection对象当前持有的任何数据库锁。
- createStatement()：创建一个Statement对象，用于将SQL语句发送到数据库。
- getAutoCommit()：检测当前连接是否为自动提交，返回布尔值。
- isClosed()：判断Connection连接是否关闭，返回布尔值。
- prepareCall(String sql)：创建一个调用数据库存储过程的CallableStatement对象。
- prepareStatement(String sql)：创建一个PreparedStatement对象，用于将参数化的SQL语句发送到数据库。
- rollback()：撤消在当前事务中所做的所有更改，并释放此Connection对象当前持有的任何数据库锁。
- rollback(Savepoint savepoint)：撤消在给定的Savepoint对象设置后进行的所有更改。
- setAutoCommit(boolean autoCommit)：将此连接的自动提交模式设置为给定状态。
- setSavepoint(String name)：在当前事务中创建具有给定名称的保存点，并返回代表它的新的Savepoint对象。
以下将使用DriverManager类来获取一个Connection连接，用于连接上本地的Oracle数据库，以及测试Connection接口的一些方法。
先把这个ojdbc6的驱动包放进去，其他两个包先无视。这个驱动包是Oracle厂商自己提供的驱动包，是对Java提供的JDBC API中接口的实现，没有这个驱动是连不上的。然后右键build path，把它加进Libraries中，如下：
要保证驱动加到里面了。
然后开始写代码：
/*
* 测试Oracle数据库连接
*/
public class TestConnection {
// Oracle数据库驱动包位置
private static final String ORACLE_DRIVER = "oracle.jdbc.OracleDriver";
// 数据库连接的url
private static final String CONNECTION_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
// 用户名
private static final String USERNAME = "yanchengzhi";
// 密码
private static final String PASSWORD = "ycz951824";
public static void main(String[] args) {
try {
// 显示加载Oracle的驱动包
Class.forName(ORACLE_DRIVER);
// 获取Connection连接
Connection connection = DriverManager.getConnection(CONNECTION_URL, USERNAME, PASSWORD);
if (connection != null) {
System.out.println("Oracle数据库连接成功！");
System.out.println("数据库连接是否关闭？" + connection.isClosed());
System.out.println(connection);
// 手动关闭连接
connection.close();
if (connection.isClosed()) {
System.out.println("已经手动关闭数据库连接！");
}
} else {
System.out.println("数据库连接失败！");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
执行这个程序：
那么以上测试就通过了，成功连接到了Oracle数据库。需要说明的是，连接最后一定要关闭，就像流一样，如果不关，可能会耗尽系统的资源。
2.5、Statement接口
这个接口用于执行静态的SQL语句，即是不带参数的SQL语句，并返回生成结果的对象。api文档说明如下：
提供的方法如下：
常用方法说明如下：
- addBatch(String sql)：将给定的SQL命令添加到此Statement对象的当前命令列表中。
- clearBatch()：清空此Statement对象的当前SQL命令列表。
- close()：Statement对象的数据库和JDBC资源，而不是等待它自动关闭时发生。
- execute(String sql)：执行给定的SQL语句，这可能会返回多个结果，返回的是布尔值。
- executeBatch()：将一批命令提交到数据库以执行，并且所有命令都执行成功，返回一个更新计数的数组。
- executeQuery(String sql)：执行给定的SQL语句，该语句返回单个ResultSet对象。
- executeUpdate(String sql)：执行给定的SQL语句，这可能是INSERT， UPDATE ，或 DELETE语句，或者不返回任何内容，如SQL DDL语句的SQL语句。
- getConnection()：检索Connection生成此对象Statement对象。
- getResultSet()：以ResultSet对象的形式获取当前结果。
- isClosed()：检索此 Statement对象是否已关闭。
2.6、PreparedStatement接口
这个接口是Statement接口的子接口，表示的是预编译的SQL语句对象，执行的是动态的SQL语句，即带参数的SQL语句。api文档说明如下：
提供的方法如下：
常用方法说明如下：
- addBatch()：向这个 PreparedStatement对象的一批命令添加一组参数。
- clearParameters()：立即清除当前参数值。
- execute()：执行此PreparedStatement对象中的SQL语句，这可能是任何类型的SQL语句。
- executeQuery()：执行此PreparedStatement对象中的SQL查询，并返回查询 PreparedStatement的ResultSet对象。
- executeUpdate()：执行在该SQL语句PreparedStatement对象，它必须是一个SQL数据操纵语言（DML）语句，如INSERT ， UPDATE或DELETE ; 或不返回任何内容的SQL语句，例如DDL语句。
- setBoolean(int parameterIndex, boolean x)：将指定的参数设置为给定的Java boolean值。
- setByte(int parameterIndex, byte x)：将指定的参数设置为给定的Java byte值。
- setDate(int parameterIndex, Date x)：使用运行应用程序的虚拟机的默认时区将指定的 java.sql.Date设置为给定的java.sql.Date值。
- setDouble(int parameterIndex, double x)：将指定的参数设置为给定的Java double值。
- setFloat(int parameterIndex, float x)：将指定的参数设置为给定的Java float值。
- setInt(int parameterIndex, int x)：将指定的参数设置为给定的Java int值。
- setLong(int parameterIndex, long x)：将指定的参数设置为给定的Java long值。
- setTime(int parameterIndex, Time x)：将指定的参数设置为给定的 java.sql.Time值。
2.7、使用JDBC对数据表进行增、删、改
2.7.1、准备工作
（1）数据库配置文件
数据库连接信息通常以普通文本属性文件进行配置，如xxxxx.properties。新建一个conf包，包下创建一个properties属性文件。
文件内容如下：
## 数据库连接url
jdbc_url=jdbc:oracle:thin:@localhost:1521:orcl
## 数据库连接驱动
jdbc_driver=oracle.jdbc.OracleDriver
## 用户
jdbc_username=yanchengzhi
## 密码
jdbc_password=ycz951824
（2）获取配置文件的属性值
新建一个utils包，包下创建一个Env类，如下：
/*
* 工具类，用于获取数据库的连接信息
* 需要继承自java.util.Properties类
*/
public class Env extends Properties {
private static final long serialVersionUID = 4182699870347430944L;
// 属性文件的位置
private static final String DB_CONF_FILE_PATH = "com/ycz/conf/oracle_db_conf.properties";
// 声明一个此类对象
private static Env env;
// 声明4个静态常量
public static final String JDBC_URL;
public static final String JDBC_DRIVER;
public static final String JDBC_USERNAME;
public static final String JDBC_PASSWORD;
// 静态代码块统一赋值
static {
// 获取Env实例
getEnv();
// 获取属性文件的输入流对象
InputStream inputStream = env.getClass().getClassLoader().getResourceAsStream(DB_CONF_FILE_PATH);
try {
// 加载输入流
env.load(inputStream);
// 关闭输入流
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
// 获取属性值
JDBC_URL = env.getProperty("jdbc_url");
JDBC_DRIVER = env.getProperty("jdbc_driver");
JDBC_USERNAME = env.getProperty("jdbc_username");
JDBC_PASSWORD = env.getProperty("jdbc_password");
}
// 实例化此类对象，单例模式
private static void getEnv() {
if (env == null) {
env = new Env();
}
}
}
测试是否能获取到属性值：
public static void main(String[] args) {
// 测试属性值是否获取到
System.out.println("连接的URL:" + Env.JDBC_URL);
System.out.println("数据库驱动：" + Env.JDBC_DRIVER);
System.out.println("用户：" + Env.JDBC_USERNAME);
System.out.println("数据库的连接密码：" + Env.JDBC_PASSWORD);
}
执行：
OK，没问题。
（3）数据源管理组件
定义一个数据源的管理组件，不使用数据库连接池。在utils包下创建新的类DataSourceManager，如下：
/*
* 工具类
* 进行数据源的管理
*/
public final class DataSourceManager {
// 定义一个方法，获取Connection连接
public static Connection getConnection() {
Connection connection = null;
try {
// 加载数据库驱动
Class.forName(Env.JDBC_DRIVER);
// 获取连接
connection = DriverManager.getConnection(Env.JDBC_URL, Env.JDBC_USERNAME, Env.JDBC_PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
// 定义一个方法，关闭Connection连接
public static void closeConnection(Connection connection) {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 定义一个方法，用于关闭Statement接口
public static void closeStatement(Statement statement) {
try {
if (statement != null && !statement.isClosed()) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 定义一个方法，用于关闭ResultSet接口
public static void closeResultSet(ResultSet resultSet) {
try {
if (resultSet != null && !resultSet.isClosed()) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
再定义一个数据源管理组件，使用c3p0的连接池，先要将包加进来：
第一个是c3p0的连接池包，第二个是辅助包。
在utils包下创建新的类DataSourceForPool，如下：
/*
* 数据源管理组件
* 使用c3p0连接池
*/
public class DataSourceForPool {
// 声明一个数据源对象
private static ComboPooledDataSource c3p0;
// 定义一个方法，创建数据源对象
public static void createDataSource() {
if (c3p0 == null) {
c3p0 = new ComboPooledDataSource();
}
try {
c3p0.setJdbcUrl(Env.JDBC_URL);// 设置连接URL
c3p0.setDriverClass(Env.JDBC_DRIVER);// 设置驱动
c3p0.setUser(Env.JDBC_USERNAME);// 设置用户
c3p0.setPassword(Env.JDBC_PASSWORD);// 设置密码
c3p0.setCheckoutTimeout(30000);// 设置超时时间
c3p0.setMaxPoolSize(20);// 设置最大连接池数
c3p0.setDataSourceName("ycz");// 设置连接池名称
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
// 定义一个方法，获取Connection连接
public static Connection getConnection() {
Connection connection = null;
try {
// 加载数据库驱动
Class.forName(Env.JDBC_DRIVER);
// 获取连接
connection = DriverManager.getConnection(Env.JDBC_URL, Env.JDBC_USERNAME, Env.JDBC_PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
// 定义一个方法，关闭Connection连接
public static void closeConnection(Connection connection) {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 定义一个方法，用于关闭Statement接口
public static void closeStatement(Statement statement) {
try {
if (statement != null && !statement.isClosed()) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 定义一个方法，用于关闭ResultSet接口
public static void closeResultSet(ResultSet resultSet) {
try {
if (resultSet != null && !resultSet.isClosed()) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
以上的准备工程就完成了，下面进行写增删改的代码。
2.7.2、JDBC进行数据的添加
现在准备使用JDBC对下面这张表进行记录的添加操作：
8条记录。
（1）实体类
准备一个与表对应的实体类Department，先看表结构：
再根据各个字段来写实体类属性，创建一个pojo包，包下创建实体类Department，如下：
public class Department {
private String id;// 部门ID
private String name;// 部门名称
private String code;// 部门编号
private Date newDate;// 部门创建日期
private String descs;// 说明
// 构造器
public Department(String id, String name, String code, Date newDate, String descs) {
this.id = id;
this.name = name;
this.code = code;
this.newDate = newDate;
this.descs = descs;
}
public Department() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public Date getNewDate() {
return newDate;
}
public void setNewDate(Date newDate) {
this.newDate = newDate;
}
public String getDescs() {
return descs;
}
public void setDescs(String descs) {
this.descs = descs;
}
}
（2）数据访问层Dao
创建一个dao包，包下新建一个DepDao类，如下：
/*
* 数据访问层
*/
public class DepDao {
// 定义一个方法，添加部门表记录
public void addDepartment(Department department) {
int res = 0;
// sql语句，？为占位符，后面会用参数替换
String sql = "insert into department(ID,NAME,CODE,NEWDATE,DESCS) values(?,?,?,?,?)";
// 获取Connection连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement pStatement = null;
try {
// 从Connection连接中获取prepareStatement对象
pStatement = connection.prepareStatement(sql);
// 参数替换占位符
pStatement.setString(1, department.getId());
pStatement.setString(2, department.getName());
pStatement.setString(3, department.getCode());
// 注意，日期要传sql.Date类型
pStatement.setDate(4, new Date(department.getNewDate().getTime()));
pStatement.setString(5, department.getDescs());
// 执行更新
res = pStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭连接及接口对象
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(pStatement);
}
if (res == 1) {
System.out.println("已成功向Department表中添加一条数据！");
}
}
}
（3）测试
public static void main(String[] args) {
// 测试添加
Department dep = new Department("DEPID1009", "测试添加", "CS1001", new Date(), "测试测试！");
DepDao depDao = new DepDao();
depDao.addDepartment(dep);
}
执行，控制台：
查看Department表：
记录添加成功。
2.7.3、JDBC进行数据的修改
现在想修改刚才添加的那条新数据。在DepDao中添加以下方法：
// 定义一个方法，修改部门表记录
public void updateDepartment(Department department) {
int res = 0;
// sql语句
String sql = "update department set NAME=?,DESCS=? where ID=?";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps = null;
try {
// 获取PreparedStatement对象
ps = connection.prepareStatement(sql);
// 占位符替换
ps.setString(1, department.getName());
ps.setString(2, department.getDescs());
ps.setString(3, department.getId());
// 执行更新
res = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps);
}
if (res == 1) {
System.out.println("成功更新了Department表中ID为" + department.getId() + "的部门记录！");
}
}
测试：
public static void main(String[] args) {
// 测试修改
Department dep = new Department();
dep.setId("DEPID1009");
dep.setName("测试修改");
dep.setDescs("修改修改！");
DepDao depDao = new DepDao();
depDao.updateDepartment(dep);
}
执行，控制台：
查看Department表：
修改成功。
2.7.4、JDBC进行数据的删除
现在想删除刚才修改的那条数据。在DepDao中添加以下方法：
// 定义一个方法，删除部门表记录
public void deleteDepartment(String id) {
int res = 0;
// sql语句
String sql = "delete from department where ID=?";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps = null;
try {
// 获取PreparedStatement对象
ps = connection.prepareStatement(sql);
// 替换占位符
ps.setString(1, id);
// 执行更新
res = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps);
}
if (res == 1) {
System.out.println("已成功删除Department表中ID为" + id + "的部门记录！");
}
}
测试：
public static void main(String[] args) {
// 测试删除
String id = "DEPID1009";
DepDao depDao = new DepDao();
depDao.deleteDepartment(id);
}
执行，控制台：
查看Department表：
DEPID1009的记录已经删除了。
2.8、ResultSet接口
这个接口类似于一个临时表，用来暂时存放数据库查询操作的所获得的结果集。它是JDBC API中唯一用来封装查询结果记录行的组件，ResultSet接口唯一创建方式是通过执行SQL查询返回创建此对象。ResultSet实例具有指向当前数据行的指针，指针的开始位置在第1条记录的前面，通过next()方法可将指针向下移动。api文档说明如下：
提供的方法如下：
由于方法比较多，就不进行说明了，标记的是用的较多的，其实用的最多的还是getXXX方法和next()方法，可以自行翻阅API文档查看。
2.9、使用JDBC进行查询
先查询Department表，这张表中只有8条记录。在DepDao中添加以下方法：
// 定义一个方法，插叙部门表的所有记录
public List<Department> getAllDepartments() {
// 容器
List<Department> departments = new ArrayList<>();
// sql语句
String sql = "select ID,NAME,CODE,NEWDATE,DESCS from department";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
// 获取PreparedStatement对象
ps = connection.prepareStatement(sql);
// 执行查询，获取结果集
resultSet = ps.executeQuery();
// 结果集中有记录时，指针下移遍历结果集
while (resultSet.next()) {
Department department = new Department();
department.setId(resultSet.getString("ID"));
department.setName(resultSet.getString("NAME"));
department.setCode(resultSet.getString("CODE"));
department.setNewDate(resultSet.getDate("NEWDATE"));
department.setDescs(resultSet.getString("DESCS"));
// 封装各条记录，加到容器中
departments.add(department);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps);
DataSourceManager.closeResultSet(resultSet);
}
return departments;
}
测试：
public static void main(String[] args) {
// 测试查询
DepDao depDao = new DepDao();
List<Department> departments = depDao.getAllDepartments();
System.out.println("一共有" + departments.size() + "个部门，信息如下：");
for (Department d : departments) {
System.out.println(d.getId() + "\t" + d.getName() + "\t" + d.getNewDate());
}
}
执行，控制台：
查询成功。
现在想查询Employee部门表中的数据：
一共有21条记录。
（1）实体类
查看表结构，先创建与表对应的实体类，在pojo包下创建Employee类，如下：
public class Employee {
private String id;//员工ID
private String name;//员工姓名
private Integer gender;//员工性别
private Date birth;//员工生日
private String address;//住址
private String phone;//员工联系方式
private Date entryDate;//员工进入部门的时间
private String email;//员工邮箱
private String depId;//员工所在部门编号
private String descs;//说明
//构造方法
public Employee(String id,String name,Integer gender,Date birth,String address,String phone,
Date entryDate,String email,String depId,String descs) {
this.id = id;
this.name = name;
this.gender = gender;
this.birth = birth;
this.address = address;
this.phone = phone;
this.entryDate = entryDate;
this.email = email;
this.depId = depId;
this.descs = descs;
}
public Employee() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getEntryDate() {
return entryDate;
}
public void setEntryDate(Date entryDate) {
this.entryDate = entryDate;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getDepId() {
return depId;
}
public void setDepId(String depId) {
this.depId = depId;
}
public String getDescs() {
return descs;
}
public void setDescs(String descs) {
this.descs = descs;
}
}
（2）数据访问层
在dao包下创建EmpDao类，如下：
/*
* 数据访问层
*/
public class EmpDao {
// 定义一个方法，查询员工表的所有记录
public List<Employee> getAllEmployees() {
// 容器
List<Employee> employees = new ArrayList<>();
// sql语句
String sql = "select ID,NAME,GENDER,BIRTH,ADDRESS,PHONE,ENTRYDATE,EMAIL,DEPID,DESCS from employee";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
// 获取PreparedStatement对象
ps = connection.prepareStatement(sql);
// 执行查询，获取ResultSet对象
resultSet = ps.executeQuery();
while (resultSet.next()) {
Employee employee = new Employee();
employee.setId(resultSet.getString("ID"));
employee.setName(resultSet.getString("NAME"));
employee.setGender(resultSet.getInt("GENDER"));
employee.setBirth(resultSet.getDate("BIRTH"));
employee.setAddress(resultSet.getString("ADDRESS"));
employee.setPhone(resultSet.getString("PHONE"));
employee.setEntryDate(resultSet.getDate("ENTRYDATE"));
employee.setEmail(resultSet.getString("EMAIL"));
employee.setDepId(resultSet.getString("DEPID"));
employee.setDescs(resultSet.getString("DESCS"));
// 封装结果，加到容器中
employees.add(employee);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps);
DataSourceManager.closeResultSet(resultSet);
}
return employees;
}
}
测试：
public static void main(String[] args) {
// 测试查询
EmpDao empDao = new EmpDao();
List<Employee> employees = empDao.getAllEmployees();
System.out.println("一共有" + employees.size() + "名员工，信息如下：");
for (Employee emp : employees) {
String sex = emp.getGender() == 1 ? "男" : "女";
System.out.println(emp.getId() + "\t" + emp.getName() + "\t"
+ sex + "\t" + emp.getAddress() + "\t" + emp.getBirth());
}
}
执行，控制台如下：
没问题，查询成功。
2.10、使用JDBC进行分页查询
只需要添加分页参数就行了，不过前提是要知道数据库支持的分页语句，MySQL和Oracle数据库的分页语句是不同的。这里就用简单一点的分页语句：
select * from 表 rownum<= a
minus
select * from 表 rownum<= b;
控制a、b的值就能实现分页。
在EmpDao中添加以下方法：
// 定义一个方法，实现分页查询
public Map<String, Object> queryEmpsPaged(int page, int size) {
// 容器
Map<String, Object> map = new HashMap<>();
int total = 0;// 总记录条数
int pageNum = 0;// 总页数
// sql，查询总记录条数
String sql0 = "select count(ID) from employee";
// sql，分页查询
String sql2 = "select ID,NAME,GENDER,BIRTH,ADDRESS from employee where rownum<=? minus "
+ "select ID,NAME,GENDER,BIRTH,ADDRESS from employee where rownum<=?";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps0 = null;
ResultSet resultSet0 = null;
PreparedStatement ps2 = null;
ResultSet resultSet2 = null;
try {
// 获取PreparedStatement对象
ps0 = connection.prepareStatement(sql0);
resultSet0 = ps0.executeQuery();
while (resultSet0.next()) {
total = resultSet0.getInt(1);
}
// 总页数
pageNum = getPages(total, size);
// 存进map
map.put("total", total);
map.put("pageNum", pageNum);
ps2 = connection.prepareStatement(sql2);
// 替换占位符
ps2.setInt(1, page * size);
ps2.setInt(2, (page - 1) * size);
resultSet2 = ps2.executeQuery();
// 容器
List<Employee> list = new ArrayList<>();
while (resultSet2.next()) {
Employee emp = new Employee();
emp.setId(resultSet2.getString("ID"));
emp.setName(resultSet2.getString("NAME"));
emp.setGender(resultSet2.getInt("GENDER"));
emp.setAddress(resultSet2.getString("ADDRESS"));
emp.setBirth(resultSet2.getDate("BIRTH"));
list.add(emp);
}
// 存进map
map.put("empList", list);
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps0);
DataSourceManager.closeStatement(ps2);
DataSourceManager.closeResultSet(resultSet0);
DataSourceManager.closeResultSet(resultSet2);
}
return map;
}
// 定义私有方法，统计页数
private int getPages(int totalSize, int size) {
return totalSize % size == 0 ? totalSize / size : (totalSize / size) + 1;
}
测试：
public static void main(String[] args) {
// 测试分页查询
EmpDao empDao = new EmpDao();
Scanner in = new Scanner(System.in);
System.out.println("请输入页码数：");
int page = in.nextInt();
System.out.println("请输入每页记录条数：");
int size = in.nextInt();
Map<String,Object> map = empDao.queryEmpsPaged(page, size);
//获取总记录条数和页数
int total = (Integer) map.get("total");
int pageSum = (Integer) map.get("pageNum");
System.out.println("一共" + total + "条记录，一共" + pageSum + "页！");
System.out.println("-----------------------------------------");
System.out.println("当前第" + page + "页，总共" + pageSum + "页！");
System.out.println("-----------------------------------------");
List<Employee> employees = (List<Employee>) map.get("empList");
for(Employee emp:employees) {
String sex = emp.getGender() == 0 ? "女" : "男";
System.out.println(emp.getId() + "\t" + emp.getName() + "\t"
+ sex + "\t" + emp.getBirth() + "\t" + emp.getAddress());
}
}
执行，控制台：
OK，分页没问题。
2.11、使用JDBC进行模糊查询
模糊查询只要传入参数就行了。在EmpDao中添加以下方法：
// 定义一个方法，按照姓名和住址进行模糊查询
public List<Employee> queryByConditions(String name, String address) {
// 容器
List<Employee> employees = new ArrayList<>();
// sql语句
String sql = "select ID,NAME,GENDER,BIRTH,ADDRESS from employee where name like '" + name + "%'"
+ " or address like '%" + address + "'";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
ps = connection.prepareStatement(sql);
resultSet = ps.executeQuery();
while (resultSet.next()) {
Employee emp = new Employee();
emp.setId(resultSet.getString("ID"));
emp.setName(resultSet.getString("NAME"));
emp.setGender(resultSet.getInt("GENDER"));
emp.setBirth(resultSet.getDate("BIRTH"));
emp.setAddress(resultSet.getString("ADDRESS"));
employees.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps);
DataSourceManager.closeResultSet(resultSet);
}
return employees;
}
测试：
public static void main(String[] args) {
// 测试模糊查询
EmpDao empDao = new EmpDao();
//查找姓黄或者住址在某道观的记录
List<Employee> employees = empDao.queryByConditions("黄", "道观");
for(Employee emp:employees) {
String sex = emp.getGender() == 0 ? "女" : "男";
System.out.println(emp.getId() + "\t" + emp.getName() + "\t"
+ sex + "\t" + emp.getBirth() + "\t" + emp.getAddress());
}
}
执行，控制台：
2.12、使用JDBC进行多表连接查询
因为查询的是多表，所以不能用List集合，因为List集合泛型规定了数据类型，很明显这查的是多表中的数据，并不是一种类型，可以考虑使用Map进行封装。
在dao包下创建DepEmpDao，如下：
/*
* 数据访问层
*/
public class DepEmpDao {
// 定义方法，进行表连接查询
public List<Map<String, Object>> getDepEmp() {
// 容器
List<Map<String, Object>> list = new ArrayList<>();
// sql语句
String sql = "select dep.NAME dName,emp.Name eName,emp.gender eGender,emp.address eAddress"
+ " from department dep inner join employee emp on dep.ID = emp.DEPID order by dep.NAME";
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
ps = connection.prepareStatement(sql);
resultSet = ps.executeQuery();
while (resultSet.next()) {
// 容器
Map<String, Object> map = new HashMap<>();
map.put("dName", resultSet.getString("dNAME"));
map.put("eName", resultSet.getString("eName"));
map.put("eGender", resultSet.getInt("eGender"));
map.put("eAddress", resultSet.getString("eAddress"));
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps);
DataSourceManager.closeResultSet(resultSet);
}
return list;
}
}
测试：
public static void main(String[] args) {
// 测试表连接查询
DepEmpDao deDao = new DepEmpDao();
//查找姓黄或者住址在某道观的记录
List<Map<String,Object>> list = deDao.getDepEmp();
System.out.println("一共" + list.size() + "条记录！");
System.out.println("----------------------------------");
for(Map<String,Object> map:list) {
String dName = (String) map.get("dName");
String eName = (String) map.get("eName");
String sex = map.get("eGender").toString().equals("0") ? "女" : "男";
String address = (String) map.get("eAddress");
System.out.println(dName + "\t" + eName + "\t"
+ sex + "\t" + address);
}
}
执行，控制台：
查询结果没问题。
2.13、使用JDBC调用函数
想在JDBC中调用整个函数。
函数是有返回值的，调用函数使用PreparedStatement接口即可，就普通的SQL语句。
dao包下创建类FunctionDao，如下：
/*
* 数据访问层
*/
public class FunctionDao {
// 测试调用函数Function
public int useFunction(int a, int b) {
int res = 0;
// sql语句
String sql = "select fun_demo2(?,?) from dual";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
ps = connection.prepareStatement(sql);
//参数替换占位符
ps.setInt(1, a);
ps.setInt(2, b);
// 执行
resultSet = ps.executeQuery();
while (resultSet.next()) {
//获取返回值
res = resultSet.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps);
DataSourceManager.closeResultSet(resultSet);
}
return res;
}
}
测试：
public static void main(String[] args) {
// 测试调用函数
FunctionDao fDao = new FunctionDao();
int res = fDao.useFunction(25, 4);
System.out.println("函数的返回结果是：" + res);
}
执行，控制台：
返回结果正确，没问题。
2.14、CallableStatement接口
这个接口是和存储过程有关的，它是PreparedStatement接口的子接口。api文档说明如下：
提供的方法如下：
用的比较多的方法是getXXX和registerOutParameter这两个方法，其余参考API文档。
2.15、使用JDBC调用存储过程
现在想调用这个存储过程。
调用存储过程需要用到CallableStatement接口。下面进行测试。
在dao包下创建类ProcudureDao类，如下：
/*
* 数据访问层
*/
public class ProcedureDao {
// 测试调用存储过程
public float useProcedure(float up, float down, float h, float area) {
// sql
String sql = "{call pro_demo1(?,?,?,?)}";
// 获取连接
Connection connection = DataSourceManager.getConnection();
CallableStatement cs = null;
try {
// 获取CallableStatement对象
// CallableStatement是PreparedStatement的子接口，是处理存储过程的唯一组件
cs = connection.prepareCall(sql);
// 参数替换占位符
cs.setFloat(1, up);
cs.setFloat(2, down);
cs.setFloat(3, h);
// 注册输出参数类型
cs.registerOutParameter(4, Types.FLOAT);
// 执行
cs.execute();
// 获取输出参数值
area = cs.getFloat(4);
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭
try {
connection.close();
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return area;
}
}
测试：
public static void main(String[] args) {
// 测试调用存储过程
ProcedureDao pDao = new ProcedureDao();
float res = pDao.useProcedure(2.5f, 7.5f, 4.0f, 0.0f);
System.out.println("梯形的面积是：" + res);
}
执行，控制台：
计算结果没问题，调用存储过程成功。
2.16、使用JDBC进行批处理
批处理（Batch proces）是指一次连接访问数据中发送一组SQL操作语句，通常对数据库多条数据行进行更新操作。合理使用批处理能够在最少次访问数据库时执行多条SQL操作从而提高数据库的访问速度并提高数据库的应用效率。一般情况下批处理执行类似的相近操作，如批量修改、批量处理、删除插入等。
现在想往这张表中一次性插入10条记录：
那么会用到批处理。下面使用JDBC写一个进行批处理的例子。
（1）实体类
先看表结构：
然后设计实体类。在pojo包下创建类Person，如下：
public class Person {
private String name;// 姓名
private int age;// 年龄
private float height;// 身高
private double money;// 工资
// 构造器
public Person(String name, int age, float height, double money) {
this.name = name;
this.age = age;
this.height = height;
this.money = money;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public float getHeight() {
return height;
}
public void setHeight(float height) {
this.height = height;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
}
（2）数据访问层
在dao包下创建类PersonDao，如下：
/*
* 数据访问层
*/
public class PersonDao {
// 测试批处理插入记录
public void insertButch(List<Person> persons) {
// sql语句
String sql = "insert into person(NAME,AGE,HEIGHT,MONEY) values (?,?,?,?)";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sql);
for (Person p : persons) {
// 占位符替换
ps.setString(1, p.getName());
ps.setInt(2, p.getAge());
ps.setFloat(3, p.getHeight());
ps.setDouble(4, p.getMoney());
// 添加到批处理
ps.addBatch();
}
// 执行批处理，返回int型数组
int[] res = ps.executeBatch();
System.out.println("成功向Person表中添加" + res.length + "条记录！");
} catch (SQLException e) {
e.printStackTrace();
} finally {
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps);
}
}
}
（3）测试
public static void main(String[] args) {
// 测试批处理
PersonDao pDao = new PersonDao();
List<Person> list = new ArrayList<>();
Person p0 = new Person("程序员A", 25, 178.5f, 50000.2);
Person p2 = new Person("程序员B", 25, 178.5f, 50000.2);
Person p3 = new Person("程序员C", 25, 178.5f, 50000.2);
Person p4 = new Person("程序员D", 25, 178.5f, 50000.2);
Person p5 = new Person("程序员E", 25, 178.5f, 50000.2);
Person p6 = new Person("程序员F", 25, 178.5f, 50000.2);
Person p7 = new Person("程序员G", 25, 178.5f, 50000.2);
Person p8 = new Person("程序员H", 25, 178.5f, 50000.2);
Person p9 = new Person("程序员I", 25, 178.5f, 50000.2);
Person p10 = new Person("程序员J", 25, 178.5f, 50000.2);
list.add(p0);
list.add(p2);
list.add(p3);
list.add(p4);
list.add(p5);
list.add(p6);
list.add(p7);
list.add(p8);
list.add(p9);
list.add(p10);
pDao.insertButch(list);
}
执行，控制台：
查看Person表：
添加成功，那么批处理成功了。
2.17、使用JDBC进行事务处理
事务的四大特性：
- 原子性：不可分割。
- 隔离性：事务之间的边界。
- 一致性：确保数据正确一致。
- 持久性：永久影响数据库。
对于默认的JDBC操作，事务的执行是自动开启的，每个SQL执行都将在不产生错误时自动提交，如果在一个线程方法中涉及多个相关SQL语句操作，则应在程序中设计事务的手动处理，要么全部成功提交事务（commit），要么出现操作异常回滚（rollback）所有操作。
事务处理的相关方法：
- setAutoCommit(boolean auto Commit)：要想成功关闭，里面布尔值要是false。
- commit()：事务提交。
- rollback()：事务撤销。
- setSavePoint(String name)：设置保存点。
- setTransactionsolation(int level)：设置事务的隔离级别。
- rollback(Savepoint savepoint)：回滚到保存点。
事务的隔离级别
事务的隔离级别有5种，如下：
- transaction_none：不支持事务。
- transaction_read_commited：禁止脏读和不可重复读，允许虚读（默认）。
- transaction_read_uncommited：允许脏读、不可重复读和虚读。
- transaction_repeatable_read：禁止脏读和不可重复读，允许虚读。
- transaction_serializable：禁止脏读、不可重复读和虚读。
专有名词说明
- 脏读：dirty reads，当事务读取还未被提交的数据时，就会发生这种事件。举例来说，Transaction1修改了一行数据，然后Transaction2在Transaction1还未提交修改操作之前读取了被修改的行。如果Transaction1回滚了修改操作，那么Transaction2读取的数据就可以看作是从未存在过的。
- 不可重复读：non-repeatable reads，当事务两次读取同一行数据，但每次得到的数据都不一样时，就会发生这事件。举例来说：Transaction1读取一行数据，然后Transaction2修改或删除该行并提交修改操作。当Transaction1试图重新读取该行时，它就会得到不同的数据值（如果该行被更新）或发现该行不再存在（如果该行被删除）。
- 虚读：phantom read，如果符合搜索条件的一行数据在后面的读取操作中出现，但该行数据却不属于最初的数据，就会发生这种事件。比如：Transaction1读取满足某些搜索条件的一些行，然后Transaction2插入了符合Transaction1的搜索条件的一个新行。如果Transaction1重新执行产生原来那些行的查询，就会得到不同的行。
这是以前写的转账事务：
-- 银行转账业务
declare
success string(64):='转账成功！';
fail string(64):='转账失败！';
-- 定义异常变量
error_fail exception;
-- 异常变量初始化
pragma exception_init(error_fail,-2290);
begin
-- 转入记录
insert into business values(trig_seq.nextval,10000,1,to_char(systimestamp,'yyyy-MM-dd HH24:mi:ss'),trig_seq.nextval);
-- 转出记录
insert into business values(trig_seq.nextval,10000,0,to_char(systimestamp,'yyyy-MM-dd HH24:mi:ss'),trig_seq.nextval);
--更新账户余额表
--转入方账户加钱
update accounts set balance=balance+10000 where id=102;
--转出方账户扣钱
update accounts set balance=balance-10000 where id=103;
-- 事务提交
commit;
-- 转账成功时
dbms_output.put_line(success);
-- 失败时
exception when error_fail then
dbms_output.put_line(fail);
-- 事务回滚
rollback;
end;
两张表如下：
下面使用JDBC来实现这个事务。
在dao包下创建TransactionDao类，如下：
/*
* 数据处理层
*/
public class TransactionDao {
// 模拟银行转账事务
public void jdbcTransaction(double money, int inId, int outId) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateStr = sdf.format(new Date());
// sql语句
String sql0 = "insert into business values(trig_seq.nextval,?,1,?,?)";
String sql2 = "insert into business values(trig_seq.nextval,?,0,?,?)";
String sql3 = "update accounts set balance = balance + ? where id = ?";
String sql4 = "update accounts set balance = balance - ? where id = ?";
// 获取连接
Connection connection = DataSourceManager.getConnection();
PreparedStatement ps0 = null;
PreparedStatement ps2 = null;
PreparedStatement ps3 = null;
PreparedStatement ps4 = null;
try {
// 关闭事务的自动提交
connection.setAutoCommit(false);
ps0 = connection.prepareStatement(sql0);
// 替换参数
ps0.setDouble(1, money);
ps0.setString(2, dateStr);
ps0.setInt(3, inId);
ps2 = connection.prepareStatement(sql2);
// 替换参数
ps2.setDouble(1, money);
ps2.setString(2, dateStr);
ps2.setInt(3, outId);
ps3 = connection.prepareStatement(sql3);
ps3.setDouble(1, money);
ps3.setInt(2, inId);
ps4 = connection.prepareStatement(sql4);
ps4.setDouble(1, money);
ps4.setInt(2, outId);
// 执行
ps0.executeUpdate();
ps2.executeUpdate();
ps3.executeUpdate();
ps4.executeUpdate();
// 事务手动提交
connection.commit();
System.out.println("转账成功！");
} catch (SQLException e) {
e.printStackTrace();
// 发生异常说明失败
System.err.println("转账失败！");
// 事务进行回滚
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
// 关闭
DataSourceManager.closeConnection(connection);
DataSourceManager.closeStatement(ps0);
DataSourceManager.closeStatement(ps2);
DataSourceManager.closeStatement(ps3);
DataSourceManager.closeStatement(ps4);
}
}
}
测试：
public static void main(String[] args) {
// 测试转账事务
TransactionDao tDao = new TransactionDao();
tDao.jdbcTransaction(10000, 1000, 999);
}
执行，控制台：
查看表：
转账成功，交易表中添加了两条记录，余额表中用户的余额更新了。
现在ID为999的余额为0，无法进行转账，再执行一次：
因为余额的约束是不小于0，这里直接报错，再看余额表：
没变化，说明事务回滚成功。
3、数据库连接池
其实前面用到了c3p0的连接池，但是在代码里我并没有使用。如下：
3.1、数据库连接池的概念
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个;释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。
在程序初始化的时候创建一定数量的数据库连接，用完可以放回去，下一个在接着用，通过配置连接池的参数来控制连接池中的初始连接数、最小连接、最大连接、最大空闲时间这些参数保证访问数据库的数量在一定可控制的范围类，防止系统崩溃。
3.2、使用连接池的理由
数据库连接是一种关键、有限的、昂贵的资源，创建和释放数据库连接是一个很耗时的操作，频繁地进行这样的操作将占用大量的性能开销，进而导致网站的响应速度下降，严重的时候可能导致服务器崩溃；数据库连接池可以节省系统许多开销。
3.3、连接池的原理
连接池基本的思想是在系统初始化的时候，将数据库连接作为对象存储在内存中，当用户需要访问数据库时，并非建立一个新的连接，而是从连接池中取出一个已建立的空闲连接对象。使用完毕后，用户也并非将连接关闭，而是将连接放回连接池中，以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时，还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等。也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。
3.4、关于连接数
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数制约。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
对于最小连接数和最大连接数的设置应考虑以下几点：
- 最小连接数：是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。
- 最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过此数，后面的数据库连接请求将被加入到等待队列中，这会影响之后的数据库操作。
- 最小连接数与最大连接数差值：最小连接数与最大连接数相差太大，那么最先的连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，它将被放到连接池中等待重复使用或是空闲超时后被释放。
3.5、Java中常见的连接池
- C3P0：是一个开放源代码的JDBC连接池，它在lib目录中与Hibernate 一起发布，包括了实现jdbc3和jdbc2扩展规范说明的Connection 和Statement 池的DataSources 对象。
- Proxool：是一个Java SQL Driver驱动程序，提供了对选择的其它类型的驱动程序的连接池封装。可以非常简单的移植到现存的代码中，完全可配置，快速、成熟、健壮。可以透明地为现存的JDBC驱动程序增加连接池功能。
- Jakarta DBCP：DBCP是一个依赖Jakartacommons-pool对象池机制的数据库连接池。DBCP可以直接的在应用程序中使用。
- BoneCP：是一个快速、开源的数据库连接池。帮用户管理数据连接，让应用程序能更快速地访问数据库。比C3P0/DBCP连接池速度快25倍。
- Druid：Druid不仅是一个数据库连接池，还包含一个ProxyDriver、一系列内置的JDBC组件库、一个SQL Parser。支持所有JDBC兼容的数据库，包括Oracle、MySql、Derby、Postgresql、SQL Server、H2等。Druid针对Oracle和MySql做了特别优化，这个连接池非常高效，推荐优先使用Druid连接池。
557
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
