mybatis
1、导入依赖
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
2、核心配置文件:
mybatis-config.xml
入门核心文件配置：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 这个配置文件是mybatis的核心配置文件：使用mybatis的功能-->
<configuration>
<!--1.导入外部配置文件-->
<properties resource="db.properties"/>
<!-- 2.开启驼峰命名 -->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
<!--3.起别名-->
<typeAliases>
<!-- <typeAlias type="com.javasm.entity.Sysuser" alias="user"/>-->
<!-- 4.批量起别名，默认名就是类名，不区分大小写-->
<package name="com.javasm.entity"/>
</typeAliases>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<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>
</environment>
</environments>
<mappers>
<!-- <!– 这个是映射文件：管理sql的xml配置文件 –>-->
<!-- <mapper resource="com/javasm/mapper/SysuserMapper.xml"/>-->
<!-- <mapper class="com.javasm.mapper.SysroleMapper"/>-->
<!--批量注册映射文件：要求是mapper接口和xml同包同目录-->
<package name="com.javasm.mapper"/>
</mappers>
</configuration>
db.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_crm?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
jdbc.username=root
jdbc.password=123456
3.日志文件
-
引入日志依赖
<!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> -
创建log4j.properties
### 设置### log4j.rootLogger = debug,stdout,D,E ### 输出信息到控制抬 ### log4j.appender.stdout = org.apache.log4j.ConsoleAppender log4j.appender.stdout.Target = System.out log4j.appender.stdout.layout = org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n ### 输出DEBUG 级别以上的日志到=E://logs/error.log ### log4j.appender.D = org.apache.log4j.DailyRollingFileAppender log4j.appender.D.File = D://logs/log.log log4j.appender.D.Append = true log4j.appender.D.Threshold = DEBUG log4j.appender.D.layout = org.apache.log4j.PatternLayout log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n ### 输出ERROR 级别以上的日志到=E://logs/error.log ### log4j.appender.E = org.apache.log4j.DailyRollingFileAppender log4j.appender.E.File =D://logs/error.log log4j.appender.E.Append = true log4j.appender.E.Threshold = ERROR log4j.appender.E.layout = org.apache.log4j.PatternLayout log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n -
使用mybatis进行crud操作
-
测试文件
public class MainTest { private SqlSession sqlSession = null; private InputStream inputStream = null; @Before public void init() throws IOException { // 核心配置文件的位置 String resource = "mybatis-config.xml"; // 1.读取mybatis的核心配置文件 inputStream = Resources.getResourceAsStream(resource); // 2.创建SqlSessionFactory 对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); // 3.创建sqlSession sqlSession = sqlSessionFactory.openSession(true); } @After public void close() throws IOException { // 提交事务 sqlSession.commit(); // 6.释放资源 if (sqlSession != null) { sqlSession.close(); } if (inputStream != null) { inputStream.close(); } } @Test public void test_FindById() { Sysuser sysuser = sqlSession.selectOne("com.javasm.mapper.SysuserMapper.findById", 2); // 5.打印结果 System.out.println(sysuser); } @Test public void update() { Sysuser sysuser = new Sysuser(); sysuser.setUid(55); sysuser.setUname("刘总稳中之稳"); int update = sqlSession.update("com.javasm.mapper.SysuserMapper.updateUser", sysuser); System.out.println(update); } // 插入刘总稳中向好 @Test public void test_insert() { SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); Boolean aBoolean = mapper.insertUser(new Sysuser().setUname("刀郎").setCreateTime(LocalDateTime.now())); System.out.println(aBoolean); } @Test public void testfindLikeUserName() { SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); List<Sysuser> likeUserName = mapper.findLikeUserName("刘"); for (Sysuser sysuser : likeUserName) { System.out.println(sysuser); } } @Test public void testfindLikeUserNameAndPwd() { SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); List<Sysuser> sysuserList = mapper.findLikeUserNameAndPwd("张", "3"); for (Sysuser sysuser : sysuserList) { System.out.println(sysuser); } } @Test public void testfindByMap() { SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); HashMap<String, String> map = new HashMap<>(); map.put("name", "张"); map.put("p", "3"); List<Sysuser> sysuserList = mapper.findByMap(map); sysuserList.forEach(System.out::println); } @Test public void test_findByQueryCriteria() { SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); QueryCriteria queryCriteria = new QueryCriteria(); //见名知意 queryCriteria.setUsername("刘"); queryCriteria.setPassword("3"); List<Sysuser> sysuserList = mapper.findByQueryCriteria(queryCriteria); sysuserList.forEach(System.out::println); } @Test public void testfindBySorted() { SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); HashMap<String, String> map = new HashMap<>(); map.put("columd", "uid"); map.put("sorted", "desc"); List<Sysuser> list = mapper.findBySorted(map); list.forEach(System.out::println); } @Test public void test_delete() { SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); Boolean aBoolean = mapper.deleteUser(56); System.out.println(aBoolean); } @Test public void test_SelectList(){ // 4.执行sql List<Sysuser> sysuserList = sqlSession.selectList("com.javasm.mapper.SysuserMapper.findAll"); // 5.打印结果 sysuserList.forEach(System.out::println); } @Test public void testFindUserIncludeOneRoleByUid(){ SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); Sysuser user = mapper.findUserIncludeOneByUid(1); System.out.println("user==" + user); System.out.println("role==" + user.getSysrole()); } @Test public void testfindRoleIncludeUserList() { SysroleMapper mapper = sqlSession.getMapper(SysroleMapper.class); Sysrole roleIncludeUserList = mapper.findRoleIncludeUserList(1); System.out.println("role==" + roleIncludeUserList); List<Sysuser> sysuserList = roleIncludeUserList.getSysuserList(); System.out.println("对应管理员的用户"); sysuserList.forEach(System.out::println); } @Test public void selectRoleAndUser() { SysroleMapper mapper = sqlSession.getMapper(SysroleMapper.class); Sysrole sysrole = mapper.selectRoleAndUserById(1); System.out.println("sysrole==" + sysrole); System.out.println("用户列表："); sysrole.getSysuserList().forEach(System.out::println); } @Test public void selectRoleContainUserList() { SysroleMapper mapper = sqlSession.getMapper(SysroleMapper.class); Sysrole sysrole = mapper.selectRoleContainUserList(1); System.out.println("sysrole == " + sysrole); sysrole.getSysuserList().forEach(System.out::println); } } -
mapper的接口:
public interface SysuserMapper { Sysuser findById(Integer id); List<Sysuser> findAll(); Boolean insertUser(Sysuser sysuser); int updateUser(Sysuser sysuser); @Update("update sysuser set deleted = 1 where uid = #{uid}") Boolean deleteUser(Integer uid); /** * 按用户名模糊查询 * * @param username * @return */ List<Sysuser> findLikeUserName(String username); /** * 多参数查询 * @param username * @return */ List<Sysuser> findLikeUserNameAndPwd(@Param("name") String name,@Param("p") String pwd); /** * 按map中的key模糊查询 * @param map * @return */ List<Sysuser> findByMap(Map<String, String> map); List<Sysuser> findByQueryCriteria(QueryCriteria queryCriteria); List<Sysuser> findBySorted(Map<String, String> map); /** * 根据uId查用户并给查出一个角色信息 * @param id * @return */ Sysuser findUserIncludeOneByUid(Integer id); /** * 根据角色id查询改角色用户 * @param rid * @return */ List<Sysuser> findUserListByRid(Integer rid); } -
mapper的接口编写：
<?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.javasm.mapper.SysuserMapper"> <select id="findById" resultType="com.javasm.entity.Sysuser"> select * from sysuser where uid = #{uid} </select> <select id="findAll" resultType="com.javasm.entity.Sysuser"> select * from sysuser where deleted IS NULL </select> <select id="findLikeUserName" resultType="com.javasm.entity.Sysuser"> select * from sysuser where uname like concat('%', #{uname} ,'%') </select> <select id="findLikeUserNameAndPwd" resultType="com.javasm.entity.Sysuser"> select * from sysuser where uname like concat('%', #{name} ,'%') and upwd like concat('%', #{p} ,'%') </select> <select id="findByMap" resultType="com.javasm.entity.Sysuser"> select * from sysuser where uname like concat('%', #{name} ,'%') and upwd like concat('%', #{p} ,'%') </select> <select id="findByQueryCriteria" parameterType="com.javasm.cirteria.QueryCriteria" resultType="com.javasm.entity.Sysuser"> select * from sysuser where uname like concat('%', #{username} ,'%') and upwd like concat('%', #{password} ,'%'); </select> <!-- resultType 和 resultMap 不能同时使用 --> <!-- <resultMap id="userMap" type="com.javasm.entity.Sysuser">--> <!-- <!– id必须写–>--> <!-- <id column="uid" property="uid"/>--> <!-- <result column="una_me2" property="uname"/>--> <!-- <!– 习惯上我们都要配置–>--> <!-- <result column="upwd" property="upwd"/>--> <!-- </resultMap>--> <resultMap id="userMap" type="com.javasm.entity.Sysuser"> <id column="uid" jdbcType="INTEGER" property="uid" /> <result column="uname" jdbcType="VARCHAR" property="uname" /> <result column="upwd" jdbcType="VARCHAR" property="upwd" /> <result column="uemail" jdbcType="VARCHAR" property="uemail" /> <result column="phone" jdbcType="CHAR" property="phone" /> <result column="create_time" jdbcType="TIMESTAMP" property="createTime" /> <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /> <result column="deleted" jdbcType="CHAR" property="deleted" /> <result column="rid" jdbcType="INTEGER" property="rid" /> </resultMap> <select id="findBySorted" resultMap="userMap"> select * from sysuser order by ${columd} ${sorted}; </select> <resultMap id="UserIncludeOneMap" type="com.javasm.entity.Sysuser"> <id column="uid" jdbcType="INTEGER" property="uid" /> <result column="uname" jdbcType="VARCHAR" property="uname" /> <result column="upwd" jdbcType="VARCHAR" property="upwd" /> <result column="uemail" jdbcType="VARCHAR" property="uemail" /> <result column="phone" jdbcType="CHAR" property="phone" /> <result column="create_time" jdbcType="TIMESTAMP" property="createTime" /> <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /> <result column="deleted" jdbcType="CHAR" property="deleted" /> <result column="rid" jdbcType="INTEGER" property="rid" /> <!-- 一对一 --> <association property="sysrole" javaType="com.javasm.entity.Sysuser" column="rid" select="com.javasm.mapper.SysuserMapper.findById"> </association> </resultMap> <select id="findUserIncludeOneByUid" resultMap="UserIncludeOneMap"> select * from sysuser where uid = #{uid}; </select> <select id="findUserListByRid" resultType="com.javasm.entity.Sysuser"> select * from sysuser where rid = #{rid}; </select> <insert id="insertUser" parameterType="com.javasm.entity.Sysuser"> insert into sysuser values(null, #{uname}, 123456, null, null, #{createTime}, null, null, 1) </insert> <update id="updateUser" parameterType="Sysuser"> update sysuser set uname=#{uname}, upwd=#{upwd}, uemail=#{uemail}, phone=#{phone}, create_time=#{createTime}, update_time=#{updateTime}, deleted=#{deleted}, rid=#{rid} where uid = #{uid} </update> </mapper>
4.{}和${}区别以及使用
#{} 用来处理前端传递的参数,保证数据的安全性
${} 用来处理表结构,动态创建表或修改字段或者表名不固定等.不接受前端传递的参数.
1.#{}和${}区别
#{} 相当于PrepareStatement,会对传入的参数进行预处理,也就是使用引号将参数包围起来
#{} ：#{} 是以预编译的形式,直接将参数设置到sql语句中; 使用PreparedStatement的sql封装方法:设置了占位符为"?",可以有效地防止sql注入.
${} ：不能防止sql注入 ：一般排序使用，排序字段或者表名（很少使用）
${} 相当于statement,不会对传入的参数进行预处理.[可能会导致SQL入侵]
sql注入：在执行sql的时候。 如果使用的是${}，用户输入的内容直接拼在sql语句上，这样就有可能拼成一条语法正确的sql，但是不符合我们要执行的sql
5.mybatis的延迟加载（懒加载）
生效：mybatis中的延迟加载仅在有二次查询的场景下生效.
延迟加载目的：减少无效的数据库查询操作，提高响应的效率。<setting name="lazyLoadingEnabled" value="true"></setting> <!-- <setting name="aggressiveLazyLoading" value="false"></setting> <setting name="lazyLoadTriggerMethods" value=""></setting>-->9.mybatis缓存（一级sqlSession和二级sqlSessionFactory）
缓存：减少数据库的重复查询操作.基于内存把第一次查询的结果保存的缓存对象中，当后续查询时，先查询缓存，缓存中有则返回；缓存中没有则查询数据库，并把查询结果放缓存。（核心，写代码的逻辑）
ehcache占用web服务器的内存（一般不用）
memercache，redis 独立部署的缓存
mybatis的缓存分为两级：
session级别缓存(一级缓存)：默认开启 同一个sqlSession
factory级别缓存(二级缓存)：默认未开启. 同一个sqlSessionFactory一级缓存：
//在web系统中，每次用户请求，后端开启sqlsession，查询完毕，session关闭.
@Test public void test1_selectOne(){ SysuserMapper mapper = sqlSession.getMapper(SysuserMapper.class); Sysuser sysuser = mapper.selectUserByKey(1); sqlSession.clearCache();//清理缓存 Sysuser sysuser1 = mapper.selectUserByKey(1); System.out.println("mapper ===="+sysuser); }二级缓存：
不同的两个人去查，可以命中缓存
开启二级缓存：
第一步：核心配置文件:
<setting name="cacheEnabled" value="true"></setting>第二步：映射配置文件:
<！--缓存策略，只对当前namespace下的所有查询全部生效，可以再select中加usecache设置成false禁用二级缓存。可以在update，delete中使用flushCache="true"强制清空缓存-->
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>第三步：编写代码：在同一个sqlSessionFactory下
@Test public void test_1SysroleSelectList(){ SqlSession sqlSession = sqlSessionFactory.openSession(); SysroleMapper sysroleMapper = sqlSession.getMapper(SysroleMapper.class); Sysrole sysrole = sysroleMapper.findRoleContainUserListByRid(1); sqlSession.close(); SqlSession sqlSession1 = sqlSessionFactory.openSession(); SysroleMapper sysroleMapper1 = sqlSession1.getMapper(SysroleMapper.class); Sysrole sysrole1 = sysroleMapper1.findRoleContainUserListByRid(1); sqlSession1.close(); SqlSession sqlSession2 = sqlSessionFactory.openSession(); SysroleMapper sysroleMapper2 = sqlSession2.getMapper(SysroleMapper.class); Sysrole sysrole2 = sysroleMapper2.findRoleContainUserListByRid(1); sqlSession2.close(); }三.Mybatis动态sql（***）
1.动态sql（标签的使用）
sql
<sql id="allFields"> uname, upwd, uemail, phone, create_time, update_time, deleted, rid </sql> <select id="selectList" resultType="com.shangma.cn.entity.Sysuser"> select <include refid="allFields"></include> from sysuser </select>2.where
第一种方式：where 1=1
<select id="findByMap" resultType="com.javasm.entity.Sysuser"> select * from sysuser where 1=1 <if test="uname!=null and uname!=''"> and uname like CONCAT(CONCAT('%', #{uname},'%')) </if> <if test="upwd!=null and upwd!=''"> and upwd like CONCAT(CONCAT('%', #{upwd},'%')) </if> </select>第二种方式：where标签
<!-- where 标签作用 ：第一：生成 where 关键字 第二：去掉紧邻着where的 第一个and --> <select id="findByMap" resultType="com.javasm.entity.Sysuser"> select * from sysuser <where> <if test="uname!=null and uname!=''"> and uname like CONCAT(CONCAT('%', #{uname},'%')) </if> <if test="upwd!=null and upwd!=''"> and upwd like CONCAT(CONCAT('%', #{upwd},'%')) </if> </where> </select>3.set
<!-- set 作用 ：第一 生成set 关键字 第二 去掉紧邻着set的第一个 ,--> <update id="upateUserById"> update sysuser <set> <if test="uname!=null and uname!=''"> ,uname = #{uname} </if> <if test="upwd!=null and upwd!=''"> ,upwd = #{upwd} </if> </set> where uid =#{uid} </update>4.foreach
<!--<delete id="deleteUser">--> <!--delete from sysuser where uid in (43,44,45)--> <!--<foreach collection="list" separator="," open="(" close=")" item="id">--> <!--#{id}--> <!--</foreach>--> <!----> <!--</delete>--> <!-- 批量删除： 传list时 第一种：默认 认识 collection 和 list 第二种 ：@Param("idList") ArrayList<Integer> idList --> <!-- 批量删除：传数组时 第一种：默认 认识 array 第二种 ：@Param("ids") Integer[] ids --> <delete id="deleteUser"> delete from sysuser where uid in <foreach collection="ids" separator="," open="(" close=")" item="id"> #{id} </foreach> </delete><!--foreach标签；循环--> <!--<insert id="insertUser" useGeneratedKeys="true" keyProperty="uid">--> <!--insert into--> <!--sysuser (uname, phone) values ("张三","111") ,("李四","222"),("王五","333")--> <!--VALUES--> <!--<foreach collection="aaa" separator="," >--> <!--(#{uname},#{phone})--> <!--</foreach>--> <!--</insert>--> <!-- params : 传 list 时 第一种： 默认mybatis认识的 collection 还可以是 list 第二种：自定义 @Param("sysuserList") List<Sysuser> sysuserList --> <!-- params : 传 数组时 第一种： 默认mybatis认识的 array 第二种：自定义 @Param("sysusers") Sysuser[] sysusers --> <insert id="insertUser"> insert into sysuser (uname, phone) values <foreach collection="array" separator="," item="user"> (#{user.uname},#{user.phone}) </foreach> </insert>（面试）
1.trim
https://wenku.baidu.com/view/0d80d4fb7c192279168884868762caaedd33baef.htmltrim 替代where标签
<!-- where 标签的作用 ：第一：生成where 关键字 第二:去掉紧邻着 where标签的第一个and --> <select id="findByMap" parameterType="java.util.Map" resultType="com.javasm.entity.Sysuser"> select * from sysuser <trim prefix="where" prefixOverrides="and" > <if test="name !=null and name != ''"> and uname like '%' #{name} '%' </if> <if test="pwd != null and pwd != ''"> and upwd like '%' #{pwd} '%' </if> </trim> </select>trim 替代set标签
<!-- set 标签作用 ：第一：生成 set 关键字 第二 ，去掉紧邻着 set 的第一个 ,--> <update id="updateById"> update sysuser <trim prefix="set" prefixOverrides=","> <if test="uname != null and upwd != ''"> ,uname = #{uname} </if> <if test="upwd != null and upwd != ''"> ,upwd = #{upwd} </if> </trim> where uid = #{uid} </update>2.choose when
https://wenku.baidu.com/view/4345d9e15cbfc77da26925c52cc58bd631869380.html<select id="selectListByChooseWhen" resultType="com.javasm.entity.Sysuser"> select * from sysuser <where> <choose> <when test="name != null and name != ''"> and uname like #{name} </when> <when test="pwd != null and pwd != ''"> and upwd like #{pwd} </when> <otherwise> </otherwise> </choose> </where>四.sql自动补全（解决方案）
-
评论区