sql

• 面向过程，用到的关键字都是字段，查询结果对象不明，需要绑定对象以获取数据

  public void testSQLQuery() {
      String sql="select * from t_student";
      Query query=session.createSQLQuery(sql).addEntity(Student.class); //由于返回的是Object类型，所以要绑定一个实体类，不然遍历时数据查不出来
      List studentList=query.list();
      Iterator it=studentList.iterator();
      while(it.hasNext()){
          Student s=(Student)it.next();
          System.out.println(s);
      }        
  }
  
  public void testSQLQuery2() {
      String sql="select * from t_student where stuName like :stuName and stuAge=:stuAge"; //where后面带字段名
      Query query=session.createSQLQuery(sql).addEntity(Student.class);
      query.setString("stuName", "张%");
      query.setInteger("stuAge", 10);
      List studentList=query.list();
      Iterator it=studentList.iterator();
      while(it.hasNext()){
          Student s=(Student)it.next();
          System.out.println(s);
      }        
  }
  

  hql

• 面向对象，关键字是对象属性，返回结果对象确定，不用绑定对象，直接强转

  public void testHQLQuery() {
      String hql="from Student";
      Query query=session.createQuery(hql); //不用绑定，可以直接强转
      List<Student> studentList=(List<Student>)query.list();
      Iterator it=studentList.iterator();
      while(it.hasNext()){
          Student s=(Student)it.next();
          System.out.println(s);
      }        
  }
  
  public void testHQLQuery2() {
      String hql="from Student where name like :stuName and age=:stuAge"; //where后面带属性名
      Query query=session.createQuery(hql);
      query.setString("stuName", "张%");
      query.setInteger("stuAge", 10);
      List<Student> studentList=(List<Student>)query.list();
      Iterator it=studentList.iterator();
      while(it.hasNext()){
          Student s=(Student)it.next();
          System.out.println(s);
      }        
  }
  
  /**
  使用别名
  */
  public void testHQLQuery3() {
      String hql="from Student as s where s.name like :stuName and s.age=:stuAge"; //where后面接对象.属性，like跟=后面接随意命名
      Query query=session.createQuery(hql);
      query.setString("stuName", "张%");
      query.setInteger("stuAge", 10);
      List<Student> studentList=(List<Student>)query.list();
      Iterator it=studentList.iterator();
      while(it.hasNext()){
          Student s=(Student)it.next();
          System.out.println(s);
      }        
  }
  
  /**
  按年龄排序
  */
  @Test
  public void testHQLQuery4() {
      String hql="from Student order by age desc"; //降序
      Query query=session.createQuery(hql);
      List<Student> studentList=(List<Student>)query.list();
      Iterator it=studentList.iterator();
      while(it.hasNext()){
          Student s=(Student)it.next();
          System.out.println(s);
      }        
  }
  
  /**
  分页
  */
  @Test
  public void testHQLQuery5() {
      String hql="from Student";
      Query query=session.createQuery(hql);
      query.setFirstResult(0); //从第1条查起
      query.setMaxResults(2); //查询两条
      List<Student> studentList=(List<Student>)query.list();
      Iterator it=studentList.iterator();
      while(it.hasNext()){
          Student s=(Student)it.next();
          System.out.println(s);
      }        
  }
  
  /**
  查询单个对象
  */
  @Test
  public void testHQLQuery6() {
      String hql="from Student";
      Query query=session.createQuery(hql);
      query.setFirstResult(1);
      query.setMaxResults(1); //就只查一条
      Student student=(Student)query.uniqueResult(); //直接强转成对象而非集合
      System.out.println(student);    
  }
  
  /**
  链式写法
  */
  @Test
  public void testHQLQuery7() {
      String hql="from Student as s where s.name like :stuName and s.age=:stuAge";
      Query query=session.createQuery(hql);
      //就是把查询语句连接成一条
      List<Student> studentList=(List<Student>)query         //因为返回的是Query所以可以接着.
              .setString("stuName", "张%") //返回的还是 Query
              .setInteger("stuAge", 10) //还是Query
              .list();
      Iterator it=studentList.iterator();
      while(it.hasNext()){
          Student s=(Student)it.next();
          System.out.println(s);
      }        
  }