JDBC优化查询

优化前

@Override  
public void findAll() {  
    Student student=null;  
    try {  
        conn=myConn.getConnection();  //这是一个工具类用于数据库的链接与关闭操作  
        String sql="select * from student;";  
        ps=conn.prepareStatement(sql);  
        rs=ps.executeQuery();  
        while(rs.next()){  
            student=new Student();  
            student.setName(rs.getString("name"));  
            student.setMath(rs.getInt("math"));  
            student.setEnglish(rs.getInt("english"));  
            System.out.println(student);  
        }  
    } catch (Exception e) {  
        e.printStackTrace();  
    }finally{  
        myConn.close(rs, ps, conn);////这是一个工具类用于数据库的链接与关闭操作  
    }  
      
}  

@Override  
public void findById(int id) {  
    try {  
        conn=myConn.getConnection();  
        String sql="select * from student where id=?;";  
        ps=conn.prepareStatement(sql);  
        ps.setInt(1, id);  
        rs=ps.executeQuery();  
        if(rs.next()){  
            Student student=new Student();  
            student.setName(rs.getString("name"));  
            student.setMath(rs.getInt("math"));  
            student.setEnglish(rs.getInt("english"));  
            System.out.println(student);  
        }  
    } catch (Exception e) {  
        // TODO Auto-generated catch block  
        e.printStackTrace();  
    }finally{  
        myConn.close(rs, ps, conn);  
    }         
      
}  

优化后

public interface ResultSetHandler {  
   public  Object doHandler(ResultSet rs) throws SQLException;  
}  

public class JdbcTem{  
   public Object query(String sql,ResultSetHandler handler,Object...args){  
     try {  
     conn=MyConnection.getConnection();//对数据库的链接操作  
     ps=conn.prepareStatement(sql);  
     if(args!=null){  
         for (int  i = 0;  i < args.length;  i++) {  
            ps.setObject(i+1,args[i]);  
        }  
     }  
      rs=ps.executeQuery();  
    return handler.doHandler(rs);  
} catch (Exception e) {  
    e.printStackTrace();  
    return null;  
}finally{  
    MyConnection.close(rs, ps, conn);//对数据库的关闭操作  
}  
   }  
   }  

上面的操作返回了一个handler的方法连同插叙的结果集一同返回给了调用者所以调用者必须要去实现具体可以用匿名内部类

通过Id查询

@Override  
public void findById(final int id) {  
 String sql="select * from student where id=?";  
Person p= (Person) jdbcTem.query(sql, new ResultSetHandler(){  
  
    @Override  
    public Object doHandler(ResultSet rs) throws SQLException {  
        Person p=null;  
        if(rs.next()){  
          p=new Person();  
          p.setEnglish(rs.getInt("english"));  
          p.setMath(rs.getInt("math"));  
          p.setName(rs.getString("name"));  
        }  
        return p;  
    }  
       
 },id);   
 System.out.println(p);  
}  

查询全部

@Override  
public void findAll() {  
    String sql="select * from student;";  
     @SuppressWarnings("unchecked")  
    List<Person>list=(List<Person>) jdbcTem.query(sql, new ResultSetHandler() {  
        @Override  
        public Object doHandler(ResultSet rs) throws SQLException {  
            List<Person>li=new ArrayList<Person>();  
            Person p=null;  
            while(rs.next()){  
                p=new Person();  
                p.setEnglish(rs.getInt("english"));  
                p.setMath(rs.getInt("math"));  
                p.setName(rs.getString("name"));  
                li.add(p);  
            }  
            return li;  
        }  
    });  
      
    for (Person person : list) {  
        System.out.println(person);  
    }  
}