随笔3.2

SpringDataJpa

HQL语句

1. HQL查简单询语句用法Query

(如果查找具体的一个或两个数据(不是全部数据)就要在HQL语句中加SELECT如果是全部查询就省略了直接FROM开头和SQL语句一样)

hql =" FROM Person p WHERE p.age/3 =10 "

hql =" FROM Person p WHERE( p.age>10) and(p.name =’lxh’) "

hql =" FROM Person p WHERE p.name is not null "

hql =" FROM Person p WHERE p.age between 20 and 30 "

hql =" FROM Person p WHERE p.name in (‘lch’,’ldf’) "

hql =" FROM Person p WHERE( p.age>10) and(p.name =’lxh’) "

hql =" FROM Person p WHERE p.name like ‘l%’ "

排列

hql =" FROM Person p order by p.age "

hql =" FROM Person p order by p.age desc "降序排列

hql =" FROM Person p order by p.age desc ,p.name "先按降序再按升序排

分组

hql =" SELECT p.sex,avg(p.age) FROM Person p group by p.sex "

hql =" SELECT p.sex,avg(p.age) FROM Person p group by p.sex having avg(p.age) >20

更新 ,删除

Update User set name =’lxh’ where name =’lll’

hql =" Delete Person WHERE name = ‘lxh’ "

hql =" FROM Person p WHERE p.name like ‘l%’ "

自己试验的demo如下:

package com.lk.jpademo.dao;

import com.lk.jpademo.domain.User;
import com.sun.org.apache.xpath.internal.operations.String;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @Author: Lukizzz
 * @Date: 2018/8/1 14:15
 * @Description:
 */
public interface UserRepository extends JpaRepository<User,Integer> {
    /**
     * 通过"name"查询该用户
     * @param name
     * @return
     */
    @Query("Select u from User u where u.name = :name")
    User findUserByName(@Param("name")String name);

    /**
     * 查询第一个email的参数
     * @param email
     * @return
     */
    @Query("select u from User u where u.email = ?1")
    User findUserByEmail(String email);

    /**
     * 可以通过@Modifying和@Query来完成更新
     * 修改id为"id"的用户名字为"name"
     * @param name
     * @param id
     * @return 只能返回void或者int/integer
     */
    @Modifying
    @Query("update User u set u.name = :name where u.id = :id")
    Integer updateUserById(@Param("name") String name,@Param("id") Integer id);

也可以使用原生sql语句

例如:

    /**
     *原生SQL语句根据name查询用户
     * @param name
     * @return
     */
    @Query(value ="select * from USER u where u.name =:name ",nativeQuery = true)
    List<User> findUserByNames(@Param("name") String name);