MyBatis学习笔记
一、第一个mybatis程序
1.1 导入依赖
<!-- mybatis核心依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- mysql驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--解决资源导出问题-->
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
1.2 创建数据库配置文件(db.properties)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis_lzf?useUnicode=true&characterEncoding=utf-8
username=root
password=123456
1.3 创建核心配置文件（mybatis-config)
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--2.引入外部配置文件db.properties-->
<properties resource="db.properties"/>
<!--1.核心配置信息-->
<environments default="environment1">
<environment id="environment1">
<!--事务控制类型-->
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
</configuration>
1.4 创建实体类（对应数据库中的表）
package com.lzf.entity;
public class User {
private int id;
private String username;
private String password;
public User() {
}
public User(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
1.5 编写dao接口（UserDao）
public interface UserDao {
User queryUserById(Integer id);
}
1.6 编写对应的daoMapper (userDaoMapper.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.lzf.dao.UserDao">
<select id="queryUserById" resultType="com.lzf.entity.User">
SELECT * from t_user WHERE id = #{arg0};
</select>
</mapper>
- 在mybatis的核心配置文件（mybatis-config）中注册mapper
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--2.引入外部配置文件db.properties-->
<properties resource="db.properties"/>
<!--1.核心配置信息-->
<environments default="environment1">
<environment id="environment1">
<!--事务控制类型-->
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--注册mapper-->
<mappers>
<mapper resource="com/lzf/dao/UserDaoMapper.xml"/>
</mappers>
1.7 编写测试类
public class TestMybatis {
public static void main(String[] args) throws IOException {
//1.加载配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建SqlSessionFactory
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3.创建SqlSession
SqlSession sqlSession = sessionFactory.openSession();
//4.通过sqlSession获得dao实现类对象
UserDao mapper = sqlSession.getMapper(UserDao.class);
//5.测试查询方法
User user = mapper.queryUserById(1);
System.out.println(user);
}
}
1.8 类型别名
在mybatis核心配置文件中加入以下代码
<!--实体类别名-->
<typeAliases>
<!--<typeAlias type="com.lzf.entity.User" alias="User"/>-->
<!--整个包取别名 别名= 类名-->
<package name="com.lzf.entity"/>
</typeAliases>
1.9 整体项目结构图
1.10 Mybatis工具类
package com.lzf.util;
import org.apache.ibatis.io.Resources;
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;
/**
* 1.加载配置文件
* 2.构建SqlSessionFactory
* 3.创建SqlSession
* 4.事务的管理
* 5.获取mapper
*/
public class MyBatisUtil {
private static SqlSessionFactory sqlSessionFactory;
private static final ThreadLocal<SqlSession> THREAD_LOCAL = new ThreadLocal<SqlSession>();
static {
//1.加载配置文件
try {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建SqlSessionFactory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//打开sqlSession
public static SqlSession openSession(){
SqlSession sqlSession = THREAD_LOCAL.get();
if(sqlSession==null){
sqlSession = sqlSessionFactory.openSession();
THREAD_LOCAL.set(sqlSession);
}
return sqlSession;
}
//事务提交
public static void commit(){
openSession().commit();
closeSession();
}
//事务回滚
public static void rollback(){
openSession().rollback();
closeSession();
}
//资源释放
public static void closeSession(){
THREAD_LOCAL.get().close();
THREAD_LOCAL.remove();
}
//获取mapper
public static <T> T getMapper(Class<T> mapper){
return openSession().getMapper(mapper);
}
}
二、CRUD
2.1 查询(参数绑定)
序号参数绑定
public interface UserDao {
User queryUserById(Integer id);
}
<select id="queryUserById" resultType="User">
SELECT * from t_user WHERE id = #{arg0};<!--arg0 arg1 arg2-->
</select>
<select id="queryUserById" resultType="User">
SELECT * from t_user WHERE id = #{param1};<!--param1 param2 param13-->
</select>
注解参数绑定【推荐】
public interface UserDao {
User selectUserByIdAndPwd(@Param("id") Integer id, @Param("pwd") String pwd);
}
<select id="selectUserByIdAndPwd" resultType="User">
select * from t_user WHERE id = #{id} AND password = #{pwd};
</select>
Map参数绑定
public interface UserDao {
User selectUserByIdAndPwd_map(Map values);
}
Map values = new HashMap();
values.put("id",1);
values.put("pwd","456");
User user = mapper.selectUserByIdAndPwd_map(values);
<select id="selectUserByIdAndPwd_map" resultType="User">
select * from t_user WHERE id=#{id} and password = #{pwd};<!--通过key获得value -->
</select>
对象参数绑定
public interface UserDao {
User selectUserByUserInfo(User user);
}
User u = new User(1,"lzf","456");
User user = mapper.selectUserByUserInfo(u);
System.out.println(user);
<select id="selectUserByUserInfo" resultType="User">
select * from t_user WHERE id = #{id} and password = #{password};<!--通过对象属性取值-->
</select>
concat
字符串拼接函数，可用于模糊查询
where name like concat(‘%’，#{keyword},’%’);
拼接了%
2.2 删除
public interface UserDao {
Integer deleteUserById(@Param("id") int id);
}
<delete id="deleteUserById" parameterType="int">
DELETE FROM t_user WHERE id = #{id} <!--参数只有一个时，id可以任意书写-->
</delete>
2.3 添加
public interface UserDao {
Integer addUser(User user);
}
<insert id="addUser" parameterType="User">
insert into t_user values (NULL,#{username},#{password});
</insert>
2.4 修改
public interface UserDao {
Integer updataUser(User user);
}
<update id="updataUser" parameterType="User">
update t_user set username = #{username},password = #{password} where id = #{id};
</update>
2.5 注意点
1. 增删改要控制事务
sqlSession.commit();
sqlSession.rollback();
2. 增删改有返回值
2.6 插入中的主键回填
主键为int 且自动增长
<insert id="addUser" parameterType="User">
<!--主键回填，将新数据的ID存入到JAVA对象的中-->
<selectKey order="AFTER" resultType="int" keyProperty="id">
select last_insert_id()
</selectKey>
insert into t_user values (#{id},#{username},#{password});
</insert>
主键为string 不主动增长
<insert id="insertStudent" parameterType="Student">
<selectKey order="BEFORE" keyProperty="id" resultType="string">
<!--生成uuid作为唯一主键-->
SELECT replace(uuid(),'-','');
</selectKey>
insert into t_student values (#{id},#{name},#{password});
</insert>
三、ORM映射
Mybatis自动ORM失效（数据库列名和 JAVA中对象属性名不能一一对应）时，解决方案如下：
4.1 列的别名
<select id="selectUserByIdAndPwd" resultType="User">
select id,username,password,regist_time AS registTime from t_user WHERE id = #{id} AND password = #{pwd};
</select>
4.2 结果映射（resultMap）
<resultMap id="user_resultMap" type="User">
<!--定义更复杂的 映射规则-->
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="regist_time" property="registTime"></result>
</resultMap>
<select id="selectUserByIdAndPwd" resultMap="user_resultMap">
select * from t_user WHERE id = #{id} AND password = #{pwd};
</select>
4.3 resultMap处理多表连接ORM映射
持有对象关系属性
<resultMap id="user_resultMap" type="User">
<!--定义更复杂的 映射规则-->
<id column="id" property="id"></id>
<result column="" property=""></result>
<result column="" property=""></result>
<association property="" javaType="">
<id></id>
<result></result>
<result></result>
</association>
</resultMap>
持有集合关系属性
<resultMap id="user_resultMap" type="User">
<!--定义更复杂的 映射规则-->
<id column="id" property="id"></id>
<result column="" property=""></result>
<result column="" property=""></result>
<collection property="" ofType="">
<id></id>
<result></result>
<result></result>
</collection>
</resultMap>
四、动态SQL
4.1 sql标签
抽取重复的sql语句，实现复用
<sql id="user_field">
<!--定义SQL片段-->
select id,username,password from t_user;
</sql>
<select id="queryUsers" resultType="User">
<!--通过ID引用SQL片段-->
<include refid="user_field"/>
</select>
<select id="selectUserById" resultType="User">
<!--通过ID引用SQL片段-->
<include refid="user_field"/>
WHERE id = #{id};
</select>
4.2 if标签
//根据id查询时给id正确赋值，username为null
//根据username查时username正确赋值,id为null
User queryUser(User user);
<select id="queryUser" resultType="User">
SELECT * from t_user
WHERE
<if test="id!=null"> <!--test中直接写id就是在取值-->
id = #{id}
</if>
<if test="username!=null"><!--同上-->
username = #{username}
</if>
</select>
4.3 where标签（忽略前缀）
自动忽略 前缀 如：or | and
<!--4.2中的代码是有问题的
当两个if都满足时，sql语句就会错误:
SELECT * from t_user id = ? username = ?
解决方案：在第二个if中加入前缀 or/and，
(此时加上的话还不够，因为此时如果第一个if不满足时，只满足第二个if，那么sql语句还是错误的)
所以还需要加上 where标签(把whrer关键字去掉)
-->
<select id="queryUser" resultType="User">
SELECT * from t_user
<where>
<if test="id!=null">
id = #{id}
</if>
<if test="username!=null">
or username = #{username}
</if>
</where>
</select>
4.4 set标签（忽略后缀）
自动忽略后缀如： ,
Integer updateUser(User user);
//对修改做约定：需要需改的属性就赋值，不需要修改的属性就为null
<!--set 关键字去掉，改成set标签-->
<update id="updateUser" parameterType="User">
update t_user
<set>
<if test="username!=null">
username=#{username},
</if>
<if test="password!=null">
password=#{password},
</if>
<if test="gender!=null">
gender=#{gender},
</if>
<if test="registTime!=null">
regist_time=#{registTime}
</if>
</set>
where id=#{id}
</update>
4.5 trim标签（代替where和set）
<trim prefix="where" prefixOverrides="or|and"> <!--增加where前缀,自动忽略前缀-->
<if test="username!=null">
username=#{username}
</if>
<if test="gender!=null">
and gender=#{gender}
</if>
</trim>
<trim prefix="set" suffixOverrides=","> <!--增加set前缀,自动忽略后缀-->
<if test="username!=null">
username=#{username},
</if>
<if test="password!=null">
password=#{password},
</if>
<if test="gender!=null">
gender=#{gender},
</if>
<if test="registTime!=null">
regist_time=#{registTime}
</if>
</trim>
4.6 foreach标签
批量操作,遍历集合
- 批量删除
Integer deleteManyUser(List<Integer> ids);
<delete id="deleteManyUser" parameterType="java.util.List">
<!--delete from t_user where id in (x,x,x,x,x,x)-->
delete from t_user where id in
<foreach collection="list" open="(" close=")" item="id9" separator=",">
#{id9}
</foreach>
</delete>
| 参数 | 描述 | 取值 |
|---|---|---|
| collection | 容器类型 | list、array、map |
| open | 起始符 | ( |
| close | 结束符 | ) |
| separator | 分隔符 | , |
| index | 下标号 | 从0开始，依次递增 |
| item | 当前项 | 任意名称（循环中通过#{任意名称}表达式进行访问） |
- 批量增加
Integer insertManyUser(List<User> users);
<insert id="insertManyUser" parameterType="java.util.List">
<!--insert into t_user values (null,x,x,x,x,x,x),(null,xxxx,xxx,xx)-->
insert into t_user values
<foreach collection="list" close="" open="" item="user9" separator=",">
(null,#{user9.username},#{user9.password},#{user9.gender},#{user9.registTime})
</foreach>
</insert>
五、缓存（Cache）
5.1 一级缓存
SqlSession级别的缓存，同一个SqlSession的发起多次同构查询，会将数据保存在一级缓存中。
- 注意：无需任何配置，默认开启一级缓存
5.2 二级缓存
SqlSessionFactory级别的缓存，同一个SqlSessionFactory构建的SqlSession发起的多次同构查询，会将数据保存在二级缓存中
- 注意：在SqlSession.commit()或者SqlSession.close()之后生效
<!--添加到matatis配置文件中，注意书写位置，在properties标签后-->
<settings>
<!--开启全局缓存，默认开启-->
<setting name="cacheEnabled" value="true"/>
</settings>
<mapper namespace="com.lzf.dao.UserDao">
<cache/> <!--指定要缓存的mapper-->
</mapper>
- 对缓存的数据进行增 删 改操作，相关的缓存会被移除
六、连接池（druid）
6.1 导入依赖
<!--Druid依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.16</version>
</dependency>
6.2 创建DruidDataSourceFactory
- MyDruidDataSourceFactory继承PooledDataSourceFactory，并替换数据源
package com.lzf.datasource;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory;
public class MyDruidDataSourceFactory extends PooledDataSourceFactory{
public MyDruidDataSourceFactory() {
this.dataSource = new DruidDataSource();//替换数据源
}
}
6.3 修改mybatis配置文件
<dataSource type="com.lzf.datasource.MyDruidDataSourceFactory"><!--数据源工厂-->
<property name="driverClass" value="${driver}"/>
<property name="jdbcUrl" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
- 注意property中name的值，与com.alibaba.druid.pool.DruidAbstractDataSource中一致
七、PageHelper
PageHelper是适用于Mabatis框架的一个分页框架
7.1 引入依赖
<!--PageHelper依赖-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
7.2 配置 mybatis-config.xml
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases> </typeAliases>
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
<environments > </environments>
</configuration>
- PageInfofenter保存分页查询结果
@Testls
public void testPageInfo(){
UserDao userDao = MyBatisUti.getMapper记录(UserDao.class);
PageHelper.start(6,2);//第六页 每页2条记录。这条语句之后的第一个查询会执行分页
List<User> users = UserDao.selectAllUsers();
PageInfo<User> pageInfo = new PageInfo<User>(users);//将分页查询的结果集保存在PageInfo对象中
System.out.println(pageInfo);
}
- PageInfofenter类相关属性
private int pageNum;//当前页
private int pageSize;//每页的数量
private int size;//当前页的数量
private int startRow;//当前页面第一个元素在数据库中行号
private int endRow;//当前页面最后一个元素在数据库中的行号
private int pages;//总页数
private int prePage;//上一页
private int nextPage;//下一页
private boolean isFirstPage;//是否为第一页
private boolean isLastPage;//是否为最后一页
private boolean hasPreviousPage;//是否有前一页
private boolean hasNextPage;//是否有下一页
private int navigatePages;//导航页码数
private int[] navigatepageNums;//所有导航页码数
private int navigateFirstPage;//导航条上的第一页
private int navigateLastPage;//导航条上的最后一页
protected List<T> list;//数据在父类的list中
八、 注解开发【了解】
8.1 增加
@Options(useGeneratedKeys = true , keyProperty = "id") // 自增key，主键为id
@Insert("insert into t_user values(#{id},#{username},#{password},#{gender},#{registTime})")
Integer insertUser(User user);
8.2 删除
@Delete("delete from t_user where id=#{id}")
Integer deleteUser(@Param("id") Integer id);
8.3 修改
@Update("update t_user set username=#{username},password=#{password},gender=#{gender},regist_time=#{registTime} where id=#{id}")
Integer updateUser(User user);
8.4 查询
@Select("SELECT id,username,password,gender,regist_time FROM t_user")
List<User> queryUsers();
版权声明：本文为li-zf原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。