mybatis
什么是mybatis?
底层封装了JDBC , 对数据库可以进行操作 , 是一个优秀的持久层框架
好处: 简化了JDBC的开发步骤, 自动完成ORM映射
核心资源
1,核心配置文件
mybatis-config.xml 配置了事务管理,数据源
2,映射文件
XxxMapper.xml 存放大量的CRUD的SQL语句
3,核心工具类
会话工厂SqlSessionFactory : 产生会话
会话SqlSession : 执行SQL语句
4,ORM
是指对象关系映射.
把表里的字段的值 查到 自动交给 类里的属性 保存
案例
pom.xml,添加mybatis的jar包
<?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">
<parent>
<artifactId>cgb2106boot03</artifactId>
<groupId>cn.wenhao</groupId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<groupId>com.jt</groupId>
<artifactId>mybatistest1</artifactId>
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
</dependencies>
</project>
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">
<!-- mybatis的核心配置文件 -->
<configuration>
<!--开启驼峰规则,简化resultMap的编写-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--配置别名,给指定类起一个指定的别名-->
<typeAliases>
<typeAlias type="cn.wenhao.pojo.UserInfo" alias="Info"></typeAlias>
</typeAliases>
<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/mybatisdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai" />
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 引入-->
<mappers>
<mapper resource="userInfoMapper.xml"></mapper>
</mappers>
</configuration>
UserMapper.xml 映射文件
配置别名:
<typeAliases>
<typeAlias type="类的路径" alias="要改的名name"></typeAlias>
</typeAliases>
<?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.xml文件的唯一标识
resultType的值用来封装查到的结果 ORM
-->
<mapper namespace="cn.wenhao.dao.userInfoMapper">
<!-- ORM-->
<resultMap id="adc" type="cn.wenhao.pojo.UserInfo" autoMapping="true">
<!-- <result column="user_name" property="userName"></result>-->
<!-- <result column="user_addr" property="userAddr"></result>-->
<!-- <result column="user_age" property="userAge"></result>-->
</resultMap>
<sql id="cols">-- 提取SQL片段,提高复用性
id,dname,loc
</sql>
<!--<select id="getById" resultType="User">-->
<select id="getById" resultMap="adc">
select * from user_info where id in(
<foreach collection="array" item="id" separator=",">
#{id}
</foreach>
)
</select>
<select id="getById" resultType="Dept">
select
<include refid="cols"></include>
from dept where id=#{id}
</select>
<select id="getByName" resultType="Dept">
select
<include refid="cols"></include>
from dept
<if test="loc!=null">
where loc=#{loc}
</if>
</select>
<insert id="add">
insert into dept values(#{id},#{dname},#{loc})
</insert>
<delete id="delete">
delete from dept where id in
<foreach collection="array" item="ids" separator="," open="(" close=")">
#{ids}
</foreach>
</delete>
</mapper>
面试题:SQL动态获取参数时,可以用#或者$
$ 底层用了低级传输器,可能发生SQL注入攻击,低效,不拼串,可能发生SQL语法错误
# 底层用了高级传输器,安全,高效,会自动拼接字符串
动态Sql
1.提取SQL片段,提高SQL片段复用性
<sql id="cols"> id,dname,loc </sql>
使用方法:<include refid="cols"></include>
2.foreach用来完成遍历 collection表示要遍历哪种集合里的数据,值是固定值:array/list/Map.key item表示即将遍历到的数据,separator是分隔符,#{id}获取遍历到的数据
<select id="getById" resultMap="adc">
select * from user_info where id in(
<foreach collection="array" item="id" separator=",">
#{id}
</foreach>
)
</select>
3.if判断
select
<include refid="cols"></include>
from dept
<if test="dname != null">
where dname=#{dname}
</if>
4.逻辑运算符: = eq, > gt, < lt , >= ge, <= le , != ne &eq;
5.xml文件中的万能的转义字符: <![CDATA[ sql语句 ]]>
创建UserInfo类 (pojo)
package cn.wenhao.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class UserInfo {
//注意:::属性的名 和 表里的字段名 必须一致,否则无法ORM
private Integer id;
private String userName;
private String userAddr;
private Integer userAge;
}
创建userInfoMapper接口 (mapper)
package cn.wenhao.dao;
import cn.wenhao.pojo.UserInfo;
import java.util.List;
public interface userInfoMapper {
List<UserInfo> getById(Integer[] id);
void add(UserInfo user);
}
创建测试类
package cn.wenhao.test;
import cn.wenhao.dao.userInfoMapper;
import cn.wenhao.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 org.junit.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLOutput;
import java.util.List;
public class Test1 {
@Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//开启会话
//参数为true自动提交事务
SqlSession session = factory.openSession(true);
//定位SQL(namespace.id)并执行
//selectOne执行查询的SQL,并返回一个结果
Object o = session.selectOne("userMapper.getById",3);
System.out.println(o);
//返回一个List集合
List<Object> list = session.selectList("userMapper.getAll");
for(Object l : list){
System.out.println(l);
}
Object o1 = session.selectOne("userMapper.getName","xiongda");
System.out.println(o1);
// Dept d= session.selectOne("", 5);
// System.out.println(d);
// 面向接口开发
DeptMapper mapper = session.getMapper(DeptMapper.class);
Dept d = mapper.getById(4);
System.out.println(d);
List<Dept> d1= mapper.getByName(null);
System.out.println(d1);
// mapper.add(new Dept(1,"阿呆","北京"));
// mapper.add(new Dept(2,"阿呆","北京"));
// mapper.add(new Dept(3,"阿呆","北京"));
// mapper.add(new Dept(4,"阿呆","北京"));
mapper.delete(new int[]{2,3});
session.commit();
}
}
总结