java 查询功能实现的八种方式

电玩女神 2022-02-03 11:47 468阅读 0赞

[前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到教程。][Link 1]

只是记录一下

**1. HQL**

**2. Mybatis : mapper.xml 中写 sql 。**

**3. Mybatis : 注解 @Select**

@Select("select * from xxx_order where order_no = #{orderNo}")
    @ResultMap("BaseResultMap")
    XxxOrder selectByOrderNo(@Param("orderNo") String orderNo);

**4. Mybatis : 注解 @SelectProvider**

@SelectProvider(type = XxxOrderProvider.class, method = "listOrderPdDataSql")  
    List<OrderPdExportVo> listOrderPdData(XxxOrderQuery query);
    
    # sql写在 Provider 类中。

详细用法见文章：[mybatis3 中 @Provider 的使用方式][mybatis3 _ _Provider]

**5. Mybatis : Example （此方法亦有用方法4实现.）**

Example example = new Example(XxxRule.class);
    Criteria criteria = example.createCriteria();
    criteria.andEqualTo("status", 1);

详细用法见文章：[MyBatis : Mapper 接口以及 Example 使用实例、详解][MyBatis _ Mapper _ Example]

关于 Example 分页有三种方式，见文章 ：[mybatis 中 Example 的使用 ：条件查询、排序、分页（三种分页方式 : RowBounds、PageHelpler 、limit ）][mybatis _ Example _ _ _ RowBounds_PageHelpler _limit]

** 6. Java Persistence API = JPA 、JPQL   **

相关文章：[JPA criteria 查询:类型安全与面向对象][JPA criteria]

@PersistenceContext
    private EntityManager em;
    ...
    
    String querySql = "SELECT xxx_id FROM xxxsibility
                       WHERE dxxpxxxnt_id = '" + dxxpxxxntId 
                  + "' AND code != '10010'";
    List<Object> xxxIdList = em.createNativeQuery(querySql).getResultList();

**7. spring-data-jpa : JpaRepository<实体类, 主键类型> **

spring-data-jpa 相关文章：

[spring data jpa 的 in 查询 Specification 实现][spring data jpa _ in _ Specification]

[spring-data-jpa 介绍 复杂查询，包括多表关联，分页，排序][spring-data-jpa _]

[使用 Spring Data JPA 简化 JPA 开发][Spring Data JPA _ JPA]

[spring-data-JPA使用JpaRepository注解自定义SQL查询数据库多表查询][spring-data-JPA_JpaRepository_SQL]

[spring-data-jpa 使用][spring-data-jpa]

例：

@Repository
    public interface WorkxxxRepository extends JpaRepository<Workxxx, 
    Integer>, JpaSpecificationExecutor<Workxxx> { // 查询方法 }

@Autowired
    private WorkxxxRepository workxxxRepository;

用法1：
    public Object deleteDraft(User user) {
           return workxxxRepository.deleteDrafts(user.getId());
    }

用法2：
    Page<Workxxx> page = workxxxRepository.findAll(new Specification<Workxxx>() {
    	@Override
    	public Predicate toPredicate(Root<Workxxx> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    
    		List<Predicate> list = Lists.newArrayList();
    		//构造条件
    		list.add(cb.equal(root.get("delFlag"), "0"));                  
    
    		if (StringUtils.isNotEmpty(xxxName)) {
    			list.add(cb.like(root.get("xxxName"), "%" + xxxName + "%"));
    		}
    
    		// 参数 search 可代表姓名、名称、任务、类型中的任意一种
    		if (StringUtils.isNotEmpty(search)) {
    			Predicate p = cb.or(cb.like(root.get("xxxName"), "%" + search + "%"));
    			p = cb.or(p, cb.like(root.get("xxxName"), "%" + search + "%"));
    			p = cb.or(p, cb.like(root.get("xxxProject"), "%" + search + "%"));
    			p = cb.or(p, cb.like(root.get("xxxType"), "%" + search + "%"));
    			list.add(p);
    		}	   
    		 
    		Predicate[] p = new Predicate[list.size()];
    		return cb.and(list.toArray(p));
    	}
    }, new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), pageable.getSort()));

**8.  JDBC直连数据库，Statement、PrepareStatement查询。**

[Link 1]: https://www.captainai.net/jiangyu1013/
[mybatis3 _ _Provider]: https://blog.csdn.net/jiangyu1013/article/details/90024813
[MyBatis _ Mapper _ Example]: https://blog.csdn.net/jiangyu1013/article/details/88689767
[mybatis _ Example _ _ _ RowBounds_PageHelpler _limit]: https://blog.csdn.net/jiangyu1013/article/details/90140532
[JPA criteria]: https://blog.csdn.net/jiangyu1013/article/details/79056212
[spring data jpa _ in _ Specification]: https://blog.csdn.net/jiangyu1013/article/details/79056231
[spring-data-jpa _]: https://blog.csdn.net/jiangyu1013/article/details/78882839
[Spring Data JPA _ JPA]: https://blog.csdn.net/jiangyu1013/article/details/78807367
[spring-data-JPA_JpaRepository_SQL]: https://blog.csdn.net/jiangyu1013/article/details/78876288
[spring-data-jpa]: https://blog.csdn.net/jiangyu1013/article/details/78907650