特殊SQL的执行

User

package com.qcby.mybatis.pojo;

public class User {
    private Integer id;
    private String username;
    private String password;
    private Integer age;
    private String sex;
    private String email;

    public User(Integer id, String username, String password, Integer age, String sex, String email) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.age = age;
        this.sex = sex;
        this.email = email;
    }

    public User(){

    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

模糊查询

在UserMapper中写入

List<User> selectUserMoreByobscure(String name);

 在UserMapper.xml中写入

<select id="selectUserMoreByobscure" resultType="user">
        select * from user where username like "%"#{username}"%"
    </select>

在测试中写入

@Test
    public void selectUserMoreByobscureTest(){
        List<User>  users= userMapper.selectUserMoreByobscure("张");
        for(User user:users)
            System.out.println(user);
    }

 结果是

DEBUG 09-29 23:23:12,071 ==>  Preparing: select * from user where username like "%"?"%"  (BaseJdbcLogger.java:137) 
DEBUG 09-29 23:23:12,086 ==> Parameters: 张(String)  (BaseJdbcLogger.java:137) 
DEBUG 09-29 23:23:12,093 <==      Total: 4  (BaseJdbcLogger.java:137) 
User{id=20, username='张三', password='123456', age=27, sex='男', email='zs@qq.com'}
User{id=21, username='张强', password='123456', age=27, sex='男', email='zq@qq.com'}
User{id=22, username='张麻', password='123456', age=27, sex='男', email='zm@qq.com'}
User{id=23, username='张首', password='123456', age=27, sex='男', email='zs@qq.com'}

模糊查询的方法

1. 在Usermapper.xml中使用 "%"#{字段}"%"

   <select id="selectUserMoreByobscure" resultType="user">
       select * from user where username like "%"#{username}"%"
   </select>

2. 在Usermapper.xml中使用 '%${字段}%’

   <select id="selectUserMoreByobscure" resultType="user">
       select * from user where username like '%${username}%'
   </select>

3. 在Usermapper.xml中使用 数据库内置拼接函数 concat()（不推荐）

   <select id="selectUserMoreByobscure" resultType="user">
       select * from user where username like concat('%',#{username},'%')
   </select>

   其中必须是#{ }

 批量删除

在UserMapper中写入

int delTomore(String ids);

在UserMapper.xml中写入

<delete id="delTomore" parameterType="string">
        delete from user where id in(${ids})
    </delete>

在测试类中写入

@Test
    public void delTomoreTest(){
        int num = userMapper.delTomore("24,25,26");
        System.out.println("删除了"+num+"条语句");
    }

运行结果是

DEBUG 09-30 14:17:32,915 ==>  Preparing: delete from user where id in(21,25,26)  (BaseJdbcLogger.java:137) 
DEBUG 09-30 14:17:32,931 ==> Parameters:   (BaseJdbcLogger.java:137) 
DEBUG 09-30 14:17:32,933 <==    Updates: 3  (BaseJdbcLogger.java:137) 
删除了3条语句

注意点

在UserMapper.xml中 使用sql语句是使用${ } 如果使用#{ }仅能删除第一个数据(即样例中24)

 动态设置表名 获取表中的数据

在UserMapper中写入

List<Map<String,Object>> getTableAll(String tablename);

在UserMapper.xml中写入

<select id="getTableAll" resultType="map">
        select * from ${tablename}
    </select>

在测试类中进行测试

@Test
    public void getTableAllTest(){
        List<Map<String, Object>> users = userMapper.getTableAll("user");
        for(Map<String, Object> map:users){
            System.out.println(map);
        }
    }

执行结果是

DEBUG 10-03 20:16:13,819 ==>  Preparing: select * from user  (BaseJdbcLogger.java:137) 
DEBUG 10-03 20:16:13,837 ==> Parameters:   (BaseJdbcLogger.java:137) 
DEBUG 10-03 20:16:13,844 <==      Total: 2  (BaseJdbcLogger.java:137) 
{password=389482, sex=男, id=19, age=27, email=zs@qq.com, username=丽丽}
{password=123456, sex=男, id=20, age=27, email=zs@qq.com, username=张三}

添加功能获取自增的主键

一般来说 在进行插入操作时我们无法进行获取自增主键的值 但通过在insert中进行

 useGeneratedKeys=""：表示当前添加功能是否使用自增主键
 keyProperty="" ：数据库中主键对应的实体类的属性名称
设置就能实现

在UserMapper中写入

int insertUserAndgetkey(User user);

在UserMapper.xml中写入

<insert id="insertUserAndgetkey" useGeneratedKeys="true" keyProperty="id">
        insert into user values (null,#{username},#{password},#{age},#{sex},#{email})
    </insert>

在测试类中写入

@Test
    public void insertUserAndgetkey(){
        User user = new User(null,"李四","777777",30,"女","ls@qq.com");
        int num = userMapper.insertUserAndgetkey(user);
        if(num>0){
            System.out.println("插入成功");
            System.out.println(user);
        }
        else
            System.out.println("插入失败");

    }

执行结果为

DEBUG 10-03 20:54:46,803 ==>  Preparing: insert into user values (null,?,?,?,?,?)  (BaseJdbcLogger.java:137) 
DEBUG 10-03 20:54:46,822 ==> Parameters: 李四(String), 777777(String), 30(Integer), 女(String), ls@qq.com(String)  (BaseJdbcLogger.java:137) 
DEBUG 10-03 20:54:46,826 <==    Updates: 1  (BaseJdbcLogger.java:137) 
插入成功
User{id=31, username='李四', password='777777', age=30, sex='女', email='ls@qq.com'}

如果不进行 useGeneratedKeys="" keyProperty="" 属性设置 结果为

DEBUG 10-03 20:55:53,523 ==>  Preparing: insert into user values (null,?,?,?,?,?)  (BaseJdbcLogger.java:137) 
DEBUG 10-03 20:55:53,539 ==> Parameters: 李四(String), 777777(String), 30(Integer), 女(String), ls@qq.com(String)  (BaseJdbcLogger.java:137) 
DEBUG 10-03 20:55:53,543 <==    Updates: 1  (BaseJdbcLogger.java:137) 
插入成功
User{id=null, username='李四', password='777777', age=30, sex='女', email='ls@qq.com'}