MyBatis 笔记
【1】配置文件
<?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>
< properties resource = " mysql.properties" > </ properties>
< properties>
< property name = " key" value = " value" />
</ properties>
< settings>
< setting name = " logImpl" value = " LOG4J" />
</ settings>
< environments default = " mysql" >
< environment id = " mysql" >
< transactionManager type = " JDBC" > </ transactionManager>
< dataSource type = " POOLED" >
< property name = " driver" value = " ${mysql.driver}" />
< property name = " url" value = " ${mysql.url}" />
< property name = " username" value = " ${mysql.username}" />
< property name = " password" value = " ${mysql.password}" />
</ dataSource>
</ environment>
</ environments>
< mappers>
< mapper resource = " mappers/UserMapper.xml" />
</ mappers>
</ configuration>
【2】JDK Logging
package com. bjsxt ;
import java. util. logging. * ;
public class TestJDKLogging {
public static void main ( String [ ] args) throws Exception {
Logger logger = Logger . getLogger ( "JDKLogger" ) ;
logger. setLevel ( Level . FINEST ) ;
ConsoleHandler consoleHandler = new ConsoleHandler ( ) ;
consoleHandler. setFormatter ( new SimpleFormatter ( ) ) ;
consoleHandler. setLevel ( Level . FINEST ) ;
logger. addHandler ( consoleHandler) ;
FileHandler fileHandler = new FileHandler ( "jdk_log.log" ) ;
fileHandler. setFormatter ( new SimpleFormatter ( ) ) ;
fileHandler. setLevel ( Level . FINEST ) ;
logger. addHandler ( fileHandler) ;
logger. finest ( "最详细的日志" ) ;
logger. fine ( "详细日志" ) ;
logger. warning ( "警告日志" ) ;
logger. info ( "标准消息日志" ) ;
logger. severe ( "严重错误日志" ) ;
}
}
【3】Log4j
< dependency>
< groupId> log4j</ groupId>
< artifactId> log4j</ artifactId>
< version> 1.2.17</ version>
</ dependency>
# log4j中定义的级别:fatal(致命错误) > error(错误) >warn(警告) >info(普通信息) >debug(调试信息)>trace(跟踪信息)
# 定义Log4j中的Logger工具中有什么handler。 第一个DEBUG代表的是日志的级别。 后续每个单词是一个handler名称
log4j.rootLogger = DEBUG , console , D
# log4j.logger是固定的,a.b.c是命名空间的名字可以只写一部分。
# 是mybatis中要使用日志的时候,必须提供的配置。
# 代表sql配置文件的namespace相应的sql,执行的时候,是否输出日志,及日志的级别。
# 如:namespace="a.b" , log4j.logger.a.b=XXX级别。 那么 a.b.*所有的sql运行的时候,都输出日志。建议
# 如:namespace="a.b" , log4j.logger.a=XXX级别, 那么 a.*.*所有的sql运行,都输出日志。不建议
# 如果有多个namespace都需要输出日志。定义若干行即可
log4j.logger.a=TRACE
log4j.logger.a.b=TRACE
log4j.logger.a.c=TRACE
### console ###
# 开始定义console名称的handler
# console handler的类型
log4j.appender.console = org.apache.log4j.ConsoleAppender
# console handler输出的日志到哪里。 System.out 控制台
log4j.appender.console.Target = System.out
# console输出日志的时候,格式是什么样的。是Pattern格式。 正则表达式格式
log4j.appender.console.layout = org.apache.log4j.PatternLayout
# 具体的格式正则规则。 %p 线程|进程 %-d 时间 {yyyy-MM-dd HH\:mm\:ss} 具体的格式 %C 类名输出日志的类型名 %m 输出的日志文本 %n回车
log4j.appender.console.layout.ConversionPattern = [%p] [%-d{yyyy-MM-dd HH\:mm\:ss}] %C.%M(%L) | %m%n
### log file ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
# 文件日志handler,记录日志到哪一个文件
log4j.appender.D.File = D:/log4j.log
# 是否是追加写入到日志文件
log4j.appender.D.Append = true
# 只能升级别,不能降
# 名字是D的handler,自定义日志级别是什么。
log4j.appender.D.Threshold = INFO
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = [%p] [%-d{yyyy-MM-dd HH\:mm\:ss}] %C.%M(%L) | %m%n
package com. bjsxt ;
import org. apache. log4j. Logger ;
public class TestLog4j {
public static void main ( String [ ] args) {
Logger logger = Logger . getLogger ( TestLog4j . class ) ;
logger. info ( "info日志" ) ;
logger. warn ( "waring日志" ) ;
logger. error ( "error日志" ) ;
logger. debug ( "debug日志" ) ;
}
}
【4】Log4j2
< dependency>
< groupId> org.apache.logging.log4j</ groupId>
< artifactId> log4j-core</ artifactId>
< version> 2.17.2</ version>
</ dependency>
<?xml version="1.0" encoding="utf-8" ?>
< Configuration >
< Appenders>
< Console name = " Console" target = " SYSTEM_OUT" >
< PatternLayout pattern = " %d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
</ Console>
< File name = " log" fileName = " log/test.log" append = " true" >
< PatternLayout pattern = " %d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
</ File>
</ Appenders>
< Loggers>
< Root level = " debug" >
< AppenderRef ref = " Console" />
< AppenderRef ref = " log" level = " info" />
</ Root>
</ Loggers>
</ Configuration>
package com. bjsxt ;
import org. apache. logging. log4j. LogManager ;
import org. apache. logging. log4j. Logger ;
public class TestLog4j2 {
public static void main ( String [ ] args) {
Logger logger = LogManager . getLogger ( TestLog4j2 . class ) ;
logger. debug ( "debug信息" ) ;
logger. info ( "info" ) ;
logger. warn ( "waring" ) ;
logger. error ( "error" ) ;
logger. fatal ( "fatal" ) ;
}
}
【5】SLF4j
SLF4j是日志的接口声明,不参与日志的具体实现。需要配合其他日志具体实现工具包才能进行使用。每次添加其他日志工具包时,不要忘记SLF4j整合这个日志的依赖。
SLF4J支持多种日志实现,但是在项目中整合依赖最好只有一个,否则会出现警告
< dependency>
< groupId> org.slf4j</ groupId>
< artifactId> slf4j-api</ artifactId>
< version> 1.7.36</ version>
</ dependency>
< dependency>
< groupId> org.slf4j</ groupId>
< artifactId> slf4j-log4j12</ artifactId>
< version> 1.7.36</ version>
</ dependency>
< dependency>
< groupId> log4j</ groupId>
< artifactId> log4j</ artifactId>
< version> 1.2.17</ version>
</ dependency>
< dependency>
< groupId> org.slf4j</ groupId>
< artifactId> slf4j-api</ artifactId>
< version> 1.7.36</ version>
</ dependency>
< dependency>
< groupId> org.apache.logging.log4j</ groupId>
< artifactId> log4j-slf4j-impl</ artifactId>
< version> 2.17.2</ version>
</ dependency>
< dependency>
< groupId> org.apache.logging.log4j</ groupId>
< artifactId> log4j-core</ artifactId>
< version> 2.17.2</ version>
</ dependency>
# Log4j配置
log4j.rootLogger=ERROR, stdout
# log4j.logger是固定的,a.b.c是命名空间的名字可以只写一部分。
log4j.logger.a.b.c=TRACE
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
public class Test {
public static void main ( String [ ] args) {
Logger logger = LoggerFactory . getLogger ( Test . class ) ;
logger. trace ( "trace" ) ;
logger. debug ( "debug" ) ;
logger. info ( "info" ) ;
logger. warn ( "warn" ) ;
logger. error ( "error" ) ;
}
}
【6】整合日志
MyBatis框架内置日志工厂。日志工厂负责自动加载项目中配置的日志。MyBatis支持以下日志,当存在多个日志工具时,严格按照从上往下顺序使用,且只会使用一个
- SLF4J
- Apache Commons Logging
- Log4j 2
- Log4j (deprecated since 3.5.9)
- JDK logging
在 Log4j 中,日志输出遵循两个主要规则:日志生成规则和日志输出规则
[1] 日志生成规则(由 Logger 决定):
Logger 的日志级别决定了哪些日志会被生成。
如果 rootLogger 设置为 INFO,那么默认情况下,只有 INFO 及以上级别的日志(即 INFO、WARN、ERROR、FATAL)会被生成。DEBUG 和 TRACE 级别的日志则不会被生成。
[2] 日志输出规则(由 Appender 决定):
Appender 的日志级别(通过 Threshold 设置)决定了哪些生成的日志会被输出。
Appender 只能输出那些已经生成的日志。
[3] 具体情况分析
根路径(rootLogger)设置为 INFO:意味着 INFO 及以上级别的日志会被生成,而 DEBUG 和 TRACE 级别的日志不会被生成。
console 设置为 TRACE:尽管 console 可以输出 TRACE 及以上级别的日志,但因为 rootLogger 限制了日志的生成,TRACE 和 DEBUG 级别的日志并不会被生成,因此也不会被 console 输出。
[4] 结论
在这种情况下:
最终输出到 console 的日志级别是 INFO 及以上的日志。即使 console 的 Appender 设置为 TRACE 级别,它也无法输出低于 INFO 级别的日志,因为这些日志根本没有被 rootLogger 生成。
因此,console 只会输出 INFO 及以上级别的日志,即 INFO、WARN、ERROR、FATAL,而不会输出 TRACE 或 DEBUG 级别的日志。
【7】SQL参数
<?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 = " a.b.c" >
< insert id = " add" >
insert into people values(default,#{suiyixie})
</ insert>
</ 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 = " a.b.c" >
< insert id = " insertUser3" >
insert into tb_user(id, name) values(#{u1.id}, #{u2.name})
</ insert>
</ mapper>
【8】${} / #{} 区别
#{} 被解析为?,用在设置列的值或条件值时,也可以使用在分页等需要设置具体值的情况
${} 表示字符串拼接,用在动态设置表名和动态设置列名的情况下
<?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 = " a.b.c" >
< insert id = " insertUser4" >
insert into ${tableName} (${idColumn}, ${nameColumn}) values (#{id}, #{name})
</ insert>
</ mapper>
【9】MyBatis中DML操作
操作 mapper标签 SqlSession方法名 新增 <insert>
insert(String)、insert(String,Object) 修改 <update>
update(String)、update(String,Object) 删除 <delete>
delete(String)、delete(String,Object)
在MyBatis中,增删改底层使用的逻辑是同一个。那么在没有特定需求的时候,可以使用update实现增删改的所有功能。也就是标签用update, 方法用update。
虽然方便,但不推荐。语义不明确,后期维护的时候,成本高。
【10】MyBatis中DQL操作
SqlSession方法名 解释说明 selectOne() 查询一行数据时,返回值为Object。如果没有查询到返回Null,但是不能查询到多行会报错。 selectList() 当查询多行数据时,返回值为List。如果没有查询到返回长度为零的List对象。 selectMap() 查询多行数据时,把其中某列结果当做key,每行结果为Value selectCursor() 使用游标查询时使用,在大量数据时可以代替分页 select() 万能方法,需要自己定义结果处理器
package com. bjsxt. utils ;
import org. apache. ibatis. io. Resources ;
import org. apache. ibatis. session. SqlSessionFactory ;
import org. apache. ibatis. session. SqlSessionFactoryBuilder ;
public final class MyBatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( Resources . getResourceAsStream ( "mybatis.cfg.xml" ) ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
throw new ExceptionInInitializerError ( e) ;
}
}
private MyBatisUtils ( ) {
}
public static SqlSessionFactory getFactory ( ) {
return sqlSessionFactory;
}
}
package com. bjsxt ;
import com. bjsxt. pojo. User ;
import com. bjsxt. utils. MyBatisUtils ;
import org. apache. ibatis. cursor. Cursor ;
import org. apache. ibatis. executor. result. DefaultResultHandler ;
import org. apache. ibatis. session. SqlSession ;
import org. junit. Test ;
import java. util. HashMap ;
import java. util. List ;
import java. util. Map ;
public class TestSelect {
@Test
public void testSelect ( ) {
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
DefaultResultHandler resultHandler = new DefaultResultHandler ( ) ;
session. select ( "user.mapper.selectAll" , resultHandler) ;
List list = resultHandler. getResultList ( ) ;
list. forEach ( obj -> {
System . out. println ( obj) ;
} ) ;
session. close ( ) ;
}
@Test
public void testSelectCursor ( ) {
Map < String , Object > params = new HashMap < > ( ) ;
params. put ( "start" , 100 ) ;
params. put ( "end" , 1000 ) ;
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
Cursor < User > cursor = session. selectCursor ( "user.mapper.selectAll" ) ;
Cursor < User > cursor1 = session. selectCursor ( "user.mapper.selectRangeId" , params) ;
session. close ( ) ;
cursor. forEach ( user -> {
System . out. println ( user) ;
} ) ;
cursor1. forEach ( user -> {
System . out. println ( user) ;
} ) ;
}
@Test
public void testSelectMap ( ) {
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
Map < String , User > map1 = session. selectMap ( "user.mapper.selectAll" , "name" ) ;
Map < String , Object > params = new HashMap < > ( ) ;
params. put ( "start" , 100 ) ;
params. put ( "end" , 1000 ) ;
Map < String , User > map2 = session. selectMap ( "user.mapper.selectRangeId" , params, "name" ) ;
session. close ( ) ;
System . out. println ( map1. size ( ) ) ;
for ( Map. Entry < String , User > entry : map1. entrySet ( ) ) {
System . out. println ( "key = " + entry. getKey ( ) + " ; value = " + entry. getValue ( ) ) ;
}
System . out. println ( map2. size ( ) ) ;
for ( Map. Entry < String , User > entry : map2. entrySet ( ) ) {
System . out. println ( "key = " + entry. getKey ( ) + " ; value = " + entry. getValue ( ) ) ;
}
}
@Test
public void testSelectAll ( ) {
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
List < User > users = session. selectList ( "user.mapper.selectAll" ) ;
System . out. println ( users. size ( ) ) ;
users. forEach ( user -> {
System . out. println ( user) ;
} ) ;
}
@Test
public void testSelectRangeId ( ) {
Map < String , Object > params = new HashMap < > ( ) ;
params. put ( "start" , 10 ) ;
params. put ( "end" , 100 ) ;
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
List < User > list1 = session. selectList ( "user.mapper.selectRangeId" , params) ;
System . out. println ( list1. size ( ) ) ;
list1. forEach ( u -> {
System . out. println ( u) ;
} ) ;
params. put ( "start" , - 10 ) ;
params. put ( "end" , - 1 ) ;
List < User > list2 = session. selectList ( "user.mapper.selectRangeId" , params) ;
System . out. println ( list2. size ( ) ) ;
list2. forEach ( user -> {
System . out. println ( user) ;
} ) ;
}
@Test
public void testSelectById ( ) {
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
User user = session. selectOne ( "user.mapper.selectById" , 1000 ) ;
System . out. println ( user) ;
session. close ( ) ;
}
@Test
public void testSelectByName ( ) {
String name = "刘备" ;
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
User u1 = session. selectOne ( "user.mapper.selectByName" , name) ;
System . out. println ( u1) ;
name = "大小姐" ;
User u2 = session. selectOne ( "user.mapper.selectByName" , name) ;
System . out. println ( u2) ;
name = "张三" ;
User u3 = session. selectOne ( "user.mapper.selectByName" , name) ;
System . out. println ( u3) ;
}
}
<?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 = " user.mapper" >
< select id = " selectAll" resultType = " com.bjsxt.pojo.User" >
select id, name
from tb_user
</ select>
< select id = " selectRangeId" resultType = " com.bjsxt.pojo.User" >
select id, name
from tb_user
where id between #{start} and #{end}
</ select>
< select id = " selectById" resultType = " com.bjsxt.pojo.User" >
select id, name
from tb_user
where id = #{id}
</ select>
< select id = " selectByName" resultType = " com.bjsxt.pojo.User" >
select id, name
from tb_user
where name = #{name}
</ select>
< select id = " selectByPageNoParamsCount" resultType = " _int" >
select count(1) from tb_student
</ select>
</ mapper>
【11】分页查询
RowBounds是MyBatis中提供的一种"假分页"实现方式。对从数据库中查询到的结果进行截取。所以如果数据库中数据量特别大的时候可能会出现OOM等问题
但是由于RowBounds不需要考虑底层数据库是什么,且使用简单,所以对于一些数据量不是特别大的应用还是有人选择使用的
在SqlSession中select、selectMap、selectList中通过方法重载都提供了一个带有RowBounds
@Test
public void testRowBounds ( ) {
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
int page = 1 ;
int rows = 3 ;
RowBounds rowBounds = new RowBounds ( ( page - 1 ) * rows, rows) ;
List < User > users = session. selectList ( "user.mapper.selectAll" , null , rowBounds) ;
users. forEach ( user -> {
System . out. println ( user) ;
} ) ;
System . out. println ( "====================================================" ) ;
page = 7 ;
rowBounds = new RowBounds ( ( page - 1 ) * rows, rows) ;
users = session. selectList ( "user.mapper.selectAll" , null , rowBounds) ;
users. forEach ( user -> {
System . out. println ( user) ;
} ) ;
}
package com. bjsxt. result ;
import com. bjsxt. pojo. Student ;
import java. io. Serializable ;
import java. util. List ;
import java. util. Objects ;
public class PageResult implements Serializable {
private int currPage;
private int total;
private List < Student > list;
private int pageSize;
public void setPages ( int pages) {
}
public int getPages ( ) {
return total % pageSize == 0 ? total / pageSize : ( total / pageSize + 1 ) ;
}
public PageResult ( ) {
}
@Override
public boolean equals ( Object o) {
if ( this == o) return true ;
if ( o == null || getClass ( ) != o. getClass ( ) ) return false ;
PageResult that = ( PageResult ) o;
return currPage == that. currPage && total == that. total && pageSize == that. pageSize && Objects . equals ( list, that. list) ;
}
public int getPageSize ( ) {
return pageSize;
}
public void setPageSize ( int pageSize) {
this . pageSize = pageSize;
}
@Override
public int hashCode ( ) {
return Objects . hash ( currPage, total, list) ;
}
public int getCurrPage ( ) {
return currPage;
}
public void setCurrPage ( int currPage) {
this . currPage = currPage;
}
public int getTotal ( ) {
return total;
}
public void setTotal ( int total) {
this . total = total;
}
public List < Student > getList ( ) {
return list;
}
public void setList ( List < Student > list) {
this . list = list;
}
}
package com. bjsxt. service ;
import com. bjsxt. param. MyParam ;
import com. bjsxt. result. PageResult ;
import java. util. Map ;
public interface StudentService {
PageResult selectByPage ( MyParam param) ;
}
package com. bjsxt. service. impl ;
import com. bjsxt. dao. StudentDao ;
import com. bjsxt. dao. impl. StudentDaoImpl ;
import com. bjsxt. param. MyParam ;
import com. bjsxt. pojo. Student ;
import com. bjsxt. result. PageResult ;
import com. bjsxt. service. StudentService ;
import java. util. List ;
public class StudentServiceImpl implements StudentService {
private StudentDao studentDao = new StudentDaoImpl ( ) ;
@Override
public PageResult selectByPage ( MyParam param) {
List < Student > students = studentDao. selectByPage ( param) ;
int total = studentDao. selectCount ( param) ;
PageResult pageResult = new PageResult ( ) ;
pageResult. setCurrPage ( param. getPageNum ( ) ) ;
pageResult. setPageSize ( param. getPageSize ( ) ) ;
pageResult. setList ( students) ;
pageResult. setTotal ( total) ;
return pageResult;
}
}
package com. bjsxt. controller ;
import com. bjsxt. param. MyParam ;
import com. bjsxt. result. PageResult ;
import com. bjsxt. service. StudentService ;
import com. bjsxt. service. impl. StudentServiceImpl ;
import com. fasterxml. jackson. databind. ObjectMapper ;
import javax. servlet. ServletException ;
import javax. servlet. annotation. WebServlet ;
import javax. servlet. http. HttpServlet ;
import javax. servlet. http. HttpServletRequest ;
import javax. servlet. http. HttpServletResponse ;
import java. io. IOException ;
@WebServlet ( "/getStudent" )
public class StudentPageServlet extends HttpServlet {
private StudentService studentService = new StudentServiceImpl ( ) ;
@Override
protected void service ( HttpServletRequest req, HttpServletResponse resp) throws ServletException , IOException {
req. setCharacterEncoding ( "UTF-8" ) ;
String pageNum = req. getParameter ( "pageNum" ) ;
String pageSize = req. getParameter ( "pageSize" ) ;
String name = req. getParameter ( "name" ) ;
byte [ ] nameBytes = name. getBytes ( "ISO-8859-1" ) ;
name = new String ( nameBytes, "UTF-8" ) ;
String address = req. getParameter ( "address" ) ;
address = new String ( address. getBytes ( "ISO-8859-1" ) , "UTF-8" ) ;
MyParam param = new MyParam ( ) ;
param. setName ( ( name == null || "" . equals ( name) ? null : name) ) ;
param. setAddress ( ( address == null || "" . equals ( address) ) ? null : address) ;
param. setPageNum ( ( pageNum == null || "" . equals ( pageNum) ) ? 1 : Integer . parseInt ( pageNum) ) ;
param. setPageSize ( ( pageSize == null || "" . equals ( pageSize) ) ? 2 : Integer . parseInt ( pageSize) ) ;
PageResult pageResult = studentService. selectByPage ( param) ;
resp. setContentType ( "application/json; charset=UTF-8" ) ;
resp. setCharacterEncoding ( "UTF-8" ) ;
ObjectMapper objectMapper = new ObjectMapper ( ) ;
String json = objectMapper. writeValueAsString ( pageResult) ;
resp. getWriter ( ) . println ( json) ;
resp. getWriter ( ) . flush ( ) ;
}
}
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
<script src="js/jquery.js"></script>
<script type="text/javascript">
$(function () {
var pageNum = 1;
var pageSize = 2;
var total = 0;
getData(pageNum, pageSize, "", "");
function getData(page, size, name, address) {
$.ajax({
"type": "get",
"url": "/getStudent",
"data": {
"pageNum": page,
"pageSize": size,
"name": name,
"address": address
},
"success": function (data) {
pageNum = data.currPage;
pageSize = data.pageSize;
total = data.total;
var list = data.list;
var stuView = $("#stu");
// 清空显示视图
stuView.empty();
var body = "";
for (var i = 0; i < list.length; i++) {
var obj = list[i];
body += "<tr>";
body += "<td>";
body += obj.id;
body += "</td>";
body += "<td>";
body += obj.name;
body += "</td>";
body += "<td>";
body += obj.address;
body += "</td>";
body += "<td>";
body += "<input type='button' class='del' stuId='" + obj.id + "' value='删除'>";
body += "</td>";
body += "</tr>";
}
stuView.append(body);
// 增加上下页按钮
var pageView = $("#pageView");
// 清空
pageView.empty();
// 增加内部的上下页按钮
var pageViewBody = "";
if (pageNum > 1) {
// 有上一页
pageViewBody += "<input type='button' id='pre' value='上一页' οnclick='pre'>"
}
if (pageNum < data.pages) {
// 有下一页
pageViewBody += "<input type='button' id='next' value='下一页' οnclick='next()'>"
}
pageView.append(pageViewBody);
},
"dataType": "json"
});
}
$("#pageView").on('click', '#pre', {}, function () {
getData(pageNum - 1, pageSize, $("input[name=name]").val(), $("input[name=address]").val());
});
$("#pageView").on('click', '#next', {}, function () {
getData(pageNum + 1, pageSize, $("input[name=name]").val(), $("input[name=address]").val());
});
$("#form").on('click', '#find', {}, function () {
getData(1, pageSize, $("input[name=name]").val(), $("input[name=address]").val());
})
$("#stu").on('click', '.del', {}, function () {
if (confirm("是否确认删除")) {
var btn = $(this);
$.ajax({
"type": "get",
"url": "/delete",
"data": "id=" + btn.attr("stuId"),
"success": function (data) {
if (data == 1) {
// 成功, 刷新页面
// 重新计算总计数据的行数和最大页码
total = total - 1;
var pages = Math.ceil(total / pageSize); // 向上取整 ,最大页码
if (pageNum <= pages) {
// 当前页可以直接使用。
getData(pageNum, pageSize, $("input[name=name]").val(), $("input[name=address]").val());
} else {
// 当前页不可用,考虑当前页是不是第一页
if (pageNum == 1) {
// 第一页,没有数据了
getData(1, pageSize, $("input[name=name]").val(), $("input[name=address]").val());
} else {
// 不是第一页,查询上一页
getData(pageNum - 1, pageSize, $("input[name=name]").val(), $("input[name=address]").val());
}
}
} else {
// 失败
alert("删除失败");
}
}
});
}
});
});
</script>
</head>
<body>
<div>
<form id="form" action="/getStudent">
姓名:<input type="text" name="name">
地址:<input type="text" name="address">
<input type="button" id="find" value="查询">
</form>
</div>
<div>
<table>
<thead>
<tr>
<th>序号</th>
<th>姓名</th>
<th>地址</th>
<th>操作</th>
</tr>
</thead>
<tbody id="stu"></tbody>
</table>
</div>
<div id="pageView"></div>
</body>
</html>
【12】模糊查询
@Test
public void testLike3 ( ) {
String arg = "d" ;
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
List < User > users = session. selectList ( "user.mapper.selectByLike3" , arg) ;
users. forEach ( user -> {
System . out. println ( user) ;
} ) ;
session. close ( ) ;
}
@Test
public void testLike2 ( ) {
String arg = "d" ;
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
List < User > users = session. selectList ( "user.mapper.selectByLike2" , arg) ;
users. forEach ( user -> {
System . out. println ( user) ;
} ) ;
session. close ( ) ;
}
@Test
public void testLike1 ( ) {
String arg = "d" ;
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
List < User > users = session. selectList ( "user.mapper.selectByLike" , "%" + arg + "%" ) ;
users. forEach ( user -> {
System . out. println ( user) ;
} ) ;
session. close ( ) ;
}
< select id = " selectByLike" resultType = " com.bjsxt.pojo.User" >
select id, name
from tb_user
where name like #{name}
</ select>
< select id = " selectByLike2" resultType = " com.bjsxt.pojo.User" >
select id, name
from tb_user
where name like '%${name}%'
</ select>
< select id = " selectByLike3" resultType = " com.bjsxt.pojo.User" >
select id, name
from tb_user
where name like concat('%', #{name}, '%')
</ select>
【13】别名
<?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>
< settings>
< setting name = " logImpl" value = " LOG4J" />
< setting name = " mapUnderscoreToCamelCase" value = " true" />
</ settings>
< typeAliases>
< typeAlias type = " com.bjsxt.pojo.People" alias = " p1" />
< typeAlias type = " com.bjsxt.pojo.People" alias = " p2" />
< package name = " com.bjsxt.pojo" />
</ typeAliases>
< environments default = " mysql" >
< environment id = " mysql" >
< transactionManager type = " JDBC" > </ transactionManager>
< dataSource type = " POOLED" >
< property name = " driver" value = " com.mysql.cj.jdbc.Driver" />
< property name = " url" value = " jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai" />
< property name = " username" value = " root" />
< property name = " password" value = " root" />
</ dataSource>
</ environment>
</ environments>
< mappers>
< mapper resource = " mappers/PeopleMapper.xml" />
< mapper resource = " mappers/PeopleDao.xml" />
< package name = " com.bjsxt.mapper" />
</ mappers>
</ configuration>
MyBatis框架中内置了一些常见类型的别名。这些别名不需要配置
别名 映射的类型 别名 映射的类型 别名 映射的类型 _byte byte string String date Date _long long byte Byte decimal BigDecimal _short short long Long bigdecimal BigDecimal _int int short Short object Object _integer int int Integer map Map _double double integer Integer hashmap HashMap _float float double Double list List _boolean boolean float Float arraylist ArrayList boolean Boolean collection Collection iterator Iterator
【14】结果填充
在MyBatis框架中,查询结果处理方案(结果填充|结果映射)有若干种:
1. 自动映射 auto mapping : 当<select resultType=自定义类型>,只要字段名称和属性名称一致,自动处理映射。 其次
如:表格字段 id, name。类型 com.bjsxt.pojo.Xxx 属性(field和property)是 id name。自动映射。
当字段名和属性名不同时,可以通过查询语法的别名实现自动映射。
优势: 不需要做任何特殊的处理配置
缺点: 要求表格的字段名称和类型的属性名称必须一致。 Java中的命名规范 驼峰。 数据库命名规范 多单词之间下划线分隔,一般所有单词全小写。
2. 手工配置映射 ResultMap映射。 首选
需要在SQL配置文件中定义标签 <resultMap>,并在select标签中,使用属性resultMap实现结果处理。
<select resultMap=xxx>
优势: 一切自定义,灵活。
缺点: 配置增加
3. 驼峰对下划线 很少使用
因为Java命名习惯 驼峰; 数据库命名习惯 下划线。因为这种常见的命名习惯很通用。所以MyBatis提供自动的驼峰对下划线映射。
需要在mybatis.cfg.xml中开启这种功能。
优势: 按照常见命名习惯自动映射
缺点: 要求表格命名和类型命名必须要个遵循常见命名习惯,并一一对应。
【15】接口绑定
MyBatis提供自动的接口实现能力。称为接口绑定。
命名习惯:
每个框架或技术,都有各自的命名习惯。
MyBatis技术中,数据访问层(持久层)接口命名习惯是 XxxMapper。
正式工作开发中,数据访问层(持久层)接口命名习惯是 XxxDao, XxxDAO
接口绑定的规则:
1. 必要规则: 必须遵守的规则,有两条
1.1 SQL配置文件namespace,必须和对应的要生成实现的接口的全命名完全相同,大小写敏感。
1.2 SQL配置文件中,编写SQL语句的标签id,必须和对应的接口中的方法名完全相同,大小写敏感。
2. 可选规则: 可以遵守的规则,遵守可以简化mybatis.cfg.xml中的配置。 有两条
2.1 SQL配置文件存放的位置,和对应的接口所在的包完全相同。
2.2 SQL配置文件的文件名(不包含后缀),和对应的接口的类型名完全相同。
遵守这两个可选规则,可以在mybatis.cfg.xml中,使用<package name="接口所在包"/>一次性配置所有的SQL配置文件。
接口绑定中的参数传递:
1. 一个参数
1.1 传递简单参数。 SQL配置文件中的占位变量命名随意。框架不检查。
1.2 传递Map参数。 SQL配置文件中的占位变量名必须是Map中的key。
1.3 传递自定义类型对象(POJO实体)。 SQL配置文件中的占位变量名必须和类型的属性名(property或field)一致。
2. 多个参数
注意: 使用注解了argM的形式就不能使用了,需要通过注解中名称或paramN的方式调用
2.1 传递多个简单参数。 要求SQL配置文件中的占位变量必须按照框架要求定义。
2.1.1 如果接口的方法参数,使用的注解@Param("")定义参数名,则占位变量名必须是注解的属性值,或param1,param2,param3等。 推荐的方式
2.1.2 如果接口的方法参数,不使用注解,则占位变量名必须是 param1,param2或 arg0, arg1 等。不推荐的方式。面试可能问。
2.2 传递多个参数,带有Map或自定义类型对象。 要求SQL配置文件的占位变量必须按照框架要求定义。
2.2.1 如果接口方法参数,使用注解,可以使用 #{注解属性.key}|#{注解属性.属性名} 或者 #{param1.key} | #{param1.属性名}
2.2.2 如果接口方法参数,不使用注解,可以使用 #{arg0.key} | #{arg0.属性名} 或者 #{param1.key} | #{param1.属性名}
int insert1 ( @Param ( "id" ) Integer id, @Param ( "name" ) String name) ;
int insert2 ( Integer id, String name) ;
int insert3 ( @Param ( "myMap" ) Map < String , Object > map, @Param ( "peo" ) People people) ;
int insert4 ( Map < String , Object > map, People people) ;
< insert id = " insert1" >
insert into tb_people(id, name) values(#{param1}, #{name})
</ insert>
< insert id = " insert2" >
insert into tb_people(id, name) values(#{arg0}, #{param2})
</ insert>
< insert id = " insert3" >
insert into tb_people(id, name) values(#{param1.id}, #{peo.name})
</ insert>
< insert id = " insert4" >
insert into tb_people(id, name) values(#{arg0.id}, #{param2.name})
</ insert>
【16】主键回填
< insert id = " insertByIncrement" useGeneratedKeys = " true" keyProperty = " id" >
insert into tb_people(id, name) values(default, #{name})
</ insert>
【17】动态SQL
动态SQL在MyBatis中是基于标签实现的。
1. if标签。判断标签。根据某boolean值,或结果为boolean的表达式(布尔表达式)动态判断。
在MyBatis的SQL配置文件中,可以写OGNL表达式。语法类似JSP中的EL表达式。在各种标签中使用,得到需要的结果。
2. choose标签。选择标签。类似java语法中是switch case。根据多个不同的判断逻辑,选择某一个分支。
<choose><when test=""></when><when test=""></when><when test=""></when><otherwise></otherwise></choose>
3. trim标签。为标签体中的文本去除前后缀或增加前后缀。先删除前后缀,再增加前后缀
<trim prefix="增加的前缀" prefixOverrides="删除的前缀" suffix="增加的后缀" suffixOverrides="删除的后缀"></trim>
4. where标签。用在SQL语句的where子句动态处理中。如果标签中存在文本,则增加where子句。如果标签中不存在文本,则不增加where子句。
where标签,有动态识别能力,如果标签内的文本以and或者or开头,自动删除前缀and或or。
5. set标签。用在update语句中的标签。动态增加set子句。 有自动识别能力,如果标签内的文本以','结尾,自动删除后缀','。
6. forEach标签。用于循环集合的标签。常用于,批量新增,范围in|not in查询等。
要循环的变量: 命名方式有
1. 使用注解@Param("名字"), 推荐使用
2. arg0, collection, list 可用于List, Collection
3. arg0, array 可用于 []
<foreach collection="要循环的变量" open="循环内容的开头字符串"
close="循环内容的结束字符串" item="循环过程的变量名" separator="每次循环时间隔符"></foreach>
<foreach collection="要循环的变量" open="("
close=")" item="a" separator=","> #{a} </foreach>
循环遍历后的结果文本是: (?, ?, ?)
7. bind 标签,用于绑定修改后的特定数据的。常用于模糊查询处理。
<bind name="定义修改后的变量名" value="OGNL表达式,做变量处理" />
8. sql和include标签。 sql标签,定义SQL语句片段。 include标签,引用定义好的SQL语法片段。
常用于定义字段列表,或复杂的判断逻辑。
如:
<sql id="xxx">数十个字段</sql>
<sql id="yyy">复杂到不向再写一次的判断逻辑
<where>
十几个if,+不同的查询条件
</where>
</sql>
< select id = " selectIf" resultType = " People" >
select * from people where 1=1
< if test = " name!=null" >
and name=#{name}
</ if>
< if test = " address!=null" >
and address=#{address}
</ if>
</ select>
< select id = " selectByChoose" resultType = " People" >
select id, name from tb_people
where 1 = 1
< if test = " id != null" >
< choose>
< when test = " id lt 10" >
and id < 10
</ when>
< when test = " id gte 10 and id lt 20" >
and id between 10 and 20
</ when>
< otherwise>
and id > 20
</ otherwise>
</ choose>
</ if>
</ select>
< select id = " selectIf" resultType = " People" >
select * from people
< trim prefix = " where" prefixOverrides = " and" >
< if test = " name!=null" >
and name=#{name}
</ if>
< if test = " address!=null" >
and address=#{address}
</ if>
</ trim>
</ select>
< select id = " selectIf" resultType = " People" >
select * from people
< where>
< if test = " name!=null" >
and name=#{name}
</ if>
< if test = " address!=null" >
and address=#{address}
</ if>
</ where>
</ select>
< update id = " update" >
update people
< set>
< if test = " name!=null" >
name=#{name},
</ if>
< if test = " address!=null" >
address=#{address},
</ if>
id=#{id}
</ set>
where id = #{id}
</ update>
< select id = " selectByForEach" resultType = " People" >
select id, name from tb_people where id in
< foreach collection = " ids" open = " (" close = " )" item = " id" separator = " ," >
#{id}
</ foreach>
</ select>
< insert id = " insertBatch" >
insert into tb_people(id, name)
< foreach collection = " collection" open = " values(" close = " )" item = " p" separator = " ),(" >
#{p.id},#{p.name}
</ foreach>
</ insert>
List < People > selectByForEach ( @Param ( "ids" ) List < Integer > ids) ;
int insertBatch ( List < People > list) ;
< select id = " selectLike" resultType = " People" >
< bind name = " n" value = " ' %'+name+'%'" />
select * from people where name like #{n}
</ select>
< select id = " selectSQL" resultType = " People" >
select < include refid = " mysqlpart" > </ include> from people
</ select>
< sql id = " mysqlpart" >
id,name,address
</ sql>
【18】常用注解
注解 功能 @Insert 新增 @Delete 删除 @Update 修改 @Select 查询 @Results / @Result 结果映射 @SelectKey 主键回填 @InsertProvider 调用SQL构建器。新增专用 @DeleteProvider 调用SQL构建器。删除专用 @UpdateProvider 调用SQL构建器。修改专用 @SelectProvider 调用SQL构建器。查询专用 @Param 定义参数的名称
常用注解:
1. 新增
@Insert("sql语句")
注解中处理参数的方式,和SQL配置文件中的方式完全相同。但是此注解没有动态SQL标签的功能。
1.1 主键回填
@SelectKey(statement="sql", before=真假, keyProperty="主键属性名"[, keyColumn="查询主键的字段名"], resultType=类型)
2. 更新
@Update("sql"), 和SQL配置文件采用相同的参数处理方案。
3. 删除
@Delete("sql")
4. 查询
@Select("sql")
使用注解时,返回结果类型,默认就是方法的返回值类型或返回值集合的泛型或Map。 相当于SQL配置文件中的<select resultType>配置方式
5. 动态新增
@InsertProvider(type=Xxx.class, method="生成SQL语句的方法名")
6. 动态更新
@UpdateProvider()
7. 动态删除
@DeleteProvider()
8. 动态查询
@SelectProvider()
9. 结果映射处理
@Results( //相当于resultMap标签, 没有强制id, 类型就是方法返回类型或返回集合的泛型
@Result(column="字段名" property="属性名"), // 配置类型中的一个属性和一个字段的映射
@Result(column="字段名" property="属性名")
)
使用注解:
1. 前提: 在mybatis.cfg.xml配置文件中,使用<mapper class="" />或者<package name="" />配置。
<mapper class="" /> 配置MyBatis框架扫描到的数据访问层接口类型, XxxMapper, XxxDao。
<package name="" /> 配置MyBatis框架扫描数据访问层接口类型的包,使用报名定义规则赋值 x.y.z
2. 日志: 使用接口的包作为日志输出的定位即可。更精确的方式是包名.接口名。
<?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>
< settings>
< setting name = " logImpl" value = " LOG4J" />
</ settings>
< typeAliases>
< package name = " com.bjsxt.pojo" />
</ typeAliases>
< environments default = " mysql" >
< environment id = " mysql" >
< transactionManager type = " JDBC" > </ transactionManager>
< dataSource type = " POOLED" >
< property name = " driver" value = " com.mysql.cj.jdbc.Driver" />
< property name = " url" value = " jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai" />
< property name = " username" value = " root" />
< property name = " password" value = " root" />
</ dataSource>
</ environment>
</ environments>
< mappers>
< mapper class = " com.bjsxt.mapper.DeptMapper" />
</ mappers>
</ configuration>
create table tb_dept(
dep_id int ( 11 ) primary key auto_increment comment '部门主键' ,
dep_name varchar ( 32 ) comment '部门名称' ,
dep_addr varchar ( 255 ) comment '部门地址'
) comment '部门表' ;
insert into tb_dept( dep_id, dep_name, dep_addr)
values ( default , '教学部' , '赛蒂工业园' ) ,
( default , '行政部' , '赛蒂工业园' ) ,
( default , '财务部' , '赛蒂工业园' ) ;
package com. bjsxt. utils ;
import org. apache. ibatis. io. Resources ;
import org. apache. ibatis. session. SqlSessionFactory ;
import org. apache. ibatis. session. SqlSessionFactoryBuilder ;
public final class MyBatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( Resources . getResourceAsStream ( "mybatis.cfg.xml" ) ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
throw new ExceptionInInitializerError ( e) ;
}
}
private MyBatisUtils ( ) {
}
public static SqlSessionFactory getFactory ( ) {
return sqlSessionFactory;
}
}
package com. bjsxt. mapper ;
import com. bjsxt. pojo. Dept ;
import com. bjsxt. provider. MyInsertSqlProvider ;
import org. apache. ibatis. annotations. * ;
import java. util. List ;
import java. util. Map ;
public interface DeptMapper {
@SelectProvider ( type = MyInsertSqlProvider . class , method = "select" )
List < Dept > selectByProvider ( Integer id) ;
@InsertProvider ( type = MyInsertSqlProvider . class , method = "suibian" )
int insertBatch ( List < Dept > list) ;
@Results ( value = {
@Result ( column = "dep_id" , property = "depId" ) , @Result ( column = "dep_name" , property = "depName" ) , @Result ( column = "dep_addr" , property = "depAddr" ) } )
@Select ( "select dep_id, dep_name, dep_addr from tb_dept where dep_id = #{depId}" )
Dept selectByResults ( Integer id) ;
@Select ( "select dep_id , dep_name , dep_addr from tb_dept" )
List < Map < String , Object > > selectAllAsMap ( ) ;
@Select ( "select dep_id as depId, dep_name as depName, dep_addr as depAddr from tb_dept" )
List < Dept > selectAll ( ) ;
@Select ( "select dep_id as depId, dep_name as depName, dep_addr as depAddr from tb_dept where dep_id = #{depId}" )
Dept selectById ( Integer id) ;
@Delete ( "delete from tb_dept where dep_id = #{depId}" )
int deleteById ( Integer id) ;
@Update ( "update tb_dept set dep_name = #{depName}, dep_addr = #{depAddr} where dep_id = #{depId}" )
int updateById ( Dept dept) ;
@Insert ( "insert into tb_dept(dep_id, dep_name, dep_addr) values(#{depId}, #{depName}, #{depAddr})" )
@SelectKey ( statement = "select @@identity as id" , keyProperty = "depId" , before = false , resultType = Integer . class , keyColumn = "id" )
int insertDept ( Dept dept) ;
}
package com. bjsxt. provider ;
import com. bjsxt. pojo. Dept ;
import org. apache. ibatis. jdbc. SQL ;
import java. util. List ;
public class MyInsertSqlProvider {
public String select ( Integer id) {
SQL s = new SQL ( ) ;
s. SELECT ( "dep_id as depId" , "dep_name as depName" , "dep_addr as depAddr" ) . FROM ( "tb_dept" ) ;
if ( id == null ) {
return s. toString ( ) ;
}
if ( id < 10 ) {
s. WHERE ( "dep_id = 2" ) ;
return s. toString ( ) ;
}
if ( id == 10 ) {
s. WHERE ( "dep_id = 3" ) ;
return s. toString ( ) ;
}
if ( id > 10 ) {
s. WHERE ( "dep_id = 4" ) ;
return s. toString ( ) ;
}
return s. toString ( ) ;
}
public String suibian ( List < Dept > list) {
StringBuilder sql = new StringBuilder ( "insert into tb_dept(dep_id, dep_name, dep_addr) values " ) ;
for ( Dept dept : list) {
sql. append ( "(" ) . append ( dept. getDepId ( ) ) . append ( ", \"" ) . append ( dept. getDepName ( ) ) . append ( "\", \"" ) . append ( dept. getDepAddr ( ) ) . append ( "\")," ) ;
}
sql. deleteCharAt ( sql. length ( ) - 1 ) ;
System . out. println ( "动态生成的SQL是:" + sql. toString ( ) ) ;
return sql. toString ( ) ;
}
}
package com. bjsxt. test ;
import com. bjsxt. mapper. DeptMapper ;
import com. bjsxt. pojo. Dept ;
import com. bjsxt. utils. MyBatisUtils ;
import org. apache. ibatis. session. SqlSession ;
import org. junit. Before ;
import org. junit. Test ;
import java. util. ArrayList ;
import java. util. List ;
import java. util. Map ;
public class TestAnnotation {
private DeptMapper deptMapper;
private SqlSession session;
@Before
public void before ( ) {
session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
deptMapper = session. getMapper ( DeptMapper . class ) ;
System . out. println ( "before方法运行,给SqlSession和DeptMapper赋值" ) ;
}
@Test
public void testSelectProvider ( ) {
List < Dept > list = deptMapper. selectByProvider ( null ) ;
System . out. println ( list) ;
list = deptMapper. selectByProvider ( 1 ) ;
System . out. println ( list) ;
list = deptMapper. selectByProvider ( 10 ) ;
System . out. println ( list) ;
list = deptMapper. selectByProvider ( 11 ) ;
System . out. println ( list) ;
}
@Test
public void testInsertBatch ( ) {
List < Dept > list = new ArrayList < > ( ) ;
list. add ( new Dept ( null , "市场部" , "赛蒂工业园" ) ) ;
list. add ( new Dept ( null , "策划部" , "赛蒂工业园" ) ) ;
list. add ( new Dept ( null , "开发部" , "赛蒂工业园" ) ) ;
int rows = deptMapper. insertBatch ( list) ;
System . out. println ( rows) ;
session. commit ( ) ;
session. close ( ) ;
}
@Test
public void testSelectResults ( ) {
Dept dept = deptMapper. selectByResults ( 2 ) ;
System . out. println ( dept) ;
session. commit ( ) ;
session. close ( ) ;
}
@Test
public void testSelect ( ) {
Dept dept = deptMapper. selectById ( 1 ) ;
System . out. println ( dept) ;
System . out. println ( "================================================" ) ;
List < Dept > list = deptMapper. selectAll ( ) ;
list. forEach ( d -> {
System . out. println ( d) ;
} ) ;
System . out. println ( "================================================" ) ;
List < Map < String , Object > > maps = deptMapper. selectAllAsMap ( ) ;
maps. forEach ( map -> {
System . out. println ( map) ;
} ) ;
session. commit ( ) ;
session. close ( ) ;
}
@Test
public void testDelete ( ) {
deptMapper. deleteById ( 5 ) ;
session. commit ( ) ;
session. close ( ) ;
}
@Test
public void testUpdate ( ) {
Dept dept = new Dept ( 4 , "总裁办" , "赛蒂工业园" ) ;
deptMapper. updateById ( dept) ;
session. commit ( ) ;
session. close ( ) ;
}
@Test
public void testInsert ( ) {
SqlSession session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
DeptMapper deptMapper = session. getMapper ( DeptMapper . class ) ;
Dept dept = new Dept ( null , "测试新增注解" , "赛蒂工业园" ) ;
System . out. println ( "新增前:" + dept) ;
int rows = deptMapper. insertDept ( dept) ;
System . out. println ( rows) ;
System . out. println ( "新增后:" + dept) ;
session. commit ( ) ;
session. close ( ) ;
}
}
【19】多表查询
表格关系:
1. 一对一
1.1 唯一外键: A表格有字段id。 B表格有字段id和a_id,其中a_id是外键,引用A表格的id字段,且a_id字段约束是unique。常用
1.2 共享主键: A表格有字段id。 B表格有字段id,其中B表格的id字段是主键,且是一个外键,引用A表格的id字段。不常用
2. 一对多
A表格有字段id。 B表格有字段id和a_id,其中a_id是外键,引用A表格的id字段
3. 多对多
A表格有字段id。B表格有字段id。 C表格有字段a_id和b_id,分别引用A表格id和B表格的id。
Java中类型的关系有:
依赖:弱关系,有类型A和B,只要A中有B,就是依赖。A依赖B。
聚合:中等强度的关系,有类型A和B,A中有B类型的或B泛型的实例变量。
组合:强关系,有类型A和B,A中有B类型或B泛型的实例变量;B中有A类型的或A泛型的实例变量。且A和B不能独立存在。
MyBatis如何处理多表关联查询:
当前类型引用的其他类型属性是非集合: 在resultMap标签中,增加子标签 <association> 来描述这个属性。
当前类型引用的其他类型属性是集合: 在resultMap标签中,增加子标签 <collection> 来描述这个属性。
MyBatis实现多表关联数据查询的方式有:
N+1次查询:如,A类型对应表格a,B类型对应表格b。A类型中有List<B>类型的属性。查询A的同时,要求查询对应的B集合。 1 是查询A, N 是若干次查询和A有关系的B
1次查询:如,A类型对应表格a,B类型对应表格b。A类型中有List<B>类型的属性。查询A的同时,要求查询对应的B集合。 使用多表联合查询的方式,一次性查询A和B。
学习案例:
1. 查询全部地址,同时查询地址对应的客户数据
1.1 N+1次
1.2 1次
2. 查询全部客户,同时查询客户对应的地址集合
2.1 N+1次
2.2 1次
3. 业务装配,就是单独查询每个表格的数据,使用代码,把数据直接的关系维护起来。
如:先查询所有的客户,迭代分析,根据客户主键,查询每个客户的地址集合,维护关系。
create table tb_customer(
id int ( 11 ) primary key auto_increment ,
name varchar ( 32 ) comment '姓名' ,
username varchar ( 32 ) comment '登录名' ,
password varchar ( 32 ) comment '登录密码'
) comment '客户表格' ;
create table tb_address(
id int ( 11 ) primary key auto_increment ,
province varchar ( 32 ) comment '省' ,
city varchar ( 32 ) comment '市' ,
address varchar ( 255 ) comment '具体地址' ,
customer_id int ( 11 ) references tb_customer( id)
) ;
insert into tb_customer( id, name, username, password) values
( default , '张三' , 'zhangsan' , '123' ) ,
( default , '李四' , 'lisi' , '123' ) ,
( default , '王五' , 'wangwu' , '123' ) ;
insert into tb_address( id, province, city, address, customer_id) values
( default , '北京' , '北京' , '西三旗' , 1 ) ,
( default , '北京' , '北京' , '西二旗' , 1 ) ,
( default , '北京' , '北京' , '西北旺' , 1 ) ,
( default , '北京' , '北京' , '经海路8号' , 2 ) ,
( default , '北京' , '北京' , '经海路9号' , 2 ) ,
( default , '北京' , '北京' , '经海路10号' , 2 ) ,
( default , '北京' , '北京' , '天安门东' , 3 ) ,
( default , '北京' , '北京' , '天安门西' , 3 ) ,
( default , '北京' , '北京' , '前门大街' , 3 ) ;
<?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>
< settings>
< setting name = " logImpl" value = " LOG4J" />
</ settings>
< typeAliases>
< package name = " com.bjsxt.pojo" />
</ typeAliases>
< environments default = " mysql" >
< environment id = " mysql" >
< transactionManager type = " JDBC" > </ transactionManager>
< dataSource type = " POOLED" >
< property name = " driver" value = " com.mysql.cj.jdbc.Driver" />
< property name = " url" value = " jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai" />
< property name = " username" value = " root" />
< property name = " password" value = " root" />
</ dataSource>
</ environment>
</ environments>
< mappers>
< mapper resource = " mappers/CustomerMapper.xml" />
< mapper resource = " mappers/AddressMapper.xml" />
</ mappers>
</ configuration>
package com. bjsxt. pojo ;
import java. io. Serializable ;
import java. util. List ;
import java. util. Objects ;
public class Customer implements Serializable {
private Integer id;
private String name;
private String username;
private String password;
private List < Address > addressList;
public Customer ( ) {
}
public Customer ( Integer id, String name, String username, String password) {
this . id = id;
this . name = name;
this . username = username;
this . password = password;
}
public List < Address > getAddressList ( ) {
return addressList;
}
public void setAddressList ( List < Address > addressList) {
this . addressList = addressList;
}
@Override
public String toString ( ) {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}' ;
}
@Override
public boolean equals ( Object o) {
if ( this == o) return true ;
if ( o == null || getClass ( ) != o. getClass ( ) ) return false ;
Customer customer = ( Customer ) o;
return Objects . equals ( id, customer. id) &&
Objects . equals ( name, customer. name) &&
Objects . equals ( username, customer. username) &&
Objects . equals ( password, customer. password) ;
}
@Override
public int hashCode ( ) {
return Objects . hash ( id, name, username, password) ;
}
public Integer getId ( ) {
return id;
}
public void setId ( Integer id) {
this . id = id;
}
public String getName ( ) {
return name;
}
public void setName ( String name) {
this . name = name;
}
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;
}
}
package com. bjsxt. pojo ;
import java. io. Serializable ;
import java. util. Objects ;
public class Address implements Serializable {
private Integer id;
private String province;
private String city;
private String address;
private Customer customer;
private Integer customerId;
public Address ( ) {
}
public Address ( Integer id, String province, String city, String address) {
this . id = id;
this . province = province;
this . city = city;
this . address = address;
}
@Override
public String toString ( ) {
return "Address{" +
"id=" + id +
", province='" + province + '\'' +
", city='" + city + '\'' +
", address='" + address + '\'' +
'}' ;
}
public Integer getCustomerId ( ) {
return customerId;
}
public void setCustomerId ( Integer customerId) {
this . customerId = customerId;
}
@Override
public boolean equals ( Object o) {
if ( this == o) return true ;
if ( o == null || getClass ( ) != o. getClass ( ) ) return false ;
Address address1 = ( Address ) o;
return Objects . equals ( id, address1. id) &&
Objects . equals ( province, address1. province) &&
Objects . equals ( city, address1. city) &&
Objects . equals ( address, address1. address) &&
Objects . equals ( customer, address1. customer) ;
}
@Override
public int hashCode ( ) {
return Objects . hash ( id, province, city, address, customer) ;
}
public Integer getId ( ) {
return id;
}
public void setId ( Integer id) {
this . id = id;
}
public String getProvince ( ) {
return province;
}
public void setProvince ( String province) {
this . province = province;
}
public String getCity ( ) {
return city;
}
public void setCity ( String city) {
this . city = city;
}
public String getAddress ( ) {
return address;
}
public void setAddress ( String address) {
this . address = address;
}
public Customer getCustomer ( ) {
return customer;
}
public void setCustomer ( Customer customer) {
this . customer = customer;
}
}
package com. bjsxt. mapper ;
import com. bjsxt. pojo. Address ;
import java. util. List ;
public interface AddressMapper {
List < Address > selectAll ( ) ;
List < Address > selectAll1 ( ) ;
}
package com. bjsxt. mapper ;
import com. bjsxt. pojo. Customer ;
import java. util. List ;
public interface CustomerMapper {
List < Customer > selectAll ( ) ;
List < Customer > selectAll1 ( ) ;
}
<?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.bjsxt.mapper.AddressMapper" >
< resultMap id = " addrMap1" type = " Address" >
< id column = " id" property = " id" />
< result column = " province" property = " province" />
< result property = " city" column = " city" />
< result column = " address" property = " address" />
< association property = " customer" javaType = " Customer" >
< id column = " cusId" property = " id" />
< result column = " name" property = " name" />
< result column = " username" property = " username" />
< result column = " password" property = " password" />
</ association>
</ resultMap>
< select id = " selectAll1" resultMap = " addrMap1" >
select addr.id, addr.province, addr.city, addr.address, cus.id as cusId, cus.name, cus.username, cus.password from
tb_address addr left join tb_customer cus on addr.customer_id = cus.id
</ select>
< resultMap id = " addrMap" type = " Address" >
< id column = " id" property = " id" />
< result column = " province" property = " province" />
< result property = " city" column = " city" />
< result column = " address" property = " address" />
< association property = " customer" javaType = " Customer"
select = " com.bjsxt.mapper.CustomerMapper.selectById"
column = " {id = customer_id}" > </ association>
</ resultMap>
< select id = " selectAll" resultMap = " addrMap" >
select id, province, city, address, customer_id from tb_address
</ select>
< select id = " selectAddrByCustomer" resultType = " Address" >
select id, province, city, address from tb_address where customer_id = #{customerId}
</ select>
</ 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.bjsxt.mapper.CustomerMapper" >
< resultMap id = " cusMap1" type = " Customer" >
< id column = " cusId" property = " id" />
< result column = " name" property = " name" />
< result column = " username" property = " username" />
< result column = " password" property = " password" />
< collection property = " addressList" javaType = " java.util.ArrayList"
ofType = " Address" >
< id column = " id" property = " id" />
< result column = " province" property = " province" />
< result column = " city" property = " city" />
< result column = " address" property = " address" />
</ collection>
</ resultMap>
< select id = " selectAll1" resultMap = " cusMap1" >
select cus.id as cusId, cus.name, cus.username, cus.password, addr.id, addr.province, addr.city, addr.address from
tb_customer as cus left join tb_address as addr on cus.id = addr.customer_id
</ select>
< resultMap id = " cusMap" type = " Customer" >
< id column = " id" property = " id" />
< result column = " name" property = " name" />
< result column = " username" property = " username" />
< result column = " password" property = " password" />
< collection property = " addressList" javaType = " java.util.List"
ofType = " Address" select = " com.bjsxt.mapper.AddressMapper.selectAddrByCustomer"
column = " {customerId = id}" > </ collection>
</ resultMap>
< select id = " selectAll" resultMap = " cusMap" >
select id, name, username, password from tb_customer
</ select>
< select id = " selectById" resultType = " Customer" >
select id, name, username, password from tb_customer where id = #{id}
</ select>
</ mapper>
package com. bjsxt. test ;
import com. bjsxt. mapper. AddressMapper ;
import com. bjsxt. mapper. CustomerMapper ;
import com. bjsxt. pojo. Address ;
import com. bjsxt. pojo. Customer ;
import com. bjsxt. utils. MyBatisUtils ;
import org. apache. ibatis. session. SqlSession ;
import org. junit. Before ;
import org. junit. Test ;
import java. util. ArrayList ;
import java. util. HashMap ;
import java. util. List ;
import java. util. Map ;
public class TestAssociation {
private SqlSession session;
private AddressMapper addressMapper;
private CustomerMapper customerMapper;
@Before
public void before ( ) {
session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
addressMapper = session. getMapper ( AddressMapper . class ) ;
customerMapper = session. getMapper ( CustomerMapper . class ) ;
}
@Test
public void testAssociation1 ( ) {
List < Address > list = addressMapper. selectAll1 ( ) ;
for ( Address address : list) {
System . out. println ( address) ;
System . out. println ( address. getCustomer ( ) ) ;
System . out. println ( "====================================================" ) ;
}
}
@Test
public void testAssociation ( ) {
List < Address > list = addressMapper. selectAll ( ) ;
}
}
package com. bjsxt. test ;
import com. bjsxt. mapper. CustomerMapper ;
import com. bjsxt. pojo. Address ;
import com. bjsxt. pojo. Customer ;
import com. bjsxt. utils. MyBatisUtils ;
import org. apache. ibatis. session. SqlSession ;
import org. junit. Before ;
import org. junit. Test ;
import java. util. List ;
public class TestCollection {
private SqlSession session;
private CustomerMapper customerMapper;
@Before
public void before ( ) {
session = MyBatisUtils . getFactory ( ) . openSession ( ) ;
customerMapper = session. getMapper ( CustomerMapper . class ) ;
}
@Test
public void testCollection1 ( ) {
List < Customer > list = customerMapper. selectAll1 ( ) ;
for ( Customer customer : list) {
System . out. println ( customer) ;
for ( Address address : customer. getAddressList ( ) ) {
System . out. println ( address) ;
}
System . out. println ( "=================================================" ) ;
}
}
@Test
public void testCollection ( ) {
List < Customer > list = customerMapper. selectAll ( ) ;
for ( Customer customer : list) {
System . out. println ( customer) ;
for ( Address address : customer. getAddressList ( ) ) {
System . out. println ( address) ;
}
System . out. println ( "==============================================================" ) ;
}
}
}
【20】延迟加载
延迟加载只能出现在多表联合查询的N+1方式中
表示当执行当前方法时,是否立即执行关联方法的SQL
全局配置从3.4.1版本开始需要在MyBatis置文件里面配置lazyLoadingEnabled=true即可在当前项目所有N+1的位置开启延迟加载
局部配置需要在collection或association标签中配置fetchType属性。fetchType可取值:lazy(延迟加载)和eager(立即加载)
当配置了fetchType属性后,全局settings的配置被覆盖,对于当前标签以fetchType属性值为准
< settings>
< setting name = " lazyLoadingEnabled" value = " true" />
</ settings>
< resultMap id = " empMap2" type = " Emp" >
< id column = " e_id" property = " id" />
< result column = " e_name" property = " name" />
< association property = " dept"
javaType = " Dept"
select = " com.bjsxt.mapper.DeptMapper.selectById"
column = " e_d_id"
fetchType = " lazy" >
</ association>
</ resultMap>
< select id = " selectAllN1" resultMap = " empMap2" >
select e_id,e_name,e_d_id from emp
</ select>
属性名 解释说明 可取值 默认值 lazyLoadingEnabled 延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。 特定关联关系中可通过设置 fetchType
属性来覆盖该项的开关状态。 true | false false aggressiveLazyLoading 开启时,任一方法的调用都会加载该对象的所有延迟加载属性。 否则,每个延迟加载属性会按需加载(参考 lazyLoadTriggerMethods
)。 true | false false (在 3.4.1 及之前的版本中默认为 true)
【21】缓存
[1] 一级缓存
会话级缓存、线程级缓存,默认开启一级缓存
要求:必须使用同一个会话、执行同一条SQL、使用完全相同的参数
一级缓存中的数据保存流程:
1. 执行SQL,查询数据
2. 查询结果保存到一级缓存中
[2] 二级缓存
会话工厂缓存、进程级缓存。
要求:必须使用同一个会话工厂,执行同一条SQL,使用完全相同的参数,且在事务结束后,才能使用的缓存。实体必须实现接口Serializable
二级缓存可能保存在内存,也可能保存在文件中。MyBatis使用Object输出/输入流,实现文件和内存的缓存数据读写。
二级缓存中的数据保存流程:
1. 执行SQL,查询数据
2. 查询结果保存到一级缓存中
3. 提交/回滚/关闭会话,刷新一级缓存到二级缓存
4. 查询同样的SQL,且使用同样的参数,使用二级缓存
[3] 注意
开发时,只用一级缓存。不用二级缓存。由于二级缓存,相对性能低,安全差。
二级缓存是以 namespace 为单位的,不同 namespace 下的操作互不影响
查询数据顺序 二级-->一级--->数据库--->把数据保存到一级,当sqlsession关闭或者提交的时候,把数据刷入到二级缓存中
[4] 只读缓存(readonly=true)
配置的目的是优化查询性能,确保缓存不被写入,但它不会影响数据库的提交操作。数据库中的数据会按照提交操作进行实际的修改。
提交操作对数据库的数据进行实际更改,不管缓存是否配置为只读。
MyBatis 通过自动清除相关缓存来确保数据的一致性,使得提交操作后的数据在下次查询时能够得到正确的反映。
< settings>
< setting name = " cacheEnabled" value = " true" />
</ settings>
< mapper namespace = " com.example.MyMapper" >
< cache type = " org.apache.ibatis.cache.decorators.Cache" />
< select id = " findByEmpno" resultType = " emp" useCache = " true" flushCache = " false" >
</ mapper>
public class Dept implements Serializable { . . . }
cache标签属性 含义 默认值 type 自定义缓存类,要求实现org.apache.ibatis.cache.Cache接口 null readOnly 配置的目的是优化查询性能,确保缓存不被写入,但它不会影响数据库的提交操作。数据库中的数据会按照提交操作进行实际的修改。提交操作对数据库的数据进行实际更改,不管缓存是否配置为只读。MyBatis 通过自动清除相关缓存来确保数据的一致性,使得提交操作后的数据在下次查询时能够得到正确的反映。是否只读true:给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这提供了很重要的性能优势。false:会返回缓存对象的拷贝(通过序列化) 。这会慢一些,但是安全。 false eviction 缓存策略LRU(默认) – 最近最少使用:移除最长时间不被使用的对象。FIFO – 先进先出:按对象进入缓存的顺序来移除它们。SOFT – 软引用:基于垃圾回收器状态和软引用规则移除对象。WEAK – 弱引用:更积极地基于垃圾收集器状态和弱引用规则移除对象。 LRU flushInterval 刷新间隔,毫秒为单位。默认为null,也就是没有刷新间隔,只有执行update、insert、delete语句才会刷新 null size 缓存对象个数 1024 blocking 是否使用阻塞性缓存BlockingCachetrue:在查询缓存时锁住对应的Key,如果缓存命中了则会释放对应的锁,否则会在查询数据库以后再释放锁,保证只有一个线程到数据库中查找指定key对应的数据false:不使用阻塞性缓存,性能更好 false
【23】四大核心接口介绍及执行流程
【24】自定义插件
MyBatis中支持扩展插件,所有插件都必须实现org.apache.ibatis.plugin.Interceptor接口,可以对四大核心接口进行拦截
List < Emp > selectAllpage ( ) ;
< resultMap id = " empMap5" type = " Emp" >
< id column = " e_id" property = " id" />
< result column = " e_name" property = " name" />
</ resultMap>
< select id = " selectByEid" resultMap = " empMap5" >
select e_id,e_name,e_d_id from emp
</ select>
public class MyPageHelper {
protected static Integer pageStart;
protected static Integer pageSize;
public static void startPage ( int pageStartArg, int pageSizeArg) {
pageStart = pageStartArg;
pageSize = pageSizeArg;
}
}
package com. bjsxt. interceptor ;
import org. apache. ibatis. executor. statement. StatementHandler ;
import org. apache. ibatis. mapping. BoundSql ;
import org. apache. ibatis. plugin. * ;
import org. apache. ibatis. reflection. MetaObject ;
import org. apache. ibatis. reflection. SystemMetaObject ;
import java. sql. Connection ;
import java. util. Properties ;
@Intercepts ( value = { @Signature (
type = StatementHandler . class ,
method = "prepare" ,
args = { Connection . class , Integer . class }
) } )
public class MyPageHelperInterceptor implements Interceptor {
@Override
public Object intercept ( Invocation invocation) throws Throwable {
StatementHandler target = ( StatementHandler ) invocation. getTarget ( ) ;
BoundSql boundSql = target. getBoundSql ( ) ;
String sql = boundSql. getSql ( ) ;
if ( MyPageHelper . pageStart!= null && MyPageHelper . pageSize!= null ) {
sql += " limit " + MyPageHelper . pageStart+ "," + MyPageHelper . pageSize;
}
MetaObject metaObject = SystemMetaObject . forObject ( target) ;
metaObject. setValue ( "parameterHandler.boundSql.sql" , sql) ;
return invocation. proceed ( ) ;
}
@Override
public Object plugin ( Object target) {
if ( target instanceof StatementHandler ) {
return Plugin . wrap ( target, this ) ;
}
return target;
}
@Override
public void setProperties ( Properties properties) {
System . out. println ( properties. getProperty ( "dialect" ) ) ;
}
}
< plugins>
< plugin interceptor = " com.bjsxt.interceptor.MyPageHelperInterceptor" >
< property name = " dialect" value = " mysql" />
</ plugin>
</ plugins>
public class Test {
public static void main ( String [ ] args) throws IOException {
InputStream is = Resources . getResourceAsStream ( "mybatis.cfg.xml" ) ;
SqlSessionFactory factory = new SqlSessionFactoryBuilder ( ) . build ( is) ;
SqlSession session = factory. openSession ( ) ;
EmpMapper empMapper = session. getMapper ( EmpMapper . class ) ;
MyPageHelper . startPage ( 0 , 2 ) ;
List < Emp > list = empMapper. selectAllpage ( ) ;
System . out. println ( list) ;
session. close ( ) ;
}
}
【25】执行器类型
【26】执行原理