写一个通用的对一个表的增删查改的操作

写一个通用的对一个表的增删查改的操作

1、在数据库中创建一张customer表

2、在idea中创建一个文件，存储连接数据库的参数

3、在idea中写一个实体类，类名就叫做Customer
其中的属性和mysql的customer表的字段名一样

实体类代码：

package myself_test.entity;

import java.util.Date;

public class Customer {
    private Integer id;
    private String name;
    private Date date;
    private String email;

    public Customer() {
    }

    public Customer(Integer id, String name, Date date, String email) {
        this.id = id;
        this.name = name;
        this.date = date;
        this.email = email;
    }

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

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", date=" + date +
                ", email='" + email + '\'' +
                '}';
    }
}

4、写一个JDBCUtils类，提供静态的方法来获取连接和关闭资源。



JDBCUtils类代码：

package myself_test.utils;

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

public class JDBCUtils {
    
    public static Connection getConnection() {
        Connection connection = null;
        try {
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc");
            Properties properties=new Properties();
            properties.load(is);
            String driverClass = properties.getProperty("driverClass");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeResource(Connection connection, Statement statement){
        try {
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(statement!=null){
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    public static void closeResource(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(statement!=null){
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(resultSet!=null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

5、Common类的方法一：修改操作

Common类的方法二：通用的查询操作（难点，整个jdbc难就男在这里，使用反射动态实现通用查询操作）

package myself_test.commonControl;

import myself_test.entity.Customer;
import myself_test.utils.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;

public class Common {

    //这个方法用于对数据库的customer表的增删改操作（无返回值，只是对数据库进行修改）
    public static void updateCustomer(String sql,Object... args){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = JDBCUtils.getConnection();
            ps = connection.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,ps);
        }
    }

    //这个方法用于对数据库customer表进行查询操作(难点，但是目前该方法仅适用于处理结果集是一行多列的情况)
    public static Customer queryCustomer(String sql,Object... args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            connection = JDBCUtils.getConnection();
            ps = connection.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            Customer customer=new Customer();
            if(rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    Class<Customer> customerClass = Customer.class;
                    String columnName = rsmd.getColumnName(i + 1);
                    Object objectValue = rs.getObject(i + 1);
                    Field declaredField = customerClass.getDeclaredField(columnName);
                    declaredField.setAccessible(true);
                    declaredField.set(customer,objectValue);
                }
            }
            return customer;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,ps,rs);
        }

    return null;

    }
}

6、测试类代码

package myself_test.utils_test;

import myself_test.commonControl.Common;
import myself_test.entity.Customer;
import org.junit.Test;

public class JDBCTest {

    //用于测试对cusetomer表的update操作（增删改）
    @Test
    public void testInsert(){
        String sql="insert into customer(name,birth,email) values(?,?,?)";
        Common.updateCustomer(sql,"张飞","2020-01-01","1570665656@qq.com");
    }

    @Test
    public void testDelete(){
        String sql="delete from customer where id=?";
        Common.updateCustomer(sql,1);
    }

    @Test
    public void testUpdate(){
        String sql="update customer set name=? where id=?";
        Common.updateCustomer(sql,"宋茜",1);
    }


    // //用于测试对cusetomer表的query操作（查询）
    @Test
    public void testQuery(){
        String sql="select id,name,birth,email from customer where id=?";
        Customer customer = Common.queryCustomer(sql, 1);
        System.out.println(customer);
    }

}