IT序号网

springdata-jpa 八种查询方法知识解答

developer 2021年06月14日 数据库 181 0

 使用:maven+Spring+jpa+Junit4

 查询方式:SQL,JPQL查询,Specification多条件复杂查询

 返回类型:list<POJO>,list<Stinrg>,list<Object>,Page<Object>

git源码地址:https://github.com/litblank/SpringFrame/tree/master/DEMO/SpringData_jpa

1. Pom.xml

 

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> 
  <modelVersion>4.0.0</modelVersion> 
  <groupId>com.litblack</groupId> 
  <artifactId>SpringData_jpa</artifactId> 
  <packaging>war</packaging> 
  <version>0.0.1-SNAPSHOT</version> 
  <name>SpringData_jpa Maven Webapp</name> 
  <url>http://maven.apache.org</url> 
  <properties> 
        <spring.version>4.3.4.RELEASE</spring.version> 
  </properties> 
  <dependencies> 
    <dependency> 
      <groupId>junit</groupId> 
      <artifactId>junit</artifactId> 
      <version>3.8.1</version> 
      <scope>test</scope> 
    </dependency> 
    <!-- jpa -->         
        <dependency> 
            <groupId>org.hibernate</groupId> 
            <artifactId>hibernate-core</artifactId> 
            <version>4.3.8.Final</version> 
             <exclusions> 
                <exclusion> 
                    <groupId>org.javassist</groupId> 
                    <artifactId>javassist</artifactId> 
                </exclusion> 
            </exclusions> 
        </dependency> 
        <!-- <dependency> 
            <groupId>org.hibernate.javax.persistence</groupId> 
            <artifactId>hibernate-jpa-2.0-api</artifactId> 
            <version>1.0.1.Final</version> 
        </dependency> --> 
        <dependency> 
            <groupId>org.hibernate</groupId> 
            <artifactId>hibernate-entitymanager</artifactId> 
            <version>4.3.11.Final</version> 
            <exclusions> 
                <exclusion> 
                    <groupId>org.javassist</groupId> 
                    <artifactId>javassist</artifactId> 
                </exclusion> 
            </exclusions> 
        </dependency> 
        <!-- oracle --> 
        <dependency> 
            <groupId>ojdbc</groupId> 
            <artifactId>ojdbc6</artifactId> 
            <version>11.2.0.4</version> 
        </dependency> 
        <!-- mysql  --> 
        <dependency> 
            <groupId>mysql</groupId> 
            <artifactId>mysql-connector-java</artifactId> 
            <version>5.1.6</version> 
        </dependency> 
        <!-- ali 数据源连接 --> 
        <dependency> 
            <groupId>com.alibaba</groupId> 
            <artifactId>druid</artifactId> 
            <version>1.0.31</version> 
        </dependency> 
    &lt;dependency&gt; 
        &lt;groupId&gt;javassist&lt;/groupId&gt; 
        &lt;artifactId&gt;javassist&lt;/artifactId&gt; 
        &lt;version&gt;3.12.1.GA&lt;/version&gt; 
    &lt;/dependency&gt; 
     
    &lt;!-- Test --&gt; 
    &lt;dependency&gt; 
        &lt;groupId&gt;org.springframework&lt;/groupId&gt; 
        &lt;artifactId&gt;spring-test&lt;/artifactId&gt; 
        &lt;version&gt;${spring.version}&lt;/version&gt; 
        &lt;scope&gt;test&lt;/scope&gt; 
    &lt;/dependency&gt; 
    &lt;dependency&gt; 
        &lt;groupId&gt;junit&lt;/groupId&gt; 
        &lt;artifactId&gt;junit&lt;/artifactId&gt; 
        &lt;version&gt;4.12&lt;/version&gt; 
    &lt;/dependency&gt; 
     
    &lt;dependency&gt; 
        &lt;groupId&gt;org.springframework&lt;/groupId&gt; 
        &lt;artifactId&gt;spring-context&lt;/artifactId&gt; 
        &lt;version&gt;${spring.version}&lt;/version&gt; 
    &lt;/dependency&gt; 
    &lt;dependency&gt; 
        &lt;groupId&gt;org.springframework&lt;/groupId&gt; 
        &lt;artifactId&gt;spring-oxm&lt;/artifactId&gt; 
        &lt;version&gt;${spring.version}&lt;/version&gt; 
    &lt;/dependency&gt; 
    &lt;dependency&gt; 
        &lt;groupId&gt;org.springframework&lt;/groupId&gt; 
        &lt;artifactId&gt;spring-orm&lt;/artifactId&gt; 
        &lt;version&gt;${spring.version}&lt;/version&gt; 
    &lt;/dependency&gt; 
     
    &lt;!-- springdata --&gt; 
    &lt;dependency&gt; 
        &lt;groupId&gt;org.springframework.data&lt;/groupId&gt; 
        &lt;artifactId&gt;spring-data-jpa&lt;/artifactId&gt; 
        &lt;version&gt;1.11.1.RELEASE&lt;/version&gt; 
    &lt;/dependency&gt; 
    &lt;dependency&gt; 
        &lt;groupId&gt;org.springframework.data&lt;/groupId&gt; 
        &lt;artifactId&gt;spring-data-commons-core&lt;/artifactId&gt; 
        &lt;version&gt;1.4.1.RELEASE&lt;/version&gt; 
    &lt;/dependency&gt; 

</dependencies>
<build>
<finalName>SpringData_jpa</finalName>
</build>
</project>

pom.xml

2. Spring-config.xml

 

<?xml version="1.0" encoding="UTF-8"?> 
<beans xmlns="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" 
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" 
    xmlns:p="http://www.springframework.org/schema/p" xmlns:cache="http://www.springframework.org/schema/cache" 
    xmlns:jpa="http://www.springframework.org/schema/data/jpa" 
xsi:schemaLocation</span>="http://www.springframework.org/schema/beans    
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/beans/spring-beans-3.1.xsd   </span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/context   </span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/context/spring-context-3.1.xsd   </span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/aop   </span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/aop/spring-aop-3.1.xsd   </span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/tx    </span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/tx/spring-tx-3.1.xsd</span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/cache </span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/cache/spring-cache-3.1.xsd</span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/data/jpa</span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/data/jpa/spring-jpa.xsd</span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/data/repository</span> 
      http:<span style="color: #008000;">//</span><span style="color: #008000;">www.springframework.org/schema/data/repository/spring-repository-1.5.xsd"&gt;  </span> 
&lt;context:component-scan base-<span style="color: #0000ff;">package</span>="com.litblack.jpa"&gt;&lt;/context:component-scan&gt; 
                                       
&lt;!-- 配置Spring Data JPA扫描目录, repository 包 --&gt; 
&lt;jpa:repositories base-<span style="color: #0000ff;">package</span>="com.litblack.jpa" /&gt;  
 
&lt;!-- 定义实体管理器工厂 --&gt; 
&lt;bean id="entityManagerFactory" <span style="color: #0000ff;">class</span>="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt; 
    &lt;property name="dataSource" ref="dataSource" /&gt; 
    &lt;!-- 扫描pojo --&gt; 
    &lt;property name="packagesToScan" value="com.litblack.jpa" /&gt; 
    &lt;property name="persistenceProvider"&gt; 
        &lt;bean <span style="color: #0000ff;">class</span>="org.hibernate.ejb.HibernatePersistence" /&gt; 
    &lt;/property&gt; 
    &lt;property name="jpaVendorAdapter"&gt; 
        &lt;bean <span style="color: #0000ff;">class</span>="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"&gt; 
            &lt;property name="generateDdl" value="true" /&gt; 
               &lt;property name="database" value="MYSQL" /&gt; 
            &lt;!-- &lt;property name="database" value="ORACLE" /&gt;  --&gt; 
            &lt;!-- &lt;property name="databasePlatform" value="org.hibernate.dialect.MySQL5InnoDBDialect" /&gt; --&gt; 
            &lt;property name="showSql" value="true" /&gt; 
        &lt;/bean&gt; 
    &lt;/property&gt; 
    &lt;property name="jpaDialect"&gt; 
        &lt;bean <span style="color: #0000ff;">class</span>="org.springframework.orm.jpa.vendor.HibernateJpaDialect" /&gt; 
    &lt;/property&gt; 
    &lt;property name="jpaPropertyMap"&gt; 
        &lt;map&gt; 
            &lt;entry key="hibernate.generate_statistics" value="false" /&gt;&lt;!-- 关闭打印的日志 --&gt; 
            &lt;entry key="hibernate.query.substitutions" value="true 1, false 0" /&gt; 
            &lt;entry key="hibernate.default_batch_fetch_size" value="16" /&gt; 
            &lt;entry key="hibernate.max_fetch_depth" value="2" /&gt; 
            &lt;entry key="hibernate.bytecode.use_reflection_optimizer" value="true" /&gt; 
            &lt;entry key="hibernate.cache.use_second_level_cache" value="false" /&gt; 
            &lt;entry key="hibernate.cache.use_query_cache" value="false" /&gt; 
        &lt;/map&gt; 
    &lt;/property&gt; 
&lt;/bean&gt; 
 
&lt;!-- 数据源 --&gt; 
&lt;bean id="dataSource" <span style="color: #0000ff;">class</span>="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"&gt; 
    &lt;property name="driverClassName" value="com.mysql.jdbc.Driver" /&gt; 
    &lt;property name="url" value="jdbc:mysql://localhost:3306/cyd?useUnicode=true&amp;amp;characterEncoding=UTF-8" /&gt; 
    &lt;property name="username" value="root" /&gt; 
    &lt;property name="password" value="root" /&gt; 
&lt;/bean&gt; 
&lt;!-- 配置事务管理器 --&gt; 
&lt;bean id="transactionManager" <span style="color: #0000ff;">class</span>="org.springframework.orm.jpa.JpaTransactionManager"&gt; 
    &lt;property name="entityManagerFactory" ref="entityManagerFactory" /&gt; 
&lt;/bean&gt; 
 
&lt;!-- 启用 annotation事务 --&gt; 
&lt;tx:annotation-driven transaction-manager="transactionManager" /&gt; 

</beans>

Spring-config.xml

3. UserBean.java

 

package com.litblack.jpa; 

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;
import org.springframework.stereotype.Component;

@Table(name = "cyd_sys_user")
@Entity
public class UserBean {

@Id 
@GeneratedValue(generator </span>= "system_uuid"<span style="color: #000000;">) 
@GenericGenerator(name </span>= "system_uuid", strategy = "uuid"<span style="color: #000000;">) 
</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String id; 
@Column(name</span>="user_name"<span style="color: #000000;">) 
</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String userName; 
@Column(name</span>="create_time"<span style="color: #000000;">) 
</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Date createTime; 
 
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getId() { 
    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> id; 
} 
 
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setId(String id) { 
    </span><span style="color: #0000ff;">this</span>.id =<span style="color: #000000;"> id; 
} 
 
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getUserName() { 
    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> userName; 
} 
 
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setUserName(String userName) { 
    </span><span style="color: #0000ff;">this</span>.userName =<span style="color: #000000;"> userName; 
} 
 
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Date getCreateTime() { 
    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> createTime; 
} 
 
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setCreateTime(Date createTime) { 
    </span><span style="color: #0000ff;">this</span>.createTime =<span style="color: #000000;"> createTime; 
} 
 
@Override 
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() { 
    </span><span style="color: #0000ff;">return</span> "UserBean [id=" + id + ", userName=" + userName + ", createTime=" + createTime + "]"<span style="color: #000000;">; 
} 

}

POJO

4. UserBeanRepository.java

复制代码
package com.litblack.jpa; 

import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
/**

  • SQL,JPQL查询

  • 返回:list<POJO>,list<Stinrg>,list<Object>,Page<Object>

  • 不能返回MAP

  • @author chenyd

  • 2018年1月10日
    */
    public interface UserBeanRepository extends JpaRepository<UserBean, Long>, JpaSpecificationExecutor<UserBean>{

    UserBean findByUserName(String username);

    @Query("from UserBean where userName =?1")
    List
    <UserBean> find_Jpql_list_obj(String username);

    @Query("select userName from UserBean where userName =?1")
    List
    <String> find_Jpql_list_one(String username);

    @Query("select userName,createTime from UserBean where userName =?1")
    List
    <Object> find_Jpql_list_morefield(String username);

    @Query("select userName,createTime from UserBean ")
    List
    <Object> find_Jpql_list_pojo_morefield();

    /**

    • 若返回类型为POJO,必须是所有POJO的所有字段,不能只查询某个字段
      */
      @Query(value
      ="select * from cyd_sys_user",nativeQuery=true)
      List
      <UserBean> find_SQL_pojo();

    @Query(value="select user_name,name from cyd_sys_user,t_user",nativeQuery=true)
    List
    <Object> find_SQL_obj();

      @Query(value = "select new map(userName,createTime) from UserBean")
        List<Map<String,Object>> find_SQL_obj_map();

    /**

    • 分页需要 #pageable 标识
    • NativeJpaQuery
    • @param pageable
    • @return
      /
      @Query(value
      ="select user_name,name from cyd_sys_user,t_user /
      #pageable/ ",countQuery="select count() from cyd_sys_user,t_user",nativeQuery=true)
      Page
      <Object> find_SQL_obj(Pageable pageable);
      }
复制代码

5. BaseJunit4Test.java

复制代码
package com.litblack.jpa; 

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Order;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations
= { "classpath*:/spring-config.xml" })
public class BaseJunit4Test {

@Autowired 
</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> UserBeanRepository userRepository; 
 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 保存 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> save_obj() { 
    System.out.println(userRepository); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; 100; i++<span style="color: #000000;">) { 
        UserBean entity </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> UserBean(); 
        entity.setUserName(</span>"user_" +<span style="color: #000000;"> i); 
        entity.setCreateTime(</span><span style="color: #0000ff;">new</span><span style="color: #000000;"> Date()); 
        userRepository.save(entity); 
    } 
 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 查询-所有 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_ALL_obj(){ 
    List</span>&lt;UserBean&gt; list=<span style="color: #000000;">userRepository.findAll(); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        UserBean obj </span>=<span style="color: #000000;"> list.get(i); 
        System.out.println(obj.getCreateTime()); 
    } 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 查询-one-obj,自定义接口 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_one_obj(){ 
    UserBean obj </span>= userRepository.findByUserName("user_1"<span style="color: #000000;">); 
    System.out.println(obj.toString()); 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 根据JQPL查询,获取一个包含所有字段的OBJ 
 * 返回: 一个pojo 集合 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_jqpl_obj(){ 
    List</span>&lt;UserBean&gt; list=userRepository.find_Jpql_list_obj("user_2"<span style="color: #000000;">); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        UserBean obj </span>=<span style="color: #000000;"> list.get(i); 
        System.out.println(obj.toString()); 
    } 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 根据JQPL查询,获取一个字段, 
 * 返回:一个字段 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_jqpl_onestr(){ 
    List</span>&lt;String&gt; list=userRepository.find_Jpql_list_one("user_2"<span style="color: #000000;">); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        String obj </span>=<span style="color: #000000;"> list.get(i); 
        System.out.println(obj.toString()); 
    } 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 根据JQPL查询,一行数据,获取多个字段 
 * 返回:object 不是POJO,不是string[]。是Object[]  
 * 注意:每个数据类型与POJO类型一致 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_jqpl_morestr(){ 
    List</span>&lt;Object&gt; list=userRepository.find_Jpql_list_morefield("user_2"<span style="color: #000000;">); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        Object[] obj </span>=<span style="color: #000000;"> (Object[]) list.get(i); 
        String username</span>=(String) obj[0<span style="color: #000000;">]; 
        Date date</span>=(Date) obj[1<span style="color: #000000;">]; 
        System.out.println(username</span>+"\t"+<span style="color: #000000;">date); 
    } 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * JQPL  获取指定字段的多个POJO 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_jqpl_pojo_morestr(){ 
    List</span>&lt;Object&gt; list=<span style="color: #000000;">userRepository.find_Jpql_list_pojo_morefield(); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        Object[] obj </span>=<span style="color: #000000;"> (Object[]) list.get(i); 
        String username</span>=(String) obj[0<span style="color: #000000;">]; 
        Date date</span>=(Date) obj[1<span style="color: #000000;">]; 
        System.out.println(username</span>+"\t"+<span style="color: #000000;">date); 
    } 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * SQL  获取POJO的所有字段,必须是所有POJO的所有字段 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_sql_pojo_allstr(){ 
    List</span>&lt;UserBean&gt; list=<span style="color: #000000;">userRepository.find_SQL_pojo(); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        UserBean obj </span>=<span style="color: #000000;"> list.get(i); 
        System.out.println(obj.toString()); 
    } 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * SQL  获取多表查询的字段,可用于多表查询,组成临时表 
 * 注意:每个数据类型与POJO类型一致 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_sql_obj_morestr(){ 
    List</span>&lt;Object&gt; list=<span style="color: #000000;">userRepository.find_SQL_obj(); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        Object[] obj </span>=<span style="color: #000000;"> (Object[]) list.get(i); 
        String username</span>=(String) obj[0<span style="color: #000000;">]; 
        String name</span>=(String) obj[1<span style="color: #000000;">]; 
        System.out.println(username</span>+"\t"+<span style="color: #000000;">name); 
    } 
}<br>/**<br>&nbsp;&nbsp; &nbsp; * Hql 能返回list&lt;Map&gt;<br>&nbsp;&nbsp; &nbsp; */<br>&nbsp;&nbsp; &nbsp;@Test<br>&nbsp;&nbsp; &nbsp;public void get_sql_obj_morestr1(){<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;List&lt;Map&lt;String,Object&gt;&gt; list=userRepository.find_SQL_obj_map();<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;for (int i = 0; i &lt; list.size(); i++) {<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Map&lt;String,Object&gt; obj = list.get(i);<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;System.out.println(obj);<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;}<br>&nbsp;&nbsp; &nbsp;} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 分页查询,先排序后分页 
 * select user_name,name from cyd_sys_user,t_user order by user_name desc limit ?, ? 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_sql_obj_morestr_page(){ 
    Sort sort</span>=<span style="color: #0000ff;">new</span> Sort(Direction.DESC,"user_name"<span style="color: #000000;">); 
    Pageable page</span>=<span style="color: #0000ff;">new</span> PageRequest(2,10,sort);<span style="color: #008000;">//</span><span style="color: #008000;">第二页,每页10条</span> 
    Page&lt;Object&gt; p=<span style="color: #000000;">userRepository.find_SQL_obj(page); 
    List</span>&lt;Object&gt; list=<span style="color: #000000;">p.getContent(); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        Object[] obj </span>=<span style="color: #000000;"> (Object[]) list.get(i); 
        String username</span>=(String) obj[0<span style="color: #000000;">]; 
        String name</span>=(String) obj[1<span style="color: #000000;">]; 
        System.out.println(username</span>+"\t"+<span style="color: #000000;">name); 
    } 
} 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 多条件查询,分页,排序 
 </span><span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> get_sql_obj_morestr_(){ 
    Page</span>&lt;UserBean&gt; p=userRepository.findAll(<span style="color: #0000ff;">new</span> Specification&lt;UserBean&gt;<span style="color: #000000;">() { 
         
        </span><span style="color: #0000ff;">public</span> Predicate toPredicate(Root&lt;UserBean&gt; root, CriteriaQuery&lt;?&gt;<span style="color: #000000;"> query, CriteriaBuilder cb) { 
            Predicate stuNameLike </span>= cb.like(root.&lt;String&gt; get("userName"), "%user_%"<span style="color: #000000;">); 
            Order oderby </span>=cb.desc(root.&lt;String&gt; get("userName"<span style="color: #000000;">)); 
            </span><span style="color: #0000ff;">if</span>(<span style="color: #0000ff;">null</span> !=<span style="color: #000000;"> stuNameLike) query.where(stuNameLike); 
            query.orderBy(oderby); 
            </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> stuNameLike; 
        } 
    },</span><span style="color: #0000ff;">new</span> PageRequest(0, 20<span style="color: #000000;">)); 
    List</span>&lt;UserBean&gt; list=<span style="color: #000000;">p.getContent(); 
    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i &lt; list.size(); i++<span style="color: #000000;">) { 
        UserBean obj </span>=<span style="color: #000000;"> list.get(i); 
        System.out.println(obj.toString()); 
    } 
} 
 
</span><span style="color: #008000;">/**</span><span style="color: #008000;"> 
 * 通用单个实体查询 
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> T 
 * </span><span style="color: #808080;">@return</span> 
 <span style="color: #008000;">*/</span> 
<span style="color: #0000ff;">public</span> &lt;T&gt;<span style="color: #000000;"> Specification base_Specification(Class T){ 
    </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">new</span> Specification&lt;T&gt;<span style="color: #000000;">(){ 
        </span><span style="color: #0000ff;">public</span> Predicate toPredicate(Root&lt;T&gt; root, CriteriaQuery&lt;?&gt;<span style="color: #000000;"> query, CriteriaBuilder cb) { 
            Predicate predicate</span>=<span style="color: #000000;">cb.conjunction(); 
            List</span>&lt;Expression&lt;Boolean&gt;&gt; expression=<span style="color: #000000;">predicate.getExpressions(); 
            expression.add(cb.like(root.</span>&lt;String&gt; get(""), ""<span style="color: #000000;">)); 
            </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> predicate; 
        }}; 
} 

}

复制代码

参考链接

转载 spring-data-jpa 介绍 复杂查询,包括多表关联,分页,排序等 IT虾米网
 
原文地址:https://www.cnblogs.com/litblank/p/8267333.html

发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

Spring Data Jpa一对多单向映射知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。