文章目录
一、日志
设置名 | 描述 | 有效值 | 默认值 |
---|---|---|---|
logImpl | 指定 MyBatis 所用日志的具体实现,未指定时将自动查找。 | SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING | 未设置 |
1.1、STDOUT_LOGGING(标准日志)
使用:
- 在mybatis-config.xml中,配置日志实现
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
- 标准日志不用再做其他配置,即可以使用!
1.2、log4j
1.2.1、简介
- Log4j是Apache的一个开源项目,通过使用Log4j,我们可以控制日志信息输送的目的地是控制台、文件、GUI组件;
- 我们也可以控制每一条日志的输出格式;
- 通过定义每一条日志信息的级别,我们能够更加细致地控制日志的生成过程。
- 通过一个配置文件来灵活地进行配置,而不需要修改应用的代码。
1.2.2、使用
- 导入log4j的包
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
- log4j.properties
#将等级为DEBUG的日志信息输出到console和file这两个目的地,console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file
#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/kuang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n
#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
- 配置log4j为日志的实现
<settings>
<setting name="logImpl" value=""/>
</settings>
-
测试运行
-
日志级别
- 日志对象,参数为当前类的class
// 注意包不能导错:import org.apache.log4j.Logger; static Logger logger = Logger.getLogger(UserDaoTest.class);
- 测试方法
@Test public void log4jTest(){ logger.info("info: log4jTest"); logger.debug("debug: log4jTest"); logger.error("error: log4jTest"); }
- 测试结果
二、分页
2.1、使用limit实现分页
- UserMapper接口
public interface UserMapper {
//获取用户列表并分页
List<User> getUserListLimit(Map<String,Integer> map);
}
- UserMapper.xml
<!--//获取用户列表并分页-->
<select id="getUserListLimit" parameterType="map" resultType="user">
# startIndex:起始索引
# pageSize:页面容量
select * from mybatis.user limit #{startIndex},#{pageSize}
</select>
- 测试方法
@Test
public void getUserListLimitTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String, Integer> map = new HashMap<String, Integer>();
map.put("startIndex",0);
map.put("pageSize",3);
List<User> userListLimit = mapper.getUserListLimit(map);
for (User user : userListLimit) {
System.out.println(user);
}
sqlSession.close();
}
-
数据库
-
测试结果
2.2、RowBounds分页
- UserMapper接口
public interface UserMapper {
//获取用户列表,分页留给java类实现
List<User> getUserList();
}
- UserMapper.xml
<!--//获取用户列表-->
<select id="getUserList" resultType="user">
select * from mybatis.user
</select>
- 测试方法
@Test
public void getUserListTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
RowBounds rowBounds = new RowBounds(0, 3);
List<User> userList = sqlSession.selectList("com.study.dao.UserMapper.getUserList", null, rowBounds);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
- 测试结果
三、使用注解开发
- UserMapper接口
public interface UserMapper {
//根据ID获取用户
@Select("select * from user where id = #{id}")
User getUserById(@Param("id") Integer id);
//根据id删除用户
@Delete("delete from user where id = #{id}")
int deleteUserById(@Param("id") Integer id);
//添加用户
@Insert("insert into user values(#{id},#{name},#{password})")
int add(User user);
//根据id修改用户
@Update("update user set name = #{name},password = #{password} where id= #{id}")
int updateUserById(Map<String,Object> map);
}
- 在核心配置文件中绑定接口
<mappers>
<mapper class="com.study.dao.UserMapper"/>
</mappers>
- 测试方法
@Test
public void myTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
int delete = mapper.deleteUserById(5);
int add = mapper.add(new User(6, "root", "123456"));
Map<String, Object> map = new HashMap<String, Object>();
map.put("id",1);
map.put("name","zhangsan");
map.put("password","123456");
int update = mapper.updateUserById(map);
System.out.println(user);
if(delete==1) {
System.out.println("删除成功!");
}else {
System.out.println("删除失败!");
}
if(add==1) {
System.out.println("添加成功!");
}else {
System.out.println("添加失败!");
}
if(update==1) {
System.out.println("修改成功!");
}else {
System.out.println("修改失败!");
}
//注意:增删改操作必须要提交事务
sqlSession.commit();
sqlSession.close();
}
- 测试结果
四、多对一和一对多问题
以学生和老师为例,多个学生对应一个老师,一个老师对应多个学生。
-
数据库表关系
-
实体类
-
Student
-
Teacher
-
4.1、多对一
- 暂不考虑 Teacher 类的 students 属性
4.1.1、按照结果嵌套处理(联表查询)
- StudentMapper 接口
public interface StudentMapper {
List<Student> getStudentList();
}
- StudentMapper.xml
<select id="getStudentList" resultMap="studentMap">
select s.id sid,s.name sname,t.id tid,t.name tname from mybatis.student s,mybatis.teacher t where t.id = s.tid
</select>
<resultMap id="studentMap" type="student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
- 测试方法
@Test
public void getStudentListTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudentList();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
- 测试结果
4.1.2、按照查询嵌套处理(子查询)
- StudentMapper 接口
public interface StudentMapper {
List<Student> getStudentList();
}
- StudentMapper.xml
<select id="getStudentList" resultMap="studentMap">
select * from mybatis.student
</select>
<resultMap id="studentMap" type="student">
<association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="teacher">
select * from mybatis.teacher where id = #{tid}
</select>
- 测试方法与4.1.1相同,在此省略
- 测试结果
4.2、一对多
- 过程和多对一相似,此处只展示 StudentMapper.xml 核心内容
4.2.1、按照结果嵌套处理
<select id="getTeacherById" resultMap="teacherMap">
select s.id sid,s.name sname,t.id tid,t.name tname from mybatis.teacher t,mybatis.student s where t.id=s.tid and t.id=#{tid}
</select>
<resultMap id="teacherMap" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" javaType="ArrayList" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
4.2.2、按照查询嵌套处理
<select id="getTeacherById2" resultMap="teacherMap">
select * from mybatis.teacher where id=#{tid}
</select>
<resultMap id="teacherMap" type="Teacher">
<result property="id" column="id"/>
<collection property="students" javaType="ArrayList" ofType="Student" column="id" select="getStudent"/>
</resultMap>
<select id="getStudent" resultType="student">
select * from mybatis.student where tid=#{tid}
</select>
五、动态SQL
5.1、if
<!--
根据传递过来的参数,通过模糊查询获取用户列表
如果参数为null或是空串,则查询获取全部用户的列表
-->
<select id="getUserListByName" resultType="user">
select * from mybatis.user
<where>
<if test="name != null and name != ''">
name like concat('%',#{name},'%')
</if>
</where>
</select>
-
测试
-
where标签
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且,若语句的开头为“AND”或“OR”,where 元素也会将它们去除。
5.2、choose, when, otherwise
<!--
优先根据id获取用户,若id为空,则根据传递过来的name通过模糊查询获取用户列表
若id 和 name 都为空,则返回空列表
-->
<select id="getUserListByIdOrName" resultType="user">
select * from mybatis.user
<where>
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="name != null and name != ''">
name like concat('%',#{name},'%')
</when>
<otherwise>
false
</otherwise>
</choose>
</where>
</select>
- 测试:传递的参数为
id = 2 , name = "张"
5.3、foreach
<!--
通过集合中的id获取对应用户列表
-->
<select id="getUserListByIds" resultType="user">
select * from mybatis.user
<foreach item="id" collection="list" open="where id in(" close=")" separator=",">
#{id}
</foreach>
</select>
- 测试:
ids = [1, 2, 3]
5.4、SQL片段
- 等价于 5.1 中的代码
<sql id="ifName">
<if test="name != null and name != ''">
name like concat('%',#{name},'%')
</if>
</sql>
<select id="getUserListByName" resultType="user">
select * from mybatis.user
<where>
<include refid="ifName"/>
</where>
</select>
5.5、set
<!--
根据id修改用户信息
-->
<update id="updateUserById" parameterType="map">
update mybatis.user
<set>
<if test="name != null and name != ''">`name` = #{name},</if>
<if test="password != null and password != ''">`password` = #{password}</if>
</set>
where id = #{id}
</update>
-
set 元素会动态前置 SET 关键字,同时也会删掉无关的逗号
-
测试