转载请注明出处哈: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>