学习笔记——查询方法工具类&&连接池

一、学习重点

查询方法工具类

二、学习内容

demo1

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

/**
 * 约束
 * 约定
 */
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);
}

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.jsoft.morning.Ch01;

import javax.sql.DataSource;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

/**
 * 约定：
 * 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);
            }
        }
    }

    @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<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;
    }

    @Override
    public void update(Object obj, String fieldName, Object fieldValue) {

    }

    @Override
    public void delete(Class clazz, String fieldName, Object fieldValue) {

    }

    @Override
    public T findOne(Class clazz, String fieldName, Object fieldValue) {
        return null;
    }
}

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> {
}

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> {
}

demo2

import java.sql.SQLException;
import java.util.List;

public interface DAO<T> {

    /**
     * 更新
     * @return
     */
    int update(String sql, Object... args) throws Exception;

    /**
     * 通用的查询所有
     */
    List<T> getForList(String sql, Object... args) throws Exception;

    /**
     * 通用的查询单个
     */
    T get(String sql, Object... args) throws Exception;

    /**
     * 查询某一个列的值，统计
     */
    <E> E getForValue(String sql, Object... args) throws SQLException;

}

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.ParameterizedType;
import java.sql.SQLException;
import java.util.List;

public class DAOImpl<T> implements DAO<T> {

    private QueryRunner runner = null;
    private Class<T> type;

    /**
     * 这个构造器中在做的事：
     *  为了获取Class<T> type = Teacher.class
     */
    public DAOImpl() {
        runner = new QueryRunner(JDBCUtil.getDataSource());
        // 获得当前类的带有泛型类型的父类（运行期this其实是DAOImpl的某个子类）
        ParameterizedType ptClass = (ParameterizedType) this.getClass().getGenericSuperclass();
        type = (Class<T>) ptClass.getActualTypeArguments()[0];
    }

    @Override
    public int update(String sql, Object... args) throws Exception {
        return runner.update(sql,args);
    }

    @Override
    public List<T> getForList(String sql, Object... args) throws Exception {
        return runner.query(sql,new BeanListHandler<>(type),args);
    }

    @Override
    public T get(String sql, Object... args) throws Exception {
        return runner.query(sql,new BeanHandler<>(type),args);
    }

    @Override
    public <E> E getForValue(String sql, Object... args) throws SQLException {

        return (E) runner.query(sql,new ScalarHandler<>(),args);
    }
}

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.jsoft.morning.Ch01;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.Properties;

public class JDBCUtil {

    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);
        }
    }

    public static DataSource getDataSource() {
        return DATA_SOURCE;
    }

}

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 DAOImpl<Teacher> {
}

import org.junit.Test;

import java.lang.invoke.VarHandle;
import java.util.List;

public class TeacherDaoTest {
    TeacherDao teacherDao = new TeacherDao();
    @Test
    public void test02(){

        try {
            List<Teacher> teachers = teacherDao.getForList("select * from teacher");
//            System.out.println(teachers);
            Teacher teacher = teacherDao.get("select id,name from teacher where id = 1");
//            System.out.println(teacher);
            long r = teacherDao.getForValue("select count(*) from teacher where name = ?", "Rose");
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }


    @Test
    public void test01() {

        try {
            int i = teacherDao.update("insert into teacher(name) values (?)", "李四");
            System.out.println(i);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
}

三、学习笔记

import com.alibaba.druid.pool.DruidDataSource;
import org.junit.Test;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 数据库连接池：
 * connection是一种稀有资源，一个连接建立就创造了一个资源
 * QQ连上了，我的QQ和腾讯的服务器建立了一个连接，有代价，同时在线人数多有可能导致服务器崩溃
 * 第一种方案：一个人玩
 * 第二种方案：把服务器的人数限定一下，最多不超过10000人，第10001个人上线，排队
 *
 * JDBC使用数据库连接池的必要性
 * 在使用基于web程序的数据库连接
 * 1、在主程序中建立连接
 * 2、执行ＳＱＬ
 * ３、断开连接
 * 所有的JDBC连接通过DriverManager.getConnection
 * 用完的连接不要被垃圾回收，能够重复使用
 * "池化思想"
 * 每次去初始化一个连接池，连接池中会有很多个连接等待被使用
 * 使用完连接之后，不需要关闭连接，只需要把连接还回到连接池，
 * 还回到连接池的操作不需要手动控制
 * 初始化连接池，10条连接，来了20个请求，10个请求就直接拿10条连接去办事
 * 剩下的10个请求，再向服务器申请连接数
 * 设置一些属性：最大等待时间
 * （1）C3P0，2代数据库连接池，过时
 * （2）DBCP，2代数据库连接池，过时
 * （3）Druid（德鲁伊）数据库连接池，最好的连接池
 *      阿里巴巴开源平台上的一个数据库连接池实现，整合了C3P0和DBCP各自优点
 *      加入了日志文件，可以监控ｓｑｌ语句的执行情况。
 * （4）Hikari（光），目前最快的连接池。springboot默认的连接池。
 *
 * 必须有对应的属性文件
 * .properties
 * 约定 > 配置 > 编码
 *
 */
public class Ch01 {
    @Test
    public void test01() throws IOException, SQLException {
        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());



    }
}

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.Test;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Properties;

public class Ch02 {

    @Test
    public void test01() throws IOException, SQLException {
        Properties properties = new Properties();
        properties.load(Ch01.class.getClassLoader().getResourceAsStream("hikari.properties"));

        HikariConfig hikariConfig = new HikariConfig(properties);
        HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);

        System.out.println(hikariDataSource.getConnection());
    }
}

import org.junit.Test;
import util.BaseDao;

import java.sql.Connection;
import java.sql.SQLException;

public class Ch03 {

    @Test
    public void test01() {
        try {
            Connection connection = BaseDao.DATA_SOURCE.getConnection();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

工具类

package util;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.jsoft.morning.Ch01;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;
import java.util.Properties;

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 release(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);
            }
        }
    }

}

import com.jsoft.morning.demo2.Teacher;
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 util.BaseDao;

import java.sql.SQLException;
import java.util.List;

/**
 * 需要引入一个依赖jar包
 * DBUtils
 */
public class Ch01 {

    @Test
    public void test03() throws SQLException {

        QueryRunner runner = new QueryRunner(BaseDao.DATA_SOURCE);
        int i = runner.update("update teacher set name = ? where id = ?", "mmm", 6);
        System.out.println(i);

    }

    /**
     * 查询一个记录
     */
    @Test
    public void test02() throws SQLException {
        QueryRunner runner = new QueryRunner(BaseDao.DATA_SOURCE);
        Teacher teacher = runner.query("select * from teacher where id = ?", new BeanHandler<>(Teacher.class), 1);
        System.out.println(teacher);
    }

    /**
     * 查询多个记录
     * @throws SQLException
     */
    @Test
    public void test01() throws SQLException {
        // 要使用DBUtils使用的是一个类
        // 传入的是一个数据源DataSource，不是一个Connection
        QueryRunner runner = new QueryRunner(BaseDao.DATA_SOURCE);
        // 查询多个记录
        List<Teacher> teachers = runner.query("select * from teacher", new BeanListHandler<>(Teacher.class));
        System.out.println(teachers);
    }

}

posted @ 2022-08-21 21:23 LJMMJL 阅读(28) 评论(0) 收藏举报

ljmskipper

学习笔记——查询方法工具类&&连接池

一、学习重点

二、学习内容

三、学习笔记

公告