spring data jpa .基本使用,多条件复合查询,报表查询。

原创 2016年08月30日 11:07:31

</pre></h1><div>本文较为简单的介绍在实际代码中如何编码。<a target=_blank target="_blank" href="http://docs.spring.io/spring-data/jpa/docs/1.9.0.RELEASE/reference/html/">点击进入官网</a></div><div></div><div>第一次接触 spring data jpa 就被它吸引。非常强大的数据库操作包。</div><div></div><div>使用起来也十分简单。</div><div>第一步,实体类与表结构的对应。</div><div>实体类与数据库的关系。</div><div>有两种方法,</div><div>1,配置数据库地址,自动通过配置的实体包下的类创建对应的数据库表结构和表关系。</div><div>2,在数据库中创建表结构,表关系。通过数据库生成对应的实体类。</div><div><pre code_snippet_id="1857449" snippet_file_name="blog_20160830_2_8905298" name="code" class="plain">#mssql
db.driver=net.sourceforge.jtds.jdbc.Driver

db.url=jdbc:jtds:sqlserver://localhost:1433/dbName
db.username=
db.password=

#mysql
#db.driver=com.mysql.jdbc.Driver
#hibernate.dialect=org.hibernate.dialect.MySQLDialect
#db.url=jdbc\:mysql\://locahost\:3306/dbName?useUnicode\=true&characterEncoding\=utf-8
db.username=
db.password=

#sybase
db.driver=com.sybase.jdbc3.jdbc.SybDriver
hibernate.dialect=org.hibernate.dialect.SybaseASE15Dialect

db.url=jdbc:sybase:Tds:localhost:5000/dbName?language=us_english&charset=cp936
db.username=
db.password=


#######其他配置
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create-drop
 
#Hibernate Configuration
# dialect for sybase ase

hibernate.hbm2ddl.auto=update
#hibernate.hbm2ddl.auto=create-drop
#hibernate.ejb.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy
hibernate.show_sql=true
hibernate.format_sql=true
current_session_context_class=thread
entitymanager.packages.to.scan=pack.entity

读取配置文件
@Configuration
@EnableWebMvc
@EnableTransactionManagement
//@ComponentScan("com.spr")
@PropertySource("classpath:db.properties")
@EnableJpaRepositories("pack.repository")
public class JpaConfig {

    private static final String PROPERTY_NAME_DATABASE_DRIVER = "db.driver";
    private static final String PROPERTY_NAME_DATABASE_PASSWORD = "db.password";
    private static final String PROPERTY_NAME_DATABASE_URL = "db.url";
    private static final String PROPERTY_NAME_DATABASE_USERNAME = "db.username";

    private static final String PROPERTY_NAME_HIBERNATE_DIALECT = "hibernate.dialect";
    private static final String PROPERTY_NAME_HIBERNATE_SHOW_SQL = "hibernate.show_sql";
    private static final String PROPERTY_NAME_ENTITYMANAGER_PACKAGES_TO_SCAN = "entitymanager.packages.to.scan";
    private static final String PROPERTY_NAME_HIBERNATE_HBM2DDL_AUTO = "hibernate.hbm2ddl.auto";

    @Resource
    private Environment env;

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName(env.getRequiredProperty(PROPERTY_NAME_DATABASE_DRIVER));
        dataSource.setUrl(env.getRequiredProperty(PROPERTY_NAME_DATABASE_URL));
        dataSource.setUsername(env.getRequiredProperty(PROPERTY_NAME_DATABASE_USERNAME));
        dataSource.setPassword(env.getRequiredProperty(PROPERTY_NAME_DATABASE_PASSWORD));

        return dataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource());
        entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistence.class);
        entityManagerFactoryBean.setPackagesToScan(
                env.getRequiredProperty(PROPERTY_NAME_ENTITYMANAGER_PACKAGES_TO_SCAN));

        entityManagerFactoryBean.setJpaProperties(hibProperties());

        return entityManagerFactoryBean;
    }

//    private Properties hibProperties() {
//        Properties properties = new Properties();
//        properties.put(PROPERTY_NAME_HIBERNATE_DIALECT, env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_DIALECT));
//        properties.put(PROPERTY_NAME_HIBERNATE_SHOW_SQL, env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_SHOW_SQL));
//        return properties;
//    }
    private Properties hibProperties() {
        Properties properties = new Properties();
        properties.put(PROPERTY_NAME_HIBERNATE_DIALECT,
                env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_DIALECT));
        properties.put(PROPERTY_NAME_HIBERNATE_SHOW_SQL,
                env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_SHOW_SQL));
        properties.put("hibernate.temp.use_jdbc_metadata_defaults", "false");
        properties.put(PROPERTY_NAME_HIBERNATE_HBM2DDL_AUTO,
                env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_HBM2DDL_AUTO));
        return properties;
    }

    @Bean
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory().getObject());
        return transactionManager;
    }

//    @Bean
//    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) throws SQLException {
//
//        JpaTransactionManager txManager = new JpaTransactionManager();
//        txManager.setEntityManagerFactory(entityManagerFactory);
//        return txManager;
//    }

}

Repository 接口
public interface CreditApplyRecordRepository extends PagingAndSortingRepository<CreditApplyRecord, Integer>, JpaSpecificationExecutor<CreditApplyRecord> {

    Set<CreditApplyRecord> findByIdIn(Set<Integer> ids);

}

关于   Repository 在官网查看相关说明,在Repository中定义方法命名规则
 Supported keywords inside method names
Keyword Sample JPQL snippet

And

findByLastnameAndFirstname

… where x.lastname = ?1 and x.firstname = ?2

Or

findByLastnameOrFirstname

… where x.lastname = ?1 or x.firstname = ?2

Is,Equals

findByFirstname,findByFirstnameIs,findByFirstnameEquals

… where x.firstname = 1?

Between

findByStartDateBetween

… where x.startDate between 1? and ?2

LessThan

findByAgeLessThan

… where x.age < ?1

LessThanEqual

findByAgeLessThanEqual

… where x.age ⇐ ?1

GreaterThan

findByAgeGreaterThan

… where x.age > ?1

GreaterThanEqual

findByAgeGreaterThanEqual

… where x.age >= ?1

After

findByStartDateAfter

… where x.startDate > ?1

Before

findByStartDateBefore

… where x.startDate < ?1

IsNull

findByAgeIsNull

… where x.age is null

IsNotNull,NotNull

findByAge(Is)NotNull

… where x.age not null

Like

findByFirstnameLike

… where x.firstname like ?1

NotLike

findByFirstnameNotLike

… where x.firstname not like ?1

StartingWith

findByFirstnameStartingWith

… where x.firstname like ?1 (parameter bound with appended %)

EndingWith

findByFirstnameEndingWith

… where x.firstname like ?1 (parameter bound with prepended %)

Containing

findByFirstnameContaining

… where x.firstname like ?1 (parameter bound wrapped in %)

OrderBy

findByAgeOrderByLastnameDesc

… where x.age = ?1 order by x.lastname desc

Not

findByLastnameNot

… where x.lastname <> ?1

In

findByAgeIn(Collection<Age> ages)

… where x.age in ?1

NotIn

findByAgeNotIn(Collection<Age> age)

… where x.age not in ?1

True

findByActiveTrue()

… where x.active = true

False

findByActiveFalse()

… where x.active = false

IgnoreCase

findByFirstnameIgnoreCase

… where UPPER(x.firstame) = UPPER(?1)


完成以上步骤就可以在业务中使用 数据库的增删改查。

下面来说如果完成 不定条件符合查询。
上面示例中 CreditApplyRecordRepository 继承  JpaSpecificationExecutor。改接口
JpaSpecificationExecutor介绍

关于  JpaSpecificationExecutor 的使用
想初始化一个  predicate 。类似1==1的。没有找到就在下面

Page<CreditCard> creditCard = creditCardRepo.findAll(new Specification<CreditCard>() {
            @Override
            public Predicate toPredicate(Root<CreditCard> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
                Predicate predicate = null;
//                if (null != appDateStart && null != appDateEnd) {
//                    predicate = cb.between(root.<Date>get("appdate"), appDateStart, appDateEnd);
//                }
                Set<String> orgNums = new HashSet<String>();
                orgNums.add(orgNum);
                if (null != orgNum) {
                    if (null != predicate) {
                        Expression<String> exOrgNum = root.<String>get("organNum");
                        predicate = cb.and(predicate, exOrgNum.in(orgNums));
                    } else {
                        Path<String> exOrgNum = root.<String>get("organNum");
                        predicate = exOrgNum.in(orgNums);
                    }
                }
                if (null != brand&&!("".equals(brand))) {
                    if (null != predicate) {
                        predicate = cb.and(predicate, cb.equal(root.<String>get("brand"), brand));
                    } else {
                        predicate = cb.equal(root.<String>get("brand"), brand);
                    }
                }
                if (null != currency&&!("".equals(currency))) {
                    if (null != predicate) {
                        predicate = cb.and(predicate, cb.equal(root.<String>get("currency"), currency));
                    } else {
                        predicate = cb.equal(root.<String>get("currency"), currency);
                    }
                }
                if (null != grade&&!("".equals(grade))) {
                    if (null != predicate) {
                        predicate = cb.and(predicate, cb.equal(root.<String>get("grade"), grade));
                    } else {
                        predicate = cb.equal(root.<String>get("grade"), grade);
                    }
                }
                if (null != media&&!("".equals(media))) {
                    if (null != predicate) {
                        predicate = cb.and(predicate, cb.equal(root.<String>get("media"), media));
                    } else {
                        predicate = cb.equal(root.<String>get("media"), media);
                    }
                }
                if (null != cardType&&!("".equals(cardType))) {
                    if (null != predicate) {
                        predicate = cb.and(predicate, cb.equal(root.<String>get("cardType"), cardType));
                    } else {
                        predicate = cb.equal(root.<String>get("cardType"), cardType);
                    }
                }
                if (null != predicate) {
                    cq.where(predicate);
                }
                return null;
            }
        },
                new PageRequest(pageIndex, pageSize)
        );

关于统计报表类的

 
@PersistenceContext
    EntityManager entityManager;  

String sql = String.format("sql_");

        Query query = entityManager.createNativeQuery(sql);
        List objecArraytList = query.getResultList();








版权声明:本文为博主原创文章,未经博主允许不得转载。

Spring Data JPA 复杂/多条件组合查询

1: 编写DAO类或接口  dao类/接口 需继承 public interface JpaSpecificationExecutor        接口;        如果需要分页,还可继...

JPA聚合函数(适用于联合查询)

最近帮老同事解决一个问题,场景是这样的,查询条件比较多,也就是我们说的联合查询,比如下面的,时间可以选不同的,状态和来源也可以选不同,而且可选可不选 如果这个时候写sql,是不是要各种条件判断,各种...

【SQL】分组数据,过滤分组-group by , having

创建分组 - GROUP BY

Spring Data JPA进阶——Specifications和Querydsl

Spring Data JPA进阶——Specifications和Querydsl本篇介绍一下Spring Data JPA中能为数据访问程序的开发带来更多便利的特性,我们知道,Spring Dat...
  • RO_wsy
  • RO_wsy
  • 2016年05月08日 15:30
  • 9152

SpringDataJpa的Specification查询

Spring Data JPA支持JPA2.0的Criteria查询,相应的接口是JpaSpecificationExecutor。Criteria 查询:是一种类型安全和更面向对象的查询 。 这个...

spring data jpa Specification的使用

看了很多前言不搭后语的教程,终于找到一个靠谱的 在业务中查询是一件非常头疼的事,毕竟不可能只是对一张表的查询是吧? 其实在业务中往往会涉及到多张表的查询,以及查询时需要的各种条件。当然这不用担心,毕竟...

spring data jpa 利用JpaSpecificationExecutor做复杂查询

spring data jpa 通过创建方法名来做查询,只能做简单的查询,那如果我们要做复杂一些的查询呢,多条件分页怎么办,这里,spring data jpa为我们提供了JpaSpecificati...

spring data jpa 利用@Query进行查询

介绍@Query注释之前,先看看怎么利用@NamedQuery进行命名查询1.现在实体类上定义方法已经具体查询语句@Entity @NamedQuery(name = "Task.findByTask...

SpringDataJpa的Specification查询

Spring Data JPA支持JPA2.0的Criteria查询,相应的接口是JpaSpecificationExecutor。Criteria 查询:是一种类型安全和更面向对象的查询 。这个接口...

spring-data-jpa 多条件查询 学习记录

spring-data-jpa 是对数据库访问的简化的封装,可以帮助我们更加方便的实现对数据库的各种操作。Spring Data JPA 规范方法的名字,根据符合规范的名字来确定方法需要实现什么样的逻...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:spring data jpa .基本使用,多条件复合查询,报表查询。
举报原因:
原因补充:

(最多只允许输入30个字)