转载请注明出处哈:http://carlosfu.iteye.com/blog/2238662
一、mybatis执行日志
加入log4j/logback能看到mybatis更详细的执行情况,以logback为例子
<logback.version>1.0.13</logback.version> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-core</artifactId> <version>${logback.version}</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>${logback.version}</version> </dependency>
配置(logback.xml):默认debug,也可以分开logger去实现(org.apache.ibatis, java.sql)
<?xml version="1.0" encoding="UTF-8"?> <configuration scan="true" scanPeriod="5 seconds"> <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender"> <encoder> <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern> </encoder> </appender> <root level="DEBUG"> <appender-ref ref="STDOUT"/> </root> </configuration>
二、实体别名
在xml配置中的resultType和parameterType都需要写全路径类名,这样不太方便,mybatis提供了别名机制解决这个问题。
1. 在mybatis-base.xml中添加如下(注意要添加到配置的最前头)
<typeAliases> <typeAlias type="com.sohu.tv.bean.Player" alias="Player"/> </typeAliases>
playerMapper.xml在使用resultType和parameterType直接写别名就可以了
<mapper namespace="com.sohu.tv.mapper.playerMapper"> <select id="getPlayer" parameterType="int" resultType="Player"> select id,name,age from players where id=#{id} </select> </mapper>
2. 如果想使得一个package下所有实体类都用简单类名作为别名,可以使用如下配置:
<typeAliases> <!-- <typeAlias type="com.sohu.tv.bean.Player" alias="Player"/> --> <package name="com.sohu.tv.bean"/> </typeAliases>
三、字段冲突:
1. 产生冲突
(1). 建表
CREATE TABLE club( id INT PRIMARY KEY AUTO_INCREMENT, name varchar(20) not null comment '俱乐部名称', info varchar(255) not null comment '俱乐部简介', create_date date comment '创建日期', rank smallint comment '排名' )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='俱乐部表'; INSERT INTO club(name, info, create_date, rank) VALUES('AC', 'AC米兰', '1899-12-26', 5); INSERT INTO club(name, info, create_date, rank) VALUES('Real Madrid', '皇家马德里', '1902-03-06', 1); INSERT INTO club(name, info, create_date, rank) VALUES('Inter', '国际米兰', '1908-03-09', 7);
(2). 实体类
package com.sohu.tv.bean; import java.util.Date; /** * 俱乐部 * * @author leifu * @Date 2015年7月28日 * @Time 下午1:43:53 */ public class Club { /** * 俱乐部id */ private int id; /** * 俱乐部名 */ private String clubName; /** * 俱乐部描述 */ private String clubInfo; /** * 创建日期 */ private Date createDate; /** * 排名 */ private int rank; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getClubName() { return clubName; } public void setClubName(String clubName) { this.clubName = clubName; } public String getClubInfo() { return clubInfo; } public void setClubInfo(String clubInfo) { this.clubInfo = clubInfo; } public Date getCreateDate() { return createDate; } public void setCreateDate(Date createDate) { this.createDate = createDate; } public int getRank() { return rank; } public void setRank(int rank) { this.rank = rank; } @Override public String toString() { return "Club [id=" + id + ", clubName=" + clubName + ", clubInfo=" + clubInfo + ", createDate=" + createDate + ", rank=" + rank + "]"; } }
(3) Dao
package com.sohu.tv.mapper; import java.util.List; import com.sohu.tv.bean.Club; /** * 俱乐部Dao * @author leifu * @Date 2015年8月3日 * @Time 下午2:30:58 */ public interface ClubDao { /** * 获取所有俱乐部信息 * @return */ public List<Club> getAllClubs(); }
(4). 添加clubMapper.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="com.sohu.tv.mapper.ClubDao"> <select id="getAllClubs" resultType="Club"> select id,name,info,create_date,rank from club </select> </mapper>
(5). 添加clubMapper.xml到总配置文件中
<mappers> <mapper resource="mapper/clubMapper.xml" /> </mappers>
(6). 单元测试:
package com.sohu.tv.test.mapper; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; import com.sohu.tv.bean.Club; import com.sohu.tv.mapper.ClubDao; /** * 俱乐部dao测试 * * @author leifu * @Date 2015年8月3日 * @Time 下午2:33:04 */ public class ClubMapperXmlTest extends BaseTest { private SqlSession sqlSession; @Before public void before() { sqlSession = sessionFactory.openSession(true); } @After public void after() { sqlSession.close(); } @Test public void testGetAllClubs() { ClubDao clubDao = sqlSession.getMapper(ClubDao.class); List<Club> clubList = clubDao.getAllClubs(); if (clubList != null && !clubList.isEmpty()) { System.out.println("clubList size: " + clubList.size()); for (Club club : clubList) { System.out.println(club); } } } }
产生错误:
clubList size: 3
Club [id=1, clubName=null, clubInfo=null, createDate=null, rank=5]
Club [id=2, clubName=null, clubInfo=null, createDate=null, rank=1]
Club [id=3, clubName=null, clubInfo=null, createDate=null, rank=7]
Club [id=1, clubName=null, clubInfo=null, createDate=null, rank=5]
Club [id=2, clubName=null, clubInfo=null, createDate=null, rank=1]
Club [id=3, clubName=null, clubInfo=null, createDate=null, rank=7]
2. 冲突解决的三种方法:
1. mysql语句使用别名
<mapper namespace="com.sohu.tv.mapper.clubMapper"> <select id="selectAllClubs" resultType="com.sohu.tv.bean.Club"> select id,name as clubName,info as clubInfo,create_date as createDate,rank from club </select> </mapper>
2. 使用resultMap做转换
<select id="getAllClubs" resultType="Club" resultMap="clubResultMap"> select id,name,info,create_date,rank from club </select> <resultMap type="Club" id="clubResultMap"> <id property="id" column="id"/> <result property="clubName" column="name"/> <result property="clubInfo" column="info"/> <result property="createDate" column="create_date"/> <result property="rank" column="rank"/> </resultMap>
3. 基于约定
以上两种在生产环境都不建议使用,应该按照一个约定实现(例如数据库字段用_隔开,实体类使用驼峰式)
<!--数据库的字段名到pojo类的属性名的自动映射--> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>
例如: create_date 对应 createDate
四、动态sql
1. sql标签:
作用:省去每个sql语句都写一大串字段
<sql id="clubColumns"> id,name,info,create_date,rank </sql> <select id="getAllClubs" resultType="Club"> select <include refid="clubColumns"/> from club </select>
2. where标签
作用: sql中的where语句
(1) ClubDao添加如下方法:
/** * 根据名称查询 * @param name * @return */ public Club getByName(String name);
(2) clubMapper.xml
<select id="getByName" parameterType="string" resultType="Club"> select <include refid="clubColumns"/> from club <where> name = #{name} </where> </select>
(3) 单元测试:
@Test public void testGetByName() { ClubDao clubDao = sqlSession.getMapper(ClubDao.class); Club club = clubDao.getByName("AC"); System.out.println(club); }
3. set(更新字段), choose(判断), where标签综合使用:
(1)ClubDao添加如下接口
import org.apache.ibatis.annotations.Param; /** * 更新俱乐部排名 * @param id * @param rank */ public void updateRank(@Param("id") int id, @Param("rank") int rank);
(2) clubMapper.xml添加对应的sql语句
<update id="updateRank" parameterType="Club"> update club <set> <choose> <when test="rank > 0">rank=#{rank}</when> <otherwise>rank=0</otherwise> </choose> </set> <where> id = #{id} </where> </update>
(3) 单元测试:
@Test public void testUpdateRank() { ClubDao clubDao = sqlSession.getMapper(ClubDao.class); clubDao.updateRank(1, 1000); }
4. foreach语句
示例:
(1) ClubDao添加如下接口
/** * 根据id列表获取俱乐部信息 * @param ids * @return */ public List<Club> getByIds(@Param("ids") List<Integer> ids);
(2) clubMapper.xml添加对应的sql语句
<select id="getByIds" parameterType="list" resultType="Club"> select <include refid="clubColumns"/> from club <where> <foreach collection="ids" item="id" open="AND (" close=")" separator="or"> id=#{id} </foreach> </where> </select>
(3)单元测试
@Test public void testGetByIds() { ClubDao clubDao = sqlSession.getMapper(ClubDao.class); List<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); printClubList(clubDao.getByIds(ids)); } private void printClubList(List<Club> clubList) { if (clubList != null && !clubList.isEmpty()) { System.out.println("clubList size: " + clubList.size()); for (Club club : clubList) { System.out.println(club); } } }
五、properties:
数据库配置可以单独写到一个配置文件中,例如db.properties
football.driver=com.mysql.jdbc.Driver football.url=jdbc:mysql://localhost:3306/football football.username=root football.password=xxxx
mybatis全局配置文件中:
<?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> <properties resource="db.properties" /> <settings> <setting name="useColumnLabel" value="true" /> <setting name="mapUnderscoreToCamelCase" value="true" /> </settings> <typeAliases> <package name="com.sohu.tv.bean" /> </typeAliases> <environments default="development"> <environment id="development"> <!-- 事务使用的是jdbc的方式 --> <transactionManager type="JDBC" /> <!-- 连接池 --> <dataSource type="POOLED"> <property name="driver" value="${football.driver}" /> <property name="url" value="${football.url}" /> <property name="username" value="${football.username}" /> <property name="password" value="${football.password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="mapper/playerMapper.xml" /> <mapper class="com.sohu.tv.mapper.PlayerAnnotationDao" /> <mapper resource="mapper/clubMapper.xml" /> </mappers> </configuration>