目录
目录
1.6mybatis核心配置文件mybatis-config.xml
1.6mybatis核心配置文件mybatis-config.xml
一、<if>标签
1.1if语法结构
<if test="判断条件">
SQL语句
</if>
1.2数据库端建表
具体内容:
use mybatis;
# 创建一个名称为t_customer的表
CREATE TABLE t_customer (
id int(32) PRIMARY KEY AUTO_INCREMENT,
username varchar(50),
jobs varchar(50),
phone varchar(16)
);
# 插入3条数据
INSERT INTO t_customer VALUES ('1', 'joy', 'teacher', '13733333333');
INSERT INTO t_customer VALUES ('2', 'jack', 'teacher', '13522222222');
INSERT INTO t_customer VALUES ('3', 'tom', 'worker', '15111111111');
1.3创建项目
1.4导入依赖(pom.xml)
具体内容:
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
</dependencies>
1.5创建package和文件夹
1.6mybatis核心配置文件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>
<!-- 环境配置 -->
<!-- 加载类路径下的属性文件 -->
<properties resource="db.properties"/>
<typeAliases>
<package name="org.example.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<!-- 数据库连接相关配置 ,db.properties文件中的内容-->
<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>
<!-- mapping文件路径配置 -->
<mappers>
<package name="org.example.mapper"/>
</mappers>
</configuration>
此处需要修改
此处需要修改
1.7mybatis属性文件jdbc.properties
具体内容:
mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
mysql.username=root
mysql.password=root
1.8log4j.xml文件
具体内容:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n"/>
</layout>
</appender>
<logger name="java.sql">
<level value="debug"/>
</logger>
<logger name="org.apache.ibatis">
<level value="info"/>
</logger>
<root>
<level value="debug"/>
<appender-ref ref="STDOUT"/>
</root>
</log4j:configuration>
1.9创建实体类
具体内容:
package org.example.pojo;
import lombok.*;
@Setter
@Getter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Customer {
private Integer id;
private String username;
private String jobs;
private String phone;
}
1.10mybatis接口类
具体内容:
package org.example.mapper;
/**
* ClassName: CustomerMapper
* Package: com.biem.mapper
* Description:
*
* @Create 2023/4/5 22:19
* @Version 1.0
*/
public interface CustomerMapper {
}
此处namespac要与自己的对应
1.11用户配置文件
具体内容:
<?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="org.example.mapper.CustomerMapper">
<!-- namespace要和mapper接口的全类名保持一致,例如com.biem.mybatis.mapper.xxxMapper -->
<!-- sql语句要和接口的方法名保持一致 -->
</mapper>
其中mapper namespace="com.example.mapper.CustomerMappe需要根据自己的修改
1.12mybatis工具类
具体内容:
package org.example.util;
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;
/**
* ClassName: MybatisUtil
* Package: com.biem.utils
* Description:
*
* @Create 2023/4/5 22:23
* @Version 1.0
*/
public class MybatisUtil {
//利用static(静态)属于类不属于对象,且全局唯一
private static SqlSessionFactory sqlSessionFactory = null;
//利用静态块在初始化类时实例化sqlSessionFactory
static {
InputStream is= null;
try {
is = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
throw new ExceptionInInitializerError(e);
}
}
/**
* openSession 创建一个新的SqlSession对象
* @return SqlSession对象
*/
public static SqlSession openSession(boolean autoCommit){
return sqlSessionFactory.openSession(autoCommit);
}
public static SqlSession openSession(){
return sqlSessionFactory.openSession();
}
/**
* 释放一个有效的SqlSession对象
* @param session 准备释放SqlSession对象
*/
public static void closeSession(SqlSession session){
if(session != null){
session.close();
}
}
}
1.13 If标签功能测试
org.example.mapper.CustomerMapper.class中添加
public List<Customer> findCustomerByNameAndJobs(Customer customer);
org/example/mapper/CustomerMapper.xml中添加
<select id="findCustomerByNameAndJobs" parameterType="customer" resultType="customer">
select * from t_customer where 1=1
<if test="username !=null and username != ''">
and username like concat('%', #{username}, '%')
</if>
<if test="jobs !=null and jobs != ''">
and jobs=#{jobs}
</if>
</select>
在src/test/java中创建类org.example.test.TestCustomer.java
具体内容:
package org.example.test;
import org.example.mapper.CustomerMapper;
import org.example.pojo.Customer;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.example.util.MybatisUtil;
import java.util.List;
public class CustomTest {
/**
* ClassName: TestCustomer
* Package: com.biem.test
* Description:
*
* @Create 2023/4/5 22:32
* @Version 1.0
*/
@Test
public void testFindAll(){
// 通过工具类获取SqlSession对象
SqlSession session = MybatisUtil.openSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.findCustomerByNameAndJobs(customer);
System.out.println("customers = " + customers);
// 关闭SqlSession
session.close();
}
@Test
public void testFindCustomerByNameAndJobs(){
// 通过工具类获取SqlSession对象
SqlSession session = MybatisUtil.openSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setUsername("jack");
customer.setJobs("teacher");
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.findCustomerByNameAndJobs(customer);
System.out.println("customers = " + customers);
// 关闭SqlSession
session.close();
}
}
运行结果:
choose-when-otherwise标签
CustomerMapper.class中添加
public List<Customer> findCustomerByCondition(Customer customer);
在CustomerMapper.xml中添加
<!-- public List<Customer> findCustomerByCondition(Customer customer);-->
<select id="findCustomerByCondition" parameterType="customer" resultType="customer">
select * from t_customer where 1=1
<!--条件判断-->
<choose>
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<when test="jobs!=null and jobs!=''">
and jobs=#{jobs}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</select>
test中添加
public void testFindCustomerByName(){
// 通过工具类获取SqlSession对象
SqlSession session = MybatisUtil.openSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setUsername("jack");
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.findCustomerByCondition(customer);
System.out.println("customers = " + customers);
// 关闭SqlSession
session.close();
}
@Test
public void testFindCustomerByJobs(){
// 通过工具类获取SqlSession对象
SqlSession session = MybatisUtil.openSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setJobs("teacher");
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.findCustomerByCondition(customer);
System.out.println("customers = " + customers);
// 关闭SqlSession
session.close();
}
@Test
public void testFindCustomerByPhone(){
// 通过工具类获取SqlSession对象
SqlSession session = MybatisUtil.openSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setPhone("1");
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.findCustomerByCondition(customer);
System.out.println("customers = " + customers);
// 关闭SqlSession
session.close();
}
where标签功能
CustomerMapper.class中添加
public List<Customer> findCustomerByIf(Customer customer);
public List<Customer> findCustomerByWhere(Customer customer);
CustomerMapper.xml中添加
<!-- public List<Customer> findCustomerByIf(Customer customer);-->
<select id="findCustomerByIf" parameterType="customer" resultType="customer">
select * from t_customer where
<if test="username !=null and username != ''">
and username like concat('%', #{username}, '%')
</if>
<if test="jobs !=null and jobs != ''">
and jobs=#{jobs}
</if>
</select>
<!-- public List<Customer> findCustomerByWhere(Customer customer);-->
<select id="findCustomerByWhere" parameterType="customer" resultType="customer">
select * from t_customer
<where>
<if test="username !=null and username != ''">
and username like concat('%', #{username}, '%')
</if>
<if test="jobs !=null and jobs != ''">
and jobs=#{jobs}
</if>
</where>
test中添加
public void testFindCustomerByIf(){
// 通过工具类获取SqlSession对象
SqlSession session = MybatisUtil.openSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setJobs("teacher");
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.findCustomerByIf(customer);
System.out.println("customers = " + customers);
// 关闭SqlSession
session.close();
}
@Test
public void testFindCustomerByWhere(){
// 通过工具类获取SqlSession对象
SqlSession session = MybatisUtil.openSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setJobs("teacher");
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.findCustomerByWhere(customer);
System.out.println("customers = " + customers);
// 关闭SqlSession
session.close();
}
<trim>标签
CustomerMapper.class中添加
public List<Customer> findCustomerByTrim(Customer customer);
CustomerMapper.xml中添加
<select id="findCustomerByTrim" parameterType="customer" resultType="customer">
select * from t_customer
<trim prefix="where" prefixOverrides="and">
<if test="username !=null and username != ''">
and username like concat('%', #{username}, '%')
</if>
<if test="jobs !=null and jobs != ''">
and jobs=#{jobs}
</if>
</trim>
</select>
test文件中添加
@Test
public void testFindCustomerByTrim(){
// 通过工具类获取SqlSession对象
SqlSession session = MybatisUtil.openSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setJobs("teacher");
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.findCustomerByTrim(customer);
System.out.println("customers = " + customers);
// 关闭SqlSession
session.close();
}