一,Mybatis的接口开发
–1,概述
比XML的开发方式要好: sqlSession.selectList(“hello.get”);
用接口开发,SQL的定位不必拼串.
–2,需求:利用Mybatis框架查询dept表里的所有数据
–3,步骤:
–准备核心配置文件mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- mybatis的核心配置文件 配置了数据源,事务,Mapper.xml映射文件 -->
<configuration>
<environments default="test">
<environment id="test">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<!--数据库的驱动-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<!--数据库的url-->
<property name="url" value="jdbc:mysql://localhost:3306/mybatisdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai" />
<!--数据库的用户名-->
<property name="username" value="root"/>
<!--数据库的密码-->
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--引入DeptMapper.xml文件-->
<mappers>
<mapper resource="DeptMapper.xml"></mapper>
</mappers>
</configuration>
--创建Dept类
package cn.tedu.pojo;
//就是把数据库表和类 / 表里的字段和类里的属性对应
public class Dept {
private int id;
private String dname ;
private String loc;
public Dept() { }
public Dept(int id, String dname, String loc) {
this.id = id;
this.dname = dname;
this.loc = loc;
}
@Override
public String toString() {
return "Dept{" +
"id=" + id +
", dname='" + dname + '\'' +
", loc='" + loc + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
--准备DeptMapper.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">
<!--这是映射文件,用来存大量SQL ,给设置namespace属性来作为这个mapper文件的唯一标识-->
<!-- namespace的值 = 接口的全路径 -->
<mapper namespace="cn.tedu.dao.DeptDao">
<!--查询dept表的所有数据
id是 接口里的方法名称
resultType是 要把结果封装给哪个类,类的全路径
-->
<select id="getAll" resultType="cn.tedu.pojo.Dept">
select * from dept
</select>
</mapper>
--准备DeptDao接口,提供方法
package cn.tedu.dao;
import cn.tedu.pojo.Dept;
import java.util.List;
//接口的全路径 = DeptMapper.xml里 namespace的值
//接口里的方法名 = DeptMapper.xml里 SQL的id值
public interface DeptDao {
List<Dept> getAll();//查询dept表的所有数据
}
--测试
package cn.tedu.test;
import cn.tedu.dao.DeptDao;
import cn.tedu.pojo.Dept;
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 org.springframework.transaction.support.ResourceTransactionDefinition;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
//测试类
public class TestDept {
public static void main(String[] args) throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
// 会话工厂SQLSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
// 会话SQLSession
SqlSession session = factory.openSession();
//获取DeptDao接口
DeptDao dao = session.getMapper(DeptDao.class);
List<Dept> list = dao.getAll();
// 解析结果集ORM(类和表 / 类里的属性和表里的字段)
for (Dept d : list) {
System.out.println(d);
}
}
}
--4,总结
mybatis-config.xml 要引入DeptMapper.xml
DeptMapper.xml 的namespace的值 = 接口的全路径
的SQL的id值 = 接口里方法的名字
DeptDao接口 的全路径 决定了 DeptMapper.xml 的namespace的值
里的方法名 决定了 DeptMapper.xml 的SQL的id值
--5,具体使用
--修改DeptMapper.xml,添加SQL
<?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">
<!--这是映射文件,用来存大量SQL ,给设置namespace属性来作为这个mapper文件的唯一标识-->
<!-- namespace的值 = 接口的全路径 -->
<mapper namespace="cn.tedu.dao.DeptDao">
<!--mybatis练习查询id=100的,解析SQL需要的参数::: #{参数}
#和$的区别?如果是整数都行,如果是字符串,#可以自动拼接字符串'',$只拼接值本身
-->
<select id="getById" resultType="cn.tedu.pojo.Dept">
select * from dept where id = #{id}
</select>
<!--mybatis练习根据loc查,解析SQL需要的参数::: #{参数}-->
<select id="getByLoc" resultType="cn.tedu.pojo.Dept">
select * from dept where loc = #{loc}
</select>
<!--mybatis练习根据id查,getOne是接口里的方法名 -->
<select id="getOne" resultType="cn.tedu.pojo.Dept">
select * from dept where id=10
</select>
<!--mybatis练习根据dname查-->
<select id="getByName" resultType="cn.tedu.pojo.Dept">
select * from dept where dname="开发"
</select>
<!--查询dept表的所有数据
id是 接口里的方法名称
resultType是 要把结果封装给哪个类,类的全路径
-->
<select id="getAll" resultType="cn.tedu.pojo.Dept">
select * from dept
</select>
</mapper>
--修改DeptDao接口,添加方法
package cn.tedu.dao;
import cn.tedu.pojo.Dept;
import java.util.List;
//接口的全路径 = DeptMapper.xml里 namespace的值
//接口里的方法名 = DeptMapper.xml里 SQL的id值
public interface DeptDao {
List<Dept> getAll(); //查询dept表的所有数据
Dept getOne();//查询一条
Dept getByName();//根据名字查询
List<Dept> getByLoc(String loc);
Dept getById(int id);
}
--修改测试类
package cn.tedu.test;
import cn.tedu.dao.DeptDao;
import cn.tedu.pojo.Dept;
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 org.springframework.transaction.support.ResourceTransactionDefinition;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
//测试类
public class TestDept {
public static void main(String[] args) throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
// 会话工厂SQLSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
// 会话SQLSession
SqlSession session = factory.openSession();
//获取DeptDao接口
DeptDao dao = session.getMapper(DeptDao.class);
List<Dept> list = dao.getAll(); //获取所有记录
// 解析结果集ORM(类和表 / 类里的属性和表里的字段)
for (Dept d : list) {
System.out.println(d);
}
System.out.println();
Dept d = dao.getOne(); //获取一条记录
System.out.println(d);
System.out.println();
Dept d2 = dao.getByName();//按照名字获取一条记录
System.out.println(d2);
System.out.println();
List<Dept> ds = dao.getByLoc("loc1");
for (Dept dd : ds) {
System.out.println(dd);
}
System.out.println();
Dept dd = dao.getById(100);
System.out.println(dd);
}
}
二,动态SQL
–1,概述
使用mybatis提供的各种标签实现SQL动态拼接
–2,分类
sql标签用来提取SQL片段 ,include标签用来引用指定片段
判断
拼接过滤条件
循环
–3,测试
–需求:操作emp表里的数据
–创建Emp类
package cn.tedu.pojo;
import java.util.Date;
//封装emp表里 每个字段的值
public class Emp {
//属性类型必须和数据库里字段类型一致,属性名称必须和数据库里字段名称一致
private int id;
private String ename;
private String job;
private double mgr;
private Date hiredate;
private double sal;
private double comm;
private int deptno;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public double getMgr() {
return mgr;
}
public void setMgr(double mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
--创建EmpMapper.xml文件,写SQL
<?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="cn.tedu.dao.EmpDao">
<!--提取SQL片段,复用性高-->
<sql id="cols">
id,ename,job,mgr,sal,comm,hiredate
</sql>
<!--查询id是100的 或者200的 或者502 的记录-->
<select id="getByIds" resultType="cn.tedu.pojo.Emp">
select <include refid="cols"/>
from emp
where id in(
/*foreach标签完成遍历,
collection值是固定写法,
separator数据间的分隔符,
item是取到的数据
*/
<foreach collection="list" separator="," item="i">
#{i}
</foreach>
)
</select>
<!--查询id=200 ename=tony的记录 /* 去除多余的and */-->
<select id="getByIdName" resultType="cn.tedu.pojo.Emp">
select <include refid="cols"/>
from emp
<where>
<if test="id != 0">
id = #{id}
</if>
<if test="ename!=null">
and ename= #{ename}
</if>
</where>
</select>
<!--根据id获取数据-->
<select id="getById" resultType="cn.tedu.pojo.Emp">
/* include引用了指定的SQL片段 */
select <include refid="cols"/>
from emp where
<if test="id != null"> /*用来判断*/
id=#{id}
</if>
</select>
<!--获取所有数据-->
<select id="getAll" resultType="cn.tedu.pojo.Emp">
select <include refid="cols"/> from emp
</select>
</mapper>
--修改mybatis-config.xml文件引入EmpMapper.xml文件
<!--引入XXXMapper.xml文件-->
<mappers>
<mapper resource="DeptMapper.xml"></mapper>
<mapper resource="EmpMapper.xml"></mapper>
</mappers>
--创建EmpDao接口,写方法
package cn.tedu.dao;
import cn.tedu.pojo.Emp;
import java.util.List;
public interface EmpDao {
List<Emp> getAll();
Emp getById(int id);
List<Emp> getByIdName(Emp emp);
List<Emp> getByIds(List list);
}
--创建测试类
package cn.tedu.test;
import cn.tedu.dao.EmpDao;
import cn.tedu.pojo.Emp;
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;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class TestEmp {
public static void main(String[] args) throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
// 会话工厂SQLSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
// 会话SQLSession
SqlSession session = factory.openSession();
//执行SQL
EmpDao dao = session.getMapper(EmpDao.class);
List<Emp> list = dao.getAll();
for (Emp e : list) {
System.out.println(e);
}
System.out.println();
Emp emp = dao.getById(100);
System.out.println(emp);
System.out.println();
Emp emp2 = new Emp();
emp2.setId(0);
emp2.setEname("tony");
List<Emp> list2 = dao.getByIdName(emp2);
for (Emp em : list2) {
System.out.println(em);
}
System.out.println();
List list4 = new ArrayList();
Collections.addAll(list4,100,200,502);
List<Emp> list3 = dao.getByIds(list4);
for (Emp emm : list3) {
System.out.println(emm);
}
}
}
三,ResultMap
–1,概述
当发起一条查询的SQL语句时,通常都会查出结果,
结果想要和哪个类做ORM映射,直接给
select标签配置resultType属性就可以了.
当出现了特殊情况时(当表里的字段名和类里的属性名不一样),select标签配置 resultMap属性
--2,步骤
--准备数据
create table user_info(
id int primary key auto_increment,
user_name varchar(100),
user_addr varchar(200),
user_age int
);
insert into user_info values(null,'韩梅梅','上海',20);
insert into user_info values(null,'王海涛','北京',30);
insert into user_info values(null,'张慎政','河南',10);
--创建UserInfo类
package cn.tedu.pojo;
//和user_info表做ORM映射
public class UserInfo {
private int id ;
private String userName ;
private String userAddr ;
private int userAge ;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserAddr() {
return userAddr;
}
public void setUserAddr(String userAddr) {
this.userAddr = userAddr;
}
public int getUserAge() {
return userAge;
}
public void setUserAge(int userAge) {
this.userAge = userAge;
}
@Override
public String toString() {
return "UserInfo{" +
"id=" + id +
", userName='" + userName + '\'' +
", userAddr='" + userAddr + '\'' +
", userAge=" + userAge +
'}';
}
}
--创建UserInfoMapper.xml,写SQL
<?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="cn.tedu.dao.UserInfoDao">
<!--resultType只能完成简单的ORM(表里的字段名和类里的属性名一样)
UserInfo{id=1, userName='null', userAddr='null', userAge=0}
<select id="getAll" resultType="cn.tedu.pojo.UserInfo">
-->
<!-- 用来解决 属性名和字段名 不一样
id 是唯一标识
type 想要把结果封装给哪个类
UserInfo{id=1, userName='韩梅梅', userAddr='上海', userAge=20}
-->
<resultMap id="userInfoRM" type="cn.tedu.pojo.UserInfo">
<!-- id是专门用来描述表里的主键信息 result描述表里的普通字段
column用来指定表里的字段名
property用来指定类中的属性名
-->
<id property="id" column="id"></id>
<result property="userName" column="user_name"></result>
<result property="userAddr" column="user_addr"></result>
<result property="userAge" column="user_age"></result>
</resultMap>
<!--resultMap 用来完成特殊情况的处理-->
<select id="getAll" resultMap="userInfoRM">
select * from user_info
</select>
</mapper>
--修改核心配置文件,引入UserInfoMapper.xml
<!--引入XXXMapper.xml文件-->
<mappers>
<mapper resource="DeptMapper.xml"></mapper>
<mapper resource="EmpMapper.xml"></mapper>
<mapper resource="UserInfoMapper.xml"></mapper>
</mappers>
--创建UserInfoDao接口,写方法
package cn.tedu.dao;
import cn.tedu.pojo.UserInfo;
import java.util.List;
public interface UserInfoDao {
List<UserInfo> getAll();
}
--创建测试类
package cn.tedu.test;
import cn.tedu.dao.UserInfoDao;
import cn.tedu.pojo.UserInfo;
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;
import java.util.List;
public class TestUInfo {
public static void main(String[] args) throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
// 会话工厂SQLSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
// 会话SQLSession
SqlSession session = factory.openSession();
UserInfoDao dao = session.getMapper(UserInfoDao.class);
List<UserInfo> list = dao.getAll();
for (UserInfo ui : list) {
System.out.println(ui);
}
}
}
四,优化ResultMap
–1,概述
当表里的字段名和类里的属性名不一致时,想要自己声明应对关系
–2,开启自动映射
–在核心配置文件中mybatis-config.xml文件中加一个配置信息(放在最上面)
–在指定的resultMap标签中,添加autoMapping属性
五,利用SpringBoot整合SSM
开发步骤:
–1,修改pom.xml添加jar包的依赖
org.mybatis.spring.boot
mybatis-spring-boot-starter
2.1.4
mysql
mysql-connector-java
5.1.48
runtime
–2,准备数据库的表和记录
CREATE TABLE car
(
id
int(11) NOT NULL auto_increment,
name
varchar(10) default NULL,
color
varchar(10) default NULL,
price
double default NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
–3,创建Car类
package cn.tedu.pojo;
import org.springframework.stereotype.Component;
@Component //交给spring容器进行ioc
public class Car {
private int id ;
private String name ;
private String color ;
private double price ;
@Override
public String toString() {
return "Car{" +
"id=" + id +
", name='" + name + '\'' +
", color='" + color + '\'' +
", price=" + price +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getColor() {
return color;
}
public void setColor(String color) {
this.color = color;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
}
--4,创建CarDao接口
package cn.tedu.dao;
import cn.tedu.pojo.Car;
//作为Mybatis的Dao层,用来操作数据
public interface CarDao {
Car get();
}
--5,创建CarMapper.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="cn.tedu.dao.CarDao">
<!-- id的值 = 接口里的方法名
resultType的值 要把结果集 封装给哪个类(类的全路径)
-->
<select id="get" resultType="cn.tedu.pojo.Car">
select * from car where id = 1
</select>
</mapper>
--6,创建CarService接口
package cn.tedu.service;
import cn.tedu.pojo.Car;
//充当Spring的功能,ioc di
public interface CarService {
Car get();
}
--7,创建CarServiceImpl实现类
package cn.tedu.service;
import cn.tedu.dao.CarDao;
import cn.tedu.pojo.Car;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
//CarService的实现类
@Component //交给spring容器ioc
public class CarServiceImpl implements CarService{
@Autowired
private CarDao carDao; //service层调用了dao层
@Override
public Car get() {
//调用了Dao层的代码
return carDao.get();
}
}
--8,创建CarController类,用来接受请求做出响应
package cn.tedu.controller;
import cn.tedu.pojo.Car;
import cn.tedu.service.CarService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/car")
public class CarController {
@Autowired
private CarService carService;
@RequestMapping("/get")
public Car get(){
return carService.get();
}
}
--9,创建RunApp类
package cn.tedu;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("cn.tedu.dao")//扫描dao接口文件所在的包
public class RunApp {
public static void main(String[] args) {
SpringApplication.run(RunApp.class);
}
}
--10,创建application.yml
选中resources-右键-new-file-输入名字
#SpringBoot配置mysql信息
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql:///mybatisdb?useUnicode=true&characterEncoding=utf8&useSSL=false
username: root
password: root
#SpringBoot整合Mybatis配置
mybatis:
#指定UserMapper.xml文件的位置
mapper-locations: classpath:*.xml
#开启驼峰映射
configuration:
map-underscore-to-camel-case: true
--11,测试
运行RunApp类
浏览器访问:http://localhost:8080/car/get
得到响应的数据:{"id":1,"name":"BMW","color":"red","price":9.9}