SpringBoot系列——Spring-Data-JPA

  前言

  jpa是ORM映射框架,更多详情,请戳:apring-data-jpa官网:http://spring.io/projects/spring-data-jpa,以及一篇优秀的博客:https://www.cnblogs.com/cmfwm/p/8109433.html,这里只是记录项目实现。

   

  查询方式

  jpa查询方法大致可分为JPA命名查询@Query查询,EntityManager对象查询

  

  JPA命名查询

interface PersonRepositoryextendsRepository<User, Long>{
    List<Person> findByEmailAddressAndLastname(EmailAddress emailAddress,String lastname);
    // 去重查询
    List<Person> findDistinctPeopleByLastnameOrFirstname(String lastname,String firstname);
    List<Person> findPeopleDistinctByLastnameOrFirstname(String lastname,String firstname)
    // 忽略大小写
    List<Person> findByLastnameIgnoreCase(String lastname);
    List<Person> findByLastnameAndFirstnameAllIgnoreCase(String lastname,String firstname);
    // 排序查询
    List<Person> findByLastnameOrderByFirstnameAsc(String lastname);
    List<Person> findByLastnameOrderByFirstnameDesc(String lastname);}
}
    使用Top和First限制查询的结果大小
User findFirstByOrderByLastnameAsc();
User findTopByOrderByAgeDesc();
Page<User> queryFirst10ByLastname(String lastname,Pageable pageable);
Slice<User> findTop3ByLastname(String lastname,Pageable pageable);
List<User> findFirst10ByLastname(String lastname,Sort sort);
List<User> findTop10ByLastname(String lastname,Pageable pageable);

  统计查询

interface UserRepositoryextendsCrudRepository<User,Long>{
    long countByLastname(String lastname);
}
  分页查询
Page<User>findAll(PageRequest.of(1,20));

  

  下表描述了JPA支持的关键字以及包含该关键字的JPA命名查询方法:

关键字

示例

SQL

And

findByLastnameAndFirstname

… where x.lastname = ?1 and x.firstname = ?2

Or

findByLastnameOrFirstname

… where x.lastname = ?1 or x.firstname = ?2

Is,Equals

findByFirstname,findByFirstnameIs,findByFirstnameEquals

… where x.firstname = ?1

Between

findByStartDateBetween

… where x.startDate between ?1 and ?2

LessThan

findByAgeLessThan

… where x.age < ?1

LessThanEqual

findByAgeLessThanEqual

… where x.age <= ?1

GreaterThan

findByAgeGreaterThan

… where x.age > ?1

GreaterThanEqual

findByAgeGreaterThanEqual

… where x.age >= ?1

After

findByStartDateAfter

… where x.startDate > ?1

Before

findByStartDateBefore

… where x.startDate < ?1

IsNull

findByAgeIsNull

… where x.age is null

IsNotNull,NotNull

findByAge(Is)NotNull

… where x.age not null

Like

findByFirstnameLike

… where x.firstname like ?1

NotLike

findByFirstnameNotLike

… where x.firstname not like ?1

StartingWith

findByFirstnameStartingWith

… where x.firstname like ?1(parameter bound with appended %)

EndingWith

findByFirstnameEndingWith

… where x.firstname like ?1(parameter bound with prepended %)

Containing

findByFirstnameContaining

… where x.firstname like ?1(parameter bound wrapped in %)

OrderBy

findByAgeOrderByLastnameDesc

… where x.age = ?1 order by x.lastname desc

Not

findByLastnameNot

… where x.lastname <> ?1

In

findByAgeIn(Collection<Age> ages)

… where x.age in ?1

NotIn

findByAgeNotIn(Collection<Age> ages)

… where x.age not in ?1

True

findByActiveTrue()

… where x.active = true

False

findByActiveFalse()

… where x.active = false

IgnoreCase

findByFirstnameIgnoreCase

… where UPPER(x.firstame) = UPPER(?1)

 

  @Query查询

  普通查询

public interface UserRepositoryextendsJpaRepository<User,Long>{
    @Query(value ="SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery =true)
    User findByEmailAddress(String emailAddress);
}
  排序查询
public interface UserRepositoryextendsJpaRepository<User, Long>{
    @Query("select u from User u where u.lastname like ?1%")
    List<User> findByAndSort(String lastname,Sort sort);
}
  分页查询
public interface UserRepositoryextendsJpaRepository<User,Long>{
    @Query(value ="SELECT * FROM USERS WHERE LASTNAME = ?1",
        countQuery ="SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
        nativeQuery =true)
    Page<User> findByLastname(String lastname,Pageable pageable);
}

 

  使用命名参数

public interface UserRepositoryextendsJpaRepository<User,Long>{
    @Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
    User findByLastnameOrFirstname(@Param("lastname")String lastname,@Param("firstname")String firstname);
}
  删除,如果希望自动清除EntityManager,可以将@Modifying注释的clearautomatic属性设置为true。
interface UserRepositoryextendsRepository<User, Long> {
    @Modifying
    @Transactional
    @Query("delete from User u where user.role.id = ?1")
    void deleteInBulkByRoleId(long roleId);
}

 

  EntityManager对象查询  

    @PersistenceContext
    private EntityManager em;


    private void pageTest() {
        //SQL
        String sql = "select * from tb_user t where t.username like :name";

        //设置SQL、映射实体,以及设置值,返回一个Query对象
        Query query = em.createNativeQuery(sql, TbUser.class).setParameter("name", "huanzi%");

        //分页、排序信息,并设置,page从0开始
        PageRequest pageRequest = PageRequest.of(0, 10, new Sort(Sort.Direction.ASC, "id"));
        query.setFirstResult((int) pageRequest.getOffset());
        query.setMaxResults(pageRequest.getPageSize());

        //获取分页结果
        Page page = PageableExecutionUtils.getPage(query.getResultList(), pageRequest, () -> {
            //设置countQuerySQL语句
            Query countQuery = em.createNativeQuery("select count(1) from ( " + ((NativeQueryImpl) query).getQueryString() + " ) count_table");
            //设置countQuerySQL参数
            query.getParameters().forEach(parameter -> countQuery.setParameter(parameter.getName(), query.getParameterValue(parameter.getName())));
            //返回一个总数
            return Long.valueOf(countQuery.getResultList().get(0).toString());
        });

        //组装返回值

        //总数
        long total = page.getTotalElements();
        System.out.println(total);
        //分页信息
        Pageable pageable = page.getPageable();
        System.out.println(pageable);
        //数据集合
        List<TbUser> content = page.getContent();
        System.out.println(content.size());
        System.out.println(content);
    }

 

  工程结构

 

  代码编写

  maven引包

        <!--添加springdata-jpa依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!--添加MySQL驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--lombok插件 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

 

  applicaction.yml

#注意:在yml文件中添加value值时,value前面需要加一个空格
#2.0.0的配置切换为servlet.path而不是"-"
server:
  port: 10086 #端口号
  servlet:
    context-path: /springboot #访问根路径

spring:
    thymeleaf:
      cache: false  #关闭页面缓存
      prefix: classpath:/view/  #thymeleaf访问根路径
      mode: LEGACYHTML5

    datasource: #数据库相关
      url: jdbc:mysql://localhost:3306/test?characterEncoding=utf-8
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

    jpa:
      show-sql: true

    mvc:
      date-format: yyyy-MM-dd HH:mm:ss #mvc接收参数时对日期进行格式化

    jackson:
      date-format: yyyy-MM-dd HH:mm:ss #jackson对响应回去的日期参数进行格式化
      time-zone: GMT+8

 

  实体类与表数据

  tb_user

  tb_description

 

/**
 * 用户类
 */
@Entity
@Table(name = "tb_user")
@Data
public class User implements Serializable {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY) //IDENTITY 自增
    private Integer id;

    @Column(name = "username")//命名相同或驼峰标识(与数据库下划线映射)可以不用写
    private String username;

    private String password;

    private Date created;

    private String descriptionId;

    @OneToOne
    @JoinColumn(name = "descriptionId",referencedColumnName = "id", insertable = false, updatable = false)
    @NotFound(action= NotFoundAction.IGNORE)
    //用户描述信息
    private Description description;
}
/**
 * 用户描述类
 */
@Entity
@Table(name = "tb_description")
@Data
public class Description implements Serializable {
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY) //IDENTITY 自增
    private Integer id;

    private String userId;

    private String description;
}

 

  通讯对象

/**
 * 统一返回对象
 */

@Data
public class Result<T> implements Serializable {
    /**
     * 通信数据
     */
    private T data;
    /**
     * 通信状态
     */
    private boolean flag = true;
    /**
     * 通信描述
     */
    private String msg = "";

    /**
     * 通过静态方法获取实例
     */
    public static <T> Result<T> of(T data) {
        return new Result<>(data);
    }

    public static <T> Result<T> of(T data, boolean flag) {
        return new Result<>(data, flag);
    }

    public static <T> Result<T> of(T data, boolean flag, String msg) {
        return new Result<>(data, flag, msg);
    }

    @Deprecated
    public Result() {

    }

    private Result(T data) {
        this.data = data;
    }

    private Result(T data, boolean flag) {
        this.data = data;
        this.flag = flag;
    }

    private Result(T data, boolean flag, String msg) {
        this.data = data;
        this.flag = flag;
        this.msg = msg;
    }

}

 

 

  分页对象

/**
 * 分页对象(参考JqGrid插件)
 */
@Data
public class PageInfo<M> {
    private int page;//当前页码
    private int pageSize;//页面大小
    private String sidx;//排序字段
    private String sord;//排序方式

    private List<M> rows;//分页结果
    private int records;//总记录数
    private int total;//总页数

    /**
     * 获取统一分页对象
     */
    public static <M> PageInfo<M> of(Page page, Class<M> entityModelClass) {
        int records = (int) page.getTotalElements();
        int pageSize = page.getSize();
        int total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1;

        PageInfo<M> pageInfo = new PageInfo<>();
        pageInfo.setPage(page.getNumber() + 1);//页码
        pageInfo.setPageSize(pageSize);//页面大小
        pageInfo.setRows(CopyUtil.copyList(page.getContent(), entityModelClass));//分页结果
        pageInfo.setRecords(records);//总记录数
        pageInfo.setTotal(total);//总页数
        return pageInfo;
    }

    /**
     * 获取JPA的分页对象
     */
    public static Page readPage(Query query, Pageable pageable, Query countQuery) {
        if (pageable.isPaged()) {
            query.setFirstResult((int) pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());
        }
        return PageableExecutionUtils.getPage(query.getResultList(), pageable, () -> executeCountQuery(countQuery));
    }

    private static Long executeCountQuery(Query countQuery) {
        Assert.notNull(countQuery, "TypedQuery must not be null!");

        List<Number> totals = countQuery.getResultList();
        Long total = 0L;
        for (Number number : totals) {
            if (number != null) {
                total += number.longValue();
            }
        }
        return total;
    }
}
/**
 * 分页条件(参考JqGrid插件)
 */
@Data
public class PageCondition {
    private int page = 1;//当前页码
    private int rows = 10;//页面大小
    private String sidx;//排序字段
    private String sord;//排序方式

    /**
     * 获取JPA的分页查询对象
     */
    public Pageable getPageable() {
        //处理非法页码
        if (page < 0) {
            page = 1;
        }
        //处理非法页面大小
        if (rows < 0) {
            rows = 10;
        }
        return PageRequest.of(page - 1, rows);
    }
}

   2019-09-27补充:之前的这个分页信息对象不是很好,现在更新一下

  1、int改成Integer

  2、非法参数处理加强

  3、使用@JsonInclude注解,减少数据传输,例如:

 

 

 

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.util.StringUtils;

/**
 * 分页条件(参考JqGrid插件)
 */
@Data
//当属性的值为空(null或者"")时,不进行序列化,可以减少数据传输
@JsonInclude(JsonInclude.Include.NON_EMPTY)
public class PageCondition {
    private Integer page;//当前页码
    private Integer rows;//页面大小
    private String sidx;//排序字段
    private String sord;//排序方式

    /**
     * 获取JPA的分页查询对象
     */
    @JsonIgnore
    public Pageable getPageable() {
        //处理非法页码
        if (StringUtils.isEmpty(page) || page < 0) {
            page = 1;
        }
        //处理非法页面大小
        if (StringUtils.isEmpty(rows) || rows < 0) {
            rows = 10;
        }
        return PageRequest.of(page - 1, rows);
    }
}

 

 

 

   UserController

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping("/getAllUser")
    public ModelAndView getAllUser(){
        Result result=userService.getAllUser();
        ModelAndView mv=new ModelAndView();
        mv.addObject("userList",result.getData());
        mv.setViewName("index.html");
        return mv;
    }

    @RequestMapping("page")
    public Result<PageInfo<User>> page(User entity, PageCondition pageCondition) {
        return userService.page(entity,pageCondition);
    }

    @RequestMapping("list")
    public Result<List<User>> list(User entity) {
        return userService.list(entity);
    }

    @RequestMapping("get/{id}")
    public Result<User> get(@PathVariable("id") Integer id) {
        return userService.get(id);
    }

    @RequestMapping("save")
    public Result<User> save(User entity) {
        return userService.save(entity);
    }

    @RequestMapping("delete/{id}")
    public Result<Integer> delete(@PathVariable("id") Integer id){
        return userService.delete(id);
    }
}

 

  UserService

public interface UserService{

    Result<PageInfo<User>> page(User entity, PageCondition pageCondition);

    Result<List<User>> list(User entity);

    Result<User> get(Integer id);

    Result<User> save(User entity);

    Result<Integer> delete(Integer id);

    Result getAllUser();
}
@Service
@Transactional
public class UserServiceImpl implements UserService { @Autowired private UserRepository userRepository; @Override public Result<PageInfo<User>> page(User entity ,PageCondition pageCondition) { Page<User> page = userRepository.findAll(Example.of(CopyUtil.copy(entity, User.class)), pageCondition.getPageable()); int records = (int) page.getTotalElements(); int pageSize = page.getSize(); int total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1; PageInfo<User> pageInfo = new PageInfo<>(); pageInfo.setPage(page.getNumber() + 1);//页码 pageInfo.setPageSize(pageSize);//页面大小 pageInfo.setRows(page.getContent());//分页结果 pageInfo.setRecords(records);//总记录数 pageInfo.setTotal(total);//总页数 return Result.of(pageInfo); } @Override public Result<List<User>> list(User entity) { List<User> entityList = userRepository.findAll(Example.of(entity)); return Result.of(entityList); } @Override public Result<User> get(Integer id) { Optional<User> optionalE = userRepository.findById(id); if (!optionalE.isPresent()) { throw new RuntimeException("ID不存在!"); } return Result.of(optionalE.get()); } @Override public Result<User> save(User entity) { User user = userRepository.save(entity); return Result.of(user); } @Override public Result<Integer> delete(Integer id) { userRepository.deleteById(id); return Result.of(id); } @Override public Result getAllUser() { List<User> userList = userRepository.getAllUser(); if(userList != null && userList.size()>0){ return Result.of(userList); }else { return Result.of(userList,false,"获取失败!"); } } }

 

  UserRepository

public interface UserRepository extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> {

    @Query(value = "from User") //HQL
//    @Query(value = "select * from tb_user",nativeQuery = true)//原生SQL
    List<User> getAllUser();

}

 

  效果

  get接口

  http://localhost:10086/springboot/user/get/1

 

  list接口

  http://localhost:10086/springboot/user/list

   http://localhost:10086/springboot/user/list?username=张三

   

  page接口

  http://localhost:10086/springboot/user/page?page=1&rows=10

   http://localhost:10086/springboot/user/page?page=1&rows=10&username=张三

 

  save接口(插入跟更新)

  没有id或id不存在,为插入,http://localhost:10086/springboot/user/save?username=张麻子&password=123

  id已存在,则为更新,注意:这里的更新是你的字段是什么jpa就帮你存什么,如果想要实现只更新接参对象有值的字段,应该先用id去同步数据,再更新,http://localhost:10086/springboot/user/save?id=1&username=张三1&password=666

  delete接口

  http://localhost:10086/springboot/user/delete/6

 

  自定义Dao层方法

  http://localhost:10086/springboot/user/getAllUser

  后记

   JPA牛逼!

  注意:

<!--继承信息-->
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.2.RELEASE</version>
    <relativePath/>
</parent>
<!--继承信息-->
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.9.RELEASE</version>
    <relativePath/>
</parent>

  2.0之后是大版本升级,使用了1.8JDK,许多方法API都发生了改变,其中就碰到了一个坑,之前写得不规范,Service中已经@Transactional了一次,Repository的自定义SQL又@Transactional一次,当我们service直接调公用JPA方法,后面又调我们自定义的Dao层方法时,由于事务传播,后面的事务不进行提交,而且也不报错

 

  补充

  有同学发现我们少贴了部分代码,在这里补充一下,CopyUtil类是我们自定义的一个实体类型转换的工具类,用于将实体模型与实体的转换,在 SpringBoot系列——Spring-Data-JPA(升级版)中,我们已经对该工具类进行了升级,升级之后支持复杂对象的转换

/**
 * 实体类型转换的工具类
 */

public class CopyUtil {

    /**
     * 类型转换:实体模型<->实体
     * <p>
     * 例如:List<DataModel> <--> List<Data>
     */

    public static <T> List<T> copyList(List list, Class<T> target) {
        List<T> newList = new ArrayList<>();
        for (Object entity : list) {
            newList.add(copy(entity, target));
        }
        return newList;
    }

    /**
     * 类型转换:实体模型 <->实体
     * <p>
     * 例如:DataModel <--> Data
     */
    public static <T> T copy(Object origin, Class<T> target) {
        T t = null;
        try {
            t = target.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        }
        BeanUtils.copyProperties(origin, t);
        return t;
    }
}

 

  2019-06-21补充:jpa有个坑,默认情况下(数据库是oracle),调用super.方法,事务会排在最后提交,因此造成我update的数据后面又被super的事务update回来

  (PS:如果你看到这里,就要注意正确使用super.方法,super.方法应该要放在最后调用,避免入坑,事实上,关于jpa的事务管理还有很多坑,这个我们以后再好好聊)

  例如:

    //先调用父类的保存方法    
  super.save();

   //执行自定义update语句
  my.update();

 

  虽然代码先执行super.save(),但这个保存事务会在最后执行提交

  我们期待的事务顺序应该是这样

  最后我是直接使用在service层注入dao层,直接调用dao.save(),事务处理就与我们期待的一样了

  可能有些同学看到会有些疑问,看到我两个操作都有操作同一个表,为什么不合并成一次操作呢,直接修改对象值,调用super.save()方法进行保存不就行了吗,还要直接写update??  这是因为save方法我们进行了特殊处理,jpa原生save方法是传入的对象的属性值是什么就帮我们保存什么,这并不符合我们的常规保存操作,我传入一个对象,里面哪些属性有值就帮我们保存哪些值,而我们的自定义update刚好就是有设置某个字段的值为空,调用父类的save方法不会帮我们设置,所以才需要单独写一个update

 

  我被SQL注入了

  2019-10-24补充:客户突然反馈,网站访问出现访问响应时间太久、进而崩溃的情况,查看日志发现是数据库连接超时,仔细一看执行的SQL不对劲

java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30014ms.

 

select * from super_search_ucid  where 1 = 1  and game_id = '6' AND 7645=IF((ORD(MID((SELECT DISTINCT(IFNULL(CAST(grantee AS CHAR),CHAR(32))) FROM information_schema.USER_PRIVILEGES LIMIT 0,1),2,1)) > 3008),SLEEP(5),7645) AND 'HhsP'='HhsP' and role_list like '%SP琉%' and props_list like '%MR琉璃%' and ( division like '%官服安卓IOS通用%' )

 

 

 

   这个SQL被SQL注入攻击,这个就是对方注入进来的字符串

6' AND 7645=IF((ORD(MID((SELECT DISTINCT(IFNULL(CAST(grantee AS CHAR),CHAR(32))) FROM information_schema.USER_PRIVILEGES LIMIT 0,1),2,1)) > 3008),SLEEP(5),7645) AND 'HhsP'='HhsP

  他在疯狂的查询信息库的用户权限表,企图搞事情

 

  经实测,这个SQL在生产上执行耗时一百多秒都没响应,导致数据库连接池的连接线程一直被占用,没有空闲的可以被调用导致连接超时

  

 

  这个接口是查询接口,我们在Security的配置中配置的是无需登录就可以访问,而且由于需要分页,同时还需要等值查询等各种操作,所以我采用的是文章前面提到的 EntityManager对象查询 方法,动态拼接SQL,而且当时并没有想到需要防范SQL注入,这才落下了那么大的一个坑,所幸并没有造成重大影响

 

  防范、解决:

  1、动态拼接的SQL一定要记得做转义

    /**
     * sql转义
     */
    public static String escapeSql(String str) {
        if (str == null) {
            return null;
        }
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < str.length(); i++) {
            char src = str.charAt(i);
            switch (src) {
                case '\'':
                    sb.append("''");// hibernate转义多个单引号必须用两个单引号
                    break;
                case '\"':
                case '\\':
                    sb.append('\\');
                default:
                    sb.append(src);
                    break;
            }
        }
        return sb.toString();
    }

 

  2、切记不要再使用select * 了,一个是查询耗时久,一个是如果不小心被SQL注入还容易泄露数据,可以参考之前写的博客《利用反射跟自定义注解拼接实体对象的查询SQL》,拼接全字段查询语句

 

  代码开源

  代码已经开源、托管到我的GitHub、码云:

  GitHub:https://github.com/huanzi-qch/springBoot

  码云:https://gitee.com/huanzi-qch/springBoot

 

posted @ 2018-11-16 17:32  huanzi-qch  阅读(5783)  评论(5编辑  收藏