MyBatis 教程 ——走马观花
MyBatis教程 ——走马观花
前言
MyBatis 消除 了几乎所有的 JDBC 代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML 或注解用于配置和原始映射,将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java 对象)映射成数据库中的记录。
每个MyBatis应用程序主要都是使用SqlSessionFactory实例的，一个SqlSessionFactory实例可以通过SqlSessionFactoryBuilder获得。SqlSessionFactoryBuilder可以从一个xml配置文件或者一个预定义的配置类的实例获得。
用xml文件构建SqlSessionFactory实例是非常简单的事情。推荐在这个配置中使用类路径资源（classpath resource)，但你可以使用任何Reader实例，包括用文件路径或file://开头的url创建的实例。MyBatis有一个实用类----Resources，它有很多方法，可以方便地从类路径及其它位置加载资源。
MyBatis 最强大的特性之一就是它的动态语句功能。如果您以前有使用JDBC或者类似框架的经历，您就会明白把SQL语句条件连接在一起是多么的痛苦，要确保不能忘记空格或者不要在columns列后面省略一个逗号等。
MyBatis环境配置搭建项目
上来直接构建一个经典的maven web项目。
前提：
#数据库、表
CREATE DATABASE hello_mybatis;
USE hello_mybatis;
CREATE TABLE `t_user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(64) NOT NULL COMMENT '姓名',
`dept` varchar(254) NOT NULL COMMENT '部门',
`phone` varchar(16) NOT NULL COMMENT '电话',
`height` decimal(10,2) DEFAULT NULL COMMENT '身高',
`create_emp` bigint(20) NOT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_emp` bigint(20) DEFAULT NULL COMMENT '修改人',
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';
INSERT INTO `hello_mybatis`.`t_user` (`name`, `dept`, `phone`, `height`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
( '大青山', 'amy empire', '18956563229', '1.85', '1', '2020-03-31 14:17:35', '1', '2020-03-31 14:17:47');
INSERT INTO `hello_mybatis`.`t_user` (`name`, `dept`, `phone`, `height`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
( '池寒枫', 'amy empire', '22056545', '1.83', '1', '2020-03-31 14:17:35', '1', '2020-03-31 14:17:47');
代码示例：
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>
<typeAliases>
<typeAlias alias="User" type="com.self.bean.User" />
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/hello_mybatis" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/self/dao/UserMapper.xml" />
</mappers>
</configuration>
注意：
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: java.io.IOException: Could not find resource com/self/dao/UserMapper
UserMapper.xml
CRUD 的sql在各个对应的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="com.self.dao.UserMapper">
<select id="getUserById" parameterType="int" resultType="User">
select * from `t_user` where id = #{id}
</select>
</mapper>
bean，数据库的映射对象实体类。
public class User {
/**
* id
*/
private int id;
/**
* 名字
*/
private String name;
/**
* 部门，帝国
*/
private String dept;
/**
* 联系号码
*/
private String phone;
/**
* 身高
*/
private BigDecimal height;
/**
* 创建人
*/
private Long createEmp;
/**
* 创建时间
*/
private String createTime;
/**
* 修改人
*/
private Long modifyEmp;
/**
* 修改时间
*/
private String modifyTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
//其他省略
}
main方法。
public class HelloTest {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSessionFactory(){
return sqlSessionFactory;
}
public static void main(String[] args) {
SqlSession sqlSession = getSessionFactory().openSession();
try {
//我们通过调用UserMapper.xml的namespace + id(方法名)来完成数据库访问
User user = (User)sqlSession.selectOne("com.self.dao.UserMapper.getUserById", 1);
System.out.println(JSON.toJSONString(user));
} finally {
sqlSession.close();
}
}
}
输出：
{"dept":"amy empire","height":1.75,"id":1,"name":"艾米","phone":"18956563228"}
配置解释：
- 配置文件 mybatis-config.xml 是 mybatis 用来建立 sessionFactory，里面主要包含了数据库连接相关内容，还有 java 类所对应的别名，比如：
这个别名非常重要，在具体的类的映射中，比如：UserMapper.xml 中 resultType 就是对应这个。 - mybatis-config.xml 里面 的
是包含要映射的类(UserMapper.java)的 xml 配置文件。 - 在UserMapper.xml 文件里面主要是定义各种 SQL 语句，以及这些语句的参数，以及要返回的类型等等。
Mybatis接口注解 (项目中一般不使用)
示例：
创建DAO接口
package com.self.dao;
import com.self.bean.User;
import org.apache.ibatis.annotations.Select;
public interface UserMapper {
@Select("SELECT * FROM t_user where name =#{name}")
public User getUserByName(String name);
}
mybatis-config.xml 配置文件不需要配置mappers，转而在sqlSessionFactory.getConfiguration().addMapper(IUser.class);里添加。
<?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>
<typeAliases>
<typeAlias alias="User" type="com.self.bean.User" />
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/hello_mybatis" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!--<mappers>-->
<!--<mapper resource="com/self/dao/UserMapper.xml" />-->
<!--</mappers>-->
</configuration>
User 实体类不变。
测试类
public class HelloTest {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
//在sqlSessionFactory中添加Mapper
sqlSessionFactory.getConfiguration().addMapper(UserMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSessionFactory(){
return sqlSessionFactory;
}
public static void main(String[] args) {
SqlSession sqlSession = getSessionFactory().openSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.getUserByName("大青山");
System.out.println(JSON.toJSONString(user));
} finally {
sqlSession.close();
}
}
}
Mybatis增删改查（CURD）
示例：
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>
<typeAliases>
<typeAlias alias="User" type="com.self.bean.User" />
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/hello_mybatis" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/self/dao/UserMapper.xml" />
</mappers>
</configuration>
User 实体类不变。
dao层接口
public interface UserMapper {
public User getUserById(Integer id);
public List<User> getUsers();
public void insert(User user);
public void update(User user);
public void deleteById(Integer id);
}
Mapper 映射文件 : src\main\resources\com\self\dao\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">
<mapper namespace="com.self.dao.UserMapper">
<select id="getUserById" parameterType="int" resultType="User">
select * from `t_user` where id = #{id}
</select>
<select id="getUsers" resultType="User">
select * from `t_user` where 1 = 1
</select>
<insert id="insert" parameterType="User">
insert into t_user(`name`, `dept`, `phone`, `height`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
(#{name},#{dept},#{phone},#{height},#{createEmp},#{createTime},#{modifyEmp},#{modifyTime})
</insert>
<delete id="deleteById" parameterType="Integer">
delete from t_user where id = #{id}
</delete>
<update id="update" parameterType="User">
update t_user
<set>
<if test="name != null">
name = #{name},
</if>
<if test="dept != null">
dept = #{dept},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="height != null">
height = #{height},
</if>
<if test="modifyEmp != null">
modify_emp = #{modifyEmp},
</if>
<if test="modifyTime != null">
modify_time = #{modifyTime}
</if>
</set>
where id = #{id}
</update>
</mapper>
测试类
package com.self;
import com.alibaba.fastjson.JSON;
import com.self.bean.User;
import com.self.dao.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
public class HelloTest {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
//在sqlSessionFactory中添加Mapper
//sqlSessionFactory.getConfiguration().addMapper(UserMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSessionFactory(){
return sqlSessionFactory;
}
public static void main(String[] args) {
SqlSession sqlSession = getSessionFactory().openSession();
try {
//我们通过调用UserMapper.xml的namespace + id(方法名)来完成数据库访问
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setCreateEmp(3L);
user.setCreateTime(new Date());
user.setDept("矮人王国");
user.setHeight(new BigDecimal("1.53"));
user.setModifyEmp(3L);
user.setModifyTime(new Date());
user.setPhone("852-253521");
user.setName("霍恩斯");
userMapper.insert(user);
User user1 = new User();
user1.setCreateEmp(3L);
user1.setCreateTime(new Date());
user1.setDept("矮人王国");
user1.setHeight(new BigDecimal("1.53"));
user1.setModifyEmp(3L);
user1.setModifyTime(new Date());
user1.setPhone("852-253521");
user1.setName("霍恩斯-克隆");
userMapper.insert(user1);
sqlSession.commit();
System.out.println("--------------------insert---------------------");
User user2 = userMapper.getUserById(4);
System.out.println("-----getUserById----"+JSON.toJSONString(user2));
User updateUser = new User();
updateUser.setDept("森林矮人王国");
updateUser.setHeight(new BigDecimal("1.56"));
updateUser.setModifyEmp(6L);
updateUser.setModifyTime(new Date());
updateUser.setPhone("852-253521");
updateUser.setName("霍恩斯");
updateUser.setId(user2.getId());
userMapper.update(updateUser);
sqlSession.commit();
System.out.println("--------------------update---------------------");
List<User> users = userMapper.getUsers();
System.out.println("----before delete-----"+JSON.toJSONString(users));
userMapper.deleteById(5);
sqlSession.commit();
System.out.println("--------------------deleteById---------------------");
List<User> afterUsers = userMapper.getUsers();
System.out.println("----before delete-----"+JSON.toJSONString(afterUsers));
} finally {
sqlSession.close();
}
}
}
输出：
--------------------insert---------------------
-----getUserById----{"dept":"矮人王国","height":1.53,"id":4,"name":"霍恩斯","phone":"852-253521"}
--------------------update---------------------
----before delete-----[{"dept":"amy empire","height":1.75,"id":1,"name":"艾米","phone":"18956563228"},{"dept":"amy empire","height":1.85,"id":2,"name":"大青山","phone":"18956563229"},{"dept":"amy empire","height":1.83,"id":3,"name":"池寒枫","phone":"22056545"},{"dept":"森林矮人王国","height":1.56,"id":4,"name":"霍恩斯","phone":"852-253521"},{"dept":"矮人王国","height":1.53,"id":5,"name":"霍恩斯-克隆","phone":"852-253521"}]
--------------------deleteById---------------------
----before delete-----[{"dept":"amy empire","height":1.75,"id":1,"name":"艾米","phone":"18956563228"},{"dept":"amy empire","height":1.85,"id":2,"name":"大青山","phone":"18956563229"},{"dept":"amy empire","height":1.83,"id":3,"name":"池寒枫","phone":"22056545"},{"dept":"森林矮人王国","height":1.56,"id":4,"name":"霍恩斯","phone":"852-253521"}]
注意：在增加，更改，删除的时候需要调用 session.commit() 来提交事务，这样才会真正对数据库进行操作提交保存，否则操作没有提交到数据中。
注意：
1、如果在mybatis-config.xml没有配置mapper的xml文件就会报下面的UserMapper is not known to the MapperRegistry.
Exception in thread "main" org.apache.ibatis.binding.BindingException: Type interface com.self.dao.UserMapper is not known to the MapperRegistry.
at org.apache.ibatis.binding.MapperRegistry.getMapper
2、如果插入的数据与字段不匹配个数会报Column count doesn't match value count at row 1。
### SQL: insert into t_user(`name`, `dept`, `phone`, `height`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES (?,?,?,?,?,?,?,?,?)
### Cause: java.sql.SQLException: Column count doesn't match value count at row 1
Mybatis表关联一对多
准备：
CREATE TABLE `t_weibo` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(10) unsigned NOT NULL COMMENT '用户id',
`title` varchar(254) NOT NULL COMMENT '主题',
`content` text DEFAULT NULL COMMENT '内容',
`create_emp` bigint(20) NOT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_emp` bigint(20) DEFAULT NULL COMMENT '修改人',
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT= 1000 DEFAULT CHARSET=utf8 COMMENT='微博帖子表';
INSERT INTO `hello_mybatis`.`t_weibo` ( `user_id`, `title`, `content`, `create_emp`, `create_time`, `modify_emp`, `modify_time`)
VALUES ( '4', '霍恩斯描述', '由于矮人种族的原因，他无法成为龙骑士，但这并不影响他在小佣兵团以及整个人神大战中快速成为超级巨星，擅长谋略尤擅兵法让他成为矮人的异类。', '2', '2020-04-01 13:47:24', '2', '2020-04-01 13:47:31');
INSERT INTO `hello_mybatis`.`t_weibo` ( `user_id`, `title`, `content`, `create_emp`, `create_time`, `modify_emp`, `modify_time`)
VALUES ( '4', '霍恩斯与莹', '艾米、大青山、霍恩斯、池傲天、沙若等佣兵团骨干可以逃脱，那些培养了2年多的人才必然全军覆没。', '2', '2020-04-01 13:47:24', '2', '2020-04-01 13:47:31');
INSERT INTO `hello_mybatis`.`t_weibo` ( `user_id`, `title`, `content`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
( '2', '大青山描述', '史料记载中第一个神圣巨龙使骑士，在所有的文明中，他都是传统骑士准则的典范，正直、善良、忠诚、守信、怜悯等一切人类美德的拥有者。人龙神三人组合中的一人。在神界受到龙神以黄金凤的胁迫，毅然解除与冰系神圣巨龙使泰穆格尔赛的龙骑士契约，解除龙神的威胁，后与光明神同归于尽。', '2', '2020-04-01 13:47:24', '2', '2020-04-01 13:47:31');
实例：
public class User {
/**
* id
*/
private int id;
/**
* 名字
*/
private String name;
/**
* 部门，帝国
*/
private String dept;
/**
* 联系号码
*/
private String phone;
/**
* 身高
*/
private BigDecimal height;
private List<Weibo> weibos;
/**
* 创建人
*/
private Long createEmp;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改人
*/
private Long modifyEmp;
/**
* 修改时间
*/
private Date modifyTime;
public List<Weibo> getWeibos() {
return weibos;
}
public void setWeibos(List<Weibo> weibos) {
this.weibos = weibos;
}
//其他省略
}
public class Weibo {
/**
* id
*/
private int id;
/**
* 主题
*/
private String title;
/**
* 内容
*/
private String content;
/**
* 用户信息
*/
private User user;
/**
* 创建人
*/
private Long createEmp;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改人
*/
private Long modifyEmp;
/**
* 修改时间
*/
private Date modifyTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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>
<typeAliases>
<typeAlias alias="User" type="com.self.bean.User" />
<typeAlias type="com.self.bean.Weibo" alias="Weibo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/hello_mybatis" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/self/dao/UserMapper.xml" />
</mappers>
</configuration>
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">
<mapper namespace="com.self.dao.UserMapper">
<resultMap id="userMap" type="User">
<result property="id" column="user_id"/>
<result property="name" column="name"/>
<result property="dept" column="dept"/>
<result property="phone" column="phone"/>
<collection property="weibos" ofType="com.self.bean.Weibo" column="user_id">
<!--<id property="id" column="t_weibo.id" javaType="int" jdbcType="INTEGER" />-->
<!--不加表别名或者表名来区分id的话会导致查询不出多条数据-->
<id property="id" column="p.id" javaType="int" jdbcType="INTEGER" />
<result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
<result property="content" column="content" javaType="string" jdbcType="VARCHAR"/>
</collection>
</resultMap>
<select id="getUserInfoById" resultMap="userMap" parameterType="int">
<!-- SELECT u.*,p.*
FROM t_user u, t_weibo p
WHERE u.id = p.user_id AND u.id=#{id}-->
SELECT u.*,p.*
FROM t_user u
inner join t_weibo p on u.id = p.user_id
WHERE u.id=#{id}
</select>
</mapper>
package com.self;
import com.alibaba.fastjson.JSON;
import com.self.bean.User;
import com.self.dao.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
/**
* Copyright (C), 2015-2020, UCAR.INC
* ClassName: HelloTest
* Author: Rongfeng.Lai
* Date: 2020/3/31 14:42
* Description:
* History:
* <author> <time> <version> <desc>
* Rongfeng.Lai 2020/3/31 v1.1.0 描述
*/
public class HelloTest {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
//在sqlSessionFactory中添加Mapper
//sqlSessionFactory.getConfiguration().addMapper(UserMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSessionFactory(){
return sqlSessionFactory;
}
public static void main(String[] args) {
SqlSession sqlSession = getSessionFactory().openSession();
try {
User user = sqlSession.selectOne("com.self.dao.UserMapper.getUserInfoById", 4);
System.out.println("---------"+JSON.toJSONString(user));
} finally {
sqlSession.close();
}
}
}
输出：
---------{"dept":"森林矮人王国","id":4,"name":"霍恩斯","phone":"852-253521","weibos":[{"content":"由于矮人种族的原因，他无法成为龙骑士，但这并不影响他在小佣兵团以及整个人神大战中快速成为超级巨星，擅长谋略尤擅兵法让他成为矮人的异类。","id":4,"title":"霍恩斯描述"}]}
当配置resultMap时jdbcType="TEXT"会报错，要配置成jdbcType="VARCHAR"。
<result property="content" column="content" javaType="string" jdbcType="TEXT"/>
报错：
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is 'com/self/dao/UserMapper.xml'. Cause: org.apache.ibatis.builder.BuilderException: Error resolving JdbcType. Cause: java.lang.IllegalArgumentException: No enum constant org.apache.ibatis.type.JdbcType.TEXT
Mybatis表关联多对一
实例：
实体类bean不变
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">
<resultMap id="weiboMap" type="Weibo">
<result property="id" column="id"/>
<result property="title" column="title"/>
<result property="content" column="content"/>
<association property="user" javaType="User">
<id property="id" column="user_id"/>
<result property="name" column="name"/>
<result property="dept" column="dept"/>
<result property="mobile" column="mobile"/>
</association>
</resultMap>
<select id="getWeiboInfoById" resultMap="weiboMap" parameterType="int">
SELECT u.*,w.*
FROM t_user u
inner join t_weibo w on u.id = w.user_id
WHERE w.id= #{id}
</select>
</mapper>
测试类
Weibo weibo = sqlSession.selectOne("com.self.dao.UserMapper.getWeiboInfoById", 1000);
System.out.println("---------"+JSON.toJSONString(weibo));
输出：
---------{"content":"史料记载中第一个神圣巨龙使骑士，在所有的文明中，他都是传统骑士准则的典范，正直、善良、忠诚、守信、怜悯等一切人类美德的拥有者。人龙神三人组合中的一人。在神界受到龙神以黄金凤的胁迫，毅然解除与冰系神圣巨龙使泰穆格尔赛的龙骑士契约，解除龙神的威胁，后与光明神同归于尽。","id":2,"title":"大青山描述","user":{"dept":"amy empire","id":2,"name":"大青山"}}
Mybatis 多对多
mybatis3.0 添加了association和collection标签专门用于对多个相关实体类数据进行级联查询，但仍不支持多个相关实体类数据的级联保存和级联删除操作。因此在进行实体类多对多映射表设计时，需要专门建立一个关联对象类对相关实体类的关联关系进行描述(映射表 user_side )。
多对多关系本质上还是一对多关系的延伸，各自是一对多关系的联系，因此变成了多对多的关系，在对单个数据处理时还是一对多关系的处理。
前提准备：
CREATE TABLE `t_side` (
`side_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`side_name` varchar(254) NOT NULL COMMENT '势力名称',
`create_emp` bigint(20) NOT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_emp` bigint(20) DEFAULT NULL COMMENT '修改人',
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`side_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT='所属势力表';
INSERT INTO `hello_mybatis`.`t_side` ( `side_name`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
('艾米帝国', '10', '2020-04-01 16:36:25', '10', '2020-04-01 16:36:28');
INSERT INTO `hello_mybatis`.`t_side` ( `side_name`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
('哈米尔王国', '10', '2020-04-01 16:36:25', '10', '2020-04-01 16:36:28');
INSERT INTO `hello_mybatis`.`t_side` ( `side_name`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
('恶魔岛', '10', '2020-04-01 16:36:25', '10', '2020-04-01 16:36:28');
INSERT INTO `hello_mybatis`.`t_side` ( `side_name`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
('精灵王国', '10', '2020-04-01 16:36:25', '10', '2020-04-01 16:36:28');
CREATE TABLE `t_user_side` (
`user_id` int(10) unsigned NOT NULL COMMENT '用户id' ,
`side_id` int(10) unsigned NOT NULL COMMENT '所属势力id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT ='用户势力映射表';
INSERT INTO `hello_mybatis`.`t_user_side` (`user_id`, `side_id`) VALUES ('1', '100');
INSERT INTO `hello_mybatis`.`t_user_side` (`user_id`, `side_id`) VALUES ('1', '101');
INSERT INTO `hello_mybatis`.`t_user_side` (`user_id`, `side_id`) VALUES ('1', '103');
INSERT INTO `hello_mybatis`.`t_user_side` (`user_id`, `side_id`) VALUES ('3', '100');
INSERT INTO `hello_mybatis`.`t_user_side` (`user_id`, `side_id`) VALUES ('2', '100');
sql错误修改
ALTER TABLE t_user_group RENAME t_user_side;
ALTER TABLE t_user_side CHANGE group_id side_id int(10) unsigned NOT NULL COMMENT '所属势力id';
实例：
bean对象
public class User {
/**
* 成员所属势力群
*/
private List<Side> sides;
/**
* id
*/
private int id;
/**
* 名字
*/
private String name;
/**
* 部门，帝国
*/
private String dept;
/**
* 联系号码
*/
private String phone;
/**
* 身高
*/
private BigDecimal height;
//private List<Weibo> weibos;
/**
* 创建人
*/
private Long createEmp;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改人
*/
private Long modifyEmp;
/**
* 修改时间
*/
private Date modifyTime;
}
public class Side {
/**
* 所属势力下成员
*/
private List<User> users;
/**
* 所属势力id
*/
private int sideId;
/**
* 所属势力名称
*/
private String sideName;
/**
* 创建人
*/
private Long createEmp;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改人
*/
private Long modifyEmp;
/**
* 修改时间
*/
private Date modifyTime;
}
public class UserSide {
/**
* 用户id
*/
private int userId;
/**
* 势力id
*/
private int sideId;
}
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>
<typeAliases>
<typeAlias alias="User" type="com.self.bean.User" />
<typeAlias type="com.self.bean.Side" alias="Side"/>
<typeAlias type="com.self.bean.UserSide" alias="UserSide"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/hello_mybatis" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/self/dao/UserMapper.xml" />
<mapper resource="com/self/dao/SideMapper.xml"/>
<mapper resource="com/self/dao/UserSideMapper.xml"/>
</mappers>
</configuration>
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">
<mapper namespace="com.self.dao.UserMapper">
<resultMap id="userMap" type="User">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="phone" column="phone"/>
<collection property="sides" column="user_id" select="com.self.dao.UserSideMapper.getSidesByUserId"/>
</resultMap>
<select id="getUserById" parameterType="int" resultMap="userMap">
select * from `t_user` where id = #{id}
</select>
</mapper>
SideMapper.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.self.dao.SideMapper">
<resultMap id="sideMap" type="Side">
<result property="sideId" column="side_id"/>
<result property="sideName" column="side_name"/>
<collection property="users" column="side_id" select="com.self.dao.UserSideMapper.getUsersBySideId"/>
</resultMap>
<select id="getSideById" parameterType="int" resultMap="sideMap">
select * from `t_side` where side_id = #{sideId}
</select>
<insert id="insert" parameterType="Side">
insert into t_side(`side_name`, `create_emp`, `create_time`, `modify_emp`, `modify_time`) VALUES
(#{sideName},#{createEmp},#{createTime},#{modifyEmp},#{modifyTime})
</insert>
</mapper>
UserSideMapper.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.self.dao.UserSideMapper">
<resultMap id="userSideMap" type="UserSide">
<result property="userId" column="user_id"/>
<result property="sideId" column="side_id"/>
</resultMap>
<resultMap id="userMap" type="User">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="phone" column="phone"/>
</resultMap>
<resultMap id="sideMap" type="Side">
<result property="sideId" column="side_id"/>
<result property="sideName" column="side_name"/>
<collection property="users" column="side_id" select="com.self.dao.UserSideMapper.getUsersBySideId"/>
</resultMap>
<select id="getSidesByUserId" resultMap="sideMap" parameterType="int">
select s.* , us.user_id from t_side s inner join t_user_side us on s.side_id = us.side_id
where us.user_id = #{userId}
</select>
<select id="getUsersBySideId" resultMap="userMap" parameterType="int">
select u.* , us.side_id from t_user u inner join t_user_side us on u.id = us.user_id
where us.side_id = #{sideId}
</select>
<insert id="insert" parameterType="UserSide">
INSERT INTO t_user_side (`user_id`, `group_id`) VALUES (#{userId},#{sideId})
</insert>
</mapper>
//测试类代码
UserSideMapper us = sqlSession.getMapper(UserSideMapper.class);
List<Side> sides = us.getSidesByUserId(1);
StringBuilder sb = new StringBuilder();
for (Side side : sides) {
sb.append(side.getSideName()+", ");
}
System.out.println("----------该成员所属势力-------------"+sb);
List<User> users = us.getUsersBySideId(100);
StringBuilder sb2 = new StringBuilder();
for (User user : users) {
sb2.append(user.getName() + ", ");
}
System.out.println("----------该势力拥有的成员-------------"+sb2);
输出：
----------该成员所属势力-------------艾米帝国, 哈米尔王国, 精灵王国,
----------该势力拥有的成员-------------艾米, 池寒枫, 大青山,
报错：
1、Cannot find class: userMap 原因：
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is 'com/self/dao/UserMapper.xml'. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias 'userMap'. Cause: java.lang.ClassNotFoundException: Cannot find class: userMap
<select id="getUserById" parameterType="int" resultMap="userMap">
select * from `t_user` where id = #{id}
</select>
<!-- 自定义的bean实例类型要指定resultMap，resultType普通bean用的-->
<select id="getUserById" parameterType="int" resultType="userMap">
select * from `t_user` where id = #{id}
</select>
Mybatis与Spring集成
框架搭建的核心就是配置文件。
实例：
pom.xml依赖
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!--=================mybatis框架依赖===============-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!--=================mybatis框架依赖===============-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.60</version>
</dependency>
<!--=================mybatis整合spring依赖===============-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.2</version>
</dependency>
<!--=================mybatis整合spring依赖===============-->
<!--=================spring依赖===============-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>3.2.7.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.3.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.3.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.8.1</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.0.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.0.9.RELEASE</version>
</dependency>
<!--=================spring依赖===============-->
<!--=================切面依赖===============-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.3.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.2</version>
</dependency>
<!--=================切面依赖===============-->
</dependencies>
web.xml 配置spring 和springmvc容器
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1">
<servlet>
<servlet-name>hellomybatis</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>
WEB-INF/hellomybatis-servlet.xml
</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>hellomybatis</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath*:applicationContext.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
</web-app>
hellomybatis-servlet.xml springmvc配置文件
<?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:tx="http://www.springframework.org/schema/tx"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd">
<!--spring mvc容器扫描配置-->
<context:component-scan base-package="com.self" use-default-filters="false">
<context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
</context:component-scan>
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/jsp/"/>
<property name="suffix" value=".jsp"/>
</bean>
</beans>
applicationContext.xml spring配置文件
<?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:tx="http://www.springframework.org/schema/tx"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd">
<!--spring 容器扫描配置-->
<context:component-scan base-package="com.self">
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
</context:component-scan>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/hello_mybatis"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
<!-- 添加事务支持 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 注册事务管理驱动 表示支持声明式事务 @Transactional 注解标注的会被代理实现事务，但要用在有接口的public方法中-->
<!--基于注解的方式使用事务配置声明-->
<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--dataSource连接池-->
<property name="dataSource" ref="dataSource"/>
<!--configLocation属性指定mybatis的核心配置文件-->
<!--如果想把下面整个mybatis-config.xml文件property注释删除了，那么在 UserMapper.xml 这些文件里的type就要配置成全路径的格式如com.self.bean.Side
否则会找不到bean-->
<property name="configLocation" value="classpath:mybatis-config.xml"/>
<!-- 所有配置的mapper文件 该配置相当于是mybatis-config.xml里的mappers配置，在这边直接扫描获取了-->
<property name="mapperLocations" value="classpath*:com/self/dao/*.xml"/>
<!--typeAliasesPackage：批量别名处理 通过这些property就可以把mybatis-config.xml替代掉了-->
<property name="typeAliasesPackage" value="com.self.bean"/>
</bean>
<!-- Mapper代理开发，使用Spring自动扫描MyBatis的接口并装配 （Spring将指定包中的所有被@Mapper注解标注的接口自动装配为MyBatis的映射接口） -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- mybatis-spring组件的扫描器（UserMapper.java），com.dao只需要接口（接口方法与SQL映射文件中的相同） -->
<property name="basePackage" value="com.self.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
</beans>
mybatis-config.xml mybaits配置文件可以不写了
<?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-config.xml可以不用配置，直接通过扫描来配置mapper-->
<!-- <typeAliases>
<typeAlias alias="User" type="com.self.bean.User" />
<typeAlias type="com.self.bean.Side" alias="Side"/>
<typeAlias type="com.self.bean.UserSide" alias="UserSide"/>
<typeAlias type="com.self.bean.Weibo" alias="Weibo"/>
</typeAliases>-->
<!--
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/hello_mybatis" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
-->
<!-- <mappers>
<mapper resource="com/self/dao/UserMapper.xml" />
<mapper resource="com/self/dao/SideMapper.xml"/>
<mapper resource="com/self/dao/UserSideMapper.xml"/>
</mappers>-->
</configuration>
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">
<select id="getUsers" resultType="User">
select * from `t_user` where 1 = 1
</select>
</mapper>
@Repository
//@Mapper
public interface UserMapper {
public List<User> getUsers();
}
public interface UserService {
public List<User> getUsers();
}
@Service("userService")
public class UserServiceImpl implements UserService {
@Override
public List<User> getUsers() {
return userMapper.getUsers();
}
}
@Controller
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/user")
public void getUsers(){
System.out.println(JSON.toJSONString(userService.getUsers()));
}
}
输出：
[{"dept":"amy empire","height":1.75,"id":1,"name":"艾米","phone":"18956563228"},{"dept":"amy empire","height":1.85,"id":2,"name":"大青山","phone":"18956563229"},{"dept":"amy empire","height":1.83,"id":3,"name":"池寒枫","phone":"22056545"},{"dept":"森林矮人王国","height":1.56,"id":4,"name":"霍恩斯","phone":"852-253521"}]
报错：
1、@RequestMapping("/user") 注解用不了。
<!--=================需要依赖 spring-web===============-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.0.9.RELEASE</version>
</dependency>
2、import不了DispatcherServlet
<!--=================需要依赖 spring-webmvc===============-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.0.9.RELEASE</version>
</dependency>
3、No bean named 'userController' available,找不到bean。报这个错是因为使用容器扫描配置时注释了过滤的Controller类型，但是没有把use-default-filters="false" 配置删了，倒置没有过滤的Controller配置的话，那么默认是全部过滤，就没有bean实例被扫描进去了。要改成下面这样。
Exception in thread "main" org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named 'userController' available
at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanDefinition(DefaultListableBeanFactory.java:687)
at org.springframework.beans.factory.support.AbstractBeanFactory.getMergedLocalBeanDefinition(AbstractBeanFactory.java:1207)
<!--spring 容器扫描配置 错误配置-->
<context:component-scan base-package="com.self" use-default-filters="false">
<!--<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>-->
</context:component-scan>
<!--spring 容器扫描配置 正确配置-->
<context:component-scan base-package="com.self" >
</context:component-scan>
context:component-scan除了具有context:annotation-config的功能之外，context:component-scan还可以在指定的package下扫描以及注册javabean 。还具有自动将带有@component,@service,@Repository等注解的对象注册到spring容器中的功能。
因此当使用 context:component-scan 后，就可以将 context:annotation-config移除。
4、Result Maps collection already contains value for com.self.dao.SideMapper.sideMap .集合容器里已经有该类型的值了。这个报错是因为当配置了mybatis-config.xml文件的mappers的同时还注册了MapperScannerConfigurer 的自动扫描MyBatis的接口并装配。注册了两遍。因此只要配置一个就行了，我这边是把mybatis-config.xml文件注释掉了。
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is 'file [F:\self\hellomybatis\target\classes\com\self\dao\SideMapper.xml]'. Cause: java.lang.IllegalArgumentException: Result Maps collection already contains value for com.self.dao.SideMapper.sideMap
<?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-config.xml可以不用配置，直接通过扫描来配置mapper-->
<!-- <typeAliases>
<typeAlias alias="User" type="com.self.bean.User" />
<typeAlias type="com.self.bean.Side" alias="Side"/>
<typeAlias type="com.self.bean.UserSide" alias="UserSide"/>
<typeAlias type="com.self.bean.Weibo" alias="Weibo"/>
</typeAliases>-->
<!--
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/hello_mybatis" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
-->
<!-- <mappers>
<mapper resource="com/self/dao/UserMapper.xml" />
<mapper resource="com/self/dao/SideMapper.xml"/>
<mapper resource="com/self/dao/UserSideMapper.xml"/>
</mappers>-->
</configuration>
5、Error creating bean with name 'userController': Unsatisfied dependency expressed through field 'userService'。注册userControllerbean时依赖的userService不能得到的注入。
这个原因还是出在spring 容器扫描配置配置上，use-default-filters="false" 使用不当。
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userController': Unsatisfied dependency expressed through field 'userService'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'com.self.service.UserService' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
<!--spring 容器扫描配置-->
<context:component-scan base-package="com.self" use-default-filters="false">
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
</context:component-scan>
<!--spring 容器扫描配置-->
<context:component-scan base-package="com.self">
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
</context:component-scan>
use-default-filters 属性的默认值为 true，即使用默认的 Filter 进行包扫描，而默认的 Filter 对标有 @Service,@Controller和@Repository 的注解的类进行扫描。设置为 false，即不使用默认的 Filter 进行扫描。也就是不扫描，只扫描下面配置的include-filter，下面配置的扫描注解有几个就只扫几个，有一个就扫一个。
我们希望 SpringMVC容器 只来控制网站的跳转逻辑，所以我们只希望 SpringMVC 的配置扫描 @Controllerce 注解标注的类，不希望它扫描其余注解标注的类，所以设置了 use-default-filters 为 false，并使用 context:include-filter 子标签设置其只扫描带有 @Controller 注解标注的类。
Spring 容器就不同了，我们希望 Spring 只不扫描带有 @Controller 注解标注的类，而扫描其他注解标注的类，而这时建立在使用默认的 Filter 进行扫描的基础上，设置了 context:exclude-filter 标签，不扫描 @Controller 注解标注的类，所以不应该设置 use-default-filters 为 false 。
use-default-filters="false" 需要和 context:include-filter 一起使用，而不能和 context:exclude-filter 属性一起使用。
MyBatis分页
分页可以分为逻辑分页和物理分页。
逻辑分页是我们的程序在显示每页的数据时，首先查询得到表中的1000条数据，然后根据当前页的“页码”选出其中的100条数据来显示。 物理分页是程序先判断出该选出这1000条的第几条到第几条，然后数据库根据程序给出的信息查询出程序需要的100条返回给我们的程序。
分页待学习其他的方法，这个不好。
MyBatis动态SQL语句
mybatis 的动态sql语句是基于OGNL表达式的。可以方便的在 sql 语句中实现某些逻辑. 总体说来mybatis 动态SQL 语句主要有以下几类:
- if 语句 (简单的条件判断)
- choose (when,otherwize) ,相当于java 语言中的 switch ,与 jstl 中的choose 很类似.
- trim (对包含的内容加上 prefix,或者 suffix 等，前缀，后缀)
- where (主要是用来简化sql语句中where条件判断的，能智能的处理 and or ,不必担心多余导致语法错误)
- set (主要用于更新时)
- foreach (在实现 mybatis in 语句查询时特别有用)
<!-- if语句样式 -->
<select id="getUsersByCond" resultType="User" parameterType="User">
select * from `t_user` where 1 = 1
<if test="name != null">
and name = #{name}
</if>
<if test="phone != null">
and phone = #{phone}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
<!-- choose, when, otherwise -->
<select id="getUsersByCondition" resultType="User" parameterType="User">
select * from `t_user` where 1 = 1
<choose>
<when test="name != null">
AND name like #{name}
</when>
<when test="phone != null">
and phone = #{phone}
</when>
<otherwise>
and dept = #{dept}
</otherwise>
</choose>
</select>
<!-- where where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where元素也知道如何将他们去除,因此可以添加AND OR 连接标签，where元素会自动帮我们适配是否需要，但是AND OR 是不能少的，因为where元素不会帮我们自动添加-->
<select id="getUsersByCondition" resultType="User" parameterType="User">
select * from `t_user`
<where>
<if test="name != null">
and name = #{name}
</if>
<if test="phone != null">
and phone = #{phone}
</if>
<if test="dept != null">
and dept = #{dept}
</if>
</where>
</select>
<!-- 自定义 trim 元素来得到 where标签元素的功能 -->
<trim prefix="" suffix="" suffixOverrides="" prefixOverrides=""></trim>
<!-- prefixOverrides:指定去除多余的前缀内容，prefixOverrides 属性会忽略通过管道分隔的文本序列，例如AND前缀会被去除掉，然后有prefix时插入 prefix 属性中指定的内容。
suffixOverrides:指定去除多余的后缀内容，如：suffixOverrides=","，去除trim标签内sql语句多余的后缀","
prefix:在trim标签内sql语句加上前缀。suffix:在trim标签内sql语句加上后缀。
-->
<select id="getUsersByCondition" resultType="User" parameterType="User">
select * from `t_user`
<trim prefix="where" prefixOverrides="and|or">
<if test="name != null">
and name = #{name}
</if>
<if test="phone != null">
and phone = #{phone}
</if>
<if test="dept != null">
and dept = #{dept}
</if>
</trim>
</select>
<!-- set -->
<update id="update" parameterType="User">
update t_user
<set>
<if test="name != null">
name = #{name},
</if>
<if test="dept != null">
dept = #{dept},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="height != null">
height = #{height},
</if>
<if test="modifyEmp != null">
modify_emp = #{modifyEmp},
</if>
<if test="modifyTime != null">
modify_time = #{modifyTime},
</if>
</set>
where id = #{id}
</update>
<!-- 自定义 trim 元素来得到 set 标签元素的功能 -->
<update id="update" parameterType="User">
update t_user
<trim prefix="set" suffixOverrides=",">
<if test="name != null">
name = #{name},
</if>
<if test="dept != null">
dept = #{dept},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="height != null">
height = #{height},
</if>
<if test="modifyEmp != null">
modify_emp = #{modifyEmp},
</if>
<if test="modifyTime != null">
modify_time = #{modifyTime},
</if>
</trim>
where id = #{id}
</update>
<!-- foreach
可以将一个 List 实例或者数组作为参数对象传给 MyBatis. java bean见下面的写法-->
<select id="getUsersByCondition" resultType="User" parameterType="User">
select * from `t_user`
<where>
<if test="name != null">
and name = #{name}
</if>
<if test="phone != null">
and phone = #{phone}
</if>
<if test="dept != null">
and dept = #{dept}
</if>
<if test="ids != null">
and id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
<!-- bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文,比如用于添加模糊查询% -->
<select id="getUsersByCondition" resultType="User" parameterType="User">
<bind name="deptBlur" value="'%' + dept + '%'"></bind>
select * from `t_user`
<where>
<if test="name != null">
and name = #{name}
</if>
<if test="phone != null">
and phone = #{phone}
</if>
<if test="dept != null">
and dept like #{deptBlur}
</if>
<if test="ids != null">
and id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
<!-- Multi-db vendor support 一个配置了“_databaseId”变量的 databaseIdProvider 对于动态代码来说是可用的，这样就可以根据不同的数据库厂商构建特定的语句 -->
insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
foreach查询的写法
public class User {
/**
* 成员所属势力群
*/
private List<Side> sides;
/**
* id
*/
private int id;
private List<Integer> ids;
private Integer[] idsArr;
/**
* 名字
*/
private String name;
/**
* 部门，帝国
*/
private String dept;
/**
* 联系号码
*/
private String phone;
/**
* 身高
*/
private BigDecimal height;
//private List<Weibo> weibos;
/**
* 创建人
*/
private Long createEmp;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改人
*/
private Long modifyEmp;
/**
* 修改时间
*/
private Date modifyTime;
public Integer[] getIdsArr() {
return idsArr;
}
public void setIdsArr(Integer[] idsArr) {
this.idsArr = idsArr;
}
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
测试类：
User user = new User();
Integer[] idsArr = {1,2,3,4};
user.setIdsArr(idsArr);
user.setDept("amy empire");
uc.getUserCondition(user);
//===============================================
User user = new User();
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
user.setIds(ids);
user.setDept("amy empire");
uc.getUserCondition(user);
mybaits if标签语句
实例：
<select id="getUsersByCondition" resultType="User" parameterType="User">
select * from `t_user`
<where>
<if test="name != null and name !='' ">
and name = #{name,jdbcType=VARCHAR}
</if>
<if test="phone != null and phone !='' ">
and phone = #{phone,jdbcType=VARCHAR}
</if>
<if test="dept != null and dept != '' ">
<!-- 另外一种模糊查询%的拼接方法 -->
and dept like CONCAT(CONCAT('%', #{dept,jdbcType=VARCHAR}),'%')
</if>
<if test="ids != null">
and id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
错误：
Exception in thread "main" org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.builder.BuilderException: Error evaluating expression 'name != null AND name !='' '. Cause: org.apache.ibatis.ognl.ExpressionSyntaxException: Malformed OGNL expression: name != null AND name !='' [org.apache.ibatis.ognl.ParseException: Encountered " <IDENT> "AND "" at line 1, column 14.
Was expecting one of:
<if test="phone != null and phone !='' ">
AND phone = #{phone,jdbcType=VARCHAR}
</if>
<!-- 下面才是正确写法 -->
<if test="phone != null and phone !='' ">
and phone = #{phone,jdbcType=VARCHAR}
</if>
！和= 之间多了一个空格也会报错，if test="phone ! = null and phone ! ='' 。应该去掉空格
<!-- 下面才是正确写法 -->
<if test="phone != null and phone !='' ">
and phone = #{phone}
</if>
MyBatis where标签语句
“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果where标签返回的内容是以 AND 或OR 开头的，则它会剔除掉。
Mybatis set标签
当 update 语句中没有使用 if 标签时，如果有一个参数为 null，都会导致错误。
当在 update 语句中使用if标签时，如果前面的if没有执行，则或导致逗号多余错误。使用set标签可以将动态的配置 SET 关键字，并剔除追加到条件末尾的任何不相关的逗号。使用 if+set 标签修改后，如果某项为 null 则不进行更新，而是保持数据库原值。
报错：如果 set 包含的内容为空的话则会出错 ,如下。
update t_user where id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where id = 2' at line 3
;
Mybatis trim标签
trim 是更灵活用来去处多余关键字的标签，它可以用来实现 where 和 set 的效果。trim 元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是 prefix 和 suffix；可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是 prefixOverrides 和 suffixOverrides；正因为 trim 有这样的功能，所以我们也可以非常简单的利用 trim 来代替 where 元素的功能。
choose (when, otherwise)标签
有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。而使用if标签时，只要test中的表达式为 true，就会执行 if 标签中的条件。MyBatis 提供了 choose 元素。if标签是与(and)的关系，而 choose 是或(or)的关系。
choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。
MyBatis SqlSessionDaoSupport实例
待验证。没用
MyBatis打印输出SQL语句
实例：
log4j.properties
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
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=C:/mybatis_show_sql.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
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
pom.xml依赖
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.2</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.21</version>
</dependency>
输出：
DEBUG - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@647f63b4] was not registered for synchronization because synchronization is not active
DEBUG - Fetching JDBC Connection from DataSource
DEBUG - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/hello_mybatis]
DEBUG - JDBC Connection [com.mysql.jdbc.JDBC4Connection@481b3df0] will not be managed by Spring
DEBUG - ==> Preparing: select * from `t_user` WHERE id in ( ? , ? , ? )
DEBUG - ==> Parameters: 1(Integer), 2(Integer), 3(Integer)
DEBUG - <== Total: 3
DEBUG - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@647f63b4]
DEBUG - Returning JDBC Connection to DataSource
ERROR - [{"dept":"amy empire","height":1.75,"id":1,"name":"大青山","phone":"18956563228"},{"dept":"amy empire","height":1.85,"id":2,"name":"艾米哈珀","phone":"18956563228"},{"dept":"amy empire","height":1.83,"id":3,"name":"池寒枫","phone":"22056545"}]
疑问
Q:什么是OGNL表达式？
A:对象导航图语言（Object Graph Navigation Language），简称OGNL，是应用于Java中的一个开源的表达式语言（Expression Language），它被集成在mybatis等框架中，作用是对数据进行访问，它拥有类型转换、访问对象方法、操作集合对象等功能。
Q:像下面这种变量要怎么配置各个环境的变量，方便替换，怎么读取到相应的properties文件？value="${driver}"
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
Q:MyBatis ofType和javaType区别?
A:JavaType和ofType都是用来指定对象类型的，但是JavaType是用来指定pojo中属性的类型，而ofType指定的是映射到list集合属性中pojo的类型。
Q:下面两个resultMap有什么区别么？为什么要多指导jdbcType 和javaType,去掉这两个后运行程序还是正常，那这两个有什么作用？
<resultMap id="userMap" type="User">
<result property="id" column="user_id"/>
<result property="name" column="name"/>
<result property="dept" column="dept"/>
<result property="phone" column="phone"/>
<collection property="weibos" ofType="com.self.bean.Weibo" column="user_id">
<!--<id property="id" column="t_weibo.id" javaType="int" jdbcType="INTEGER" />-->
<!--不加表别名或者表名来区分id的话会导致查询不出多条数据-->
<id property="id" column="p.id" />
<result property="title" column="title" />
<result property="content" column="content" />
</collection>
</resultMap>
<!--===========================分割===========================-->
<resultMap id="userMap" type="User">
<result property="id" column="user_id"/>
<result property="name" column="name"/>
<result property="dept" column="dept"/>
<result property="phone" column="phone"/>
<collection property="weibos" ofType="com.self.bean.Weibo" column="user_id">
<!--<id property="id" column="t_weibo.id" javaType="int" jdbcType="INTEGER" />-->
<!--不加表别名或者表名来区分id的话会导致查询不出多条数据-->
<id property="id" column="p.id" javaType="int" jdbcType="INTEGER" />
<result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
<result property="content" column="content" javaType="string" jdbcType="VARCHAR"/>
</collection>
</resultMap>
Q:@Mapper注解的作用是什么？
@Mapper
public interface UserMapper {}
Q:在写mybatissql时为什么查询绑定的值还要指定上jdbcType类型呢？不写也是可以的，写和不写的区别？如下
and phone = #{phone,jdbcType=VARCHAR}
Q:有空研究下log4j.properties上的具体配置如何操作，自定义，形成文档。
