mybatis 多数据源 配置实例

woxio770

　　简介：本案例是使用使用mybatis来增删改查的一个实例， 它的数据源是多个的，可以随意切换，这在数据量比较大的项目中还是经常要用到的哦。 简单的说有点意思，哈哈。最后测试由于刚好有httpclients的代码就之间拿过来用了。
　　操作的实体类：

public class User {
private int id;
private String username;
private String password;
private String salt;
////////////get/set方法省略/////////////
}
　　数据属性配置文件mybatis-config-datasource.properties
　　使用mysql数据库，其中%DBNAME%会根据实际情况替换实际连接的数据库。

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/%DBNAME%
username=root
password=123456

　　数据库连接配置文件：mybatis-config.xml
　　使用了C3P0连接池
　　红色字体部分(最后三行)配置了实体操作配置文件路径

<?xml version="1.0" encoding="UTF-8" ?>   
<!DOCTYPE configuration        
PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"        
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">  
<configuration>  
<settings>  
<!-- 全局映射器启用缓存 -->  
<setting name="cacheEnabled" value="false" />  
<!-- 查询时，关闭关联对象即时加载以提高性能 -->  
<setting name="lazyLoadingEnabled" value="true" />
<!-- 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指定)，不会加载关联表的所有字段，以提高性能 -->  
<setting name="aggressiveLazyLoading" value="false" />  
<!-- 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果 -->  
<setting name="multipleResultSetsEnabled" value="true" />  
<!-- 允许使用列标签代替列名 -->  
<setting name="useColumnLabel" value="true" />  
<!-- 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖 -->  
<setting name="useGeneratedKeys" value="true" />  
<!-- 给予被嵌套的resultMap以字段-属性的映射支持 -->  
<setting name="autoMappingBehavior" value="FULL" />  
<!-- 对于批量更新操作缓存SQL以提高性能  -->  
<!-- <setting name="defaultExecutorType" value="BATCH" />   -->
<!-- FORSQLSERVER -->
<setting name="defaultExecutorType" value="SIMPLE" />
<!-- 数据库超过25000秒仍未响应则超时 -->  
<setting name="defaultStatementTimeout" value="25000" />  
</settings>  
<typeAliases>
<typeAlias type="com.qing.datasource.C3P0DataSourceFactory" alias="C3P0" />
</typeAliases>
<environments default="DAG">
<environment id="DAG">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="C3P0">
<property name="driverClass" value="${driver}" />
<property name="jdbcUrl" value="${url}" />
<property name="user" value="${username}" />
<property name="password" value="${password}" />
<property name="preferredTestQuery" value="SELECT 1" />
<property name="acquireIncrement" value="3" />
<property name="minPoolSize" value="10" />
<property name="maxPoolSize" value="100" />
<property name="maxIdleTime" value="60" />
</dataSource>
</environment>
</environments>
<mappers>  
<mapper resource="sqlmapper/UserMapper.xml"/>
</mappers>
</configuration>  
　　数据库切换的初始化

public class DBNameUtil {
private Map<String,String> map = new HashMap<String,String>();
private static DBNameUtil dbName = new DBNameUtil();
private DBNameUtil(){
map.clear();
map.put("dag1", "test");
map.put("dag2", "shiro");
map.put("dag3", "shiroweb");
}
public String getDBName(String dagId){
return map.get(dagId);
}
public static DBNameUtil getInstance(){
return dbName;
}
}
　　现在我们应该把这两个配置文件里的数据组合起来
　　创建Mybatis SqlSessionFactory实例

import java.io.IOException;
import java.io.InputStream;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.springframework.util.StringUtils;
import com.qing.utils.DBNameUtil;
/**
* 构造指定DAG ID的Mybatis SqlSessionFactory实例，并进行全局缓存.
*/
public final class DataSourceSqlSessionFactoryBuilder {
private DataSourceSqlSessionFactoryBuilder() {};
/**
* Mybatis配置文件名.
*/
private static final String MYBATIS_CONFIG = "mybatis-config.xml";
/**
* Mybatis数据源属性配置文件.
*/
private static final String MYBATIS_CONFIG_PROPERTIES = "mybatis-config-datasource.properties";
/**
* 缓存每个DAG对应的Mybatis数据源.
*/
private static final Map<String, SqlSessionFactory> DATASOURCE = new ConcurrentHashMap<String, SqlSessionFactory>();
public static SqlSessionFactory buildSqlSessionFactory(String dagID)
throws IOException {
if (StringUtils.isEmpty(dagID)) {
throw new IOException("The DAG id is Empty!");
}
if (DATASOURCE.get(dagID) != null) {
System.out.println("DDR: get dataSource from cache width dagid[{" + dagID + "}].");
return DATASOURCE.get(dagID);
}
String dbName = DBNameUtil.getInstance().getDBName(dagID);
if (dbName == null) {
throw new IOException("The DAG id is not found! " + dagID);
}
Properties properties = null;
SqlSessionFactory sqlSessionFactory = null;
InputStream inputStream = Resources.getResourceAsStream(MYBATIS_CONFIG);
properties = Resources.getResourceAsProperties(MYBATIS_CONFIG_PROPERTIES);
String url = properties.getProperty("url").toString().replace("%DBNAME%", dbName);
properties.setProperty("url", url);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,properties);
DATASOURCE.put(dagID, sqlSessionFactory);
System.out.println("DDR: build dataSource with dagId[{" + dagID + "}] and domain[{" + dbName + "}].");
return DATASOURCE.get(dagID);
}
}

　　有了SqlSessionFactory，现在就要创建SqlSession了，但是为方便管理，并且考虑到线程安全以及事物，我们建立一个SessionManager类。

import java.sql.SQLException;
import org.apache.ibatis.session.SqlSession;
/**
* 管理线程上下文持有的 Mybatis SqlSession实例.
*/
public final class SessionManager {
private SessionManager(){};
//当前线程上下文，持有Mybatis SqlSession对象.
private static ThreadLocal<SqlSession> sqlSessionHolder = new ThreadLocal<SqlSession>();
//获取当前线程持有的SqlSession对象实例.
public static SqlSession getSqlSession() {
return sqlSessionHolder.get();
}
//设置当前线程持有的SqlSession实例.
public static void setSqlSession(SqlSession sqlSession) {
sqlSessionHolder.set(sqlSession);
}
/**
* 使用当前线程的SqlSession实例构造指定Mapper实例的代理对象.
* @param clazz Mapper接口类型.
* @return
*/
public static <T> T get(Class<T> clazz) {
SqlSession session = sqlSessionHolder.get();
try {
System.out.println("==> dataSource: {}"+session.getConnection().getMetaData().getURL());
} catch (SQLException e) {
e.printStackTrace();
}
return sqlSessionHolder.get().getMapper(clazz);
}
//关闭当前线程持有的SqlSession实例并从其ThreadLocal上下文实例中移除.
public static void close() {
if (sqlSessionHolder.get() != null) {
sqlSessionHolder.get().close();
}
sqlSessionHolder.remove();
}
/**
* 进行数据库事务提交.
* @param deltegate 回调接口.
* @throws Exception
*/
public static void transactional(TransactionDelegate deltegate) throws Exception{
SqlSession session = sqlSessionHolder.get();
try {
System.out.println("==> dataSource: {}"+session.getConnection().getMetaData().getURL());
} catch (SQLException e) {
e.printStackTrace();
}
try{
session.commit(false);
deltegate.execute(session);
session.commit();
}catch(Exception ex){
session.rollback();
throw ex;
}finally{
session.commit(true);
}
}
//事务代理接口
public interface TransactionDelegate{
public void execute(SqlSession session) throws Exception;
}
}
　　最后就是创建操作实体的接口，以及实体相关配置了
　　UserMapper.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">
<mapper namespace="com.qing.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.qing.entity.User">
<result column="id" jdbcType="TINYINT" property="id" />
<result column="username" jdbcType="VARCHAR" property="username" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="salt" jdbcType="VARCHAR" property="salt" />
</resultMap>
<sql id="Base_Column_List">
id,username,password,salt
</sql>
<select id="findByUserName" parameterType="java.lang.String" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List" />
FROM sys_user_test
WHERE username = #{username}
</select>
<insert id="save" parameterType="com.qing.entity.User" useGeneratedKeys="false">
INSERT INTO sys_user_test (id
,username
,password
,salt
) VALUES (
#{id}
,#{username}
,#{password}
,#{salt}
)
</insert>
<update id="updateUser" parameterType="com.qing.entity.User">
UPDATE sys_user_test
<set >
<if test="id != null" >
id = #{id},
</if>
<if test="username != null">
username = #{username},
</if>
<if test="password != null">
password = #{password},
</if>
<if test="salt != null">
salt = #{salt},
</if>
</set>
WHERE id = #{id}
</update>  
</mapper>
　　操作数据库接口

public interface UserMapper {
List<User> findByUserName(String username);
void save(User user);
int updateUser(User user);
}
　　测试：
　　新建一个测试的servlet

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
System.out.println("====================MyServlet 我来啦 ==================");
String dagId = request.getParameter("dagId");
System.out.println("dagId================>"+dagId);
System.out.println("dbName================>"+DBNameUtil.getInstance().getDBName(dagId));
SqlSessionFactory sqlSessionFactory = DataSourceSqlSessionFactoryBuilder.buildSqlSessionFactory(dagId);
SessionManager.setSqlSession(sqlSessionFactory.openSession(true));
User user = new User();
user.setId(5);
user.setUsername("wang");
user.setPassword("2345134");
user.setSalt(DBNameUtil.getInstance().getDBName(dagId)+user.getId());
SessionManager.get(UserMapper.class).save(user);
}
　　把项目放到tomcat下面启动。
　　使用的是HttpClients进行测试

public class Test1 {
/**
* 发送 post请求访问本地应用并根据传递参数不同返回不同结果
*/  
public void post() {  
// 创建默认的httpClient实例.   
CloseableHttpClient httpclient = HttpClients.createDefault();  
// 创建httppost   
HttpPost httppost = new HttpPost("http://localhost:8080/mybatis/MyServlet");  
// 创建参数队列   
List<NameValuePair> formparams = new ArrayList<NameValuePair>();  
formparams.add(new BasicNameValuePair("dagId", "dag2"));  
UrlEncodedFormEntity uefEntity;  
try {  
uefEntity = new UrlEncodedFormEntity(formparams, "UTF-8");  
httppost.setEntity(uefEntity);  
System.out.println("executing request " + httppost.getURI());  
CloseableHttpResponse response = httpclient.execute(httppost);  
try {  
HttpEntity entity = response.getEntity();  
if (entity != null) {  
System.out.println("--------------------------------------");  
System.out.println("Response content: " + EntityUtils.toString(entity, "UTF-8"));  
System.out.println("--------------------------------------");  
}  
} finally {  
response.close();  
}  
} catch (ClientProtocolException e) {  
e.printStackTrace();  
} catch (UnsupportedEncodingException e1) {  
e1.printStackTrace();  
} catch (IOException e) {  
e.printStackTrace();  
} finally {  
// 关闭连接,释放资源   
try {  
httpclient.close();  
} catch (IOException e) {  
e.printStackTrace();  
}  
}  
}   
public static void main(String[] args) {
Test1 t = new Test1();
t.post();
}
}