情况描述:
本人初次接触Mybatis,然后对于其中的一些基础查询做一些简单总结,一次用来记录他的用法,便于以后查漏补缺。
1、Mybatis中查询特定的列:(单列)
如果查询指定列为Long类型,那么在resultType中应该是:java.lang.Long,如果是其他类型的以此类推即可。
Mapper层:
//查询所有用户名List<String> queryListName();
xml层:
<select id="queryListName" resultType="java.lang.String">select name from user2</select>
调用:
@Testpublic void getListName(){List<String> strings = userMapper.queryListName();strings.forEach(System.out::println);}
结果:
**
补充其实查询单列与查询单值是一个道理。只不过mapper层的方法的返回值是一个具体的数据类型,比如我们只查询查询用户id为4的用户名。
mapper层
String queryName();
xml层
<select id="queryName" resultType="java.lang.String">select name from user2 where user_id = 4</select>
2、Mybatis中查询特定的列:(多列)
**
对于多列查询,这个时候就建议你,尽量将其封装到实体类中。
Mapper层:
//查询三列List<User2> queryListNameAndAge();
xml配置:
<select id="queryListNameAndAge" resultType="com.swagger.swaggerdemo1.Pojo.User2">select id,name,create_time from user2</select>
测试:
@Testpublic void queryListNameAndAge(){List<User2> lists = userMapper.queryListNameAndAge();//升序排列List<String> name = lists.stream().sorted(Comparator.comparing(User2::getCreateTime)).map(user -> {return user.getName();}).collect(Collectors.toList());name.forEach(System.out::println);lists.forEach(System.out::println);}
**
3、Mybatis中一次查询多个集合(相当于一对多进行查询)
**
在这里 主要运用了两个关键标签:resultMap、collection
如果我们对collection的column传入多个参数问题:
举个例子:
(1)Pojo层:
package com.swagger.swaggerdemo1.Pojo;import io.swagger.models.auth.In;
import org.omg.PortableInterceptor.INACTIVE;import java.util.List;public class ToUser2 {public Integer id;public String name;public List<Integer> maths;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public List<Integer> getMaths() {return maths;}public void setMaths(List<Integer> maths) {this.maths = maths;}@Overridepublic String toString() {return "ToUser2{" +"id=" + id +", name='" + name + '\'' +", maths=" + maths +'}';}
}
(2)Mapper层:
//查询指定集合List<ToUser2> queryGrades();
(3)xml层
<resultMap id="BaseDate" type="com.swagger.swaggerdemo1.Pojo.ToUser2"><id column="id" property="id"></id><collection property="maths" column="id" ofType="java.lang.Integer" select="queryMaths"></collection></resultMap><select id="queryMaths" resultType="java.lang.Integer">SELECT math FROM `grade` where user_id = #{id}</select><select id="queryGrades" resultMap="BaseDate">select * from user2</select>
(4)测试:
@Testpublic void queryCollection(){List<ToUser2> toUser2s = userMapper.queryGrades();toUser2s.forEach(System.out::println);}
**
4、Mybatis中进行关联查询(相当于一对一进行查询)
**
关联查询的核心的标签则是:association
举个例子:
(1)Pojo层:
学生表:
package com.swagger.swaggerdemo1.Pojo;import java.util.Date;public class User2 {private Integer id;private String name;private Date createTime;private Integer tid;private Teacher teacher;public Integer getTid() {return tid;}public void setTid(Integer tid) {this.tid = tid;}public Teacher getTeacher() {return teacher;}public void setTeacher(Teacher teacher) {this.teacher = teacher;}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "User2{" +"id=" + id +", name='" + name + '\'' +", createTime=" + createTime +", tid=" + tid +", teacher=" + teacher +'}';}
}
老师表:
package com.swagger.swaggerdemo1.Pojo;public class Teacher {private Integer id;private String name;private Integer age;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}@Overridepublic String toString() {return "Teacher{" +"id=" + id +", name='" + name + '\'' +", age=" + age +'}';}
}
(2)mapper层
//查询(association)List<User2> queryUserAndTeacher();
(3)xml层:
<resultMap id="UserAndTeacher" type="com.swagger.swaggerdemo1.Pojo.User2"><association property="teacher" column="tid" select="queryTeacher"></association></resultMap><select id="queryTeacher" resultType="com.swagger.swaggerdemo1.Pojo.Teacher">select * from teacher where id = #{id}</select><select id="queryUserAndTeacher" resultMap="UserAndTeacher">select * from user2</select>
(4)测试层:
@Testpublic void queryAssociation(){List<User2> user2s = userMapper.queryUserAndTeacher();user2s.forEach(System.out::println);}
测试结果:
总结:如果你是一个新手小白,其实只要你掌握这四种查询方式,基本上你已经差不多能成为一个可以进行增删改查的程序员了!当然,仅仅掌握这些是远远不够的,还需要掌握他的高阶用法!!