JDBC中对数据的增删改查操作的通用方法

JDBC中对数据的增删改查操作的通用方法

一、增，删，改的通用方法

代码如下（示例）：

package exer;

import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @ClassName Exer
 * @Description TOOD
 * @Author Xiaoqz
 * @Data 2021/5/20 10:47
 * @Version 1.0
 */
public class Exer {

    @Test
    public void test(){
        String sql = "insert into customers(name,email,birth)value(?,?,?)";
        update(sql,"小明","xxxxxxxxxx@qq.com","2000-01-01");
    }

    /*
    *@Description 针对于数据库test中的表的增，删，改的通用操作
    *@Data 12:30 2021/5/20
    *@Param [sql, args]
    *@return void
    */
    public static void update(String sql,Object ...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.读取配置文件，获取数据库的连接
            InputStream is = Exer.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);
            //读取配置文件中的四个基本信息
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");
            //加载驱动
            Class.forName(driverClass);
            //获取连接
            conn = DriverManager.getConnection(url, user, password);

            //2.预编译sql语句，返回PreparedStatement的实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //4.执行操作
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.资源的关闭
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }

            }
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }

            }
        }

    }

}

jdbc.properties文件中的内容

user=root
password=1234
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

二、查询操作的通用方法

创建一个数据库中数据表对应的java类

/**
 * @ClassName Order
 * @Description
 * ORM编程思想（object relational mapping）对象关系映射
 * 一个数据表对应一个java类
 * 表中的一条记录对应java类的一个对象
 * 表中的一个字段对应java类的一个属性
 * @Author Xiaoqz
 * @Data 2021/5/19 16:35
 * @Version 1.0
 */
public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;

    public Order() {
    }

    public Order(int orderId, String orderName, Date orderDate) {
        this.orderId = orderId;
        this.orderName = orderName;
        this.orderDate = orderDate;
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    @Override
    public String toString() {
        return "OrderForQuery{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", orderDate=" + orderDate +
                '}';
    }
}

查询表中的一条或多条记录

代码如下（示例）：

package exer1;


import org.junit.Test;
import preparedstatement.curd.Order;

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

/**
 * @ClassName Exer03
 * @Description TOOD
 * @Author Xiaoqz
 * @Data 2021/5/20 15:13
 * @Version 1.0
 */
public class Exer03 {

    @Test
    public void test(){
        String sql ="select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id < ?";
        List<Order> orders = forQuery(Order.class, sql, 3);
        orders.forEach(System.out::println);
    }
    /*
    *@Description 针对不同表的通用的查询操作
    *@Data 15:54 2021/5/20
    *@Param [clazz, sql, args]
    *@return java.util.List<T>
    */
    public <T> List<T> forQuery(Class<T> clazz, String sql, Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");
            //2.加载驱动
            Class.forName(driverClass);
            //3.获取连接
            conn = DriverManager.getConnection(url,user,password);
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();

            //获取元数据
            ResultSetMetaData metaData = rs.getMetaData();
            //获取列数
            int columnCount = metaData.getColumnCount();
            ArrayList<T> list = new ArrayList<>();
            while (rs.next()) {
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    Object value = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnLable = metaData.getColumnLabel(i + 1);
                    //给cust对象的指定的某个属性赋值为value,通过反射
                    Field field = clazz.getDeclaredField(columnLable);
                    //如果是私有的无法访问，需要setAccessible(true)
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                if(rs != null)
                    rs.close();
            }catch(SQLException E){
                E.printStackTrace();
            }
            try{
                if(ps != null)
                    ps.close();
            }catch(SQLException E){
                E.printStackTrace();
            }
            try{
                if(conn != null)
                    conn.close();
            }catch(SQLException E){
                E.printStackTrace();
            }
        }
        return null;
    }
}

结果

数据库中的表

注：若有什么语法错误，逻辑不清楚，亦或是有什么需要改进的地方，非常欢迎和我交流，希望大家多多指正！