【后端开发】PreparedStatement实现表的增删改查

StatementTest.java

package com.cls1277.preparedstatement;

import com.cls1277.preparedstatement.customer.Customer;
import com.cls1277.utils.JDBCutils;
import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

public class StatementTest {
    @Test
    public void testInsert()  {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            InputStream resourceAsStream = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(resourceAsStream);
            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);
//        System.out.println(conn);

            //  预编译SQL语句，返回preparedstatement的实例
            String sql = "insert into customers(name, email, birth)values(?,?,?)";
            ps = conn.prepareStatement(sql);
            // 填充占位符：index从1开始的
            ps.setString(1, "曹老师");
            ps.setString(2, "cls1277@163.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("2002-06-10");
            ps.setDate(3, new java.sql.Date(date.getTime()));
            // 执行操作
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 资源关闭
            try {
                if(ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void testUpdate(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCutils.getConnection();
            String sql = "update customers set name = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "cls1277");
            ps.setInt(2, 20);
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, ps);
        }
    }

    // 通用增删改
    public void update(String sql, Object ... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCutils.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 {
            JDBCutils.closeResource(conn, ps);
        }
    }

    @Test
    public void testCommon() {
        String sql = "delete from customers where id = ?";
        update(sql, 19);
    }

    @Test
    public void testQuery() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCutils.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1);
            rs = ps.executeQuery();
            //实现功能为：判断是否有下一项返回bool型，如果为true则下移
            if(rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String email = rs.getString(3);
                java.sql.Date birth = rs.getDate(4);
                //1.sout直接输出
                //2.
                Object[] data = new Object[]{id, name, email, birth};
                //3.
                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, ps, rs);
        }
    }

    //通用查，但是得是Customer类型的
    public Customer query(String sql, Object ... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCutils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0; i<args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if(rs.next()) {
                Customer customer = new Customer();
                for(int i=0; i<columnCount; i++) {
                    Object columnValue = rs.getObject(i+1);
                    //获取列的别名，所以columnName不建议使用：如果没有起别名，那getColumnName=getColumnLabel
//                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    String columnName = rsmd.getColumnName(i + 1);
                    //反射
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(customer, columnValue);
                }
                return customer;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, ps, rs);
        }
        return null;
    }

    @Test
    public void testCommonQuery() {
        String sql = "select id,name,birth,email from customers where id = ?";
        Customer customer = query(sql, 13);
        System.out.println(customer);
    }
}

Customer.java

package com.cls1277.preparedstatement.customer;

import java.sql.Date;

public class Customer {
    //属性名跟表中的名对应上
    //或者sql语句起别名，与属性对应
    private int id;
    private String name, email;
    java.sql.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 String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }

    public Date getBirth() {
        return birth;
    }

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

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

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

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

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

JDBCutils.java

package com.cls1277.utils;

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

public class JDBCutils {
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(resourceAsStream);
        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 = null;
        conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    public static void closeResource(Connection conn, PreparedStatement ps) {
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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