MyBatis的增删改查
- 上篇文章构建了MyBatis的框架并实现了简单查询,本篇文章继续做一些拓展
Mapper的增删改查
1.增—>添加insert
(1)insert
- UserMapper.xml
<!-- 添加记录-->
<insert id="insertUser" parameterType="com.neuedu.entity.User">
insert into user (username,sex,birthday,address)
values(#{username},#{sex},#{birthday},#{address})
</insert>
- UserMapperTest
@Test
public void insertUser(){
// Mybatis默认是开启事务的,如果开始事务就不会自动提交--->所以需要sqlSession.commit()
// using语句
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
User user = new User();
user.setUsername("李四");
user.setSex("男");
// 重设时间-->需要格式化
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
user.setBirthday(sdf.parse("2002-10-10"));
user.setAddress("唐山市");
int insert = sqlSession.insert("com.neuedu.mapper.UserMapper.insertUser",user);
System.out.println(insert);
// 提交事务
sqlSession.commit();
} catch (ParseException e) {
throw new RuntimeException(e);
}
}
*开启事务的方式
-
1.设为自动开启
try(SqlSession sqlSession = sqlSessionFactory.openSession(true)){...} -
2.通过commit开启事务
sqlSession.commit();
(2)获取用户自增ID
-
大多数时候数据库的id都会设为自增的,但是我们总会对数据库进行一些更改—>这时候我们如何知道insert对象的id呢?
-
UserMapper.xml
// select LAST_INSERT_ID()—>sql语句—>进行id的查询
<!-- 自增ID查询-->
<insert id="insertGetId" parameterType="com.neuedu.entity.User">
<!-- keyColumns是我们要获取的对象
keyProperty是获取对象的值之后,这个值要给谁
order="AFTER" 指insert之后,再把对象给到我们
-->
<selectKey keyColumn="id" keyProperty="id" order="AFTER" resultType="int">
select LAST_INSERT_ID()
</selectKey>
insert into user (username,sex,birthday,address)
values(#{username},#{sex},#{birthday},#{address})
</insert>
- UserMapperTest
@Test
public void insertGetId(){
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
User user = new User();
user.setUsername("王五");
user.setSex("男");
// 重设时间-->需要格式化
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
user.setBirthday(sdf.parse("2002-10-10"));
user.setAddress("唐山市");
int insert = sqlSession.insert("com.neuedu.mapper.UserMapper.insertGetId", user);
// 因为把参数传到了User类中,所以可以直接用getId()函数获取
System.out.println(user.getId());
// 提交事务
sqlSession.commit();
} catch (ParseException e) {
throw new RuntimeException(e);
}
}
(3)获取UUID
-
如果两个表结构相同–>当我们想要合并时,因为id是自增的—>所以会出现重复的现象
-
怎么解决?—>UUID(全局标识)—>因为UUID为字符型,所以要修改一下表结构和User类
-
数据库–>复制一份表,id的类型改为varchar
-
User_1
复制一个User类,命名为User_1===>与数据库相对应的===>把id的数据类型改为String
package com.neuedu.entity;
import lombok.Data;
import java.util.Date;
//这里的内容 与数据库表信息对应
@Data
public class User_1 {
private String id;
private String username;
private String sex;
private Date birthday;
private String address;
}
- UserMapper.xml
<!-- 获取UUID-->
<insert id="insertGetUUID" parameterType="com.neuedu.entity.User_1">
<!-- 这时候order改为before,因为UUID会先产生-->
<selectKey keyColumn="id" keyProperty="id" order="BEFORE" resultType="string">
select uuid()
</selectKey>
insert into user_1 (id,username,sex,birthday,address)
values(#{id},#{username},#{sex},#{birthday},#{address})
</insert>
- UserMapperTest
@Test
public void testUUID(){
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
User_1 user = new User_1();
user.setUsername("张三");
user.setSex("男");
// 重设时间-->需要格式化
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
user.setBirthday(sdf.parse("2002-10-10"));
user.setAddress("南京市");
int insert = sqlSession.insert("com.neuedu.mapper.UserMapper.insertGetUUID", user);
// 因为把参数传到了User类中,所以可以直接用getId()函数获取
System.out.println(user.getId());
// 提交事务
sqlSession.commit();
} catch (ParseException e) {
throw new RuntimeException(e);
}
}
2.删—>删除delete
- UserMapper.xml
<!-- 删除-->
<!-- 原始数据类型,可以省略类型指定parameterType-->
<!-- <delete id="deleteById" parameterType="com.neuedu.entity.User">-->
<delete id="deleteById">
delete from user
where id=#{id}
</delete>
- UserMapperTest
@Test
public void deleteUser(){
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
int delete = sqlSession.delete("com.neuedu.mapper.UserMapper.deleteById", 6);
sqlSession.commit();
}
}
3.改—>更新update
- UserMapper.xml
<!-- 更新-->
<update id="updateUser" parameterType="com.neuedu.entity.User">
update user set
username=#{username},sex=#{sex},birthday=#{birthday},address=#{address}
where id=#{id}
</update>
- UserMapperTest
@Test
public void updateUser(){
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
User user = new User();
// 修改必须有id
user.setId(7);
user.setUsername("王酒");
user.setSex("男");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
user.setBirthday(sdf.parse("2020-10-22"));
user.setAddress("河北省");
int update = sqlSession.update("com.neuedu.mapper.UserMapper.updateUser", user);
sqlSession.commit();
} catch (ParseException e) {
throw new RuntimeException(e);
}
}
4.查—>查询select
- 上篇文章做了简单查询—>只能查询一条记录—>用到了selectOne函数
(1)查询所有记录
- UserMapper.xml
<!-- 查询全部数据-->
<select id="findByAll" resultType="com.neuedu.entity.User">
select * from user
</select>
- UserMapperTest
@Test
public void findByAll(){
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
List<User> list = sqlSession.selectList("com.neuedu.mapper.UserMapper.findByAll");
//以json格式输出查询结果
System.out.println(JSON.toJSONString(list));
}
}
json--->[简单来说就是一种语言格式---(就类似于java,xml,js这些)]
json(JavaScript Object Notation),即JavaScript对象标记法
json是一种轻量级(Light-Meight),基于文本的(Text-Based),可读的(Human-Readable)格式
(2)Like #{}参数查询
- UserMapper.xml
<!-- Like-#{}参数查询-->
<select id="findByLikeParam" resultType="com.neuedu.entity.User">
select * from user where address like #{value}
</select>
- UserMapperTest
@Test
public void findByLikeParam(){
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
List<User> list = sqlSession.selectList(
"com.neuedu.mapper.UserMapper.findByLikeParam","%山%");
System.out.println(JSON.toJSONString(list));
}
}
(3)Like ${}拼接查询
- UserMapper.xml
<!-- Like-${}拼接查询-->
<select id="findByLikeparam2" resultType="com.neuedu.entity.User">
select * from user where address like ${value}
</select>
- UserMapperTest
@Test
public void findByLikeParam2(){
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
List<User> list = sqlSession.selectList(
"com.neuedu.mapper.UserMapper.findByLikeparam2", "'%山%'");
System.out.println(JSON.toJSONString(list));
}
}
(4)#{}和${}的区别
在使用mybatis时,我们会使用#{}和${}这两个符号来为sql语句传参数
二者有什么区别呢?
-
#{}是预编译处理,是占位符
防注入安全,也就是当使用参数查询时,会自动添加单引号
MyBatis在处理#{}的时候,会将sql中的#{}替换成 ?号 ,调用PreparedStatement来赋值eg. select * from user where name=#{value};//假设我们注入的值为value=zhangsan MyBatis处理#{}时,会将#{}替换为单引号: select * from user where name=? 然后把value的值放进去,外面再加上单引号 -
${}是字符串替换,是拼接符
有sql注入风险.原生拼接,不会加单引号eg.select * from user where name=${value};//假设value=zhangsan MyBatis在处理${}时,是直接把值拼接上去了: select * from user where name=zhangsan
(5)${}的用法
a.动态排序
- UserMapper.xml
<!-- ${}使用场景==>动态排序-->
<select id="findByOrder" resultType="com.neuedu.entity.User">
select * from user order by id ${value}
</select>
- UserMapperTest
@Test
public void findByOrder(){
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
List<User> list = sqlSession.selectList(
"com.neuedu.mapper.UserMapper.findByOrder","desc");
System.out.println(JSON.toJSONString(list));
}
}
b.动态表
- UserMapper.xml
<!-- ${}动态表-->
<select id="findByTable" resultType="com.neuedu.entity.User_1">
select * from ${value}
</select>
- UserMapperTest
@Test
public void findByTable(){
try(SqlSession sqlSession = sqlSessionFactory.openSession(true)){
List<User_1> list = sqlSession.selectList(
"com.neuedu.mapper.UserMapper.findByTable", "user_1");
System.out.println(JSON.toJSONString(list));
}
}
4266
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
