Version: Next

CRUD

Mapper.xml -> namespace

  • namespace中的包名要与接口名一致

Mapper.xml -> select

选择、查询语句

<select id="方法名" resultType="Sql语句的返回值" parameterTyep="参数类型"></select>
  • id:就是对应的namespace中的方法名
  • resultType: Sql语句执行的返回类型
  • parameterType:参数的类型
//接口
public interface IUserMapper {
//查询全部用户
List<User> getUserList();
//根据id查询用户
User getUserById(int id);
}
<!-- mapper.xml -->
<mapper namespace="com.bsx.dao.IUserMapper">
<select id="getUserList" resultType="com.bsx.pojo.User">
SELECT * FROM USER;
</select>
<select id="getUserById" resultType="com.bsx.pojo.User" parameterType="int">
SELECT * FROM USER WHERE id = #{id}
</select>
</mapper>
//测试类
@Test
public void testGetUserById() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
//1.获取SqlSession对象
try {
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}

Mapper.xml -> insert

warning

增删改操作需要提交事务

//接口
public interface IUserMapper {
int addUser(User user);
}
<!-- 对象中的属性可以直接取出来 user.name , user.password-->
<insert id="addUser" parameterType="com.bsx.pojo.User">
INSERT INTO USER (id, name, password) VALUES (NULL, #{name}, #{password});
</insert>
//测试类
@Test
public void testInsertUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
User user = new User();
user.setName("新来的");
user.setPassword("3306");
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
int returnNumber = mapper.addUser(user);
System.out.println("return number = " + returnNumber);
if (returnNumber > 0) {
System.out.println("插入成功");
}
//提交事务
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}

Mapper.xml -> Update

//接口
public interface IUserMapper {
int updateUser(User user);
}
<!-- mapper.xml -->
<update id="updateUser" parameterType="com.bsx.pojo.User">
update user set name = #{name}, password = #{password} where id = #{id};
</update>
@Test
public void testUpdateUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
User user = new User();
user.setId(1);
user.setName("改名的");
user.setPassword("123456");
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
int returnNumber = mapper.updateUser(user);
System.out.println("return number = " + returnNumber);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}

Mapper.xml -> Delete

//接口
int deleteUser(int id);
<delete id="deleteUser">
DELETE FROM USER WHERE id = #{id};
</delete>
@Test
public void testDelete() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
int returnNumber = mapper.deleteUser(3);
System.out.println("return number = " + returnNumber);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}

万能Map

不需要填写实体类的所有字段 -> parameterType="map"

//接口
//万能的map
int addUser2(Map<String,Object> map);
<!-- values部分的名字为 map的key就可以-->
<!-- 可以只填充部分字段-->
<insert id="addUser2" parameterType="map">
insert into user (id,name,password) values (#{userid},#{userName},#{password})
</insert>
//测试
@Test
public void testAddUser2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("userid", null);
map.put("userName", "map来的");
map.put("password", "123123");
mapper.addUser2(map);
sqlSession.commit();
}

模糊查询

warning

注意！！！测试类中拼接值必须用%%包起来,传递通配符，sql语句中的参数名与接口参数名一致，只有一个参数

//接口
//模糊查询
List<User> getUserLike(String value);
<!-- 模糊查询-->
<select id="getUserLike" resultType="com.bsx.pojo.User">
SELECT * FROM USER where name like #{value}
</select>
//测试
@Test
public void testLike(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
List<User> userList = mapper.getUserLike("%狂%");
for (User user : userList) {
System.out.println(user);
}
}