基本功能实现的项目结构,将SqlSessionFactory 使用工具类进行封装。映射文件的名称要与接口的名称一致,在映射文件中实现接口中定义的方法的sql语句。核心配置文件用于配置数据库连接的基本信息和引入映射文件。
(一)框架
Maven导入MyBatis配置
<dependencies>
<!-- 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.12</version>
<scope>test</scope>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!-- log4j日志 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
Log4j日志配置文件
<?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>
Mybatis核心配置文件
<?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/test?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<package name="mapper/UserMapper.xml"/>
</mappers>
</configuration>
创建接口
public interface UserMapper {
int insertUser();
}
映射文件
<?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="mybatis.mapper.UserMapper">
<!--int insertUser();-->
<insert id="insertUser">
insert into User values('admin','22','admin')
</insert>
</mapper>
进行测试
@Test
public void InsertTest() throws IOException {
// 获取核心配置文件的输入流
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
// 获取sql的会话对象sqlsession,是mybatis提供的操作数据库的对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获取接口的代理实现类对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 调用mapper接口中的方法
int result = mapper.insertUser();
System.out.println("结果:" + result);
// 提交事务
sqlSession.commit();
sqlSession.close();
}
使用工具类封装,openSession(true)可以自动提交事务
public static SqlSession getSqlSession(){
SqlSession sqlSession = null;
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
sqlSession = sqlSessionFactory.openSession(true);
} catch (IOException e) {
throw new RuntimeException(e);
}
return sqlSession;
}
select 语句返回值的获取(单条,多条)
@Test
public void testSelect(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUsername();
System.out.println(user);
sqlSession.close();
}
@Test
public void testSelectAllUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> list = mapper.getAllUser();
list.forEach(System.out::println);
sqlSession.close();
}
(二)获取参数
单个参数输入
<select id="getUsername" resultType="mybatis.pojo.User">
select * from user where name = #{name}
</select>
@Test
public void testGetUserByName(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUsername("w");
System.out.println(user);
}
多个参数输入
<select id="checkLogin" resultType="mybatis.pojo.User">
select * from user where name = #{arg0} and password = #{arg1}
</select>
@Test
public void testCheckLogin(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.checkLogin("w","123");
System.out.println(user);
}
map集合类型的参数输入
<select id="checkLoginByMap" resultType="mybatis.pojo.User">
select * from user where username = #{username} and password = #{password}
</select>
@Test
public void testCheckLoginByMap(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("username","w");
map.put("password","123");
User user = mapper.checkLoginByMap(map);
System.out.println(user);
}
使用param注解
User checkLoginByParam(@Param("username") String username,@Param("password") String password);
<select id="checkLoginByParam" resultType="mybatis.pojo.User">
select * from user where username = #{username} and password = #{password}
</select>
@Test
public void testCheckLoginByParam(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.checkLoginByParam("w","123");
System.out.println(user);
}
(三)查询
创建接口
public interface SelectMapper {
User1 getUsername(@Param("id") int age);
List<User1> getAllUser();
Integer getCount();
Map<String,Object> getUserByAgeToMap(@Param("id") int age);
// List<Map<String,Object>> getAllUserToMap();
@MapKey("age")
Map<String,Object> getAllUserToMap();
}
映射文件
<?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="mybatis.mapper.SelectMapper">
<select id="getUsername" resultType="mybatis.pojo.User1">
select * from t_user where age = #{id}
</select>
<select id="getAllUser" resultType="mybatis.pojo.User1">
select * from t_user
</select>
<select id="getCount" resultType="Integer">
select count(*) from t_user
</select>
<select id="getUserByAgeToMap" resultType="map">
select * from t_user where age = #{id}
</select>
<select id="getAllUserToMap" resultType="map">
select * from t_user
</select>
</mapper>
测试类
public class SelectMapperTest {
@Test
public void testGetUserByAge(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
User1 user1 = mapper.getUsername(1);
System.out.println(user1);
}
@Test
public void testGetAllUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
List<User1> list = mapper.getAllUser();
list.forEach(System.out::println);
}
@Test
public void testGetCount(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Integer count = mapper.getCount();
System.out.println(count);
}
@Test
public void testGetUserToMap(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String,Object> map = mapper.getUserByAgeToMap(1);
System.out.println(map);
}
@Test
public void testGetAllUserToMap(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
// List<Map<String,Object>> list = mapper.getAllUserToMap();
Map<String,Object> map = mapper.getAllUserToMap();
System.out.println(map);
}
}
(四)模糊查询、批量删除
public interface SpecialSQLMapper {
List<Map<String,Object>> getUserByLike(@Param("mohu") String mohu);
void DeleteMoreUser(@Param("ids") String ids);
}
<?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="mybatis.mapper.SpecialSQLMapper">
<select id="getUserByLike" resultType="map">
select * from t_user where name like '%${mohu}%'
</select>
<delete id="DeleteMoreUser" >
delete from t_user where age in(${ids})
</delete>
</mapper>
测试类
public class SpecialSQLTest {
@Test
public void testSpecialSQL(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
List<Map<String, Object>> list = mapper.getUserByLike("E");
System.out.println(list);
}
@Test
public void testDeleteMoreUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
mapper.DeleteMoreUser("1,2");
}
}
(五)resultMap和映射关系
在MyBatis核心配置中来处理字段(将下划线映射为驼峰)
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
使用resultMap将数据库的字段与实现类匹配
<?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="mapper.EmpMapper">
<resultMap id="empResultMap" type="pojo.Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
</resultMap>
<select id="getEmpByEmpId" resultMap="empResultMap">
select * from t_emp where emp_id = #{empId}
</select>
</mapper>
级联方式处理多对一映射
<resultMap id="empAndDeptResultMap" type="pojo.Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<result column="dept_id" property="dept.deptId"></result>
<result column="dept_name" property="dept.deptName"></result>
</resultMap>
<select id="getEmpAndDeptByEmpId" resultMap="empAndDeptResultMap">
select t_emp.*,t_dept.*
from t_emp
left join t_dept on t_emp.dept_id = t_dept.dept_id
where t_emp.emp_id = ${empId}
</select>
association:处理实体类类型的属性
<resultMap id="empAndDeptResultMap" type="pojo.Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<association property="dept" javaType="pojo.Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
</association>
</resultMap>
<select id="getEmpAndDeptByEmpId" resultMap="empAndDeptResultMap">
select t_emp.*,t_dept.*
from t_emp
left join t_dept on t_emp.dept_id = t_dept.dept_id
where t_emp.emp_id = ${empId}
</select>
(五)动态SQL
if标签
public interface dynamic {
List<Emp> getEmpByCondition(Emp emp);
}
<mapper namespace="dynamic">
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp where
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
</mapper>
@Test
public void TestIf(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
dynamic mapper = sqlSession.getMapper(dynamic.class);
Emp emp = new Emp(null,"张三",20,"男");
List<Emp> list = mapper.getEmpByCondition(emp);
list.forEach(System.out::println);
}
where标签
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<where>
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</where>
</select>
trim标签
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<trim prefix="where" suffixOverrides="and">
<if test="empName != null and empName != ''">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="gender != null and gender != ''">
gender = #{gender}
</if>
</trim>
</select>
choose、when、otherwise标签
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<where>
<choose>
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<when test="gender != null and gender != ''">
gender = #{gender}
</when>
<otherwise>
emp_id = 2
</otherwise>
</choose>
</where>
</select>
foreach标签,用于批量操作
void insertMoreEmp(@Param("emps") List<Emp> emps);
void deleteMoreEmp(@Param("empIds") Integer[] empIds);
<insert id="insertMoreEmp">
insert into t_emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.gender},null)
</foreach>
</insert>
<delete id="deleteMoreEmp">
delete from t_emp where emp_id in
<foreach collection="empIds" item="empId" separator="," open="(" close=")">
#{empId}
</foreach>
</delete>
@Test
public void TestForeach(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
dynamic mapper = sqlSession.getMapper(dynamic.class);
Emp emp1 = new Emp(null,"小明1",20,"男");
Emp emp2 = new Emp(null,"小明1",20,"男");
Emp emp3 = new Emp(null,"小明1",20,"男");
List<Emp> list1 = Arrays.asList(emp1, emp2, emp3);
mapper.insertMoreEmp(list1);
}
@Test
public void TestForeach2(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
dynamic mapper = sqlSession.getMapper(dynamic.class);
Integer[] empIds = new Integer[]{6,7};
mapper.deleteMoreEmp(empIds);
}
sql标签
<sql id="empColumns">
emp_id,emp_name,age,gender,dept_id
</sql>
select <include refid="empColumns"></include> from t_emp