MyBatis 查询示例

环境搭建

数据库schema

 

1)datasource.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!-- SqlSeesionTemplate是线程安全的,可以被多个Dao共享,可以使用Singleton -->
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <!-- 第一个参数是 sqlSessionFactory -->
        <constructor-arg index="0" ref="sqlSessionFactory"/>
        <!-- 第二个参数是 ExecutorType -->
        <constructor-arg index="1" value="BATCH"/>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 指定数据源 -->
        <property name="dataSource" ref="dataSource" />
        <!-- 指定MyBatis配置文件 -->
        <property name="configLocation" value="classpath:mybatis-config.xml" />
        <!-- 导入Mapper -->
        <property name="mapperLocations" value="classpath:mappers/*.xml" />
    </bean>

    <!-- datasource -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatistest?characterEncoding=utf8" />
        <property name="username" value="root" />
        <property name="password" value="root" />
    </bean>

</beans>

 

 

2.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"
       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">
    <context:component-scan base-package="dao" />
    <context:component-scan base-package="bean" />

    <bean id="userDao" class="dao.UserDao">
        <!--<property name="sqlSessionFactory" ref="sqlSessionFactory" />-->
        <property name="sqlSessionTemplate" ref="sqlSession" />
    </bean>

    <bean id="articleDao" class="dao.ArticleDao">
        <property name="sqlSessionTemplate" ref="sqlSession" />
    </bean>

    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
        <property name="prefix" value="/" />
        <property name="suffix" value=".jsp" />
    </bean>

</beans>

 

 

3. log4j.properties

将ibatis log4j运行级别调到DEBUG可以在控制台打印出ibatis运行的sql语句,方便调试: 

### 设置Logger输出级别和输出目的地 ###
log4j.rootLogger=debug,stdout,logfile


### 把日志信息输出到控制台 ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
#log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout


### 把日志信息输出到文件：jbit.log ###
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=jbit.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %F %p %m%n


###显示SQL语句部分
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

 

4.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>
    <!-- MyBatis 配置 -->
    <settings>
        <setting name="cacheEnabled" value="true"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="multipleResultSetsEnabled" value="true"/>
        <setting name="useColumnLabel" value="true"/>
        <setting name="useGeneratedKeys" value="false"/>
        <setting name="autoMappingBehavior" value="PARTIAL"/>
        <setting name="defaultExecutorType" value="BATCH"/>
        <setting name="defaultStatementTimeout" value="25"/>
        <setting name="safeRowBoundsEnabled" value="false"/>
        <setting name="mapUnderscoreToCamelCase" value="false"/>
        <setting name="localCacheScope" value="SESSION"/>
        <setting name="jdbcTypeForNull" value="OTHER"/>
        <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
    </settings>
    
    <!-- 类别名设置 -->
    <typeAliases>
        <typeAlias alias="User" type="bean.User"/>
        <typeAlias alias="Article" type="bean.Article"/>
        <typeAlias alias="Post" type="bean.Post"/>
    </typeAliases>
</configuration>

 

查询代码示例

BaseMapper.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="dao.base">
    <resultMap id="userResultMap" type="User">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="address" column="address" />
    </resultMap>

    <resultMap id="postResultMap" type="Post">
        <id property="id" column="id" />
        <result property="content" column="content" />
    </resultMap>

    <!-- 复杂类型的ResultMap构造 -->
    <resultMap id="articleResultMap" type="Article">
        <id property="id" column="article_id" />
        <result property="title" column="article_title" />
        <result property="content" column="article_content" />
        <!-- association表示单个外键对象使用 -->
        <!-- property为Article里的成员 -->
        <!-- columnprefix表示在userResultMap里的column全都加上prefix,以跟select语句里column label的匹配 -->
        <association property="user" resultMap="userResultMap" columnPrefix="user_"/>
        <!-- collection表示多个的外键对象List,例如一对多关系 -->
        <!-- 此处表示一个Article对应多个Post -->
        <collection property="postList" resultMap="postResultMap" columnPrefix="post_" />
    </resultMap>
</mapper>

 

UserMapper.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">

<!-- 对应类的SQL语句 -->
<mapper namespace="dao.userdao">
    <!-- 简单查询示例 -->
    <!-- Select的flushCache默认为false -->
    <!-- useCache默认为true,statementType默认为PREPARED -->
    <!-- MyBatis结果集的映射规则就是使用column label(没有则直接使用column) -->
    <!-- 去对应resultType里的property名字并赋值(除了使用resultMap手动控制映射) -->
    <select id="selectUserByID" parameterType="int" resultType="User"
            flushCache="false" useCache="true" timeout="10000" statementType="PREPARED">
        SELECT
            *
        FROM user
        WHERE id = #{id}
    </select>
    
    <!-- 简单插入示例 -->
    <!-- 此处值得注意的是 userGenerateKeys,keyProperty,keyColumn -->
    <!-- 这三个值表示使用jdbc自动生成主键并赋给数据库里的column和实体的property -->
    <!-- flushCache默认为true,statmentType默认为PREPARED -->
    <insert id="insertUser" parameterType="User" timeout="10000"
            flushCache="true" statementType="PREPARED" useGeneratedKeys="true"
            keyProperty="id" keyColumn="id">
        INSERT INTO user (name, age, address)
        VALUES (#{name}, #{age}, #{address})
    </insert>

    <!-- 简单更新示例 -->
    <update id="updateUser" parameterType="User">
        UPDATE user SET
           name = #{name},
           age = #{age},
           address = #{address}
    </update>

    <!-- 简单删除 -->
    <delete id="deleteUser" parameterType="int">
        DELETE FROM user
        WHERE id = #{id}
    </delete>

    <!-- Cache 配置 -->
    <!-- 根据官方文档的介绍
        By default, just local sessión caching is enabled that is used solely to cache data for the duration of a sessión.
        To enable a global second level of caching you simply need to add one line to your SQL Mapping file
        需要加上这个cache标签才能使用全局的cache,否则只能使用session范围内的一级缓存,实际上在spring中根本无法使用一级缓存 -->
    <cache
        eviction="FIFO"
        flushInterval="60000"
        size="512"
        readOnly="true" />
</mapper>

 

ArticleMapper.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="dao.articledao">
    <!-- select里的column label必须与resultMap里的column(或者加上prefix的column)匹配 -->
    <select id="selectArticleById" parameterType="int" resultMap="dao.base.articleResultMap">
        SELECT
          a.id AS article_id,
          a.title AS article_title,
          a.content AS article_content,
          a.user_id AS user_id,
          u.name AS user_name,
          u.age AS user_age,
          u.address AS user_address,
          p.id AS post_id,
          p.content AS post_content
        FROM article AS a
        JOIN user AS u
          ON a.user_id = u.id
        JOIN post AS p
          ON a.id = p.article_id
        WHERE a.id = #{id}
    </select>
</mapper>

 

 

Dao

package dao;

import bean.User;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.stereotype.Repository;

/**
 * Created with IntelliJ IDEA.
 * User: zhenwei.liu
 * Date: 13-8-3
 * Time: 下午9:21
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class UserDao extends SqlSessionDaoSupport {
    public User selectUserById(int id) {
        return getSqlSession().selectOne("dao.userdao.selectUserByID", id);
    }
}


package dao;

import bean.Article;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.stereotype.Repository;

/**
 * Created with IntelliJ IDEA.
 * User: zhenwei.liu
 * Date: 13-8-5
 * Time: 下午12:37
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class ArticleDao extends SqlSessionDaoSupport {
    public Article selectArticleById(int id) {
        return getSqlSession().selectOne("dao.articledao.selectArticleById", id);
    }
}

 

bean

package bean;

public class User {

    private Integer id;


    private String name;


    private Integer age;


    private String address;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return id + "|" + name + "|" + age + "|" + address;
    }
}

package bean;


public class Post {
    private String id;
    private String content;

    public String getId() {
        return id;
    }

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

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    @Override
    public String toString() {
        return content;
    }
}

package bean;

import java.util.List;

public class Article {
    
    private Integer id;

    
    // user属于单个外键复杂对象
    private User user;

    
    private String title;

    
    private String content;

    // postList属于一对多关系复杂对象
    private List<Post> postList;

    
    public Integer getId() {
        return id;
    }

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

    
    public User getUser() {
        return user;
    }

    
    public void setUser(User user) {
        this.user = user;
    }

    
    public String getTitle() {
        return title;
    }

    
    public void setTitle(String title) {
        this.title = title == null ? null : title.trim();
    }

    
    public String getContent() {
        return content;
    }

    
    public void setContent(String content) {
        this.content = content == null ? null : content.trim();
    }

    public List<Post> getPostList() {
        return postList;
    }

    public void setPostList(List<Post> postList) {
        this.postList = postList;
    }

    @Override
    public String toString() {
        return title + "|" + content + "|" + user + "|" + postList;
    }
}