XML实现CRUD
# MyBatis实现 XML形式的CURD⛵️
# 1、增加操作
# 🅰️方案一、通过对象插入
【示例:UserMapper.java
】
public interface UserMapper {
boolean addUser(User user);
}
2
3
4
【示例:UserMapper.xml
】
<mapper namespace="com.singerw.mapper.UserMapper">
<!--增加用户-->
<insert id="addUser">
insert into g_users
value (null,
#{username},
#{userphone},
#{userpassword},
0,
now(),
now(),
1)
</insert>
</mapper>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
【示例:UserMapperTest.java
】
public class UserMapperTest {
private SqlSession sqlSession = MyBatisUtils.getSqlSession();
private UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
@Test
public void addUser() {
User user = new User("张欣", "19999999999", "assam1314520");
boolean flag = userMapper.addUser(user);
sqlSession.commit();
System.out.println(flag ? "增加成功" : "增加失败");
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
# 🅱️方案二、通过Map插入
💛(万能方法)工作必备野路子方法,也可以使用到查询,修改,删除等等中💛
假设我们的实体类中,或者数据库的表中,字段或者参数很多,我们应当考虑使用Map
插入
【示例:UserMapper.java
】
public interface UserMapper {
boolean addUser2(Map<String,Object> map);
}
2
3
4
【示例:UserMapper.xml
】
<?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.singerw.mapper.UserMapper">
<!--增加用户-->
<insert id="addUser2" parameterType="map">
insert into g_users (userid, username, userphone, userpassword, jurisdiction, createtime, logintime, userstatus)
values (null,#{username},#{userphone},#{userpassword},#{jurisdiction},now(),now(),#{userstatus});
</insert>
</mapper>
2
3
4
5
6
7
8
9
10
11
12
13
【示例:UserMapperTest.java
】
public class UserMapperTest {
private SqlSession sqlSession = MyBatisUtils.getSqlSession();
private UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
@Test
public void addUser() {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap hashMap = new HashMap<String,Object>();
hashMap.put("username","singerw");
hashMap.put("userphone","18888888888");
hashMap.put("userpassword","123456");
hashMap.put("jurisdiction",1);
hashMap.put("userstatus",1);
mapper.addUser2(hashMap);
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 2、删除操作
【示例:UserMapper.java
】
public interface UserMapper {
boolean delUser(int userid);
}
2
3
4
【示例:UserMapper.xml
】
<mapper namespace="com.singerw.mapper.UserMapper">
<!--根据ID删除用户-->
<delete id="delUser">
delete
from goku.g_users
where userid = #{userid}
</delete>
</mapper>
2
3
4
5
6
7
8
9
10
【示例:UserMapperTest.java
】
public class UserMapperTest {
private SqlSession sqlSession = MyBatisUtils.getSqlSession();
private UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
@Test
public void delUser() {
boolean flag = userMapper.delUser(10);
//必须要提交
sqlSession.commit();
System.out.println(flag ? "删除成功" : "删除失败");
sqlSession.close();
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
# 3、修改操作
# 🅰️方案一、参数为一个对象
【示例:UserMapper.java
】
public interface UserMapper {
boolean updateUser2(User user);
}
2
3
4
【示例:UserMapper.xml
】
<mapper namespace="com.singerw.mapper.UserMapper">
<!--根据ID修改用户-->
<update id="updateUser">
update g_users
set username = #{username},
userphone = #{userphone},
userpassword = #{userpassword},
jurisdiction = #{jurisdiction},
userstatus = #{userstatus}
where userid = #{userid}
</update>
</mapper>
2
3
4
5
6
7
8
9
10
11
12
13
14
【示例:UserMapperTest.java
】
public class UserMapperTest {
private SqlSession sqlSession = MyBatisUtils.getSqlSession();
private UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
@Test
public void updateUser2() {
User user = new User("测试测试测试", "156515561561", "123456", 1, 1, 8);
boolean flag = userMapper.updateUser2(user);
//必须要提交
sqlSession.commit();
System.out.println(flag ? "修改成功" : "修改失败");
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
# 🅱️方案二、参数为多个不同参数
注意:此时我们的修改的方法的参数不再是单一的参数,而是多个参数
【示例:UserMapper.java
】
public interface UserMapper {
boolean updateUser(String username, String userphone, String userpassword, int jurisdiction, int userstatus, int userid);
}
2
3
4
【示例:UserMapper.xml
】
<mapper namespace="com.singerw.mapper.UserMapper">
<!--根据ID修改用户-->
<update id="updateUser">
update g_users
set username = #{username},
userphone = #{userphone},
userpassword = #{userpassword},
jurisdiction = #{jurisdiction},
userstatus = #{userstatus}
where userid = #{userid}
</update>
</mapper>
2
3
4
5
6
7
8
9
10
11
12
13
14
【示例:UserMapperTest.java
】
public class UserMapperTest {
private SqlSession sqlSession = MyBatisUtils.getSqlSession();
private UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
@Test
public void updateUser() {
boolean flag = userMapper.updateUser("测试测试测试", "1444444444", "123456", 1, 1, 5);
//必须要提交
sqlSession.commit();
System.out.println(flag ? "修改成功" : "修改失败");
}
}
2
3
4
5
6
7
8
9
10
11
12
13
报错处理
这时我们发现测试结果报错:
Parameter 'username' not found. Available parameters are [arg3, arg2, param5, arg5, arg4, param6, arg1, arg0, param3, param4, param1, param2]
多个参数的绑定默认按照顺序;
🅰️解决方法一(推荐):修改UserMapper.xml
中的update
参数信息为如下,MyBatis才能识别到。
【解决方法示例:UserMapper.xml
】
<mapper namespace="com.singerw.mapper.UserMapper">
<!--根据ID修改用户-->
<update id="updateUser">
update g_users
set username = #{arg0},
userphone = #{arg1},
userpassword = #{arg2},
jurisdiction = #{arg3},
userstatus = #{arg4}
where userid = #{arg5}
</update>
</mapper>
2
3
4
5
6
7
8
9
10
11
12
13
14
或者修改UserMapper.java
中的updateUser
方法的参数信息,这样MyBatis也能识别到。
public interface UserMapper {
boolean updateUser(@Param("username") String username, @Param("userphone") String userphone, @Param("userpassword") String userpassword, @Param("jurisdiction") int jurisdiction, @Param("userstatus") int userstatus, @Param("userid") int userid);
}
2
3
4
5
🅱️解决方法二:将参数构造成一个map
对象,将我们的属性传递进来
【解决方法示例:UserMapper.java
】
public interface UserMapper {
boolean updateUser(Map map);
}
2
3
4
【解决方法示例:UserMapper.xml
】
<mapper namespace="com.singerw.mapper.UserMapper">
<!--根据ID修改用户-->
<!--根据ID修改用户-->
<!--根据ID修改用户-->
<update id="updateUser">
update g_users
set username = #{username},
userphone = #{userphone},
userpassword = #{userpassword},
jurisdiction = #{jurisdiction},
userstatus = #{userstatus}
where userid = #{userid}
</update>
</mapper>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
【解决方法示例:UserMapperTest.java
】
public class UserMapperTest {
private SqlSession sqlSession = MyBatisUtils.getSqlSession();
private UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
@Test
public void updateUser() {
HashMap map = new HashMap();
map.put("username","测试测试测试");
map.put("userphone","1444444444");
map.put("userpassword","123456");
map.put("jurisdiction",1);
map.put("userstatus",0);
map.put("userid",10);
boolean flag = userMapper.updateUser(map);
//必须要提交
sqlSession.commit();
System.out.println(flag ? "修改成功" : "修改失败");
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 4 、查询操作
【示例:UserMapper.java
】
public interface UserMapper {
List<User> getUsers();
User getUser(int userid);
}
2
3
4
5
6
【示例:UserMapper.xml
】
<mapper namespace="com.singerw.mapper.UserMapper">
<!--查询所有用户-->
<!-- 此时没有指定包的情况,其实用的是别名Blog -->
<select id="getUsers" resultType="User">
select *
from goku.g_users
</select>
<!-- 此时没有指定包的情况,其实用的是别名Blog -->
<select id="getUser" resultType="User">
select *
from goku.g_users
where userid = #{userid}
</select>
</mapper>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
【示例:UserMapperTest.java
】
public class UserMapperTest {
private SqlSession sqlSession = MyBatisUtils.getSqlSession();
private UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
@Test
public void getUsers() {
List<User> users = userMapper.getUsers();
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
@Test
public void getUser() {
User user = userMapper.getUser(2);
System.out.println(user);
sqlSession.close();
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21