使用JDBC实现通用查询、增删改查

目录

一、配置文件
二、工具类
三、JavaBean
四、通用增删改
五、通用查询
六、preparedStatement的优势

---

一、配置文件

返回目录

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

注 : password是DBMS(数据库管理系统)中设置的密码

---

二、工具类的编写(实现数据库的开启与关闭功能)

返回目录

package CSDN_jdbc;

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

/**
 * @ClassName 操作数据库的工具类
 * @Author 一熹
 * @Version 1.8
 **/
public class crudUtil {
    /**
     * @description: 获取数据库的连接
     * @param: void
     * @return: java.sql.Connection
     * @author: 一熹
     */
    public static Connection getConnection() throws Exception {
        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");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    /**
     * @description: 关闭数据库的连接
     * @param: conn, ps, rs
     * @return: void
     * @author: 一熹
     */
    public static void closeResource(Connection conn, PreparedStatement ps) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //方法的重载
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

三、JavaBean的编写(以表Customer与Order为例)

返回目录
下图是Customer的JavaBean

package CSDN_jdbc.JavaBean;

import java.sql.Date;

/**
 * @ClassName Customer的JavaBean
 * @Author 一熹
 * @Version 1.8
 **/
public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer() {
    }

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

    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 String getEmail() {
        return email;
    }

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

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

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

下图是Order的JavaBean

package CSDN_jdbc.JavaBean;

import java.sql.Date;

/**
 * @ClassName Order的JavaBean
 * @Author 一熹
 * @Version 1.8
 **/
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 "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", orderDate=" + orderDate +
                '}';
    }
}

---

四、通用增删改操作方法以及测试

返回目录

/**
 * @ClassName 通用增删改操作方法
 * @Author 一熹
 * @Version 1.8
 **/
public class preparedStatementTest {
    public void update(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = crudUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            crudUtil.closeResource(conn, ps);
        }
    }

    /**
     * @description: 对通用增删改方法的测试
     * @param: void
     * @return: void
     * @author: 一熹
     */
    @Test
    public void test1() {
        String sql = "update customers set name = ? where id = ?";//预编译，'?'代表占位符
        update(sql, "周润发", 19);
    }

---

五、通用查询方法以及测试

返回目录

 /**
    * @description: 通用查询操作
    * @param: clazz,sql,args
    * @return: java.util.List<T>
    * @author: 一熹
    */
    public <T> List<T> query(Class<T> clazz, String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<T> list = null;
        try {
            conn = crudUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);//可变形参可看作数组
            }
            rs = ps.executeQuery();
            list = new ArrayList<>();
            while (rs.next()) {//next()作用：判断结果集的下一条是否有数据，则返回true并下移指针，否则返回false指针不动
                ResultSetMetaData rsmd = ps.getMetaData();//获取结果集的元数据
                int columnCount = rsmd.getColumnCount();//获取列数
                T t = clazz.newInstance();//通过反射常见对象
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);//获取列的别名
                    //通过反射给columnLable列赋值为columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            crudUtil.closeResource(conn, ps, rs);
        }
        return null;
    }
    /**
    * @description:对通用查询方法的测试
    * @param: void
    * @return: void
    * @author: 一熹
    */
    @Test
    public void testQuery(){
        //别名用于和JavaBean的属性同名，由于order是关键词故用着重号(`)修饰
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id < ?";
        List<Order> query = query(Order.class, sql, 5);
        query.forEach(System.out::println);
    }
}

---

六、preparedStatement相较于statement的优势

返回目录
1.解决了statement的拼串问题
2.避免了SQL注入问题
3.可以操作Blob数据(占位符中可以传入流)
4.更高效的批量操作(预编译，只需校验一次)