MyBatis 查询示例

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

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

将ibatis log4j运行级别调到DEBUG可以在控制台打印出ibatis运行的sql语句,方便调试: ### 设置Logger输出级别和输出目的地 ###log4j.rootLogger=debug,stdout,logfile### 把日志信息输出到控制台 ###log4j.appender.stdout=org.apache.log4j.ConsoleAppender#log4j.appender.stdout.Target=System.errlog4j.appender.stdout.layout=org.apache.log4j.SimpleLayout### 把日志信息输出到文件：jbit.log ###log4j.appender.logfile=org.apache.log4j.FileAppenderlog4j.appender.logfile.File=jbit.loglog4j.appender.logfile.layout=org.apache.log4j.PatternLayoutlog4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %F %p %m%n###显示SQL语句部分log4j.logger.com.ibatis=DEBUGlog4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUGlog4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUGlog4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUGlog4j.logger.java.sql.Connection=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG

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

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

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

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

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. */@Repositorypublic 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. */@Repositorypublic class ArticleDao extends SqlSessionDaoSupport {    public Article selectArticleById(int id) {        return getSqlSession().selectOne("dao.articledao.selectArticleById", id);    }}

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;    }}