一、Mybatis
思路 ：搭建框架--导入Mybatis--编写代码--测试
1、搭建环境
-
搭建数据库
-
创建一个父项目
-
导包，导maven依赖
<!-- mysql驱动依赖--> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <!-- mybatis--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <!-- junit依赖--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies> <!-- 在build中配置resources，来防止我们资源导出失败的问题--> <build> <resources> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources> </build> -
创建数据库
create table `user`( `id` int(11) not null auto_increment comment '编号', `name` varchar(20) default null, `pwd` varchar(20) default null, primary key(`id`) )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2、在这个父类里面创建一个模块
-
资源加载pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <parent> <artifactId>Mybatis-study</artifactId> <groupId>com.lyh</groupId> <version>1.0-SNAPSHOT</version> </parent> <modelVersion>4.0.0</modelVersion> <artifactId>mybatis-01</artifactId> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </properties> <!-- 在build中配置resources，来防止我们资源导出失败的问题--> <build> <resources> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources> </build> </project> -
编写mybatis的核心配置文件(创建一个mybatis-config.xml文件)：注意：需要改4处
<?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> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> </configuration> -
编写mybatis工具类
package com.lyh.utils; 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; //SqlSessionFactory--创建sqlSession public class MybatisUtils { //获取SqlSessionFactory对象 private static SqlSessionFactory sqlSessionFactory; static { try { //使用mybatis第一步，获取sqlSessionFactory对象 String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } //既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。 // SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。 // 你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。 public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(); } } -
编写代码
-
实体类
package com.lyh.entity; public class User { private int id; private String name; private String pwd; public User() { } public User(int id, String name, String pwd) { this.id = id; this.name = name; this.pwd = pwd; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", pwd='" + pwd + '\'' + '}'; } } -
dao接口（现在是mapper接口）
package com.lyh.dao; import com.lyh.entity.User; import java.util.List; public interface UserMapper { List<User> getUserList(); } -
接口实现类由原来的UserDaoImpl变成了UserMapper.xml文件。注意：需要改3处
<?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"> <!--namespace=需要绑定一个mapper(dao接口)接口，指定的mapper接口--> <mapper namespace="com.lyh.dao.UserMapper"> <!-- select查询语句这里的id对应着接口中方法的名字--> <!-- 这里就记住resultType和resultMap--> <select id="getUserList" resultType="com.lyh.entity.User" > select * from user ; </select> </mapper>
-
-
在mybatis-config.xml中配置一下写的UserMapper.xml配置（以后写一个(Dao)Mapper接口对应一个(Dao)Mapper.xml配置文件，而Mapper.xml配置文件需要在mybatis-config.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> <!-- 环境--> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <!-- //写一个mapper.xml配置，得在这里配置一个--> <mapper resource="com/lyh/dao/UserMapper.xml"/> </mappers> </configuration> -
测试
package com.lyh; import com.lyh.dao.UserMapper; import com.lyh.entity.User; import com.lyh.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.jupiter.api.Test; import java.util.List; public class app { @Test public void UserDaoTest(){ //第一步：获取sqlSession对象 SqlSession sqlSession = MybatisUtils.getSqlSession(); //获取mapper接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<User> userList = userMapper.getUserList(); for (User user: userList) { System.out.println(user); } //关闭sqlSession sqlSession.close(); } } -
注意错误：
org.apache.ibatis.binding.BindingException: Type interface com.lyh.dao.UserMapper is not known to the MapperRegistry. (com.lyh.dao.UserMapper这个接口没有在mybatis-config.xml中注册)解决办法，在mybatis-config.xml中注册一下UserMapper接口。
<mappers> <!-- //写一个mapper.xml配置，得在这里配置一个--> <mapper resource="com/lyh/dao/UserMapper.xml"/> </mappers>
3、注解版的mybatis更加简介
-
配置依赖和上面的依赖一样
-
配置mybatis-config.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> <!-- 环境--> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> </configuration> -
编写MybatisUtils工具类
package com.lyh.utils; 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; /** * SqlSessionFactory */ public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory; static { try { //获取mybatis获取SqlSessionFactory对象 String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(); } } -
编写实体类
package com.lyh.entity; public class User { private int id; private String name; private String pwd; public User() { } public User(int id, String name, String pwd) { this.id = id; this.name = name; this.pwd = pwd; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", pwd='" + pwd + '\'' + '}'; } } -
编写dao/Mapper接口
package com.lyh.dao; import com.lyh.entity.User; import org.apache.ibatis.annotations.Select; import java.util.List; public interface UserMapper { @Select("select * from user ;") List<User> getUserList(); } -
编写dao/Mapper接口对应的Mapper.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"> <!--namespace=需要绑定一个mapper(dao接口)接口，指定的mapper接口--> <mapper namespace="com.lyh.dao.UserMapper"></mapper> -
在mybatis-config.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> <!-- 环境--> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <!-- //写一个mapper.xml配置，得在这里配置一个--> <mapper resource="com/lyh/dao/UserMapper.xml"/> </mappers> </configuration> -
测试
package com.lyh; import com.lyh.dao.UserMapper; import com.lyh.entity.User; import com.lyh.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.jupiter.api.Test; import java.util.List; public class app { @Test public void UserDaoTest() { //第一步：获取sqlSession对象 SqlSession sqlSession = MybatisUtils.getSqlSession(); //获取mapper接口 //方式一：getMapper(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<User> userList = userMapper.getUserList(); for (User user : userList) { System.out.println(user); } //关闭sqlSession sqlSession.close(); } }
注解版和普通版区别：
-
：注解版：直接在接口中方法名上写一个查询（增删改查）注释，然后Mapper.xml文件中就不用写sql语句了。普通版：需要在mapper中写sql语句。
-
两者都需要在mybatis-config.xml文件中配置mapper.xml文件
4、CRUD
namespace=需要绑定一个mapper(dao接口)接口，指定的mapper接口
注意：黄色字体都是重点中的重点，要不要看随你
-
编写UserMapper接口
package com.lyh.dao; import com.lyh.entity.User; import org.apache.ibatis.annotations.Select; import java.util.List; public interface UserMapper { //获取全部用户 // @Select("select * from user ;") List<User> getUserList(); //根据id查询用户 User getUserById(Integer id); //添加用户 int addUser(User user); //修改用户 int updateUser(User user); //删除用户 int delUser(Integer id); } -
编写UserMapper.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"> <!--namespace=需要绑定一个mapper(dao接口)接口，指定的mapper接口--> <mapper namespace="com.lyh.dao.UserMapper"> <!-- select查询语句这里的id对应着接口中方法的名字--> <!-- 这里就记住resultType(SQl语句执行的返回值)和resultMap--> <!-- 这里就记住resultType对应的实体类名称--> <select id="getUserList" resultType="com.lyh.entity.User"> select * from user; </select> <!-- parameterType参数类型（常用int String Double char等）--> <select id="getUserById" parameterType="int" resultType="com.lyh.entity.User"> select * from user where id = #{id}; </select> <insert id="addUser" parameterType="com.lyh.entity.User"> insert into user (id,name,pwd) values (#{id},#{name},#{pwd}); </insert> <update id="updateUser" parameterType="com.lyh.entity.User"> update user set name =#{name},pwd=#{pwd} where id=#{id}; </update> <delete id="delUser" parameterType="com.lyh.entity.User"> delete from user where id=#{id}; </delete> </mapper> -
测试
package com.lyh; import com.lyh.dao.UserMapper; import com.lyh.entity.User; import com.lyh.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.jupiter.api.Test; import java.util.List; public class app { //查询所有用户 @Test public void UserDaoTest() { //第一步：获取sqlSession对象 SqlSession sqlSession = MybatisUtils.getSqlSession(); //获取mapper接口 //方式一：getMapper(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<User> userList = userMapper.getUserList(); for (User user : userList) { System.out.println(user); } //关闭sqlSession sqlSession.close(); } //根据ID查询用户 @Test public void getUserById(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); //获得接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); User userById = userMapper.getUserById(16); System.out.println(userById); //关闭sqlSession sqlSession.close(); } //添加用户 @Test public void addUser(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User("李永辉","456"); int i = mapper.addUser(user); if (i>0){ System.out.println("添加成功"); }else { System.out.println("添加失败"); } //提交事务 sqlSession.commit(); sqlSession.close(); } //修改用户信息 @Test public void updateUser(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int i = mapper.updateUser(new User(16, "cc", "sss")); if (i>0){ System.out.println("修改成功"); } sqlSession.commit(); sqlSession.close(); } //删除用户信息 @Test public void delUser(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int i = mapper.delUser(16); if (i>0){ System.out.println("删除成功"); } sqlSession.commit(); sqlSession.close(); } }
注意：增删改：都需要提交事务 sqlSession.commit();
5、万能Map
6、配置解析
核心配置文件
-
mybatis-config.xml
-
MyBatis 的配置文件包含了会深深影响 MyBatis 行为的设置和属性信息。 配置文档的顶层结构如下：
properties（属性） settings（设置） typeAliases（类型别名） typeHandlers（类型处理器） objectFactory（对象工厂） plugins（插件） environments（环境配置） environment（环境变量） transactionManager（事务管理器） dataSource（数据源） databaseIdProvider（数据库厂商标识） mappers（映射器）
-
环境变量（environments）
MyBatis 可以配置成适应多种环境；
注意：尽管可以配置多个环境，但每个 SqlSessionFactory 实例只能选择一种环境
-
默认使用的环境 ID（比如：default="development"）。
-
每个 environment 元素定义的环境 ID（比如：id="development"）。
-
事务管理器的配置（比如：type="JDBC"）。
-
数据源的配置（比如：type="POOLED"）。
默认环境和环境 ID 顾名思义。 环境可以随意命名，但务必保证默认的环境 ID 要匹配其中一个环境 ID。
-
-
Mybatis默认的事务管理器就是JDBC，连接池：POOLED
-
properties（属性）：这些属性可以在外部进行配置，并可以进行动态替换。你既可以在典型的 Java 属性文件中配置这些属性，也可以在 properties 元素的子元素中设置。
-
typeAliases（类型别名）
第一种方法：类型别名可为 Java 类型设置一个缩写名字。
<typeAliases> <!-- 为 Java 实体类设置一个缩写名字--> <typeAlias type="com.lyh.entity.User" alias="User" /> </typeAliases>第二种方法：也可以指定一个包名，MyBatis 会在包名下面搜索需要的java类
扫描实体类的包，它默认别名就是这个类的 类名，首字母小写
<typeAliases> <!-- 在实体类中首字母小写的非限定类名来作为它的别名--> <package name="com.lyh.entity"/> </typeAliases>注意：
-
在实体类比较少的时候，使用第一种方式
-
如果实体类十分多，建议使用第二种
-
第一种可以DIY别名，第二种这不行，如果非要改，需要在实体类上增加注解
@Alias("hello") public class User {}<?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"> <!--namespace=需要绑定一个mapper(dao接口)接口，指定的mapper接口--> <mapper namespace="com.lyh.Mapper.UserMapper"> <!-- select查询语句这里的id对应着接口中方法的名字--> <!-- 这里就记住resultType(SQl语句执行的返回值)和resultMap--> <!-- 这里就记住resultType对应的实体类名称--> <select id="getUserList" resultType="hello"> select * from user; </select> </mapper>
-
-
设置（settings）
这是 MyBatis 中极为重要的调整设置，它们会改变 MyBatis 的运行时行为。 下表描述了设置中各项设置的含义、默认值等。
一个配置完整的 settings 元素的示例如下：
<settings> <setting name="cacheEnabled" value="true"/> <setting name="lazyLoadingEnabled" value="true"/> <setting name="multipleResultSetsEnabled" value="true"/> <setting name="useColumnLabel" value="true"/> <setting name="useGeneratedKeys" value="false"/> <setting name="autoMappingBehavior" value="PARTIAL"/> <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/> <setting name="defaultExecutorType" value="SIMPLE"/> <setting name="defaultStatementTimeout" value="25"/> <setting name="defaultFetchSize" value="100"/> <setting name="safeRowBoundsEnabled" value="false"/> <setting name="mapUnderscoreToCamelCase" value="false"/> <setting name="localCacheScope" value="SESSION"/> <setting name="jdbcTypeForNull" value="OTHER"/> <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/> </settings>-
映射Mappers(mybatis-config.xml文件中的)
第一种：<!-- 使用相对于类路径的资源引用 -->
<mappers> <!-- //写一个mapper.xml配置，得在这里配置一个--> <mapper resource="com/lyh/Mapper/UserMapper.xml"/> </mappers> -
第二种：<!-- 使用映射器接口实现类的完全限定类名 -->
```xml
<mappers>
<!-- //写一个mapper.xml配置，得在这里配置一个-->
<!-- <mapper resource="com/lyh/Mapper/UserMapper.xml"/>-->
<mapper class="com.lyh.Mapper.UserMapper"></mapper>
</mappers>第三种：<!-- 将包内的映射器接口实现全部注册为映射器 -->
<mappers>
<!-- //写一个mapper.xml配置，得在这里配置一个-->
<!-- <mapper resource="com/lyh/Mapper/UserMapper.xml"/>-->
<!-- <mapper class="com.lyh.Mapper.UserMapper"></mapper>-->
<package name="com.lyh.Mapper"/>
</mappers>如果没有注册会出现的错误是：
Type interface com.lyh.Mapper.UserMapper is not known to the MapperRegistry.注意：
-
接口和它的Mapper配置文件必须同名
-
接口和他的Mapper配置文件必须在同一个包下？
7、解决属性名和字段名不一致的问题
-
resultMap：（如果实体类的属性和数据库中的不一样就需要用resultMap）
结果集映射
数据库中的字段名：
实体类中的字段名：
public class User { private int id; private String name; private String password;数据库中字段名和实体类中的属性不一样，这时就该用我们的resultMap；
-
编写我们的UserMapper.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.lyh.Mapper.UserMapper"> <!-- id对应着select中resultMap中的名字，type对应的实体类--> <resultMap id="userMap" type="com.lyh.entity.User"> <id column="id" property="id" /> <result column="name" property="name" /> <result column="pwd" property="password" /> </resultMap> <select id="getUserById" resultMap="userMap"> select * from user where id = #{id}; </select> </mapper>注意：select标签中resultMap的值等于resultMap标签的id属性，而resultMap标签中的type属性指的就是实体类，column就是数据库中的字段，property就是实体类的字段。（就相当于map集合，一个key对应一个value）
8、日志
-
Mybatis 通过使用内置的日志工厂提供日志功能。内置日志工厂将会把日志工作委托给下面的实现之一
-
SLF4J
-
LOG4J
-
LOG4J2
-
JDK_LOGGING
-
COMMONS_LOGGING
-
STDOUT_LOGGING （标准日志输出）
-
NO_LOGGING
-
9、LOG4J日志工厂
-
Log4j是Apache的一个开源项目，通过使用Log4j，我们可以控制日志信息输送的目的地是控制台、文件、GUI组件，
-
我们也可以控制每一条日志的输出格式；
-
通过定义每一条日志信息的级别，我们能够更加细致地控制日志的生成过程。
-
最令人感兴趣的就是，这些可以通过一个配置文件来灵活地进行配置，而不需要修改应用的代码。
-
导入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/logFile.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的类中，导入包apache的
-
日志对象，参数为当前类的class
static Logger logger = Logger.getLogger(UserTest.class);
-
10、Limit实现分页
-- 分页两个参数，第一个是从哪里开始，第二次参数每页显示几个
-- 每页显示两个，从第0个开始
select * from user limit 0,2;
-- 每页显示三个，从第0个开始
select * from user limit 3;-
使用Mybatis实现分页
-
接口
//分页 List<User> getUserByLimit(Map<String,Integer> map); -
Mapper.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.lyh.Mapper.UserMapper"> <!-- id对应着select中resultMap中的名字，type对应的实体类--> <resultMap id="userMap" type="com.lyh.entity.User"> <id column="id" property="id" /> <result column="name" property="name" /> <result column="pwd" property="password" /> </resultMap> <!-- 根据ID查询--> <select id="getUserById" resultMap="userMap"> select * from user where id = #{id}; </select> <!-- 分页--> <select id="getUserByLimit" parameterType="map" resultMap="userMap"> select * from user limit #{startIndex},#{pageSize} </select> </mapper> -
测试
@Test public void getUserByLimit(){ SqlSession sqlSession = MybatisUtils.getsqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); HashMap<String, Integer> map = new HashMap<>(); map.put("startIndex",0); map.put("pageSize",2); List<User> userByLimit = mapper.getUserByLimit(map); System.out.println(userByLimit); sqlSession.close(); }注意：map一个键对应一个值：这里的键对应的就是Mapper.xml中的startIndex，pageSize。值在你传的参数中。
-
11、分页插件
12、注解开发
别忘了在数据中创建user表
-
创建工具类Mybatisutils
package com.lyh.utils; 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; public class Mybatisutils { private static SqlSessionFactory sqlSessionFactory; static { try { String resource="mybatis-config.xml"; InputStream stream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream); } catch (IOException e) { e.printStackTrace(); } } //获得getSqlSession public static SqlSession getSqlSession(){ ////提交事务，在openSession中加载true，这可以自动提交事务 return sqlSessionFactory.openSession(true); } } -
编写mybatis-config.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> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> </configuration> -
创建实体类User
package com.lyh.entity; public class User { private Integer id; private String name; private String pwd; public User() { } public User(Integer id, String name, String pwd) { this.id = id; this.name = name; this.pwd = pwd; } 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 getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", pwd='" + pwd + '\'' + '}'; } } -
编写接口Mapper
package com.lyh.Mapper; import com.lyh.entity.User; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; import java.util.List; import java.util.Map; public interface UserMapper { //查询 @Select("select * from user;") List<User> getUserList(); //根据ID查询 @Select("select * from user where id=#{id};") List<User> getUserListById(@Param("id")Integer id); //添加 @Insert("insert into user values(#{id},#{name},#{pwd});") int addUser(User user); //修改 @Update("update user set name=#{name},pwd=#{pwd} where id=#{id};") int UpdateUser(User user); //删除 @Delete("delete from user where id=#{id};") int delUser(@Param("id")Integer id); //模糊查询 @Select("select * from user where name like #{name};") List<User> getUserListByName(String name); //分页 @Select("select * from user limit #{startIndex},#{pageSize};") List<User> getUserListLimit(Map<String,Integer> map); } -
在mybatis-config.xml中注册
<mappers> //找到我们的接口 <mapper class="com.lyh.Mapper.UserMapper"></mapper> </mappers> -
测试
package com.lyh.Mapper; import com.lyh.entity.User; import com.lyh.utils.Mybatisutils; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.HashMap; import java.util.List; import java.util.Map; public class UserMapperTest { //查询 @Test public void getUserList(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userList = mapper.getUserList(); for (User user:userList ) { System.out.println(user); } sqlSession.close(); } //添加 @Test public void addUser(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int 王子嗯 = mapper.addUser(new User(10, "王子嗯", "456")); if (王子嗯>0){ System.out.println("添加成功"); } sqlSession.commit(); sqlSession.close(); } //修改 @Test public void UpdateUser(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int i = mapper.UpdateUser(new User(1,"代雅鑫","123123")); if (i>0){ System.out.println("修改成功"); } sqlSession.commit(); sqlSession.close(); } //删除 @Test public void delUser(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int i = mapper.delUser(57); if (i>0){ System.out.println("删除成功"); } sqlSession.commit(); sqlSession.close(); } 根据ID查询 @Test public void getUserListById(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userListById = mapper.getUserListById(1); System.out.println(userListById); } //模糊查询 @Test public void getUserListByName(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userListByName = mapper.getUserListByName("%"+"永"+"%"); System.out.println(userListByName); } //分页这个不可用 @Test public void getUserListLimit(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); HashMap<String, Integer> map = new HashMap<>(); map.put("startIndex",0); map.put("pageSize",2); List<User> userListLimit = mapper.getUserListLimit(map); System.out.println(userListLimit); sqlSession.close(); } }本质：反射机制实现
底层：动态代理
-
关于@Param(""注解)
-
基本类型的参数或者String类型，需要加上
-
引用类型不需要加
-
如果只有一个基本类型的话，可以忽略，建议都加上
-
我们在SQL中引用的加上我们这@Param(""注解)设定的属性名
#{}和${}区别：#{}可以防止sql注入，而${}不能防止sql注入
-
13、Lombok
Project Lombok is a java library that automatically plugs into your editor and build tools, spicing up your java.
Never write another getter or equals method again, with one annotation your class has a fully featured builder, Automate your logging variables, and much more.不用写set和get方法
使用步骤：
-
在IDEa中安装Lombok插件
-
在项目中导入lombok的jar包（maven中导入依赖即可）
<!-- lombok依赖--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> </dependency> -
在实体类中加入注解即可
package com.lyh.entity; import lombok.*; @Data @AllArgsConstructor @NoArgsConstructor public class User { private Integer id; private String name; private String pwd; }
lombok中的注解：
-
@Data：放在实体类上面的注解，自动生成：无参构造、get、set、toString、hashcode、equals
-
@AllArgsConstructor:放在实体类上面，自动生成有参构造
-
@NoArgsConstructor：放在实体类上面，自动生成无参构造
-
@ToString：放在实体类上，自动生成toStriong方法
-
@Getter：放在实体类上面，自动生成get方法
-
@Getter：放在实体类上面，自动生成get方法
14、多对一处理（多个学生对应一个老师）
-
多个学生对应一个老师
-
对于学生而言，关联：多个学生对应一个老师（多对一）
-
对于老师而言，集合：一个老师，有很多学生（一对多）
SQL：
create table teacher(
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
insert into teacher(`id`,`name`) VALUES(1,'李老师');
create table `student`(
`id` int(10) NOT NULL,
`name` varchar(30) default null,
`tid` int(10) default NULL,
primary key (`id`),
key `fktid` (`tid`),
constraint `fktid` foreign key(`tid`) REFERENCES `teacher` (`id`)
)engine = innodb default charset=utf8;
insert into `student` VALUES('1','小明','1');
insert into `student` VALUES('2','小红','1');
insert into `student` VALUES('3','校长','1');
insert into `student` VALUES('4','小于','1');
insert into `student` VALUES('5','小王','1');环境搭建：
-
配置pom.xml文件，依赖
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> </dependency> -
编写MybatisUtils类
package com.lyh.utils; 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; public class Mybatisutils { private static SqlSessionFactory sqlSessionFactory; static { try { String resource="mybatis-config.xml"; InputStream stream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream); } catch (IOException e) { e.printStackTrace(); } } //获得getSqlSession public static SqlSession getSqlSession(){ //提交事务，在openSession中加载true，这可以自动提交事务 return sqlSessionFactory.openSession(true); } } -
编写配置mybatis-config.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> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <!-- <mapper resource="com/lyh/dao/TeacherMapper.xml"></mapper>--> <!-- <mapper resource="com/lyh/dao/StudentMapper.xml"></mapper>--> <mapper class="com.lyh.dao.TeacherMapper"></mapper> <mapper class="com.lyh.dao.StudentMapper"></mapper> </mappers> </configuration> -
编写实体
编写Teacher、Student实体类
package com.lyh.pojo; import lombok.Data; @Data public class Student { private int id; private String name; //学生需要关联一个老师 private Teacher teacher; }package com.lyh.pojo; import lombok.Data; @Data public class Teacher { private int id; private String name; } -
编写接口
-
编写StudentMapper接口
package com.lyh.dao; public interface StudentMapper { } -
编写TeacherMapper接口
package com.lyh.dao; import com.lyh.pojo.Teacher; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; public interface TeacherMapper { @Select("select * from teacher where id=#{tid}") Teacher getTeacherByid(@Param("tid") int id); }
-
-
编写xml文件
-
编写StudentMapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lyh.dao.StudentMapper"> </mapper> -
编写TeacherMapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lyh.dao.TeacherMapper"> </mapper>
-
-
在mybatis-config.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> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <!-- <mapper resource="com/lyh/dao/TeacherMapper.xml"></mapper>--> <!-- <mapper resource="com/lyh/dao/StudentMapper.xml"></mapper>--> <mapper class="com.lyh.dao.TeacherMapper"></mapper> <mapper class="com.lyh.dao.StudentMapper"></mapper> </mappers> </configuration> -
测试查询是否能够成功
package com.lyh; import com.lyh.dao.TeacherMapper; import com.lyh.pojo.Teacher; import com.lyh.utils.Mybatisutils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class app { @Test public void getTeacherByid(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacherByid = mapper.getTeacherByid(1); System.out.println(teacherByid); sqlSession.close(); } } -
编写StudentMapper接口
package com.lyh.dao; import com.lyh.pojo.Student; import java.util.List; public interface StudentMapper { public List<Student> getStudent(); public List<Student> getStudent2(); } -
编写StudentMapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lyh.dao.StudentMapper"> <select id="getStudent2" resultMap="StudentTeacher2"> select s.id sid,s.`name` sname,t.`name` tname from student s,teacher t where s.tid=t.id; </select> <resultMap id="StudentTeacher2" type="com.lyh.pojo.Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="com.lyh.pojo.Teacher"> <result property="id" column="id"></result> <result property="name" column="tname"/> </association> </resultMap> <!-- ============================================================--> <!-- 思路： 1.查询所有学生信息 2.根据查询出来的tid，寻找对应的老师 --> <resultMap id="studentTeacher" type="com.lyh.pojo.Student"> <!-- property:实体类中的属性 column:数据库中的属性 --> <result property="id" column="id"></result> <result property="name" column="name"></result> <!-- 复杂的属性，我们需要单独处理 对象：association 集合：collection--> <association property="teacher" column="tid" javaType="com.lyh.pojo.Teacher" select="getTeacherByid"/> </resultMap> <select id="getStudent" resultMap="studentTeacher"> select * from student </select> <select id="getTeacherByid" resultType="com.lyh.pojo.Teacher"> select * from teacher where id = #{tid} </select> </mapper> -
测试：
package com.lyh; import com.lyh.dao.StudentMapper; import com.lyh.dao.TeacherMapper; import com.lyh.pojo.Student; import com.lyh.pojo.Teacher; import com.lyh.utils.Mybatisutils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class app { @Test public void getTeacherByid(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> student = mapper.getStudent(); for (Student students: student ) { System.out.println(students); } sqlSession.close(); } @Test public void getStudent2(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> student2 = mapper.getStudent2(); String s = student2.toString(); System.out.println(s); sqlSession.close(); } }
15、一对多（一个老师对应多个学生）
环境搭建：
由上可搭建环境复制过来，修改dao层还有实体类
-
编写实体类Student、Teacher
package com.lyh.pojo; import lombok.Data; import java.util.List; @Data public class Teacher { private int id; private String name; //一个老师拥有多个学生 private List<Student> studentList; }package com.lyh.pojo; import lombok.Data; import java.util.List; @Data public class Teacher { private int id; private String name; //一个老师拥有多个学生 private List<Student> studentList; } -
编写TeacherMapper接口
package com.lyh.dao; import com.lyh.pojo.Teacher; import org.apache.ibatis.annotations.Param; import java.util.List; public interface TeacherMapper { //获取所有老师 // List<Teacher> getTeacher(); //获取指定老师下的所有学生及老师的信息(测试环境是否建成) Teacher getTeacher(@Param("tid") int id); Teacher getTeacher2(@Param("tid") int id); } -
编写TeacherMapper.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"> <!--namespace=需要绑定一个mapper(dao接口)接口，指定的mapper接口--> <mapper namespace="com.lyh.dao.TeacherMapper"> <!-- 按结果嵌套查询--> <!-- select->id是方法名--> <select id="getTeacher" resultMap="TeacherStudent"> select s.id sid, s.name sname, t.name tname, t.id tid from student s, teacher t where s.tid = t.id and t.id=#{tid} </select> <!-- type：对应你查的实体类--> <resultMap id="TeacherStudent" type="com.lyh.pojo.Teacher"> <!-- 这是com.lyh.pojo.Teacher这个类的属性名--> <result property="id" column="tid"></result> <result property="name" column="tname"></result> <!-- com.lyh.pojo.Teacher这个类的第三个属性是个集合， 然后的话就是查询ofType：com.lyh.pojo.Student Student这个实体类对应的属性和属性名 --> <collection property="studentList" ofType="com.lyh.pojo.Student"> <result property="id" column="sid"></result> <result property="name" column="sname"></result> <result property="tid" column="tid"></result> </collection> </resultMap> <!--============================================================================--> </mapper> -
测试：
package com.lyh; import com.lyh.dao.TeacherMapper; import com.lyh.pojo.Teacher; import com.lyh.utils.Mybatisutils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class app { @Test public void getTeacher(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); } }
小结：
-
关联 - association【多对一】
-
集合 - collection【一对多】
-
JavaType & ofType
-
JavaType：用来指定实体类中属性的类型
-
ofType：用来指定映射到List或者集合中的pojo类型，反省中的约束类型
注意：
-
保证SQL的可读性，尽量保证通俗易懂
-
注意一对多和多对一，属性名和字段的问题
-
如果问题编号排查错误，可以使用日志，建议使用Log4j
-
16、动态SQL
什么是动态SQL：动态SQL就是指根据不同的条件生成不同的SQL语句
利用动态SQL这一特性可以彻底摆脱这种痛苦
环境搭建：
-
在数据库中创建表
create table `blog`( `id` varchar(50) not null comment '博客id', `title` varchar(100) not null comment '博客标题', `author` varchar(30) not null comment '博客作者', `create_time` datetime not null comment '创建时间', `views` int(30) not null comment '浏览量' )engine=InnoDB default charset=utf8 -
导入依赖
<!-- lombok依赖--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> </dependency> -
编写Mybatisutils工具类
package com.lyh.utils; 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; public class Mybatisutils { private static SqlSessionFactory sqlSessionFactory; static { try { String resource="mybatis-config.xml"; InputStream stream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream); } catch (IOException e) { e.printStackTrace(); } } //获得getSqlSession public static SqlSession getSqlSession(){ //提交事务，在openSession中加载true，这可以自动提交事务 return sqlSessionFactory.openSession(true); } } -
编写mybatis-config.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> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/lyh/dao/TeacherMapper.xml"></mapper> <mapper resource="com/lyh/dao/StudentMapper.xml"></mapper> <!-- <mapper class="com.lyh.dao.TeacherMapper"></mapper>--> <!-- <mapper class="com.lyh.dao.StudentMapper"></mapper>--> </mappers> </configuration> -
编写pojo实体类
package com.lyh.pojo; import lombok.Data; import java.util.Date; @Data public class Blog { private String id; private String title; private String author; private Date createTime;//属性名和字段名不一致 private int views; } -
编写Mapper接口
package com.lyh.dao; import com.lyh.pojo.Blog; import java.util.List; import java.util.Map; public interface BlogMapper { //插入数据 int addBlog(Blog blog); //查询博客 List<Blog> blogList(Map map); } -
编写Mapper.xml文件
<!-- where这个判断就是拼接sql的时候会自动去除and和or-->
-
if判断（）
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lyh.dao.BlogMapper"> <insert id="addBlog" parameterType="com.lyh.pojo.Blog"> insert into blog values(#{id},#{title},#{author},#{createTime},#{views}) </insert> <select id="blogList" parameterType="map" resultType="com.lyh.pojo.Blog"> select * from blog where 1=1 <if test="title != null"> and title =#{title} </if> <if test="author !=null"> and author =#{author} </if> </select> </mapper>测试：
@Test public void blogList(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap<>(); map.put("title","Mybatis如此简单"); map.put("author","李永辉"); List<Blog> blogList = mapper.blogList(map); for (int i = 0; i < blogList.size(); i++) { System.out.println(blogList); } sqlSession.close(); }注意：上面执行的sql语句是：select * from blog WHERE title = ? and author = ?
-
choose(when，otherwise)：choose相当于Java中的switch，只能选择其中的一个
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lyh.dao.BlogMapper"> <!-- where这个判断就是拼接sql的时候会自动去除and和or--> <select id="blogListByName" parameterType="map" resultType="com.lyh.pojo.Blog"> select * from blog <where> <choose> <when test="title !=null"> title = #{title} </when> <when test="author != null"> and = #{author} </when> <otherwise> and views = #{views} </otherwise> </choose> </where> </select> </mapper>测试：
@Test public void blogList(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap<>(); map.put("title","Mybatis如此简单"); map.put("author","李永辉"); List<Blog> blogList = mapper.blogListByName(map); for (int i = 0; i < blogList.size(); i++) { System.out.println(blogList); } sqlSession.close(); }注意：上面执行的sql语句是： select * from blog WHERE title = ?
-
trim(where,set)：和之前的SQL差不多，
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lyh.dao.BlogMapper"> <update id="updateBlog" parameterType="map"> update blog <set> <if test="title != null"> title = #{title}, </if> <if test="author != null"> author = #{author}, </if> </set> where id = #{id} </update> </mapper>测试：
@Test public void updateBlog(){ SqlSession sqlSession = Mybatisutils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap<>(); map.put("title","Mybatis如此简单"); map.put("author","李永辉"); // map.put("createTime",new Date()); // map.put("views",9999); map.put("id","497697627"); int i = mapper.updateBlog(map); if (i>0){ System.out.println("修改成功"); } sqlSession.close(); }注意：上面测试执行SQL语句：update blog SET title = ?, author = ? where id = ?
-
-
foreach：动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）
它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。
-
接口
//查询1-2-3号记录的博客 List<Blog> queryBlogForeach(Map map); -
xml文件
<!-- select * from blog where (id=854139543 or id=497697627 or id=1997870334); --> <select id="queryBlogForeach" parameterType="map" resultType="com.lyh.pojo.Blog"> select * from blog <where> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> id = #{id} </foreach> </where> </select> -
测试
@Test public void queryBlogForeach() { SqlSession sqlSession = Mybatisutils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap<>(); ArrayList<Integer> ids = new ArrayList<>(); ids.add(854139543); ids.add(497697627); ids.add(1997870334); map.put("ids", ids); List<Blog> blogs = mapper.queryBlogForeach(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }
-
SQL片段
有时候，我们可能将一些公共的部分抽取出来，方便复用
-
使用SQL标签取公共部分
<sql id="if-title-author"> <if test="title != null"> title = #{title} </if> <if test="author !=null"> and author = #{author} </if> </sql> -
在需要使用的地方使用include标签引用即可
<select id="blogList" parameterType="map" resultType="com.lyh.pojo.Blog"> select * from blog <where> <include refid="if-title-author"></include> </where> </select>测试：
@Test public void blogListByName() { SqlSession sqlSession = Mybatisutils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap<>(); map.put("title", "Mybatis如此简单"); map.put("author", "李永辉"); List<Blog> blogList = mapper.blogListByName(map); for (int i = 0; i < blogList.size(); i++) { System.out.println(blogList); } sqlSession.close(); }注意事项：
-
最好基于单表来定义SQL片段！
-