本文章涉及环境版本:
- mysql 5.7
- Mybatis 3.5.x
- Maven 3.6.x
- JDK 1.8
项目代码仓库:
https://github.com/Gang-bb/Study-Record/tree/main/bzhan-mybatis-study
需要clone整个bzhan-mybatis-study项目
(整体是一个maven多module工程)
该文章对应:《bzhan-mybatis-study01 》module项目
1. select
![image-20210205225336791](https://i-blog.csdnimg.cn/blog_migrate/89a425b0faa297d518944f3fe2e1b441.png)
选择,查询语句;
- id : 就是对应的namespace中接口的方法名;
- resultType:Sql语句执行的返回值!
- parameterType : 参数类型!
- UserMapper接口中添加
User selectUserById(int id);
- UserMapper.xml文件中添加查询语句
<select id="selectUserById" resultType="com.gangbb.model.pojo.User" parameterType="_int">
select * from user where id = #{id}
</select>
- 测试
@Test
public void testSelectOne(){
SqlSession sqlSession = null;
try {
//1. 获取sqlsession对象
sqlSession = MybatisUtil.openSession();
//2.方式一: getMapper
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserById(2);
//方式二:
//User user = sqlSession.selectOne("selectUserById", 1);
System.out.println(user);
} finally {
//3. 关闭sqlsession
sqlSession.close();
}
}
- 结果
![image-20210205182308996](https://i-blog.csdnimg.cn/blog_migrate/3d656ce8d35dfdef4eea88a434778d2b.png)
2. update
- UserMapper接口中添加
int updateUser(User user);
- UserMapper.xml文件中添加查询语句
<update id="updateUser">
update user set name=#{name}, password=#{password} where id = #{id}
</update>
- 测试
/**
* 更新一个用户信息
*/
@Test
public void UpdateOne(){
SqlSession sqlSession = null;
try {
//1. 获取sqlsession对象
sqlSession = MybatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//1.直接定义一个user
User user = new User(1, "Gangbbxx", "123456");
//或者从数据库中查出记录后在修改
//ser user = userMapper.selectUserById(1);
//user.setName("修改后的名字");
int i = userMapper.updateUser(user);
//提交事务
sqlSession.commit(i != 0);
System.out.println("成功更新" + i + "行");
} finally {
//3. 关闭sqlsession
sqlSession.close();
}
}
- 结果
![image-20210205190029713](https://i-blog.csdnimg.cn/blog_migrate/f08073668dae6beed1c9a4d7164597f6.png)
3. Insert
- UserMapper接口中添加
int insertUser(User user);
- UserMapper.xml文件中添加查询语句
<insert id="insertUser">
insert into user(id, name, password) values (#{id},#{name},#{password});
</insert>
- 测试
/**
* 测试插入一条数据
*/
@Test
public void insertOne(){
SqlSession sqlSession = null;
try {
//1. 获取sqlsession对象
sqlSession = MybatisUtil.openSession();
//2.方式一: getMapper
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User(null, "Gangbb", "123456");
//int i = userMapper.insertUser(user);
//方式二:
int i = sqlSession.insert("insertUser", user);
//提交事务
sqlSession.commit(i != 0);
System.out.println("成功插入" + i + "行");
} finally {
//3. 关闭sqlsession
sqlSession.close();
}
}
- 结果
4. Delete
- UserMapper接口中添加
int deleteUser(int id);
- UserMapper.xml文件中添加查询语句
<delete id="deleteUser">
delete from user where id=#{id};
</delete>
- 测试
@Test
public void deleteOne(){
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int i = userMapper.deleteUser(2);
//提交事务
sqlSession.commit(i != 0);
System.out.println("成功删除" + i + "行");
} finally {
//3. 关闭sqlsession
sqlSession.close();
}
}
- 结果
![image-20210205195603511](https://i-blog.csdnimg.cn/blog_migrate/a7b44edb8c39ac8a1dac272fd6efe367.png)
PS: 删改需要提交事务!
5. Map传参
如果我们的实体类,或者数据库中的表,字段或者参数过多,我们应当考虑使用Map!
- UserMapper接口中添加
int addUser(Map<String, Object> map);
- UserMapper.xml文件中添加查询语句
PS:values中的参数名与传入map中的key一一对应
<insert id="addUser" parameterType="map">
insert into user(id, name, password) values (#{uid},#{userName},#{password});
</insert>
- 测试
/**
* 测试用map传递参数插入一条数据
*/
@Test
public void addOne(){
SqlSession sqlSession = null;
try {
//1. 获取sqlsession对象
sqlSession = MybatisUtil.openSession();
//2.方式一: getMapper
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User(null, "Gangbb", "123456");
Map<String, Object> map = new HashMap<String, Object>();
map.put("uid", 23);
map.put("userName", "map添加");
map.put("password", "123456");
int i = userMapper.addUser(map);
//提交事务
sqlSession.commit(i != 0);
System.out.println("成功插入" + i + "行");
} finally {
//3. 关闭sqlsession
sqlSession.close();
}
}
- 结果
![image-20210206102026243](https://i-blog.csdnimg.cn/blog_migrate/b50d4a3f115a010febcee48fc8475412.png)
Map传递参数,直接在sql中取出key即可! 【parameterType=“map”】
对象传递参数,直接在sql中取对象的属性即可!【parameterType=“Object”】
只有一个基本类型参数的情况下,可以直接在sql中取到!
多个参数用Map,或者注解!
5. 模糊查询
表中记录:
- 方式一:调用时传入 %
- UserMapper接口中添加
List<User> selectUserByKey(String key);
- UserMapper.xml文件中添加查询语句
<!--模糊查询-->
<select id="selectUserByKey" parameterType="string" resultType="com.gangbb.model.pojo.User">
select * from user where name like #{key}
</select>
- 测试类主要关键代码
// 方式A
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectUserByKey("%G%");
//方式B
List<User> userList = sqlSession.selectList("selectUserByKey", "%G%");
- 方式二:sql中写%
- UserMapper接口中添加
List<User> selectUserByKey(String key);
- UserMapper.xml文件中添加查询语句
<!--模糊查询-->
<select id="selectUserByKey" parameterType="string" resultType="com.gangbb.model.pojo.User">
select * from user where name like ‘%"#{value}"%’
</select>
- 测试类主要关键代码
// 方式A
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectUserByKey("G");
//方式B
List<User> userList = sqlSession.selectList("selectUserByKey", "G");
- 方式三:CONCAT拼接
- UserMapper接口中添加
List<User> selectUserByKey(String key);
- UserMapper.xml文件中添加查询语句
<!--模糊查询-->
<select id="selectUserByKey" parameterType="string" resultType="com.gangbb.model.pojo.User">
select * from user where name like CONCAT(CONCAT('%', #{key}), '%');
</select>
- 测试类主要关键代码
// 方式A
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectUserByKey("G");
//方式B
List<User> userList = sqlSession.selectList("selectUserByKey", "G");
三种模糊查询的结果都一样:**