Mybatis 开发使用总结2023
Mybatis
一。入门
1.mybatis:是一款优秀的持久化框架,用于简化JDBC的开发。
2.JDBC
3.数据库连接池
4.lombok
二。mybatis的基础增删改查操作
1.驱动:com.mysql.cj.jdbc.Driver
2.url:jdbc:mysql://localhost:3306/mybatis
3.user:root
4.password:123
三。mybatis动态sql
1.创建springboot工程,数据库表user,实体类User;
2.引入mybatis的相关依赖,配置mybatis数据库连接信息;application.properties
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
3.编写sql语句(@Mapper注解/xml);
@Mapper //该接口是mybatis的接口 在运行时,会自动生产该接口的实现类对象,并且该对象交给IOC容器管理
4.测试类:
@SpringBootTest //springboot 整合单元测试的注解
class SpringbootMybatisQuickstartApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
public void testListUser(){
List<User> userList= userMapper.list();
userList.stream().forEach(user->{
System.out.println(user);
});
}
}
- 配置sql 提示:idea-->database + 右键--》show context actions-->inject language or refercence-->Mysql
6.JDBC:java database connectivity 使用java语言操作关系型数据库的一套API.
sun公司定义的一套操作所有关系型数据库的规范接口;各数据库厂商去实现这套接口,提供数据库驱动jar包;
操作方法:
1.注册驱动;
2.建立连接对象;
3.获取执行sql的对象,执行sql;
4.返回结果对象ResultSet;
5.封装结果数据;
6.释放资源。
JDBC 开发比较繁琐,重复的代码,所有有mybatis框架;
7.数据库连接池
datasouce(drui阿里德鲁伊),hikari追光者默认的连接池(springboot):
好处:1.资源重用,2.提升系统响应速度,3.避免数据库连接遗漏;
起步依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.11</version>
<scope>test</scope>
</dependency>
四。lombok是一个实用的java类库,通过注解自动生成构造器 ,getter/setter,equals,toString等方法,并可以自动生成日志变量,简化java开发,提高效率。
先添加依赖,后使用:
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>test</scope>
</dependency>
提供的注解:
@Getter/Setter
@toString
@EqualsAndHashCode
@Data (all)
@NoArgConstructor
@AllArgConstructor
五。mybatis 基础操作:增删改查
package com.alex.mapp;
import com.alex.pojo.User;
import org.apache.ibatis.annotations.*;
import java.awt.geom.QuadCurve2D;
import java.time.LocalDateTime;
import java.util.List;
@Mapper // 在运行时,会自动生产该接口的实现类对象,并且该对象交给IOC容器管理
public interface UserMapper {
//查询所有用户
@Select("select * from user")
public List<User> list();
//根据主键查询1个用户
@Select("select * from user where id=#{id}")
public User getById(Integer id);
// 当数据库自动和实体里的名称不一致时,不会查询出来,可以起别名让与实体类属性一致
// @Select("select id, username, password, roleId, photo, sex, age, add_ress address from user where id=#{id}")
// public User getById(Integer id);
// 结果映射
// @Results({
// @Result(column = "dep_id",property = "deptId"),
// @Result(column = "create_time",property = "createTime"),
// @Result(column = "update_time",property = "updateTime"),
// })
// @Select("select * from user where id=#{id}")
// public User getById(Integer id);
//根据ID删除
@Select("delete from user where id=#{id}")
public int delete(Integer id);
//插入数据
@Options(keyProperty = "id",useGeneratedKeys = true) //会自动将生产的主键值赋值给emp对象的id属性
@Insert("insert into user(username,password,roleId,photo,sex,age,address)" +
" values(#{username},#{password},#{roleId},#{photo},#{sex},#{age},#{address})")
public int insert(User user);
//更新数据
@Update("update user set username=#{username},password=#{password},roleId=#{roleId},photo=#{photo},sex=#{sex},age=#{age},address=#{address},updatetime=#{updatetime} where id=#{id}")
public int update(User user);
// @Select("select * from user where username like '%${name}%' and sex =#{sex} and createtime between #{begin} and #{end} order by updatetime desc ")
// public List<User> listByContion(String name, int sex, LocalDateTime begin,LocalDateTime end);
//解决${}拼接字符串,防止sql注入问题
@Select("select * from user where username like concat('%',#{name},'%') and sex =#{sex} and createtime between #{begin} and #{end} order by updatetime desc ")
public List<User> listByContion(String name, int sex, LocalDateTime begin,LocalDateTime end);
}
测试类:
package com.alex;
import com.alex.mapp.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import com.alex.pojo.User;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
@SpringBootTest //springboot 整合单元测试的注解
class SpringbootMybatisQuickstartApplicationTests {
@Autowired
private UserMapper userMapper;
//test listuser
@Test
public void testListUser(){
List<User> userList= userMapper.list();
userList.stream().forEach(user->{
System.out.println(user);
});
}
// test ListByContion
@Test
public void testListByContion(){
List<User> userList= userMapper.listByContion("chen",1, LocalDateTime.of(2021,1,1,1,1),LocalDateTime.of(2022,1,1,1,1));
System.out.println(userList);
}
@Test
public void testGetById(){
User user=userMapper.getById(15);
System.out.println(user);
}
@Test
public void testDelete(){
int delete=userMapper.delete(1);
System.out.println(delete);
}
@Test
public void testInsert(){
User user=new User();
user.setUsername("chenshaojun3");
user.setPassword("123456");
user.setRoleId(1);
user.setAge(20);
user.setAddress("shenzhen3");
user.setPhoto("URL");
user.setSex(1);
int result=userMapper.insert(user);
System.out.println(user.getId());
}
@Test
public void testUpdate(){
User user=new User();
user.setId(15);
user.setUsername("chenshaojun5");
user.setPassword("123456");
user.setRoleId(1);
user.setAge(20);
user.setAddress("shenzhen5");
user.setPhoto("URL");
user.setSex(1);
int result=userMapper.update(user);
System.out.println(result);
}
// test jdbc
@Test
public void testJDBC() throws Exception {
String irs="login faill";
PreparedStatement ps=null;
ResultSet rs=null;
Connection ct=null;
String url="jdbc:mysql://IP:3306/AA_ssm";
String user="root";
String pwd="***";
String sql="select * from user";
Class.forName("com.mysql.jdbc.Driver");
ct= DriverManager.getConnection(url,user,pwd);
ps=ct.prepareStatement(sql);
//ps.setString(1,"test");
//ps.setString(2,"123456");
rs =ps.executeQuery();
List<User> userList =new ArrayList<>();
while (rs.next()){
int id=rs.getInt("id");
String username=rs.getString("username");
String password=rs.getString("password");
int roleId=rs.getInt("roleId");
String photo=rs.getString("photo");
int sex=rs.getInt("sex");
int age=rs.getInt("age");
String address=rs.getString("address");
User users=new User(id,username,password,roleId,photo,sex,age,address);
userList.add(users);
}
ps.close();
ct.close();
userList.stream().forEach(usr ->{
System.out.println(usr);
});
System.out.println("test jdbc is success!");
}
}
1.# 开启mybatis 的驼峰名明自动映射开关,表字段:a_column --> aColumn
mybatis.configuration.map-underscore-to-camel-case=true
- // 当数据库自动和实体里的名称不一致时,不会查询出来,可以起别名让与实体类属性一致
// @Select("select id, username, password, roleId, photo, sex, age, add_ress address from user where id=#{id}")
// public User getById(Integer id);
- // 结果映射
// @Results({
//
@Result(column = "dep_id",property = "deptId"),
// @Result(column = "create_time",property = "createTime"),
// @Result(column = "update_time",property = "updateTime"),
// })
// @Select("select * from user where id=#{id}")
// public User getById(Integer id);
六。#配置mybatis的日志,指定输出到控制台,application.properties
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
七。预编译sql
#{id} #是预编译用?作为占位符;性能更高,更安全;
${id}是拼接时直接将值拼接,存在sql注入风险不建议使用
sql-->(sql语法检查,优化sql,编译sql)缓存--》执行sql
delete from tb where id=1
delete from tb where id=?
SQL注入:
select count(*) from tb where username='aaaa' and password='' or '1'='1 (' or '1'='1)为输入的密码
八。 XML映射文件规范
1.XML映射文件的名称要与Mapper接口名称一致,并且将XML映射文件和Mapper接口放在同一个包下(同包同名)
2.XML映射文件的namespace属性为mapper接口全类名称一致
3.XML映射文件中的sql语句ID与mapper接口中的方法名一致,并保持返回类型一致
4.安装mybatisX 插件(settings-->plugins-->mybatisX);提高XML映射文件开发效率,可以从xml和关联的方法间相互跳转;
5.注解来映射 & XML 来映射 sql语句 那个好:
官方推荐:https://mybatis.net.cn/getting-started.html:
UserMapper.xml demo:
<?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.alex.mapp.UserMapper">
<select id="list" resultType="com.alex.pojo.User"> # resultType 是方法返回类型对象的类型
select * from user
</select>
</mapper>
使用注解来映射简单语句会使代码显得更加简洁,但对于稍微复杂一点的语句,Java 注解不仅力不从心,还会让你本就复杂的 SQL 语句更加混乱不堪。 因此,如果你需要做一些很复杂的操作,最好用 XML 来映射语句。
选择何种方式来配置映射,以及认为是否应该要统一映射语句定义的形式,完全取决于你和你的团队。 换句话说,永远不要拘泥于一种方式,你可以很轻松的在基于注解和 XML 的语句映射方式间自由移植和切换。
推荐:简单的增删改查使用注解来映射sql
若是比较复制的sql推荐使用xml文件来映射sql
九。动态sql
test,where,set,if,foreach,sql,include
<?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.alex.mapp.UserMapper">
<sql id="commonSelect">
select id,username,password from user // 定义可以重复的sql片段
</sql>
<!-- 查询sql-->
<select id="list" resultType="com.alex.pojo.User">
-- select * from user
<include refid="commonSelect"></include> // 通过refid 指定包含的sql片段
</select>
<!-- 更新sql-->
<update id="update2">
update user
<set>
<if test="username !=null“>
username =#{username},
</if>
</set>
where id=#{1}
</update>
<!--条件查询-->
<select id="list2" resultType="com.alex.pojo.User">
select * from user
<where>
<if test="username != null">
username like concat('%',#{name},'%')
</if>
<if test="sex != null">
and sex =#{sex}
</if>
<if test="begin != null and end != null">
and createtime between #{begin} and #{end}
</if>
</where>
order by updatetime desc
</select>
<!--批量删除用户-->
<delete id="deleteByIds">
delete from user where id in
<foreach collection="ids",item="id",separator=",",open="(",close=")">
#{id}
</foreach>
</delete>
</mapper>
十。application.properties 数据库配置文件
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://ip:3306/a_hotel_ssm
spring.datasource.username=root
spring.datasource.password=****
#配置mybatis的日志,指定输出到控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# 开启mybatis 的驼峰命名自动映射开关,表字段:a_column --> aColumn
mybatis.configuration.map-underscore-to-camel-case=true