springmvc jpa 多数据源

本次使用Mysql 和 sqlServer

 

一 项目结构

 

二 properties文件

env.properties(可以不要)

protocol=http

jdbc.properties

# mysql
jdbc.first.driver = com.mysql.cj.jdbc.Driver
jdbc.first.jdbcUrl=jdbc:mysql://localhost:3306/test1
jdbc.first.user=root
jdbc.first.password=root
 
# sqlserver
jdbc.second.driver= com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.second.jdbcUrl= jdbc:sqlserver://localhost;DatabaseName=test2
jdbc.second.user= root
jdbc.second.password= root
 
jdbc.initialPoolSize=3
jdbc.miniPoolSize=3
jdbc.maxPoolSize=20
jdbc.maxIdleTime=20
 
#hibernate config
hibernate.dialect = org.hibernate.dialect.MySQLDialect
hibernate.show_sql = false
hibernate.format_sql = true
#hibernate.hbm2ddl.auto =update
hibernate.hbm2ddl.auto =none
hibernate.cache.use_second_level_cache=false
hibernate.cache.use_query_cache=false
hibernate.cache.provider_class=net.sf.ehcache.hibernate.EhCacheProvider
hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory

 

三 配置文件

pom（注意mysql版本，如果和本机不匹配，那么会报错）

<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>
    <parent>
        <groupId>com.kintech</groupId>
        <artifactId>kintech.parent</artifactId>
        <version>0.0.1-SNAPSHOT</version>
    </parent>
    <artifactId>kintech.test1</artifactId>
    <packaging>war</packaging>
    <name>kintech.test1 Maven Webapp</name>
    <url>http://maven.apache.org</url>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
        </dependency>
        <!-- hibernate -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
        </dependency>
        <!-- hibernate -->
 
        <!--  JPA  -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-ehcache</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
        </dependency>
        <!--  JPA  -->
 
        <!--    Mysql    -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <!--    Mysql    -->
 
        <!--    SQL server    -->
        <dependency>
            <groupId>com.microsoft</groupId>
            <artifactId>microsoft-sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
        <!--    SQL server    -->
 
        <!-- dataSource pool -->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
        </dependency>
        <!-- dataSource pool -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
 
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>
    <build>
        <finalName>kintech.test1</finalName>
    </build>
</project>

 

application.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:mvc="http://www.springframework.org/schema/mvc" 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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
                           http://www.springframework.org/schema/context 
                           http://www.springframework.org/schema/context/spring-context-4.3.xsd  
                           http://www.springframework.org/schema/mvc 
                           http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd
                           http://www.springframework.org/schema/aop 
                           http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
                           http://www.springframework.org/schema/tx 
                           http://www.springframework.org/schema/tx/spring-tx-4.3.xsd http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd"
       default-lazy-init="true">
 
    <!-- 引入属性文件 -->
    <bean
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:properties/env.properties</value>
                <value>classpath:properties/jdbc.properties</value>
            </list>
        </property>
    </bean>
 
    <!-- <context:annotation-config /> -->
 
      <!-- 开启自动扫描包 -->
   <context:component-scan base-package="com.kintech" use-default-filters="true" annotation-config="true">
      <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>  
      <context:exclude-filter type="annotation" expression="org.springframework.web.bind.annotation.RestController"/> 
   </context:component-scan>
 
    <import resource="spring-business.xml"/>
 
</beans>

 

business.xml（注意扫描路径 packagesToScan   jpa:repositories base-package）

<?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:mvc="http://www.springframework.org/schema/mvc"
       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:jpa="http://www.springframework.org/schema/data/jpa"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                           http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
                           http://www.springframework.org/schema/context 
                           http://www.springframework.org/schema/context/spring-context-4.3.xsd  
                           http://www.springframework.org/schema/mvc 
                           http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd
                           http://www.springframework.org/schema/aop 
                           http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
                           http://www.springframework.org/schema/tx 
                           http://www.springframework.org/schema/tx/spring-tx-4.3.xsd http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd"
       default-lazy-init="true">
 
    <!--mysql 配置数据源-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="${jdbc.first.driver}" />  <!--数据库连接驱动-->
        <property name="jdbcUrl" value="${jdbc.first.jdbcUrl}" />     <!--数据库地址-->
        <property name="user" value="${jdbc.first.user}" />   <!--用户名-->
        <property name="password" value="${jdbc.first.password}" />   <!--密码-->
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}" />      <!--最大连接数-->
        <property name="minPoolSize" value="${jdbc.miniPoolSize}" />       <!--最小连接数-->
        <property name="initialPoolSize" value="${jdbc.initialPoolSize}" />      <!--初始化连接池内的数据库连接-->
        <property name="maxIdleTime" value="${jdbc.maxIdleTime}" />  <!--最大空闲时间-->
    </bean>
 
    <!--  hibernate  -->
    <!--配置session工厂-->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="packagesToScan" value="com.kintech.test1.model" />
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop> <!--hibernate根据实体自动生成数据库表-->
                <prop key="hibernate.dialect">${hibernate.dialect}</prop>   <!--指定数据库方言-->
                <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>     <!--在控制台显示执行的数据库操作语句-->
                <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>     <!--在控制台显示执行的数据哭操作语句（格式）-->
                <prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop>
                <prop key="hibernate.cache.use_query_cache">${hibernate.cache.use_query_cache}</prop>  <!-- 查询缓存 -->
                <prop key="hibernate.cache.provider_class">${hibernate.cache.provider_class}</prop>
                <prop key="hibernate.cache.region.factory_class">${hibernate.cache.region.factory_class}</prop>
                <prop key="hibernate.allow_update_outside_transaction">true</prop>
            </props>
        </property>
    </bean>
 
    <!--  JPA mysql  -->
    <!-- JPA实体管理器工厂 -->
    <bean id="entityManagerFactory" name="jpaEntityManagerFactory"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="jpaVendorAdapter" ref="hibernateJpaVendorAdapter" />
        <!-- 加入定制化包路径 -->
        <property name="packagesToScan" value="com.kintech.test1.model.first" />
 
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.current_session_context_class">thread</prop>
                <prop key="hibernate.hbm2ddl.auto">none</prop><!-- validate/update/create -->
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.format_sql">true</prop>
 
                <!-- 建表的命名规则 -->
                <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
 
            </props>
        </property>
    </bean>
 
    <!-- 设置JPA实现厂商的特定属性 -->
    <bean id="hibernateJpaVendorAdapter"
          class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="databasePlatform" value="${hibernate.dialect}"/>
    </bean>
 
    <!-- Jpa 事务配置 -->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
    </bean>
 
    <!-- Spring Data Jpa配置 -->
    <jpa:repositories base-package="com.kintech.test1.dao.first"  transaction-manager-ref="transactionManager" entity-manager-factory-ref="entityManagerFactory"/>
 
    <!-- 使用annotation定义事务 -->
    <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />
    <!--  JPA mysql  -->
 
 
    <!-- JPA sqlserver -->
    <bean id="sqlserverDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="${jdbc.second.driver}" />  <!--数据库连接驱动-->
        <property name="jdbcUrl" value="${jdbc.second.jdbcUrl}" />     <!--数据库地址-->
        <property name="user" value="${jdbc.second.user}" />   <!--用户名-->
        <property name="password" value="${jdbc.second.password}" />   <!--密码-->
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}" />      <!--最大连接数-->
        <property name="minPoolSize" value="${jdbc.miniPoolSize}" />       <!--最小连接数-->
        <property name="initialPoolSize" value="${jdbc.initialPoolSize}" />      <!--初始化连接池内的数据库连接-->
        <property name="maxIdleTime" value="${jdbc.maxIdleTime}" />  <!--最大空闲时间-->
    </bean>
 
    <!-- 整合sqlserverjpa -->
    <bean id="sqlserverEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="sqlserverDataSource"></property>
        <property name="packagesToScan" value="com.kintech.test1.model.second"></property>
        <property name="persistenceUnitName" value="sqlserverdb"></property>
        <property name="jpaVendorAdapter" ref="hibernateJpaVendorAdapter2" />
        <property name="jpaProperties">
            <props>
                <!--设置外连接抓取树的最大深度 -->
                <prop key="hibernate.max_fetch_depth">3</prop>
                <prop key="hibernate.jdbc.fetch_size">18</prop>
                <prop key="hibernate.jdbc.batch_size">10</prop>
                <!-- 自动建表类型 validate|create|create-drop|update -->
                <!-- <prop key="hibernate.hbm2ddl.auto">validate</prop> -->
                <!-- 是否显示SQL -->
                <prop key="hibernate.show_sql">false</prop>
                <!-- 显示SQL是否格式化 -->
                <prop key="hibernate.format_sql">false</prop>
                <!-- 关闭二级缓存 -->
                <prop key="hibernate.cache.provider_class">org.hibernate.cache.NoCacheProvider</prop>
                <!-- 关闭实体字段映射校验 -->
                <prop key="javax.persistence.validation.mode">none</prop>
            </props>
        </property>
    </bean>
    <!-- 设置JPA实现厂商的特定属性 -->
    <bean id="hibernateJpaVendorAdapter2"
          class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="databasePlatform" value="org.hibernate.dialect.SQLServerDialect"/>
    </bean>
    <bean id="sqlservertransactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="sqlserverEntityManagerFactory" />
        <qualifier value="sqlserverEM"/>
    </bean>
    <jpa:repositories base-package="com.kintech.test1.dao.second"  transaction-manager-ref="sqlservertransactionManager" entity-manager-factory-ref="sqlserverEntityManagerFactory"/>
    <tx:annotation-driven transaction-manager="sqlservertransactionManager" proxy-target-class="false"/>
    <!-- JPA sqlserver -->
 
</beans>

 

springmvc-servlet.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:mvc="http://www.springframework.org/schema/mvc"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:p="http://www.springframework.org/schema/p"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                           http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
                           http://www.springframework.org/schema/context 
                           http://www.springframework.org/schema/context/spring-context-4.3.xsd  
                           http://www.springframework.org/schema/mvc 
                           http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd
                           http://www.springframework.org/schema/aop 
                           http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
                           http://www.springframework.org/schema/tx 
                           http://www.springframework.org/schema/tx/spring-tx-4.3.xsd"
                           default-lazy-init="true" >
    
    
    <!-- 默认的注解映射的支持 -->
    <mvc:annotation-driven />
    <!--静态资源映射  -->
    <mvc:default-servlet-handler />
 
    <!-- 扫描Controller -->
    <context:component-scan base-package="com.kintech" use-default-filters="false">
        <context:include-filter type="annotation" expression="org.springframework.stereotype.Controller" />
        <context:include-filter type="annotation" expression="org.springframework.web.bind.annotation.RestController" />
    </context:component-scan>
    
    
     <!-- 引入属性文件 -->                    
    <bean
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:properties/env.properties</value>
            </list>
        </property>
    </bean>
</beans>

 

四 创建实体类和Dao

搞完上面的，基本就差不多了。这里就准备测试了。

（mysql和sqlserver的Model,service,dao都分包创建）

Model创建

model.first

package com.kintech.test1.model.first;
 
 
import com.fasterxml.jackson.annotation.JsonProperty;
import javax.persistence.*;
 
 
@Entity
@Table(name = "user",catalog = "test1")
public class User implements java.io.Serializable  {
    private static final long serialVersionUID = 6693634101527860224L;
 
    @JsonProperty("id")
    private Integer id;
 
    @JsonProperty("name")
    private String name;
 
    @JsonProperty("schoolId")
    private Integer schoolId;
 
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Integer getId() {
        return this.id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
 
    public String getName() {
        return this.name;
    }
    public void setName(String name) {
        this.name = name;
    }
 
    public Integer getSchoolId() {
        return this.schoolId;
    }
    public void setSchoolId(Integer schoolId) {
        this.schoolId = schoolId;
    }
 
 
 
}

 

model.second

package com.kintech.test1.model.second;
 
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
 
 
@Entity
@Table(name="company"
        ,catalog="logisoft.dbo"
)
@Data
@AllArgsConstructor
@NoArgsConstructor
public class HK_GicaCompany implements java.io.Serializable {
    private String logi_company_id;
    @Id
    private String company_code;
    private String key_code;
    private String short_name;
    private String long_name;
 
}

 

Dao创建

增删改，记得加上@Transactional  （dao还需要@Modifying）

dao.first

package com.kintech.test1.dao.first;
 
import com.kintech.test1.model.first.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
 
@Repository
public interface UserTestDao extends JpaRepository<User,Integer> {
}

 

dao.second

package com.kintech.test1.dao.second;
 
import com.kintech.test1.model.second.HK_GicaCompany;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
 
@Repository
public interface HK_GicaCompanyDao extends JpaRepository<HK_GicaCompany,String> {
 
    @Transactional(readOnly = true)
    @Query(value = "select top 10 * from company where status='Y' "
            ,nativeQuery = true)
    List<HK_GicaCompany> get10();
}

 

五 测试

 
import com.kintech.test1.dao.first.UserTestDao;
import com.kintech.test1.dao.second.HK_GicaCompanyDao;
import com.kintech.test1.model.first.User;
import com.kintech.test1.model.second.HK_GicaCompany;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
 
import java.util.List;
 
 
/**
 * @author Tyler
 * @date 2022/7/7
 */
 
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring/applicationContext.xml"})
public class TylerTest {
    @Autowired
    UserTestDao userDao;
    @Autowired
    HK_GicaCompanyDao hk_gicaCompanyDao;
 
    @Test
    public void test1() throws Exception {
        List<User> userList= userDao.findAll();
        System.out.println("mysql user表 : "+ userList.size());
        List<HK_GicaCompany> list = hk_gicaCompanyDao.get10();
        System.out.println("sqlserver company表 : "+ list.size());
    }
}

 

结果：