select
根据id查询
1.在UserMapper中添加对应方法
User selectUserById(int id);
2.在UserMapper.xml中添加对应的sql语句
<select id="selectUserById" resultType="com.dream.pojo.User">
select * from user where id = #{id}
</select>
3.测试
@Test
public void SelectUserById() {
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selectUserById(1);
System.out.println(user);
session.close();
}
根据姓名和密码查询用户
直接在方法中传递参数
User selectUserByNP(@Param("username") String username, @Param("pwd") String pwd);
<select id="selectUserByNP" resultType="com.dream.pojo.User">
select * from user where name = #{username} and pwd = #{pwd}
</select>
@Test
public void selectUserByNP() {
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selectUserByNP("张三","123");
System.out.println(user);
session.close();
}
也可以使用万能的Map
User selectUserByNP2(Map<String,Object> map);
<select id="selectUserByNP2" parameterType="map" resultType="com.dream.pojo.User">
select * from user where name = #{username} and pwd = #{pwd}
</select>
@Test
public void selectUserByNP2() {
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("username","王五");
map.put("pwd","12123");
User user = mapper.selectUserByNP2(map);
System.out.println(user);
session.close();
}
模糊查询
List<User> selectUserLike(String Value);
<select id="selectUserLike" resultType="com.dream.pojo.User">
select * from user where name like #{value};
</select>
@Test
public void selectUserLike(){
SqlSession sqlSession = MybatisUtils.getSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.selectUserLike("%王%");
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
insert
int addUser(User user);
<insert id="addUser" parameterType="com.dream.pojo.User">
insert into user (id,name,pwd) values (#{id},#{name},#{pwd})
</insert>
@Test
public void AddUser() {
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User(4,"赵六","qwer");
int i = mapper.addUser(user);
System.out.println(i);
session.commit();
session.close();
}
update
int updateUser(User user);
<update id="updateUser" parameterType="com.dream.pojo.User">
update user set name=#{name},pwd=#{pwd} where id = #{id}
</update>
@Test
public void UpdateUser() {
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selectUserById(1);
user.setPwd("12321");
int i = mapper.updateUser(user);
System.out.println(i);
session.commit();
session.close();
}
delete
int deleteUser(int id);
<delete id="deleteUser" parameterType="int">
delete from user where id = #{id}
</delete>
@Test
public void DeleteUser() {
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
int i = mapper.deleteUser(4);
System.out.println(i);
session.commit();
session.close();
}
所有的增删改操作都需要提交事务
接口所有的普通参数都尽量写上@Param参数,尤其是在多个参数的情况下
根据业务的需求可以考虑使用map传递参数
模糊查询时应避免直接在sql语句中拼接通配符,会引起sql注入