一,Mybatis
1.干嘛用的?
简化了JDBC操作数据库的过程,是一个优秀的ORM框架
两个配置文件-
核心配置文件 - 用来配置数据库的连接的参数
映射文件-用来写SQL语句
两个核心的工具类:
SqlSessionFactory--会话工厂,用来产生会话
SqlSession- - 会话,用来执行SQL
ORM:对象关系映射
是指把表里字段的值 自动映射给 类里的属性
2.项目结构
3.修改pom文件,添加mybatis的jar包依赖
<!--mybatis依赖包-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<!--jdbc依赖包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
</dependencies>
4.创建核心配置文件,指定连接数据库的参数
<?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">
<!-- mybatis的核心配置文件 -->
<configuration>
<!--可以配置多个数据库连接的环境,使用default指定默认用哪个-->
<environments default="test">
<!--配置了具体连接数据库的参数-->
<environment id="test">
<!--使用的事务管理器-->
<transactionManager type="JDBC"></transactionManager>
<!--数据源:就是制定了数据库连接时的参数-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatisdb2?characterEncoding=utf8&serverTimezone=Asia/Shanghai" />
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
</configuration>
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">
<!--这是映射文件,namespace用来作为文件的唯一标识-->
<mapper namespace="Dept1NS">
<!--select标签表示要发起查询的SQL,id是SQL语句的唯一标识,
resultType用来完成ORM,把表里的字段值 自动映射 类里的属性-->
<select id="getAll" resultType="cn.tedu.pojo.Dept1">
select * from Dept1
</select>
</mapper>
6.创建Dept1类
package cn.tedu.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
public class Dept1 {
private Integer id;
private String dname;
private String loc;
}
7.修改核心配置文件,加入映射文件
在mybatis1-config.xml里加入映射文件DeptMapper.xml
<configuration>
<!--引入映射文件-->
<mappers>
<mapper resource="DeptMapper.xml"></mapper>
</mappers>
</configuration>
8.创建测试类
package cn.tedu.test;
import cn.tedu.pojo.Dept1;
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.junit.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Test1 {
@Test
public void get() throws IOException {
//1,读取核心配置文件
InputStream in = Resources.getResourceAsStream("mybatis1-config.xml");
//2,创建会话工厂
SqlSessionFactory factory1 = new SqlSessionFactoryBuilder().build(in);
//3,创建会话(前3步固定格式,可提取)
SqlSession sess = factory1.openSession();
//4,执行SQL
//session.selectList("namespace的值.id的值");//唯一的定位了SQL
List<Dept1> list = sess.selectList("Dept1NS.getAll");
for(Dept1 i: list ){
System.out.println(i);
}
}
}
二. 练习修改映射文件
<?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 ="UserMapperNS"><!--唯一标识-->
<!--select标签表示要发起查询SQL,id是SQL语句的唯一标识,
resultType用来完成ORM,把表里的字段值 自动映射 类的属性-->
<select id="getAll" resultType="User">
select * from user
</select>
<select id="getById" resultType="User">
select * from user where id = 1
</select>
<?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 ="UserMapperNS"><!--唯一标识-->
<!--select标签表示要发起查询SQL,id是SQL语句的唯一标识,
resultType用来完成ORM,把表里的字段值 自动映射 类的属性-->
<select id="getAll" resultType="User">
select * from user
</select>
<select id="getById" resultType="User">
select * from user where id = 1
</select>
<select id="getByName" resultType="User">
select * from user where name = #{name}
</select>
<select id="getByAddr" resultType="int">
select count(1) from user where addr=#{addr}
</select>
</mapper>
<select id="getByName" resultType="User">
select * from user where name = #{name}
</select>
<select id="getByAddr" resultType="int">
select count(1) from user where addr=#{addr}
</select>
</mapper>
修改测试文件
package cn.tedu.test;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 cn.tedu.pojo.User;
import org.junit.Test;
public class MyBatisTest {
@Test
public void get() throws Exception {
//1,创建SqlSessionFactory对象,线程非安全,用来产生SqlSession
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//2,创建SqlSession,用来执行sql
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession session = factory.openSession();
//3, 定位SQL: namespace的值+id的值
//selectList(Sql是定位方式,Sql需要的参数)
List<User> list2 = session.selectList("UserMapperNS.getByName", "xiongda");
for (User uu: list2){
System.out.println(uu);
}
User u2 = session.selectOne("UserMapperNS.getById");
System.out.println(u2);
//4,解析结果并打印
List<User> list = session.selectList("UserMapperNS.getAll");
for (User u : list) {
System.out.println(u);
}
int count = session.selectOne("UserMapperNS.getByAddr", "上海");
System.out.println(count);
}
}
三,其他知识点
–1,别名
–设置别名:修改核心配置文件,加一个typeAliases标签
<!--用来设置别名 type是类的全路径 alias是以后要用的别名-->
<typeAliases>
<typeAlias type="cn.tedu.pojo.User" alias="User"></typeAlias>
</typeAliases>
–使用别名:在映射文件里,在resultType处直接使用别名
<select id="getAll" resultType="User">
select * from user
</select>
–2,xml的转义符号
四,动态SQL
创建Dept1类,用来完成ORM
package cn.tedu.pojo1;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@Data/*getXxx,setXxx,toString ,equals*/
@AllArgsConstructor/*全参*/
@NoArgsConstructor/*无参构造*/
@Accessors(chain = true)/*链式编程*/
public class Dept1 {
private Integer id;
private String dname;
private String loc;
}
****面试题:**$ 和 #取值的区别?**
都能获取参数的值,$只获取值本身不拼接单引号,#获取到值以后自动拼接单引号
$底层使用Statement不安全低效,#底层使用PreparedStatement高效安全SQL简单
创建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">
<mapper namespace="hello">
<!--提取Sql片段,提高复用性-->
<sql id="sd">id,dname</sql>
<!--resultType有别名在核心配置文件-->
<select id="get" resultType="Dept1">
/*s使用指定的SQL片段*/
select <include refid="sd"></include>
from dept1
</select>
<!--查询name = java 教研部的部门信息-->
<select id="get1" resultType="Dept1">
select <include refid="sd"></include> from dept1
<if test="dname!=null">
where dname=#{dname}
</if>
</select>
<select id="get2" resultType="String">
select loc from dept1 where id =5 or id=3 ;
</select>
<select id="get3" resultType="String">
select loc from dept1
/*可以去除多余的or或者and关键字*/
<where>
<if test="id !=null">
id=#{id}
</if>
or id=5
</where>
</select>
<delete id="delete1">
delete from dept1 where id in(
/* foreach来遍历 collection属性值是固定的"array" item是循环变量 separator是数据分割符 open左闭合区间 close有区间*/
<foreach collection="array" item="i" separator=",">
#{i}
</foreach>
)
</delete>
</mapper>
–创建测试文件
package cn.tedu.test;
import cn.tedu.pojo1.Dept1;
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.junit.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Dept1Test {
@Test
public void get() throws IOException {
/*读取核心配置文件*/
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
/*创建会话工厂*/
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
/*创建会话*/
SqlSession session = factory.openSession();//提交事务,可给参数true,不需要次次commit
/*读取Sql语句,封装在list里面*/
List<Dept1> list = session.selectList("hello.get");
//遍历
for (Dept1 i: list){
System.out.println(i);
}
/*if标签执行判断null,不执行语句,动态SQL 查询name = null的部门信息*/
List<Dept1> list1 = session.selectList("hello.get1","null");
//遍历
for (Dept1 i: list1){
System.out.println(i);
}
List<String> list2 = session.selectList("hello.get2");
//遍历
for (String i: list2){
System.out.println(i);
}
/*<where> 标签 动态SQL 查询id=10 or id=5的loc信息*/
List<String> list3 = session.selectList("hello.get3","10");
//遍历
for (String i: list3){
System.out.println(i);
}
/*foreach 标签先声明一个数组,动态SQL语句*/
int[] a = {1,2,3};
/*删除id={1,2,3}的数据信息*/
session.delete("hello.delete1",a);
session.commit();//z增删改都需要提交事务,不然对数据库没有持久性影响
System.out.println("删除掉了");
}
}