本篇博客内容:
- 初始配置流程
- 基于Mapper接口开发
- MyBatis全局配置文件
- CRUD 增删改查操作
1 初始配置流程
实际开发不应用此方法
1.1 导入相关包
方便打印相关信息,加入 log4j.xml 配置文件至 classpath 下
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration
xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT"
class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern"
value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug" />
</logger>
<logger name="org.apache.ibatis">
<level value="info" />
</logger>
<root>
<level value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
1.2 创建 bean 和 配置文件
Member.class
public class Member {
private Integer memberId;
private String name;
private Integer balance;
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.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ssm?serverTimezone=Hongkong" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!--
-->
<mappers>
<mapper resource="cyt/mybatis/mapper/MemberMapper.xml" />
</mappers>
</configuration>
MemberMapper.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="abcd">
<select id="selectMember" resultType="cyt.mybatis.bean.Member">
SELECT member_id memberId, name, balance FROM ssm.tbl_member WHERE member_id = #{id}
</select>
</mapper>
1.3 Junit 测试
@Test
void test() throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
System.out.println(sqlSessionFactory);
SqlSession session = sqlSessionFactory.openSession();
System.out.println(session);
try {
Member member = session.selectOne("abcd.selectMember", 1);
System.out.println(member);
} finally {
session.close();
}
}
注意
- 在配置 Mapper 的配置文件时,namespace中在这个例子中可以随便写,如 abcd,后面以及实际应用不会这么做!
- 在使用
Member member = session.selectOne("abcd.selectMember", 1);
获取到内容封装到 member 时,是按照你获取到的结果集列名(并非数据库表的字段名!)和 bean 的属性一一对应,例如这里我的数据库表中第一项为 member_id,但 bean 中为 memberId,故使用别名才能正确封装,sql语句:SELECT member_id memberId, name, balance FROM ssm.tbl_member WHERE member_id = ? 而不使用 select * from…
输出信息:
org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@69ee81fc
org.apache.ibatis.session.defaults.DefaultSqlSession@17503f6b
DEBUG 10-18 11:28:49,373 ==> Preparing: SELECT member_id memberId, name, balance FROM ssm.tbl_member WHERE member_id = ? (BaseJdbcLogger.java:137)
DEBUG 10-18 11:28:49,398 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 10-18 11:28:49,421 <== Total: 1 (BaseJdbcLogger.java:137)
Member [memberId=1, name=Tom, balance=55]
2 基于Mapper接口开发
实际则是基于 dao 接口的开发,需要自己写一个 dao,但不需要写接口实现,mybatis 帮你实现
!!!以下操作是在上一节内容基础上修改而来
2.1 创建dao
MemberDao.class
public interface MemberDao {
public Member getMemberById(Integer memberId);
}
2.2 配置 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">
<mapper namespace="cyt.mybatis.dao.MemberDao">
<select id="getMemberById" resultType="cyt.mybatis.bean.Member">
SELECT member_id memberId, name, balance FROM ssm.tbl_member WHERE member_id = #{id}
</select>
</mapper>
- 此前提过配置 namespace 可以随便写一串字符,但现在用接口开发则必须指明你的 dao 完整类名
- <select>中设置属性 id 为 dao 中特定的方法名,如此一来才可以把 dao 和 sql语句结合起来
3 Mybatis全局配置文件
在MyBatis框架的核心配置文件中,<configuration>元素是配置文件的根元素,其他元素都要在<configuration>元素内配置。
- properties
- settings
- typeAliases
- typeHandlers
- objectFactory
- plugins
- environments
- databaseIdProvider
- mappers
特别注意!所有的配置元素是有顺序的,这些子元素必须按照由上到下的顺序进行配置,否则 MyBatis在解析XML配置文件的时候会报错
3.1 <properties>元素
- 编写db.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?serverTimezone=Hongkong
jdbc.username=root
jdbc.password=123456
- 配置<properties… />属性
<properties resource="db.properties" />
- 修改配置文件中数据库连接的信息
<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>
3.2 <setting>元素
<!-- 设置 -->
<settings>
<setting name="cacheEnabled" value="true" />
<setting name="lazyLoadingEnabled" value="true" />
<setting name="multipleResultSetsEnabled" value="true" />
<setting name="useColumnLabel" value="true" />
<setting name="useGeneratedKeys" value="false" />
<setting name="autoMappingBehavior" value="PARTIAL" />
...
</settings>
举例:
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
该设置可以让数据库表中习惯设置名为 xxxx_xxxx 和 java中驼峰式命名相兼容。即在前面我们提到的不能用select * from…来作为sql查询,必须用一个别名使得和bean中一致,但如果设置该属性为true,即可直接用星号查询。
<mapper namespace="cyt.mybatis.dao.MemberDao">
<select id="getMemberById" resultType="cyt.mybatis.bean.Member">
SELECT * FROM ssm.tbl_member WHERE member_id = #{id}
</select>
</mapper>
3.3 <typeAliases>元素
<typeAliases>元素用于为配置文件中的Java类型设置一个简短的名字,即设置别名。别名的设置与XML配置相关,其使用的意义在于减少全限定类名的冗余。
<typeAliases>
<typeAlias type="cyt.mybatis.bean.Member" alias="Member"/>
</typeAliases>
type 中指定全类名, alias 表示指定别名
当类过多时可以通过自动扫描包的形式自定义别名
<typeAliases>
<package name="cyt.mybatis.bean"/>
</typeAliases>
这种方式只能指定特定包,在包中扫描,别名不能特别指定,默认为类名,如 Member
3.4 <mappers>元素
- 使用类路径引入
<mappers>
<mapper resource="cyt/mybatis/mapper/MemberMapper.xml" />
</mappers>
- 使用包名引入
<mappers>
<package name="cyt.mybatis.dao"/>
</mappers>
使用包名引入要保证 xml 和 dao 同名,且在同一个包下
注意记得 mapper.xml 中 namespace 要对应上
4 CRUD 增删改查
在Junit设置初始化
private SqlSession session;
@BeforeEach
void init() throws Exception{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
session = sqlSessionFactory.openSession();
}
dao接口中添加方法
public interface MemberMapper {
public Member getMemberById(Integer memberId);
public int addMember(Member member);
public int updateMember(Member member);
public void deleteMemberById(Integer memberId);
}
4.1 insert元素
<!-- public void addMember(Member member); -->
<insert id="addMember" parameterType="Member"
keyProperty="memberId" useGeneratedKeys="true">
insert into tbl_member (name,balance) values (#{name},#{balance});
</insert>
测试
@Test
void testAdd() throws Exception {
try {
MemberMapper dao = session.getMapper(MemberMapper.class);
Member member = new Member(null,"Jiyong",350);
int result = dao.addMember(member);
session.commit(); // 修改完 session 要 commit
System.out.println(member);
System.out.println(result);
} finally {
session.close();
}
}
返回插入成功的数据生成的主键值:
由于有自增主键,故在传入 member 时new Member(null,"Jiyong",350)
传入主键为空,在后面打印出来也会为空。
故可以使用 keyProperty,useGeneratedKeys 两个属性把自增主键返回,且此方法会修改传入的对象,例如这里把 member 打印出来可看出第一个 id 属性已被修改
4.2 delete元素
<!-- public void deleteMemberById(Integer memberId); -->
<delete id="deleteMemberById">
delete from tbl_member where member_id = #{id}
</delete>
测试
@Test
void testDelete() throws Exception {
try {
MemberMapper dao = session.getMapper(MemberMapper.class);
dao.deleteMemberById(5);
session.commit(); // 修改完 session 要 commit
} finally {
session.close();
}
}
4.3 update元素
<!-- public void updateMember(Member member); -->
<update id="updateMember" parameterType="Member">
update tbl_member set name=#{name}, balance=#{balance} where member_Id = #{memberId}
</update>
@Test
void testUpdate() throws Exception {
try {
MemberMapper dao = session.getMapper(MemberMapper.class);
Member member = new Member(1,"Chen",315);
dao.updateMember(member);
session.commit(); // 修改完 session 要 commit
} finally {
session.close();
}
}
4.4 select元素
<!-- public Member getMemberById(Integer memberId); -->
<select id="getMemberById" resultType="Member">
SELECT member_id memberId, name, balance FROM ssm.tbl_member WHERE member_id = #{id}
<!-- SELECT * FROM ssm.tbl_member WHERE member_id = #{id} -->
</select>
@Test
void testSearch() throws Exception {
try {
MemberMapper dao = session.getMapper(MemberMapper.class);
Member member = dao.getMemberById(7);
System.out.println(member);
} finally {
session.close();
}
}
- 在对数据库做操作后都会有表被修改的行数,可以在dao方法中设置返回值为int,直接自动获取被修改的行数
- 修改完必须
session.commit()
4.5 sql元素
在一个映射文件中,通常需要定义多条SQL语句,这些SQL语句的组成可能有一部分是相同的(如多条select语句中都查询相同的id、username、jobs字段),如果每一个SQL语句都重写一遍相同的部分,势必会增加代码量,导致映射文件过于臃肿。那么有没有什么办法将这些SQL语句中相同的组成部分抽取出来,然后在需要的地方引用呢
这里举简单例子
定义一个包含id、username、jobs和phone字段的代码片段
<sql id="customerColumns">id,username,jobs,phone</sql>
在其他语句中
<select id="findCustomerById" parameterType="Integer"
resultType="com.po.Customer">
select <include refid="customerColumns"/>
from t_customer
where id = #{id}
</select>