mybatis
目录
一、查询
1.1结果映射
开启驼峰映射:如果字段名与属性名符合驼峰命名规则,mybatis会自动通过驼峰命名规则映射。
字段起别名:在SQL语句中,对不一样的列名起别名,别名和实体类属性名一样。
@Results @Result:通过 @Results及@Result 进行手动结果映射。
1.2多条件查询
@Param 标注在方法参数的前面,用于声明参数在#{}中的名字
1.3模糊查询
${}性能低,不安全,存在SQL注入问题:
#{}推荐:
二、XML
书写规范
XML文件的名称与Mapper接口名称一致,并且放置在相同包下(同包同名)。
XML文件的namespace属性为Mapper接口全限定名一致。
XML文件中sql语句的id与Mapper 接口中的方法名一致。
XML文件中sql语句的返回类型与Mapper 接口中的方法返回类型一致。
三、动态SQL
<if>
用于判断条件是否成立,如果条件为true,则拼接SQL
<where>
where 元素只会在子元素有内容的情况下才插入where子句
而且会自动去除子句的开头的AND 或OR
<set>
动态地在行首插入SET关键字,并会删掉额外的逗号(用在update语句中)
<foreach >
用来批量处理的 比如批量删除拼接 in后面的值
<sql>
定义一个sql片段 就是提取公共的sql
<include>
引入sql片段
四、配置文件
4.1settings标签
控制一些全局配置项的开闭
4.2mappers标签
加载Mapper接口位置
4.3environments标签
Druid(德鲁伊): 阿里巴巴提供的数据库连接池技术,国内使用率很高,提供了完善的监控机制
HikariCP: 日本人开发的连接池技术,号称性能之王,速度最快,SpringBoot2.0默认使用此连接池
五、案例
5.1数据表
5.2实现类
代码如下(示例):
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.time.LocalDate;
import java.time.LocalDateTime;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
private Integer id;
private String username;
private String password;
private String name;
private Short gender;
private String image;
private Short job;
//注意:这四个属性跟数据表中的字段不一致
private LocalDate ed;
private Integer deptId;
private LocalDateTime createTime;
private LocalDateTime updateTime;
}
5.3mapper实现
代码如下:
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.time.LocalDate;
import java.util.Date;
import java.util.List;
public interface EmpMapper {
@Select("select * from emp")
public List<Emp> findAll();
@Select("select * from emp")
public List<Emp> findAll1();
@Select("select id,username,password,name,gender,image,job,entrydate ed,dept_id,create_time,update_time from emp")
public List<Emp> findAll2();
@Select("select * from emp")
@Results({
@Result(column = "entrydate",property = "ed")
})
public List<Emp> findAll3();
@Select("select * from emp where name =#{name} and gender = #{gender} and entrydate between #{begin} and #{end} ")
@Results({
@Result(column = "entrydate",property = "ed")
})
public List<Emp> findByCondition(@Param("name") String name,@Param("gender") Integer gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);
@Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and entrydate between #{begin} and #{end} ")
@Results({
@Result(column = "entrydate",property = "ed")
})
public List<Emp> findByCondition2(@Param("name") String name,@Param("gender") Integer gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);
public Emp findById(Integer id);
List<Emp> findByCondition3(@Param("name") String name,@Param("gender") Short gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);
void update(Emp emp);
void deleteByIds(@Param("ids") List<Integer> ids);
}
5.4工具类实现
代码如下:
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.InputStream;
public class MybatisUtil {
private static SqlSessionFactory sqlSessionFactory = null;
//保证SqlSessionFactory仅仅创建一次
static {
try {
//读取配置文件
InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建SqlSessionFactory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取sqlSession
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
//提交事务 关闭sqlSession
public static void close(SqlSession sqlSession) {
if (sqlSession != null) {
//提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
}
}
5.5XML动态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="com.itheima.mapper.EmpMapper">
<sql id="mySql">
select * from emp
</sql>
<resultMap id="MyMap" type="com.itheima.domain.Emp">
<result column="entrydate" property="ed"/>
</resultMap>
<select id="findById" resultMap="MyMap">
<include refid="mySql"/>
where id = #{id}
</select>
<select id="findByCondition3" resultType="com.itheima.domain.Emp">
<include refid="mySql"/>
<where>
<if test="name != null and name !=''">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
</select>
<update id="update">
update emp
<set>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="password != null and password != ''">
password = #{password},
</if>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="image != null and image != ''">
image = #{image},
</if>
<if test="job != null">
job = #{job},
</if>
<if test="ed != null">
entrydate = #{ed},
</if>
<if test="deptId != null">
dept_id = #{deptId},
</if>
<if test="createTime != null">
create_time = #{createTime},
</if>
<if test="updateTime != null">
update_time = #{updateTime},
</if>
</set>
where id = #{id}
</update>
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
</mapper>
5.6XML配置实现
代码如下:
<?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="mapUnderscoreToCamelCase" value="true"/>
<!--在控制台输出发送的sql日志-->
<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.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--声明含有sql的接口所在包-->
<package name="com.itheima.mapper"/>
</mappers>
</configuration>
5.7测试实现
代码如下:
package com.itheima.test;
import com.itheima.domain.Emp;
import com.itheima.mapper.EmpMapper;
import com.itheima.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;
public class EmpMapperTest {
// 测试查询所有
@Test
public void testFindAll(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> empList = empMapper.findAll();
for (Emp emp : empList) {
System.out.println(emp);
}
MybatisUtil.close(sqlSession);
}
// 测试结果集映射开启驼峰命名规则
@Test
public void testFindAll1(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> empList = empMapper.findAll1();
for (Emp emp : empList) {
System.out.println(emp);
}
MybatisUtil.close(sqlSession);
}
// 测试结果集映射起别名
@Test
public void testFindAll2(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> empList = empMapper.findAll2();
for (Emp emp : empList) {
System.out.println(emp);
}
MybatisUtil.close(sqlSession);
}
// 测试结果集映射手动结果映射@Results @Result
@Test
public void testFindAll3(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> empList = empMapper.findAll3();
for (Emp emp : empList) {
System.out.println(emp);
}
MybatisUtil.close(sqlSession);
}
// 测试条件查询
@Test
public void testFindCondition(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> empList = empMapper.findByCondition("张三丰",1, LocalDate.of(2000,1,1),LocalDate.of(2020,1,1));
for (Emp emp : empList) {
System.out.println(emp);
}
MybatisUtil.close(sqlSession);
}
// 测试模糊查询
@Test
public void testFindCondition2(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> empList = empMapper.findByCondition2("张",1, LocalDate.of(2000,1,1),LocalDate.of(2020,1,1));
for (Emp emp : empList) {
System.out.println(emp);
}
MybatisUtil.close(sqlSession);
}
// 测试根据id查询
@Test
public void testFindById(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
Emp emp1 = empMapper.findById(4);
System.out.println(emp1);
MybatisUtil.close(sqlSession);
}
//条件查询
@Test
public void testFindByCondition() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
//List<Emp> empList = empMapper.findByCondition3("张", (short) 1, LocalDate.of(2002, 01, 01), LocalDate.of(2023, 12, 31));
//List<Emp> empList = empMapper.findByCondition3("张", (short) 1, null, null);
List<Emp> empList = empMapper.findByCondition3("", (short) 1, null, null);
empList.forEach(e -> System.out.println(e));//lambda方式打印
MybatisUtil.close(sqlSession);
}
//更新
@Test
public void testUpdate() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setId(2);
emp.setUsername("haha2");
emp.setName("sdnajn");
emp.setGender( (short) 1);
emp.setImage("haha.jpg");
emp.setJob((short) 2);
emp.setDeptId(1);
emp.setCreateTime(LocalDateTime.of(2023, 1, 1, 1, 1,1));
emp.setUpdateTime(LocalDateTime.now());
empMapper.update(emp);
MybatisUtil.close(sqlSession);
}
//批量删除
@Test
public void deleteByIds() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
empMapper.deleteByIds(Arrays.asList(13, 14, 15));
MybatisUtil.close(sqlSession);
}
}
5.8pom.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">
<modelVersion>4.0.0</modelVersion>
<groupId>com.itheima</groupId>
<artifactId>day04-01-mybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>17</maven.compiler.source>
<maven.compiler.target>17</maven.compiler.target>
</properties>
<dependencies>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<!--junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.26</version>
</dependency>
</dependencies>
</project>
总结
以上就是今天学习的内容。
标签:Web,day04,学习,EmpMapper,sqlSession,emp,empMapper,import,public From: https://blog.csdn.net/filthy555/article/details/140406602