准备工作
- 将之前的项目复制一份，改名，然后将对应的pom文件中的
改成新的项目名称 - 将新的项目导入到idea中，直接打开pom文件 一直点下一步即可
注意：在编写测试方法时，测试类的名字为UserMapperTest，测试方法的方法名为testXxxx，这样取名符合编程规范
Mybatis 案例
一、案例1：入门程序（略过）
二、案例2：两个以上sql 参数
数据库：
drop database if exists mybatisdemo;
create database mybatisdemo;
use mybatisdemo;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` date DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', '张三', '2018-07-10', '1', '北京');
INSERT INTO `user` VALUES ('2', '李四', '2018-07-10', '0', '上海');
INSERT INTO `user` VALUES ('3', '王五', '2018-07-10', '1', '广州');
INSERT INTO `user` VALUES ('4', '王六', '2018-07-10', '1', '深圳');
INSERT INTO `user` VALUES ('5', '王八', '2018-07-10', '1', '上海');
create table employee(
id varchar(255) primary key,
name varchar(255)
)场景: 查在上海的女人
select * from user whrere sex = ? and address = ?1. 通过Map 的方式
mapper.xml
<select id="selectUserByAddrAndSexA" parameterType="map" resultMap="userMap">
select * from user where sex = #{sex} and address = #{address}
</select>mapper 接口的代码
List<User> selectUserByAddrAndSexA(Map<String,Object> map);测试代码
Map<String,Object> map = new HashMap<>();
map.put("sex",0);
map.put("address","上海");
List<User> userList = userMapper.selectUserByAddrAndSexA(map);2. 通过java Bean 的方式
mapper.xml
<select id="selectUserByAddrAndSexB" parameterType="net.suncaper.mybatisdemo.domain.User" resultMap="userMap">
select * from user where sex = #{sex} and address = #{address}
</select>mapper 接口的代码
List<User> selectUserByAddrAndSexB(User user);测试代码
User user = new User();
user.setSex("0");
user.setAddress("上海");
List<User> userList = userMapper.selectUserByAddrAndSexB(user); 3. 通过java Bean + 命名式参数 的方式
mapper.xml
<select id="selectUserByAddrAndSexC" resultMap="userMap">
select * from user where sex = #{u.sex} and address = #{u.address}
</select>mapper 接口的代码
List<User> selectUserByAddrAndSexC(@Param("u") User user);测试代码
User user = new User();
user.setSex("0");
user.setAddress("上海");
List<User> userList = userMapper.selectUserByAddrAndSexC(user);4. 通过 命名式参数 的方式
mapper.xml
<select id="selectUserByAddrAndSexD" resultMap="userMap">
select * from user where sex = #{a} and address = #{b}
</select>mapper 接口的代码
List<User> selectUserByAddrAndSexD(@Param("a") String sex,@Param("b")String address);测试代码
List<User> userList = userMapper.selectUserByAddrAndSexD("0","上海");三、案例3：以集合或数组作为参数
查询广州、北京、上海、深圳的人
mapper.xml
<select id="selectUserBySomeAddr" resultMap="userMap">
select * from user where address in
<foreach collection="addresses" open="(" close=")" separator="," item="addr">
#{addr}
</foreach>
</select>mapper 接口的代码
List<User> selectUserBySomeAddr(@Param("addresses") String[] addresses);测试代码
List<User> userList= userMapper.selectUserBySomeAddr(new String[]{"上海","广州","深圳","北京"});四、案例4：演示# 和 $ 的区别
$ 只是字符串的拼接，不能防止SQL注入
${value}会被直接替换，而#{value}会被使用?作为 预处理
注：JDBC是通过设定传入数据类型进行防注入的
mapper.xml
<select id="selectUserByName" parameterType="string" resultMap="userMap">
select * from user where username = '${value }'
<!--或者username=#{username}-->
</select>mapper 接口的代码
List<User> selectUserByName(String name);测试代码
List<User> users = userMapper.selectUserByName("王八");执行结果对比
五、案例5：模糊查询
场景:查询名字 包含”王“ 字混球
方式1：通过$符号（方便但是不安全）
mapper.xml
<select id="selectUserLikeName" parameterType="string" resultMap="userMap">
select * from user where username like '%${value}%'
</select>mapper 接口的代码
//查询有"王"字
List<User> selectUserLikeName(String name);测试代码
List<User> users = userMapper.selectUserLikeName("王");功能没问题 但是不能防止SQL注入攻击
方式2：通过字符串连接函数（安全但是不好看）
使用数据库函数CONCAT
mapper.xml
<select id="selectUserLikeName1" parameterType="string" resultMap="userMap">
select * from user where username like CONCAT('%',#{value},'%')
</select>
<!--使用#符号可以防止sql注入攻击-->mapper 接口的代码
//查询有"王"字
List<User> selectUserLikeName1(String name);测试代码
List<User> users = userMapper.selectUserLikeName1("王");通过#符号的使用，防止了sql注入，可是依赖性太强
方式3：通过<bind> 标签（中规中矩，安全方便）
mapper.xml
<select id="selectUserLikeName2" parameterType="string" resultMap="userMap">
<bind name="item" value="'%'+name+'%'"/>
select * from user where username like #{item}
</select>mapper 接口的代码
//查询有"王"字
List<User> selectUserLikeName2(String name);测试代码
List<User> users = userMapper.selectUserLikeName2("王");既可以防止sql注入，又不依赖数据库 任何数据库都可以执行
六、案例6：插入语句并获得主键
方式1: 通过useGeneratedKeys
通过 useGeneratedKeys拿到数据库自动增长的id值，赋给插入对象的主键字段（user 对象的id属性）
mapper.xml
<insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex}, #{address} )
</insert>段落引用
mapper 接口的代码
void insertUser(User user);测试代码
User user = new User("龙哥","0",new Date(),"重庆");
userMapper.insertUser(user);
//持有持久化ID
System.out.println(user.getId());方式2: 通过数据库函数实现
自动增长的id : select last_insert_id()
获取uuid: select uuid()
mapper.xml
<!-- 字符串类型作为主键 -->
<insert id="insertEmployee" parameterType="Employee">
<selectKey resultType="string" keyProperty="id" order="BEFORE">
select uuid()
</selectKey>
insert into employee(id,name) values(#{id},#{name})
</insert>
<!-- int 类型作为主键 -->
<insert id="insertUser1" parameterType="User">
<selectKey resultType="_int" keyProperty="id" order="AFTER">
select last_insert_id()
</selectKey>
insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex}, #{address})
</insert>
mapper 接口的代码
void insertEmployee(Employee employee);
void insertUser1(User user);测试代码
Employee employee = new Employee();
/* employee.setId(IDGener.getUUID()); 默认情况下，缺少主键不能插入数据库*/
employee.setName("员工222");
employeeMapper.insertEmployee(employee);
System.out.println(employee.getId());
//-------------------------------
User user = new User("黄爷","0",new Date(),"重庆");
userMapper.insertUser1(user);
System.out.println(user.getId());七、案例7：更新和删除操作
通过删除和更新，事务处理
默认情况下 sqlSession 是事务不自动提交，需要显式提交
mapper.xml
<delete id="deleteUserByName" parameterType="User">
delete from user where username = #{username}
</delete>
<update id="updateUserNameById" parameterType="User">
update user set username = #{username} where id = #{id}
</update>mapper 接口的代码
public void deleteUserByName(User user);
void updateUserNameById(User user);测试代码
//true 自动提交 默认是true
//在测试类中如果sqlSession为false，那么如果想要提交事务需要编写代码:
@Test
public void testDeleteUserByName(){
boolean isAuto = false;
try {
isAuto = sqlSession.getConnection().getAutoCommit();
sqlSession.getConnection().setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
try {
User user = new User();
user.setUsername("李四");
userMapper.deleteUserByName(user);
sqlSession.commit();
} catch (Exception e) {
sqlSession.rollback();
e.printStackTrace();
}
try {
sqlSession.getConnection().setAutoCommit(isAuto);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testUpdateUserNameById(){
boolean isAuto = false;
try {
isAuto = sqlSession.getConnection().getAutoCommit();
sqlSession.getConnection().setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
try {
User user = new User();
user.setId(1);
user.setUsername("黄柯");
userMapper.updateUserNameById(user);
sqlSession.commit();
} catch (Exception e) {
sqlSession.rollback();
e.printStackTrace();
}
try {
sqlSession.getConnection().setAutoCommit(isAuto);
} catch (SQLException e) {
e.printStackTrace();
}
}
//结束