配套资料，免费下载
链接: https://pan.baidu.com/s/1CKiwCbQV4FGg_4YMQoebkg
提取码: 7cn3
复制这段内容后打开百度网盘手机App，操作更方便哦
第一章 概述
JDBC（Java Database Connectivity）是SUN公司提供的一种数据库访问规则、规范，由于数据库种类较多且Java语言使用比较广泛，SUN公司就提供了一种规范，让其它的数据库提供商去实现底层的访问规则，我们的Java程序只要使用SUN公司提供的jdbc驱动就可以连接不同厂商的数据库了
第二章 数据库连接
1、工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtil {
private static String driverClass = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/mytest";
private static String user = "root";
private static String password = "root";
static {
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void release(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
closeConnection(connection);
closePreparedStatement(preparedStatement);
closeResultSet(resultSet);
}
public static void release(Connection connection, PreparedStatement preparedStatement) {
closeConnection(connection);
closePreparedStatement(preparedStatement);
}
public static void closeConnection(Connection connection) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
connection = null;
}
}
}
public static void closePreparedStatement(PreparedStatement preparedStatement) {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
preparedStatement = null;
}
}
}
public static void closeResultSet(ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
resultSet = null;
}
}
}
}
2、测试类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class JDBCTest {
@Test
public void testQuery() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
String sql = "select * from admin";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object username = resultSet.getObject(2);
Object password = resultSet.getObject(3);
System.out.println(id + ":" + username + ":" + password);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection, preparedStatement, resultSet);
}
}
@Test
public void testInsert() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection();
String sql = "insert into admin values(NULL,'xiaowu','123456')";
preparedStatement = connection.prepareStatement(sql);
int rows = preparedStatement.executeUpdate(sql);
if (rows > 0) {
System.out.println("插入成功");
} else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection, preparedStatement);
}
}
@Test
public void testUpdate() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection();
String sql = "update admin set username = 'xiaoqi' where id = 5";
preparedStatement = connection.prepareStatement(sql);
int rows = preparedStatement.executeUpdate(sql);
if (rows > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection, preparedStatement);
}
}
@Test
public void testDelete() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection();
String sql = "delete from admin where id = 5";
preparedStatement = connection.prepareStatement(sql);
int rows = preparedStatement.executeUpdate(sql);
if (rows > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection, preparedStatement);
}
}
}
第三章 数据库事务
@Test
public void testTransaction() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection();
// 前提：关闭事务
connection.setAutoCommit(false);
String sql = "update account set money = money - ? where id = ?";
preparedStatement = connection.prepareStatement(sql);
// 扣钱， 扣ID为 1 的100块钱
preparedStatement.setInt(1, 100);
preparedStatement.setInt(2, 1);
preparedStatement.executeUpdate();
// 模拟异常
int a = 10 / 0;
// 加钱， 给ID为 2 加100块钱
preparedStatement.setInt(1, -100);
preparedStatement.setInt(2, 2);
preparedStatement.executeUpdate();
// 成功：提交事务
connection.commit();
} catch (SQLException e) {
try {
// 失败：回滚事务
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtil.release(connection, preparedStatement);
}
}
第四章 数据库连接池
4.1、DBCP
1、基本使用
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp2.BasicDataSource;
public class DBCPTest {
public static void main(String[] args) throws SQLException {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUsername("root");
dataSource.setPassword("root");
Connection connection = dataSource.getConnection();
String sql = "select * from admin";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
ResultSet resultSet = prepareStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object username = resultSet.getObject(2);
Object password = resultSet.getObject(3);
System.out.println(id + ":" + username + ":" + password);
}
resultSet.close();
connection.close();
dataSource.close();
}
}
2、高级使用
dbcp-config.properties
#基本设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mytest
username=root
password=root
#数据库连接池初始化数量
initialSize=10
#数据库连接池中的最大的数据库连接数
maxActive=50
#数据库连接池中的最小的数据库连接数
minIdle=5
DBCPWithConfig.java
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
public class DBCPWithConfig {
@SuppressWarnings("static-access")
public static void main(String[] args) throws Exception {
BasicDataSourceFactory dataSourceFactory = new BasicDataSourceFactory();
Properties properties = new Properties();
InputStream is = DBCPWithConfig.class.getClassLoader().getResourceAsStream("dbcp-config.properties");
properties.load(is);
DataSource dataSource = dataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
String sql = "select * from admin";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
ResultSet resultSet = prepareStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object username = resultSet.getObject(2);
Object password = resultSet.getObject(3);
System.out.println(id + ":" + username + ":" + password);
}
resultSet.close();
connection.close();
}
}
4.2、Druid
1、基本使用
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.alibaba.druid.pool.DruidDataSource;
public class DruidTest {
public static void main(String[] args) throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUsername("root");
dataSource.setPassword("root");
Connection connection = dataSource.getConnection();
String sql = "select * from admin";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
ResultSet resultSet = prepareStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object username = resultSet.getObject(2);
Object password = resultSet.getObject(3);
System.out.println(id + ":" + username + ":" + password);
}
resultSet.close();
connection.close();
dataSource.close();
}
}
2、高级使用
druid-config.properties
#基本设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mytest
username=root
password=root
#数据库连接池初始化数量
initialSize=10
#数据库连接池中的最大的数据库连接数
maxActive=50
#数据库连接池中的最小的数据库连接数
minIdle=5
DruidWithConfig.java
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidWithConfig {
@SuppressWarnings("static-access")
public static void main(String[] args) throws Exception {
DruidDataSourceFactory dataSourceFactory = new DruidDataSourceFactory();
Properties properties = new Properties();
InputStream is = DruidWithConfig.class.getClassLoader().getResourceAsStream("druid-config.properties");
properties.load(is);
DataSource dataSource = dataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
String sql = "select * from admin";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
ResultSet resultSet = prepareStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object username = resultSet.getObject(2);
Object password = resultSet.getObject(3);
System.out.println(id + ":" + username + ":" + password);
}
resultSet.close();
connection.close();
}
}
4.3、C3P0
1、基本使用
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Test {
public static void main(String[] args) throws SQLException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
Connection connection = dataSource.getConnection();
String sql = "select * from admin";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
ResultSet resultSet = prepareStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object username = resultSet.getObject(2);
Object password = resultSet.getObject(3);
System.out.println(id + ":" + username + ":" + password);
}
resultSet.close();
connection.close();
dataSource.close();
}
}
2、高级使用
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<!-- 基本设置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1/mytest</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 数据库连接池初始化数量 -->
<property name="initialPoolSize">10</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">100</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">10</property>
</default-config>
</c3p0-config>
C3P0WithConfig.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0WithConfig {
public static void main(String[] args) throws SQLException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
Connection connection = dataSource.getConnection();
String sql = "select * from admin";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
ResultSet resultSet = prepareStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object username = resultSet.getObject(2);
Object password = resultSet.getObject(3);
System.out.println(id + ":" + username + ":" + password);
}
resultSet.close();
connection.close();
dataSource.close();
}
}
第五章 DButils
1、基本的使用
Admin.java
public class Admin {
private Integer id;
private String username;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Admin [id=" + id + ", username=" + username + ", password=" + password + "]";
}
}
DBUtilsTest.java
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBUtilsTest {
@Test
public void testQueryOne() {
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql = "select * from admin where id = ?";
Admin admin = queryRunner.query(sql, new BeanHandler<Admin>(Admin.class), 1);
System.out.println(admin);
dataSource.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testQueryAll() {
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql = "select * from admin";
List<Admin> admins = queryRunner.query(sql, new BeanListHandler<Admin>(Admin.class));
for (Admin admin : admins) {
System.out.println(admin);
}
dataSource.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testInsert() {
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql = "insert into admin values(NULL,?,?)";
int rows = queryRunner.update(sql, "xiaojiu", "123456");
if (rows > 0) {
System.out.println("插入成功");
} else {
System.out.println("插入失败");
}
dataSource.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testUpdate() {
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql = "update admin set username = 'xiaoqi' where id = ?";
int rows = queryRunner.update(sql, 4);
if (rows > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
dataSource.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testDelete() {
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql = "delete from admin where id = ?";
int rows = queryRunner.update(sql, 6);
if (rows > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
dataSource.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
注意问题：
BeanHandler，查询到的单个数据封装成一个对象
BeanListHandler，查询到的多个数据封装成一个List<对象>
ArrayHandler，查询到的单个数据封装成一个数组
ArrayListHandler，查询到的多个数据封装成一个集合，集合里面的元素是数组
MapHandler，查询到的单个数据封装成一个map
MapListHandler，查询到的多个数据封装成一个集合，集合里面的元素是Map
ColumnListHandler
KeyedHandler
ScalarHandler
2、自定义实现
ResultSetHandler.java
import java.sql.ResultSet;
import java.sql.SQLException;
public interface ResultSetHandler<T> {
/**
* 定义数据封装的规则
*
* @param <T>
* @param rs
*/
T handle(ResultSet rs) throws SQLException;
}
MyQueryRunner.java
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class MyQueryRunner {
private DataSource dataSource = null;
public MyQueryRunner() {
super();
}
public MyQueryRunner(DataSource dataSource) {
super();
this.dataSource = dataSource;
}
public <T> T query(String sql, ResultSetHandler<T> handler, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement(sql);
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
preparedStatement.setObject(i + 1, args[i]);
}
ResultSet resultSet = preparedStatement.executeQuery();
return (T) handler.handle(resultSet);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection, preparedStatement);
}
return null;
}
public int update(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement(sql);
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
preparedStatement.setObject(i + 1, args[i]);
}
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection, preparedStatement);
}
return 0;
}
}
Admin.java
public class Admin {
private Integer id;
private String username;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Admin [id=" + id + ", username=" + username + ", password=" + password + "]";
}
}
MyQueryRunnerTest.java
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class MyQueryRunnerTest {
@Test
public void testQueryOne() {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
MyQueryRunner queryRunner = new MyQueryRunner(dataSource);
String sql = "select * from admin where id = ?";
Admin admin = queryRunner.query(sql, new ResultSetHandler<Admin>() {
@Override
public Admin handle(ResultSet rs) throws SQLException {
while (rs.next()) {
Admin admin = new Admin();
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
admin.setId(id);
admin.setUsername(username);
admin.setPassword(password);
return admin;
}
return null;
}
}, 1);
System.out.println(admin);
dataSource.close();
}
@Test
public void testQueryAll() {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
MyQueryRunner queryRunner = new MyQueryRunner(dataSource);
String sql = "select * from admin";
List<Admin> admins = queryRunner.query(sql, new ResultSetHandler<List<Admin>>() {
@Override
public List<Admin> handle(ResultSet rs) throws SQLException {
List<Admin> admins = new ArrayList<Admin>();
while (rs.next()) {
Admin admin = new Admin();
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
admin.setId(id);
admin.setUsername(username);
admin.setPassword(password);
admins.add(admin);
}
return admins;
}
});
for (Admin admin : admins) {
System.out.println(admin);
}
dataSource.close();
}
@Test
public void testInsert() {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
MyQueryRunner queryRunner = new MyQueryRunner(dataSource);
String sql = "insert into admin values(NULL,?,?)";
int rows = queryRunner.update(sql, "xiaoqiang", "123456");
if (rows > 0) {
System.out.println("插入成功");
} else {
System.out.println("插入失败");
}
dataSource.close();
}
@Test
public void testUpdate() {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
MyQueryRunner queryRunner = new MyQueryRunner(dataSource);
String sql = "update admin set username = 'mingming' where id = ?";
int rows = queryRunner.update(sql, 4);
if (rows > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
dataSource.close();
}
@Test
public void testDelete() {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest");
dataSource.setUser("root");
dataSource.setPassword("root");
MyQueryRunner queryRunner = new MyQueryRunner(dataSource);
String sql = "delete from admin where id = ?";
int rows = queryRunner.update(sql, 7);
if (rows > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
dataSource.close();
}
}