【面试】系统兼容多个数据库改造

本文主要是介绍【面试】系统兼容多个数据库改造，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

系统改成同时兼容Oracle和Mysql和TDSQL，

SpringBoot+Mybatis 通过databaseIdProvider支持多数据库 - 青衫不改の小白 - 博客园

配置案例

jdbc:defaultDataSourceId: default # 默认数据源 defaultdataSources:- id: default # 数据源defaultpool:#driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver#url: jdbc:oracle:thin:@ip:port:orcldriverClassName: com.mysql.jdbc.Driver#        driverClassName: com.mysql.cj.jdbc.Driver#        url: jdbc:mysql://ip:port/alibaba_manage?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=falseurl: jdbc:mysql://ip:port/alibaba_manage?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=falseusername: rootpassword: 123456initialSize: 1minIdle: 1maxActive: 20maxWait: 60000configLocation: classpath:conf/mybatis.xmlmybatisPath: classpath*:mybatis/*.xml,classpath*:mapper/*.xml,classpath*:mybatis/mapping/**/*.xml,classpath*:mybatis/**/*.xml,classpath*:mapper/**/*.xml- id: tdsql # 数据源defaultpool:#driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver#url: jdbc:oracle:thin:@ip:port:orcldriverClassName: com.mysql.jdbc.Driver#        driverClassName: com.mysql.cj.jdbc.Driver#        url: jdbc:mysql://ip:port/alibaba_manage?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=falseurl: jdbc:mysql://ip:port/alibaba_acct?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=falseusername: rootpassword: 123456initialSize: 1minIdle: 1maxActive: 20maxWait: 60000configLocation: classpath:conf/mybatis.xmlmybatisPath: classpath*:mybatis/*.xml,classpath*:mapper/*.xml,classpath*:mybatis/mapping/**/*.xml,classpath*:mybatis/**/*.xml,classpath*:mapper/**/*.xml

@ConfigurationProperties(prefix = "jdbc")
public class SqlConfig {private String databaseIdProvider;public String getDatabaseIdProvider() {return databaseIdProvider;}public void setDatabaseIdProvider(String databaseIdProvider) {this.databaseIdProvider = databaseIdProvider;}
}

 在resources/META-INF下，spring.factories文件里，加上这个类的全类名(首行必须得是接口)。

org.springframework.boot.autoconfigure.EnableAutoConfiguration=\
com.szkingdom.kuas.support.config.TdSqlConfig

该配置是利用了tomcat的SPI机制，实例化这个类，加入spring的bean管理中。效果等同于@Component

<environments default="developmentOracle"><environment id="development">...<environment id="developmentOracle">...

Mybatis 文档篇 2.8：Configuration 之 DatabaseIdProvider - 简书

    <insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false" databaseId="oracle">INSERT INTO user_info(USER_CODE,FIELD_CODE,FIELD_VALUE,UPDATE_TIME)<foreach item="userInfo" index="index" collection="userInfos" separator="UNION ALL">(SELECT#{userInfo.userCode},#{userInfo.fieldCode},#{userInfo.fieldValue},#{userInfo.updateTime}FROM DUAL)</foreach></insert><insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false" databaseId="mysql">INSERT INTO user_info(USER_CODE,FIELD_CODE,FIELD_VALUE,UPDATE_TIME) VALUES<foreach item="userInfo" index="index" collection="userInfos" separator=",">(#{userInfo.userCode},#{userInfo.fieldCode},#{userInfo.fieldValue},#{userInfo.updateTime})</foreach></insert>

    <select id="getLastLogin" resultMap="LoginMap">selectLOGIN_SN, LOGIN_NAME, LOGIN_CLS, LOGIN_DESC, USER_TYPE, EXP_DAYS, INTERVAL_DAYS, LOGIN_SYN, VERSION, REMARKfrom LOGIN<where><if test="loginDo.loginSn != null">and LOGIN_SN = #{loginDo.loginSn}</if><if test="loginDo.version != null and loginDo.version != ''">and VERSION = #{loginDo.version}</if></where><if test="_databaseId == 'oracle'">AND rownum=1 ORDER BY VERSION DESC</if><if test="_databaseId == 'mysql'">ORDER BY VERSION DESC LIMIT 1</if><if test="_databaseId == 'pgsql'">ORDER BY VERSION DESC LIMIT 1</if></select>

    <select id="getNextVal" resultType="Long" databaseId="oracle">SELECT ${sequenceName}.NEXTVAL AS NEXTVALFROM DUAL</select><select id="getNextVal" resultType="Long" databaseId="mysql">select nextval('${sequenceName}')</select><select id="getNextVal" resultType="Long" databaseId="pgsql">select pgsql_nextval(${sequenceName})</select>

这篇关于【面试】系统兼容多个数据库改造的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---