环境搭建
数据库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; } }