本文章为黑马javaweb课程学习笔记 项目结构如下
- 首先进行数据准备，准备emp表以及对应的实体类
- emp表
- Emp实体类
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 entrydate;
private Integer deptId;
private LocalDateTime createTime;
private LocalDateTime updateTime;
}
接下来是mapper包下的EmpMapper接口
package mybatistest.mapper;
import mybatistest.pojo.Account;
import mybatistest.pojo.Emp;
import org.apache.ibatis.annotations.*;
import java.time.LocalDate;
import java.util.List;
@Mapper
//可以直接生成实体类并将其放入Bean之中
public interface EmpMapper {
@Select(value = "select * from test.account;")
public List<Account> selectAccount();
@Select("select * from emp;")
public List<Emp> selectEmp();
//由mybatis提供#{id}，动态获取id的值
//当只传递进一个变量时{}里面可以随便写名称，但是当存在多个变量时，则变量名必须对应
//#{}是采取预编译方法拼接sql语句，而${}是直接拼接字符串
@Delete("delete from emp where id = #{id};")
public int deleteEmp(Integer id);
// @Insert("insert into emp (id, username, name, gender, image, job, entrydate, dept_id, create_time, update_time) values " +
// "(#{id},#{username},#{name},#{},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})"),id自增，类之中未定义id，使用报错
@Options(keyProperty = "id",useGeneratedKeys = true)
//使用该注解意味着会拿到生成后的主键，keyProperty为将生成的主键封装到对象的id之中，useGeneratedKeys为获取生成的主键
@Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)" +
" values (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")
public int insertEmp(Emp emp);
@Update("update emp set username=#{username},name=#{name},gender=#{gender},image=#{image},job=#{job},update_time=#{updateTime},entrydate=#{entrydate} where id = #{id};")
public void update(Emp emp);
//@Select无法自动进行映射，无法经将dept_id映射为deptId
// @Select(value = "select * from emp where id = #{id};")
// public Emp selectById(Integer id);
//解决方案1，给查询结果起别名
// @Select("select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime, update_time updateTime from emp where id=#{id};")
// public Emp selectById(Integer id);
//解决方案2，采取@Results,@Result分别进行映射
// @Results({
// @Result(column = "dept_id" ,property = "deptId"),
// @Result(column = "create_time",property = "createTime"),
// @Result(column = "update_time",property = "updateTime")
// })
//解决方案3，开启驼峰命名的自动映射
//在配置文件当中#开启mybatis的驼峰命名自动映射开关 a_column ------> aCloumn
//#mybatis.configuration.map-underscore-to-camel-case=true
@Select(value = "select * from emp where id = #{id};")
public Emp selectById(Integer id);
//条件查询
// @Select("select * from emp where name like '%张%' and entrydate between '2000-01-01' and '2020-01-01' order by update_time desc ;")
// public List<Emp> selectIf(String name, LocalDate begin, LocalDate end);
//该方法会报错，原因是字符串内部不可以使用#
// @Select("select * from emp where name like '%#{name}%' and entrydate between '2000-01-01' and '2020-01-01' order by update_time desc ;")
// public List<Emp> selectIf(String name, LocalDate begin, LocalDate end);
//可以使用$方法进行直接的字符串拼接解决上述原因，但是该方法无法进行预编译，无法防止sql注入
// @Select("select * from emp where name like '%${name}%' and entrydate between '2000-01-01' and '2020-01-01' order by update_time desc ;")
// public List<Emp> selectIf(String name, LocalDate begin, LocalDate end);
//采取concat能完美解决上述问题
// @Select("select * from emp where name like concat('%','张','%') and entrydate between #{begin} and #{end} order by update_time desc ;")
//以下三个方法采取xml文件映射
public List<Emp> selectIf(String name, short gender, LocalDate begin, LocalDate end);
public void updateXml(Emp emp);
public void deletByIds(List<Integer> ids);
}
简单功能建议使用注解开发，复杂的功能建议使用xml文件开发，尤其是涉及到多个条件筛选时，可以使用where，set，if标签进行搭配来进行动态SQL
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--命名空间必须与mapper接口名一致且要放在同名包之下-->
<mapper namespace="mybatistest.mapper.EmpMapper">
<!-- 为了提高代码的可重用性，可以将共同的sql语句使用sql标签进行抽取-->
<!-- 在使用时使用include标签进行使用-->
<sql id="commonSelect">
select * from emp
</sql>
<update id="updateXml">
update emp
<!-- set标签和where一样都具有去除多余字段功能，where去除多余and和or，set去除多余,-->
<set>
<if test="username != null">
username=#{username},
</if>
<if test="name != null">
name=#{name},
</if>
<if test="gender != null">
gender=#{gender},
</if>
<if test="image != null">
image=#{image},
</if>
<if test="job != null">
job=#{job},
</if>
<if test="updateTime != null">
update_time=#{updateTime},
</if>
<if test="entrydate != null">
entrydate=#{entrydate}
</if>
</set>
where id = #{id};
</update>
<delete id="deletByIds">
delete from emp where id in
<!-- foreach标签主要用于批量操作之中-->
<!-- collection为遍历的集合-->
<!-- item为元素-->
<!-- separator为分隔符-->
<!-- open为拼接开始符号-->
<!-- close为拼接结束符号-->
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!-- id值必须要与方法名一致-->
<!-- 返回类型为单条记录返回类型，比如List<Emp>单条记录返回类型为Emp-->
<!-- 写完了不要忘记把原本接口注释删掉-->
<select id="selectIf" resultType="mybatistest.pojo.Emp">
<!-- 动态sql案例-->
<include refid="commonSelect"></include>
<!-- 如果不使用where标签而单纯的使用if标签，最终的sql语句可能会出现没有name标签但是存在and gender的情况，最终报错-->
<!-- where标签会自动的去除and或者or的重复-->
<where>
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
or gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc ;
</select>
</mapper>
所有测试程序如下
package mybatistest;
import mybatistest.mapper.EmpMapper;
import mybatistest.pojo.Account;
import mybatistest.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;
@SpringBootTest
class MybatisTestApplicationTests {
@Autowired
private EmpMapper empMapper;
@Test
public void testDelete(){
if(empMapper.deleteEmp(15)==1){
System.out.println("yes");
}else{
System.out.println("no");
}
}
@Test
public void testSelect(){
List<Account> empList = empMapper.selectAccount();
System.out.println(empList);
}
@Test
public void testAccoun(){
List<Emp> empList = empMapper.selectEmp();
System.out.println(empList);
}
@Test
public void testInsert(){
Emp emp = new Emp();
emp.setUsername("Tom5");
emp.setName("汤姆3");
emp.setImage("1.jpg");
emp.setGender((short)1);
emp.setJob((short)1);
emp.setEntrydate(LocalDate.of(2000,1,1));
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
if(empMapper.insertEmp(emp)==1){
//未使用@Options，无法获取主键，故emp.getId()为null
System.out.println("insert ok"+emp.getId());
}
}
@Test
public void updateEmp(){
Emp emp = new Emp();
emp.setId(18);
emp.setUsername("Tom3");
emp.setName("汤姆3");
emp.setImage("1.jpg");
emp.setGender((short)1);
emp.setJob((short)1);
emp.setEntrydate(LocalDate.of(2000,1,1));
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
empMapper.update(emp);
}
@Test
public void testUapateXml(){
Emp emp = new Emp();
emp.setId(22);
emp.setUsername("Tom3asad");
empMapper.updateXml(emp);
}
@Test
public void testSelectByid(){
Emp emp = new Emp();
emp = empMapper.selectById(2);
System.out.println(emp);
}
@Test
public void testSelectIf(){
String name = "张";
short gender = 1;
LocalDate begin = LocalDate.of(2000,1,1);
LocalDate end = LocalDate.of(2020,1,1);
List<Emp> empList = empMapper.selectIf(name,gender,begin,end);
System.out.println(empList);
}
@Test
public void testDeletByIds(){
List<Integer> ids = Arrays.asList(18,19,20);
empMapper.deletByIds(ids);
}
}
还有最后的配置文件，其中包括了如何开启日志以及开启驼峰命名的自动映射
#配置数据库连接信息
#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://localhost:3306/example
#连接数据库的用户名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=
#配置mybatis的日志, 指定输出到控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
#开启mybatis的驼峰命名自动映射开关 a_column ------> aCloumn
mybatis.configuration.map-underscore-to-camel-case=true
