myBatis获取SqlSession连接对象的两种方式

1、spring配置文件applicationContext.xml，代码如下：

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jee="http://www.springframework.org/schema/jee"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:jaxws="http://cxf.apache.org/jaxws"
    xmlns:jaxrs="http://cxf.apache.org/jaxrs"
    xmlns:cxf="http://cxf.apache.org/core"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/jee 
        http://www.springframework.org/schema/jee/spring-jee.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://cxf.apache.org/jaxws http://cxf.apache.org/schemas/jaxws.xsd  
      http://cxf.apache.org/core http://cxf.apache.org/schemas/core.xsd  
      http://cxf.apache.org/jaxrs http://cxf.apache.org/schemas/jaxrs.xsd"> 
    
    <!-- 引入jdbc配置文件 -->  
    <context:property-placeholder location="classpath:com/test/config/jdbc.properties"/>  
    
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">  
<property name="driverClass">  
            <value>${jdbc.driverClassName}</value>  
        </property>  
        <property name="jdbcUrl">  
            <value>${jdbc.url}</value>  
        </property>  
        <property name="user">  
            <value>${jdbc.username}</value>  
        </property>  
        <property name="password">  
            <value>${jdbc.password}</value>  
        </property>  
        <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->   
        <property name="acquireIncrement">  
            <value>${jdbc.c3p0.acquireIncrement}</value>  
        </property>    
        <!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->   
        <property name="initialPoolSize">  
            <value>${jdbc.c3p0.initialPoolSize}</value>  
        </property>  
        <property name="minPoolSize">  
            <value>${jdbc.c3p0.minPoolSize}</value>  
        </property>  
        <property name="maxPoolSize">  
            <value>${jdbc.c3p0.maxPoolSize}</value>  
        </property>  
        <!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->   
        <property name="maxIdleTime">  
            <value>${jdbc.c3p0.maxIdleTime}</value>  
        </property>  
        <!--每60秒检查所有连接池中的空闲连接。Default: 0 -->   
        <property name="idleConnectionTestPeriod">  
            <value>${jdbc.c3p0.idleConnectionTestPeriod}</value>  
        </property>  
        <!-- JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements   
                            属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。   
                            如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0-->   
        <property name="maxStatements">  
            <value>${jdbc.c3p0.maxStatements}</value>  
        </property>  
    </bean>  
    
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <property name="configLocation" value="classpath:com/test/config/myBatis-config.xml" />  
        <property name="dataSource" ref="dataSource"/>  
    </bean> 
    
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">  
      <constructor-arg index="0" ref="sqlSessionFactory" />  
    </bean> 
    
<!-- 自动扫描组件，这里要把controler下面的 controller去除，他们是在spring3-servlet.xml中配置的，如果不去除会影响事务管理的。  -->
  <context:component-scan base-package="com.test">
  <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" /> 
  </context:component-scan>
 
    <!-- 事务配置 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
abstract="false" lazy-init="default" autowire="default">
  <property name="dataSource" ref="dataSource"></property>
</bean>

<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="get*" read-only="true"/>
<tx:method name="search*" read-only="true"/>
<tx:method name="query*" read-only="true"/>
<tx:method name="create*" rollback-for="Throwable" />
<tx:method name="save*" rollback-for="Throwable" />
<tx:method name="update*" rollback-for="Throwable" />
<tx:method name="delete*" rollback-for="Throwable" />
<tx:method name="tx*" rollback-for="Throwable" />
<tx:method name="rnew*" rollback-for="Throwable" propagation="REQUIRES_NEW"/><!-- 独立事务 -->
<tx:method name="*" read-only="true"/>
</tx:attributes>
</tx:advice>

<aop:config>
<aop:pointcut id="serviceOperation" expression="execution(* com.test.service.*Service.*(..))"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceOperation"/>
</aop:config><!--
   
     自动扫描mapper    
    --><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">  
         <property name="basePackage"  value="com.test.dao"/>  
    </bean> 

</beans>

2、jdbc.properties配置文件代码如下：

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@172.20.41.214:1521:ifims
jdbc.username=IFIMS
jdbc.password=IFIMSDEV

#c3p0
jdbc.c3p0.minPoolSize=50
jdbc.c3p0.maxPoolSize=300
jdbc.c3p0.initialPoolSize=100
jdbc.c3p0.maxIdleTime=10
jdbc.c3p0.acquireIncrement=3
jdbc.c3p0.maxStatements=0
jdbc.c3p0.maxStatementsPerConnection=100
jdbc.c3p0.idleConnectionTestPeriod=60
jdbc.c3p0.acquireRetryAttempts=30
jdbc.c3p0.breakAfterAcquireFailure=false
jdbc.c3p0.testConnectionOnCheckout=false

3、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="com.test.model.TSysUsers" alias="TSysUsers"></typeAlias>  
    </typeAliases>  
   <mappers>  
       <mapper resource="com/test/dao/mapper/TSysUsers.xml"></mapper>   
   </mappers>   
</configuration>

4、实体类TSysUsers.java，代码如下：

public class TSysUsers implements java.io.Serializable {
// Fields
private Long userid;
private TSysDepart TSysDepart;
private String loginname;
private String loginpwd;
private String username;
private String cellphone;
private String deptPort;

// Property accessors
public Long getUserid() {
return this.userid;
}
public void setUserid(Long userid) {
this.userid = userid;
}
public TSysDepart getTSysDepart() {
return this.TSysDepart;
}
public void setTSysDepart(TSysDepart TSysDepart) {
this.TSysDepart = TSysDepart;
}
public String getLoginname() {
return this.loginname;
}
public void setLoginname(String loginname) {
this.loginname = loginname;
}
public String getLoginpwd() {
return this.loginpwd;
}
public void setLoginpwd(String loginpwd) {
this.loginpwd = loginpwd;
}
public String getUsername() {
return this.username;
}
public void setUsername(String username) {
this.username = username;
}
public String getCellphone() {
return this.cellphone;
}
public void setCellphone(String cellphone) {
this.cellphone = cellphone;
}
public String getDeptPort() {
return deptPort;
}
public void setDeptPort(String deptPort) {
this.deptPort = deptPort;
}

}

5、sql映射配置文件TSysUsers.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="TSysUsers">
    <select id="queryByLoginName" parameterType="TSysUsers" resultType="TSysUsers">  
        select USERID as userId, LOGINNAME as LOGINNAME, LOGINPWD as LOGINPWD,
        USERNAME as USERNAME, CELLPHONE as CELLPHONE, DEPTPORT as DEPTPORT 
        from USERS t where 1 = 1
        <if test="loginname!=null"> 
        and t.LOGINNAME = #{loginname}
        </if>
    </select> 
 </mapper> 

6、public interface UserDao {
/**
* 登录
* @param loginName 用户名
* @return 用户
*/
public TSysUsers findByAccount(String loginName);
}

7、@Repository("userDaoImpl")
public class UserDaoImpl extends BaseDaoImpl<TSysUsers> implements UserDao {
@Override
public TSysUsers findByAccount(String loginName) {
TSysUsers tSysUsers = new TSysUsers();
tSysUsers.setLoginname(loginName);
List<TSysUsers> users= null;
try {
users = this.sqlSession.selectList("TSysUsers.queryByLoginName", tSysUsers);
} catch (Exception e) {
e.printStackTrace();
}
return users.size() == 0 ? null : users.get(users.size()-1);
}

}

8、public abstract class BaseDaoImpl<T> {
      @Autowired
protected SqlSessionTemplate sqlSession;
    
    public DataGrid queryBase(String sqlId, String countSqlId, Map<String, Object> params, Pager pager) {
        DataGrid result = new DataGrid();
        if (countSqlId != null) {
            result.setTotal(queryCount(countSqlId, params));
        }
        result.setRows(this.sqlSession.selectList(sqlId, params));
        return result;
    }
    
    public Long queryCount(String countSqlId, Map<String, Object> params) {
    Long count =  this.sqlSession.selectOne(countSqlId, params);
    eturn count;
    }
}

其中8中获取SqlSession连接对象也可以这样写：

public abstract class BaseDaoImpl<T> {
       @Autowired
protected SqlSessionFactory sqlSessionFactory;

public getSqlSession() {

return sqlSessionFactory.openSession();

}

}

这样写之后，7中的UserDaoImpl就要变成下面的写法：

@Repository("userDaoImpl")
public class UserDaoImpl extends BaseDaoImpl<TSysUsers> implements UserDao {

@Override
public TSysUsers findByAccount(String loginName) {
TSysUsers tSysUsers = new TSysUsers();
tSysUsers.setLoginname(loginName);
List<TSysUsers> users = null;

SqlSession sqlSession = null;
try {

sqlSession = this.getSqlSession();

users = sqlSession .selectList("TSysUsers.queryByLoginName", tSysUsers);
} catch (Exception e) {
e.printStackTrace();
} finally {

sqlSession.close();

}

return users.size() == 0 ? null : users.get(users.size()-1);
}

}

比较两种写法，第一种更好，因为SqlSessionTemplate你不必手动关闭，SqlSessionTemplate是一个代理类，内部他会为每次请求创建线程安全的sqlsession，并与Spring进行集成.在你的方法调用完毕以后他会自动关闭的，而第二种写法就要手动关闭连接，而且通过SqlSessionFactory获取Sqlsession对象不如SqlSessionTemplate速度快、效率高。