使用：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><dependency> <groupId>javassist</groupId> <artifactId>javassist</artifactId> <version>3.12.1.GA</version> </dependency> <!-- Test --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>${spring.version}</version> <scope>test</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-oxm</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>${spring.version}</version> </dependency> <!-- springdata --> <dependency> <groupId>org.springframework.data</groupId> <artifactId>spring-data-jpa</artifactId> <version>1.11.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework.data</groupId> <artifactId>spring-data-commons-core</artifactId> <version>1.4.1.RELEASE</version> </dependency>
SpringData_jpa
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"> </span> <context:component-scan base-<span style="color: #0000ff;">package</span>="com.litblack.jpa"></context:component-scan> <!-- 配置Spring Data JPA扫描目录, repository 包 --> <jpa:repositories base-<span style="color: #0000ff;">package</span>="com.litblack.jpa" /> <!-- 定义实体管理器工厂 --> <bean id="entityManagerFactory" <span style="color: #0000ff;">class</span>="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="dataSource" ref="dataSource" /> <!-- 扫描pojo --> <property name="packagesToScan" value="com.litblack.jpa" /> <property name="persistenceProvider"> <bean <span style="color: #0000ff;">class</span>="org.hibernate.ejb.HibernatePersistence" /> </property> <property name="jpaVendorAdapter"> <bean <span style="color: #0000ff;">class</span>="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> <property name="generateDdl" value="true" /> <property name="database" value="MYSQL" /> <!-- <property name="database" value="ORACLE" /> --> <!-- <property name="databasePlatform" value="org.hibernate.dialect.MySQL5InnoDBDialect" /> --> <property name="showSql" value="true" /> </bean> </property> <property name="jpaDialect"> <bean <span style="color: #0000ff;">class</span>="org.springframework.orm.jpa.vendor.HibernateJpaDialect" /> </property> <property name="jpaPropertyMap"> <map> <entry key="hibernate.generate_statistics" value="false" /><!-- 关闭打印的日志 --> <entry key="hibernate.query.substitutions" value="true 1, false 0" /> <entry key="hibernate.default_batch_fetch_size" value="16" /> <entry key="hibernate.max_fetch_depth" value="2" /> <entry key="hibernate.bytecode.use_reflection_optimizer" value="true" /> <entry key="hibernate.cache.use_second_level_cache" value="false" /> <entry key="hibernate.cache.use_query_cache" value="false" /> </map> </property> </bean> <!-- 数据源 --> <bean id="dataSource" <span style="color: #0000ff;">class</span>="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/cyd?useUnicode=true&amp;characterEncoding=UTF-8" /> <property name="username" value="root" /> <property name="password" value="root" /> </bean> <!-- 配置事务管理器 --> <bean id="transactionManager" <span style="color: #0000ff;">class</span>="org.springframework.orm.jpa.JpaTransactionManager"> <property name="entityManagerFactory" ref="entityManagerFactory" /> </bean> <!-- 启用 annotation事务 --> <tx:annotation-driven transaction-manager="transactionManager" />
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;">; }}
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; /**@author chenyd
SQL，JPQL查询
返回：list
,list ,list 不能返回MAP
*/ public interface UserBeanRepository extends JpaRepository<UserBean, Long>, JpaSpecificationExecutor 2018年1月10日
{ UserBean findByUserName(String username);
@Query(
"from UserBean where userName =?1") Listfind_Jpql_list_obj(String username); @Query(
"select userName from UserBean where userName =?1") Listfind_Jpql_list_one(String username); @Query(
"select userName,createTime from UserBean where userName =?1") List
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 < 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><UserBean> list=<span style="color: #000000;">userRepository.findAll(); </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i < 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><UserBean> 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 < 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><String> 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 < 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><Object> 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 < 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><Object> 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 < 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><UserBean> 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 < 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><Object> 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 < 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> * Hql 能返回list<Map><br> */<br> @Test<br> public void get_sql_obj_morestr1(){<br> List<Map<String,Object>> list=userRepository.find_SQL_obj_map();<br> for (int i = 0; i < list.size(); i++) {<br> Map<String,Object> obj = list.get(i);<br> System.out.println(obj);<br> }<br> } </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<Object> p=<span style="color: #000000;">userRepository.find_SQL_obj(page); List</span><Object> list=<span style="color: #000000;">p.getContent(); </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i < 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><UserBean> p=userRepository.findAll(<span style="color: #0000ff;">new</span> Specification<UserBean><span style="color: #000000;">() { </span><span style="color: #0000ff;">public</span> Predicate toPredicate(Root<UserBean> root, CriteriaQuery<?><span style="color: #000000;"> query, CriteriaBuilder cb) { Predicate stuNameLike </span>= cb.like(root.<String> get("userName"), "%user_%"<span style="color: #000000;">); Order oderby </span>=cb.desc(root.<String> 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><UserBean> list=<span style="color: #000000;">p.getContent(); </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i < 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> <T><span style="color: #000000;"> Specification base_Specification(Class T){ </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">new</span> Specification<T><span style="color: #000000;">(){ </span><span style="color: #0000ff;">public</span> Predicate toPredicate(Root<T> root, CriteriaQuery<?><span style="color: #000000;"> query, CriteriaBuilder cb) { Predicate predicate</span>=<span style="color: #000000;">cb.conjunction(); List</span><Expression<Boolean>> expression=<span style="color: #000000;">predicate.getExpressions(); expression.add(cb.like(root.</span><String> get(""), ""<span style="color: #000000;">)); </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> predicate; }}; }}
