1、输入映射和输出映射
1.1 parameterType(输入类型)
1.1.1 传递简单类型
参考Mybatis(一)
1.1.2 传递pojo对象
参考Mybatis(一)
Mybatis使用ognl表达式解析对象字段的值,#{}或者${}括号中的值为pojo属性名称
#{}:占位符,相当于jdbc的问号?
${}:字符串拼接指令,如果入参是普通数据类型{ }内部只写value
1.1.3 传递pojo包装对象
1. 新建包装pojo对象QueryVo
/**
* 包装pojo
* @author Administrator
*/
public class QueryVo {
//用户对象
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
2. 映射文件与sql
<!-- 1、resultType:如果要返回数据集合,只需设定为每一个元素的数据类型
2、 包装的pojo取值通过 "."来获取
-->
<select id="getUserByQueryVo" parameterType="queryvo"
resultType="mybatis.pojo.User">
<!-- SELECT * FROM USER WHERE username LIKE #{name} -->
SELECT * FROM USER WHERE username LIKE '%${user.username}%'
</select>
3. 新增接口方法
public interface UserMapper {
/**
* 传递包装pojo
*
* @param vo
* @return
*/
List<User> getUserByQueryVo(QueryVo vo);
}
4. 增加测试方法,完成测试
@Test
public void testGetUserByQueryVo() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
User user2 = new User();
user2.setUsername("小");
vo.setUser(user2);
List<User> list = userMapper.getUserByQueryVo(vo);
for (User user : list) {
System.out.println(user);
}
sqlSession.close();
}
1.2 resultType(输出类型)
1.2.1 输出简单类型
<!-- 查询用户表数据条数 -->
<select id="queryUserCount" resultType="int">
SELECT count(1) FROM
`user`
</select>
public interface UserMapper {
/**
* 查询用户数据条数
*
* @return
*/
Integer queryUserCount();
}
@Test
public void testQueryUserCount() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int userCount = userMapper.queryUserCount();
System.out.println(userCount);
sqlSession.close();
}
1.2.2 输出pojo对象
参考Mybatis(一)
1.2.3 输出pojo列表
参考Mybatis(一)
1.3 输出resultMap
1. 新建订单pojo
package mybatis.pojo;
import java.util.Date;
public class Order {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number == null ? null : number.trim();
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note == null ? null : note.trim();
}
@Override
public String toString() {
return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ",
createtime=" + createtime + ", note=" + note + "]";
}
}
2. 完成订单列表的查询,先编写OrderMapper.xml,再编写接口与测试代码
<mapper namespace="mybatis.mapper.OrderMapper">
<select id="queryOrderAll" resultType="order">
SELECT
`id`,
`user_id` userId,<!--注:这里使用是别名,因为在pojo中和在数据库中两者名字不同-->
`number`,
`createtime`,
`note`
FROM
`order`
</select>
</mapper>
public interface OrderMapper {
/**
* 查询所有订单
*
* @return
*/
List<Order> queryOrderAll();
}
@Test
public void testQueryOrderAll() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Order> list = orderMapper.queryOrderAll();
for (Order order : list) {
System.out.println(order);
}
sqlSession.close();
}
3. 演示基于完成订单列表的查询,由user_id字段与pojo属性不一致时引出的resultMap。
一种方法是使用上面的别名,还有一种就是配置resultMap属性。
<!--resultMap入门,定义 type:映射到pojo的order,写的是别名;id:唯一标识-->
<resultMap type="order" id="order_list_map">
<!--<id>用于映射主键 -->
<id property="id" column="id" />
<!-- 普通字段用<result>映射 -->
<!--property:主键在pojo的属性名 -->
<!--column:主键在数据库的列名 -->
<result property="userId" column="user_id" />
<result property="number" column="number" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
</resultMap>
<!-- 使用resultMap -->
<select id="queryOrderAllMap" resultMap="order_list_map">
SELECT
`id`,
`user_id`,
`number`,
`createtime`,
`note`
FROM
`order`
</select>
public interface OrderMapper {
/**
* ResultMap使用
*
* @return
*/
List<Order> queryOrderAllMap();
}
@Test
public void testqueryOrderAllMap() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Order> list = orderMapper.queryOrderAllMap();
for (Order order : list) {
System.out.println(order);
}
sqlSession.close();
}
2、动态sql
2.1 由多查询条件拼装引出if标签和where标签
<select id="getUserByPojo" parameterType="user"
resultType="mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
<!-- WHERE username LIKE #{name}; -->
WHERE 1=1
<if test="username != null and username !=''">
and username LIKE '%${username}%'
</if> <if test="sex !=null and sex !=''">
and sex=#{sex}
</if>
<!--加个 1=1 ,后面每句拼接都加上and,保持一致性 -->
</select>
或者
<select id="getUserByPojo" parameterType="user"
resultType="mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
<!--加个 1=1 ,后面每句拼接都加上and,保持一致性 -->
<!--<where>自动补上where关键字,同时处理多个and,
用了where标签就不能再手动加上where关键字 -->
<where>
<if test="username != null and username !=''">
and username LIKE '%${username}%'
</if>
<if test="sex !=null and sex !=''">
and sex=#{sex}
</if>
</where>
</select>
编写接口
public interface UserMapper {
/**
* 演示-if标签的使用
*
* @param user
* @return
*/
List<User> getUserByPojo(User user);
}
编写测试代码
@Test
public void testgetUserByPojo() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setSex("1");
user.setUsername("张");
List<User> list = userMapper.getUserByPojo(user);
for (User user2 : list) {
System.out.println(user2);
}
sqlSession.close();
}
2.2 Sql片段
<!--sql片段的抽取,定义 -->
<sql id="user_sql">
`id`,
`username`,
`birthday`,
`sex`,
`address`
</sql>
SELECT
<!-- sql片段的使用:include:引入sql片段,refid引入片段id -->
<include refid="user_column" />
FROM USER
其它步骤跟前面类似,添加接口方法与测试方法,完成测试。
2.3 Foreach
1. 修改pojo中的QueryVo
/**
* 包装pojo
*
* @author Administrator
*
*/
public class QueryVo {
private User user;
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
2. 配置UserMapper.xml
<!--sql片段的抽取,定义 -->
<sql id="user_sql">
`id`,
`username`,
`birthday`,
`sex`,
`address`
</sql>
<select id="getUserByIds" parameterType="queryvo" resultType="user">
SELECT
<include refid="user_sql"></include>
FROM `user`
<where>
<!--foreach循环标签 -->
<!--collection:要遍历的集合 -->
<!--open:循环开始之前输出的内容 -->
<!--item: 设置循环变量 -->
<!--separator:分隔符 -->
<!--close:循环结束之后输出的内容 -->
<!--目的:id IN(1,25,29,30,55) -->
<foreach collection="ids" open="id IN(" item="uId" separator=","
close=")">
#{uId}
</foreach>
</where>
</select>
3. 编写测试代码
@Test
public void testgetUserByIds() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
// 构建id列表
vo.setIds(Arrays.asList(1, 25, 29, 30, 55));
List<User> list = userMapper.getUserByIds(vo);
for (User user : list) {
System.out.println(user);
}
sqlSession.close();
}
3、关联查询
3.1 一对一关联查询
3.1.1 方法一,使用resultType
1. 新建OrderUser的pojo,继承自Order。
public class OrderUser extends Order {
private String username;
private String address;
…….get,set
}
2. 修改order的映射文件,新增查询方法getOrderUser。
<!-- 一对一关联查询,使用resultType -->
<select id="getOrderUser" resultType="orderuser">
SELECT
o.`id`,
o.`user_id` userId,
o.`number`,
o.`createtime`,
o.`note`,
u.`username`,
u.`address`
FROM `order` o
LEFT JOIN `user` u
ON u.id = o.`user_id`
</select>
3. 编写测试代码
@Test
public void testgetOrderUser() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<OrderUser> list = orderMapper.getOrderUser();
for (OrderUser orderUser : list) {
System.out.println(orderUser);
}
sqlSession.close();
}
3.1.2 方法二,使用resultMap
1. 改造order的pojo
2. 修改order的映射文件
<resultMap type="order" id="order_user_map">
<!--<id>用于映射主键 -->
<id property="id" column="id" />
<!-- 普通字段用<result>映射 -->
<!--property:主键在pojo的属性名 -->
<!--column:主键在数据库的列名 -->
<result property="userId" column="user_id" />
<result property="number" column="number" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
<!--association用于配置一对一关系 -->
<!--property:order里面的User属性 -->
<!--javaType:user的数据类型,支持别名 -->
<association property="user" javaType="mybatis.pojo.User">
<!--id:声明主键,表示user_id是关联查询对象的唯一标识 -->
<!--注意:这里column不能写"id",写id表示order表的主键id,应该写主表的user_id -->
<id property="id" column="user_id" />
<result property="username" column="username" />
<result property="address" column="address" />
<result property="birthday" column="birthday" />
<result property="sex" column="sex" />
</association>
</resultMap>
<!--一对一关联查询:resultMap的使用 -->
<select id="getOrderUserMap" resultMap="order_user_map">
SELECT
o.`id`,
o.`user_id`,
o.`number`,
o.`createtime`,
o.`note`,
u.username,
u.address,
u.birthday,
u.sex
FROM
`order` o
LEFT JOIN
`user` u
ON
o.user_id=u.id
</select>
3. 编写测试代码
@Test
public void testgetOrderUserMap() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Order> list = orderMapper.getOrderUserMap();
for (Order order : list) {
System.out.println(order);
System.out.println(" 此订单的用户为:" + order.getUser());
}
sqlSession.close();
}
3.2 一对多关联查询
1. 改造user的pojo
2. 修改user的映射文件
<!--一对多关联查询 -->
<resultMap type="user" id="user_order_map">
<id property="id" column="id" />
<result property="username" column="username" />
<result property="address" column="address" />
<result property="birthday" column="birthday" />
<result property="sex" column="sex" />
<!--collection:用于配置一对多关联 -->
<!--property:User中的Order属性 -->
<!--ofType:orders的数据类型,支持别名 -->
<collection property="orders" ofType="mybatis.pojo.Order">
<id property="id" column="oid" />
<result property="userId" column="id" />
<result property="number" column="number" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
</collection>
</resultMap>
<!--一对多关联查询 -->
<select id="getUserOrderMap" resultMap="user_order_map">
SELECT
u.`id`,
u.`username`,
u.`birthday`,
u.`sex`,
u.`address`,
u.`uuid2`,
o.id oid,
o.user_id,
o.number,
o.createtime,
o.note
FROM `user` u
LEFT JOIN `order` o
ON
o.user_id=u.id
</select>
3. 编写测试代码
@Test
public void testgetUserOrderMap() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.getUserOrderMap();
for (User user : list) {
System.out.println(user);
for (Order order : user.getOrders()) {
if (order.getId() != null)
System.out.println(" 此用户下的订单:" + order);
}
}
sqlSession.close();
}
4、Mybatis整合spring
4.1 整合思路
1、SqlSessionFactory对象应该放到spring容器中作为单例存在。
2、传统dao的开发方式中,应该从spring容器中获得sqlsession对象。
3、Mapper代理形式中,应该从spring容器中直接获得mapper的代理对象。
4、数据库的连接以及数据库连接池事务管理都交给spring容器来完成。
4.2 整合步骤
- 创建一个java工程。
- 导入jar包。
- mybatis的配置文件sqlmapConfig.xml
- 编写Spring的配置文件
- 1)数据库连接及连接池
- 2)sqlsessionFactory对象,配置到spring容器中
- 3)编写Spring的配置文件
- 复制jdbc.properties配置文件到新工程
- 复制log4j.properties配置文件到新工程
4.3 具体步骤
第一步:创建工程
第二步:导入jar包
第三步:编写SqlMapConfig.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>
<mappers>
<mapper resource="mybatis/user.xml" />
</mappers>
</configuration>
第四步:编写Spring的配置文件applicationContext.xml(传统DAO开发)
a) 复制user.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">
<!-- namespace:命名空间,用于隔离sql语句,后继有重要作用 -->
<!-- #{}:占位符,相当于jdbc的问号? -->
<!-- ${}:字符串拼接指令,如果入参是普通数据类型{ }内部只写value -->
<mapper namespace="user">
<!-- id:sql id,语句的唯一标识 parameterType:入参的数据类型 resultType:返回结果的数据类型 -->
<select id="getUserById" parameterType="int"
resultType="mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
WHERE id=#{id};
</select>
<!-- resultType:如果返回结果为集合时,只需设置为每一个元素的数据类型 -->
<select id="getUserByUserName" parameterType="string"
resultType="mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
<!-- WHERE username LIKE #{name}; -->
WHERE username LIKE '%${value}%';
</select>
<!--useGeneratedKeys:使用自增 -->
<!--keyProperty:与之配套使用,这里是user的主键 -->
<!--插入用户 -->
<insert id="insertUser" parameterType="mybatis.pojo.User"
useGeneratedKeys="true" keyProperty="id">
<!-- useGeneratedKeys="true" keyProperty="id" 等价于下面的 <selectKey keyProperty="id"
resultType="int" order="AFTER"> SELECT LAST_INSERT_ID(); </selectKey>-->
<!-- selectKey:主键返回; -->
<!-- keyProperty:user中的主键属性; -->
<!-- resultType:主键数据类型 ; -->
<!-- order:指定selectKey何时执行;AFTER表示之后 -->
<!-- <selectKey keyProperty="id" resultType="int" order="AFTER"> SELECT
LAST_INSERT_ID(); </selectKey> -->
INSERT INTO `user`
(`username`,
`birthday`,
`sex`,
`address`)
VALUES(
#{username},
#{birthday},
#{sex},
#{address}
) ;
</insert>
</mapper>
b) 在SqlMapConfig.xml加载user.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>
<mappers>
<mapper resource="mybatis/user.xml" />
</mappers>
</configuration>
c) 复制UserDao接口到新工程,并修改,只留下要测试的三个方法
package mybatis.dao;
import java.util.List;
import mybatis.pojo.User;
/**
* 用户信息持久化接口
* @author Administrator
*
*/
public interface UserDao {
/**
* 根据用户ID查询用户信息
* @param id
* @return
*/
User getUserById(Integer id);
/**
* 根据用户名查找用户列表
* @param userName
* @return
*/
List<User> getUserByUserName(String userName);
/**
* 添加用户
* @param user
*/
void insertUser(User user);
}
d) 编写UserDaoImpl实现类,关键是继承SqlSessionDaoSupport
package mybatis.dao.impl;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import mybatis.dao.UserDao;
import mybatis.pojo.User;
public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {
@Override
public User getUserById(Integer id) {
SqlSession sqlSession = super.getSqlSession();
User user = sqlSession.selectOne("user.getUserById", id);
// 因为sqlSession是由spring管理,这里不需要关闭了,否则会报错(后面用在事务中用到)
return user;
}
@Override
public List<User> getUserByUserName(String userName) {
SqlSession sqlSession = super.getSqlSession();
List<User> list = sqlSession.selectList("user.getUserByUserName", userName);
return list;
}
@Override
public void insertUser(User user) {
SqlSession sqlSession = super.getSqlSession();
sqlSession.insert("user.insertUser", user);
}
}
e) 在applicationContext.xml中配置UserDaoImpl实现类
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
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-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util-4.0.xsd">
<!-- 加载配置文件 -->
<context:property-placeholder location="classpath:jdbc.properties" />
<!-- 数据库连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 连接池的最大数据库连接数 -->
<property name="maxActive" value="10" />
<!-- 最大空闲数 -->
<property name="maxIdle" value="5" />
</bean>
<!--配置SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!--加载mybatis核心配置文件 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml" />
<!--配置别名 -->
<property name="typeAliasesPackage" value="mybatis.pojo" />
</bean>
<!--传统Dao配置 -->
<bean id="userDao" class="mybatis.dao.impl.UserDaoImpl">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
</beans>
第五步:编写测试代码
package mybatis.test;
import java.util.Date;
import java.util.List;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import mybatis.dao.UserDao;
import mybatis.pojo.User;
public class UserDaoTest {
private ApplicationContext applicationContext;
@Before
public void init() {
applicationContext = new
ClassPathXmlApplicationContext("classpath:applicationContext.xml");
}
@Test
public void testGetUserById() {
UserDao userDao = (UserDao) applicationContext.getBean("userDao");
User user = userDao.getUserById(30);
System.out.println(user);
}
@Test
public void testGetUserByUserName() {
UserDao userDao = applicationContext.getBean(UserDao.class);
List<User> list = userDao.getUserByUserName("小");
for (User user : list) {
System.out.println(user);
}
}
@Test
public void testInsertUser() {
UserDao userDao = applicationContext.getBean(UserDao.class);
User user = new User();
user.setSex("1");
user.setBirthday(new Date());
user.setAddress("美国纽约");
user.setUsername("万三百");
userDao.insertUser(user);
}
}
4.4 Mapper代理模式开发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">
<!-- namespace:命名空间,用于隔离sql语句,后继有重要作用 -->
<!-- #{}:占位符,相当于jdbc的问号? -->
<!-- ${}:字符串拼接指令,如果入参是普通数据类型{ }内部只写value -->
<!-- 动态代理开发规则: 1、namespace必须是接口的全路径名 2、接口的方法必须与sql id一致 3、接口的入参必须与parameterType类型一致
4、接口的返回值必须与resultType类型一致 -->
<mapper namespace="mybatis.mapper.UserMapper">
<!--sql片段的抽取,定义 -->
<sql id="user_sql">
`id`,
`username`,
`birthday`,
`sex`,
`address`
</sql>
<!-- id:sql id,语句的唯一标识 parameterType:入参的数据类型 resultType:返回结果的数据类型 -->
<select id="getUserById" parameterType="int" resultType="user">
SELECT
<!--sql片段的使用:refid引用定义好的sql片段id -->
<include refid="user_sql"></include>
FROM `user`
WHERE id=#{id};
</select>
<!-- resultType:如果返回结果为集合时,只需设置为每一个的数据类型 -->
<select id="getUserByUserName" parameterType="string"
resultType="mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
<!-- WHERE username LIKE #{name}; -->
WHERE username LIKE '%${value}%';
</select>
<!--useGeneratedKeys:使用自增 -->
<!--keyProperty:与之配套使用,这里是user的主键 -->
<!--插入用户 -->
<insert id="insertUser" parameterType="mybatis.pojo.User"
useGeneratedKeys="true" keyProperty="id">
<!-- selectKey:主键返回; -->
<!-- keyProperty:user中的主键属性; -->
<!-- resultType:主键数据类型 ; -->
<!-- order:指定selectKey何时执行;AFTER表示之后 -->
<!-- <selectKey keyProperty="id" resultType="int" order="AFTER"> SELECT
LAST_INSERT_ID(); </selectKey> -->
INSERT INTO `user`
(`username`,
`birthday`,
`sex`,
`address`)
VALUES(
#{username},
#{birthday},
#{sex},
#{address}
) ;
</insert>
</mapper>
第二步:复制UserMapper接口到新工程,并修改,只留下要测试的三个方法
package mybatis.mapper;
import java.util.List;
import mybatis.pojo.User;
/**
* 用户信息持久化接口
*
* @author Administrator
*
*/
public interface UserMapper {
/**
* 根据用户ID查询用户信息
*
* @param id
* @return
*/
User getUserById(Integer id);
/**
* 根据用户名查找用户列表
*
* @param userName
* @return
*/
List<User> getUserByUserName(String userName);
/**
* 添加用户
*
* @param user
*/
void insertUser(User user);
}
第三步:配置Mapper
第一种方式:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
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-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util-4.0.xsd">
<!-- 加载配置文件 -->
<context:property-placeholder location="classpath:jdbc.properties" />
<!-- 数据库连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 连接池的最大数据库连接数 -->
<property name="maxActive" value="10" />
<!-- 最大空闲数 -->
<property name="maxIdle" value="5" />
</bean>
<!--配置SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!--加载mybatis核心配置文件 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml" />
<!--配置别名 -->
<property name="typeAliasesPackage" value="mybatis.pojo" />
</bean>
<!--动态代理配置方式:第一种 -->
<bean id="baseMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"
abstract="true" lazy-init="true">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
<!-- 用户动态代理接口的配置 -->
<bean id="userMapper" parent="baseMapper">
<property name="mapperInterface" value="mybatis.mapper.UserMapper" />
</bean>
</beans>
第二种方式:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
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-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util-4.0.xsd">
<!-- 加载配置文件 -->
<context:property-placeholder location="classpath:jdbc.properties" />
<!-- 数据库连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 连接池的最大数据库连接数 -->
<property name="maxActive" value="10" />
<!-- 最大空闲数 -->
<property name="maxIdle" value="5" />
</bean>
<!--配置SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!--加载mybatis核心配置文件 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml" />
<!--配置别名 -->
<property name="typeAliasesPackage" value="mybatis.pojo" />
</bean>
<!--动态代理配置方式:第二种:包扫描(推荐使用) -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- basePackage:配置映射包装扫描,多个包时用","或";"分隔 -->
<property name="basePackage" value="mybatis.mapper" />
</bean>
</beans>
第四步:编写测试代码(两种方式测试代码相同)
package mybatis.test;
import java.util.Date;
import java.util.List;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import mybatis.mapper.UserMapper;
import mybatis.pojo.User;
public class UserMapperTest {
private ApplicationContext applicationContext;
@Before
public void init() {
applicationContext = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
}
@Test
public void testGetUserById() {
UserMapper userMapper=(UserMapper) applicationContext.getBean("userMapper");
User user=userMapper.getUserById(30);
System.out.println(user);
}
@Test
public void testGetUserByUserName() {
//在applicationContext.xml中没有配置动态代理的id时,可以这样写,id默认是类名
UserMapper userMapper= applicationContext.getBean(UserMapper.class);
List<User> list = userMapper.getUserByUserName("张");
for(User user:list){
System.out.println(user);
}
}
@Test
public void testInsertUser() {
UserMapper userMapper= applicationContext.getBean(UserMapper.class);
userMapper.insertUser(new User("xxx","2",new Date(),"新疆"));
}
}
5、逆向工程
注意的点:在generatorConfig.xml中配置mapper生成的详细信息,注意改下几点:
1、 添加要生成的数据库表
2、 po文件所在包路径
3、 mapper文件所在包路径
总结:优点
(1)基于SQL语句编程,相当灵活,不会对应用程序或者数据库的现有设计造成任何影响,SQL写在XML里,解除sql与程序代码的耦合,便于统一管理;提供XML标签,支持编写动态SQL语句,并可重用。
(2)与JDBC相比,减少了50%以上的代码量,消除了JDBC大量冗余的代码,不需要手动开关连接;
(3)很好的与各种数据库兼容(因为MyBatis使用JDBC来连接数据库,所以只要JDBC支持的数据库MyBatis都支持)。
(4)能够与Spring很好的集成;
(5)提供映射标签,支持对象与数据库的ORM字段关系映射;提供对象关系映射标签,支持对象关系组件维护。
缺点
(1)SQL语句的编写工作量较大,尤其当字段多、关联表多时,对开发人员编写SQL语句的功底有一定要求。
(2)SQL语句依赖于数据库,导致数据库移植性差,不能随意更换数据库。