3、CRUD
1、namespace
namespace中的包名要和Dao/Mapper接口的包名一致
2、select
选择,查询语句;
- id:就是对应的namespace中的方法名
- resultType:SQL语句执行的返回值
- parameterType:参数类型
1.编写接口
//根据ID查询用户
User getUserById(int id);
2.编写对应的mapper中的sql语句
<select id="getUserById" parameterType="int" resultType="com.gy.pojo.User">
select * from mybatis.user where id = #{id}
</select>
3.测试
@Test
public void testGetUserById() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
User user = mapper.getUserById(1);
System.out.println(user);
sqlSession.close();
}
3、insert
1.编写接口
//insert一个用户
int addUser(User user);
2.编写对应的mapper中的sql语句
<!--对象中的属性,可以直接取出来-->
<insert id="addUser" parameterType="com.gy.pojo.User">
insert into mybatis.user (id, name, pwd) values (#{id},#{name},#{pwd});
</insert>
3.测试
//增删改需要提交事务
@Test
public void testAddUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
int res = mapper.addUser(new User(4, "gongyi", "lovemuzi"));
if (res > 0) {
System.out.println("插入成功");
}
//提交事务
sqlSession.commit();
sqlSession.close();
}
4、update
1.编写接口
//修改用户
int updateUser(User user);
2.编写对应的mapper中的sql语句
<!--idea连接mysql后,写insert,update等语句有提示和自动补全等功能很方便-->
<update id="updateUser" parameterType="com.gy.pojo.User">
update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id};
</update>
3.测试
@Test
public void testUpdateUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
int res = mapper.updateUser(new User(4, "我是工一", "我是密码"));
if (res > 0) {
System.out.println("更新成功");
}
sqlSession.commit();
sqlSession.close();
}
5、delete
1.编写接口
//删除一个用户
int deleteUser(int id);
2.编写对应的mapper中的sql语句
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id = #{id};
</delete>
3.测试
@Test
public void testDeleteUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
mapper.deleteUser(4);
sqlSession.commit();
sqlSession.close();
}
注意点:
- 增删改需要提交事务
6、分析错误
- 标签不要匹配错(insert就是insert,不要用select)
- resource绑定mapper,需要使用路径(有时候是类路径,有时候是/)
- 程序配置文件必须符合规范
- NullPointerException,没有注册到资源(提升作用域)
- 输出的xml文件中存在中文乱码问题
- maven资源没有导出问题
7、万能Map
假设,我们的实体类,或者数据库中的表,字段或者参数过多,我们应当考虑使用map
//万能的Map
int addUser2(Map<String, Object> map);
User getUserById2(Map<String, Object> map);
<!--
对象中的属性,可以直接取出来
传递map的key
-->
<insert id="addUser2" parameterType="map">
insert into mybatis.user(id, name, pwd) values (#{userId},#{userName},#{userPassword});
</insert>
<select id="getUserById2" parameterType="map" resultType="com.gy.pojo.User">
select * from mybatis.user where id = #{userId} and name=#{userName}
</select>
@Test
public void testAddUser2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("userId", 4);
map.put("userName", "测试map1");
map.put("userPassword", "测试密码");
mapper.addUser2(map);
//提交事务
sqlSession.commit();
sqlSession.close();
}
@Test
public void testGetUserById2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("userId", 4);
map.put("userName", "测试map1");
User user = mapper.getUserById2(map);
System.out.println(user);
sqlSession.close();
}
map传递参数,直接在sql中取出key即可,【parameterType=“map”】
对象传递参数,直接在sql中取对象的属性即可,【parameterType=“Object” 】
只有一个基本类型参数的情况下,可以直接在sql中取到
多个参数用Map,或者注解
8、思考题
模糊查询怎么写?
1.Java代码执行的时候,传递通配符%%
List<User> userList = mapper.getUserLike("%一%");
2.在SQL拼接中使用通配符
select * from mybatis.user where name like "%"#{value}"%"
核心代码:
List<User> getUserLike(Map<String, Object> map);
<!--
select * from mybatis.user where id = ?
select * from mybatis.user where id = 1 or 1=1
select * from mybatis.user where name like #{value};
select * from mybatis.user where name like "%"#{value}"%"
-->
<select id="getUserLike" parameterType="map" resultType="com.gy.pojo.User">
select * from mybatis.user where name like "%"#{value}"%";
</select>
@Test
public void testGetUserLike() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("value", "一");
//List<User> userList = mapper.getUserLike(map);
List<User> userList = mapper.getUserLike(map);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
彩蛋
1.异常读日志,从下往上看