本次使用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());
}
}
结果: