目录
数据库连接池
JDBC使用数据库连接的必要性：在使用基于web程序的数据库连接
“池化思想”
小案例1：
小案例2：
connection是一种稀有资源，一个连接建立就创造了一个资源。
2Q连上了，我的oQ和腾讯的服务器建立了一个连接，有代价，同时在线人数很多
有可能导致服务器崩溃。
第一种方案:我一个人玩
第二种方案:把服务器的人数限定一下，最多不超过10000人,第10001个人上线，排队。
1、在主程序中建立连接
2、执行sQL
3、断开连接
所有的JDBC连接通动DriverManager. getConnection 。
用完的连接不要被垃圾回收，能够重复使用
每次去初始化一个连接池，连接池中会有很多个连接等待被使用。
使用完连接之后，不需要关闭连接，只需要把连接还回到连接池，还回到连接池的操作不需要我们手动控制。
初始化连接池，10条连接，来了20个请求，10个请求就直接拿10条连接去办事剩下的10个请求，在向服务器申请链接数。
设置一些属性，最大等待时间。
（1）C3P0,2代数据库连接池，太老了，不学
（2）DBCP，2代数据库连接池，太老了，不学
（3）Druid(德鲁伊) 数据库连接池，最好用的连接池。
阿里巴巴开源平台上的一个数据库连接池实现，整合了C3P0和DBCP各自的优点
加入了日志监控，可以监控SQL语句的执行情况
（4）Hikari(光)，目前最快的连接池。springboot默认的连接池
public class Ch01 {
@Test
public void test01() throws SQLException, IOException {
Properties properties = new Properties();
properties.load(Ch01.class.getClassLoader().getResourceAsStream("druid.properties"));
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.configFromPropety(properties);
System.out.println(druidDataSource.getConnection());
System.out.println(druidDataSource.getCreateCount());
}
}
public class Ch02 {
@Test
public void test01() throws IOException, SQLException {
Properties prop = new Properties();
prop.load(Ch01.class.getClassLoader().getResourceAsStream("hikari.properties"));
HikariConfig hikariConfig = new HikariConfig(prop);
HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
System.out.println(hikariDataSource.getConnection());
}
}
public class Teacher {
private Integer id;
private String name;
public Teacher(String name) {
}
public Teacher(Integer id, String name) {
this.id = id;
this.name = name;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + ''' +
'}';
}
}
public class TeacherDao extends BaseDao {
public int saveTeacher(Teacher teacher) {
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "insert into teacher (name) values (?)";
try {
conn = DATA_SOURCE.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, teacher.getName());
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(conn, pstmt, null);
}
}
public int updateTeacher(Teacher teacher) {
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "update teacher set name = ? where id = ?";
try {
conn = DATA_SOURCE.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, teacher.getName());
pstmt.setInt(2, teacher.getId());
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(conn, pstmt, null);
}
}
public int deleteTeacher(Integer id) {
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "delete from teacher where id = ?";
try {
conn = DATA_SOURCE.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(conn, pstmt, null);
}
}
public List findAllTeacher() {
PreparedStatement pstmt = null;
Connection conn = null;
List list = new ArrayList<>();
ResultSet rs = null;
String sql = "SELECT * FROM teachers ";
try {
conn = DATA_SOURCE.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()){
Integer id = rs.getInt("id");
String name = rs.getString("name");
Teacher teacher = new Teacher(id,name);
list.add(teacher);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(conn, pstmt, rs);
}
return list;
}
public Teacher findOneTeacher(Integer sid) {
PreparedStatement pstmt = null;
Connection conn = null;
ResultSet rs = null;
TeacherDao t = null;
String sql = "SELECt * FROM teachers where id = ?";
try {
conn = DATA_SOURCE.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
pstmt.setInt(1, 1);
while (rs.next()) {
String name = rs.getString("name");
Integer id = rs.getInt("id");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(conn, pstmt, rs);
}
return null;
}
}
public class Test {
public static void main(String[] args) {
TeacherDao teacherDao = new TeacherDao();
System.out.println(teacherDao.findAllTeacher());
}
}
public class User {
private Integer id;
private String username;
private String password;
public User() {
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = 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 "User{" +
"id=" + id +
", username='" + username + ''' +
", password='" + password + ''' +
'}';
}
}
public class UserDao extends BaseDaoImpl{ }
public class Teacher {
private Integer id;
private String name;
public Teacher() {
}
public Teacher(Integer id, String name) {
this.id = id;
this.name = name;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + ''' +
'}';
}
}
public class TeacherDao extends BaseDaoImpl{ }
约定：
1、表名和类型名必须相同
2、表的字段名和类的属性名必须相同
@param
public class BaseDaoImplimplements IBaseDao { private static final DataSource DATA_SOURCE; static { Properties properties = new Properties(); try { properties.load(Ch01.class.getClassLoader().getResourceAsStream("druid.properties")); // 创建德鲁伊的数据源 DATA_SOURCE = DruidDataSourceFactory.createDataSource(properties); } catch (IOException e) { throw new RuntimeException(e); } catch (Exception e) { throw new RuntimeException(e); } } @Override public Connection getConnection() { try { return DATA_SOURCE.getConnection(); } catch (SQLException e) { throw new RuntimeException(e); } } @Override public void closeAll(Statement stmt, ResultSet rs) { if(Objects.nonNull(stmt)) { try { stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(Objects.nonNull(rs)){ try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } @Override public void save(Object object) { // insert into user(id,username,password) values (?,?,?) Class clazz = object.getClass(); Field[] fields = clazz.getDeclaredFields(); // 拼接出一个insert语句 StringBuilder strb = new StringBuilder("insert into "); // insert into user String[] split = clazz.getName().split("\."); strb.append(split[split.length - 1]); strb.append(" ("); for (Field field : fields) { strb.append(field.getName().toLowerCase()).append(","); } // insert into user (id,username,password strb.deleteCharAt(strb.length() - 1); strb.append(") values ("); for (Field field : fields) { strb.append("?,"); } strb.deleteCharAt(strb.length() - 1); strb.append(")"); PreparedStatement pstmt = null; try { Connection conn = DATA_SOURCE.getConnection(); pstmt = conn.prepareStatement(strb.toString()); // 给?赋值 for (int i = 0; i < fields.length; i++) { fields[i].setAccessible(true); pstmt.setObject(i+1,fields[i].get(object)); } pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } catch (IllegalAccessException e) { throw new RuntimeException(e); } finally { closeAll(pstmt,null); } } @Override public List findAll(Class clazz) { // 拼sql // select id,username,password from user // 其中id,username,password可变的他们都是一个类的属性 List list = new ArrayList<>(); PreparedStatement pstmt = null; ResultSet rs = null; // 利用反射获取属性名 Field[] fields = clazz.getDeclaredFields(); // 拼装sql语句，拼字符串 StringBuilder fieldStr = new StringBuilder(); fieldStr.append("select "); for (Field field : fields) { // id,username,password, fieldStr.append(field.getName().toLowerCase()).append(","); } // select id,username,password fieldStr.deleteCharAt(fieldStr.length() - 1); fieldStr.append(" from "); // select id,username,password from // String clazzName = clazz.getName().toLowerCase(); System.out.println(clazzName + "--------------------"); String[] split = clazzName.split("\."); fieldStr.append(split[split.length - 1]); // select id,username,password from user Connection conn = getConnection(); try { pstmt = conn.prepareStatement(fieldStr.toString()); rs = pstmt.executeQuery(); while(rs.next()){ // 1. 创建对象 Object obj = clazz.getDeclaredConstructor().newInstance(); for (Field field : fields) { Object value = rs.getObject(field.getName()); // 访问私有化的结构 field.setAccessible(true); // 利用反射给属性赋值，赋不上值 // 因为属性一定是private field.set(obj,value); } list.add((T) obj); } } catch (SQLException e) { throw new RuntimeException(e); } catch (InvocationTargetException e) { throw new RuntimeException(e); } catch (InstantiationException e) { throw new RuntimeException(e); } catch (IllegalAccessException e) { throw new RuntimeException(e); } catch (NoSuchMethodException e) { throw new RuntimeException(e); } finally { closeAll(pstmt,rs); } return list; } @Override public void update(Object obj, String fieldName, Object fieldValue) { PreparedStatement pstmt = null; Class clazz = obj.getClass(); // 拼接出一个update语句 // update user set StringBuilder strb = new StringBuilder("update " + clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1) + " set "); Field[] fields = clazz.getDeclaredFields(); for (Field field : fields) { field.setAccessible(true); // update user set username = ?,password = ?, strb.append(field.getName()).append(" = ").append("?").append(","); } strb.deleteCharAt(strb.length() - 1); // update user set username = ?,password = ? strb.append(" where ").append(fieldName).append("=").append(fieldValue); // System.out.println(strb.toString()); try { Connection conn = DATA_SOURCE.getConnection(); pstmt = conn.prepareStatement(strb.toString()); for (int i = 0; i < fields.length; i++) { fields[i].setAccessible(true); pstmt.setObject(i+1,fields[i].get(obj)); } pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } catch (IllegalAccessException e) { throw new RuntimeException(e); } finally { closeAll(pstmt,null); } } @Override public void delete(Class clazz, String fieldName, Object fieldValue) { // 拼接一个delete语句 PreparedStatement pstmt = null; StringBuilder sql = new StringBuilder("delete from "); // delete from user sql.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1)); sql.append(" where ").append(fieldName).append(" = ?"); try { Connection conn = DATA_SOURCE.getConnection(); pstmt = conn.prepareStatement(sql.toString()); pstmt.setObject(1,fieldValue); pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } finally { closeAll(pstmt,null); } } @Override public T findOne(Class clazz, String fieldName, Object fieldValue) { T t = null; PreparedStatement pstmt = null; ResultSet rs = null; // 拼接一个select语句 Field[] fields = clazz.getDeclaredFields(); StringBuilder strb = new StringBuilder(); strb.append("select "); for (Field field : fields) { strb.append(field.getName().toLowerCase()).append(","); } strb.deleteCharAt(strb.length() - 1); // select id,username,password strb.append(" from "); strb.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1)); // select id,username,password from user strb.append(" where "); strb.append(fieldName).append("= ?"); // select id,username,password from user where id = ? try { Connection conn = DATA_SOURCE.getConnection(); pstmt = conn.prepareStatement(strb.toString()); pstmt.setObject(1,fieldValue); rs = pstmt.executeQuery(); while(rs.next()) { Object o = clazz.getDeclaredConstructor().newInstance(); for (Field field : fields) { Object value = rs.getObject(field.getName()); field.setAccessible(true); field.set(o,value); } t = (T) o; } } catch (SQLException e) { throw new RuntimeException(e); } catch (InvocationTargetException e) { throw new RuntimeException(e); } catch (InstantiationException e) { throw new RuntimeException(e); } catch (IllegalAccessException e) { throw new RuntimeException(e); } catch (NoSuchMethodException e) { throw new RuntimeException(e); } return t; } }
接口：
public interface IBaseDao{ Connection getConnection(); void closeAll(Statement statement, ResultSet resultSet); void save(Object object); List findAll(Class clazz); void update(Object obj,String fieldName,Object fieldValue); void delete(Class clazz,String fieldName,Object fieldValue); T findOne(Class clazz,String fieldName,Object fieldValue); }
public class Demo {
public static void main(String[] args) {
// UserDao userDao = new UserDao();
// System.out.println(Demo.class.getName());
// UserDao userDao = new UserDao();
// System.out.println(userDao.findAll(User.class));
// System.out.println(Demo.class.getName());
// System.out.println(userDao.findAll(User.class));
// userDao.update(new User(2,"xyz","654321"),"id",2);
TeacherDao teacherDao = new TeacherDao();
// System.out.println(teacherDao.findAll(Teacher.class));
teacherDao.save(new Teacher(10,"HH"));
// System.out.println(teacherDao.findAll(Teacher.class));
// teacherDao.save(new Teacher(10,"HH"));
// teacherDao.update(new Teacher(10,"zzz"),"id",10);
// teacherDao.delete(Teacher.class,"id",10);
System.out.println(teacherDao.findOne(Teacher.class, "id", 5));
}
}
工具类：
public class BaseDao {
public static final DataSource DATA_SOURCE;
static {
Properties properties = new Properties();
try {
properties.load(Ch01.class.getClassLoader().getResourceAsStream("druid.properties"));
// 创建德鲁伊的数据源
DATA_SOURCE = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs){
if (Objects.nonNull(stmt)){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(Objects.nonNull(conn)){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(Objects.nonNull(rs)){
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
public class util {
public static final DataSource DATA_SOURCE;
static {
Properties properties = new Properties();
try {
properties.load(Ch01.class.getClassLoader().getResourceAsStream("druid.properties"));
// 创建德鲁伊的数据源
DATA_SOURCE = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs){
if (Objects.nonNull(stmt)){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(Objects.nonNull(conn)){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(Objects.nonNull(rs)){
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
