MyBatis之各种查询

User类

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中写入

User selectUserById(int id);

UserMapper.xml中写入

<select id="selectUserById" parameterType="int" resultType="user">
        select * from user where id = #{id}
    </select>

测试类中写入

@Test
    public void selectUserById(){
        User user = userMapper.selectUserById(19);
        System.out.println(user);
    }

 执行结果为

DEBUG 09-28 22:20:48,658 ==>  Preparing: select * from user where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 09-28 22:20:48,674 ==> Parameters: 19(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 09-28 22:20:48,682 <==      Total: 1  (BaseJdbcLogger.java:137) 
User{id=19, username='丽丽', password='389482', age=27, sex='男', email='zs@qq.com'}

查询一个list集

UserMapper中写入

List<User> selectUserAll();

UserMapper.xml中写入

<select id="selectUserAll" resultType="user">
        select * from user
    </select>

测试类中写入

@Test
    public void selectUserAllTest(){
        List<User> users = userMapper.selectUserAll();
        for(User u:users){
            System.out.println(u);
        }
    }

执行结果为

DEBUG 09-28 22:24:09,776 ==>  Preparing: select * from user  (BaseJdbcLogger.java:137) 
DEBUG 09-28 22:24:09,792 ==> Parameters:   (BaseJdbcLogger.java:137) 
DEBUG 09-28 22:24:09,801 <==      Total: 5  (BaseJdbcLogger.java:137) 
User{id=19, username='丽丽', password='389482', age=27, sex='男', email='zs@qq.com'}
User{id=20, username='张三', password='123456', age=27, sex='男', email='zs@qq.com'}
User{id=21, username='张三', password='123456', age=27, sex='男', email='zs@qq.com'}

查询数据总量

UserMapper中写入

int getCount();

UserMapper.xml中写入

<select id="getCount" resultType="int">
        select count(*) from user;
    </select>

测试类中写入

public void getCountTest(){
        int num = userMapper.getCount();
        System.out.println(num);
    }

测试结果为

DEBUG 09-28 22:34:42,414 ==>  Preparing: select count(*) from user;  (BaseJdbcLogger.java:137) 
DEBUG 09-28 22:34:42,431 ==> Parameters:   (BaseJdbcLogger.java:137) 
DEBUG 09-28 22:34:42,438 <==      Total: 1  (BaseJdbcLogger.java:137) 
5

查询一条数据为map集合

UserMapper中写入

Map<String,Object> selectUserTomap(Integer id);

UserMapper.xml中写入

Map:xml中 如果需要传递参数需要传递Map类型

<select id="selectUserTomap" resultType="map">
        select * from user where id = #{id};
    </select>

测试类中写入

@Test
    public void selectUserTomapTest(){
        Map<String, Object> map = userMapper.selectUserTomap(19);
        System.out.println(map);
    }

测试结果为

DEBUG 09-28 23:04:08,233 ==>  Preparing: select * from user where id = ?;  (BaseJdbcLogger.java:137) 
DEBUG 09-28 23:04:08,251 ==> Parameters: 19(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 09-28 23:04:08,259 <==      Total: 1  (BaseJdbcLogger.java:137) 
{password=389482, sex=男, id=19, age=27, email=zs@qq.com, username=丽丽}

查询多条数据为map集合

UserMapper中写入

List<Map<String,Object>> selectUserAllTolist();

UserMapper.xml中写入

List:xml中 如果需要传递参数需要传递List中的类型

<select id="selectUserAllTolist" resultType="map">
        select * from user
    </select>

测试类中写入

@Test
    public void selectUserAllTolist(){
        List<Map<String, Object>> mapList = userMapper.selectUserAllTolist();
        for(Map<String, Object> map:mapList)
            System.out.println(map);
    }

测试结果为

DEBUG 09-28 23:07:48,572 ==>  Preparing: select * from user  (BaseJdbcLogger.java:137) 
DEBUG 09-28 23:07:48,589 ==> Parameters:   (BaseJdbcLogger.java:137) 
DEBUG 09-28 23:07:48,601 <==      Total: 5  (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=张三}
{password=123456, sex=男, id=21, age=27, email=zs@qq.com, username=张三}

特别注意点

Map:xml中 如果需要传递参数需要传递Map类型

List:xml中 如果需要传递参数需要传递List中的类型