XhstormR


On a dark desert highway Cool wind in my hair


Hibernate Query Language

Updated on 2017-05-19

面向对象的查询语言

select… + from… + where… + group by… + having… + order by…

http://docs.huihoo.com/hibernate/reference-v3_zh-cn/queryhql.html

检索对象:from

sessionFactory.currentSession.use {
    it.beginTransaction()

    val query = it.createQuery("from Customer", Customer::class.java)
    query.list().forEach { println(it.name) }

    val query = it.createQuery("from Commodity c,Seller s", Array<Any>::class.java)     设置别名
    query.list().forEach { println(Arrays.toString(it)) }     笛卡尓积

    it.transaction.commit()
}

list()   :无视缓存并加载整个数据,发送  1  条 SQL 语句。
iterate():检索缓存并延迟加载数据,发送 1+N 条 SQL 语句。

选择属性:select

单个属性:

原生对象
----
val query = it.createQuery("select birthday from Customer", Date::class.java)
query.list().forEach { println(it) }

数据去重
----
val query = it.createQuery("select distinct sex from Customer", String::class.java)
query.list().forEach { println(it) }

多个属性:

默认为 Array
----
val query = it.createQuery("select name,address,tel from Seller", Array<Any>::class.java)
query.list().forEach { println(Arrays.toString(it)) }

指定为 List
----
val query = it.createQuery("select new list(name,address,tel) from Seller", List::class.java)
query.list().forEach { println(it) }

指定为 Map
----
val query = it.createQuery("select new map(name,address,tel) from Seller", Map::class.java)
query.list().forEach { println("${it["0"]}  ${it["1"]}  ${it["2"]}") }
----
val query = it.createQuery("select new map(name as n,address as a,tel as t) from Seller", Map::class.java)     设置别名
query.list().forEach { println("${it["n"]}  ${it["a"]}  ${it["t"]}") }

指定为自定义对象 Test
----
val query = it.createQuery("select new Test(name,address,tel) from Seller", Test::class.java)
query.list().forEach { println(it) }

Test

data class Test(var s1: String, var s2: String, var s3: String)

限制范围:where

比较运算:

=, !=, <, >, <=, >=
----
val query = it.createQuery("from Commodity c where c.price>400", Commodity::class.java)
query.list().forEach { println(it.name) }

四则运算:

+, -, *, /
----
val query = it.createQuery("from Commodity c where c.price*5<2000", Commodity::class.java)
query.list().forEach { println(it.price) }

范围运算:

[not] between v1 and v2
----
val query = it.createQuery("from Customer c where c.age between 20 and 35", Customer::class.java)
query.list().forEach { println(it.age) }

逻辑运算:

and, or, not
且, 或, 非
----
val query = it.createQuery("from Commodity c where (c.price between 100 and 5000) and (c.category not like '%童装%')", Commodity::class.java)
query.list().forEach { println(it.name) }

集合运算:

元素是否属于集合(参数为集合,value in ?):
[not] in (v1, v2, v3)
----
val query = it.createQuery("from Customer c where c.age in (20,35)", Customer::class.java)     底层使用 Statement 接口
query.list().forEach { println(it.age) }
----
val query = it.createQuery("from Customer c where c.age in :values", Customer::class.java)     底层使用 PreparedStatement 接口
query.setParameter("values", arrayListOf(20, 35))     设置参数
query.list().forEach { println(it.age) }

元素是否属于集合(参数为元素,? in list):
[not] member of -> [not] in
----
val orderForm = it.get(OrderForm::class.java, 1)
val query = it.createQuery("from Customer c where :values member of c.orderforms", Customer::class.java)
query.setParameter("values", orderForm)     设置参数
query.list().forEach { println(it.name) }

集合是否为空集:
is empty -> not exists
is not empty -> exists
----
val query = it.createQuery("select o.orderitems.size from OrderForm o where o.orderitems is not empty", Integer::class.java)
query.list().forEach { println(it) }

空值判断:

is [not] null
----
val query = it.createQuery("from Commodity c where c.description is null", Commodity::class.java)
query.list().forEach { println(it.name) }

字符串匹配:

精确匹配:like '张爱玲'
模糊匹配:like '张%'
通配符:
%:任意字符
_:任意一个字符
----
val query = it.createQuery("from Customer c where c.address like '%北京%'", Customer::class.java)
query.list().forEach { println(it.address) }

uniqueResult:查询结果最多只能有一个,否则抛出异常。
----
val query = it.createQuery("from Customer c where c.age>30", Customer::class.java)
val customer = query.uniqueResultOptional().orElse(Customer(name = "不存在"))
println(customer.name)

排序结果:order by

  • 升序排序(由低到高):asc
  • 降序排序(由高到低):desc
val query = it.createQuery("from Commodity c order by c.seller.id asc,c.price desc,c.name asc", Commodity::class.java)     多排序规则
query.list().forEach { println("${it.seller?.id}  ${it.price}  ${it.name}") }

----
输出:
1  200  女士套装
1  200  男士西服
1  120  中式童装
2  4000  笔记本电脑
2  400  移动硬盘
3  5000  液晶电视
3  4000  滚筒洗衣机
4  50  《Java核心》
4  40  《海底两万里》
4  30  《hibernate编程》

Code

Seller(商家)

import javax.persistence.*

@Entity
@NamedQuery(name = "allSeller", query = "from Seller")
data class Seller(
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        val id: Int? = null,
        var name: String? = null,
        var tel: String? = null,
        var address: String? = null,
        var website: String? = null,
        var star: Int? = null,
        var business: String? = null,
        @OneToMany(mappedBy = "seller", cascade = arrayOf(CascadeType.ALL), orphanRemoval = true)
        val commoditys: MutableList<Commodity> = arrayListOf()
) {
    fun addCommodity(commodity: Commodity) {
        commoditys.add(commodity)
        commodity.seller = this
    }

    fun removeCommodity(commodity: Commodity) {
        commoditys.remove(commodity)
        commodity.seller = null
    }
}

Commodity(商品)

import javax.persistence.*

@Entity
data class Commodity(
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        val id: Int? = null,
        var name: String? = null,
        var price: Int? = null,
        var unit: String? = null,
        var category: String? = null,
        var description: String? = null,
        @ManyToOne
        var seller: Seller? = null,
        @OneToMany(mappedBy = "commodity", cascade = arrayOf(CascadeType.ALL), orphanRemoval = true)
        val orderitems: MutableList<OrderItem> = arrayListOf()
) {
    fun addOrderItem(orderItem: OrderItem) {
        orderitems.add(orderItem)
        orderItem.commodity = this
    }

    fun removeOrderItem(orderItem: OrderItem) {
        orderitems.remove(orderItem)
        orderItem.commodity = null
    }
}

Customer(客户)

import java.util.*
import javax.persistence.*

@Entity
data class Customer(
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        val id: Int? = null,
        var name: String? = null,
        var tel: String? = null,
        var address: String? = null,
        var email: String? = null,
        var sex: String? = null,
        var description: String? = null,
        var age: Int? = null,
        var birthday: Date? = null,
        @OneToMany(mappedBy = "customer", cascade = arrayOf(CascadeType.ALL), orphanRemoval = true)
        val orderforms: MutableList<OrderForm> = arrayListOf()
) {
    fun addOrderForm(orderForm: OrderForm) {
        orderforms.add(orderForm)
        orderForm.customer = this
    }

    fun removeOrderForm(orderForm: OrderForm) {
        orderforms.remove(orderForm)
        orderForm.customer = null
    }
}

OrderForm(订单)

import java.util.*
import javax.persistence.*

@Entity
data class OrderForm(
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        val id: Int? = null,
        var tradedate: Date? = null,
        var status: String? = null,
        var amount: Int? = null,
        @ManyToOne
        var customer: Customer? = null,
        @OneToMany(mappedBy = "orderForm", cascade = arrayOf(CascadeType.ALL), orphanRemoval = true)
        val orderitems: MutableList<OrderItem> = arrayListOf()
) {
    fun addOrderItem(orderItem: OrderItem) {
        orderitems.add(orderItem)
        orderItem.orderForm = this
    }

    fun removeOrderItem(orderItem: OrderItem) {
        orderitems.remove(orderItem)
        orderItem.orderForm = null
    }
}

OrderItem(订单明细)

import javax.persistence.*

@Entity
data class OrderItem(
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        val id: Int? = null,
        var amount: Int? = null,
        var actprice: Int? = null,
        var discount: Int? = null,
        @ManyToOne
        var orderForm: OrderForm? = null,
        @ManyToOne
        var commodity: Commodity? = null
)

A

import org.hibernate.SessionFactory
import org.hibernate.boot.MetadataSources
import org.hibernate.boot.registry.StandardServiceRegistryBuilder
import java.util.*

val sessionFactory: SessionFactory by lazy {
    val registry = StandardServiceRegistryBuilder().configure().build()
    MetadataSources(registry).buildMetadata().buildSessionFactory()
}

fun main(args: Array<String>) {
    val seller1 = Seller(name = "A服装店", tel = "13000000000", address = "中国重庆市××区", website = "www.a.com", star = 5, business = "经营各种服装")
    val seller2 = Seller(name = "B数码店", tel = "15800000000", address = "中国杭州市××区", website = "www.b.com", star = 4, business = "经营各种数码产品")
    val seller3 = Seller(name = "C电器店", tel = "16800000000", address = "中国深圳市××区", website = "www.c.com", star = 4, business = "经营各种家电")
    val seller4 = Seller(name = "D书籍店", tel = "17600000000", address = "中国西安市××区", website = "www.d.com", star = 5, business = "经营各种书籍")

    val customer1 = Customer(name = "张三", tel = "13800000000", address = "中国上海××区××路", email = "13800000000@138.com", sex = "男", age = 25, birthday = Date(), description = "热爱编程的程序员")
    val customer2 = Customer(name = "李四", tel = "13888888888", address = "中国北京××区××路", email = "13888888888@138.com", sex = "女", age = 20, birthday = Date(), description = "酷爱网购的白领")
    val customer3 = Customer(name = "王五", tel = "15888888888", address = "中国深圳××区××路", email = "15888888888@158.com", sex = "男", age = 35, birthday = Date(), description = "这个家伙很懒,什么也没有留下")

    val commodity1 = Commodity(name = "中式童装", price = 120, unit = "套", category = "童装", description = "中式童装")
    val commodity2 = Commodity(name = "女士套装", price = 200, unit = "套", category = "女装", description = "女士职业套装")
    val commodity3 = Commodity(name = "男士西服", price = 200, unit = "套", category = "男装", description = "男士西服套装")
    val commodity4 = Commodity(name = "笔记本电脑", price = 4000, unit = "台", category = "电脑", description = "双核笔记本电脑")
    val commodity5 = Commodity(name = "移动硬盘", price = 400, unit = "块", category = "电脑周边", description = "1T移动硬盘")
    val commodity6 = Commodity(name = "液晶电视", price = 5000, unit = "台", category = "电视", description = "4k液晶电视")
    val commodity7 = Commodity(name = "滚筒洗衣机", price = 4000, unit = "台", category = "洗衣机", description = "滚筒洗衣机")
    val commodity8 = Commodity(name = "《hibernate编程》", price = 30, unit = "", category = "实体书", description = "介绍hibernate编程")
    val commodity9 = Commodity(name = "《Java核心》", price = 50, unit = "", category = "实体书", description = "介绍Java编程核心")
    val commodity10 = Commodity(name = "《海底两万里》", price = 40, unit = "", category = "电子书", description = "经典科幻小说")

    val orderForm1 = OrderForm(status = "已收货", amount = 4400, tradedate = Date())
    val orderForm2 = OrderForm(status = "已发货", amount = 520, tradedate = Date())
    val orderForm3 = OrderForm(status = "已付款", amount = 9120, tradedate = Date())

    val orderItem1 = OrderItem(discount = 1, amount = 1, actprice = 4000)
    val orderItem2 = OrderItem(discount = 1, amount = 1, actprice = 400)
    val orderItem3 = OrderItem(discount = 1, amount = 1, actprice = 120)
    val orderItem4 = OrderItem(discount = 1, amount = 1, actprice = 200)
    val orderItem5 = OrderItem(discount = 1, amount = 1, actprice = 200)
    val orderItem6 = OrderItem(discount = 1, amount = 1, actprice = 5000)
    val orderItem7 = OrderItem(discount = 1, amount = 1, actprice = 4000)
    val orderItem8 = OrderItem(discount = 1, amount = 1, actprice = 30)
    val orderItem9 = OrderItem(discount = 1, amount = 1, actprice = 50)
    val orderItem10 = OrderItem(discount = 1, amount = 1, actprice = 40)

    seller1.let {
        it.addCommodity(commodity1)
        it.addCommodity(commodity2)
        it.addCommodity(commodity3)
    }
    seller2.let {
        it.addCommodity(commodity4)
        it.addCommodity(commodity5)
    }
    seller3.let {
        it.addCommodity(commodity6)
        it.addCommodity(commodity7)
    }
    seller4.let {
        it.addCommodity(commodity8)
        it.addCommodity(commodity9)
        it.addCommodity(commodity10)
    }

    customer1.addOrderForm(orderForm1)
    customer2.addOrderForm(orderForm2)
    customer3.addOrderForm(orderForm3)

    orderForm1.let {
        it.addOrderItem(orderItem1)
        it.addOrderItem(orderItem2)
    }
    orderForm2.let {
        it.addOrderItem(orderItem3)
        it.addOrderItem(orderItem4)
        it.addOrderItem(orderItem5)
    }
    orderForm3.let {
        it.addOrderItem(orderItem6)
        it.addOrderItem(orderItem7)
        it.addOrderItem(orderItem8)
        it.addOrderItem(orderItem9)
        it.addOrderItem(orderItem10)
    }

    commodity1.addOrderItem(orderItem3)
    commodity2.addOrderItem(orderItem4)
    commodity3.addOrderItem(orderItem5)
    commodity4.addOrderItem(orderItem1)
    commodity5.addOrderItem(orderItem2)
    commodity6.addOrderItem(orderItem6)
    commodity7.addOrderItem(orderItem7)
    commodity8.addOrderItem(orderItem8)
    commodity9.addOrderItem(orderItem9)
    commodity10.addOrderItem(orderItem10)

    sessionFactory.currentSession.use {
        it.beginTransaction()
        it.save(seller1)
        it.save(seller2)
        it.save(seller3)
        it.save(seller4)
        it.save(customer1)
        it.save(customer2)
        it.save(customer3)
        it.transaction.commit()
    }

    sessionFactory.close()
}
TOP