SSM第8天 MyBatis动态SQL 和Log4j

一、mybatis.xml文件

1.1 typeAliases别名

mybatis.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>
	<typeAliases>
		<typeAlias type="com.zhiyou100.model.Course" alias="Course"  />
		<typeAlias type="com.zhiyou100.model.Subject" alias="Subject"/>
	</typeAliases> 
</configuration>

mapper.xml
<mapper namespace="com.zhiyou100.dao.CourseDAO">
	<resultMap type="Course" id="course">
		<id property="id" column="id"/>
		<result property="courseTitle" column="course_title"/>
		<result property="courseDesc" column="course_desc"/>
		<result property="subjectID" column="subject_id"/>
	</resultMap>
	<select id="queryAll" resultMap="course">
	
		select * from course
	
	</select>
</mapper>	

1.2 setting标签

https://blog.csdn.net/u014231523/article/details/53056032

1.3 cacheEnabled 二级缓存

<setting name="cacheEnabled" value="true"/>

1.4 mapUnderscoreToCamelCase

<setting name="mapUnderscoreToCamelCase" value="true"/>

二级缓存和一级缓存

1.1 一级缓存

		InputStream inputStream = Resources.getResourceAsStream("1.xml");
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession sqlSession = factory.openSession();
		StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
		studentMapper.queryAll();
		studentMapper.queryAll();
	
<!--当我们在同一个sqlSession中，执行查询操作，mybatis会进行数据的缓存-->


SqlSession sqlSession2 = factory.openSession();
		StudentMapper studentMapper2 = sqlSession2.getMapper(StudentMapper.class);
		studentMapper2.queryAll();


<!-- 但是当我们在不同的sqlSession中，不会进行缓存 -->
<!-- 这种被称为一级缓存 -->

1.2 SSM中一级缓存无效

在框架中，每次执行数据库操作，spring都会执行创建一个新的sqlSession，所以一级缓存无效

1.3 二级缓存

二级缓存是mapper级别，
开启二级缓存
需要mybatis.xml 中添加
<setting name="cacheEnabled" value="true"/>
以及xml中
<cache />
Model类实现Serializable接口

一、Log4j

1.1 pom.xml

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
	<groupId>log4j</groupId>
	<artifactId>log4j</artifactId>
	<version>1.2.17</version>
</dependency>

1.2 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

log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = E://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

log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =E://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


log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.Java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

1.3 mybatis.xml

<settings>
	<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

1.4 Java

public static Logger logger = Logger.getLogger(MainClass.class);
logger.trace("----------------trace");
logger.info("------------------info");
logger.warn("-----------------warn");
logger.error("------------------error");
logger.fatal("-----------------fatal");

二、#{} 与${} 的区别

#{}:防止sql注入
${}:普通的拼接

三、动态sql（一）

3.1 where

<if test="courseTitle != null ">
	course_title = #{courseTitle},
</if>

3.2 set

<update id="update" parameterType="Course">
	update course
	<set>
		
		<if test="courseTitle != null ">
			course_title = #{courseTitle},
		</if>

		<if test="courseDesc != null">
			course_desc = #{courseDesc},
		</if>
		<if test="subjectID != null">
			subject_id = #{subjectID},
		</if>
	</set>
	where
	id = #{id}
</update>
tips：
set标签会自动生成set关键字所以sql语句不需要写set
set会自动将多余的逗号，给删除掉

3.3 where

SELECT * FROM course
<where> 
<if test="id != null">
id <= #{id}
AND

</if>
<if test="course_title != null">
course_title LIKE CONCAT('%','Spring','%')
</if>
</where>
tips：
where标签会自动生成where关键字所以sql语句不需要写where
where 会将多余的and 和or 给删除掉

四、动态sql（二）批量删除和添加

4.1 DAO

package com.zhiyou100.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import com.zhiyou100.model.Course;
@Mapper
public interface CourseDAO extends BaseDAO<Course>{
    //批量删除List类型
	public void deleteMoreById1(List<Integer> ids);
	//批量删除数组类型
	public void deleteMoreById2(int [] ids);
	//批量添加List类型
	public void addMore(List<Course> courses);
}

4.2 批量删除(List类型)

<delete id="deleteMoreById1" parameterType="int">
	DELETE FROM course
	WHERE
	id in (
	<!-- 1,3,5,7 -->
	<foreach separator="," collection="list" item="id">
		#{id}
	</foreach>
	)
</delete>

parameterType参数类型为：List
collection集合数据为:list
item为元素变量名
separator为分割内容

4.2 批量删除(数组类型)

<delete id="deleteMoreById2" parameterType="int">
	DELETE FROM course
	WHERE
	id in (
	<foreach separator="," item="id" collection="array">
		#{id}
	</foreach>

	)

</delete>
parameterType参数类型为：int[]
collection集合数据为:array

4.3 批量添加（List类型）

<insert id="addMore" parameterType="List">
	INSERT INTO course VALUES
	<foreach collection="list" separator="," item="course">
	(null,#{course.courseTitle},#{course.courseDesc},#{course.subjectID})
	</foreach>
</insert>

4.4 自己研究出批量添加（数组类型）

五、pagehelper

https://pagehelper.github.io/docs/howtouse/

六、作业

1.使用SSM进行修改头像和修改个人资料