Mybatis快速入门
官网：MyBatis中文网
目录结构
创建users表，添加数据
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
`home_address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
导入依赖
在pom.xml文件中写入对应jar包的依赖
<dependencies>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.1</version>
</dependency>
<!--单元测试依赖-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
<!--添加slf4j日志api-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.30</version>
</dependency>
<!--添加logback-classic依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<!--添加logback-core依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.2.3</version>
</dependency>
</dependencies>
导入logback.xml
导入logback.xml到resource目录下
配置打印日志
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<!-- console表示当前日志信息是可以输出到控制台的-->
<appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%level] %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
</encoder>
</appender>
<logger name="com.Carter_x" level="DEBUG" additivity="false">
<appender-ref ref="Console"/>
</logger>
</configuration>
编写核心配置文件
在resource目录下创建核心配置文件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>
<typeAliases>
<typeAlias type="com.zjf.utils.User" alias="User"/>//起别名,在xml文件中可简写resultType="User"
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>//对com.mysql.cj.jdbc.Driver特别说明，这是MySQL Connector/J 8.0及以后版本的驱动类名，相对于早期的com.mysql.jdbc.Driver（主要在5.x版本中使用）：
<property name="url" value="jdbc:mysql:///batis?useSSL=false&serverTimezone=UTC"/>//batis数据库名称，& 代表&符号的意思
<property name="username" value="root"/>
<property name="password" value="1234"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 引入接口文件-->
<!-- <mapper resource="com/zjf/mapper/UserMapper.xml"/>-->
<package name="com.zjf.mapper"/>//两种方式，推荐第二种
</mappers>
</configuration>
编写User实体类
package com.zjf.utils;
import java.util.Date;
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public User() {
}
public User(Integer id, String username, Date birthday, String sex, String address) {
this.id = id;
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
查询所有
编写UserMapper接口
package com.zjf.mapper;
import com.zjf.utils.User;
import java.util.List;
public interface UserMapper {
List<User> selectAll();
}
编写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.zjf.mapper.UserMapper">
<resultMap id="userResultMap" type="com.zjf.utils.User">
<result column="home_address" property="address"/>
</resultMap>//column表示数据库中的列名，property表示Java对象中的属性名。这个resultMap的作用是在查询结果集中将"home_address"列的数据映射到User对象的address属性中。
<select id="selectAll" resultMap="userResultMap">
select * from users
</select>
</mapper>
数据库字段和实体类字段名不一致怎么处理
-
起别名(但是如果字段很多表很多就很复杂)
<select id="selectAll" resultType="User"> select id,username,birthday,sex,home_address as homeAddress from users </select> -
抽取sql片段(确定不灵活,不同的sql语句需要查询不同的字段)
<sql id="userSql"> id,username,birthday,sex,home_address homeAddress </sql> <select id="selectAll" resultType="User"> select <include refid="userSql" /> from users </select> -
使用resultMap
<resultMap id="userResultMap" type="user"> <result column="home_address" property="homeAddress"/> </resultMap> <select id="selectAll" resultMap="userResultMap"> select * from users </select>
编写测试代码
@Test
public void TestselectAll() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);//通过Resources.getResourceAsStream(resource)方法加载配置文件，将其转换为输入流
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//使用SqlSessionFactoryBuilder的build方法，传入输入流，构建SqlSessionFactory对象
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.selectAll();
for (User user : users) {
System.out.println(user);
}
结果
查询单条数据
编写UserMapper接口
User selectOne(Integer id);
编写UserMapper.xml
<select id="selectOne" resultMap="userResultMap">
select * from users where id = #{id};
</select>
编写测试代码
@Test
public void TestselectOne() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user= mapper.selectOne(2);//id = 2的user
System.out.println(user);
}
结果
多条件模糊查询
方法1.@Param注解实现
编写UserMapper接口
List<User> selectCondition(@Param("username") String username,@Param("address") String address);
@Param(“username”)指定了方法参数username在SQL语句中的参数名也是username。
@Param(“address”)指定了方法参数address在SQL语句中的参数名也是address
编写UserMapper.xml
<select id="selectCondition" resultMap="userResultMap">
select * from users where username like concat("%",#{username},"%") and home_address like concat("%",#{address},"%")//模糊查询，通过concat函数将"%"+"{username}"+ "%"和"%"+"{address}"+ "%"拼接成完整的模糊匹配条件
</select>
这里的#{username}和#{address}就是SQL中的占位符，它们对应的值是Java方法调用时传入的@Param注解的参数。MyBatis会将这些参数值绑定到对应的占位符上，而实际的数据库字段名是username和home_address。
编写测试代码
@Test
public void TestselectCondition() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users= mapper.selectCondition("张", "重庆");
for (User user : users) {
System.out.println(user);
}
}
方法2.创建对象实现
编写UserMapper接口
List<User> selectCondition(User user);
编写测试代码
@Test
public void TestselectCondition() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//创建User对象
User user1 = new User();
user1.setUsername("张");
user1.setAddress("重庆");
List<User> users= mapper.selectCondition(user1);
for (User user : users) {
System.out.println(user);
}
}
方法3.map方式实现
编写UserMapper接口
List<User> selectCondition(Map map);
编写测试代码
@Test
public void TestselectCondition() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map map = new HashMap();
map.put("username","张");
map.put("address","重庆");
List<User> users= mapper.selectCondition(map);
for (User user : users) {
System.out.println(user);
}
}
结果
动态条件查询
在上面的条件查询中,存在一个问题,如果用户没有传入条件参数,获取传入了某一个几个条件参数,该怎么写sql语句,我们需要让映射文件中的sql语句根据条件参数是否传入生成
编写UserMapper.xml
<select id="selectCondition" resultMap="userResultMap">
select * from users
<where>
<if test="username!=null and username!=''" >
username like concat("%",#{username},"%")
</if>
<if test="address!=null and address!=''" >
and home_address like concat("%",#{address},"%")
</if>
</where>
</select>
添加
编写UserMapper接口
Integer add(User user);
编写UserMapper.xml
<insert id="add" >
insert into users(username,birthday,sex,home_address) values (#{username},#{birthday},#{sex},#{address})
</insert>
编写测试代码
@Test
public void Testadd() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// SqlSession sqlSession = sqlSessionFactory.openSession(true);设为true代表自动提交事务，则可省略sqlSession.commit()
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user1 = new User();
user1.setUsername("夏洛");
user1.setAddress("上海");
Integer num = mapper.add(user1);
System.out.println(num);//num值代表数据库发生修改的行数
sqlSession.commit();//注意提交事务
sqlSession.close();
}
获取对象id
当您使用 <insert> 标签定义一个插入操作时，可以通过设置 useGeneratedKeys="true" 和 keyProperty="id" 来自动获取数据库自动生成的键值，通常是自增主键的id
<insert id="add" useGeneratedKeys="true" keyProperty="id" >
insert into users(username,birthday,sex,home_address) values (#{username},#{birthday},#{sex},#{address})
</insert>
添加测试代码
System.out.println(user1.getId());
结果
静态/动态修改
静态修改
编写UserMapper接口
Integer update(User user);
编写UserMapper.xml
<update id="update">
update users set username=#{username},birthday=#{birthday},sex=#{sex},home_address=#{address} where id=#{id}
</update>
编写测试代码
@Test
public void Testupdate() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);//这里设置为true，自动提交事务
//执行sql语句
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user1 = new User();
user1.setId(1);
user1.setUsername("袁华");
Integer num = mapper.update(user1);
System.out.println(num);
sqlSession.close();
}
结果
对象中设置的属性username成功修改，未设置的属性为null
动态修改
编写UserMapper.xml
<update id="update">
update Users
<set>
<if test="username!=null and username!=''">
username=#{username}
</if>
<if test="birthday!=null and birthday!=''">
birthday=#{birthday}
</if>
<if test="sex!=null and sex!='' ">
sex = #{sex},
</if>
<if test="address!=null and address!='' ">
home_address = #{address}
</if>
</set>
where id = #{id};
</update>
结果
只针对对象设置的属性进行修改，未设置的属性不会改变
删除/批量删除
删除
编写UserMapper接口
Integer delete(Integer id);
编写UserMapper.xml
<delete id="delete">
delete from users where id=#{id}
</delete>
编写测试代码
@Test
public void Testdelete() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//执行sql语句
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Integer num = mapper.delete(2);
System.out.println(num);
sqlSession.close();
}
结果
批量删除
编写UserMapper接口
Integer deletes(@Param("ids") int[] ids);//@Param("ids")为方法参数名,SQL语句中应该使用的名字是",与UserMapper.xmlz中collection保持一致，若不添加@param注解默认参数名为array，添加可增加可读性
编写UserMapper.xml
<delete id="deletes">
delete from users
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
foreach----动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候） ，foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符
- collection=“ids”：指定要遍历的集合，这里命名为ids。
- item=“id”：指定遍历时每个元素的变量名，这里命名为id。
- separator=“,”：指定遍历完成后各个元素之间的分隔符，这里使用逗号（,）作为分隔符。
- open=“(”：指定遍历结果的起始符号，这里使用左括号（(）作为起始符号。
- close=“)”：指定遍历结果的结束符号，这里使用右括号（)）作为结束符号。
编写测试代码
@Test
public void Testdeletes() throws IOException {
//加载核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//执行sql语句
//获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int ids[] = {1,3};
Integer num = mapper.deletes(ids);
System.out.println(num);
sqlSession.close();
}
结果
639
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
