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

</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();