MyBatis-16MyBatis动态SQL之【支持多种数据库】
生活随笔
收集整理的這篇文章主要介紹了
MyBatis-16MyBatis动态SQL之【支持多种数据库】
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
- 概述
- 配置
- 1.MyBatis全局配置文件增加
- 2.映射文件中的標簽調(diào)整包含databaseId屬性
- 示例
- 增加個查詢當(dāng)前時間的接口
- 編寫映射文件
- 單元測試
- 注意事項
概述
MyBatis-15MyBatis動態(tài)SQL之【bind】 bind標簽并不能解決更換數(shù)據(jù)庫帶來的所有問題。 那么還有沒其他的方式來支持不同的數(shù)據(jù)庫呢 ？
答案是 使用if標簽以及由MyBatis提供的databaseIdProvider數(shù)據(jù)庫廠商標識配置
MyBatis可以根據(jù)不同的數(shù)據(jù)庫廠商執(zhí)行不同的SQL,這種多廠商的支持是基于映射語句中的databaseId屬性。 MyBatis會加載不帶databaseId屬性和帶有匹配當(dāng)前數(shù)據(jù)庫databaseId屬性的所有語句。 如果同時找到了帶有databaseId和不帶databaseId的相同語句，則不帶databaseId的將被舍棄。
配置
1.MyBatis全局配置文件增加
為了支持多廠商數(shù)據(jù)庫，需要在MyBatis全局配置文件中加入databaseIdProvider配置 <databaseIdProvider type="DB_VENDOR"/>
也可以通過實現(xiàn)接口 org.apache.ibatis.mapping.DatabaseIdProvider 并在 mybatis-config.xml 中注冊來構(gòu)建自己的 DatabaseIdProvider
DB_VENDOR會通過DatabaseMetaData#getDatabaseProductName()返回的字符串進行設(shè)置， 通常情況下這個字符串比較長而且相同產(chǎn)品的不同版本會返回不同的值，通常會設(shè)置屬性別名使其變短。
<!-- 多數(shù)據(jù)庫支持 --><databaseIdProvider type="DB_VENDOR"><property name ="SQL Server" value="sqlserver"/><property name ="DB2" value ="db2"/><property name ="Oracle" value ="oracle"/><property name ="MySQL" value ="mysql"/><property name ="PostgreSQL" value ="postgresql"/><property name ="Derby" value ="derby"/><property name ="HSQL" value ="hsqldb"/><property name ="H2" value ="h2"/></databaseIdProvider>上述列舉了常見的數(shù)據(jù)庫產(chǎn)品名稱，在有property配置時，databaseId將被設(shè)置為第一個能匹配數(shù)據(jù)庫產(chǎn)品名稱的屬性鍵對應(yīng)的值，如果沒有匹配則置為null .
DB_VENDOR的匹配策略為DatabaseMetaData#getDatabaseProductName()返回的字符串包含property中name部分的值即可匹配。
數(shù)據(jù)庫產(chǎn)品名一般由選擇的當(dāng)前數(shù)據(jù)庫的JDBC驅(qū)動所決定，只要找到對應(yīng)數(shù)據(jù)庫DatabaseMetaData的實現(xiàn)類，一般在getDatabaseProductName()方法中就可以直接找到該值。
任何情況下都可以通過調(diào)用DatabaseMetaData#getDatabaseProductName()來獲取具體的值
完整的mybatis-config.xml如下
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration><!-- 引入外部屬性文件 ,必須放在第一位--><properties resource="db.properties"/><settings><!-- 通過logImpl屬性指定使用LOG4J輸出日志,mybatis默認使用log4j作為輸出日志信息。 --><setting name="logImpl" value="LOG4J" /><!-- 通過配置這個屬性為true可以自動將下畫線方式命名的數(shù)據(jù)庫列映射到j(luò)ava對象駝峰式命名屬性中<setting name="mapUnderscoreToCamelCase" value="true"/>--></settings><!-- typeAliases元素下配置了一個包的別名,通常確定一個類的時候需要使用全限定名,比如 com.artisan.mybatis.simple.mapper.model.Country--><typeAliases><package name="com.artisan.mybatis.simple.model" /></typeAliases><!-- 和spring整合后 environments配置將廢除--><environments default="development"><environment id="development"><!-- 使用jdbc事務(wù)管理--><transactionManager type="JDBC"/><!-- 數(shù)據(jù)庫連接池--><dataSource type="UNPOOLED"><property name="driver" value="${jdbc.driver}" /><property name="url" value="${jdbc.url}" /><property name="username" value="${jdbc.username}" /><property name="password" value="${jdbc.password}" /></dataSource></environment></environments><!-- 多數(shù)據(jù)庫支持 --><databaseIdProvider type="DB_VENDOR" ><property name ="SQL Server" value="sqlserver"/><property name ="DB2" value ="db2"/><property name ="Oracle" value ="oracle"/><property name ="MySQL" value ="mysql"/><property name ="PostgreSQL" value ="postgresql"/><property name ="Derby" value ="derby"/><property name ="HSQL" value ="hsqldb"/><property name ="H2" value ="h2"/></databaseIdProvider><mappers><!-- 配置具體的mapper --><mapper resource="com/artisan/mybatis/simple/mapper/CountryMapper.xml" /><!-- 逐一配置，比較繁瑣，容易遺漏，接口方式不推薦<mapper resource="com/artisan/mybatis/xml/mapper/UserMapper.xml"/><mapper resource="com/artisan/mybatis/xml/mapper/UserRoleMapper.xml"/><mapper resource="com/artisan/mybatis/xml/mapper/RoleMapper.xml"/><mapper resource="com/artisan/mybatis/xml/mapper/PrivilegeMapper.xml"/><mapper resource="com/artisan/mybatis/xml/mapper/RolePrivilegeMapper.xml"/>--><!-- 推薦：通過包的方式配置，mybatis會先查找對應(yīng)包下的所有的接口 --><package name="com.artisan.mybatis.xml.mapper"/></mappers> </configuration>注意databaseIdProvider節(jié)點的位置。
2.映射文件中的標簽調(diào)整包含databaseId屬性
除了增加上述配置之外，映射文件也需要調(diào)整，關(guān)鍵在于下面幾個映射文件的標簽中含有的databaseId屬性
- select
- insert
- update
- delete
- selectKey
- sql
示例
舉個簡單的例子，查詢當(dāng)前時間
我們知道 mysql中的語句為
select now() from dualoracle中為
select sysdate from dual增加個查詢當(dāng)前時間的接口
結(jié)合mybatis全局配置文件中的 mappers-package節(jié)點，在com.artisan.mybatis.xml.mapper包中增加接口
MultiDBMapper.java
package com.artisan.mybatis.xml.mapper;public interface MultiDBMapper {String getSysTime();}編寫映射文件
MultiDBMapper.xml
<?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" > <!-- 當(dāng)Mapper接口和XML文件關(guān)聯(lián)的時候， namespace的值就需要配置成接口的全限定名稱 --> <mapper namespace="com.artisan.mybatis.xml.mapper.MultiDBMapper"><select id="getSysTime" resultType="String" databaseId="mysql">select now() from dual</select><select id="getSysTime" resultType="String" databaseId="oracle">select 'oralce-'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual </select></mapper>單元測試
package com.artisan.mybatis.xml.mapper;import java.io.InputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.util.Properties;import org.apache.ibatis.session.SqlSession; import org.junit.Test;public class MultiDBMapperTest extends BaseMapperTest {public String getDatabaseProductName() {String productName = null;try {String dbfile = "db.properties";InputStream in = ClassLoader.getSystemResourceAsStream(dbfile);Properties p = new Properties();p.load(in);Class.forName(p.getProperty("jdbc.driver"));String url = p.getProperty("jdbc.url");String user = p.getProperty("jdbc.username");String pass = p.getProperty("jdbc.password");Connection con = DriverManager.getConnection(url, user, pass);DatabaseMetaData dbmd = con.getMetaData();productName = dbmd.getDatabaseProductName();System.out.println("數(shù)據(jù)庫名稱是：" + productName);} catch (Exception e) {e.printStackTrace();}return productName;}@Testpublic void getSysTimeTest() {// 獲取數(shù)據(jù)庫名稱getDatabaseProductName();// 獲取SqlSessionSqlSession sqlSession = getSqlSession();// 獲取MultiDBMapperMultiDBMapper multiDBMapper = sqlSession.getMapper(MultiDBMapper.class);// 調(diào)用接口方法String sysTime = multiDBMapper.getSysTime();System.out.println("當(dāng)前時間:" + sysTime);sqlSession.close();}}日志
2018-04-27 16:05:57,730 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-27 16:05:57,730 INFO [main] (BaseMapperTest.java:29) - reader close successfully 數(shù)據(jù)庫名稱是：MySQL 2018-04-27 16:05:57,819 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: select now() from dual 2018-04-27 16:05:57,929 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 2018-04-27 16:05:57,959 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: now() 2018-04-27 16:05:57,959 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 2018-04-27 16:05:57.0 2018-04-27 16:05:57,969 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 當(dāng)前時間:2018-04-27 16:05:57.0當(dāng)基于不同的數(shù)據(jù)庫運行時，MyBatis會根據(jù)配置找到合適的SQL去執(zhí)行。
注意事項
我們在上面演示了基于databaseId的基本用法，實際在工作中，大部分的SQL還是相同的，沒有必要寫成2個。這樣會導(dǎo)致大量重復(fù)的SQL。
數(shù)據(jù)庫的更換可能只會引起某個SQL語句的部分不同， 可以使用if標簽配合默認的上下文中的_databaseId參數(shù)去實現(xiàn)。
我們用前幾篇博文的例子來演示下
改造前
<select id="selectSysUsersAdvancedWithWhere" resultType="com.artisan.mybatis.xml.domain.SysUser">SELECTa.id,a.user_name userName,a.user_password userPassword,a.user_email userEmail,a.user_info userInfo,a.head_img headImg,a.create_time createTimeFROMsys_user a<where><if test="userName != null and userName != '' ">and user_name like concat('%',#{userName},'%')</if><if test="userEmail != null and userEmail != '' ">and user_email = #{userEmail}</if></where></select>改造后
<!-- 多數(shù)據(jù)庫的支持 BEGIN --><select id="selectSysUsersAdvancedMulitDB" resultType="com.artisan.mybatis.xml.domain.SysUser">SELECTa.id,a.user_name userName,a.user_password userPassword,a.user_email userEmail,a.user_info userInfo,a.head_img headImg,a.create_time createTimeFROMsys_user a<where><if test="userName != null and userName != '' "><if test="_databaseId == 'mysql' ">and user_name like concat('%',#{userName},'%')</if><if test="_databaseId == 'oracle' ">and user_name like '%'||#{username}||'%'</if></if><if test="userEmail != null and userEmail != '' ">and user_email = #{userEmail}</if></where></select><!-- 多數(shù)據(jù)庫的支持 END -->增加個接口方法，方便區(qū)分測試
/*** * * @Title: selectSysUsersAdvancedMulitDB* * @Description: selectSysUsersAdvancedMulitDB* * @param sysUser* @return* * @return: List<SysUser>*/List<SysUser> selectSysUsersAdvancedMulitDB(SysUser sysUser);單元測試
@Testpublic void selectSysUsersAdvancedMulitDBTest() {logger.info("selectSysUsersAdvancedMulitDBTest");// 獲取SqlSessionSqlSession sqlSession = getSqlSession();List<SysUser> userList = null;try {// 獲取UserMapper接口UserMapper userMapper = sqlSession.getMapper(UserMapper.class);logger.info("===========1.當(dāng)用戶只輸入用戶名時，需要根據(jù)用戶名模糊查詢===========");// 模擬前臺傳參 1.當(dāng)用戶只輸入用戶名時，需要根據(jù)用戶名模糊查詢SysUser sysUser = new SysUser();sysUser.setUserName("ad");// 調(diào)用selectSysUserByAdvancedCondition,根據(jù)查詢條件查詢用戶userList = userMapper.selectSysUsersAdvancedMulitDB(sysUser);// 根據(jù)數(shù)據(jù)庫sys_user表中的記錄,可以匹配到admin, 期望userList不為空Assert.assertNotNull(userList);// 根據(jù)查詢條件，期望只有1條數(shù)據(jù)Assert.assertTrue(userList.size() == 1);logger.info("userList:" + userList);// 為了測試 匹配多條記錄的情況，我們將id=1001這條數(shù)據(jù)的userName 由test 改為artisansysUser.setUserName("i");// 調(diào)用selectSysUserByAdvancedCondition,根據(jù)查詢條件查詢用戶userList = userMapper.selectSysUsersAdvancedMulitDB(sysUser);// 根據(jù)數(shù)據(jù)庫sys_user表中的記錄,可以匹配到admin和artisan, 期望userList不為空Assert.assertNotNull(userList);// 根據(jù)查詢條件，期望只有2條數(shù)據(jù)Assert.assertTrue(userList.size() == 2);logger.info("userList:" + userList);logger.info("===========2.當(dāng)用戶只輸入郵箱使，根據(jù)郵箱進行完全匹配===========");// 模擬前臺傳參 2.當(dāng)用戶只輸入郵箱使，根據(jù)郵箱進行完全匹配sysUser.setUserEmail("admin@artisan.com");userList = userMapper.selectSysUsersAdvanced(sysUser);Assert.assertNotNull(userList);Assert.assertTrue(userList.size() == 1);logger.info(userList);sysUser.setUserEmail("1admin@artisan.com");userList = userMapper.selectSysUsersAdvancedMulitDB(sysUser);Assert.assertTrue(userList.size() == 0);logger.info("===========3.當(dāng)用戶同時輸入用戶名和密碼時，用這兩個條件查詢匹配的用戶===========");// 模擬組合查詢條件,存在記錄的情況sysUser.setUserName("i");sysUser.setUserEmail("admin@artisan.com");userList = userMapper.selectSysUsersAdvancedMulitDB(sysUser);Assert.assertNotNull(userList);Assert.assertEquals("admin@artisan.com", sysUser.getUserEmail());Assert.assertTrue(userList.size() == 1);logger.info(userList);logger.info("===========4.當(dāng)用戶同時輸入無法匹配的用戶名和密碼===========");// 模擬組合查詢條件,不存在記錄的情況sysUser.setUserName("x");sysUser.setUserEmail("admin@artisan.com");userList = userMapper.selectSysUsersAdvancedMulitDB(sysUser);Assert.assertTrue(userList.size() == 0);logger.info(userList);} catch (Exception e) {e.printStackTrace();} finally {sqlSession.close();logger.info("sqlSession close successfully ");}}日志
2018-04-27 16:22:51,822 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-27 16:22:51,826 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-27 16:22:51,827 INFO [main] (UserMapperTest.java:934) - selectSysUsersAdvancedMulitDBTest 2018-04-27 16:22:51,857 INFO [main] (UserMapperTest.java:943) - ===========1.當(dāng)用戶只輸入用戶名時，需要根據(jù)用戶名模糊查詢=========== 2018-04-27 16:22:51,987 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') 2018-04-27 16:22:52,086 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: ad(String) 2018-04-27 16:22:52,157 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-27 16:22:52,157 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-27 16:22:52,167 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-27 16:22:52,167 INFO [main] (UserMapperTest.java:953) - userList:[SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理員用戶, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]] 2018-04-27 16:22:52,177 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') 2018-04-27 16:22:52,177 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String) 2018-04-27 16:22:52,177 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-27 16:22:52,177 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-27 16:22:52,177 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-27 16:22:52,177 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 2 2018-04-27 16:22:52,187 INFO [main] (UserMapperTest.java:964) - userList:[SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理員用戶, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018], SysUser [id=1001, userName=artisan, userPassword=123456, userEmail=test@artisan.com, userInfo=測試用戶, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]] 2018-04-27 16:22:52,187 INFO [main] (UserMapperTest.java:966) - ===========2.當(dāng)用戶只輸入郵箱使，根據(jù)郵箱進行完全匹配=========== 2018-04-27 16:22:52,187 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE 1=1 and user_name like concat('%',?,'%') and user_email = ? 2018-04-27 16:22:52,187 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), admin@artisan.com(String) 2018-04-27 16:22:52,187 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-27 16:22:52,187 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-27 16:22:52,187 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-27 16:22:52,187 INFO [main] (UserMapperTest.java:972) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理員用戶, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]] 2018-04-27 16:22:52,187 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') and user_email = ? 2018-04-27 16:22:52,197 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), 1admin@artisan.com(String) 2018-04-27 16:22:52,197 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0 2018-04-27 16:22:52,197 INFO [main] (UserMapperTest.java:978) - ===========3.當(dāng)用戶同時輸入用戶名和密碼時，用這兩個條件查詢匹配的用戶=========== 2018-04-27 16:22:52,197 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') and user_email = ? 2018-04-27 16:22:52,197 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), admin@artisan.com(String) 2018-04-27 16:22:52,197 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-27 16:22:52,207 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-27 16:22:52,207 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-27 16:22:52,207 INFO [main] (UserMapperTest.java:987) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理員用戶, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]] 2018-04-27 16:22:52,207 INFO [main] (UserMapperTest.java:989) - ===========4.當(dāng)用戶同時輸入無法匹配的用戶名和密碼=========== 2018-04-27 16:22:52,207 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') and user_email = ? 2018-04-27 16:22:52,207 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: x(String), admin@artisan.com(String) 2018-04-27 16:22:52,207 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0 2018-04-27 16:22:52,207 INFO [main] (UserMapperTest.java:995) - [] 2018-04-27 16:22:52,217 INFO [main] (UserMapperTest.java:1001) - sqlSession close successfully總結(jié)
以上是生活随笔為你收集整理的MyBatis-16MyBatis动态SQL之【支持多种数据库】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MyBatis-15MyBatis动态S
- 下一篇： MyBatis-17MyBatis代码生