SpringBoot之Jpa 多数据源

王念博客

发布于 2019-07-24 10:34:56

1.2K0

发布于 2019-07-24 10:34:56

文章被收录于专栏：王念博客

前言:JPA全称Java Persistence API.JPA通过JDK 5.0注解或XML描述对象－关系表的映射关系，并将运行期的实体对象持久化到数据库中,在Spring 2.0.1中，正式提供对JPA的支持，这也促成了JPA的发展，要知道JPA的好处在于可以分离于容器运行，变得更加的简洁。之前上一家公司就是用的jpa，感觉很简单,特别是注解的实现完全解决了xml配置的繁琐，这个案例只是一个超级简单的demo，如果需要分页和一对多关联关系需要自己查阅一下其他资料，反正我是不推荐使用join 建立表关联关系。

1 .导入maven依赖

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
 <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <scope>runtime</scope>
 </dependency>

2.配置数据库连接属性

spring.datasource.url=jdbc:mysql:///test?useUnicode=true&characterEncoding=utf8&autoReconnect=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.jdbc.Driver
# Specify the DBMS
spring.jpa.database=MYSQL
# Show or not log for each sql query
spring.jpa.show-sql=true
# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto=update
# Naming strategy
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
# stripped before adding them to the entity manager)
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

以上配置就是hibernate的相关配置

3.创建实体类

@Entity
@Table(name = "good")
public class Good {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column
    private String goodName;
    @Column
    private Integer number;
}

@Table注解的name是数据库的表名

@Id 声明id为主键

@GeneratedValue 为主键生成的规则

@Column 设置该属性为字段可以用name指定名length指定长度

org.hibernate.cfg.ImprovedNamingStrategy 会自动映射属性和字段名(goodName ==> good_name) 一般没有特殊情况一致就行了。

4.创建Dao数据访问层

@Repository
public interface GoodDao extends JpaRepository<Good, Serializable> {
     Good findByGoodName(String goodName);
}

默认的常用方法都有了 save(保存更新) findAll delete findOne.....

如果需要find某个属性的话只需要findByGoodName 属性名首字母大写就可以了，需要实现这个接口

如果需要自定义sql的话，也是可以加@Query注解来自定义

@Query(value = "select  id from User where name=:name",nativeQuery = true)
public  Integer  getId(String name);

5.测试就可以了

Good good = goodDao.getOne(1);

6.分页加排序

        Sort sort = new Sort(Sort.Direction.ASC,"name");
        Pageable pageable = new PageRequest(0, 20,sort);
        Page<User> page = dao.findAll(pageable);
        List<User> users = page.getContent();
        System.out.println("一共多少条:" + page.getTotalElements());//一共多少条
        System.out.println("一共多少页:" + page.getTotalPages());//一共多少页
        System.out.println("开始位置:" + page.getNumber());//开始位置
        System.out.println("显示多少行" + page.getSize());//显示多少行
        System.out.println(page.getSort());

7.多数据源

7.1 配置两个数据库的连接用户名密码

datasource.primary.url=jdbc:mysql://12.12.12.12/test?useUnicode=true&characterEncoding=utf8&autoReconnect=true
datasource.primary.username=root
datasource.primary.password=root
datasource.primary.driverClassName=com.mysql.jdbc.Driver

datasource.secondary.url=jdbc:mysql://11.11.11.11/test_db?useUnicode=true&characterEncoding=utf8&autoReconnect=true
datasource.secondary.username=root
datasource.secondary.password=root
datasource.secondary.driverClassName=com.mysql.jdbc.Driver

7.2 配置两个DatasourceBean

@Configuration
public class DataSourceConfiguration {
    @Bean(name = "primaryDataSource")
    @Primary
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix = "datasource.primary")
    public DataSource primaryDataSource() {
        System.out.println("-------------------- primaryDataSource初始化 ---------------------");
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @ConfigurationProperties(prefix = "datasource.secondary")
    public DataSource secondaryDataSource() {
        System.out.println("-------------------- secondaryDataSource初始化---------------------");
        return DataSourceBuilder.create().build();
    }

7.3 分开配置 EntityManager以及JpaProperties

package com.wangnian.repositoryConfig;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPrimary",
                       transactionManagerRef = "transactionManagerPrimary",
                       basePackages = {"com.wangnian.model1"})//设置dao（repo）所在位置
public class RepositoryPrimaryConfig {
    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDS;

    @Bean(name = "entityManagerPrimary")
    @Primary
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactoryPrimary")
    @Primary
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDS)
                .properties(getVendorProperties(primaryDS))
                .packages("com.wangnian.model1") //设置实体类所在位置
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }

    @Bean(name = "transactionManagerPrimary")
    @Primary
    PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }

}

7.4 使用接口的方式或者使EntityManager测试

/**
 * Created by http://my.oschina.net/wangnian on 2016/10/26.
 */
@Repository
public interface Dao1 extends JpaRepository<User, Serializable> {
}

8.使用EntityManager 返回 MAP

query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

上面我设置了自动创建表 update，千万不要用create，否则会删点数据重建。

9.分页计算

第1页显示10行 limit 计算公式 (page-1)*count

10条数据显示10行有多少页计算公式 (total-1)/count+1

10.封装原生sql方法

package com.example;

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

/**
 * Created by wangnian on 2017/3/22.
 */
public interface BaseDao {
    /**
     * 增删改
     *
     * @param sql        自定义sql语句
     * @param parameters 参数<:key,value>
     * @return 执行条数
     */
    int executeUpdate(String sql, Map<String, Object> parameters);

    /**
     * 查询
     *
     * @param sql        自定义sql语句
     * @param parameters 参数<:key,value>
     * @param page       第几页
     * @param pageSize   显示多少行
     * @return 集合 Map<列名,值>
     */
    List<Map<String, Object>> getList(String sql, Map<String, Object> parameters, Integer page, Integer pageSize);


    /**
     * 查询sql的总条数
     *
     * @param sql        自定义sql语句
     * @param parameters 参数<:key,value>
     * @param page       第几页
     * @param pageSize   显示多少行
     * @return 总条数 total，当前第几页 pageSize，总共多少页 sumPage,显示多少行 count
     */
    Map<String, Object> getListTotal(String sql, Map<String, Object> parameters, Integer page, Integer pageSize);

}

package com.example;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by wangnian on 2017/3/22.
 */
@Repository
public class BaseDaoImpl implements BaseDao {

    private static final Logger LOGGER = LoggerFactory.getLogger(BaseDaoImpl.class);

    @Autowired(required = false)
    private EntityManager entityManager;

    @Override
    @Transactional
    public int executeUpdate(String sql, Map<String, Object> parameters) {
        long startTime = System.currentTimeMillis();
        Query query = entityManager.createNativeQuery(sql);
        for (Map.Entry<String, Object> entry : parameters.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        int updateCount = query.executeUpdate();
        LOGGER.info("ExecuteUpdate ExecuteSQL Time:{}ms UpdateCount:{} Sql:{}  {}", System.currentTimeMillis() - startTime, updateCount, sql, parameters);
        return updateCount;
    }

    @Override
    public List<Map<String, Object>> getList(String sql, Map<String, Object> parameters, Integer page, Integer pageSize) {
        long startTime = System.currentTimeMillis();
        Query query = entityManager.createNativeQuery(sql);
        for (Map.Entry<String, Object> entry : parameters.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Integer fistResult = (page - 1) * pageSize;
        query.setFirstResult(fistResult);
        query.setMaxResults(pageSize);
        List<Map<String, Object>> list = query.getResultList();
        LOGGER.info("GetList ExecuteSQL Time:{}ms ResultSize:{} Sql:{} limit {} {}  {} ", System.currentTimeMillis() - startTime, list.size(), sql, fistResult, pageSize, parameters);
        return list;
    }

    @Override
    public Map<String, Object> getListTotal(String sql, Map<String, Object> parameters, Integer page, Integer pageSize) {
        long startTime = System.currentTimeMillis();
        Query query = entityManager.createNativeQuery(sql);
        for (Map.Entry<String, Object> entry : parameters.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        BigInteger bigInteger = (BigInteger) query.getSingleResult();
        Integer total = bigInteger.intValue();
        int sumPage = (total + pageSize - 1) / pageSize;
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("total", total);
        resultMap.put("page", page);
        resultMap.put("pageSize", pageSize);
        resultMap.put("sumPage", sumPage);
        LOGGER.info("GetListTotal ExecuteSQL Time:{}ms  Total:{}  Sql:{}  {}", System.currentTimeMillis() - startTime, total, sql, parameters);
        return resultMap;
    }


}

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

数据库