简单的CRUD
第一步
先在UserMapper.java接口中添加具体要实现的方法
public interface UserMapper {
//查询全部用户
List<User> getUserList();
//根据ID查询用户
User getUserById(int id);
//insert into 一个用户
int addUser(User user);
int updateUser(User user);
int deleteUser(int id);
//万能的Map
int addUser2(Map<String,Object> map);
}
第二步
在UserMapper.xml中添加上面接口要实现的标签
在这里需要注意:
传入参数的类型 parameterType="int"
返回结果的类型 resultType="com.shadow.pojo.User
如果参数过多,但要使用的参数很少,用实体类的构造器全部赋值太麻烦,可以使用Map< key,value >来进行传入参数,但是必须注意要写对应Map的key
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.shadow.dao.UserMapper">
<select id="getUserList" resultType="com.shadow.pojo.User">
select * from mybatis.user
</select>
<select id="getUserById" parameterType="int" resultType="com.shadow.pojo.User">
select * from mybatis.user where id = #{id}
</select>
<insert id="addUser" parameterType="com.shadow.pojo.User">
insert into mybatis.user (id,name,password) values (#{id},#{name},#{password})
</insert>
<update id="updateUser" parameterType="com.shadow.pojo.User">
update mybatis.user set name=#{name},password=#{password} where id =#{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id=#{id};
</delete>
<!-- 万能的Map
-->
<insert id="addUser2" parameterType="map">
insert into mybatis.user (id,name,password) values (#{userid},#{username},#{userPassword})
</insert>
</mapper>
第三步
在测试类中进行测试
这里要注意的是:增删改都需要提交事务,否则不能实际的修改数据库
public class UserMapperTest {
@Test
public void test() {
//第一步:获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//第二步:执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.getUserList();
for (User user : userList) {
System.out.println(user);
}
//关闭sqlSession
sqlSession.close();
}
@Test
public void getUserById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User userById = mapper.getUserById(2);
System.out.println(userById.toString());
sqlSession.close();
}
//增删改 需要提交事物
@Test
public void addUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.addUser(new User(6,"小明","123456"));
if (i > 0) {
System.out.println("插入成功!");
}
//提交事务
sqlSession.commit();
sqlSession.close();
}
@Test
public void updateUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int count = mapper.updateUser(new User(5, "小红", "333333"));
if (count > 0) {
System.out.println("修改成功!");
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void deleteUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int count = mapper.deleteUser(6);
if (count > 0) {
System.out.println("删除成功!");
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void addUser2() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()){
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("userid",7);
map.put("username","小兰");
map.put("userPassword","145632");
mapper.addUser2(map);
sqlSession.commit();
}
}
}
小结:
- 所有的增删改操作都需要提交事务!
- 接口所有的普通参数,尽量都写上@Param参数,尤其是多个参数时,必须写上!
- 有时候根据业务的需求,可以考虑使用map传递参数!
- 为了规范操作,在SQL的配置文件中,我们尽量将Parameter参数和resultType都写上!
模糊查询
第一步
先在UserMapper.java接口中添加具体要实现的方法
//模糊查询
List<User> getUserLike(String value);
第二步
在UserMapper.xml中添加上面接口要实现的标签
<select id="getUserLike" resultType="com.shadow.pojo.User">
select * from mybatis.user where name like #{value};
</select>
第三步
在测试类中进行测试
@Test
public void getUserLike(){
try(SqlSession sqlSession = MybatisUtils.getSqlSession()){
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserLike("%小%");
for (User user : userList) {
System.out.println(user);
}
}
}