preparedstatement 与编译

当批量查询及修改数据是，需要通过与编译方式对数据库进行操作，即通过perparedstatement对象进行操作。
当与编译是SQL语句编写方式如下：

select id,name,age from tablename whereid =?;
通过站位符代替具体内容测试代码如下：

//查询函数
    public void PreSelect(userInfo user) throws SQLException{
        
        DBUtil data = new DBUtil();
        Connection DBconn = (Connection) data.getConnection();
        System.out.println(DBconn);
        String SQL = "select id, username, email from users where id >?";
        try{
            PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(SQL);
            prep.setInt(1,user.getId());
            System.out.println(prep);
            ResultSet res = prep.executeQuery();
            while(res.next()){
                int id = res.getInt(1);
                String username = res.getString(2);
                System.out.println("查询内容为：  "+id+" "+username);
                
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        DBconn.close();
    
    }

通过定义一个传输类可自定义传输参数。
使用相同方法添加一条数据如下：

public void PreAdd() throws SQLException{
        
        try{
        DBUtil data = new DBUtil();
        Connection DBconn = (Connection) data.getConnection();
        //添加语句INSERT INTO `test`.`users` (`id`, `username`, `email`) VALUES ('10', 'add', 'add@test');
        String SQLadd = "INSERT INTO `test`.`users` (`id`, `username`, `email`) VALUES (?, ?, ?)";
        PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(SQLadd);
        prep.setInt(1,11);
        prep.setString(2, "addr");
        prep.setString(3, "addr@test");
        
        prep.executeLargeUpdate();
        
        System.out.println(prep);
        }catch (SQLException e){
            e.printStackTrace();
        }
        
    }

    public void PreAdd(userInfo user) throws SQLException{
        
        try{
        DBUtil data = new DBUtil();
        Connection DBconn = (Connection) data.getConnection();
        //添加语句INSERT INTO `test`.`users` (`id`, `username`, `email`) VALUES ('10', 'add', 'add@test');
        String SQLadd = "INSERT INTO `test`.`users` (`id`, `username`, `email`) VALUES (?, ?, ?)";
        PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(SQLadd);
        prep.setInt(1,user.getId());
        prep.setString(2, user.getUsername());
        prep.setString(3, user.getEmail());
        
        prep.executeLargeUpdate();
        
        System.out.println(prep);
        }catch (SQLException e){
            e.printStackTrace();
        }
        
    }

数据删除方式如下

//删除函数
    public void PreDel(userInfo user) throws SQLException{
        try{
        DBUtil data = new DBUtil();
        Connection DBconn = (Connection) data.getConnection();
        //删除语句DELETE FROM `test`.`users` WHERE `id`='5';

        String SQL = "DELETE FROM `test`.`users` WHERE `id`=?";
        PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(SQL);
        prep.setInt(1, user.getId());
        prep.executeUpdate();
        }catch ( SQLException e){
            e.printStackTrace();
        }
    }       
'''
修改函数如下：

//修改函数
public void PreMod(userInfo user) throws SQLException{
try{
DBUtil data = new DBUtil();
Connection DBconn = (Connection) data.getConnection();
System.out.println("premod"+DBconn);
//修改语句UPDATE test.users SET username='9', email='9' WHERE id='6';
String sql = "UPDATE test.users SET username=?, email=? WHERE id=?";
PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(sql);
prep.setString(1, user.getUsername());
prep.setString(2, user.getEmail());
prep.setInt(3, user.getId());
System.out.println("修改语句"+prep);
prep.executeUpdate();
System.out.println(prep);
}catch (SQLException e){
e.printStackTrace();
}

}

整体测试函数如下：

//运行测试函数
@Test
public void runtest() throws SQLException{

    userInfo use = new userInfo();
    use.setId(5);
    use.setUsername("sheng");
    use.setEmail("sheng@124.com");
    //添加函数测试
    //PreAdd(use);
    //删除测试函数
    //PreDel(use);
    //查询函数测试
    PreMod(use);
    PreSelect(use);

//

}