网站建设论文3000字范文,在线服务器代理,国内主流网站服务器,石家庄网站开发哪家好概述 ORM映射为我们带来便利的同时#xff0c;也失去了较大灵活性#xff0c;如果SQL较复杂#xff0c;要进行动态查询#xff0c;那必定是一件头疼的事情#xff08;也可能是lz还没发现好的方法#xff09;#xff0c;记录下自己用的三种复杂查询方式。 环境 springBoo… 概述 ORM映射为我们带来便利的同时也失去了较大灵活性如果SQL较复杂要进行动态查询那必定是一件头疼的事情也可能是lz还没发现好的方法记录下自己用的三种复杂查询方式。 环境 springBoot IDEA2017.3.4 JDK8 pom.xml ?xml version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersionparentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion2.1.6.RELEASE/versionrelativePath/ !-- lookup parent from repository --/parentgroupIdcom.xmlxy/groupIdartifactIdseasgame/artifactIdversion0.0.1-SNAPSHOT/versionnameseasgame/namedescriptionDemo project for Spring Boot/descriptionpropertiesjava.version1.8/java.version/propertiesdependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependency!--数据库连接--dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdscoperuntime/scope/dependency!-- 热启动等 --dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-devtools/artifactIdscoperuntime/scopeoptionaltrue/optional/dependency!--Java bean 实体--dependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId/dependency!--swagger2 API 测试工具 --dependencygroupIdio.springfox/groupIdartifactIdspringfox-swagger2/artifactIdversion2.8.0/version/dependencydependencygroupIdio.springfox/groupIdartifactIdspringfox-swagger-ui/artifactIdversion2.8.0/version/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-configuration-processor/artifactIdoptionaltrue/optional/dependency!--安全框架认证--dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-security/artifactId/dependencydependencygroupIdnet.sf.json-lib/groupIdartifactIdjson-lib/artifactIdversion2.2.2/versionclassifierjdk15/classifier/dependency!--汉字转拼音--dependencygroupIdcom.belerweb/groupIdartifactIdpinyin4j/artifactIdversion2.5.1/version/dependency!-- thymeleaf模板 --dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-thymeleaf/artifactId/dependency!--dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId移除嵌入式tomcat插件exclusionsexclusiongroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-tomcat/artifactId/exclusion/exclusions/dependency--dependencygroupIdjavax.servlet/groupIdartifactIdjavax.servlet-api/artifactIdversion3.1.0/versionscopeprovided/scope/dependency/dependenciespackagingwar/packagingbuildpluginsplugingroupIdorg.springframework.boot/groupIdartifactIdspring-boot-maven-plugin/artifactId/pluginplugingroupIdorg.apache.maven.plugins/groupIdartifactIdmaven-compiler-plugin/artifactIdconfigurationsource1.8/sourcetarget1.8/target/configuration/plugin/pluginsfinalNameseasgame/finalNamepluginManagementpluginsplugingroupIdorg.apache.maven.plugins/groupIdartifactIdmaven-compiler-plugin/artifactIdversion2.3.2/versionconfigurationencoding${project.build.sourceEncoding}/encodingsource1.7/sourcetarget1.7/target/configuration/pluginplugingroupIdorg.apache.maven.plugins/groupIdartifactIdmaven-surefire-plugin/artifactIdconfigurationtestFailureIgnoretrue/testFailureIgnore/configuration/plugin/plugins/pluginManagement/build/project Query 当一个SQL较为复杂时第一个想到的就是原生的SQL语句。如果只是简单的查询那情况还没这么糟糕 Query(value SELECT IFNULL(sum(right_num),0) sumRight FROM t_record WHERE record_owner_id ?1 AND responder_no ?2 ,nativeQuery true)MapString,Object sumRightNum(int studentId,int responderNo); 但如果需要进行动态查询或更改那这个value就变得复杂了。 package com.xmlxy.seasgame.dao;import com.xmlxy.seasgame.entity.ScoreEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;import java.util.List;/*** * Description: * author hwc* date 2019/9/5* return
*/
public interface ScoreDao extends CrudRepositoryScoreEntity,Integer
{/** * * Description:*param scoreEntity* author hwc* date 2019/9/6*/Transactional(rollbackFor Exception.class)ModifyingQuery(value UPDATE t_score t SET t.responder_no CASE WHEN :#{#scoreEntity.responderNo} IS NULL THEN t.responder_no ELSE :#{#scoreEntity.responderNo} END, t.max_level CASE WHEN :#{#scoreEntity.maxLevel} IS NULL THEN t.max_level ELSE :#{#scoreEntity.maxLevel} END, t.right_num CASE WHEN :#{#scoreEntity.rightNum} IS NULL THEN t.right_num ELSE :#{#scoreEntity.rightNum} END, t.use_time CASE WHEN :#{#scoreEntity.userTime} IS NULL THEN t.use_time ELSE :#{#scoreEntity.userTime} END WHERE student_id :#{#scoreEntity.getStudentId()},nativeQuery true)void updateScore(Param(scoreEntity) ScoreEntity scoreEntity);
} JPQL 如果Java代码内发出JPQL查询就需要利用到EntityManager的响应方法了。一般执行以下流程 获取一个EntityManager实例调用实例的方法createQuery创建一个Query实例如果有需要可以指定检索的最大数量和起始位置使用Query方法getResultList执行查询当然更新和删除操作得使用executeUpdate执行进行一个复杂的动态SQL查询 public PageRankEntity getScoreByRank(int gradeId,int classId,Pageable pageable){StringBuilder countSelectSql new StringBuilder();countSelectSql.append( SELECT COUNT(*) );countSelectSql.append( FROM );countSelectSql.append( t_score s, );countSelectSql.append( t_student st );countSelectSql.append( WHERE );countSelectSql.append( s.student_id st.student_id );StringBuilder selectSql new StringBuilder();selectSql.append( SELECT s.student_id,st.real_name,st.student_class,s.max_level,s.use_time,s.right_num );selectSql.append( FROM t_score s );selectSql.append( JOIN t_student st ON s.student_id st.student_id );selectSql.append( WHERE 1 1 );MapString,Object params new HashMap();StringBuilder whereSql new StringBuilder();if (gradeId ! -1){whereSql.append( AND st.student_grade :student_grade );params.put(student_grade,gradeId);}/**班级ID*/if (classId ! -1){whereSql.append( AND st.student_class :classId );params.put(classId,classId);}String orderSql ORDER BY s.max_level DESC,s.use_time,s.right_num ASC ;String countSql new StringBuilder().append(countSelectSql).append(whereSql).toString();Query countQuery entityManager.createNativeQuery(countSql);for (Map.EntryString,Object entry : params.entrySet()){countQuery.setParameter(entry.getKey(),entry.getValue());}BigInteger totalCount (BigInteger)countQuery.getSingleResult();String querySql new StringBuilder().append(selectSql).append(whereSql).append(orderSql).toString();Query query entityManager.createNativeQuery(querySql,RankEntity.class);for (Map.EntryString,Object entry:params.entrySet()){query.setParameter(entry.getKey(),entry.getValue());}query.setFirstResult((int) pageable.getOffset());query.setMaxResults(pageable.getPageSize());ListRankEntity rankEntities query.getResultList();PageRankEntity page new PageImpl(rankEntities,pageable,totalCount.longValue());return page;}注意如果没有重新定义Pageable那么pageNumber必须减1因为是从0开始的。 Criteria 这是一种规范查询是以元模型的概念为基础的这个元模型可以是实体累嵌入类或者映射的父类简单介绍几个里面用到接口。 CriteraQuery是一个特定的顶层查询对象里面包含selectfromwhereorder by等各个部分然而他只对实体类或嵌入类的标准查询起作用。 Root标准查询的根对象根定义了实体类型是你想要查询要获得的结果也可以添加查询条件结合实体管理对象得到查询的对象。 CriteriaBuilder接口用来构建CritiaQuery的构建器 StudentEntity类 package com.xmlxy.seasgame.entity;import io.swagger.annotations.ApiModel;
import lombok.Data;import javax.persistence.*;
import javax.print.attribute.standard.MediaSize;
import java.io.Serializable;/*** * Description:学生对象* param* author hwc* date 2019/8/8
*/
Entity
Table(name t_base_student)
ApiModel
Data
public class StudentEntity implements Serializable
{private static final long serialVersionUID 546L;IdGeneratedValue(strategy GenerationType.AUTO)Column(name student_id)private Integer studentId;Column(name student_grade)private Integer studentGrade;Column(name student_class)private Integer studentClass;Column(name address)private String address;Column(name telephone)private Integer telephone;Column(name real_name)private String realName;Column(name id_number)private String idNumber;Column(name study_id)private String studyId;Column(name is_delete)private int isDelete;Column(name uuid)private String uuid;} dao层 public interface StudentDao extends JpaRepositoryStudentEntity,Integer,JpaSpecificationExecutor
{
} 动态查询 public PageStudentEntity getTeacherClassStudent(int pageNumber,int pageSize,int gradeId, int classId,String keyword){pageNumber pageNumber 0 ? 0 : pageNumber;pageSize pageSize 0 ? 10 : pageSize;SpecificationStudentEntity specification new SpecificationStudentEntity(){Overridepublic Predicate toPredicate(RootStudentEntity root, CriteriaQuery? criteriaQuery, CriteriaBuilder criteriaBuilder){//page : 0 开始, limit : 默认为 10ListPredicate predicates new ArrayList();predicates.add(criteriaBuilder.equal(root.get(studentGrade),gradeId));predicates.add(criteriaBuilder.equal(root.get(studentClass),classId));if (!Constant.isEmptyString(keyword)){predicates.add(criteriaBuilder.like(root.get(realName).as(String.class),% keyword %));}return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));}};/*studentId必须是实体类属性与数据库对应否则报ropertyReferenceException异常*/PageRequest page new PageRequest(pageNumber,pageSize,Sort.Direction.ASC,studentId);PageStudentEntity pages studentDao.findAll(specification,page);return pages;} 因为这个项目应用比较简单所以条件只有一个如果条件较多甚至可以定义一个专门的类去接收拼接参数然后判断成立就add进去。 转载一篇写得不错的文章https://blog.csdn.net/u010775025/article/details/80497986 转载于:https://www.cnblogs.com/dslx/p/11474453.html
