【第22期】观点:IT 行业加班,到底有没有价值?

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 的使用
想初始化一个  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();








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

相关文章推荐

jpa多条件查询方法

@Override public List myEditProductList(ProductEntity product, Page pageItems) { //String sql ...

spring data jpa 查询时的统计

public List listByProvinceId(int provinceId) { EntityManager em = entityManagerFactory.creat...
  • wyljz
  • wyljz
  • 2016-09-28 15:01
  • 2374

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

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

SpringJPA 直接实现count(*)

刚开始使用JPA时,基本都依赖@query(SQL)注解通过原生sql来实现根据编号统计条数: 方法一:@Query(" select count(t) from FollowerInfo t wh...

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

spring-data-jpa 是对数据库访问的简化的封装,可以帮助我们更加方便的实现对数据库的各种操作。Spring Data JPA 规范方法的名字,根据符合规范的名字来确定方法需要实现什么样的逻...

jpa Query查询,时间查询,in查询(Spring Data Jpa 3)

记录下原来用的jpa Query查询; 解决问题:使用Query从entry查询数据填充到DTO;先看entry:package com.test.entity;@Entity @Data publi...

一步一步学SpringDataJpa——JpaRepository查询功能

1.JpaRepository支持接口规范方法名查询。意思是如果在接口中定义的查询方法符合它的命名规则,就可以不用写实现,目前支持的关键字如下。 Keyword Sample J...

JPA学习笔记---JPA数据的操作:增加,删除,修改,获取,使用JPQL进行查询

JPA学习笔记---JPA数据的操作:增加,删除,修改,获取,使用JPOL进行查询 2013-02-04 JPA数据的操作:增加,删除,修改,获取,使用JPOL进行查询 CRUD操作  pa...

JPA,HQL多对多的查询语句

一个老师教许多学生,一个学生被许多老师教,一个学生有好多书,同一种书被许多同学拥有. 要查询教拥有书"a"的学生的老师! Hql语句: SELECT t FROM Teacher t join...

转载 spring-data-jpa 介绍 复杂查询,包括多表关联,分页,排序等

本篇进行Spring-data-jpa的介绍,几乎涵盖该框架的所有方面,在日常的开发当中,基本上能满足所有需求。这里不讲解JPA和Spring-data-jpa单独使用,所有的内容都是在和Spring...
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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