连接数据库进行增删改查（封装）

一、src下新建文件夹lib，导入三个jar包

二、src下新建文件db.properties

写入
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/数据库名?useSSL=false
user=root
password=123456

三、新建utils文件夹,在文件夹中创建JdbcUtil类

JdbcUtil.java

package com.qfedu.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {
    private static String driverClass = null;
    private static String url = null;
    private static String user = null;
    private static String password = null;

    //使用静态代码块完成驱动的自动加载
    static {
        try {
        //1、创建对象properties
        Properties properties = new Properties();
        //2、使用load 加载src下面的文件
            properties.load(new FileInputStream("./src/db.properties"));
        //3、从properties这个对象中取出来对应的属性
            driverClass = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
        //4.加载驱动
            Class.forName(driverClass);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //getConnection
    public static Connection getConnection() throws SQLException {
        Connection connection = null;
        connection = DriverManager.getConnection(url,user,password);
        return connection;
    }
    //完成统一的close方法
    public static void close(Connection connection) {
        close(connection,null,null);
    }
    public static void close(Connection connection, Statement statement) {
        close(connection,statement,null);
    }
    public static void close(Connection connection,Statement statement,ResultSet resultSet) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

四、新建BaseDo.java

package com.qfedu.test;

import com.qfedu.utils.JdbcUtil;
import org.apache.commons.beanutils.BeanUtils;

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

/**
 * @author xue_yun_xiang
 * @create 2021-03-29-14:33
 */

//基于元数据、BeanUtils、反射思想完成统一的query()和update()
    //update()：增删改
    //query()：查询
public class BaseDo {
    /**
     * 方法分析：
     *      名字；update()
     *      参数列表：
     *          1、String sql：sql语句
     *          2、参数个数、参数类型都不确定
     *             只能放一个数组，可以放不同类型的 Object[] 数组
     *          3、返回值
     *              int 返回受影响的行数
     *       public int update(String sql,Object[] parameters){}
     */
    public int update(String sql,Object[] parameters) throws SQLException {
        if (sql == null) {
            throw new SQLException("SQL is null");
        }

        //1、获取数据库连接
        Connection connection = JdbcUtil.getConnection();

        //2、获取预处理的搬运工
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //3、处理preparestament的参数
        //3.1 获取sql语句参数的个数
        int parameterCount = preparedStatement.getParameterMetaData().getParameterCount();
        //4 赋值
        if (parameters != null && parameters.length == parameterCount) {
            for (int i = 1; i <= parameterCount; i++) {
                preparedStatement.setObject(i,parameters[i - 1]);
            }
        }

        //5 执行sql语句
        int i = preparedStatement.executeUpdate();

        //6、关闭资源
        JdbcUtil.close(connection,preparedStatement);

        //7、 返回
        return i;
    }

    /**
     * 统一的查询方法
     * 1、查询一条数据
     * 2、查询多条数据
     * 3、查询任意类对象的类型的数据（放射）
     *
     * 方法分析：
     *       query()
     *       参数：
     *       1、String sql 要执行的sql语句
     *       2、对应sql语句的参数的数组
     *       3、？？？
     *          Class<T>
     *              1、必须带有泛型
     *              2、执行查询的类对象
     *              3、通过Class类对象
     *                  类名.class
     *       返回值：
     *          数组无法确定长度
     *          List<T> 或者  List<Object>，但需要强转
     *          public <T> List<T> query(String sql,Object[] parameters,Class<T> cls) {}
     */
    public <T> List<T> query(String sql,Object[] parameters,Class<T> cls) throws SQLException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {
        if (sql == null || cls == null) {
            throw new NullPointerException();
        }

        Connection connection = JdbcUtil.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //3.获取sql语句的参数个数
        int parameterCount = preparedStatement.getParameterMetaData().getParameterCount();

        //4.赋值
        if ( parameters.length == parameterCount && parameters != null ) {
            for (int i = 1; i <= parameterCount; i++) {
                preparedStatement.setObject(i,parameters[ i - 1]);
            }
        }

        //5.执行sql语句，返回ResultSet结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        //6.准备一个List集合
        List<T> list = new ArrayList<>();

        //7.获取结果集元数据对象
        ResultSetMetaData metaData = resultSet.getMetaData();

        //7.获取结果集中列的个数（字段）
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {
            //创建对应数据类型的类对象，使用反射
            T t = cls.getConstructor().newInstance();
            for (int i = 1; i <= columnCount; i++) {
                //获取字段名
                String fieldName = metaData.getColumnName(i);
                //获取字段对应的值
                Object object = resultSet.getObject(fieldName);

                //使用BeanUtils把指定字段的数据，在类对象中赋值
                BeanUtils.setProperty(t,fieldName,object);
            }
            //存放在List集合中
            list.add(t);
        }

        JdbcUtil.close(connection,preparedStatement,resultSet);

        //返回
        return list.size() != 0?list:null;
    }
}

五、Student.java

public class Student {

    private int id;
    private String name;
    private int age;
    private String info;

    public Student() {

    }

    public Student(int id, String name, int age, String info) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.info = info;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", info='" + info + '\'' +
                '}';
    }
}

六、Demo1.java

import com.sun.corba.se.spi.orbutil.threadpool.Work;
import org.junit.Test;

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

/**
 * @author xue_yun_xiang
 * @create 2021-03-29-14:28
 */
public class Demo1 extends BaseDo{

    //增
    @Test
    public void testAdd() throws SQLException {
        String sql = "insert into student(name, age, info)values(?,?,?)";
        super.update(sql,new Object[]{"波妞",111,"悬崖上的金鱼"});
    }

    //删
    @Test
    public void testDelete() throws SQLException {
        String sql = "delete from student where id = ?";
        super.update(sql,new Object[]{6});
    }

    //改
    @Test
    public void testUpdate() throws SQLException {
        String sql = "update student set name = ?,age = ?, info = ? where id = ?";
        super.update(sql, new Object[]{"盖伦",666,"大宝剑",5});
    }

    @Test
    public void testSelectOne() throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException, NoSuchMethodException {
        String sql = "select * from student where id = ?";
        List<Student> query = super.query(sql,new Object[]{5}, Student.class);
        if (query != null) {
            System.out.println(query);
        }
    }

    @Test
    public void testSelectAll() throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException, NoSuchMethodException {
        String sql = "select * from student";
        List<Student> query = super.query(sql, new Object[]{}, Student.class);
        if (query != null) {
            for (Student student : query) {
                System.out.println(student);
            }
        }
    }
}