目录
JDBC使用数据库连接的必要性：在使用基于web程序的数据库连接
数据库连接池
connection是一种稀有资源，一个连接建立就创造了一个资源。
2Q连上了，我的oQ和腾讯的服务器建立了一个连接，有代价，同时在线人数很多
有可能导致服务器崩溃。
第一种方案:我一个人玩
第二种方案:把服务器的人数限定一下，最多不超过10000人,第10001个人上线，排队。
JDBC使用数据库连接的必要性：在使用基于web程序的数据库连接
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());
}
}
小案例1：
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<Teacher> findAllTeacher() {
PreparedStatement pstmt = null;
Connection conn = null;
List<Teacher> 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());
}
}
小案例2：
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<User> {
}
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<Teacher> {
}约定：
1、表名和类型名必须相同
2、表的字段名和类的属性名必须相同
@param <T>
public class BaseDaoImpl<T> implements IBaseDao<T> {
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);
}
}
}
/**
* 通用的保存方法
* @param object 传入一个要保存的对象
*/
@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);
}
}
/**
* 通用的查询所有的方法
* @param clazz 要操作的对象.class类型
* @return
*/
@Override
public List<T> findAll(Class clazz) {
// 拼sql
// select id,username,password from user
// 其中id,username,password可变的他们都是一个类的属性
List<T> 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;
}
/**
* 通用的修改
* @param obj 要修改的对象
* @param fieldName 根据什么去修改数据 id
* @param fieldValue 根据条件的值 1
*/
@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);
}
}
/**
* 通用的删除
* @param clazz 要删除的类.class
* @param fieldName 根据什么去删除 id
* @param fieldValue 根据的条件的值 1
*/
@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);
}
}
/**
* 查询某一条记录
* @param clazz 要查询的类.class
* @param fieldName 根据什么去查询 id
* @param fieldValue 查询的条件的值 1
* @return
*/
@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<T> {
/**
* 获取连接的方法
*/
Connection getConnection();
/**
* 关闭资源
*/
void closeAll(Statement statement, ResultSet resultSet);
/**
* 通用的保存
*/
void save(Object object);
/**
* 通用的查询所有
*/
List<T> 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);
}
}
}
}
133
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
