2. Mybatis案例（查询）

案例1：两个以上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 接口方法

List<User> selectUserByAddrAndSexA(Map<String,Object> map);

• Mapper 映射文件

<select id="selectUserByAddrAndSexA" parameterType="map" resultMap="userMap">
        select * from user where sex = #{sex} and address = #{address}
 </select>

• 测试代码

   Map<String,Object> map = new HashMap<>();
   map.put("sex",0);
   map.put("address","上海");
   List<User> userList = userMapper.selectUserByAddrAndSexA(map);

2. 通过java Bean 的方式

• Mapper 接口方法

List<User> selectUserByAddrAndSexB(User user);

• Mapper 映射文件

<select id="selectUserByAddrAndSexB" parameterType="net.suncaper.mybatisdemo.domain.User" resultMap="userMap">
        select * from user where sex = #{sex} and address = #{address}
 </select>

• 测试代码

User user = new User();
user.setSex("0");
user.setAddress("上海");
List<User> userList = userMapper.selectUserByAddrAndSexB(user);  

3. 通过java Bean + 命名式参数 的方式

• Mapper 接口方法

List<User> selectUserByAddrAndSexC(@Param("u") User user);

• Mapper 映射文件

<select id="selectUserByAddrAndSexC" resultMap="userMap">
        select * from user where sex = #{u.sex} and address = #{u.address}
 </select>

• 测试代码

    User user = new User();
    user.setSex("0");
    user.setAddress("上海");
    List<User> userList = userMapper.selectUserByAddrAndSexC(user);

4. 通过命名式参数 的方式

• Mapper 接口方法

List<User> selectUserByAddrAndSexD(@Param("a") String sex,@Param("b")String address);

• Mapper 映射文件

<select id="selectUserByAddrAndSexD" resultMap="userMap">
        select * from user where sex = #{a} and address = #{b}
 </select>

• 测试代码

List<User> userList = userMapper.selectUserByAddrAndSexD("0","上海");

案例2：以集合或数组作为参数

查询广州、北京、上海、深圳的人

• Mapper 接口方法

List<User> selectUserBySomeAddr(@Param("addresses") String[] addresses);

• Mapper 映射文件

<select id="selectUserBySomeAddr"  resultMap="userMap">
       select * from user where address in
       <foreach collection="addresses" open="(" close=")" separator="," item="addr">
         #{addr}
       </foreach>
 </select>

• 测试代码

List<User> userList= userMapper.selectUserBySomeAddr(new String[]{"上海","广州","深圳","北京"});

案例3：演示# 和 $ 的区别

• $ 只是字符串的拼接，不能防止SQL注入
• ${value} 会被直接替换，而 #{value} 会被使用 ?作为 预处理
• Mapper 接口方法

List<User>  selectUserByName(String name);

• Mapper 映射文件

<select id="selectUserByName" parameterType="string" resultMap="userMap">
         select * from user where username = '${value }'
 </select>

• 测试代码

List<User> users = userMapper.selectUserByName("王");

案例4：模糊查询

场景:查询名字 包含”王“ 字

方式1：通过$符号

• Mapper 接口方法

List<User>  selectUserLikeName(String name);

• Mapper 映射文件

<select id="selectUserLikeName" parameterType="string" resultMap="userMap">
       select * from user where username like '%${value}%'
   </select>

• 测试代码

List<User> users = userMapper.selectUserLikeName("王");

方式2：通过字符串连接函数

• Mapper 接口方法

List<User>  selectUserLikeName1(String name);

• Mapper 映射文件

<select id="selectUserLikeName1" parameterType="string" resultMap="userMap">
       select * from user where username like CONCAT('%',#{value},'%')
    </select>

• 测试代码

List<User> users = userMapper.selectUserLikeName1("王");

方式3：通过 标签

• Mapper 接口方法

List<User>  selectUserLikeName2(@Param("name")String name);

• Mapper 映射文件

<select id="selectUserLikeName2" parameterType="string" resultMap="userMap">
      <bind name="item" value="'%'+name+'%'"/>
       select * from user where username like  #{item}
  </select>

• 测试代码

List<User> users = userMapper.selectUserLikeName2("王");

案例5：插入语句并获得主键

方式1: 通过useGeneratedKeys

通过 useGeneratedKeys拿到数据库自动增长的id值，赋给插入对象的主键字段（user 对象的id属性）

• Mapper 接口方法

 void insertUser(User user);

• Mapper 映射文件

<insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex}, #{address} )
 </insert>

• 测试代码

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 接口方法

void insertEmployee(Employee employee);
void insertUser1(User user);

• Mapper 映射文件

<!-- 字符串类型作为主键 -->
<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>

• 测试代码

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());

案例6：更新和删除操作

通过删除和更新，事务处理
默认情况下 sqlSession 是事务不自动提交，需要显式提交

• Mapper 接口方法

 public void deleteUserByName(User user);
void updateUserNameById(User user);

• Mapper 映射文件

<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>

• 测试代码

@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();
       }

   }