Mybatis学习-初步认知与使用
Mybatis是一款优秀的持久层框架。且支持定制化SQL,存储过程以及高级映射
Mybatis几乎免除了所有的JDBC代码以及设置参数和获取结果集的工作,它使用简单的XML或注解来配置原始类型、接口和将数据库中的记录映射成POJO(Plain Old Java Objects,普通老式java对象)
为什么需要Mybatis:方便;传统的JDBC代码太复杂了,简化程序员编写与数据库实现交互的代码的过程
优点:
- 简单易学
- 灵活
- SQL与代码分离,提高了可维护性
- 提供映射标签,支持对象与数据库的ORM字段关系映射
- 提供对象关系映射标签,支持对象关系组件维护
- 提供XML标签,支持编写动态SQL
第一个Mybatis程序
搭建数据库
CREATE TABLE user (
id INT(20) NOT NULL,
name VARCHAR(30) DEFAULT NULL,
pwd VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (id)
)
在项目中创建一个模块Mybatis01
- 编写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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Drive"/>
<property name="url" value="jdbc:mysql://localhost:3306/learn?serverTimezone=GMT&useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="rooot "/>
<property name="password" value="123456789"/>
</dataSource>
</environment>
</environments>
<!--每一个Mapper.xml都需要在Mybatis核心配置文件中注册-->
<mappers>
<!--UserMapper.xml文件放在resource的mapper文件夹中-->
<mapper resource="mapper/UerMapper.xml"/>
</mappers>
</configuration>
- 编写Mybatis工具类
/**
* mybatis的sqlsessionfactory工具类,获取sqlSessionFactory对象
* Created by 罗星
* 2020/11/19 0019 20:16
*/
public class MybatisUtil {
private static SqlSessionFactory sqlSessionFactory;
static {
InputStream inputStream = null;
try {
String resource = "mybatis-config.xml";
inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//获取Sqlsession实例
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
//设置自动提交
//return sqlSessionFactory.openSession(true);
}
}
- 编写项目代码
- 实体类
public class User {
private int id;
private String name;
private String 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接口
public interface UserDao {
List<User> getUserList();
}
- 接口实现。由原来的Impl类转换为一个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.luoqing.dao.UserMapper">
<!--id是需要重写方法的名字-->
<select id="getUserList" resultType="com.luoqing.model.User">
select * from learn.user
</select>
</mapper>
Mapper.xml需要放在resource文件夹中才能被扫描到。如果需要放在java文件夹中则需要在主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>
</build>
- 测试
public class UserDaoTest {
@Test
public void test() {
//获取SQLSession对象
SqlSession sqlSession = MybatisUtil.getSqlSession();
try {
//方式一:getMapper
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> userList = mapper.getUserList();
//方式二:
//List<User> userList = sqlSession.selectList("com.luoqing.dao.UserDao.getUserList");
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭SQLSession
sqlSession.close();
}
}
}
可能遇到的问题:
- 配置文件没有注册:所有的Mapper.xml文件都需要在mybatis-config.xml文件中的mappers标签中注册
- 绑定接口错误:Mapper.xml的namespace属性的值需要准确对应接口的路径的名字
CRUD
实现步骤
- 在Mapper接口中添加方法
- 在Mapper.xml中添加对应标签及SQL语句
- 编写实现代码
1. select
接口
public interface UserMapper {
//根据ID查询用户
User getUserById(int id);
}
Mapper.xml
<mapper namespace="com.luoqing.dao.UserMapper">
<!--parameterType:参数类型-->
<!--resultType:SQL语句执行的返回值-->
<select id="getUserById" parameterType="int" resultType="com.luoqing.model.User">
<!--只有一个基本类型参数的情况下,可以在SQL中使用#{}获取到-->
select * from learn.user where id = #{id}
</select>
</mapper>
测试
@Test
public void getUserByIdTest() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User userById = mapper.getUserById(1);
System.out.println(userById);
sqlSession.close();
}
2. insert
接口
public interface UserMapper {
int addUser(User user);
}
Mapper.xml
<insert id="addUser" parameterType="com.luoqing.model.User">
<!--参数为自定义对象时,可以直接使用#{属性名}获取-->
insert into learn.user(id, name, pwd) values (#{id}, #{name}, #{pwd})
</insert>
测试
@Test
public void addUserTest() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int luoxing = mapper.addUser(new User(4, "luoxing", "12345"));
if (luoxing > 0) {
System.out.println("success");
}
//提交事务
sqlSession.commit();
sqlSession.close();
}
3. update
接口
public interface UserMapper {
int updateUser(User user);
}
Mapper.xml
<update id="updateUser" parameterType="com.luoqing.model.User">
update learn.user set name=#{name},pwd=#{pwd} where id = #{id}
</update>
测试
@Test
public void updateUserTest() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updateUser(new User(4, "yellow" , "12313"));
//提交事务
sqlSession.commit();
sqlSession.close();
}
4. delete
接口
public interface UserMapper {
int deleteUser(int id);
}
Mapper.xml
<delete id="deleteUser" parameterType="int">
delete from learn.user where id = #{id}
</delete>
测试
@Test
public void deleteUserTest() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUser(4);
sqlSession.commit();
sqlSession.close();
}
5. 模糊查询
- java代码传递参数时,添加通配符,防止SQL注入
List<User> userList = mapper.getUserList("%李%");
- 在SQL拼接中使用通配符,防止SQL注入
<select id="getUserList" parameterType="String" resultType="com.luoqing.model.User">
select * from learn.user where name like "%"#{value}"%"
</select>
可能存在的问题:
- 关于事务提交:增删改一定要提交事务,否则修改无法上传
好用的工具-Map
假设我们的实体类或者数据库中的表字段或者参数过多,我们可以使用map减少工作量。使用map可以随意控制属性的名称和数量
map传递参数直接在SQL中使用#{Key}取出即可
- 编写接口
public interface UserMapper {
//添加一条记录
int addUser2(Map<String, Object> map);
}
- Mapper.xml
<insert id="addUser2" parameterType="map">
insert into learn.user(id, name, pwd) values (#{userId}, #{userName})
</insert>
- 测试
@Test
public void addUser2Test() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
//map中的key可以随意
map.put("userId", 4);
map.put("userName", "luoxing");
//数据库中password默认为null,我们这里不进行赋值
mapper.addUser2(map);
sqlSession.commit();
sqlSession.close();
}
在上面的例子中,我们使用map给User表中新添加了一行数据,只需要将需要使用的字段添加进去就行了。如果使用的是实体类传参,要不就是给User类添加一个只有id和name的新的构造方法,要不就是给password赋值为null,当实体类有几十个或者上百个属性的时候,工作量会大到几乎不可能完成