一、准备工作:
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&failOverReadOnly=false&useUnicode=true&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&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>
结束