JDBC工具类-CSDN博客

本文链接：https://blog.csdn.net/qq_46058550/article/details/128918998

文章展示了在Java项目中如何使用Maven依赖管理MySQL驱动和Druid数据源，以及配置JDBC连接属性。JDBCUtils类用于线程安全地获取和释放数据库连接，实现了连接池的单例模式。BaseDao抽象类提供了增删改查的基本方法，利用反射处理查询结果到实体类的映射。测试类对这些功能进行了验证。

摘要由CSDN通过智能技术生成

1.依赖

     <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>

2.jdbc.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3308/test?characterEncoding=UTF-8&useSSL=false
username=root
password=123456

3.JDBCUtils

package top.remained.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

// TODO：利用线程本地变量，存储连接信息！确保一个线程获取的是同一个conn
//  优势：事物操作的时候 service和Dao属于同一个线程 不用传递其参数
//  只加载一次 声明static 单例模式
public class JDBCUtils {
    private static DataSource dataSource = null;
    private static ThreadLocal<Connection> tl = new ThreadLocal<>();
//   加载配置
    static {
    Properties properties = new Properties();
    InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
    try {
        properties.load(is);
    }catch (Exception e){
        throw  new RuntimeException(e);
    }
    try {
     dataSource =   DruidDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
    //    获取连接
    public static Connection getConnection() throws SQLException {
//        判断线程本地是否存在
        if (tl.get() == null) {
            Connection connection = dataSource.getConnection();
            tl.set(connection);
            return connection;
        }
        return tl.get();
    }
//    断开连接
    public static void closeConnection() throws SQLException {
      Connection connection = tl.get();
        if (connection != null) {
//            清空线程本地数据变量
            tl.remove();
//          事物回归
            connection.setAutoCommit(true);
            connection.close();
        }
    }

}

4.BaseDao

package top.remained.dao;

import top.remained.utils.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public abstract class BaseDao {
//        增删改
    public int executeUpdate(String sql,Object... params) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
//        占位符赋值
        for (int i = 1; i <= params.length; i++) {
//            注意params是i-1
            ps.setObject(i,params[i-1]);
        }
        int row = ps.executeUpdate();
        ps.close();
//        是否回收连接 判断是否是事物
        if (connection.getAutoCommit()) {
//            未开启 直接关闭
            JDBCUtils.closeConnection();
        }
//        开启事物 业务层管理
        return  row;
    }
//    查询 返回List<T> 
//    如果返回List<Map>  map没有数据校验机制 不支持反射操作
//    TODO:必须有参构造 且属性名对应的变量名一致 如不一致 可用别名
    public <T> List<T> executeQuery(Class<T> clazz,String sql,Object... params) throws SQLException {
//        获取连接
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
//        占位符赋值
        if (params != null && params.length>0) {
            for (int i = 1; i <= params.length; i++) {
                ps.setObject(i, params[i - 1]);
            }
        }
            ResultSet resultSet = ps.executeQuery(sql);
//            结果集解析
            List<T> list  = new ArrayList<>();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int maxCount = metaData.getColumnCount();
            while (resultSet.next()){
//              实例化对象
                T t = null;
                try {
                    t = clazz.newInstance();
                } catch (InstantiationException | IllegalAccessException e) {
                    throw new RuntimeException(e);
                }
                for (int i = 1; i <=maxCount ; i++) {
//                    对象的属性值
                    Object value = resultSet.getObject(i);
//                    所对应的值
                    String propertyName = metaData.getColumnLabel(i);
//                    通过反射给对象赋值
                    Field filed = null;
                    try {
                        filed = clazz.getDeclaredField(propertyName);
                    } catch (NoSuchFieldException e) {
                        throw new RuntimeException(e);
                    }
//                   设置改变 private的权限
                   filed.setAccessible(true);
//                   给对应的属性赋值
//                    参数1 赋值的对象 如果是静态可省略  参数2具体的属性
                    try {
                        filed.set(t,value);
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException(e);
                    }
                }
                list.add(t);
            }
//            关闭资源
            resultSet.close();
            ps.close();
            if (connection.getAutoCommit()) {
                JDBCUtils.closeConnection();
            }
            return list;


    }
}

5.工具类的测试（增删改查）

import org.junit.Test;
import top.remained.bean.Student;
import top.remained.dao.BaseDao;

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

public class TestUtil extends BaseDao {
//   TODO: 增
    @Test
    public void insert() throws SQLException {
        int i = executeUpdate("insert into student values(?,?,?,?)", "lx", "yyds", 1, new Date(System.currentTimeMillis()));
        System.out.println(i);
    }
    //   TODO: 删
    @Test
    public void delete() throws SQLException {
        int i = executeUpdate("delete from student where id=?", "lx");
        System.out.println(i);
    }
    //   TODO: 改
    @Test
    public void update() throws SQLException {
        int i = executeUpdate("update  student  set id=? where id=?",  "zz","lx");
        System.out.println(i);
    }
//    //   TODO: 查
    @Test
    public void query() throws SQLException {
//        字段名和别名一致的话就不要用select *了
        List<Student> students = executeQuery(Student.class, "select id,name,age,birth_date as brithDate from student");
        System.out.println(students);
    }

}

注意：实体类和表需要对应