項(xiàng)目結(jié)構(gòu)
數(shù)據(jù)庫表
student_type 表
student 表
依賴
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
實(shí)體類
Student 類
一個(gè)學(xué)生只有一個(gè)年級
package com.tmg.domain;
public class Student {
private int id;
private String name;
private int age;
private String email;
private Integer typeId;
private Type type;
public Integer getTypeId() {
return typeId;
}
public void setTypeId(Integer typeId) {
this.typeId = typeId;
}
public Type getType() {
return type;
}
public void setType(Type type) {
this.type = type;
}
public Student(int id, String name, int age, String email) {
this.id = id;
this.name = name;
this.age = age;
this.email = email;
}
public Student() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", email='" + email + '\'' +
", typeId=" + typeId +
// ", type=" + type +
'}';
}
}
Type 類
一個(gè)年級有多個(gè)學(xué)生，所以用 list
package com.tmg.domain;
import java.util.List;
public class Type {
private Integer id;
private String name;
private List<Student> students;
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<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Type{" +
"id=" + id +
", name='" + name + '\'' +
// ", students=" + students +
'}';
}
}
StudentDao
package com.tmg.dao;
import com.tmg.domain.Student;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface StudentDao {
//多個(gè)參數(shù)的配置
void insertEmp( @Param("stuName") String name,
@Param("stuAge") int age, @Param("stuEmail") String email);
List<Student> selectByStudent(Student student);
void update(Student employee);
void update2(Student employee);
List<Student> selectByIds(@Param("ids") int []id);
// List<Student> selectById(int id);
List<Student> selectByTypeId(int id);
List<Student> selectAll();
}
TypeDao
package com.tmg.dao;
import com.tmg.domain.Type;
import java.util.List;
public interface TypeDao {
List<Type> selectAll();
Type selectById(Integer id);
}
mybatis-config.xml配置數(shù)據(jù)源，日志等
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- dddd-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="ture"/><!--配置下劃線轉(zhuǎn)換為駝峰命名風(fēng)格-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager><!--事務(wù)管理器-->
<dataSource type="POOLED"><!--數(shù)據(jù)源 POOLED代表池化-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="dao/StudentDao.xml"></mapper>
<mapper resource="dao/TypeDao.xml"></mapper>
</mappers>
</configuration>
TypeDao.xml
下列代碼中：
1 resultMap 里面property對應(yīng)實(shí)體類屬性，column對應(yīng)數(shù)據(jù)庫字段名
2 主鍵用 id 標(biāo)簽 其他用result
3 關(guān)聯(lián)查詢(子查詢和連接查詢) 連接查詢查一次
4 一個(gè)年級多個(gè)學(xué)生，所以用collection 如果一對一用association文章來源：http://www.zghlxwxcb.cn/news/detail-809993.html
<?xml version="1.0" encoding="UTF-8" ?>
<!--指定約束文件：定義和限制當(dāng)前文件中可以使用的標(biāo)簽和屬性，以及標(biāo)簽出現(xiàn)的順序
mybatis-3-mapper.dtd 約束文件名稱
-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tmg.dao.TypeDao">
<resultMap id="typeMap" type="com.tmg.domain.Type">
<id property="id" column="type_id"></id>
<result property="name" column="type_name"></result>
<!-- 連接查詢-->
<!-- <collection property="students"-->
<!-- javaType="java.util.List" ofType="com.tmg.domain.Student">-->
<!-- <id property="id" column="stu_id" javaType="java.lang.Integer"></id>-->
<!-- <result property="name" column="stu_name"></result>-->
<!-- <result property="age" column="stu_age"></result>-->
<!-- <result property="email" column="stu_email"></result>-->
<!-- </collection>-->
<!-- 子查詢-->
<collection property="students" column="type_id"
javaType="java.util.List" ofType="com.tmg.domain.Student"
select="com.tmg.dao.StudentDao.selectByTypeId">
</collection>
<!-- property 實(shí)體類中的屬性名 column 子查詢使用的字段 javaType 集合類型 ofType 集合里面的泛型類型-->
</resultMap>
<select id="selectAll" resultMap="typeMap">
select s.*,t.* from student s join student_type t on s.type_id=t.type_id
</select>
<select id="selectById" resultMap="typeMap">
select * from student_type where type_id=#{id}
</select>
</mapper>
StudentDao.xml
動(dòng)態(tài)sql不理解可看以下博客：
https://blog.csdn.net/weixin_57689217/article/details/135707991?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22135707991%22%2C%22source%22%3A%22weixin_57689217%22%7D文章來源地址http://www.zghlxwxcb.cn/news/detail-809993.html
<?xml version="1.0" encoding="UTF-8" ?>
<!--指定約束文件：定義和限制當(dāng)前文件中可以使用的標(biāo)簽和屬性，以及標(biāo)簽出現(xiàn)的順序
mybatis-3-mapper.dtd 約束文件名稱
-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--映射的命名空間 = 包名+接口類-->
<mapper namespace="com.tmg.dao.StudentDao">
<!--配置insert操作 id是方法名 parameterType是參數(shù)類型 #{屬性名}用于讀取對象的屬性值-->
<!--#{}和${}的區(qū)別，#{}相當(dāng)于PreparedStatement的占位符？提前編譯，避免SQL注入 ${}是Statement字符串拼接，不能避免注入 -->
<!--獲得最新的自增主鍵值 useGeneratedKeys=true keyProperty主鍵的屬性-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
insert into student( id,name,age,email) values (#{id},#{name},#{age},#{email});
</insert>
<!-- 問題：查詢出的名稱為多個(gè)單詞的字段出現(xiàn)null值-->
<!-- 原因：數(shù)據(jù)庫的字段單詞以下劃線分隔，Java的屬性以駝峰命名，導(dǎo)致部分名稱不一致無法實(shí)現(xiàn)映射-->
<select id="selectAll" resultMap="student">
select * from student
</select>
<resultMap id="student" type="com.tmg.domain.Student">
<!--配置主鍵 property是java屬性名 column是表字段名-->
<id property="id" column="stu_id" javaType="java.lang.Integer"></id>
<!--普通字段-->
<result property="name" column="stu_name"></result>
<result property="age" column="stu_age"></result>
<result property="email" column="stu_email"></result>
<result property="typeId" column="type_id"></result>
<!-- <association property="type"-->
<!-- javaType="com.tmg.domain.Type">-->
<!-- <id property="id" column="type_id"></id>-->
<!-- <result property="name" column="type_name"></result>-->
<!-- </association>-->
<association property="type" column="type_id"
javaType="com.tmg.domain.Type"
select="com.tmg.dao.TypeDao.selectById">
</association>
</resultMap>
<!-- 動(dòng)態(tài)sql-->
<sql id="mySelect">
select * from student
</sql>
<select id="selectByStudent" parameterType="com.tmg.domain.Student" resultType="com.tmg.domain.Student" resultMap="student">
<include refid="mySelect"></include>
<where>
<if test="name !=null">
stu_name like "%"#{name}"%"
</if>
<if test="age !=null and age!=0">
and stu_age=#{age}
</if>
<if test="email !=null">
and stu_email=#{email}
</if>
</where>
</select>
<update id="update">
update student
<set>
<if test="age !=null and age!=0">
stu_age=#{age},
</if>
<if test="email!=null">
stu_email=#{email},
</if>
<if test="name">
stu_name=#{name},
</if>
</set>
where stu_id=#{id};
</update>
<update id="update2">
update student
<trim prefix="set" suffixOverrides=",">
<if test="age !=null and age!=0">
stu_age=#{age},
</if>
<if test="email!=null">
stu_email=#{email},
</if>
<if test="name">
stu_name=#{name},
</if>
</trim>
where stu_id=#{id};
</update>
<select id="selectByIds" resultMap="student">
select s.*,t.* from student s join student_type t on s.type_id=t.type_id
where stu_id in
<foreach collection="ids" item="id" separator="," open="(" close=")" index="1">
#{id}
</foreach>
</select>
<select id="selectByTypeId" resultMap="student">
<include refid="mySelect"></include> where type_id=#{id}
</select>
</mapper>
TypeDaoText 測試類
package com.tmg.dao;
import com.tmg.domain.Type;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.util.List;
public class TypeDaoText {
@Test
public void testselectAll() throws IOException {
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = build.openSession();
TypeDao mapper = sqlSession.getMapper(TypeDao.class);
List<Type> typeList = mapper.selectAll();
for (Type type : typeList) {
System.out.println(type);
}
}
@Test
public void testselectById() throws IOException {
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = build.openSession();
TypeDao mapper = sqlSession.getMapper(TypeDao.class);
Type type = mapper.selectById(1);
System.out.println(type);
}
}
StudentDaoText 測試類
package com.tmg.dao;
import com.tmg.domain.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.util.List;
public class StudentDaoText {
@Test
public void testinsertEmp() throws IOException {
SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = factoryBuilder.build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = factory.openSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
mapper.insertEmp("tmg",18,"tmg@qq.com");
sqlSession.commit();
}
@Test
public void testselectByStudent() throws IOException {
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = build.openSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
// student.setName("z");
// student.setAge(18);
// student.setEmail("tmg@qq.com");
List<Student> students = mapper.selectByStudent(student);
for (Student student1 : students){
System.out.println(student1);
}
}
@Test
public void testupdate() throws IOException {
//創(chuàng)建會(huì)話工廠構(gòu)建器
SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory build = factoryBuilder.build(Resources.getResourceAsStream("mybatis-config.xml"));
//創(chuàng)建會(huì)話
SqlSession sqlSession = build.openSession();
//獲得Mapper對象
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
// student.setName();
student.setId(1);
student.setAge(22);
mapper.update(student);
sqlSession.commit();
}
@Test
public void testupdate2() throws IOException {
SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory build = factoryBuilder.build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = build.openSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
student.setId(1);
student.setAge(44);
mapper.update2(student);
sqlSession.commit();
}
@Test
public void testselectByIds() throws IOException {
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = build.openSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
int []a={1};
List<Student> students = mapper.selectByIds(a);
for (Student student:students){
System.out.println(student);
System.out.println(student.getType());
}
}
@Test
public void testselectAll() throws IOException {
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = build.openSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List<Student> students = mapper.selectAll();
for(Student student : students){
System.out.println(student);
System.out.println(student.getType());
}
}
}
到了這里，關(guān)于mybatis xml多表查詢，子查詢，連接查詢，動(dòng)態(tài)sql的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
