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);
    }        
}