MyBatis
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。
MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
1、核心配置文件
resources/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="yan">
<!--某个环境配置-->
<environment id="yan">
<!--事务管理器,jdbc/manager -->
<transactionManager type="JDBC"/>
<!--数据源配置,unpooled/pooled/jndi-->
<dataSource type="POOLED">
<!-- 创建连接的基本配置 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///test?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
<!-- 还有连接池的其它配置参数-->
</dataSource>
</environment>
</environments>
<!-- 注册映射元文件-->
<mappers>
<mapper resource="com/yan/dao/UserMapper.xml"/>
</mappers>
</configuration>
2、创建表
- 分析需求
- 定义模式
- 规范化
drop database test;
create database test default character set utf8;
use test;
create table t_users(
id bigint primary key auto_increment,
username varchar(20) not null unique,
password varchar(20) not null,
birth datetime default now(),
sex boolean default 1,
salary numeric(8,2)
)engine=innodb default charset utf8;
3、根据表结构定义对应的实体类
@Data
public class UserBean implements Serializable {
private Long id;
private String username;
private String password;
private Date birth;
private Boolean sex;
private Double salary;
}
4、定义映射元文件
一般名称方式为【表名/实体类名-mapper.xml】
resources/com/yan/mapper/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.yan.dao.UserMapper">
</mapper>
5、为了保留测试代码,所以引入 junit—回归测试\单元测试
添加依赖
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
测试类
test/java目录下
public class UserMapperTest {
private SqlSessionFactory factory;
@Before //声明bb方法是在每个test方法执行之前执行
public void bb()throws Exception{
Reader r= Resources.getResourceAsReader("mybatis-config.xml");
factory=new SqlSessionFactoryBuilder().build(r);
}
@Test //测试方法
public void test1(){}
}
插入数据
MyBatis不会生成sql语句,所以任何操作都需要在映射元文件种定义相关的sql语句
<?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.yan.dao.UserMapper">
<insert id="save" parameterType="com.yan.entity.UserBean">
insert into t_users(username,password) values(#{username},#{password})
</insert>
</mapper>
然后在接口中定义对应的方法声明
public interface UserMapper {
int save(UserBean user);
}
单元测试
public class UserMapperTest {
private SqlSessionFactory factory;
@Before
public void bb()throws Exception{
Reader r= Resources.getResourceAsReader("mybatis-config.xml");
factory=new SqlSessionFactoryBuilder().build(r);
}
@Test
public void testSave(){
SqlSession session=factory.openSession();
UserMapper userMapper=session.getMapper(UserMapper.class);
UserBean user=new UserBean();
user.setUsername("猴子");
user.setPassword("123456");
int len=userMapper.save(user);
session.commit();
session.close();
}
}
插入完成后要求返回自增长的id值
针对mysql的auto_increment,在配置上添加设置useGeneratedKeys=“true”
<insert id="save" parameterType="com.yan.entity.UserBean" useGeneratedKeys="true" keyProperty="id">
insert into t_users(username,password) values(#{username},#{password})
</insert>
修改单元测试
public class UserMapperTest {
private SqlSessionFactory factory;
@Before
public void bb()throws Exception{
Reader r= Resources.getResourceAsReader("mybatis-config.xml");
factory=new SqlSessionFactoryBuilder().build(r);
}
@Test
public void testSave(){
SqlSession session=factory.openSession();
UserMapper userMapper=session.getMapper(UserMapper.class);
UserBean user=new UserBean();
user.setUsername("猴子");
user.setPassword("123456");
int len=userMapper.save(user);
session.commit();
session.close();
Assert.assertEquals(1,len);
if(len>0){
System.out.println(user.getId());
}
}
}
删除数据
- 逻辑删除和物理删除
修改映射元文件,添加delete操作配置
<delete id="remove" parameterType="long">
delete from t_users where id=#{id} //针对简单类型,#{}用于指代这个参数,而不是参数中的某个属性值,这个名称可以随意写
</delete>
修改接口,新增方法
public interface UserMapper {
int save(UserBean user);
int remove(Long id); //对应映射元文件种的<delete>
}
单元测试
@Test
public void testRemove(){
SqlSession session=factory.openSession();
UserMapper userMapper=session.getMapper(UserMapper.class);
int len=userMapper.remove(1L);
if(len>0)
System.out.println("删除成功!");
Assert.assertEquals(1,len);
session.commit();
session.close();
}
按照id加载数据
修改映射元文件,添加select操作配置
<select id="load" parameterType="long" resultType="com.yan.entity.UserBean">参数是long类型,而返回值为UserBean类型
select * from t_users where id=#{id}
</select>
修改接口,新增方法
public interface UserMapper {
int save(UserBean user);
int remove(Long id);
UserBean load(Long id); //对应的就是<select id="load">
}
单元测试
@Test
public void testLoad(){
SqlSession session=factory.openSession();
UserMapper userMapper=session.getMapper(UserMapper.class);
UserBean user=userMapper.load(3L);
System.out.println(user);
session.commit();
session.close();
}
修改操作
按照id修改数据
修改映射元文件,添加update操作配置
<update id="modify" parameterType="com.yan.entity.UserBean">
update t_users set username=#{username},password=#{password} where id=#{id}
</update>
修改接口,新增方法
public interface UserMapper {
int save(UserBean user);
int remove(Long id);
UserBean load(Long id);
int modify(UserBean user); //对应<update id="modify">对应名称
}
单元测试
@Test
public void testModify() {
SqlSession session = factory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
UserBean user = userMapper.load(3L);
user.setUsername("小吕");
user.setPassword("666666");
int len=userMapper.modify(user);
if(len>0) System.out.println("修改成功!");
session.commit();
session.close();
}
查询所有数据
修改映射元文件,添加select操作配置
<select id="selectAll" resultType="com.yan.entity.UserBean"> 注意虽然返回的是一个集合,但是不能写集合类型,应该定义集合元素的类型
select * from t_users
</select>
修改接口,新增方法
public interface UserMapper {
int save(UserBean user);
int remove(Long id);
UserBean load(Long id);
int modify(UserBean user);
List<UserBean> selectAll(); //对应的是<select id="selectAll">
}
单元测试
@Test
public void testSelectAll() {
SqlSession session = factory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
List<UserBean> userList=userMapper.selectAll();
userList.forEach(System.out::println);
session.close();
}
查询总行数
修改映射元文件,添加select操作配置
<select id="selectCount" resultType="long"> 返回结果值为long类型,没有参数
select count(*) from t_users
</select>
修改接口,新增方法
public interface UserMapper {
... ...
long selectCount();//对应<select id="selectCount">
}j
单元测试
@Test
public void testSelectCount() {
SqlSession session = factory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
long len=userMapper.selectCount();
System.out.println(len);
Assert.assertEquals(2,len);
session.close();
}
不使用接口的调用方式
- 属于比较老的调用方法
int len=session.insert("com.yan.dao.UserMapper.save",user); insert表示执行<insert>操作,对应的id为[namespace+id]
int len=session.delete("com.yan.dao.UserMapper.remove",1L);
UserBean user=session.selectOne("com.yan.dao.UserMapper.load",3L);
int len=session.update("com.yan.dao.UserMapper.modify",user);
List<UserBean> userList=session.selectList("com.yan.dao.UserMapper.selectAll");
long len=session.selectOne("com.yan.dao.UserMapper.selectCount");
最大的问题是:不能进行静态检查