使用Mybatis进行增删改查等操作
通常方式进行增删查改
查找
1.编写接口
//查询全部用户
List<User> getUserList();
//根据id查询用户
User getUserById(int id);
2.编写对应的mapper.xml中的sql语句
<!-- select查询语句-->
<select id="getUserList" resultType="com.lzj.entity.User">
select * from mybatis.user
</select>
<select id="getUserById" parameterType="int" resultType="com.lzj.entity.User">
select * from mybatis.user where id = #{id}
</select>
3.测试
@Test
public void test() {
//获得SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//方式一:通过getMapper执行SQL
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserList();
for (User user : userList) {
System.out.println(user);
}
//方式二:不推荐这种方式
List<User> userList1 = sqlSession.selectList("com.lzj.dao.UserMapper.getUserList");
for (User user : userList1) {
System.out.println(user);
}
//关闭SqlSession对象
sqlSession.close();
}
@Test
public void getUserByIdTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User userById = mapper.getUserById(1);
System.out.println(userById);
sqlSession.close();
}
增加
1.编写接口
//insert一个用户
int addUser(User user);
2.编写对应的mapper.xml中的sql语句
<insert id="addUser" parameterType="com.lzj.entity.User">
insert into mybatis.user (id,name,pwd) values (#{id},#{name},#{pwd});
</insert>
3.测试
@Test
public void addUserTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.addUser(new User(4, "李六", "123456"));
if(res>0){
System.out.println("yes");
}
//提交事务,增删改都必须要提交事务
sqlSession.commit();
sqlSession.close();
}
修改
1.编写接口
//修改用户
int updateUser(User user);
2.编写对应的mapper.xml中的sql语句
<update id="updateUser" parameterType="com.lzj.entity.User">
update mybatis.user
set name=#{name},pwd=#{pwd}
where id=#{id};
</update>
3.测试
@Test
public void updateUserTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.updateUser(new User(4, "李鬼", "1234"));
if(res>0){
System.out.println("yes");
}
//提交事务,增删改都必须要提交事务
sqlSession.commit();
sqlSession.close();
}
删除
1.编写接口
//删除一个用户
int deledeUser(int id);
2.编写对应的mapper.xml中的sql语句
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id=#{id};
</delete>
3.测试
@Test
public void deleteUserTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.deledeUser(4);
if(res>0){
System.out.println("yes");
}
//提交事务,增删改都必须要提交事务
sqlSession.commit();
sqlSession.close();
}
注解方式进行增删查改
1.修改xml配置文件,绑定接口
<mappers>
<mapper class="com.lzj.dao.UserMapper"></mapper>
</mappers>
2.在接口类中编写相关接口
public interface UserMapper {
@Select("select * from user")
List<User> getUser();
//根据id查询用户
//方法存在多个参数,所有的参数前面必须加上@Param("id")注解
@Select("select * from user where id = #{id} and name =#{name}")
User getUserById(@Param("id") int id,@Param("name") String name);
//插入
@Insert("insert into user(id,name,pwd) values (#{id},#{name},#{password})")
int addUser(User user);
//修改
@Update("update user set name=#{name},pwd=#{password} where id=#{id}")
int updateUser(User user);
//删除
@Delete("delete from user where id=#{id}")
int deleteUser(@Param("id") int id);
}
3.进行测试
@Test
public void getUserTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> user = mapper.getUser();
for (User user1 : user) {
System.out.println(user1);
}
sqlSession.commit();
sqlSession.close();
}
关于@Param()注解
- 基本类型的参数或者String类型,需要加上
- 引用类型不需要加
- 如果只有一个基本类型的花可以忽略,但是建议还是加上
- 在SQL中引用的是这里的@Param()中设定的属性名
万能的Map
假设我们的实体类或者数据库中的表,字段或者参数过多,我们应当考虑使用Map
- Map传递参数,直接在sql中取出key即可!
- 对象传递参数,直接在sql中取对象的属性即可!
- 只有一个基本类型的情况下,可以直接在sql中取到!
- 多个参数用Map,或者注解
1.编写接口
//万能的Map
int addUser2(Map<String,Object> map);
2.编写对应的mapper.xml中的sql语句
<insert id="addUser2" parameterType="map">
insert into mybatis.user (id,name,pwd) values (#{userId},#{userName},#{password});
</insert>
3.测试
@Test
public void addUser2Test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("userId","5");
map.put("userName","qqw");
map.put("password","12345678");
int res = mapper.addUser2(map);
if(res>0){
System.out.println("yes");
}
//提交事务,增删改都必须要提交事务
sqlSession.commit();
sqlSession.close();
}
}
模糊查询
Java代码执行的时候传递通配符%%
List<User> userList = mapper.getUserLike("%李%");
是sql拼接中使用通配符("%"#{value}"%"),%需要加双引号
select * from mybatis.user where name like "%"#{value}"%"
分页
使用mybatis分页插件PageHelper
注意点
- 增删改需要提交事务(sqlSession.commit();)