1. 导入依赖，完善pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>MyBatisTest_1</artifactId> <version>1.0-SNAPSHOT</version> <name>MyBatisTest_1</name> <!-- FIXME change it to the project's website --> <url>http://www.example.com</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <dependencies> <!-- mybatis 框架--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.5</version> </dependency> <!--mysql 连接数据库--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency> <!-- 日志 --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.12</version> </dependency> <!-- junit 测试--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> </dependencies> <build> <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) --> <plugins> <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle --> <plugin> <artifactId>maven-clean-plugin</artifactId> <version>3.1.0</version> </plugin> <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging --> <plugin> <artifactId>maven-resources-plugin</artifactId> <version>3.0.2</version> </plugin> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.8.0</version> </plugin> <plugin> <artifactId>maven-surefire-plugin</artifactId> <version>2.22.1</version> </plugin> <plugin> <artifactId>maven-jar-plugin</artifactId> <version>3.0.2</version> </plugin> <plugin> <artifactId>maven-install-plugin</artifactId> <version>2.5.2</version> </plugin> <plugin> <artifactId>maven-deploy-plugin</artifactId> <version>2.8.2</version> </plugin> <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle --> <plugin> <artifactId>maven-site-plugin</artifactId> <version>3.7.1</version> </plugin> <plugin> <artifactId>maven-project-info-reports-plugin</artifactId> <version>3.0.0</version> </plugin> </plugins> </pluginManagement> </build> </project>2.建立UserModel2
package com.itlaobing.model; import java.util.List; /** * @author ：wys * @date ：Created in 2020/9/3 20:28 */ public class UserModel2 { private int id; private String name; private String password; private List<AccountModel> accountModels; public UserModel2() { } public UserModel2(int id, String name, String password, List<AccountModel> accountModelList) { this.id = id; this.name = name; this.password = password; this.accountModels = accountModelList; } 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 String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public List<AccountModel> getAccountModelList() { return accountModels; } public void setAccountModelList(List<AccountModel> accountModelList) { this.accountModels = accountModelList; } @Override public String toString() { return "UserModel2{" + "id=" + id + ", name='" + name + '\'' + ", password='" + password + '\'' + ", accountModelList=" + accountModels + '}'; } }3.建立AccountModel
package com.itlaobing.model; import java.io.Serializable; /** * @author ：wys * @date ：Created in 2020/9/3 20:28 */ public class AccountModel implements Serializable { private int id; private int uid; private double money; private UserModel2 userModel2; public UserModel2 getUserModel2() { return userModel2; } public void setUserModel2(UserModel2 userModel2) { this.userModel2 = userModel2; } public AccountModel() { } public AccountModel(int id, int uid, double money) { this.id = id; this.uid = uid; this.money = money; } public int getId() { return id; } public void setId(int id) { this.id = id; } public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public double getMoney() { return money; } public void setMoney(double money) { this.money = money; } @Override public String toString() { return "AccountModel{" + "id=" + id + ", uid=" + uid + ", money=" + money + ", name=" + userModel2.getName() + '}'; } }4.创建IUserInfoDao（接口）
//这里省略其他，因为有其他的方法 /** * 实现一对多查询 * @return */ List<UserModel2> findOneToMany(); /** * 实现一对一查询 * @return */ List<AccountModel> findOneToOne();5.创建SqlMapConfig.xml（用来连接数据库）
<?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> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/>//这里我是mysql8以上的版本 <property name="url" value="jdbc:mysql://localhost:3306/userinfo?serverTimezone=UTC"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <mappers> <mapper resource="IUserInfoDao.xml"/> </mappers> </configuration>6.创建IUserInfoDao.xml (写sql语句)
<resultMap id="findOneToMany_Mapper" type="com.itlaobing.model.UserModel2"> <id column="id" property="id"></id> <result column="name" property="name"></result> <result column="password" property="password"></result> <!-- one to many--> <collection property="accountModels" ofType="com.itlaobing.model.AccountModel"> <id column="aid" property="id"></id> <result column="uid" property="uid"></result> <result column="money" property="money"></result> </collection> </resultMap> <select id="findOneToMany" resultMap="findOneToMany_Mapper"> select u.*, a.id as aid, a.uid, a.money from user u left join account a on u.id = a.uid; </select> <resultMap id="findOneToOne_Mapper" type="com.itlaobing.model.AccountModel"> <id column="id" property="id"></id> <result column="uid" property="uid"></result> <result column="money" property="money"></result> <!-- one to one--> <association property="userModel2" javaType="com.itlaobing.model.UserModel2"> <id column="uid" property="id"></id> <result column="name" property="name"></result> <result column="password" property="password"></result> </association> </resultMap> <select id="findOneToOne" resultMap="findOneToOne_Mapper"> select user.name, account.* from user,account where user.id = account.uid; </select>7.创建测试类
package com.itlaobing.dao; import com.itlaobing.model.AccountModel; import com.itlaobing.model.UserModel2; 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.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; import static org.junit.Assert.*; public class IUserInfoDaoTest { SqlSession sqlSession = null; IUserInfoDao iUserInfoDao = null; @Before public void init(){ String resource = "SqlMapConfig.xml"; InputStream inputStream = null; try { inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); iUserInfoDao = sqlSession.getMapper(IUserInfoDao.class); } catch (IOException e) { e.printStackTrace(); } } @Test public void findOneToMany() { List<UserModel2> list = iUserInfoDao.findOneToMany(); for(UserModel2 user : list){ System.out.println(user); } } @Test public void findOneToOne() { List<AccountModel> list = iUserInfoDao.findOneToOne(); for(AccountModel acc : list){ System.out.println(acc); } } }1.User实体类
package com.itlaobing.model; import java.io.Serializable; import java.util.List; /** * @author ：wys * @date ：Created in 2020/9/4 11:21 */ public class User implements Serializable { private Integer id; private String name; private String password; private List<Role> roles; public User() { } public User(Integer id, String name, String password, List<Role> roles) { this.id = id; this.name = name; this.password = password; this.roles = roles; } 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 String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public List<Role> getRoles() { return roles; } public void setRoles(List<Role> roles) { this.roles = roles; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", password='" + password + '\'' + ", roles=" + roles + '}'; } }2.Role实体类
package com.itlaobing.model; import java.io.Serializable; import java.util.List; /** * @author ：wys * @date ：Created in 2020/9/4 11:22 */ public class Role implements Serializable { private Integer id; private String role_name; private String role_desc; private List<User> users; public Role() { } public Role(Integer id, String role_name, String role_desc, List<User> users) { this.id = id; this.role_name = role_name; this.role_desc = role_desc; this.users = users; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getRole_name() { return role_name; } public void setRole_name(String role_name) { this.role_name = role_name; } public String getRole_desc() { return role_desc; } public void setRole_desc(String role_desc) { this.role_desc = role_desc; } public List<User> getUsers() { return users; } public void setUsers(List<User> users) { this.users = users; } @Override public String toString() { return "Role{" + "id=" + id + ", role_name='" + role_name + '\'' + ", role_desc='" + role_desc + '\'' + ", users=" + users + '}'; } }3.IUserInfoDao
/** * 实现多对多查询1 * @return */ List<Role> findManyToMany(); /** * 实现多对多查询2 * @return */ List<User> findManyToMany2();4.IUserInfoDao.xml
<resultMap id="findManyToMany_Mapper" type="com.itlaobing.model.Role"> <id column="id" property="id"/> <result column="role_name" property="role_name"/> <result column="role_desc" property="role_desc"/> <collection property="users" ofType="com.itlaobing.model.User"> <id column="userId" property="id"/> <result column="name" property="name"/> <result column="password" property="password"/> </collection> </resultMap> <select id="findManyToMany" resultMap="findManyToMany_Mapper"> select r.*, u.id userId, u.name, u.password from role r left join user_role ur on r.id = ur.rid left join user u on u.id = ur.uid; </select> <resultMap id="findManyToMany2_Mapper" type="com.itlaobing.model.User"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="password" property="password"/> <collection property="roles" ofType="com.itlaobing.model.Role"> <id column="roleId" property="id"/> <result column="role_name" property="role_name"/> <result column="role_desc" property="role_desc"/> </collection> </resultMap> <select id="findManyToMany2" resultMap="findManyToMany2_Mapper"> select u.*, r.id roleId, r.role_desc, r.role_name from user u left join user_role ur on ur.uid = u.id left join role r on ur.rid = r.id; </select>5.test方法测试
@Test public void findManyToMany() { List<Role> list = iUserInfoDao.findManyToMany(); for(Role role : list){ System.out.println(role); } } @Test public void findManyToMany2() { List<User> list = iUserInfoDao.findManyToMany2(); for(User user : list){ System.out.println(user); } }至此，完成查询。
ps：注意各种属性的对应值，不要弄混了！
