|
连接池：是用来管理数据库连接的一种技术。在做数据库操作时，反复的创建连接和关闭连接耗费时间。
连接池的工作过程：
创建连接池时，在连接池中初始化一定数量的连接，当应用程序获得连接时，某个连接变为忙状态，根据配置的最 小空闲连接数，连接池创建新的连接，但连接数量不能超过配置的最大连接数。当应用程序用完连接后，连接变为 空闲状态，当所有的连接都是忙状态，应用程序获得连接时将等待。
常用的连接池有：
1. dbcp连接池：老牌的连接池
2. c3p0连接池：先对dbcp连接池稳定，但速度慢。
3. hirake连接池：springboot框架默认使用这个连接池，速度比druid快
4. druid德鲁伊连接池：阿里巴巴开发的连接池，对SQL语句进行监控、拦截的功能。
当然也可以自己写连接池，但通常不这么做，用写好的成熟的连接池
DBCP连接池：
public class Test {
public static void main(String[] args) {
BasicDataSource pool = new BasicDataSource();
pool.setDriverClassName("com.mysql.jdbc.Driver");
pool.setUrl("jdbc:mysql://localhost:3306/java2113?characterEncoding=utf8");
pool.setUsername("root");
pool.setPassword("root");
pool.setInitialSize(3);
pool.setMinIdle(3);
pool.setMaxActive(5);
try {
Connection conn = pool.getConnection();
System.out.println(conn.isClosed());
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.oracle.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import org.apache.commons.dbcp.BasicDataSource;
import com.oracle.vo.Student;
//通用的数据库操作类
public class BaseDao {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
ResultSetMetaData rsmd;
static String driver ;
static String url;
static String user;
static String password;
static String initSize;
static String minIdle;
static String maxActive;
static BasicDataSource pool;
static {
ResourceBundle bundle = ResourceBundle.getBundle("db");
driver = bundle.getString("driver");
url = bundle.getString("url");
user = bundle.getString("user");
password = bundle.getString("password");
initSize = bundle.getString("initSize");
minIdle = bundle.getString("minIdle");
maxActive = bundle.getString("maxActive");
pool = new BasicDataSource();
pool.setDriverClassName(driver);
pool.setUrl(url);
pool.setUsername(user);
pool.setPassword(password);
pool.setInitialSize(Integer.parseInt(initSize));
pool.setMinIdle(Integer.parseInt(minIdle));
pool.setMaxActive(Integer.parseInt(maxActive));
}
public int insert(String sql,Object[] values) {
getConnection();
int result = 0;
try {
stmt = conn.prepareStatement(sql);
for(int i = 0;i<values.length;i++) {
stmt.setObject(i+1, values[i]);
}
result = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
public int update(String sql,Object[] values) {
return insert(sql,values);
}
public int delete(String sql,Object[] values) {
return insert(sql,values);
}
//按条件查询
//结果集的数据向 vo中填充属性 通过接口 描述填充的过程
public <T> List<T> select(String sql,Object[] values,Convert<T> convert){
List<T> list = new ArrayList<T>();
getConnection();
try {
stmt = conn.prepareStatement(sql);
for(int i = 0;i<values.length;i++) {
stmt.setObject(i+1, values[i]);
}
rs = stmt.executeQuery();
while(rs.next()) {
T t = convert.convert(rs);
list.add(t);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
//按id查询
public <T> T selectById(String sql,Object[] values,Convert<T> convert) {
getConnection();
try {
stmt = conn.prepareStatement(sql);
for(int i = 0;i<values.length;i++) {
stmt.setObject(i+1, values[i]);
}
rs = stmt.executeQuery();
if(rs.next()) {
return convert.convert(rs);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
//获得连接
public Connection getConnection() {
try {
conn = pool.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void main(String[] args) {
//向学生表插入一条记录
/*String sql = "insert into student(sname,tel,amt) values(?,?,?)";
Object[] values = new Object[] {"abc","13111111111",1000};
CommonData cdata = new CommonData();
System.out.println(cdata.insert(sql, values));*/
//id是35的学生电话修改为11111111111
/*String sql = "update student set tel=? where id=?";
Object[] values = new Object[] {"11111111111",35};
CommonData cdata = new CommonData();
System.out.println(cdata.update(sql, values));*/
String sql = "delete from student where id = ?";
Object[] values = new Object[] {35};
BaseDao cdata = new BaseDao();
System.out.println(cdata.delete(sql, values));
//显示所有学生的姓名和电话号
List<Student> list = cdata.select("select * from student", new Object[] {}, new
StudentConvert());
for(Student s:list) {
System.out.println(s.getSname());
}
Student s = cdata.selectById("select * from student where id=?", new Object[] {21}, new
StudentConvert());
System.out.println(s.getSname());
/* List<Student> list = cdata.select("select * from student", new Object[] {},(rs)-> {
Student s = new Student();
try {
s.setId(rs.getInt("id"));
s.setAmt(rs.getInt("amt"));
s.setSname(rs.getString("sname"));
} catch (SQLException e) {
e.printStackTrace();
}
return s;
});*/
}
}
class StudentConvert implements Convert<Student> {
@Override
public Student convert(ResultSet rs) {
Student s = new Student();
try {
s.setId(rs.getInt("id"));
s.setAmt(rs.getInt("amt"));
s.setSname(rs.getString("sname"));
s.setBirthday(rs.getDate("birthday"));
} catch (SQLException e) {
e.printStackTrace();
}
return s;
}
}
|