通用增删改查方法的封装[了解]

一、通用增删改方法

功能

1. 执行增删改
2. 执行查询

1、封装

	/**
     * 功能：增删改
     * 可以针对任何表里的任何增删改sql语句
     *
     */
    //增删改                    sql语句     不知道占位符值的类型和数量，用Object...
    public static int update(String sql,Object... params){
        try {
            //1.获取连接
            Connection connection = JDBCUtilsByDruid.getConnection();

            //2.执行sql语句
            PreparedStatement statement = connection.prepareStatement(sql);
            //可变参数的长度  params.length
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i+1,params[i]);
            }
            int update = statement.executeUpdate();
            return update;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

2、使用

//修改beauty表中柳岩性别为男
    @Test
    public void testUpdate(){
        int update = CRUDUtils.update("UPDATE beauty SET sex=? WHERE name='柳岩'", "m");
        System.out.println(update>0?"success":"fail");

        int update1 = CRUDUtils.update("DELETE FROM admin WHERE id=?", 8);
        System.out.println(update1>0?"success":"fail");

    }

二、针对一个表的查询方法

针对多个表，可以使用泛型。

orm:object relation mapping 对象关系映射

创建一个和表中字段一一对应的类, get/set方法调用其属性。

此类中包含：

• 和字段对应的私有属性

• 各属性的get/set方法

• 有参构造

• toString方法

public class Boys {
    private int id;
    private String boyName;
    private int userCP;

    @Override
    public String toString() {
        return "Boys{" +
                "id=" + id +
                ", boyName='" + boyName + '\'' +
                ", userCP=" + userCP +
                '}';
    }

    public int getId() {
        return id;
    }

    public String getBoyName() {
        return boyName;
    }

    public int getUserCP() {
        return userCP;
    }

    public void setBoyName(String boyName) {
        this.boyName = boyName;
    }

    public void setUserCP(int userCP) {
        this.userCP = userCP;
    }

    public void setId(int id) {
        this.id = id;
    }
    public Boys(int id,String boyName,int userCP){
        super();
        this.id=id;
        this.boyName=boyName;
        this.userCP=userCP;
    }
}

1、单条查询方法

1.封装

/**
     * 查询单个
     * orm:object relation mapping 对象关系映射
     * 表中每一个字段对应java中一个类的每一个属性
     *
     * @param sql
     * @param params
     * @return
     *
     * 只针对Boys表，查询单条
     * 改装成泛型，可以针对多张表查询
     */
    public static  Boys querySingle(String sql,Object... params) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        try {
            //1.获取连接
            connection = JDBCUtilsByDruid.getConnection();

            //2.执行查询
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i+1,params[i]);
            }
            set = statement.executeQuery();
            //将查询到的结果集返回为对象的形式
            if(set.next()){
                int id = set.getInt("id");
                String boyName = set.getString("boyName");
                int userCP = set.getInt("userCP");
                Boys bo = new Boys(id,boyName,userCP);
                return bo;
            }
            return null;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally{
            JDBCUtilsByDruid.close(set,statement,connection);
        }
    }

2.使用

@Test//查询一个表中单条记录
    public void testQuery() throws Exception {
        Boys boy = CRUDUtils.querySingle("SELECT * FROM boys WHERE id=?",3);
        System.out.println(boy);
    }

2、多条查询方法

1.封装

/**
     * 查询多个
     * orm:object relation mapping 对象关系映射
     * 表中每一个字段对应java中一个类的每一个属性
     *
     * @param sql
     * @param params
     * @return
     *
     * 只针对Boys表，查询多条
     */
    public static List<Boys> queryMultiple(String sql, Object... params) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        try {
            //1.获取连接
            connection = JDBCUtilsByDruid.getConnection();

            //2.执行查询
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i+1,params[i]);
            }
            set = statement.executeQuery();

            List<Boys> list = new ArrayList<>();
            //将查询到的结果集返回为对象的形式
            while(set.next()){
                int id = set.getInt("id");
                String boyName = set.getString("boyName");
                int userCP = set.getInt("userCP");
                Boys bo = new Boys(id,boyName,userCP);
                list.add(bo);
            }
            return list;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally{
            JDBCUtilsByDruid.close(set,statement,connection);
        }
    }

2.使用

 @Test//查询一个表中多条记录
    public void testQueryMultiple() throws Exception {
        List<Boys> list = CRUDUtils.queryMultiple("SELECT * FROM boys");
        for (Boys boys:list) {
            System.out.println(boys);
        }
    }