Ibatis+DBCP+Mysql数据库访问

1. 添加ibatis的jar包依赖

<dependency>  
    <groupId>org.apache.ibatis</groupId>  
    <artifactId>ibatis-sqlmap</artifactId>  
    <version>2.3.4.726</version>  
</dependency>  
<dependency>  
    <groupId>com.ibatis</groupId>  
    <artifactId>ibatis-common</artifactId>  
    <version>2.1.0.565</version>  
</dependency>  

2. 新建ibatis-dbcp-ds.xml配置数据源bean, 注意是dbcp.BasicDataSource的实例

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                               http://www.springframework.org/schema/beans/spring-beans.xsd">


    <bean id="dbcpDatasource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mydbname"/>
        <property name="username" value="xxxx"/>
        <property name="password" value="xxxx"/>
    </bean>


</beans>

3. 新建ibatis-dbcp-sqlmap.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >

<sqlMap namespace="galaxy.db.user.sqlmap">
    <typeAlias alias="userDO" type="galaxy.com.db.model.UserDO"/>
    <resultMap id="userDOMap" class="userDO">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="job" column="job"/>
    </resultMap>

    <select id="queryUserById" parameterClass="int" resultMap="userDOMap">
        select * from user where id=#value#
    </select>

    <insert id="insert" parameterClass="userDO">
        insert into user(name, age , job)
        values(#name#, #age#, #job#)
    </insert>
</sqlMap>

4. 将sqlmap.xml注册到sqlMapConfig中

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">


<sqlMapConfig>
    <settings
            cacheModelsEnabled ="true"
            lazyLoadingEnabled="true"
            enhancementEnabled="true"
            errorTracingEnabled="true"
            maxRequests="1000"
            maxSessions="800"
            maxTransactions="500"
            useStatementNamespaces="true"/>

    <sqlMap resource="beans/ibatis/dbcp/sqlmap/ibatis-dbcp-sqlmap.xml"/>

</sqlMapConfig>

5. 新建ibatis-dbcp-context.xml, 实例化sqlMapClient的bean， transaction需要事务的时候可以用到

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                               http://www.springframework.org/schema/beans/spring-beans.xsd">
    <!--导入数据源bean的配置-->
    <import resource="conf/ibatis-dbcp-ds.xml"/>

    <bean id="dbcpSqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="dataSource" ref="dbcpDatasource"/>
        <property name="configLocation" value="beans/ibatis/dbcp/conf/ibatis-dbcp-config.xml"/>
    </bean>

    <bean id="dbcpTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dbcpDatasource"/>
    </bean>

</beans>

6. 数据模型类：

/**
 * Alipay.com Inc.
 * Copyright (c) 2004-2017 All Rights Reserved.
 */
package galaxy.com.db.model;

public class UserDO {
    private Integer id;
    private String name;
    private Integer age;
    private String job;

    /**
     * Getter method for property <tt>id</tt>.
     *
     * @return property value of id
     */
    public Integer getId() {
        return id;
    }

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

    /**
     * Getter method for property <tt>name</tt>.
     *
     * @return property value of name
     */
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    /**
     * Getter method for property <tt>age</tt>.
     *
     * @return property value of age
     */
    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    /**
     * Getter method for property <tt>job</tt>.
     *
     * @return property value of job
     */
    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }
}

7. DAO接口：

/**
 * Alipay.com Inc.
 * Copyright (c) 2004-2017 All Rights Reserved.
 */
package galaxy.com.db.ibatis.dbcp;

import galaxy.com.db.model.UserDO;

public interface DbcpUserDao {
    void insert(UserDO userDO);

    UserDO queryByUserId(Integer id);
}

8. DAO实现类：

/**
 * Alipay.com Inc.
 * Copyright (c) 2004-2017 All Rights Reserved.
 */
package galaxy.com.db.ibatis.dbcp;

import com.ibatis.sqlmap.client.SqlMapClient;
import galaxy.com.db.model.UserDO;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.SQLException;

@Component
public class DbcpUserDaoImpl implements DbcpUserDao {
    @Resource(name = "dbcpSqlMapClient")
    private SqlMapClient dbcpSqlMapClient;
    private String sqlmapNamespace = "galaxy.db.user.sqlmap";



    private String getStatementFullId(String statementId){
        return sqlmapNamespace + "." + statementId;
    }

    @Override
    public void insert(UserDO userDO) {

    }

    @Override
    public UserDO queryByUserId(Integer id) {
        try {
            return (UserDO)dbcpSqlMapClient.queryForObject(getStatementFullId("queryUserById"), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}