JDBC查询
本小节主要介绍在jdbc中对各种查询的应用
一、简介
1、查询使用PreparedStatement中的executeQuery()方法执行,返回的数据时结果集,需要用ResultSet对象接收
2、可以使用getXX()方法把字段值从结果集中取出来
3、对实体类和BaseDao的封装:
public class Student { private int id; private String name; private String sex; private int age; public Student() { } public Student(int id, String name, String sex, int age) { this.id = id; this.name = name; this.sex = sex; this.age = age; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } }
public class BaseDao { protected Connection con; protected PreparedStatement pst; protected ResultSet rs; //打开连接的方法 public void openCon() { try { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.创建连接 con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/0501oop-jdbc", "root", "root"); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); } } //3.写sql //4.创建PreparedStatement对象预处理 //5、执行并返回结果 //6、处理结果 //关闭资源的方法 public void closeAll(){ //7.关闭连接 try { if (rs!=null) { rs.close(); } if (pst!=null) { pst.close(); } if (con!=null) { con.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } } }
二、查询全部数据
public class Methods extends BaseDao{ //查询全部数据的方法 public List<Student> selectAll(){ List<Student> list=null; openCon(); String sql="select * from student"; try { pst=con.prepareStatement(sql); rs=pst.executeQuery(); list=new ArrayList<Student>(); while(rs.next()){ Student stu=new Student(); stu.setId(rs.getInt("id")); stu.setName(rs.getString("name")); stu.setSex((rs.getString("sex"))); stu.setAge(rs.getInt("age")); list.add(stu); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { closeAll(); } return list; } }
public static void main(String[] args) { Methods m=new Methods(); System.out.println("-------查询全部-------"); List<Student> list=m.selectAll(); for (Student stu:list) { System.out.println(stu.getId()+"\t"+stu.getName()+ "\t"+stu.getSex()+"\t"+stu.getAge()); } }
三、通过ID查询
public class Methods extends BaseDao{ //通过id查询数据,返回集合 public List<Student> selectById(int i){ List<Student> list=null; openCon(); String sql="select * from student where id=?"; try { pst=con.prepareStatement(sql); pst.setInt(1,i); rs=pst.executeQuery(); list=new ArrayList<Student>(); while(rs.next()){ Student s=new Student(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); s.setSex(rs.getString("sex")); s.setAge(rs.getInt("age")); list.add(s); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { closeAll(); } return list; } //通过id查询,返回对象 public Student selectById01(int i){ Student s=null; openCon(); String sql="select * from student where id=?"; try { pst=con.prepareStatement(sql); pst.setInt(1,i); rs=pst.executeQuery(); while(rs.next()){ s=new Student(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); s.setSex(rs.getString("sex")); s.setAge(rs.getInt("age")); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { closeAll(); } return s; } }
public static void main(String[] args) { System.out.println("--------通过id查询,返回集合-------"); //Student s=new Student(3,"张三","男",12); //list=m.selectById(s); list=m.selectById(3); for (Student stu:list) { System.out.println(stu.getId()+"\t"+stu.getName()+ "\t"+stu.getSex()+"\t"+stu.getAge()); } System.out.println("-------通过id查询,返回对象------"); Student stu=m.selectById01(3); System.out.println(stu.getId()+"\t"+stu.getName()+ "\t"+stu.getSex()+"\t"+stu.getAge()); }
四、模糊查询
public class Methods extends BaseDao{ //模糊查询,返回集合 //传给模糊值更合理 public List<Student> selectByLike(Student student){ List<Student> list=null; openCon(); String sql="select * from student where name like ? or sex like ?"; try { pst=con.prepareStatement(sql); pst.setString(1,"%"+student.getName()+"%"); pst.setString(2,"%"+student.getSex()+"%"); rs=pst.executeQuery(); list=new ArrayList<Student>(); while(rs.next()){ Student s=new Student(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); s.setSex(rs.getString("sex")); s.setAge(rs.getInt("age")); list.add(s); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { closeAll(); } return list; } }
public static void main(String[] args) { System.out.println("------模糊查询,返回集合------"); Student s=new Student(3,"王","男",12); list=m.selectByLike(s); for (Student student:list) { System.out.println(student.getId()+"\t"+student.getName()+ "\t"+student.getSex()+"\t"+student.getAge()); } }
五、分页查询
public class Methods extends BaseDao{ //分页查询,返回集合 public List<Student> selectByLimit(int a,int b){ List<Student> list=null; openCon(); String sql="select * from student limit ?,?"; try { pst=con.prepareStatement(sql); pst.setInt(1,a); pst.setInt(2,b); rs=pst.executeQuery(); list=new ArrayList<Student>(); while(rs.next()){ Student s=new Student(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); s.setSex(rs.getString("sex")); s.setAge(rs.getInt("age")); list.add(s); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { closeAll(); } return list; } }
public static void main(String[] args) { System.out.println("--------分页查询---------"); list=m.selectByLimit(0,3); for (Student student:list) { System.out.println(student.getId()+"\t"+student.getName()+ "\t"+student.getSex()+"\t"+student.getAge()); } }