一、获取MyBatis
-
maven仓库:
<dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>x.x.x</version> </dependency>
-
Github:https://github.com/mybatis/mybatis-3/releases
-
中文文档:https://mybatis.net.cn/getting-started.html
二、环境搭建
1.数据库搭建
CREATE TABLE `user` (
`id` INT ( 20 ) NOT NULL PRIMARY KEY,
`name` VARCHAR ( 30 ) DEFAULT NULL,
`pwd` VARCHAR ( 30 ) DEFAULT NULL
) ENGINE = INNODB DEFAULT charset = utf8
insert into `user` (`id`,`name`,`pwd`) values
(1,'chenyu','123456'),
(2,'cy','123456'),
(3,'admin','123456')
2.新建Maven项目
删除src文件夹,编写父项目的pom.xml文件
<?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">
<modelVersion>4.0.0</modelVersion>
<!-- 父工程 -->
<groupId>org.example</groupId>
<artifactId>myBatis</artifactId>
<version>1.0-SNAPSHOT</version>
<!-- 导入依赖 -->
<dependencies>
<!-- MySQL -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<!-- MyBatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.1</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
3.创建一个模块
- 编写核心配置文件 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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- 低版本 -->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<!-- 高版本 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
</configuration>
- 编写mybatis工具类
package com.chen.utils;
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;
//sqlSessionFactory --> sqlSession
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
// 使用Mybatis第一步:获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
// 既然有了SqlSessionFactory,顾名思义,我们就可以从中获得SqlSession的实例
// SqlSession 完全包含了面向数据库执行SQL命令所需的所有方法。
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
}
4.编写代码
- 实体类(pojo)
package com.chen.pojo;
public class User {
private int id;
private String name;
private String pwd;
public User() {
}
public User(int id, String name, String pwd) {
this.id = id;
this.name = name;
this.pwd = pwd;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", pwd='" + pwd + '\'' +
'}';
}
}
- Dao接口(dao)
package com.chen.mapper;
import com.chen.pojo.User;
import java.util.List;
public interface userMapper {
List<User> getUserList();
}
- 接口实现类(Mapper.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">
<!-- namespace=绑定一个对应的Dao或者Mapper接口-->
<mapper namespace="com.chen.mapper.UserMapper">
<!-- 查询语句 -->
<!-- id=方法名 -->
<select id="getUserList" resultType="com.chen.pojo.User">
select * from mybatis.user
</select>
</mapper>
5.测试
在src/test/java里面创建测试类
package com.chen.dao;
import com.chen.mapper.UserMapper;
import com.chen.pojo.User;
import com.chen.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserMapperTest {
@Test
public void test() {
// 1.获得SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
// 方式一:getMapper
// 2.获得Mapper
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 3.执行SQL
List<User> userList = mapper.getUserList();
// 方式二:
// List<User> userList = sqlSession.selectList("com.chen.mapper.UserMapper.getUserList");
for (User user : userList) {
System.out.println(user.toString());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 4.关闭SqlSession
sqlSession.close();
}
}
}
注意点:绑定异常
org.apache.ibatis.binding.BindingException:
Type interface com.chen.mapper.UserMapper is not known to the MapperRegistry.
MapperRegistry 不知道类型接口 com.chen.mapper.UserMapper。
解决方法:mybatis-config.xml中注册Mapper
<!-- 每一个Mapper.XML都需要在Mybatis核心配置文件注册 -->
<mappers>
<mapper resource="com/chen/mapper/UserMapper.xml"/>
</mappers>
注册了但是扫描不到mapper的文件需要在配置文件中添加扫描配置
解决方法:pom.xml增加配置
<!-- 添加扫描配置 -->
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
<!-- UTF-8配置问题 -->
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
三、SQL基本操作
1.namespace
namespace中的包名要和Dao/mapper接口包名一致
2.select
选择,查询语句;
- id:对应namespace接口中的方法名
- parameterType:参数类型
- resultType:返回值类型,可以是class也可以是基本数据类型。
3.Insert
<insert id="addUser" parameterType="com.chen.pojo.User">
insert into mybatis.user (id,name,pwd) value (#{id},#{name},#{pwd});
</insert>
4.Update
<update id="updateUser" parameterType="com.chen.pojo.User">
update mybatis.user
set name = #{name},pwd = #{pwd}
where id = #{id};
</update>
5.Delete
<delete id="deleteUser" parameterType="java.lang.Integer">
delete from mybatis.user where id = #{id}
</delete>
6.测试
注意:增删改需要提交事务,否则所做操作无效
package com.chen.dao;
import com.chen.mapper.UserMapper;
import com.chen.pojo.User;
import com.chen.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
import java.util.Scanner;
public class UserMapperTest {
@Test
public void test() {
// 1.获得SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
// 方式一:getMapper
// 2.获得Mapper
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 3.执行SQL
List<User> userList = mapper.getUserList();
// 方式二:
// List<User> userList = sqlSession.selectList("com.chen.mapper.UserMapper.getUserList");
for (User user : userList) {
System.out.println(user.toString());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 4.关闭SqlSession
sqlSession.close();
}
}
@Test
public void testGetUserById() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user.toString());
sqlSession.close();
}
@Test
public void testAddUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int row = mapper.addUser(new User(5, "zhang", "123456"));
if (row > 0) {
System.out.println("增加成功!");
}
sqlSession.commit();
sqlSession.close();
this.test();
}
@Test
public void testUpdateUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int row = mapper.updateUser(new User(2, "chen", "123456"));
if (row > 0) {
System.out.println("修改成功!");
}
sqlSession.commit();
this.test();
}
@Test
public void testDeleteUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int row = mapper.deleteUser(1);
if (row > 0) {
System.out.println("删除成功!");
}
sqlSession.commit();
this.test();
}
}
四、SQL进阶操作
1.万能Map
好处:不受字段限制就能进行数据操作
/**
* 根据条件Map获取用户
* @param map 条件
* @return 用户
*/
List<User> getUserByMap(Map<String,Object> map);
<select id="getUserByMap" parameterType="map" resultType="com.chen.pojo.User">
select * from mybatis.user
<where>
<if test="userId!=null">
id = #{userId}
</if>
<if test="userName != null">
and name like "%"#{userName}"%"
</if>
<if test="userPassword != null">
and pwd = #{userPassword}
</if>
</where>
</select>
@Test
public void testGetUserByMap() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("userPassword", "123456");
map.put("userName", "z");
List<User> userList = mapper.getUserByMap(map);
System.out.println(userList.toString());
sqlSession.close();
}
2.模糊查询
方式一:写死,好处是用户不能随便进行更多操作
name like "%"#{userName}"%"
方式二:放开,用户可以通过不同的字符集进行不同的操作效果
map.put("userName", "%z%");
name like #{userName}
3.#{}使用
- MyBatis处理 #{ } 占位符,使用的 JDBC 对象是 PreparedStatement 对象,执行sql语句的效率更高。
- 使用 PreparedStatement 对象,能够避免 sql 注入,使得sql语句的执行更加安全。
- #{ } 常常作为列值使用,位于sql语句中等号的右侧;
- #{ } 位置的值与数据类型是相关的。
4.${}使用
- MyBatis处理 ${ } 占位符,执行sql语句的效率相对于 #{ } 占位符要更低。
- ${ } 占位符的值,使用的是字符串连接的方式,有 sql 注入的风险,同时也存在代码安全的问题。
- ${ } 占位符中的数据是原模原样的,不会区分数据类型。
- ${ } 占位符常用作表名或列名,这里推荐在能保证数据安全的情况下使用 ${ }。
/**
* 自定义获取User
* @return 用户集合
*/
List<User> getUserWhere(String where);
<select id="getUserWhere" resultType="com.chen.pojo.User">
select *
from mybatis.user
where ${where}
</select>
@Test
public void testGetUserWhere() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserWhere("id = 1");
System.out.println(userList.toString());
sqlSession.close();
}
}。
/**
* 自定义获取User
* @return 用户集合
*/
List<User> getUserWhere(String where);
<select id="getUserWhere" resultType="com.chen.pojo.User">
select *
from mybatis.user
where ${where}
</select>
@Test
public void testGetUserWhere() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserWhere("id = 1");
System.out.println(userList.toString());
sqlSession.close();
}