spring-data-jpa,spring-data-mongodb,querydsl,spring-jdbc查询集锦

spring-data-jpa,spring-data-mongodb,querydsl,spring-jdbc查询集锦

本文主要列出spring-data-jpa,spring-data-mongodb,querydsl查询方式，包括同一个结果的不同查询方式对比：

一，mysql数据库查询：

1，spring-data-jpa方式查询：

实体org

@Data
@EqualsAndHashCode(callSuper = false)
@Entity
@Table(name = "sys_org")
public class Org extends BaseEntity {

    private static final long serialVersionUID = -3209581507992887143L;

    // 机构编号
    @Column(name = "code", columnDefinition = "varchar(32) COMMENT 代码'")
    private String code;

    // 机构名称
    @NotBlank(message = "组织名称不能为空")
    @Column(name = "name", columnDefinition = "varchar(32) COMMENT '组织名称'")
    private String name;

    @Column(name = "enterprise_link", columnDefinition = "varchar(32) COMMENT '企业联系人'")
    private String enterpriseLink;

    @Column(name = "legal_person", columnDefinition = "varchar(32) COMMENT '负责人名称'")
    private String legalPerson;

    @Column(name = "tel", columnDefinition = "varchar(32) COMMENT '联系电话'")
    private String tel;

    @Column(name = "address", columnDefinition = "varchar(128) COMMENT '联系地址'")
    private String address;

    @Column(length = 4)
    private Integer orderNum;

    @Column(name = "category", columnDefinition = "varchar(32) COMMENT '企业性质'")
    private String category;
    @NotNull
    @Column(name = "type", columnDefinition = "int COMMENT '机构类型'")
    private Integer type;
}

spring-data-jpa查询方式的dao:

dao中要继承JpaSpecificationExecutor, JpaRepository, PagingAndSortingRepository

package com.cecft.common.dao;

import com.cecft.common.entity.Org;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface OrgDao extends JpaSpecificationExecutor<Org>, JpaRepository<Org, String>, PagingAndSortingRepository<Org, String> {
    @Query("select o from Org o where o.id = ?1")
    Org findDeptById(String id);
    Org findByDelFlagAndCode(int delFlag, String code);
}

service中主要代码（不是完整的）

  public List<Org> findByDelFlagAndCode(int delFlag, String code) {
        return orgDao.findByDelFlagAndCode(delFlag, code);
    }

2，querydsl方式查询：

同样的实体，此种查询方式，dao中内容与此查询无关

####### pom.xml文件中相关依赖

<dependency>
			<groupId>com.querydsl</groupId>
			<artifactId>querydsl-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>com.querydsl</groupId>
			<artifactId>querydsl-apt</artifactId>
		</dependency>

同时还要引入插件：

<plugin>
				<groupId>com.mysema.maven</groupId>
				<artifactId>apt-maven-plugin</artifactId>
				<version>1.1.3</version>
				<executions>
					<execution>
						<goals>
							<goal>process</goal>
						</goals>
						<configuration>
							<outputDirectory>target/generated-sources/java</outputDirectory>
							<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
						</configuration>
					</execution>
				</executions>
			</plugin>

该插件会查找使用javax.persistence.Entity注解的域类型，并为它们生成对应的查询类型，即Q开头的类。

service中主要代码（不是完整的）

	@Autowired
	@PersistenceContext
	private EntityManager entityManager;
	private JPAQueryFactory queryFactory;
	@PostConstruct
	public void init() {
	   queryFactory = new JPAQueryFactory(entityManager);
	}

  public List<Org> findOrgsByDelFlag(Integer delFlag，String code) {
        QOrg qorg = QOrg.org;
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(qorg.delFlag.eq(delFlag));
        builder.and(qorg.co'de.eq(code));
     
        JPAQuery<Org> jpaQuery = queryFactory.select(qorg).from(qorg).where(builder).orderBy(qorg.updateTime.desc());
        return jpaQuery.fetch();
    }

3，spring-data-jap 与querydsl联合的方式查询：

参见我的另一个篇文章：https://blog.csdn.net/wuniangzhanyan/article/details/111247565

4，spring-jdbc查询方式：

同样的实体，此种查询方式，dao中内容与此查询无关

service中主要代码（不是完整的）

@Autowired
    private JdbcTemplate jdbctemplate;

    private String getByDelFlagAndCode(Integer delFlag，String code) {
        Set<String> result = new HashSet<>();

        String sql = "select l.name from sys_org gl where gl.del_flag = '"+delFlag+"'";
        jdbctemplate.query(sql, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Role item = new Role();
                item.setName(("" + rs.getString("name")).trim());
                result.add(item.getName());
            }
        });
     
    }

5，spring-data-jap 分组查询查询：

service中主要代码

public List<Tuple> userMessageVos (){
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<User> root = cq.from(User.class);
    List<Order> orderList = new ArrayList<>();
    orderList.add(cb.desc(cb.count(root)));//按照查询出来的数量进行排序
    Path<String> actualOverDate = root.get("createDate");//返回值中的创建事件
    cq.select(cb.tuple(createDate,cb.count(root)));
    cq.groupBy(createDate);//通过创建分组
    cq.orderBy(orderList);//排序
    TypedQuery<Tuple> q = entityManager.createQuery(cq);
    List<Tuple> result = q.getResultList();//查询
    return result;
}

单元测试中调用

@Test
public List<UserVo> testGroupSearch() {
    List<Tuple> list =  homePageAnalyseService.userMessageVos();
    List<UserVo> resultVos = new ArrayList<>();
    if (CollectionUtils.isNotEmpty(list)) {
        for (Tuple item : list) {
            UserVo vo = new UserVo();
            LocalDate date = (LocalDate)item.get(0);
            Integer count = (Integer)item.get(1);
            vo.setMonth(date.toString());
            vo.setCount(count);
            resultVos.add(vo);
        }
    }
    return list;
}

6，spring-jdbc 分组查询查询：

本示例是我自己的一个业务场景，记录下来供需要的人参考，我需要根据创建日期去按年，按月查询不同的数据表，但这些表中某些字段一样，做统计用的，主要用于给前端返回echars图表所用的数据。用上边的方法就不太适用了，因为上边查询5要针对每个表写一套，但是我这个方法6，我自己觉得还行，如果有更好的也可以帮我指导一下哦。
参数说明：
orgId：要统计的机构id；
beginDate：统计开始时间；
endDate：统计结束时间
accordingType：统计方式,按年或者按月
tableName:要查询的表名
dates：根据开始结束日期获取的时间集合

private List<BigDecimal> getHistogramList(String orgId, LocalDate beginDate,  LocalDate endDate, String accordingType, String tableName,List<String> dates) {
        Map<String, BigDecimal> map = new HashMap<>();
        List<BigDecimal> result = new ArrayList<>();
        String where = "where f.factor_id ='"+factorId+"' and approval_status ='" +FlowConstants.COMMON.APPROVAL_STATUS_CONFIRM_AGREE +"' ";
        String sql = "";
        //按月查询
        if (StringUtils.equals(ACCORDING_MONTH,accordingType)) {
            sql = "SELECT DATE_FORMAT(over_date,'%Y-%m') as month, sum(current_amount)  as money from "
                    +tableName
                    +" f "
                    +where
                    +" and ( over_date BETWEEN '"+beginDate+"' and '"+endDate+"') GROUP BY  month";
        }
        //按年查询
        if (StringUtils.equals(ACCORDING_YEAR,accordingType)) {
            sql = "SELECT DATE_FORMAT(over_date,'%Y') as month,sum(current_amount)  as money from "
                    +tableName
                    +" f "
                    +where
                    +" and (over_date BETWEEN '"+beginDate+"' and '"+endDate+"') GROUP BY  month";
        }

        jdbctemplate.query(sql, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                SearchResultVo item = new SearchResultVo();
                item.setMonth(("" + rs.getString("month")).trim());
                item.setMoney((rs.getBigDecimal("money")));
                map.put(item.getMonth(),item.getMoney());
            }
        });

        for (String month : dates) {
            //如果这个月份有数据，则加入list，否则为0
            if (map.get(month) != null) {
                result.add(map.get(month));
            } else {
                result.add(BigDecimal.ZERO);
            }
        }

        return result;
    }

二，mongodb数据库查询：

1，spring-data-mongodb查询方式：

实体

@Data
@Document(collection = "my_record")
public class BusinessRecord {
    @JsonSerialize(using = ObjectIdJsonSerializer.class)
    @Id
    private ObjectId id;
  	private String name;
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime; // 创建时间
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime updateTime; // 操作时间
   
}

(1)dao中声明接口的方式

dao

dao中要继承MongoRepository

package com.mongo.dao;

import java.util.List;
import org.bson.types.ObjectId;
import org.springframework.data.mongodb.repository.MongoRepository;
import org.springframework.stereotype.Repository;
import com.business.mongo.entity.BusinessRecord;

@Repository
public interface BusinessRecordDao extends MongoRepository<BusinessRecord, ObjectId> {
    List<BusinessRecord> findByName( String name);
}

service

 public List<BusinessRecord> findByName(String name) {
        return businessRecordDao.findByName(name);
    }

（2）mongoTemplate方式查询

service中主要代码（不是完整的）

import org.springframework.data.mongodb.core.MongoOperations;

@Resource(name = "mongoTemplate")
private MongoOperations mongoTemplate;

public List<BusinessRecord> getByName(String name) {
        Query query = new Query();
        query.addCriteria(Criteria.where("name").is(name));
        List<BusinessRecord> result = mongoTemplate.find(query, BusinessRecord.class);
        return result;
    }