Java-JDBC- 操作Mysql数据库（Druid连接池）

最新推荐文章于 2024-01-04 09:20:59 发布

This_is_code

最新推荐文章于 2024-01-04 09:20:59 发布

阅读量791

点赞数

文章标签： java 数据库 mysql

本文链接：https://blog.csdn.net/This_is_/article/details/129643366

版权

基础部分

导入druid的jar包https://mvnrepository.com/artifact/com.alibaba/druid/1.1.12https://mvnrepository.com/artifact/com.alibaba/druid/1.1.12

使用数据库连接池Druid这里介绍两种连接方法

硬链接：

    //硬编码
    public void testHard() throws Exception {
        //连接池对象
        DruidDataSource druidDataSource = new DruidDataSource();
        //设置参数（必须的:setUrl,setUsername,setPassword｜非必需的:setInitialSize...）
        druidDataSource.setUrl("jdbc:mysql://localhost:3306/xxxxx");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("xxxx");
        //获取连接
        DruidPooledConnection connection = druidDataSource.getConnection();
        //CRUD

        //回收连接
        connection.close();
    }

软连接：

首先准备一个druid.properties配置文件写入数据库的基本配置

#druid配置文件
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/xxxxxx
username=root
password=xxxx

public void testSoft() throws Exception {
        Properties properties = new Properties();
        InputStream rs = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(rs);

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        //CRUD

        connection.close();

    }

实战部分

1.配置文件src/druid.properties

#druid配置文件
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/xxxxxx
username=root
password=xxxx

2.工具类

package druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

//这个工具类的作用就是用来给所有的SQL操作提供连接，和释放连接。
public class JDBCUtilsV2 {

    private static DataSource dataSource=null;//连接池对象
    private static ThreadLocal<Connection> tl=new ThreadLocal<>();

    static {
        Properties properties = new Properties();
        InputStream rs = JDBCUtilsV2.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(rs);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        Connection connection= tl.get();
        if (connection==null){
            connection=dataSource.getConnection();
            tl.set(connection);
        }
        return connection;
    }

    //释放连接
    public static void freeConnection() throws SQLException {
        Connection connection = tl.get();
        if (connection!=null){
            tl.remove();//清空线程本地变量
            connection.setAutoCommit(true);//回归事务状态（默认）
            connection.close();
        }

    }
}

3.数据库如下（id为主键且自增）

4.User类如下

@Data
public class User {
    private int id;
    private String userId;
    private String username;
    private String gender;
    private int age;
    private String password;
}

5. DAO接口及其实现类，对所有表的操作（增、删、改、查）代码重复度很高，抽取公共代码，给这些DAO的实现类可以抽取一个公共的父类，我们称为BaseDao

package druid;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;


public class BaseDao {

    /**
     * @param sql    传入带占位符sql语句
     * @param params 传入占位符的参数(Object...按列表使用)
     * @return 返回影响行数
     */
    public int executeUpdate(String sql, Object... params) throws Exception {
        //获取连接
        Connection connection = JDBCUtilsV2.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= params.length; i++) {
            preparedStatement.setObject(i, params[i - 1]);
        }
        int rows = preparedStatement.executeUpdate();
        preparedStatement.close();
        //回收连接考虑事务
        if (connection.getAutoCommit()) {
            //没有开启事务
            JDBCUtilsV2.freeConnection();
        }
        return rows;
    }

    /**
     * 通用的查询多个Javabean对象的方法，例如：多个User对象
     * 这里的tClass接收的是T类型的Class对象
     * @param tClass 如果查询员工信息，tClass代表Employee.class
     * @param sql String类型的查询语句
     * @param params 占位符参数
     * @return
     * @param <T> 
     * @throws Exception
     */
    public <T> List<T> executeQuery(Class<T> tClass,String sql,Object... params)throws Exception{
        //获取链接
        Connection connection = JDBCUtilsV2.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        if (params!=null&&params.length!=0){
            //占位符是从1-n，params是从0-n-1
            for (int i = 0; i < params.length; i++) {
             preparedStatement.setObject(i+1,params[i]);
            }
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();//装当前结果集列的信息
        int columnCount = metaData.getColumnCount();//列的数量
        List<T> list = new ArrayList<>();//一行数据对应一个T类型的对象
        while (resultSet.next()) {
            T t=tClass.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                Object value = resultSet.getObject(i);//获取第i列的值（resultSet中的行列都从1开始数）
                String propertyName = metaData.getColumnLabel(i);//select userId as ID,username as Name,gender,age,password from users;中可获取别名ID、Name
                Field field = tClass.getDeclaredField(propertyName);
                field.setAccessible(true);//打破private修饰
                field.set(t,value);
            }
            list.add(t);
        }
        resultSet.close();
        preparedStatement.close();
        if(connection.getAutoCommit()){
            JDBCUtilsV2.freeConnection();
        }
        return list;
    }
}

7.举个例子

//在users中插入一条数据，结果如图8.1所示
    @Test
    public void testInsert() throws Exception {
        UUID uuid = UUID.randomUUID();
        String sql = "insert into users(userId,username,gender,age,password) values(?,?,?,?,?);";
        int rows = executeUpdate(sql, uuid.toString().replace("-", ""), "zhangsan", "male", 25, "123456");
        return rows;
    }

//在users中查询所有数据，结果如图8.2所示
    @Test
    public void findAll() throws Exception {
        String sql = "select * from users;";
        List<User> list = executeQuery(User.class, sql);
        System.out.println(list.toString());
    }

8.结果