Ibatis+JDBC+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-jdbc-config.xml文件配置db数据源以及指定DO和表的sql映射关系文件路径

如果http://www.ibatis.com/dtd/sql-map-config-2.dtd文件找不到,请自行百度下载到本地目录,然后在idea的preference中搜索DTD标签页进行配置:

<?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"/>


    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
            <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost:3306/mydbname"/>
            <property name="JDBC.Username" value="XXXX"/>
            <property name="JDBC.Password" value="XXXX"/>
            <!--以下为可选项-->
            <!--<property name="JDBC.DefaultAutoCommit" value="true" />-->
            <!--<property name="Pool.MaximumActiveConnections" value="10"/>-->
            <!--<property name="Pool.MaximumIdleConnections" value="5"/>-->
            <!--<property name="Pool.MaximumCheckoutTime" value="120000"/>-->
            <!--<property name="Pool.TimeToWait" value="500"/>-->
            <!--<property name="Pool.PingQuery" value="select 1 from ACCOUNT"/>-->
            <!--<property name="Pool.PingEnabled" value="false"/>-->
            <!--<property name="Pool.PingConnectionsOlderThan" value="1"/>-->
            <!--<property name="Pool.PingConnectionsNotUsedFor" value="1"/>-->
        </dataSource>
    </transactionManager>
    
    <sqlMap resource="beans/ibatis/jdbc/sqlmap/user-sqlmap.xml"/>
</sqlMapConfig>




3. 新建UserSqlMap.xml文件,定义sql和mapping

<?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. 新建ibatis-jdbc-context.xml文件,实例化SqlMapClientFactoryBean,  configLocation属性用前面创建的ibatis-jdbc-config.xml文件路径,而config里面设置的是数据源,这里也就是指定sqlMapClient的数据源

<?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="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="configLocation" value="beans/ibatis/jdbc/conf/ibatis-jdbc-config.xml"/>
    </bean>

</beans>


5. DAO接口:

package galaxy.com.db.ibatis.jdbc;

import galaxy.com.db.model.UserDO;

public interface MUserDAO {
    void insert(UserDO userDO);

    UserDO queryByUserId(Integer id);
}

6. DAO实现类,注入sqlMapClient来访问mysql数据库

package galaxy.com.db.ibatis.jdbc;


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 MUserDAOImpl implements MUserDAO {
    @Resource
    private SqlMapClient sqlMapClient;
    private String sqlmapNamespace = "galaxy.db.user.sqlmap";
    @Override
    public void insert(UserDO userDO) {
        try {
            sqlMapClient.insert(getStatementFullId("insert"), userDO);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


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


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





©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页