1.案例
2.mapper接口的原理
3.mapper映射文件的规范
4.多参数问题的处理
5.#{}和${}的区别
6.动态sql
一、案例
目录结构
1、数据库
2、pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.xj</groupId>
<artifactId>Mybatis</artifactId>
<version>1.0.0</version>
<dependencies>
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!--mysql连接驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<!--Junit单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
<scope>provided</scope>
</dependency>
<!--log4j日志-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
<build>
<!--加载资源文件-->
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-clean-plugin</artifactId>
<version>2.6</version>
</plugin>
</plugins>
<!--从哪个地方加载配置文件-->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>
3、db.properties
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/Mybatis
username = root
password = root
4、主配置文件 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>
<!--属性配置
resource:从classes文件夹开始寻找文件
-->
<properties resource="db.properties" />
<!--配置别名-->
<typeAliases>
<!--给配置的包下的所有的java类都创建别名
默认情况下,别名为简单类名,大小写不敏感
-->
<package name="com.xj.bean"></package>
</typeAliases>
<!--配置环境
default:Mybatis启动时使用哪个环境
-->
<environments default="development">
<environment id="development">
<!--事务管理器
使用mysql的事务管理器来管理事务
-->
<transactionManager type="JDBC"/>
<!--数据源-->
<dataSource type="POOLED">
<property name="driver" value="${driverClassName}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--关联映射文件-->
<!--resource可以直接写文件的全限名,从classes文件中寻找文件-->
<mappers>
<mapper resource="com/xj/mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
5、实体类User
package com.xj.bean;
import lombok.*;
import org.apache.ibatis.type.Alias;
import java.math.BigDecimal;
import java.util.Date;
/**
* Created by Administrator on 2019/12/20 0020.
*/
@Getter@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Alias("myUser")
public class User {
private Integer id;
private String name;
private String password;
private Integer age;
private Date birthday;
private BigDecimal salary;
}
6、工具类 MyBatisUtils
package com.xj.utils;
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.InputStream;
/**
* Created by Administrator on 2019/12/20 0020.
*/
public class MyBatisUtils {
private static SqlSessionFactory factory = null;
static{
//加载主配置文件
String resource = "Mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
factory = new SqlSessionFactoryBuilder().build(inputStream);
}
//获取session对象
public static SqlSession getSession(){
return factory.openSession();
}
}
7、mapper接口
package com.xj.mapper;
import com.xj.bean.User;
import java.util.List;
/**
* Created by Administrator on 2019/12/21 0021.
*/
public interface UserMapper {
void save(User user);
void update(User user);
void delete(Integer id);
User get(Integer id);
List<User> list();
}
8、service
package com.xj.service;
import com.xj.bean.User;
import java.util.List;
/**
* Created by Administrator on 2019/12/21 0021.
*/
public interface IUservice {
void save(User user);
void update(User user);
void delete(Integer id);
User get(Integer id);
List<User> list();
}
package com.xj.service.impl;
import com.xj.bean.User;
import com.xj.mapper.UserMapper;
import com.xj.service.IUservice;
import com.xj.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
/**
* Created by Administrator on 2019/12/21 0021.
*/
public class UserServiceImpl implements IUservice{
public void save(User user) {
SqlSession session = MyBatisUtils.getSession();
// 获取mapper接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
userMapper.save(user);
session.commit();
session.close();
}
public void update(User user) {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
userMapper.update(user);
session.commit();
session.close();
}
public void delete(Integer id) {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
userMapper.delete(id);
session.commit();
session.close();
}
public User get(Integer id) {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.get(id);
session.commit();
session.close();
return user;
}
public List<User> list() {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
List<User> list = userMapper.list();
session.commit();
session.close();
return list;
}
}
9、映射文件 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的命名规范,通常为接口的全限名-->
<mapper namespace="com.xj.mapper.UserMapper">
<!--定义一个结果集映射
id:resultMap的唯一标识
type:封装成哪个类的对象
-->
<resultMap id="userResultMap" type="myUser">
<id column="u_id" property="id"></id>
<result column="u_name" property="name"></result>
<result column="u_password" property="password"></result>
<result column="u_age" property="age"></result>
<result column="u_birthday" property="birthday"></result>
<result column="u_salary" property="salary"></result>
</resultMap>
<!--
useGeneratedKeys:启动数据库自动生成的主键
keyProperty:把获取到的主键注入给对象的哪个属性
keyColumn:从哪个列获取自动生成的主键(可以省略)
-->
<insert id="save" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
insert into t_user (u_name,u_password,u_age,u_birthday,u_salary)
values (#{name},#{password},#{age},#{birthday},#{salary})/*调用get方法*/
</insert>
<update id="update">
update t_user
set
u_name = #{name},
u_password = #{password},
u_age = #{age},
u_birthday = #{birthday},
u_salary = #{salary}
where
u_id = #{id}
</update>
<delete id="delete">
delete from t_user where u_id = #{id}/*获取参数的值,并不是调用get方法*/
</delete>
<select id="get" resultType="myUser" resultMap="userResultMap">/*查询的时候必须要有返回值类型,这返回的是类的全限定名*/
select u_id,u_name,u_password,u_age,u_birthday,u_salary from t_user where u_id=#{id}
</select>
<select id="list" resultMap="userResultMap">/*如果返回的是集合,那应该是设置为集合包含的类型*/
select u_id,u_name,u_password,u_age,u_birthday,u_salary from t_user
</select>
</mapper>
10、测试类
import com.xj.bean.User;
import com.xj.service.IUservice;
import com.xj.service.impl.UserServiceImpl;
import org.junit.Test;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
/**
* Created by Administrator on 2019/12/20 0020.
*/
public class MybatisTest{
@Test
public void testSave() throws Exception{
IUservice service = new UserServiceImpl();
User user = new User(null,"tom","1111",12,new Date(),new BigDecimal(4000));
service.save(user);
}
@Test
public void testUpdate() throws Exception{
IUservice service = new UserServiceImpl();
User user = new User(3,"rose","444",45,new Date(),new BigDecimal(2222));
service.update(user);
}
@Test
public void testDelete() throws Exception{
IUservice service = new UserServiceImpl();
service.delete(3);
}
@Test
public void testGet() throws Exception{
IUservice service = new UserServiceImpl();
User user = service.get(4);
System.out.println(user);
}
@Test
public void testList() throws Exception{
IUservice service = new UserServiceImpl();
List<User> list = service.list();
System.out.println(list);
}
}
11.日志文件log4j.properties
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.xj.mapper.UserMapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
二、mapper接口的原理
注:只能传入一个参数,不能传入多个参数(如上面的user或id都是一个参数)
三、mapper映射文件的规范
1、映射文件中namespace的值,必须等于mapper接口的全限定名
2、映射文件中的SQL的id值,必须等于mapper接口中的方法名
3、映射文件中的SQL的paramterType必须跟对应的方法的类型相同
4、方法的返回值必须和resultType或者resultMap的值保持一致
四、多参数问题的处理
1.封装成javaBean
新建一个类
缺点:会造成类的冗余
2. 封装成map对象
1、IUservice接口添加登录方法
User login1(String username,String password);
2、UserServiceImpl实现接口中的方法
public User login1(String username, String password) {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
Map map = new HashMap();
map.put("username",username);
map.put("password",password);
User user = userMapper.selectByUsernameAndPassword(map);
session.commit();
session.close();
return user;
}
3、UserMapper接口添加的方法
User selectByUsernameAndPassword(Map map);
4、映射文件中添加对应的SQL语句
<select id="selectByUsernameAndPassword" resultMap="userResultMap">
select * from t_user where u_name=#{username} and u_password=#{password}/*map的key值*/
</select>
5、测试类
@Test
public void testLogin1() throws Exception{
IUservice service = new UserServiceImpl();
User user = service.login1("jack","111");
System.out.println(user);
}
缺点:传入多个参数,需要进行封装map,会比较麻烦,另外,如果需要封装多个map,那么就不容易区分到底用的是哪个。
3.使用@param注解的方式(推荐)
User login2(String username,String password);
public User login2(String username, String password) {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.selectByUsernameAndPassword2(username,password);
session.commit();
session.close();
return user;
}
/*
* @Param注解的作用:
* 把执行方法的时候传递的参数,自动封装到map中,
* map的key为注解中的value值,@param注解中括号的值
* map的value为执行方法时,传递的实际参数
* */
User selectByUsernameAndPassword2(@Param("username") String username, @Param("password") String password);
<select id="selectByUsernameAndPassword2" resultMap="userResultMap">
select * from t_user where u_name=#{username} and u_password=#{password}
</select>
@Test
public void testLogin2() throws Exception{
IUservice service = new UserServiceImpl();
User user = service.login2("jack","111");
System.out.println(user);
}
五、#{}和${}的区别
1.区别
(1)使用#{}
(2)使用${}
2.如何选择哪一个使用?
数据库中查询的结果按u_id降序排序,使用下面的SQL语句,得到的结果按u_id降序排序
1、mapper接口中的方法
List<User> orderbyColum(@Param("columName") String columName);
2、service接口以及实现类
List<User> orderbyColum(String columName);
public List<User> orderbyColum(String columName) {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
List<User> list = userMapper.orderbyColum(columName);
session.commit();
session.close();
return list;
}
3(1)、映射文件中的SQL语句(使用#{})
<select id="orderbyColum" resultMap="userResultMap">
select * from t_user order by #{columName} desc
</select>
3(2)、映射文件中的SQL语句(使用${})
<select id="orderbyColum" resultMap="userResultMap">
select * from t_user order by ${columName} desc
</select>
4、测试类
@Test
public void testOrder() throws Exception{
IUservice service = new UserServiceImpl();
List<User> list = service.orderbyColum("u_id");
System.out.println(list);
}
5、结果
使用#{}
使用${}
如何选用:
${}:当传入的参数时作为SQL执行的一部分(例如:表名或列名)的时候必须使用${};
#{}:当传入的参数时同数据库进行交互的时候,使用#{}.
简单的判断:传入的参数在SQL中是否能够加上单引号。可以加单引号,使用#{},不能加单引号,使用${}
六、动态sql
1.if
根据条件包含 where 子句的一部分。
特点:当if的判断条件满足时,添加if标签中的字句,当if的判断条件不满足时,什么都不添加
1、service
List<User> query(User user);
public List<User> query(User user) {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
List<User> list = userMapper.query(user);
session.commit();
session.close();
return list;
}
2、mapper接口中的方法
List<User> query(User user);
3、映射文件
<select id="query" resultMap="userResultMap">
select * from t_user where u_name=#{name} and u_age=#{age}
</select>
4、测试类
@Test
public void testQuery() throws Exception{
IUservice service = new UserServiceImpl();
User user = new User();
user.setName("jack");
user.setAge(23);
List<User> list = service.query(user);
System.out.println(list);
}
5、查询结果,可以查到值
如果是下面这种情况?
就没有查询到数据
解决方法如下:将SQL语句添加if标签
<select id="query" resultMap="userResultMap">
select * from t_user where
<if test="name!=null">
u_name=#{name}
</if>
<if test="age!=null">
and u_age=#{age}
</if>
</select>
执行结果是没有问题的
但是如果是下面这种情况,SQL语句是有毛病的,因为where后面直接跟了一个and,还是会报错的
解决方法:在where后面添加 1=1
2.choose
有时我们不想应用到所有的条件语句,而只想从中择其一项。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
适用于:当判断的条件为true时,执行一个语句,当判断条件为false时,执行另外一个语句
<!--当name属性不为空时,根据name属性的值进行查询
当name属性为空时,查询jack的信息-->
<select id="query" resultMap="userResultMap">
select * from t_user where 1=1
<choose>
<when test="name!=null">
and u_name=#{name}
</when>
<otherwise>
and u_name="jack"
</otherwise>
</choose>
</select>
1、当name属性不为空时
2、当name属性为空时
3.where和set
where语句的作用:
1、当where标签中的语句不为空时,会在语句之前拼上"where"关键字
2、假如where标签中的语句是以and或or开头语,那么这个and或者or会被省略掉
例如:如果不想在where后面添加1=1的条件,可以使用where标签
<select id="query" resultMap="userResultMap">
select * from t_user
<where>
<if test="name!=null">
and u_name=#{name}
</if>
<if test="age!=null">
and u_age=#{age}
</if>
</where>
</select>
set语句的作用:
1、假如set标签中的语句不为空,那么会在开头拼接set关键字
2、假如set标签中的语句是以","结尾,那么这个","会被去掉
例如:没有使用set标签,将更新语句中的最后一个属性的值为null,那么SQL语句最后会多一个",",就会报错
<update id="update">
update t_user
set
<if test="name!=null">
u_name=#{name},
</if>
<if test="password!=null">
u_password = #{password},
</if>
<if test="age!=null">
u_age=#{age},
</if>
<if test="birthday!=null">
u_birthday = #{birthday},
</if>
<if test="salary!=null">
u_salary = #{salary}
</if>
where
u_id = #{id}
</update>
解决办法:使用set标签
<update id="update">
update t_user
<set>
<if test="name!=null">
u_name=#{name},
</if>
<if test="password!=null">
u_password = #{password},
</if>
<if test="age!=null">
u_age=#{age},
</if>
<if test="birthday!=null">
u_birthday = #{birthday},
</if>
<if test="salary!=null">
u_salary = #{salary}
</if>
</set>
where
u_id = #{id}
</update>
4.trim
(1)替换where
<select id="query" resultMap="userResultMap">
select * from t_user
<trim prefix="where" prefixOverrides="and|or">
<if test="name!=null">
and u_name=#{name}
</if>
<if test="age!=null">
and u_age=#{age}
</if>
</trim>
</select>
(2)替换set
<update id="update">
update t_user
<trim prefix="set" suffixOverrides=",">
<if test="name!=null">
u_name=#{name},
</if>
<if test="password!=null">
u_password = #{password},
</if>
<if test="age!=null">
u_age=#{age},
</if>
<if test="birthday!=null">
u_birthday = #{birthday},
</if>
<if test="salary!=null">
u_salary = #{salary}
</if>
</trim>
where
u_id = #{id}
</update>
5.foreach
动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历,通常是在构建 IN 条件语句的时候。下面是批量删除的例子。
1、service
//批量删除
void batchDelete(Integer[] ids);
public void batchDelete(Integer[] ids) {
SqlSession session = MyBatisUtils.getSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
userMapper.batchDelete(ids);
session.commit();
session.close();
}
2、mapper接口
void batchDelete(Integer[] ids);
3、映射文件
<delete id="batchDelete">
delete from t_user where u_id in
/*(1,2,3,4)
注意:当接口中方法的参数为数组或者集合时,mybatis会自动把该参数封装成一个map
map的key为参数类型,首字母小写
数组的key值"array",集合的key为"list",map的key为"map"
collection:参数在封装成的map中的key值
open:表示字符串以什么开始
close:表示字符串以什么结束
item:给集合中的每一个元素起的别名
separator:集合中元素的分隔符
*/
<foreach collection="array" open="(" close=")" item="item" separator=",">
#{item}
</foreach>
</delete>
4、测试类
@Test
public void testBatchDelete(){
IUservice service = new UserServiceImpl();
Integer[] ids = new Integer[]{5,6,7,8,9,10};
service.batchDelete(ids);
}