Mybatis学习笔记_2、增删改查

沿用上一节所建数据表和配置文件等

查询

1.模糊查询

mapper.xml

<mapper namespace="UserMapper">
  	<!-- #{}占位符 尽量使用占位符来解决问题 -->
	<!-- ${}字符串拼接 容易产生SQL注入问题  -->
   <select id="selectUserByName" parameterType="String" resultType="pers.goodwin.mybatis.bean.User">
	<!-- select * from t_user where u_username like '%${value}%' -->
	select * from t_user where u_username like "%"#{id}"%"
  </select>
</mapper>

测试代码

@Test
	public void test_selectUserList() throws IOException {
		InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		//参数1：要操作的SQL语句，参数2：SQL的参数
		List<User> userList = sqlSession.selectList("UserMapper.selectUserByName", "王");
		for (User user : userList) {
			System.out.println(user);
		}
	}

结果

增加

mapper.xml

<!-- 添加用户 -->
  <insert id="insertUser" parameterType="pers.goodwin.mybatis.bean.User">
  	insert into t_user values (null, #{u_username}, #{u_password}, #{u_gender}, #{u_cid})
  </insert>

测试代码

@Test
	public void test_insertUser() throws IOException {
		InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		User user = new User();
		user.setU_username("小明");
		user.setU_password("111");
		user.setU_gender(0);
		user.setU_cid(2);
		sqlSession.insert("UserMapper.insertUser", user);
		sqlSession.commit();
	}

结果

修改

mapper.xml

  <update id="updateUser" parameterType="pers.goodwin.mybatis.bean.User">
  update t_user set u_username = #{u_username} where u_id = #{u_id}
  </update>

测试代码

@Test
	public void test_updateUser() throws IOException {
		InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		User user = new User();
		user.setU_id(7);
		user.setU_username("小王");

		sqlSession.update("UserMapper.updateUser", user);
		sqlSession.commit();
	}

结果

删除

mapper.xml

  <delete id="deleteUserById" parameterType="Integer">
  	delete from t_user where u_id = #{id}
  </delete>

测试代码

@Test
	public void test_delectUser() throws IOException {
		InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession sqlSession = sqlSessionFactory.openSession();

		sqlSession.delete("UserMapper.deleteUserById",7 );
		sqlSession.commit();
	}

结果