[Java知识库]JDBC增删改查

一：工具类（连接数据库，关闭）

连接数据库

public static Connection getConnection() throws Exception {
        //1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        //2.加载驱动
        Class.forName(driverClass);
        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

关闭资源

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

二：增删改通用方法

public  void update(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.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            JDBCutils.closeResource(connection,ps);
        }
    }

三：查询通用方法

（1）为了显示查询结果，将字段放入一个类中封装好

/**
 * @author Lydia
 * @create 2021-10-15 16:53
 * ORM编程思想（Object relational mapping）
 * 一个数据表对应一个java类
 * 表中的一条记录队形java类的一个对象
 * 表中一个字段对应java类的一个属性
 */
public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;

    public Order() {
        super();
    }

    public Order(int orderId, String orderName, Date orderDate) {
        super();
        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 +
                '}';
    }
}

（2）查询某个特定表的结果

public Order QueryOrder(String sql,Object ...args) throws Exception {
        //1.获取连接
        Connection connection = JDBCutils.getConnection();
        //2.预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        //3.执行并返回结果集
        ResultSet resultSet = ps.executeQuery();
        ResultSetMetaData rsmd = resultSet.getMetaData();//元数据
        int columnCount = rsmd.getColumnCount();//列数
        //4.处理结果集
        if (resultSet.next()){
            Order order = new Order();
            for (int i=0;i<columnCount;i++){
                Object columnValue = resultSet.getObject(i+1);//将columnValue赋值给columnName
//                String columnName = rsmd.getColumnName(i+1);//获取列属性名
                String columnLabel = rsmd.getColumnLabel(i+1);//获取列的别名
                Field field = Order.class.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(order, columnValue);
            }
            return order;
        }
        //关闭流
        JDBCutils.closeResource(connection,ps,resultSet);
        return null;
    }

（3）用泛型代替表名，查询任意表的结果

//将SQL表名用泛型代替
 public <T> T QueryCommon(Class<T> clazz,String sql,Object ...args) throws Exception {
        //1.获取连接
        Connection connection = JDBCutils.getConnection();
        //2.预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        //3.执行并返回结果集
        ResultSet resultSet = ps.executeQuery();
        ResultSetMetaData rsmd = resultSet.getMetaData();//元数据
        int columnCount = rsmd.getColumnCount();//列数
        //4.处理结果集
        if (resultSet.next()){
            T t = clazz.getConstructor().newInstance();
            for (int i=0;i<columnCount;i++){
                Object columnValue = resultSet.getObject(i+1);//将columnValue赋值给columnName
                String columnLabel = rsmd.getColumnLabel(i+1);//获取列的别名
                Field field = clazz.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            return t;
        }
        //关闭流
        JDBCutils.closeResource(connection,ps,resultSet);
        return null;
    }

（4）查询返回多个结果，将多个对象装入集合中

    public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args)  {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //1.获取连接
            connection = JDBCutils.getConnection();
            //2.预编译sql语句
            ps = connection.prepareStatement(sql);
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            //3.执行并返回结果集
            resultSet = ps.executeQuery();
            ResultSetMetaData rsmd = resultSet.getMetaData();//元数据
            int columnCount = rsmd.getColumnCount();//列数
            //4.处理结果集
            ArrayList<T> list = new ArrayList<>();//创建集合对象
            while (resultSet.next()){
                T t = clazz.getConstructor().newInstance();
                for (int i=0;i<columnCount;i++){
                    Object columnValue = resultSet.getObject(i+1);//将columnValue赋值给columnName
                    String columnLabel = rsmd.getColumnLabel(i+1);//获取列的别名
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭流
            JDBCutils.closeResource(connection,ps,resultSet);
        }
        return null;
    }

（5）查询测试

表的字段名和类的属性名不一致时，需要给字段名起别名

String sql="select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id=?";
Order order = QueryOrder(sql, 1);
System.out.println(order);

打印集合的结果forEach(System.out::println)

String sql2="select id,name,email,birth from `customers` where id<?";
        List<Customer> forList = getForList(Customer.class, sql2, 4);
        forList.forEach(System.out::println);

四：preparedStatement与statement比较

preparedStatement替换statement,实现数据的增删改查
*          解决Statement sql注入问题，因为他会预编译sql语句
*          操作Blob的数据，而statement做不到
*          实现更高效的批量插入