mybatis使用详解
一、环境搭建
1. 目录结构
2. 导入依赖
<!-- 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.13</version>
</dependency>
<!-- log4j日志 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
3. 创建配置文件
创建 jdbc.properties 数据库配置文件和 mybatis 配置文件 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>
<!--
mybatis核心配置文件中的标签必须要按照指定的顺序配置
properties?,settings?,typeAliases?,
typeHandlers?,objectFactory?,
objectWrapperFactory?,reflectorFactory?,
plugins?,environments?,
databaseIdProvider?,mappers?
-->
<!-- 引入properties文件，此后就可以在当前文件中使用${key}的方式访问value-->
<properties resource="jdbc.properties"/>
<!--
typeAliases类型别名
设置类型别名，即为某个具体的类型设置一个别名
在Mybatis范围中，就可以使用别名表示一个具体的类型
-->
<typeAliases>
<!--
type：设置需要起别名的类型
alias：设置某个类型的别名
如果没有设置alias，当前的类型就拥有默认的别名，即类名且不区分大小写
-->
<typeAlias type="com.example.test.datasource.entities.User" alias="User"/>
<!-- <typeAlias type="com.example.test.datasource.entities.User" alias="abc"/>-->
<!--通过包类设置类型别名，指定包下所有的类型将全部拥有默认的别名，即类名且不区分大小写-->
<!-- <package name="com.example.test.datasource.entities"/>-->
</typeAliases>
<!-- environments:配置连接数据库的环境
default:设置默认使用的环境的id
属性：
id：设置环境的唯一标识，不能重复
-->
<environments default="development">
<environment id="development">
<!-- transactionManager：设置事务管理器-->
<!-- 属性：-->
<!-- type:设置事务管理的方式-->
<!-- type="JDBC|MANAGED-->
<!-- JDBC:表示使用JDBC原生的事务管理方式-->
<!-- MANAGED：被管理，例如spring-->
<transactionManager type="JDBC"/>
<!-- dataSource：设置数据源
属性：
type：设置数据源类型
type="POOLED|UNPOOLED|JNDI"
POOLED:表示使用数据库连接池
UNPOOLED：表示不是要给你数据库连接池
JNDI：表示使用上下文中的数据源
-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- 引入mybatis的映射文件-->
<mappers>
<mapper resource="mappers/User.xml"/>
</mappers>
</configuration>
jdbc.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=1085202075
4. 创建测试数据库
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` char(1) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
5. 创建日志配置文件 log4j.properties
log4j.rootLogger=DEBUG,stdout
# MyBatis logging configuration...
# MyBatis
log4j.logger.com.example.test.datasource.mappers.UserMapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
二、创建服务与映射
1. 创建t_user表对应的实体类
package com.example.test.datasource.entities;
public class User {
private Long id;
private String username;
private String password;
private Integer age;
private String gender;
private String email;
public User(){}
public User(String username, String password, Integer age, String gender, String email) {
this.username = username;
this.password = password;
this.age = age;
this.gender = gender;
this.email = email;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
", email='" + email + '\'' +
'}';
}
}
2. 创建接口与接口实现类
创建UserService接口与接口实现类UserServiceImpl
UserService.java
package com.example.test.datasource.service;
import com.example.test.datasource.entities.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface UserService {
int insertUser(User user);
int updateUser(User user, User userCondition);
int deleteById(int id);
List<User> queryAll();
List<User> queryAllByIds(Integer[] ids);
List<User> queryAllByIds2(List<Integer> list);
List<User> queryAllByIds3(Map<String, Object> mapIds);
List<User> queryAll2();
User queryById(int id, String fields);
}
UserServiceImpl.java
package com.example.test.datasource.service;
import com.example.test.datasource.entities.User;
import com.example.test.datasource.mappers.UserMapper;
import java.util.List;
import java.util.Map;
public class UserServiceImpl implements UserService {
public UserMapper userMapper;
public UserServiceImpl(UserMapper userMapper){
this.userMapper = userMapper;
}
@Override
public int insertUser(User user) {
return userMapper.insertUser(user);
}
@Override
public int updateUser(User user, User userCondition) {
return userMapper.updateUser(user, userCondition);
}
@Override
public int deleteById(int id) {
return userMapper.deleteById(id);
}
@Override
public List<User> queryAll() {
return userMapper.queryAll();
}
@Override
public List<User> queryAllByIds(Integer[] ids) {
return userMapper.queryAllByIds(ids);
}
@Override
public List<User> queryAllByIds2(List<Integer> list) {
return userMapper.queryAllByIds2(list);
}
@Override
public List<User> queryAllByIds3(Map<String, Object> mapIds) {
return userMapper.queryAllByIds3(mapIds);
}
@Override
public List<User> queryAll2() {
return userMapper.queryAll2();
}
@Override
public User queryById(int id, String fields) {
return userMapper.queryById(id, fields);
}
}
3. 创建mybatis映射文件
创建mybatis映射文件，MyBatis使用Mapper来实现映射，而且Mapper必须是接口。
package com.example.test.datasource.mappers;
import com.example.test.datasource.entities.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface UserMapper {
int insertUser(User user);
int updateUser(@Param("user") User user, @Param("userCondition") User userCondition);
int deleteById(int id);
List<User> queryAll();
List<User> queryAllByIds(Integer[] ids);
List<User> queryAllByIds2(List<Integer> list);
List<User> queryAllByIds3(Map<String, Object> mapIds);
@Select("select * from t_user")
List<User> queryAll2();
/**
* 使用注解传递多个参数
*/
@Select("select ${fields} from t_user where id = #{id}")
User queryById(@Param("id") int id, @Param("fields") String fields);
}
4. 配置xml SQL映射文件
Mapper接口映射文件可以直接在方法上添加@Select注解用来执行SQL查询，对于复杂的SQL语句则需创建对应的xml文件来处理。下面创建一个文件 src/main/resources/mappers/User.xml，文件对应mybatis-config.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.example.test.datasource.mappers.UserMapper">
<!-- namespace需和UserMapper映射接口全类名保持一致 -->
<!--
mapper接口和映射文件要保证两个一致
1、mapper接口的全类名和映射文件的namespace一致
2、mapper接口中的方法名要和映射文件中的sql的id保持一致
parameterType：传入数据的类型
resultType：返回结果类型（这里返回list，mybatis会自动转成User的集合类型）
${value}：对于字符串拼接中的占位符，必须用value
#{ss}：普通占位符，ss和任意取
parameterType="User" 值User对应配置文件typeAlias设置的别名
-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id" parameterType="User">
insert into t_user value (#{id}, #{username}, #{password}, #{age}, #{gender}, #{email})
</insert>
<update id="updateUser">
update t_user
<set>
<if test="user.username != null">username=#{user.username},</if>
<if test="user.password != null">password=#{user.password},</if>
<if test="user.age != null">age=#{user.age},</if>
<if test="user.gender != null">gender=#{user.gender},</if>
<if test="user.email != null">email=#{user.email}</if>
</set>
<trim prefix="where" prefixOverrides="AND |OR ">
<if test="userCondition.id != null">id=#{userCondition.id}</if>
<if test="userCondition.username != null">AND username=#{userCondition.username}</if>
<if test="userCondition.password != null">AND password=#{userCondition.password}</if>
<if test="userCondition.age != null">AND age=#{userCondition.age}</if>
<if test="userCondition.gender != null">AND gender=#{userCondition.gender}</if>
<if test="userCondition.email != null">AND email=#{userCondition.email}</if>
</trim>
</update>
<delete id="deleteById">
delete
from t_user
where id = #{id};
</delete>
<select id="queryAll" resultType="User">
select * from t_user
</select>
<select id="queryAllByIds">
select * from t_user where id in
<foreach collection="array" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<select id="queryAllByIds2">
select * from t_user
<foreach collection="list" item="item" index="index" open=" where id in(" separator="," close=")">
#{item}
</foreach>
</select>
<select id="queryAllByIds3">
select * from t_user
<foreach collection="listIds" item="item" index="index" open=" where id in(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
三、执行CURD操作
创建SqlSessionFactory工厂方法，使用工厂方法获取SqlSession类，通过SqlSession类获取对应映射接口的实现类
1. 执行select查询
package com.example.test;
import com.example.test.datasource.entities.User;
import com.example.test.datasource.mappers.UserMapper;
import com.example.test.datasource.service.UserServiceImpl;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.SQL;
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.text.SimpleDateFormat;
import java.util.*;
public class App
{
public static void main( String[] args ) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
UserServiceImpl userService = new UserServiceImpl(sqlSession.getMapper(UserMapper.class));
List<User> users = userService.queryAll();
System.out.println(users);
} catch (Exception e) {
System.err.println(e.getMessage());
}
}
}
输出结果：
public static void test() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
UserServiceImpl userService = new UserServiceImpl(sqlSession.getMapper(UserMapper.class));
User user = userService.queryById(1, "id,username");
} catch (Exception e) {
System.err.println(e.getMessage());
}
}
输出结果：
2. 执行insert into
这里执行插入语句需注意默认是开启事务并且不会自动提交的，要成功提交需执行 sqlSession.commit(); 手动提交事务。还有一个方法是 SqlSession sqlSession = sqlSessionFactory.openSession(true); 加上参数true，就会自动提交事务。如需获取自增id需要在User.xml 里面给insert标签加上属性 useGeneratedKeys=“true” 与 keyProperty=“id”，自增的id会赋值到keyProperty指定的实体类对应的字段上。
// SqlSession sqlSession = sqlSessionFactory.openSession(true); //加上参数true，会自动提交事务
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
UserServiceImpl userService = new UserServiceImpl(sqlSession.getMapper(UserMapper.class));
User user = new User("小石头", "123456789", 21, "男", "123456789@qq.com");
int res = userService.insertUser(user);
// 提交事务
sqlSession.commit();
System.out.println((res == 0 ? "新增失败" : "新增成功") + " 自增id=" + user.getId());
} catch (Exception e) {
System.err.println(e.getMessage());
}
输出结果：
3. 执行update
try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
UserServiceImpl userService = new UserServiceImpl(sqlSession.getMapper(UserMapper.class));
User condition = userService.queryById(1, "id,username,email");
// 更新字段
User user = new User();
user.setEmail("newEmail@qq.com");
int res = userService.updateUser(user, condition);
System.out.println((res == 0 ? "更新失败" : "更新成功") + " res=" + res);
} catch (Exception e) {
System.err.println(e.getMessage());
}
输出结果：
4. 执行delete
try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
UserServiceImpl userService = new UserServiceImpl(sqlSession.getMapper(UserMapper.class));
int res = userService.deleteById(1);
System.out.println((res == 0 ? "删除失败" : "删除成功") + " res=" + res);
} catch (Exception e) {
System.err.println(e.getMessage());
}
输出结果：
ps：初学者有不准确的地方希望多多指正😘😘😘
684
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
