2021-11-7JDBC-PreparedStatementCRUD

一、修改表

把建立连接操作和关闭资源操作都封装到了包中

@Test
    public void testUpdate() throws Exception {
        //1.获取数据库连接
        Connection conn=JDBCUtils.getConnection();
        //2.预编译sql语句，返回PreparedStatement的实例
        String sql="update customers set name= ? where id= ?";//预编译sql语句

        PreparedStatement ps = conn.prepareStatement(sql);


        //3. 填充占位符
        ps.setObject(1,"张三");
        ps.setObject(2,18);


        //4.执行
        ps.execute();
        //5.资源的关闭
        JDBCUtils.closeResource(conn,ps);

    }

通用方法：update() 增删改都行，取决于sql语句

    //通用的增删改操作
    public void update(String sql,Object... args) throws Exception {
        Connection conn=JDBCUtils.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        for(int i=0;i<args.length;i++)
        {
            ps.setObject(i+1,args[i]);
        }

        ps.execute();
        JDBCUtils.closeResource(conn,ps);

    }
    @Test
    public void testCommonUpdate() throws Exception {
        String sql="update customers set name=? where id=?";
        update(sql,"李四",18);

       // String sql="delete from `order` where id=?";
    }

二、查询表

import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CustomerForQuery {
    @Test

    public void testQuery1() throws Exception
    {
        Connection conn= JDBCUtils.getConnection();
        String sql="select id,name,email,birth from customers where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,18);
        ResultSet resultSet = ps.executeQuery();
        //处理结果集
        if(resultSet.next())
        {
            int id=resultSet.getInt(1);
            String name=resultSet.getString(2);
            String email=resultSet.getString(3);
            Date birth=resultSet.getDate(4);

            //方式一
//            System.out.println("id="+id+",name="+name+",email="+email+",birth="+birth);
//
//            //方式二
//            Object[] data=new Object[]{id,name,email,birth};
            //方式三：将数据封装为一个对象
            Customer customer = new Customer(id, name, email, birth);
            System.out.println(customer);

        }
        //关闭资源，注意：ResultSet也要关闭
        JDBCUtils.closeResource(conn,ps,resultSet);


    }
}

通用操作 query

public class CustomerForQuery {
    public Customer queryForCustomers(String sql,Object... args)
    {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet resultSet=null;
        try {
            conn=JDBCUtils.getConnection();

            ps=conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++)
            {
                ps.setObject(i+1,args[i]);
            }

            resultSet = ps.executeQuery();

            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount=metaData.getColumnCount();
            if(resultSet.next())
            {
                Customer cust=new Customer();
                for(int i=0;i<columnCount;i++)
                {
                    //获取列值
                    Object columnValue=resultSet.getObject(i+1);
                    //获取每个列的列名
                    String columnName=metaData.getColumnName(i+1);
                    //给cust对象指定的columnName属性，赋值为columnValue：通过反射
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(cust,columnValue);

                }

                return cust;

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {//finally中的语句一定会执行（即使在return后）
            JDBCUtils.closeResource(conn,ps,resultSet);
        }

        return null;

    }

    @Test
    public void testQueryForCustomers()
    {
        String sql="select id,name,birth,email from customers where id=?";
        Customer customer= queryForCustomers(sql,1);
        System.out.println(customer);
        
        sql="select name,email from customers where name=?";
        Customer customer1=queryForCustomers(sql,"李四");
        System.out.println(customer1);
    }

三、查询通用操作

查询一条记录

import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class PreparedStatementQueryTest {
    public <T> T getInstance(Class<T> cl,String sql,Object...args) throws Exception
    {
        Connection conn= null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for(int i=0;i<args.length;i++)
            {
                ps.setObject(i+1,args[i]);
            }

            resultSet = ps.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount=metaData.getColumnCount();
            while (resultSet.next())
            {
                //创建一个对象，属于cl类
                T t = cl.newInstance();
                for(int i=0;i<columnCount;i++) {
                    Object columnValue = resultSet.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i + 1);

                    Field field = cl.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }

                return t;

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
//            conn.close();
//            ps.close();
//            resultSet.close();
            JDBCUtils.closeResource(conn,ps,resultSet);
        }


        return null;

    }

    @Test

    public void testPreparedStatementQueryTest() throws Exception {
        Class cl=Customer.class;
        String sql="select id ,name from customers where id= ? ";
        Customer customer=(Customer) getInstance(cl,sql,1);

        System.out.println(customer);
    }

}

查询多条记录

建立ArrayList，返回ArrayList

import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

public class PreparedStatementQueryTest {
    public <T> ArrayList<T> getInstance(Class<T> cl,String sql,Object...args) throws Exception
    {
        Connection conn= null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for(int i=0;i<args.length;i++)
            {
                ps.setObject(i+1,args[i]);
            }

            resultSet = ps.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount=metaData.getColumnCount();
            //创建集合对象
            ArrayList<T> list=new ArrayList<>();
            while (resultSet.next())
            {
                //创建一个对象，属于cl类
                T t = cl.newInstance();
                for(int i=0;i<columnCount;i++) {
                    Object columnValue = resultSet.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i + 1);

                    Field field = cl.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);

            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
//            conn.close();
//            ps.close();
//            resultSet.close();
            JDBCUtils.closeResource(conn,ps,resultSet);
        }


        return null;

    }

    @Test

    public void testPreparedStatementQueryTest() throws Exception {
        Class cl=Customer.class;
        String sql="select id ,name,email,birth from customers where id< ? ";
    //    Customer customer=(Customer) getInstance(cl,sql,18);
        ArrayList<Customer> cus=getInstance(cl,sql,18);

        for(Customer c:cus)
        {
            System.out.println(c);
        }
        //list.forEach(System.out::println);
    }

}

PreparedStatement一些优势：

• 解决Statement的拼串、sql注入问题
• 可以操作Blob的数据
• 可以实现更高效的批量操作