PrepareStatement编写通用增删改，通用查询方法

 

使用PrepareStatement编写通用的增删改方法， 通用的查询方法（返回一条记录 或者返回多条记录）

 

public class TestPrepateStatement {
 //解决Statement的sql注入问题
  @Test
  public void test3(){
   Scanner s = new Scanner(System.in);
   System.out.println("请输入user:");
   String user = s.nextLine();
   System.out.println("请输入password:");
   String password = s.nextLine();
   //select user name,password,balance from user_table where user = '' and password = ''
   //select user name,password,balance from user_table where user = 'a' or '1' = ' and password = ' or '1' = '1'
   String sql = "select user name,password,balance from user_table where user = ? and password = ?";
   
   User u = testSelect(sql, User.class,user,password);
   if(u != null){
    System.out.println("登录成功");
   }else{
    System.out.println("登录失败");
   }
  }
 
 
 
 //解决Statement拼串的问题。
 @Test
 public void testInsert1(){
  Scanner s = new Scanner(System.in);
  System.out.println("id:");
  int id = s.nextInt();
  System.out.println("name:");
  String name = s.next();
  System.out.println("email:");
  String email = s.next();
  System.out.println("birth");
  String birth = s.next();
  String sql = "intsert into customers(id, name, email, birth) values(?,?,?,?)";
  update(sql,id,name,email,birth);
 }
 
 
 @Test
 public void test1(){
  String sql = "select id, name, email,birth from customers where id <?";
  List<Customer> list = query(sql, Customer.class, 10);
  for(Customer c : list){
   System.out.println(c);
  }
 }
 // 使用PreparedStatement实现通用的查询的方法(version 2.0)
 public <T> List<T> query(String sql, Class<T> clazz, Object ... args){
  Connection connection = null;
  PreparedStatement p = null;
  ResultSet rs = null;
  try {
   connection = JDBCUtils.getConnection();
   p = connection.prepareStatement(sql);
   for(int i =0; i<args.length; i++){
     p.setObject(i+1, args[i]);
   }
   rs = p.executeQuery();
   ResultSetMetaData rsmd = rs.getMetaData();
   int count = rsmd.getColumnCount();
   List<T> list = new ArrayList<T>();
   while(rs.next()){
    T t = clazz.newInstance();
    for(int i=0; i<count; i++){
     Object columnValue = rs.getObject(i+1);
     String columnLble = rsmd.getColumnLabel(i+1);
     PropertyUtils.setProperty(t, columnLble , columnValue);
    }
    list.add(t);
   }
   return list;
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{   
   JDBCUtils.close(rs, p, connection);
  }
  
  return null;
 }
 
 
 @Test
 public void test(){
  String sql = "select id,name,email,birth from customers where id = ?";
  Customer cust = testSelect(sql, Customer.class, 20);
  System.out.println(cust);
 }
 
 // 使用PreparedStatement实现通用的查询的方法(version 2.0)
 public <T> T testSelect(String sql, Class<T> clazz, Object ... args){
  Connection connection = null;
  PreparedStatement p = null;
  ResultSet rs = null;
  try {
   connection = JDBCUtils.getConnection();
   p = connection.prepareStatement(sql);
   for(int i=0;i<args.length; i++){
     p.setObject(i+1, args[i]);
   }
   rs = p.executeQuery();
   ResultSetMetaData rsmd = rs.getMetaData();
   int count = rsmd.getColumnCount();
   while(rs.next()){
    T t = clazz.newInstance();
    for(int i=0; i<count; i++){
      Object columnValue = rs.getObject(i + 1);
      String columnLable = rsmd.getColumnLabel(i+1);
      PropertyUtils.setProperty(t, columnLable, columnValue);
     }
    return t;
   }
  }  catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{   
   JDBCUtils.close(rs, p, connection);
  }
  return null;
 }
 
 @Test
 public void tesUpdate(){
  String sql = "delete from customers where id = ?";
  update(sql,24);
 }
 
 // 使用PreparedStatement实现通用的增删改的方法(version 2.0)
 public void update(String sql, Object ... args){
  Connection connection = null;
  PreparedStatement p = null;
  try {
   connection = JDBCUtils.getConnection();
   p = connection.prepareStatement(sql);
   for(int i=0; i<args.length; i++){
    p.setObject(i+1, args[i]);
   }
   p.execute();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   JDBCUtils.close(null, p, connection);   
  }
 }
 
 
 // 向数据表中插入一条数据
 @Test
 public void testInsert(){
  Connection connection = null;
  PreparedStatement  p = null;
  try {
   // 1.获取数据库的连接
   connection = JDBCUtils.getConnection();
   // 2.编写一个带占位符的sql语句。 占位符：？
   String sql = "insert into customers(id, name, email, birth) values(?,?,?,?)";
   // 3.预编译sql语句，生成PreparedStatement的对象
   p  = connection.prepareStatement(sql);
   
   // 4.填充占位符p.setObject(1, 9);
   p.setObject(1, 9);
   p.setObject(2, "张国荣");
   p.setObject(3, "[e-mail]");
   p.setObject(4, "1899-09-09");
   // 5.执行
   p.execute();
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{   
   JDBCUtils.close(null, p, connection);
  }
 }
}