MyBatis入门 (二) 增删改查实现
MyBatis入门(二)
增删改查实现
查询（select）
根据id进行查询
//UserDao接口
public interface UserMapper {
//查询所有
List<User> getUserList();
//根据id查询
User getUserById(int id);
}
//UserDao接口实现
<?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.godwin.mapper.UserMapper">
<select id="getUserList" resultType="com.godwin.pojo.User">
select * from mybatis.user;
</select>
<select id="getUserById" parameterType="int" resultType="com.godwin.pojo.User">
select * from mybatis.user where id = #{id}
</select>
</mapper>
@Test
public void getUserById(){
//第一步，获得SqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//获得UserMapper接口的class并返回他
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
sqlSession.close();
}
增加(insert)
//UserDao接口
public interface UserMapper {
//查询所有
List<User> getUserList();
//根据id查询
User getUserById(int id);
//增加
int addUser(User user);
}
<?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.godwin.mapper.UserMapper">
<select id="getUserList" resultType="com.godwin.pojo.User">
select * from mybatis.user;
</select>
<select id="getUserById" parameterType="int" resultType="com.godwin.pojo.User">
select * from mybatis.user where id = #{id}
</select>
<insert id="addUser" parameterType="com.godwin.pojo.User">
insert into mybatis.user(id,name,pwd)values(#{id},#{name},#{pwd})
</insert>
</mapper>
@Test
public void addUser(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.addUser(new User(5, "赵七", "1234567"));
//提交事务
sqlSession.commit();
sqlSession.close();
}
修改(update)
//UserDao接口
public interface UserMapper {
//查询所有
List<User> getUserList();
//根据id查询
User getUserById(int id);
//增加
int addUser(User user);
//修改
int updateUser(User user);
}
<?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.godwin.mapper.UserMapper">
<select id="getUserList" resultType="com.godwin.pojo.User">
select * from mybatis.user;
</select>
<select id="getUserById" parameterType="int" resultType="com.godwin.pojo.User">
select * from mybatis.user where id = #{id};
</select>
<insert id="addUser" parameterType="com.godwin.pojo.User">
insert into mybatis.user(id,name,pwd)values(#{id},#{name},#{pwd});
</insert>
<update id="updateUser" parameterType="com.godwin.pojo.User">
update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id};
</update>
</mapper>
@Test
public void updateUser(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.updateUser(new User(5,"秦七","123"));
sqlSession.commit();
sqlSession.close();
}
删除(delete)
//UserDao接口
public interface UserMapper {
//查询所有
List<User> getUserList();
//根据id查询
User getUserById(int id);
//增加
int addUser(User user);
//修改
int updateUser(User user);
//删除
int deleteUser(int id);
}
<?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.godwin.mapper.UserMapper">
<select id="getUserList" resultType="com.godwin.pojo.User">
select * from mybatis.user;
</select>
<select id="getUserById" parameterType="int" resultType="com.godwin.pojo.User">
select * from mybatis.user where id = #{id};
</select>
<insert id="addUser" parameterType="com.godwin.pojo.User">
insert into mybatis.user(id,name,pwd)values(#{id},#{name},#{pwd});
</insert>
<update id="updateUser" parameterType="com.godwin.pojo.User">
update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id};
</update>
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id = #{id};
</delete>
</mapper>
@Test
public void deleteUser(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.deleteUser(5);
sqlSession.commit();
sqlSession.close();
}
增删改查注意：
//增修删必须加事务
sqlSession.commit();
流程：
- 编写接口
- 编写对应的mapper中的sql语句
- 测试
排查错误
- 标签不要匹配错
- resource绑定mapper,需要使用路径!
- 读错从下往上翻
- 程序配置文件必须符合规范
- NullPointerException,没有注册到资源
- maven资源没有导出问题
Map
假设，我们的实体类，或者数据库中的表，字段或者参数过多，我们应当考虑使用Map!
//UserDao接口
int addUser1(Map<String,Object>map);
<insert id="addUser1" parameterType="map">
insert into mybatis.user(id,name,pwd) values(#{userid},#{username},#{userpwd});
</insert>
@Test
public void addUser1(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("userid",5);
map.put("username","七七");
map.put("userpwd","233233");
mapper.addUser1(map);
sqlSession.commit();
sqlSession.close();
}
Map传递参数，直接在sql中取出key即可！
对象传递参数，直接在sql中取对象的属性即可
只有一个基本数据类型的参数下，可以直接在sql中取到
多个参数用Map，或者注解
模糊查询
- Java代码执行的时候，传递通配符%%
- 在sql拼接中使用通配符！
//UserDao接口
List<User> getUserLike(String value);
<select id="getUserLike" resultType="com.godwin.pojo.User">
select * from mybatis.user where name like #{value};
</select>
@Test
public void getUserLike(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userLike = mapper.getUserLike("%张%");
for (User user : userLike) {
System.out.println(user);
}
sqlSession.close();
}
复习笔记资料来自B站UP主：狂神说
