1. 数据库连接池
1. 概念:存放数据库链接的一个容器。当系统初始化完成后,容器被创建,容器中会申请一些连接对象,当用户访问数据库时,从容器中获取连接对象,当用户访问完之后,会将连接对象归还给容器
2. 好处
(1)节约资源
(2)用户访问高效
3. 实现
(1)标准接口:DataSource(javax.sql包下的)
1. 方法:
获取连接:getConnection()
归还连接:Connection.close():如果连接对象Connection是从连接池获取的,那么调用Connection.close()方法,则不会在关闭连接,而是归还连接
(2)一般不用标准接口,使用数据库厂商实现的
1. C3P0:数据库连接技术
2. Druid:数据库连接池技术,由阿里巴巴提供
2. C3P0:数据库连接技术
1. 使用步骤
1. 导入jar包:
1. c3p0-0.9.5.2.jar
2. mchange-commons-java-0.2.12.jar
2. 定义配置文件:
名称:c3p0.properties 或者 c3p0-config.xml
路径:src下
3. 创建核心对象,数据库连接池对象:ComboPooledDataSource
4. 获取连接:getConnection
2. 代码示例
/**
* C3P0基本演示
*/
public class C3P0Demo01 {
public static void main(String[] args) throws SQLException {
//1. 创建数据库连接对象
ComboPooledDataSource ds = new ComboPooledDataSource();
//2. 获取连接对象
Connection conn = ds.getConnection();
//3. 打印
System.out.println(conn); //com.mchange.v2.c3p0.impl.NewProxyConnection@1677d1 [wrapping: com.mysql.jdbc.JDBC4Connection@48fa0f47]
}
}
===============================================================================================
/**
* C3P0获取连接对象
*/
public class C3P0Demo02 {
public static void main(String[] args) throws SQLException {
//1. 获取DataSource,使用默认配置
ComboPooledDataSource ds = new ComboPooledDataSource();
//2. 获取连接
for (int i = 1; i <= 10; i++) {
Connection conn = ds.getConnection();
System.out.println(i + ":" + conn);
}
/*
1:com.mchange.v2.c3p0.impl.NewProxyConnection@1e802ef9 [wrapping: com.mysql.jdbc.JDBC4Connection@2b6faea6]
2:com.mchange.v2.c3p0.impl.NewProxyConnection@670002 [wrapping: com.mysql.jdbc.JDBC4Connection@1f0f1111]
3:com.mchange.v2.c3p0.impl.NewProxyConnection@56528192 [wrapping: com.mysql.jdbc.JDBC4Connection@6e0dec4a]
4:com.mchange.v2.c3p0.impl.NewProxyConnection@5ccddd20 [wrapping: com.mysql.jdbc.JDBC4Connection@1ed1993a]
5:com.mchange.v2.c3p0.impl.NewProxyConnection@794cb805 [wrapping: com.mysql.jdbc.JDBC4Connection@4b5a5ed1]
6:com.mchange.v2.c3p0.impl.NewProxyConnection@3cc2931c [wrapping: com.mysql.jdbc.JDBC4Connection@20d28811]
7:com.mchange.v2.c3p0.impl.NewProxyConnection@60d8c9b7 [wrapping: com.mysql.jdbc.JDBC4Connection@48aaecc3]
8:com.mchange.v2.c3p0.impl.NewProxyConnection@7adda9cc [wrapping: com.mysql.jdbc.JDBC4Connection@5cee5251]
9:com.mchange.v2.c3p0.impl.NewProxyConnection@5c909414 [wrapping: com.mysql.jdbc.JDBC4Connection@4b14c583]
10:com.mchange.v2.c3p0.impl.NewProxyConnection@4ddced80 [wrapping: com.mysql.jdbc.JDBC4Connection@1534f01b]
*/
}
}
3. Druid:数据库连接技术
1. 使用步骤
1. 导入jar包:druid-1.0.9.jar
2. 定义配置文件
1. 是properties形式的
2. 可以叫任意名称,放在任意目录下
3. 加载配置文件:Properties
4. 获取数据库连接对象:通过工厂来获取:DruidDataSourceFactory
5. 获取连接:getConnection
2. 代码示例
/**
* 使用新的工具类
* 完成添加操作:给lol表添加一条记录
*/
public class DruidDemo02 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
//1. 获取连接
conn = JDBCUtils.getConnection();
//2. 定义sql
String sql = "insert into lol values(?,?,?)";
//3. 获取pstmt对象
pstmt = conn.prepareStatement(sql);
//4. 给?赋值
pstmt.setInt(1,3);
pstmt.setString(2,"tf");
pstmt.setInt(3,22);
//5. 执行sql
int count = pstmt.executeUpdate();
//6. 打印
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7. 释放资源
JDBCUtils.close(pstmt,conn);
}
}
}
===============================================================================================
/**
* Druid连接池的工具类
*/
public class JDBCUtils {
//1. 定义成员变量
private static DataSource ds;
static {
try {
//1. 加载配置文件
Properties pro = new Properties();
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
//2. 获取DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 释放资源
*/
public static void close(Statement stmt,Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs,Statement stmt, Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取连接池方法
*/
public static DataSource getDataSource(){
return ds;
}
}
4. Spring JDBC
1. 概念: Spring框架对JDBC的简单封装,提供了一个JDBCTemplate对象简化JDBC开发
2. 步骤
1. 导入jar包
2. 创建JDBCTemplate对象。依赖于数据源DataSource
JdbcTemplate template = new JdbcTemplate(ds)
3. 调用JDBCTemplate的方法完成CRUD的操作
1. update():执行DML语句。增、删、改语句
2. queryForMap():将查询结果封装为map集合,将列名作为key,值作为value,将这条记录封装为一个map集合
【注意】这个方法查询的结果集长度只能为1
3. queryForList():将查询结果集封装为List集合
【注意】将每一条结果封装为一个map集合,再将map集合封装到list中
4. query():将查询结果封装为JavaBean对象
query的参数:RowMapper
一般使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
new BeanPropertyRowMapper<类型>(类型.class)
5. queryForObject():将查询结果封装为对象
【注意】一般用于聚合函数的查询
3. 代码示例1
/**
* JDBCTemplate入门
*/
public class JDBCTemplateDemo01 {
public static void main(String[] args) {
//1. 导入jar包
//2. 创建JDBCTemplate对象
JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
//3. 调用方法
String sql = "update lol set age = 44 where id = ?";
int count = template.update(sql, 3);
System.out.println(count);
}
}
===============================================================================================
/**
* Druid连接池的工具类
*/
public class JDBCUtils {
//1. 定义成员变量
private static DataSource ds;
static {
try {
//1. 加载配置文件
Properties pro = new Properties();
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
//2. 获取DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 释放资源
*/
public static void close(Statement stmt,Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs,Statement stmt, Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取连接池方法
*/
public static DataSource getDataSource(){
return ds;
}
}
4. 代码示例2
/**
* 需求:
* 1. 修改1号数据的 salary 为 10000
* 2. 添加一条记录
* 3. 删除刚才添加的记录
* 4. 查询id为1的记录,将其封装为Map集合
* 5. 查询所有记录,将其封装为List
* 6. 查询所有记录,将其封装为Lol对象的List集合
* 7. 查询总记录数
*/
public class JDBCTemplateDemo02 {
//1. 获取JDBCTemplate对象
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
//1. 修改1号数据的age为 10000
@Test
public void test1(){
String sql = "update lol set age = 10000 where id = 1";
int count = template.update(sql);
System.out.println(count); //1
}
//2. 添加一条记录
@Test
public void test2(){
String sql = "insert into lol(id,name,age) values(?,?,?)";
int count = template.update(sql,4,"lol",23);
System.out.println(count); //1
}
//3. 删除刚才添加的记录
@Test
public void test3(){
String sql = "delete from lol where id = 4";
int count = template.update(sql);
System.out.println(count); //1
}
//4. 查询id为1的记录,将其封装为Map集合
@Test
public void test4(){
String sql = "select * from lol where id = ?";
Map<String, Object> map = template.queryForMap(sql, 1);
System.out.println(map); //{id=1, name=noc, age=10000}
}
//5. 查询所有记录,将其封装为List
@Test
public void test5(){
String sql = "select * from lol ";
List<Map<String, Object>> list = template.queryForList(sql);
for (Map<String, Object> map : list) {
System.out.println(map);
}
}
/**
* {id=1, name=noc, age=10000}
* {id=2, name=mf, age=18}
* {id=3, name=tf, age=44}
*/
//6_1. 查询所有记录,将其封装为Lol对象的List集合
@Test
public void test6_1(){
String sql = "select * from lol";
List<Lol> list = template.query(sql, new RowMapper<Lol>() {
@Override
public Lol mapRow(ResultSet rs, int i) throws SQLException {
Lol lol = new Lol();
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
lol.setId(id);
lol.setName(name);
lol.setAge(age);
return lol;
}
});
for (Lol lol : list) {
System.out.println(lol);
}
/**
* Lol{id=1, name='noc', age=10000}
* Lol{id=2, name='mf', age=18}
* Lol{id=3, name='tf', age=44}
*/
}
// 6_2. 查询所有记录,将其封装为Lol对象的List集合
@Test
public void test6_2(){
String sql = "select * from lol";
List<Lol> list = template.query(sql, new BeanPropertyRowMapper<Lol>(Lol.class));
for (Lol lol : list) {
System.out.println(lol);
}
/**
* Lol{id=1, name='noc', age=10000}
* Lol{id=2, name='mf', age=18}
* Lol{id=3, name='tf', age=44}
*/
}
//7. 查询总记录数
@Test
public void test7(){
String sql = "select count(id) from lol";
Long total = template.queryForObject(sql, Long.class);
System.out.println(total); //3
}
}