目录
一、Mybatis是什么?
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
二、使用步骤
1.导入表
drop table if exists tb_user;
create table tb_user(
id int primary key,
username varchar(20),
password varchar(20),
gender varchar(20),
addr varchar(20)
);
INSERT INTO `tb_user`(`id`, `username`, `password`, `gender`, `addr`) VALUES (1, 'zs', 'zs123', '男', '北京');
INSERT INTO `tb_user`(`id`, `username`, `password`, `gender`, `addr`) VALUES (2, 'ls', 'ls123', '男', '天津');
INSERT INTO `tb_user`(`id`, `username`, `password`, `gender`, `addr`) VALUES (3, 'ww', 'ww123', '男', '南京');
2.导入坐标(pom.xml)
<!-- mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<!-- 连接数据库依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
3.配置文件
mybatis-config.xml
放在Resources
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/数据库名"/>
<property name="username" value="root"/>
<property name="password" value="密码"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--单个映射文件地址-->
<!--<mapper resource="Tb_UserMapper.xml"/>-->
<!--多个映射文件地址-->
<package name="com.mapper"/>
</mappers>
</configuration>
Tb_UserMapper.xml(据表名而定)
在Resources包中建立mapper包,并将Tb_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">
<mapper namespace="com.mapper.Tb_UserMapper">
<!-- statementId:namespace.id 用来唯一标识sql语句-->
<select id="selectAll" resultType="com.pojo.Tb_User">
select id ,username ,password ,gender ,addr address
from tb_user
</select>
</mapper>
4.实体类(pojo)
package com.pojo;
public class Tb_User {
private Integer id;
private String username;
private String password;
private String gender;
private String address;
public Tb_User() {
}
public Tb_User(Integer id, String username, String password, String gender, String address) {
this.id = id;
this.username = username;
this.password = password;
this.gender = gender;
this.address = address;
}
public Integer getId() {
return this.id;
}
public void setId(Integer id) {
this.id = id;
}
/**
* 获取
* @return username
*/
public String getUsername() {
return username;
}
/**
* 设置
* @param username
*/
public void setUsername(String username) {
this.username = username;
}
/**
* 获取
* @return password
*/
public String getPassword() {
return password;
}
/**
* 设置
* @param password
*/
public void setPassword(String password) {
this.password = password;
}
/**
* 获取
* @return gender
*/
public String getGender() {
return gender;
}
/**
* 设置
* @param gender
*/
public void setGender(String gender) {
this.gender = gender;
}
/**
* 获取
* @return address
*/
public String getAddress() {
return address;
}
/**
* 设置
* @param address
*/
public void setAddress(String address) {
this.address = address;
}
public String toString() {
return "Tb_User{id = " + id + ", username = " + username + ", password = " + password + ", gender = " + gender + ", address = " + address + "}";
}
}
5.查
public class test01 {
public static void main(String[] args) throws IOException {
String config="mybatis-config.xml";
//将配置文件以流的形式加载到内存中
InputStream is= Resources.getResourceAsStream(config);
//通过SqlSessionFactory工厂解析配置文件
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
//获取SqlSession对象
SqlSession sqlSession = ssf.openSession();
//获取查询结果并且遍历
sqlSession.selectList("com.mapper.Tb_UserMapper.selectAll")//Tb_UserMapper.selectAll是statementId 用来唯一标识sql语句
.forEach(
user-> System.out.println(user)
);
sqlSession.close();//查询之后记得关流释放资源
}
}
三、mapper代理
1.概述
mapper代理解决此处硬编码问题
2.如何代理
<1>在com包下建立mapper包
<2>创建Tb_UserMapper接口
<3>将sql语句id值作为方法名,据sql书写返回值类型
public interface Tb_UserMapper {
List<Tb_User> selectAll();
}
<4>对应的映射文件的名称空间要改成接口的全限定类名
<5> 获取并使用mapper代理
四、增删改查(CRUD)
1.查
<1>查询所有
public interface Tb_UserMapper {
List<Tb_User> selectAll();
}
<select id="selectAll" resultType="com.pojo.Tb_User">
select id ,username ,password ,gender ,addr address
from tb_user
</select>
<2>条件查询
public interface Tb_UserMapper {
List<Tb_User> selectAll();
List<Tb_User> selectById(Integer id);
}
<select id="selectById" resultType="com.gaohe.pojo.Tb_User">
select id, username, password, gender, addr address
from tb_user
where id = #{id}
</select>
a.参数占位符
#{} 和 ${} 可以接受传来的参数
区别:
${} 直接将参数拼接入sql语句,
#{} 可以防止sql注入
使用时机:
*参数传递,都使用 #{}
*如果要对表名、列名进行动态设置,只能使用 ${} 进行sql拼接
b.数据库表的字段名 和 实体类的属性名称 不一致
表名字段为addr ,Tb_User类属性address,只有当表中字段和Tb_User属性名中相一致时,才能接收到数据。
解决方法
1>起别名
缺点:每次查询都要定义一次别名
2>sql片段
缺点:不灵活,sql内容被固定死
3>resultMap:
1&.定义<resultMap>标签
<resultMap id="Tb_User" type="com.pojo.Tb_User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="addr" property="address"/>
</resultMap>
Ps. *主键字段用<id>标签,其他字段用<result>标签
*column对应表字段,property对应pojo类属性
2&.在<select>标签中,使用 resultMap 属性替换 resultType 属性
c.传递多个参数
1>@Param(“对应参数占位符名”)注解
在接口加注解
Tb_User selectById_Name(@Param("id") Integer id,
@Param("username") String username);
2>封装成pojo对象
Tb_User selectById_Name(Tb_User user);//将参数通过setter方法封装到对象中
3>封装成map集合
Tb_User selectById_Name(Map map);//将参数以键值对形式封装到map集合,key:对应占位符名称 value:值
<3>动态查询
当我们查询时有多个条件,假如每次查询的条件不相同,那我们就得写多条条件不同的sql语句,代码复用性低,所以mybatis通过动态查询标签解决问题。
a.<if>标签
通过test属性中的表达式去判断内容是否有效,有效则将if中的sql片段拼接执行。
List<Tb_User> selectDynamically(@Param("id") Integer id,
@Param("username") String username,
@Param("password") String password,
@Param("gender") String gender,
@Param("address") String address);
<select id="selectDynamically" resultMap="Tb_User">
select *
from tb_user where 1=1 <!--由于 and 引起的sql语法问题,通过 1=1 使得语句通顺-->
<if test="id!=null and id !=''"><!-- test 为判断条件, 其查询字段为实体类属性-->
and id=#{id}
</if>
<if test="username!=null and username !=''">
and username=#{username}
</if>
<if test="password!=null and password !=''">
and password=#{password}
</if>
<if test="gender!=null and gender !=''">
and gender=#{gender}
</if>
<if test="address !=null and address !=''">
and addr =#{address}
</if>
</select>
b.<where>标签
- 只有if标签有内容的情况下才会拼接where字句;
- 若首个子句的开通为"and"或"or",where标签会将它替换去除。
<select id="selectDynamically" resultMap="Tb_User">
select *
from tb_user
<where>
<if test="id!=null and id !=''">
and id=#{id}
</if>
<if test="username!=null and username !=''">
and username=#{username}
</if>
<if test="password!=null and password !=''">
and password=#{password}
</if>
<if test="gender!=null and gender !=''">
and gender=#{gender}
</if>
<if test="address !=null and address !=''">
and addr =#{address}
</if>
</where>
</select>
c.<choose> <when> <otherwise> 标签
choose类似于java里的switch语句,when 为case ,otherwise则为default
List<Tb_User> selectDynamicallySwitch(@Param("id") Integer id,
@Param("username") String username,
@Param("password") String password,
@Param("gender") String gender,
@Param("address") String address);
<select id="selectDynamicallySwitch" resultMap="Tb_User">
select *
from tb_user
<where>
<choose>
<when test="id!=null and id !=''">
id=#{id}
</when>
<when test="username!=null and username !=''">
username=#{username}
</when>
<when test="password!=null and password !=''">
password=#{password}
</when>
<when test="gender!=null and gender !=''">
gender=#{gender}
</when>
<when test="address!=null and address !=''">
addr=#{address}
</when>
<otherwise>
id= 1//如果没有一个条件符合,则查找id = 1 的记录
</otherwise>
</choose>
</where>
</select>
public class test03 {
public static void main(String[] args) throws IOException {
String config = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(config);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = ssf.openSession();
Tb_UserMapper tb_userMapper = sqlSession.getMapper(Tb_UserMapper.class);
List<Tb_User> tb_users = tb_userMapper.selectDynamicallySwitch(null,
"ls",
"zs4231423",//注意这里账号和密码不匹配,待会看查出来的结果
null,
null);
System.out.println(Objects.isNull(tb_users));
tb_users.forEach(
tb_user -> System.out.println(tb_user)
);
sqlSession.close();
}
}
这里账号和密码不匹配,注意查看出来的结果.
证明 choose标签类似于switch,只要有一条件符合,后面的的条件就不再执行
d.<foreach>标签
传参为list或array:
List<Tb_User> selectByIds(@Param("ids") List<Integer> ids);
<select id="selectByIds" resultMap="Tb_User">
select *
from tb_user
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")" index="0">
<!--collection为传过来的对象名,必选
数组默认为array,List默认为list,自定义名加@Param()注解
item为元素名,必选
separator将元素分开
open和close分别表示在语句开始前和开始后添加自定义内容
index标识从第几索引开始
-->
#{id}
</foreach>
</select>
传参为map:
List<Tb_User> selectByAttribute(@Param("maps") Map maps);
<select id="selectByAttribute" resultMap="Tb_User">
select *
from tb_user
<where>
<foreach collection="maps" index="k" item="v">
<!-- k : key v : value -->
and ${k} = #{v}
</foreach>
</where>
</select>
public class test05 {
public static void main(String[] args) throws IOException {
String config = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(config);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = ssf.openSession();
Tb_UserMapper tb_userMapper = sqlSession.getMapper(Tb_UserMapper.class);
HashMap<String, Object> maps = new HashMap<>();
maps.put("id", 1);
maps.put("username", "zs");
List<Tb_User> tb_users = tb_userMapper.selectByAttribute(maps);
System.out.println(Objects.isNull(tb_users));
tb_users.forEach(
tb_user -> System.out.println(tb_user)
);
sqlSession.close();
}
}
这解释了传参为map的查询原理
e.<trim>标签
List<Tb_User> selectById_Username_Password(@Param("id") Integer id,
@Param("username") String username,
@Param("password") String password);
<select id="selectById_Username_Password" resultMap="Tb_User">
select *
from tb_user
<trim prefix="where" prefixOverrides="and | or">
<!--
prefix:给标签内的sql加上前缀
suffix:加后缀
prefixOverrides:去除多余前缀
suffixOverrides:去除多余后缀
-->
<if test="id != null and id !=''">
and id =#{id}
</if>
<if test="username != null and username !=''">
and username =#{username}
</if>
<if test="password != null and password !=''">
and password =#{password}
</if>
</trim>
</select>
f.<bind>标签
List<Tb_User> selectByUsernameLikely(@Param("username") String username);
<select id="selectByUsernameLikely" resultMap="Tb_User">
select *
from tb_user
where
<bind name="username" value="'%'+username+'%'"/><!--实现模糊查询-->
username like #{username}
</select>
我们可以通过 <bind> 标签实现模糊查询
2.增
void addOne(Tb_User tb_user);
<insert id="addOne">
insert into tb_user(id, username, password, gender, addr)
values (null, #{username},//由于id是主键,我们不必设值
#{password},
#{gender},
#{address})
</insert>
public class test07 {
public static void main(String[] args) throws IOException {
String config = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(config);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = ssf.openSession(true);//这里要设置参数,缺省为false,表示开启事务,true则为自动提交事务
Tb_UserMapper tb_userMapper = sqlSession.getMapper(Tb_UserMapper.class);
tb_userMapper.addOne(new Tb_User(1, "ssss", "ssss12345", "男", "河南"));
tb_userMapper.selectAll().forEach(
tb_user -> System.out.println(tb_user)
);
sqlSession.close();
}
}
3.删
void deleteById(Integer id);
<delete id="deleteById">
delete
from tb_user
where id = #{id}
</delete>
删除操作也需要设置openSession的参数值
4.改
int updateOne(Tb_User tb_user);
<update id="updateOne">
update tb_user
<set>//可去除多余的符号
<if test="username !=null and username !='' ">
username = #{username},
</if>
<if test="password !=null and password !='' ">
password = #{password},
</if>
<if test="gender !=null and gender !='' ">
gender = #{gender},
</if>
<if test="address !=null and address !='' ">
addr = #{address},
</if>
</set>
where id = #{id}
</update>
修改操作也需要设置openSession的参数值
总结
第一次尝试写CSDN,如有错误的地方,请诸位大佬指正。
以下是参考其他大佬的链接:
黑马mybatis教程全套视频教程,2天Mybatis框架从入门到精通
非常感谢这些大佬的分享