项目中需要支持mysql及db2数据库,但是每种数据库有特定的语法,需要对sql做处理来适配各种数据库
解决思路:
(1)使用原生sql,不依赖数据库特性。缺点:可能导致sql语句发杂,执行效率低下
(2)对不同数据库定义不同的mapper文件。缺点:对不依赖数据库的sql语句重复定义
(3)使用mybatis的databaseId,适配多种数据库(本例中使用的方式)
1.依赖配置
<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.jb</groupId>
<artifactId>springIntegrationMybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
<springVersion>4.3.18.RELEASE</springVersion>
</properties>
<dependencies>
<!-- spring相关依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${springVersion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${springVersion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${springVersion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${springVersion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${springVersion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${springVersion}</version>
</dependency>
<!-- mybatis相关依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.3</version>
</dependency>
<!-- dbcp数据源 -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<!-- mysql数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.9</version>
</dependency>
<!-- <dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>-->
<!--<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>-->
<!-- 测试相关依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${springVersion}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<finalName>springIntegrationMybatis</finalName>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.0.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.7.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.20.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.0</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
2.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:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<!-- IoC配置 -->
<!-- 扫描类包,将标注Spring注解的类自动转化Bean,同时完成Bean的注入 -->
<context:component-scan base-package="com.jb.springIntegrationMybatis.service" />
<!-- 引入配置文件 两种方式-->
<!--<context:property-placeholder location="classpath:jdbc.properties"/>-->
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties" />
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</bean>
<!-- databaseIdProvider属性配置 -->
<bean id="vendorProperties"
class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="properties">
<props>
<prop key="DB2">db2</prop>
<prop key="MySQL">mysql</prop>
</props>
</property>
</bean>
<bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
<property name="properties" ref="vendorProperties" />
</bean>
<!-- spring和MyBatis完美整合,不需要mybatis的配置映射文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- 自动扫描mapping.xml文件,**表示迭代查找 -->
<property name="mapperLocations" value="classpath:mapper/*.xml" />
<property name="databaseIdProvider" ref="databaseIdProvider"/>
</bean>
<!-- DAO接口所在包名,Spring会自动查找其下的类 ,包下的类需要使用@MapperScan注解,否则容器注入会失败 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.jb.springIntegrationMybatis.dao" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<!-- 配置事务管理器 -->
<!--<tx:annotation-driven/>-->
<!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
3.jdbc.properties配置
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatislearn?characterEncoding=utf8
username=root
password=root
4.mapper文件配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jb.springIntegrationMybatis.dao.UserDao">
<select id="getUser" parameterType="int" resultType="com.jb.springIntegrationMybatis.pojo.User" databaseId="mysql">
select age, name from user where id=#{id}
</select>
<select id="getUser" parameterType="int" resultType="com.jb.springIntegrationMybatis.pojo.User" databaseId="db2">
select age, name from user where id=#{id}
</select>
</mapper>
5.java类
dao接口
package com.jb.springIntegrationMybatis.dao;
import com.jb.springIntegrationMybatis.pojo.User;
import org.mybatis.spring.annotation.MapperScan;
/**
* 这里的@MapperScan就是上面所讲的Mapper扫描器中所需要的配置,会自动生成代理对象。
* 注意,接口中的方法名称要和对应的MyBatis映射文件中的语句的id值一样,因为生成的
* 动态代理,会根据这个匹配相应的Sql语句执行。另外就是方法的参数和返回值也需要注
* 意。接口中的方法如何定义,对应的MyBatis映射文件就应该进行相应的定义。
* 最后,标注中的userDao是用来作为Spring的Bean的id(或name)进行使用的,方便我
* 们在Service层进行注入使用。
*/
@MapperScan
public interface UserDao {
//此处的方法名必须和mapper中的映射文件中的id同名
//mapper映射文件中通过com.jb.springIntegrationMybatis.dao.UserDao.getUser,即this.getClass().getName()+".getUser"
public User getUser(int id);
}
service接口
package com.jb.springIntegrationMybatis.service;
import com.jb.springIntegrationMybatis.pojo.User;
import org.springframework.stereotype.Service;
@Service
public interface IUserService {
User getUser(int id);
}
service实现
package com.jb.springIntegrationMybatis.service;
import com.jb.springIntegrationMybatis.dao.UserDao;
import com.jb.springIntegrationMybatis.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service("userService")
public class UserServiceImpl implements IUserService {
@Autowired
private UserDao userDao;
public User getUser(int id) {
return userDao.getUser(id);
}
}
junit测试
package com.jb.springIntegrationMybatis;
import com.jb.springIntegrationMybatis.pojo.User;
import com.jb.springIntegrationMybatis.service.IUserService;
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 org.springframework.transaction.annotation.Transactional;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:application.xml")
@Transactional
public class TestDatabseIdPrivoder {
@Autowired
private IUserService userService;
@Test
public void testGetUserListInfo()
{
User user = userService.getUser(1);
System.out.println(user.toString());
}
}
6.配置说明:
<bean id="vendorProperties"
class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="properties">
<props>
<prop key="Oracle">oracle</prop>
<prop key="MySQL">mysql</prop>
</props>
</property>
</bean>
<bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
<property name="properties" ref="vendorProperties" />
</bean>
<!-- spring和MyBatis完美整合,不需要mybatis的配置映射文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- 自动扫描mapping.xml文件,**表示迭代查找 -->
<property name="mapperLocations" value="classpath:mapper/*.xml" />
<property name="databaseIdProvider" ref="databaseIdProvider"/>
</bean>
自定义databaseIdProvider,覆盖SqlSessionFactoryBean中默认创建的,项目启动时会根据配置的数据源获取数据库类型
private String getDatabaseName(DataSource dataSource) throws SQLException {
String productName = this.getDatabaseProductName(dataSource);
if (this.properties != null) {
Iterator i$ = this.properties.entrySet().iterator();
Entry property;
do {
if (!i$.hasNext()) {
return null;
}
property = (Entry)i$.next();
} while(!productName.contains((String)property.getKey()));
return (String)property.getValue();
} else {
return productName;
}
}
sql执行时会找数据库类型与sql标签中配置的databaseId属性相同的sql语句