MyBatis提高
一、输入映射和输出映
1. parameterType（输入类型）
- 传递简单类型
- 传递pojo对象
- 传递pojo包装类
- 包装类的编写
package com.lld.MyBatis.Model; import java.io.Serializable; public class QueryVo implements Serializable{ /** * */ private static final long serialVersionUID = 1L; private User user; public void setUser(User user) { this.user = user; } } - 调用
@Test public void testMapperFindUserByName() throws Exception { //加载核心配置文件 String resource = "sqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); //创建SQLsessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); //创建SQLsession SqlSession session = sqlSessionFactory.openSession(); //使用session获取UserMapper的实现类（传进去一个接口返回一个接口类型的，但是底层已经帮你创建好了实现类） UserMapper userMapper = session.getMapper(UserMapper.class); //调用方法 User user = new User(); user.setUsername("五"); QueryVo vo = new QueryVo(); vo.setUser(user); List<User> list = userMapper.fingUserByUsername(vo); //遍历获得数据 for (User u : list) { System.out.println(u); } }
- 包装类的编写
2. resultType（输出类型）
- 简单类型
- 输出pojo对象
- 输出pojo列表
- resultType
<mapper namespace="com.lld.MyBatis.Mapper.OrdersMapper">
<!--
resultMap里的id要和select里的resultMap相同
在类型不匹配的时候使用resultMap
resultMap里的result只需要添加名称不匹配的列即可
-->
<resultMap type="Orders" id="orders">
<!--
column：数据库表中的值
property：编写映射model中的值
-->
<result column="user_id" property="userId"/>
</resultMap>
<select id="selectOrdersList" resultMap="orders">
SELECT id, user_id, number, createtime, note FROM orders
</select>
</mapper>
二、动态SQL
if和where
<!-- 根据性别和名字查询用户 -->
<select id="findUserBySexAndUsername" parameterType="User" resultType="User">
select * from user
<!-- where标签可以自动添加where，同时处理sql语句中第一个and关键字 -->
<where>
<!-- 判断是否为空和是否为空字符串 -->
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="username != null and username != ''">
and username = #{username}
</if>
</where>
</select>
sql片段
将一些公共的SQL语句提取成为一个SQL片段，在需要用的时候将其引入
- 引用本mapper中的SQL片段
- 引用别的mapper中的SQL片段
在refid前面加上对应的Mapper.xml的namespace
foreach
- UserMapper.java
public List findUserByIDs(QueryVo vo);
- UserMapper.xm;
<!-- 根据多个id查询用户信息 -->
<select id="findUserByIDs" parameterType="QueryVo" resultType="User">
<include refid="userFields"></include>
<where>
id in
<!--
collection="listids" ：遍历QueryVo里面的listids
item="id" ：遍历出来的值赋给id
open="(" ：在开始便利时加(
close=")" ：在结束便利时加)
separator="," ：在便利出一个值后加一个,
最后出现的结果(1,2,3....)
-->
<foreach collection="listids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</where>
</select>
- Queryvo.java
- Junit.java
@Test
//根据多个id查询用户信息
public void testMapperfindUserByIDs() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SQLsessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SQLsession
SqlSession session = sqlSessionFactory.openSession();
//使用session获取UserMapper的实现类（传进去一个接口返回一个接口类型的，但是底层已经帮你创建好了实现类）
UserMapper userMapper = session.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
List<Integer> list = new ArrayList<Integer>();
list.add(26);
list.add(27);
list.add(30);
vo.setListids(list);
List<User> users = userMapper.findUserByIDs(vo);
for (User user : users) {
System.out.println(user);
}
}
- 注意
三、关联查询
一对一关联查询
- ordersMapper.xml文件
<!--
SELECT
o.id,
o.user_id,
o.number,
o.createtime,
o.note,
u.username,
u.address
FROM orders o
LEFT JOIN USER u
ON o.user_id = u.id
-->
<!-- 当配置一对一时，只要resultMap中出现association，则orders表里的所有的字段都要进行配置 -->
<resultMap type="Orders" id="order">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!--
配置一对一
property：对应orders.java中的user
javaType：对应User.java
-->
<association property="user" javaType="User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
</association>
</resultMap>
<select id="selectOrders" resultMap="order">
SELECT
o.id,
o.user_id,
o.number,
o.createtime,
o.note,
u.username,
u.address
FROM orders o
LEFT JOIN user u
ON o.user_id = u.id
</select>
- OrdersMapper.java文件
//查询所有订单信息，关联查询下单用户信息。
public List<Orders> selectOrders();
- Junit.java文件
@Test
//查询所有订单信息，关联查询下单用户信息。
public void testMapperselectOrders() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SQLsessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SQLsession
SqlSession session = sqlSessionFactory.openSession();
//使用session获取OrdersMapper的实现类（传进去一个接口返回一个接口类型的，但是底层已经帮你创建好了实现类）
OrdersMapper ordersMapper = session.getMapper(OrdersMapper.class);
List<Orders> orders = ordersMapper.selectOrders();
for (Orders order : orders) {
System.out.println(order);
}
}
- 运行结果
一对多关联查询
- ordersMapper.xml
<!-- -->
<resultMap type="User" id="user">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<!--
配置一对多
property：对应user.java中的user
ofType：对应user.java里面的list集合里面的泛型
-->
<collection property="ordersList" ofType="Orders">
<id column="id" property="id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
</collection>
</resultMap>
<select id="selectUsers" resultMap="user">
SELECT
o.id,
o.user_id,
o.number,
o.createtime,
o.note,
u.username
FROM user u
LEFT JOIN orders o
ON o.user_id = u.id
</select>
- ordersMapper.java
//查询所有用户信息及用户关联的订单信息。
public List selectUsers();
- User.java
- Junit.java
@Test
//查询所有用户信息及用户关联的订单信息。
public void testMapperselectUser() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SQLsessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SQLsession
SqlSession session = sqlSessionFactory.openSession();
//使用session获取OrdersMapper的实现类（传进去一个接口返回一个接口类型的，但是底层已经帮你创建好了实现类）
OrdersMapper ordersMapper = session.getMapper(OrdersMapper.class);
List<User> Users = ordersMapper.selectUsers();
for (User user : Users) {
System.out.println(user);
}
}
四、MyBatis整合Spring
传统方式整合
1.创建项目
2.导入jar包
3.创建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>
4.创建applicationContext.xml
<?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">
<!-- 配置DataSource -->
<property name="dataSource" ref="dataSource"/>
<!-- 加载MyBatis核心配置文件 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
<!-- 配置package包扫描 -->
<property name="typeAliasesPackage" value="com.lld.model"/>
</bean>
<!-- 传统Dao配置 -->
<bean class="com.lld.Dao.Impl.UserDaoImpl">
<property name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>
</beans>
5. 创建model
package com.lld.model;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address + "]";
}
}
6. 创建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="Integer" resultType="User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
WHERE id = #{id2}
</select>
</mapper>
7.创建dao以及daoimpl
- dao
package com.lld.Dao;
import com.lld.model.User;
public interface UserDao {
public User findUserById(Integer id);
}
- daoimpl
package com.lld.Dao.Impl;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import com.lld.Dao.UserDao;
import com.lld.model.User;
public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {
@Override
public User findUserById(Integer id) {
//获得session
SqlSession session = this.getSqlSession();
User user = session.selectOne("user.getUserById", id);
//将User返回
return user;
}
}
8.创建测试类
package com.lld.test;
import static org.junit.Assert.*;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.lld.Dao.UserDao;
import com.lld.model.User;
public class UserDaoTest {
private ApplicationContext applicationContext;
@Before
public void init(){
applicationContext = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
}
@Test
public void testFindUserById() {
UserDao userDao = applicationContext.getBean(UserDao.class);
User user = userDao.findUserById(30);
System.out.println(user);
}
}
动态代理开发（使用mapper）
1.创建mapper
- usermapper.java
package com.lld.mapper;
import com.lld.model.User;
public interface UserMapper {
/*
* 使用动态代理开发mybatis要遵循四大原则
* 1. 方法名要与User.xml中的每个sql的id相同
* 2. 返回值类型要与调用sql的resultType相同
* 3. 输入类型要与调用sql的parameterType相同
* 4. mapper中的namespace要是mapper接口的全路径地址
*/
public User fingUserById(Integer id);
}
- 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：在调用的时候使用（namespace.id）
-->
<mapper namespace="com.lld.mapper.UserMapper">
<!--
配置select：通过id查询一个用户
parameterType：输入参数类型
resultType：返回值类型
-->
<select id="fingUserById" parameterType="Integer" resultType="User">
select * from user where id = #{v}
</select>
</mapper>
2.在applicationContest.xml里面添加以下内容
<!-- 动态代理配置方式一 -->
<!-- <bean id="baseMapper" class="org.mybatis.spring.mapper.MapperFactoryBean" abstract="true" lazy-init="true">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean> -->
<!-- 配置一个包的接口 -->
<!-- <bean parent="baseMapper">
<property name="mapperInterface" value="com.lld.mapper.UserMapper" />
</bean> -->
<!-- 动态代理配置方式二(推荐) -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.lld.mapper"/>
</bean>
3.创建测试类
package com.lld.test;
import static org.junit.Assert.*;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.lld.mapper.UserMapper;
import com.lld.model.User;
public class UserMapperTest {
private ApplicationContext applicationContext;
@Before
public void init(){
applicationContext = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
}
@Test
public void testFingUserById() {
UserMapper userMapper = applicationContext.getBean(UserMapper.class);
User user = userMapper.fingUserById(30);
System.out.println(user);
}
}
扫一扫
609
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
