项目中Mybatis、Proxool配置以及dao层的封装实例

一、准备工作：

1.数据库：项目中使用Mysql作为数据库

2.需要的相关jar，这里主要列出Mybatis、Proxool、Mysql相关jar：

mybatis-3.1.1.jar，mysql-connector-java-5.0.5-bin.jar、proxool-0.9.1.jar、proxool-cglib.jar

二、开始配置文件

1.Mybatis配置文件mybatis-config.xml

   <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
 <typeAliases>
  <typeAlias type="cn.com.test.ms.factory.ProxoolDataSourceFactory" alias="mysqlproxool"/><!--proxool连接池数据源，自定义-->
 </typeAliases>
 <environments default="development">
 

<!--连接池-->

 <environment id="proxool">  <!-- 修改这里以后必须同步修改application.properties文件mybatis_environment属性的值 -->
    <transactionManager type="JDBC" /> 
    <dataSource type="mysqlproxool"> 
    </dataSource> 
  </environment> 

<!--jdbc-->
  <environment id="mysql"> <!-- 修改这里以后必须同步修改application.properties文件mybatis_environment属性的值 -->
   <transactionManager type="JDBC" />
   <dataSource type="POOLED">
    <property name="driver" value="com.mysql.jdbc.Driver" />
   
    <property name="url" value="jdbc:mysql://172.0.0.1:3306/test?

autoReconnect=true&amp;failOverReadOnly=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
    <property name="username" value="root"/>
    <property name="password" value="root"/>
   
    <!-- 在任意时间存在的活动(也就是正在使用)连接的数量 -->
    <property name="poolMaximumActiveConnections" value="200"/>
   
    <!-- 任意时间存在的空闲连接数
    <property name="poolMaximumIdleConnections" value="5"/> -->
   
   </dataSource>
  </environment>
 </environments>
 <mappers>

 
  <mapper resource="cn/com/pkit/ms/model/User.xml" />

 </mappers>
</configuration>

2.Proxool配置文件proxool.xml

<?xml version="1.0" encoding="UTF-8" ?>
<something-else-entirely>
 <proxool>
  <alias>mysqlproxool</alias>
  <driver-class>com.mysql.jdbc.Driver</driver-class>
  <driver-url>jdbc:mysql://172.0.0.1:3306/test?useUnicode=true&amp;characterEncoding=UTF-8</driver-url>
  <driver-properties>
   <property name="user" value="root" />
   <property name="password" value="root" />
  </driver-properties>
  <!-- proxool 自动侦察各个连接状态的时间间隔 ( 毫秒 ),侦察到空闲的连接就马上回收 , 超时的销毁 -->
  <house-keeping-sleep-time>60000</house-keeping-sleep-time>
  <!-- 指因未有空闲连接可以分配而在队列中等候的最大请求数 , 超过这个请求数的用户连接就不会被接受 -->
  <!-- <maximum-new-connections>20</maximum-new-connections> -->
  <simultaneous-build-throttle>20</simultaneous-build-throttle>
  <!-- 最少保持的空闲连接数
  <prototype-count>5</prototype-count>-->
  <!-- 允许最大连接数 , 超过了这个连接，再有请求时，就排在队列中等候，最大的等待请求数由 maximum-new-connections
   决定 -->
  <maximum-connection-count>500</maximum-connection-count>
  <!-- 最小连接数 -->
  <minimum-connection-count>5</minimum-connection-count> -->
  <!-- 在分配连接前后是否进行有效性测试 -->
  <test-before-use>true</test-before-use>
  <!--<test-after-use>true</test-after-use>-->
  <!-- 用于测试的 SQL -->
  <house-keeping-test-sql>select 1 from dual</house-keeping-test-sql>

<!-- 表示连接的最大活动时间，默认5分钟<maximum-active-time>300000</maximum-active-time>-->
 <maximum-active-time>18000000</maximum-active-time><!-- 300分钟 -->

 </proxool>
</something-else-entirely>

3.application.properties,选择使用jdbc 或者 连接池proxool

mybatis_environment=proxool
#mybatis_environment=mysql

三、代码

1.proxool数据源ProxoolDataSourceFactory

package cn.com.test.ms.factory;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.ibatis.datasource.DataSourceFactory;
import org.logicalcobwebs.proxool.ProxoolDataSource;

public class ProxoolDataSourceFactory implements DataSourceFactory {

 protected ProxoolDataSource dataSource;
 
 public ProxoolDataSourceFactory(){
  dataSource = new ProxoolDataSource("mysqlproxool");  // alias参考proxool.xml 中标签<alias>的值
 }
 @Override
 public void setProperties(Properties props) {
 }

 @Override
 public DataSource getDataSource() {
  return dataSource;
 }

}

2.mybatis获取session工具类MybatisUtils

package cn.com.test.ms.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据库CRUD 工具类
 * @author Administrator
 *
 */
public final class MybatisUtils {

 private static final String CONFIG_FILE = "mybatis-config.xml";
 private static final Logger logger = LoggerFactory.getLogger(MybatisUtils.class);
 private static final ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
 private static TransactionFactory transactionFactory = new JdbcTransactionFactory();
 private static SqlSessionFactory sqlSessionFactory = null;
 private static final String MYBATIS_ENVIRONMENT = ApplicationPropUtil.getValue("mybatis_environment");
 
 private MybatisUtils(){
 
 }
 
 static {
  buildSessionFactory();
 }
 
 /**
  * 获取session
  * @return SqlSession
  */
 public static SqlSession getSession(){
  SqlSession session = threadLocal.get();
  if (session == null){
   if(sqlSessionFactory == null){
    buildSessionFactory();
      }
   session = sqlSessionFactory != null ? sqlSessionFactory.openSession() : null;
   threadLocal.set(session);
  }
  
  return session;
 }
 
 /**
  * 关闭session
  */
 public static void closeSession(){
  SqlSession session = threadLocal.get();
  if (session != null){
   session.close();
   threadLocal.set(null);
  }
 }
 
 public static TransactionFactory getTransactionFactory(){
  return transactionFactory;
 }
 
 public static SqlSessionFactory getSessionFactory(){
  if (sqlSessionFactory == null){
   buildSessionFactory();
  }
  return sqlSessionFactory;
 }
 
 
 private static void buildSessionFactory(){
  if (sqlSessionFactory == null){
   InputStream inputStream = null;
   try {
    inputStream = Resources.getResourceAsStream(CONFIG_FILE);
    sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, MYBATIS_ENVIRONMENT);
//    addMapper(sqlSessionFactory);
   } catch (IOException e) {
    logger.error("读取mybatis-config.xml文件时出现异常，异常信息：" + e.getMessage());
    e.printStackTrace();
   } catch (Exception e){
    logger.error("创建SessionFactory失败，异常信息：" + e.getMessage());
    e.printStackTrace();
   } finally{
    if (inputStream != null){
     try {
      inputStream.close();
     } catch (IOException e) {
      e.printStackTrace();
     }
     inputStream = null;
    }
   }
  }
 }
 
// private static void addMapper(SqlSessionFactory sqlSessionFactory){
//  if (sqlSessionFactory != null){
//   sqlSessionFactory.getConfiguration().addMapper(MerchantInfoContentDao.class);
//  }
// }
 
}

3.读取properties工具类ApplicationPropUtil

package cn.com.test.ms.util;

import java.io.IOException;
import java.util.Properties;
/**
 * application.properties 工具
 * @author Administrator
 *
 */
public final class ApplicationPropUtil {

 private static final String NAME = "application.properties";
 
 //private static final String DEFAULT_KEY = "secret_key";
 
 private static final Properties AppProp = new Properties();
 
 static {
  try {
   AppProp.load(ApplicationPropUtil.class.getClassLoader().getResourceAsStream(NAME));
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
 
 public static String getValue(String key){
  return AppProp.getProperty(key);
 }
 
 /**
  * 获取加密密钥
  * @return
  */
// public static String getDefaultValue(){
//  return getValue(DEFAULT_KEY);
// }
 
}

4.数据访问superDao

package cn.com.test.ms.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.wmf.tools.web.WebPage;

import cn.com.pkit.ms.util.MybatisUtils;

public class SuperDao{
 
 protected <T> List<T> selectList(String arg0, Object arg1) throws SQLException{
  SqlSession session = MybatisUtils.getSession();
  List<T> retList = null;
  if (session != null){
   retList = session.selectList(arg0, arg1);
  }
  MybatisUtils.closeSession();
  return retList;
 }
 
 protected long selectCount(String p,Object params) throws SQLException{
  SqlSession session = MybatisUtils.getSession();
  if(session!=null){
   Number total =(Number)session.selectOne(p,params);
   if(total==null) return 0;
   return total.longValue();
  }
  MybatisUtils.closeSession(); 
  return 0;
 }
 protected <T> List<T> selectList(String arg0) throws SQLException{
  SqlSession session = MybatisUtils.getSession();
  List<T> retList = null;
  if (session != null){
   retList = session.selectList(arg0);
  }
  MybatisUtils.closeSession();
  return retList;
 }
 
 protected <T> T selectOne(String arg0, Object arg1) throws SQLException{
  SqlSession session = MybatisUtils.getSession();
  T ret = null;
  if (session != null){
   ret = session.selectOne(arg0, arg1);
  }
  MybatisUtils.closeSession();
  return ret;
 }
 
 protected boolean save(String arg0, Object arg1) throws SQLException{
  SqlSession session = MybatisUtils.getSession();
  boolean ret = false;
  if (session != null){
   int affectedRows = session.insert(arg0, arg1);
   if (affectedRows > 0){
    ret = true;
   }
  }
  MybatisUtils.closeSession();
  return ret;
 }
 
 protected boolean update(String arg0, Object arg1) throws SQLException{
  SqlSession session = MybatisUtils.getSession();
  boolean ret = false;
  if (session != null){
   int affectedRows = session.update(arg0, arg1);
   if (affectedRows > 0){
    ret = true;
   }
  }
  MybatisUtils.closeSession();
  return ret;
 }
}

5.接口dao  UserDao

package cn.com.test.ms.dao;

import java.sql.SQLException;
import java.util.List;

import cn.com.test.ms.model.User;

public interface UserDao {
 
 /**
  * 查询用户
  * @param userId 用户编号
  * @return
  * @throws SQLException
  */
 User queryUser(String userId) throws SQLException;
 
}

6.接口daoimpl

package cn.com.test.ms.dao.impl;

import java.sql.SQLException;
import java.util.List;

import cn.com.test.ms.dao.UserDao;
import cn.com.test.ms.model.User;

public class UserDaoImpl extends SuperDao implements UserDao {

 @Override
 public User queryUser(String userId) throws SQLException {
  return selectOne("cn.com.pkit.ms.model.User.queryUser", userId);
 }

}

7.model  User

package cn.com.test.ms.model;

import java.io.Serializable;

public class User implements Serializable {

 /**
  *
  */
 private static final long serialVersionUID = -8709376412981684384L;

 private int id;
 private String userId;
 private String loginName;

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getUserId() {
  return userId;
 }

 public void setUserId(String userId) {
  this.userId = userId;
 }

 public String getLoginName() {
  return loginName;
 }

 public void setLoginName(String loginName) {
  this.loginName = loginName;
 }

 @Override
 public String toString() {
  return "User [id=" + id + ", userId=" + userId + ", loginName="
    + loginName + "]";
 }

}

8.User.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="cn.com.test.ms.model.User" >
 
 <resultMap type="cn.com.test.ms.model.User" id="User">
  <result property="id" column="ID" />
  <result property="userId" column="UserID" />
  <result property="loginName" column="LoginName" />
 </resultMap>
 
 <select id="queryUser" parameterType="java.lang.String" resultMap="User">
  <![CDATA[
   select t1.ID as ID,t1.UserID as UserID,
   t1.LoginName as LoginName from t_user t1 where t1.UserID=#{userId}
  ]]>
 </select>
 
</mapper>

 

结束