HQL查询语句

转载 2015年11月20日 16:31:52

HQL查询:
***Session session = HibernateSessionFactoryUtil.getSessionFactory() .openSession();
session.beginTransaction();//开始会话并交互
Query query = session .createQuery(“SQL”);//执行SQL返回结果列表
//SQL可以为任意查询语句
List list = query.list();//ClassA为实体类*

public class HibernateTest {
/**
*直接使用模板
* @param args
*/
public static void main(String[] args) {
HibernateTest test = new HibernateTest();
test.where();
test.function();
test.update();
test.jiaoChaCheck();
test.innerJoin();
test.QBC();
test.leftOuterJoin();
test.rightOuterJoin();
}

public void where() {
// 使用where查询
Session session = HibernateSessionFactoryUtil.getSessionFactory() .openSession();
session.beginTransaction();//开始会话
Query query = session .createQuery(“from User where id not between 200 and 2000”);//selsect *返回结果列表
List list = query.list();

for (User user : list) {//for高级
System.out.println(user.getId() + user.getUsername());
}
// 投影查询 中使用where子句
query = session.createQuery(“select username from User where id=2”);
List listname = query.list();

for (String name : listname) {
System.out.println(name);
}
// in查询
query = session
.createQuery(“from User where username in (‘Hongten’,’Hanyuan’,’dfgd’)”);
List listin = query.list();

for (User user : listin) {
System.out.println(user.getId() + user.getUsername());
}
// like查询 模糊查询
query = session.createQuery(“from User where username not like ‘Hon%’”);
List listlike = query.list();

for (User user : listlike) {
System.out.println(user.getId() + user.getUsername());
}
// null查询
query = session.createQuery(“from User where password is null”);
List listnull = query.list();

for (User user : listnull) {
System.out.println(user.getId() + user.getUsername());
}
// and查询
query = session
.createQuery(“from User where password is not null and id<5”);
List listand = query.list();

for (User user : listand) {
System.out.println(user.getId() + user.getUsername()
+ user.getPassword());
}
// order by
query = session.createQuery(“from User order by username,id desc”);
List listorderby = query.list();

for (User user : listorderby) {
System.out.println(user.getId() + user.getUsername());
}
// 使用”?”号 作为参数占位符,一条HQL语句中可以使用多个?
// query.setInteger(0,2)
// query.setString(0,”Hongten”)
query = session .createQuery(“select username from User where username=?”);
query.setString(0, “Hongten”);
List listwenhao = query.list();
for (String name : listwenhao) {
System.out.println(name);
}

session.getTransaction().commit();

}

public void function() {// 把大写字母转化为小写字母
// 作用可以用在:比如在一个用户注册的程序中,大小写不容易区分,但是全部转化为小写后就可以很容易进行比较
Session session = HibernateSessionFactoryUtil.getSessionFactory() .openSession();
session.beginTransaction();
// 输出原始的数据
Query query = session.createQuery(“select username from User”);
List list = query.list();

for (String name : list) {
System.out.println(name);
}
System.out.println(“——————————————-“);
// 输出的数据全部转化为小写
query = session.createQuery(“select lower(username) from User”);
List listChange = query.list();

for (String name : listChange) {
System.out.println(name);
}
session.getTransaction().commit();
}

public void update() {
Session session = HibernateSessionFactoryUtil.getSessionFactory() .openSession();
session.beginTransaction();
Query query = session .createQuery(“update User set username=’洪伟1231’ where id=?”);
query.setInteger(0, 3);
int rowCount = query.executeUpdate();
System.out.println(rowCount);
session.getTransaction().commit();
}

public void operateProfile() {// 对profile这个类执行HQL语句操作
Session session = HibernateSessionFactoryUtil.getSessionFactory() .openSession();
session.beginTransaction();
// 执行查询操作
Query query = session.createQuery(“from Profile”);
List list = query.list();
for (Profile profile : list) {
System.out.println(profile.getId() + profile.getEmail()
+ profile.getAddress() + profile.getMobile()
+ profile.getPostcode());
}
// 执行删除操作
query = session.createQuery(“delete from Profile where id=?”);
query.setInteger(0, 3);
int rowCount = query.executeUpdate();
System.out.println(rowCount);
session.getTransaction().commit();
}

public void jiaoChaCheck() {//交叉查询
//这种方法查询出来的结果是笛卡尔积,对于我们开发中没有多大用处
Session session = HibernateSessionFactoryUtil.getSessionFactory() .openSession();
session.beginTransaction();
Query query=session.createQuery(“from User,Profile”);

List

shouzhang

Hibernate:
select
lower(user0_.username) as col_0_0_
from
users.user user0_
hongten
hanyuan
hongwei
mingliu
shouzhang
Hibernate:
update
users.user
set
username=’Hongwei1231’
where
id=?
1
Hibernate:
select
user0_.id as id0_0_,
profile1_.id as id1_1_,
user0_.username as username0_0_,
user0_.password as password0_0_,
profile1_.user_id as user2_1_1_,
profile1_.email as email1_1_,
profile1_.phone as phone1_1_,
profile1_.mobile as mobile1_1_,
profile1_.address as address1_1_,
profile1_.postcode as postcode1_1_
from
users.user user0_,
users.profile profile1_
ID :1,UserName:hongten,Password:123hongtenzone@foxmail.com45464Guangzhoushi65465
ID :1,UserName:hongten,Password:123hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :1,UserName:hongten,Password:123hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :2,UserName:hanyuan,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465
ID :2,UserName:hanyuan,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :2,UserName:hanyuan,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :3,UserName:Hongwei1231,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465
ID :3,UserName:Hongwei1231,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :3,UserName:Hongwei1231,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :4,UserName:mingliu,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465
ID :4,UserName:mingliu,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :4,UserName:mingliu,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :5,UserName:shouzhang,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465
ID :5,UserName:shouzhang,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :5,UserName:shouzhang,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
Hibernate:
select
profile0_.id as id1_0_,
user1_.id as id0_1_,
profile0_.user_id as user2_1_0_,
profile0_.email as email1_0_,
profile0_.phone as phone1_0_,
profile0_.mobile as mobile1_0_,
profile0_.address as address1_0_,
profile0_.postcode as postcode1_0_,
user1_.username as username0_1_,
user1_.password as password0_1_
from
users.profile profile0_
inner join
users.user user1_
on profile0_.user_id=user1_.id
ID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
ID:3 Username:Hongwei1231 Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
Hibernate:
select
this_.id as id1_1_,
this_.user_id as user2_1_1_,
this_.email as email1_1_,
this_.phone as phone1_1_,
this_.mobile as mobile1_1_,
this_.address as address1_1_,
this_.postcode as postcode1_1_,
user1_.id as id0_0_,
user1_.username as username0_0_,
user1_.password as password0_0_
from
users.profile this_
inner join
users.user user1_
on this_.user_id=user1_.id
ID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
ID:3 Username: Hongwei1231 Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian

#

Hibernate:
select
this_.id as id1_1_,
this_.user_id as user2_1_1_,
this_.email as email1_1_,
this_.phone as phone1_1_,
this_.mobile as mobile1_1_,
this_.address as address1_1_,
this_.postcode as postcode1_1_,
user2_.id as id0_0_,
user2_.username as username0_0_,
user2_.password as password0_0_
from
users.profile this_
left outer join
users.user user2_
on this_.user_id=user2_.id
ID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
ID:3 Username: 洪伟1231 Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
Hibernate:
select
profile0_.id as id1_0_,
user1_.id as id0_1_,
profile0_.user_id as user2_1_0_,
profile0_.email as email1_0_,
profile0_.phone as phone1_0_,
profile0_.mobile as mobile1_0_,
profile0_.address as address1_0_,
profile0_.postcode as postcode1_0_,
user1_.username as username0_1_,
user1_.password as password0_1_
from
users.profile profile0_
left outer join
users.user user1_
on profile0_.user_id=user1_.id
order by
profile0_.user_id
ID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian

ID:3 Username: 洪伟1231 Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian

Hibernate:
select
user0_.id as id0_0_,
profiles1_.id as id1_1_,
user0_.username as username0_0_,
user0_.password as password0_0_,
profiles1_.user_id as user2_1_1_,
profiles1_.email as email1_1_,
profiles1_.phone as phone1_1_,
profiles1_.mobile as mobile1_1_,
profiles1_.address as address1_1_,
profiles1_.postcode as postcode1_1_,
profiles1_.user_id as user2_0__,
profiles1_.id as id0__
from
users.user user0_
left outer join
users.profile profiles1_
on user0_.id=profiles1_.user_id
1hongten[com.b510.example.Profile@14eaec9]
2hanyuan[com.b510.example.Profile@569c60]
3Hongwei1231[com.b510.example.Profile@d67067]
4mingliu[]
5shouzhang[]
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
users.user user0_
right outer join
users.profile profiles1_
on user0_.id=profiles1_.user_id
order by
user0_.id
Hibernate:
select
profiles0_.user_id as user2_1_,
profiles0_.id as id1_,
profiles0_.id as id1_0_,
profiles0_.user_id as user2_1_0_,
profiles0_.email as email1_0_,
profiles0_.phone as phone1_0_,
profiles0_.mobile as mobile1_0_,
profiles0_.address as address1_0_,
profiles0_.postcode as postcode1_0_
from
users.profile profiles0_
where
profiles0_.user_id=?
1hongten[com.b510.example.Profile@10c0f66]
Hibernate:
select
profiles0_.user_id as user2_1_,
profiles0_.id as id1_,
profiles0_.id as id1_0_,
profiles0_.user_id as user2_1_0_,
profiles0_.email as email1_0_,
profiles0_.phone as phone1_0_,
profiles0_.mobile as mobile1_0_,
profiles0_.address as address1_0_,
profiles0_.postcode as postcode1_0_
from
users.profile profiles0_
where
profiles0_.user_id=?
2hanyuan[com.b510.example.Profile@e265d0]
Hibernate:
select
profiles0_.user_id as user2_1_,
profiles0_.id as id1_,
profiles0_.id as id1_0_,
profiles0_.user_id as user2_1_0_,
profiles0_.email as email1_0_,
profiles0_.phone as phone1_0_,
profiles0_.mobile as mobile1_0_,
profiles0_.address as address1_0_,
profiles0_.postcode as postcode1_0_
from
users.profile profiles0_
where
profiles0_.user_id=?
3Hongwei1231[com.b510.example.Profile@8997d1]

HQL 查询方式, 及分页查询:、 命名查询语句、 投影查询、报表查询

1. 持久化类: Department.java public class Department { private Integer id; private String name; ...
  • chuck_kui
  • chuck_kui
  • 2016年06月06日 13:03
  • 4367

hibernate中HQL查询语句

参考https://www.oschina.net/code/snippet_1051545_36896 我的查询示例 参考我的项目示例: // 根据HQl语句查询 @Overri...
  • Peng_Hong_fu
  • Peng_Hong_fu
  • 2016年11月19日 12:37
  • 1269

hibernate 使用HQL语句进行多表联查

先看数据库: [order_list_sub_sendnum_part]表: id olss part_num 1 1 001 2 2 002 3 3 003 4 4 004 5 5 ...
  • qq_24531461
  • qq_24531461
  • 2017年04月07日 13:56
  • 1670

HQL.pdf数据查询语句

  • 2015年09月25日 09:11
  • 346KB
  • 下载

hql查询语句

  • 2013年05月24日 22:23
  • 39KB
  • 下载

hql精点查询语句.doc

  • 2012年07月27日 10:47
  • 47KB
  • 下载

hibernate--HQL查询语句-----分组、排序--多条件排序

1.实体的更新和删除:   在继续讲解HQL其他更为强大的查询功能前,我们先来讲解以下利用HQL进行实体更新和删除的技术。这项技术功能是Hibernate3的新加入的功能,在Hibernate2中...
  • liyanhui1001
  • liyanhui1001
  • 2011年08月25日 15:24
  • 38326

hibernate的hql查询语句总结

hibernate的hql查询语句总结 这篇随笔将会记录hql的常用的查询语句,为日后查看提供便利。 在这里通过定义了三个类,Special、Classroom、Student来做...
  • Mr_HHH
  • Mr_HHH
  • 2016年03月27日 12:09
  • 357

hql查询语句(hibernate)

.    1.from子句 from Person 表明从Person持久化类中选出全部的实例。 推荐:from Personas p   2.select子句 select p.na...
  • renhui999
  • renhui999
  • 2012年07月27日 16:55
  • 658

hibernate--HQL查询语句(2)

3. 参数绑定:   Hibernate中对动态查询参数绑定提供了丰富的支持,那么什么是查询参数动态绑定呢?其实如果我们熟悉传统JDBC编程的话,我们就不难理解查询参数动态绑定,如下代码传统JDBC...
  • ITfishing
  • ITfishing
  • 2012年01月07日 14:36
  • 311
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HQL查询语句
举报原因:
原因补充:

(最多只允许输入30个字)