目录
5.1.1 先从接口(UserMapper.java) 里面写
5.1.2 再写接口的配置文件(UserMapper.xml)
5.1.3 最后写 MyBatisTest.java 测试类
1、copy之前的maven工程,可得到一个普通的文件
2、将普通文件 变成一个maven工程
3、修改配置
4、解决子工程 项目名后面多出一堆名字的 问题
4.1 问题
4.2 解决步骤
‘
5、增删改查
注意:除了查询、插入, 其他都要提交事务
5.1 查询
5.1.1 先从接口(UserMapper.java) 里面写
package com.by.mapper;
import com.by.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface UserMapper {
//单个参数传递
User findUserById(Integer id);
//传递多个参数--序号参数绑定
User findUserByIdAndName(Integer id, String username);
//传递多个参数--注解参数绑定
User findUserByIdAndName2(@Param("id") Integer id, @Param("username")String username);
//对象参数绑定
User findUserByPojo(User userParam);
//Map参数绑定
User findUserByMap(Map<String, Object> map);
//模糊查询
List<User> findUserByUsername(String username);
List<User> findUserByUsername2(String username);
//sql注入
User login(User userParam);
//聚合函数查询
Integer getCount();
}
5.1.2 再写接口的配置文件(UserMapper.xml)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- Map<namespace.id, MappedStatement(sql, resultType)> -->
<!-- namespace="接口全类名" -->
<mapper namespace="com.by.mapper.UserMapper">
<!--
id="findUserById":接口的方法名
parameterType="java.lang.Integer":参数类型
resultType="com.by.pojo.User":查询的结果装到哪个对象里
-->
<!--单个参数绑定-->
<select id="findUserById" parameterType="java.lang.Integer" resultType="com.by.pojo.User">
<!--#{}:等价于jdbc的?,即占位符,用于拼接参数*-->
SELECT * FROM user WHERE id=#{id}
</select>
<!--序号参数绑定-->
<select id="findUserByIdAndName" resultType="com.by.pojo.User">
<!--SELECT * FROM user WHERE id=#{arg0} AND username=#{arg1}-->
SELECT * FROM user WHERE id=#{param1} AND username=#{param2}
</select>
<!--推荐使用:-->
<!--注解参数绑定-->
<select id="findUserByIdAndName2" resultType="com.by.pojo.User">
SELECT * FROM user WHERE username=#{username} AND id=#{id}
</select>
<!-- 对象参数绑定 -->
<select id="findUserByPojo" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
<!--
#{id}:属性名,getId
#{username}:属性名,getUsername
-->
SELECT * FROM user WHERE id=#{id} AND username=#{username}
</select>
<!--Map参数绑定-->
<select id="findUserByMap" parameterType="java.util.Map" resultType="com.by.pojo.User">
<!--
#{id}:map的key,map.get("id")
#{username}:map的key,map.get("username")
-->
SELECT * FROM user WHERE id=#{id} AND username=#{username}
</select>
<!--模糊查询-->
<select id="findUserByUsername" parameterType="java.lang.String" resultType="com.by.pojo.User">
SELECT * FROM user where username like CONCAT('%', #{username}, '%')
<!--SELECT * FROM user where username like '%${value}%'-->
</select>
<select id="findUserByUsername2" parameterType="java.lang.String" resultType="com.by.pojo.User">
SELECT * FROM user where username = #{username}
<!--SELECT * FROM user where username = #{sb}-->
<!--SELECT * FROM user where username = '${value}'-->
</select>
<!--sql注入-->
<select id="login" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
SELECT * FROM user WHERE username=#{username} AND password=#{password}
</select>
<!--聚合函数查询-->
<select id="getCount" resultType="java.lang.Integer">
SELECT COUNT(1) FROM user
</select>
</mapper>
5.1.3 最后写 MyBatisTest.java 测试类
package com.by.test;
import com.by.mapper.UserMapper;
import com.by.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatisTest {
private InputStream inputStream;
private SqlSession sqlSession;
//测试方法执行前调用
@Before
public void createSqlSession() throws IOException {
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sessionFactory.openSession();
}
@Test
public void testFindUserById() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//调接口的一个方法findUserById() 此方法所要做的事:从map里面把sql找出来 --> 交给监听器JDBC --> 把数据装到对象中
User user = userMapper.findUserById(41);
System.out.println(user);
}
@Test
public void testFindUserByIdAndUsername() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findUserByIdAndName(41, "张三丰");
System.out.println(user);
}
/**
* 注解参数绑定 -- 适用于:参数少
* @throws IOException
*/
@Test
public void testFindUserByIdAndUsername2() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findUserByIdAndName2(41, "张三丰");
System.out.println(user);
}
/**
* 对象参数绑定 -- 适用于:参数多
* @throws IOException
*/
@Test
public void testFindUserByPojo() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User userParam = new User();
userParam.setId(41);
userParam.setUsername("张三丰");
User user = userMapper.findUserByPojo(userParam);
System.out.println(user);
}
/**
* Map参数绑定
* @throws IOException
*/
@Test
public void testFindUserByMap() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("id", 41);
map.put("username", "张三丰");
User user = userMapper.findUserByMap(map);
System.out.println(user);
}
/**
* 模糊查询
* @throws IOException
*/
@Test
public void testFindUserByUsername() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.findUserByUsername("张");
for (User user : userList) {
System.out.println(user);
}
}
@Test
public void testFindUserByUsername2() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.findUserByUsername2("张三丰");
for (User user : userList) {
System.out.println(user);
}
}
/**
* sql注入
* @throws IOException
*/
@Test
public void testLogin() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User userParam = new User();
userParam.setUsername("张三丰' #");
userParam.setPassword("251314");
User user = userMapper.login(userParam);
System.out.println(user);
}
/**
* 聚合函数查询
*/
@Test
public void testGetCount()throws IOException{
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Integer count = userMapper.getCount();
System.out.println("总共:"+count+"个人");
}
@After
public void closeSqlSession() throws IOException {
sqlSession.close();
inputStream.close();
}
}
5.2 删除
也可按照 “测试类、接口、映射文件” 的顺序来写
/**
* 删除
*/
@Test
public void testDeleteUserById() throws IOException{
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
userMapper.deleteUserById(42);
sqlSession.commit();
}
//删除
void deleteUserById(Integer id);
<!--删除-->
<delete id="deleteUserById" parameterType="java.lang.Integer">
DELETE FROM user WHERE id=#{id}
</delete>
5.3 修改
/**
* 修改
*/
@Test
public void testUpdateUserById() throws IOException{
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User userParam = new User();
userParam.setId(49);
userParam.setUsername("猪八戒");
userParam.setPassword("111");
userParam.setBirthday(new Date());
userParam.setSex("男");
userParam.setAddress("高老庄");
userMapper.updateUserById(userParam);
sqlSession.commit();
}
//修改
void updateUserById(User userParam);
<!--修改-->
<update id="updateUserById" parameterType="com.by.pojo.User">
UPDATE user
SET username=#{username},password=#{password},birthday=#{birthday},sex=#{sex},address=#{address}
WHERE id=#{id}
</update>
5.4 添加
/**
* 新增
*/
@Test
public void testInsertUser() throws IOException{
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User userParam = new User();
userParam.setUsername("刘翠兰");
userParam.setPassword("111");
userParam.setBirthday(new Date());
userParam.setSex("女");
userParam.setAddress("高老庄");
userMapper.insertUser(userParam);
System.out.println(userParam.getId());
}
//添加
void insertUser(User userParam);
<!-- 添加-->
<insert id="insertUser" parameterType="com.by.pojo.User" useGeneratedKeys="true" keyProperty="id">
insert into user(username, password, birthday, sex, address)
values(#{username},#{password},#{birthday},#{sex},#{address})
</insert>