mybatis框架之多表关联关系(基于注解方式)
表与表之间都有哪些关系?
- 一对一关系: 一个人对应一个身份证
- 一对多关系: 一个省份对应多个城市
- 多对多关系: 一个用户可以有多种角色,一个角色可以有多个用户
代码示例
- maven依赖
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
一对一关系示例
-
person表结构
-
Card表结构
-
entity层的实体类
- person表映射的实体类Person类:
/*这是实现一对多关系的entity层 * 这是数据库表Person表映射的实体类Person * 里面有对应数据库表中字段的属性值, * 以及为实现一对一关系, * 添加一个card对象属性,一个人对应一个卡 * 并生成它们的set get 和tostring方法 * */ public class Person { private Integer pid; private String pname; //作为person的外键 private Integer cid; //验证一对一关系,一个person对应一个卡 private Card card; public Integer getPid() { return pid; } public void setPid(Integer pid) { this.pid = pid; } public String getPname() { return pname; } public void setPname(String pname) { this.pname = pname; } public Integer getCid() { return cid; } public void setCid(Integer cid) { this.cid = cid; } public Card getCard() { return card; } public void setCard(Card card) { this.card = card; } @Override public String toString() { return "Person{" + "pid=" + pid + ", pname='" + pname + '\'' + ", cid=" + cid + ", card=" + card + '}'; } }
- card表对应的实体类Card类
/*这是实现一对多关系的entity层 * 这是数据库表Card表映射的实体类Card * 里面有对应数据库表中字段的属性值, * 并生成它们的set get 和tostring方法 * */ public class Card { private Integer cid; private String cname; public Integer getCid() { return cid; } public void setCid(Integer cid) { this.cid = cid; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } @Override public String toString() { return "Card{" + "cid=" + cid + ", cname='" + cname + '\'' + '}'; } }
-
dao层的规范接口
-
PersonDao接口
import com.wdhcr.onetoone.entity.Person; import org.apache.ibatis.annotations.One; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; public interface PersonDao { @Select("select * from person where pid = #{pid}") @Results({ @Result(id = true ,column = "pid" ,property = "pid"), @Result(column = "pname" ,property = "pname"), //根据cid的 值去card表中查找对应的卡,返回回来 @Result(column = "cid" ,property = "card" ,one = @One(select = "com.wdhcr.onetoone.dao.CardDao.getCardById")) }) public Person getPersonById(int pid); }
- CardDao接口
import com.wdhcr.onetoone.entity.Card; import org.apache.ibatis.annotations.Select; public interface CardDao { @Select("select * from card where cid = #{cid}") public Card getCardById(int cid); }
-
mybatis框架的全局核心配置文件,注意修改你的库,和用户名密码,添加对应的接口
<?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="dev"> <environment id="dev"> <!-- 设置事务类型--> <transactionManager type="JDBC"></transactionManager> <!-- 设置数据源类型--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/库"/> <property name="username" value="用户名"/> <property name="password" value="密码"/> </dataSource> </environment> </environments> <!-- 注入你的映射文件--> <mappers> <!--一对一关系,使用注解方法--> <mapper class="com.wdhcr.onetoone.dao.CardDao"></mapper> <mapper class="com.wdhcr.onetoone.dao.PersonDao"></mapper> </mappers> </configuration>
-
测试类代码
import com.wdhcr.onetoone.dao.PersonDao; import com.wdhcr.onetoone.entity.Person; 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 java.io.IOException; import java.io.InputStream; public class Test { public static void main(String[] args) throws IOException { //加载全局配置文件 InputStream resourceAsStream = Resources.getResourceAsStream("ConfigMapper.xml"); //船舰sqlsession工厂 SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream); //获取session SqlSession sqlSession = build.openSession(); PersonDao mapper = sqlSession.getMapper(PersonDao.class); Person personById = mapper.getPersonById(1); //因为生成了tostring方法所以直接打印 System.out.println(personById); } }
一对多关系
-
表结构
- Provinces省份表结构
- city表结构
- Provinces省份表结构
-
entity层的实体类
-
Provinces表映射的Provinces实体类
import java.util.Set; /*这是实现一对多关系的entity层 * 这是数据库表Provinces省份表映射的实体类Provinces * 里面有对应数据库表中字段的属性值, * 以及为实现一对多关系,一个省对应多个城市, * 添加一个类型为city的集合,存储省份里对应有的城市 * 并生成它们的set get 和tostring方法 * */ public class Provinces { private Integer pid; private String pname; private Set<City> cities; public Integer getPid() { return pid; } public void setPid(Integer pid) { this.pid = pid; } public String getPname() { return pname; } public void setPname(String pname) { this.pname = pname; } public Set<City> getCities() { return cities; } public void setCities(Set<City> cities) { this.cities = cities; } @Override public String toString() { return "Provinces{" + "pid=" + pid + ", pname='" + pname + '\'' + ", cities=" + cities + '}'; } }
- city表映射的City实体类
/*这是实现一对多关系的entity层 * 这是数据库表city城市表映射的实体类City * 里面有对应数据库表中字段的属性值, * 并生成它们的set get 和tostring方法 * */ public class City { private Integer cid; private String cname; private Integer pid; public Integer getCid() { return cid; } public void setCid(Integer cid) { this.cid = cid; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } public Integer getPid() { return pid; } public void setPid(Integer pid) { this.pid = pid; } @Override public String toString() { return "City{" + "cid=" + cid + ", cname='" + cname + '\'' + ", pid=" + pid + '}'; } }
-
dao层的规范接口
-
ProvincesDao接口
import com.wdhcr.onetomany.entity.Provinces; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; public interface ProvincesDao { @Select("select * from Provinces where pid = #{pid}") @Results({ @Result(id = true,column = "pid" , property = "pid"), @Result(column = "pname" , property = "pname"), //根据pid去city表中找pid对应的所有城市,放在一个set集合中,返回回来 @Result(column = "pid" ,property = "cities" ,many = @Many(select = "com.wdhcr.onetomany.dao.CityDao.getCityByid")) }) public Provinces getProvincesById(int pid); }
-
CityDao接口
import com.wdhcr.onetomany.entity.City; import org.apache.ibatis.annotations.Select; import java.util.Set; public interface CityDao { @Select("select * from city where pid = #{pid}") public Set<City> getCityByid(int pid); }
-
-
mybatis全局核心配置文件
<?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="dev"> <environment id="dev"> <!-- 设置事务类型--> <transactionManager type="JDBC"></transactionManager> <!-- 设置数据源类型--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/库"/> <property name="username" value="用户名"/> <property name="password" value="密码"/> </dataSource> </environment> </environments> <!-- 注入你的映射文件--> <mappers> <!--一对多--> <mapper class="com.wdhcr.onetomany.dao.ProvincesDao"></mapper> <mapper class="com.wdhcr.onetomany.dao.CityDao"></mapper> </mappers> </configuration>
-
测试类代码
import com.wdhcr.onetomany.dao.ProvincesDao; import com.wdhcr.onetomany.entity.City; import com.wdhcr.onetomany.entity.Provinces; 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 java.io.IOException; import java.io.InputStream; /* * 这是实现一对一关系的测试类 * */ public class Test { public static void main(String[] args) throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("ConfigMapper.xml"); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = build.openSession(); ProvincesDao mapper = sqlSession.getMapper(ProvincesDao.class); Provinces provincesById = mapper.getProvincesById(1); System.out.println(provincesById.getPname()); for (City city : provincesById.getCities()) { System.out.println(city); } } }
多对多关系
-
表结构
- useres表结构
- role表结构
- 中间表u_r结构
- useres表结构
-
entity层的实体类
-
useres表映射的Useres实体类
import java.util.Set; public class Useres { private Integer uid; private String uname; //每个用户有多个角色 private Set<Role> roles; public Integer getUid() { return uid; } public void setUid(Integer uid) { this.uid = uid; } public String getUname() { return uname; } public void setUname(String uname) { this.uname = uname; } public Set<Role> getRoles() { return roles; } public void setRoles(Set<Role> roles) { this.roles = roles; } @Override public String toString() { return "Useres{" + "uid=" + uid + ", uname='" + uname + '\'' + ", roles=" + roles + '}'; } }
- role表映射的实体类Role
import java.util.Set; public class Role { private Integer rid; private String rname; //每个角色有多个用户 private Set<Useres> useres; public Integer getRid() { return rid; } public void setRid(Integer rid) { this.rid = rid; } public String getRname() { return rname; } public void setRname(String rname) { this.rname = rname; } public Set<Useres> getUseres() { return useres; } public void setUseres(Set<Useres> useres) { this.useres = useres; } @Override public String toString() { return "Role{" + "rid=" + rid + ", rname='" + rname + '\'' + ", useres=" + useres + '}'; } }
-
-
dao层的规范接口
-
RoleDao接口
import com.wdhcr.manytomany.entity.Role; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import java.util.Set; public interface RoleDao { //根据用户的id查询它所有的角色,以一个set集合的方式返回 @Select("select * from role where rid in (select r_id from u_r where u_id = #{uid})") public Set<Role> getRoleById(int uid); //查询每个角色对应的用户有哪些 @Select("select * from role where rid = #{rid}") @Results({ @Result(id = true,column = "rid",property = "rid"), @Result(column = "rname",property = "rname"), @Result(column = "rid",property = "useres",many = @Many(select = "com.wdhcr.manytomany.dao.UseresDao.getUseresByRid")) }) public Role getRolebyCid(int cid); }
-
UseresDaos接口
import com.wdhcr.manytomany.entity.Useres; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import java.util.Set; public interface UseresDao { //查询指定用户对应的个字段的值,以及它的多个角色 @Select("select * from useres where uid = #{uid}") @Results({ @Result(id = true,column = "uid" ,property = "uid"), @Result(column = "uname",property = "uname"), @Result(column = "uid", property = "roles",many = @Many(select = "com.wdhcr.manytomany.dao.RoleDao.getRoleById")) }) public Useres getUserById(int uid); //根据指定的jueseid查询都有哪些用户是个角色 @Select("select * from useres where uid in (select u_id from u_r where r_id = #{cid})") public Set<Useres> getUseresByRid(int cid); }
-
-
全局核心配置文件
`````` <?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="dev"> <environment id="dev"> <!-- 设置事务类型--> <transactionManager type="JDBC"></transactionManager> <!-- 设置数据源类型--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/test"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <!-- 注入你的映射文件--> <mappers> <!--多对多关系--> <mapper class="com.wdhcr.manytomany.dao.RoleDao"></mapper> <mapper class="com.wdhcr.manytomany.dao.UseresDao"></mapper> </mappers>
-
测试类代码
import com.wdhcr.manytomany.dao.RoleDao; import com.wdhcr.manytomany.dao.UseresDao; import com.wdhcr.manytomany.entity.Role; import com.wdhcr.manytomany.entity.Useres; 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 java.io.IOException; import java.io.InputStream; import java.util.Set; /* * 这是实现多对多关系的测试类 * */ public class Test { public static void main(String[] args) throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("ConfigMapper.xml"); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = build.openSession(); //一个用户对应多个角色 UseresDao mapper = sqlSession.getMapper(UseresDao.class); Useres userById = mapper.getUserById(1); Set<Role> roles = userById.getRoles(); System.out.println(userById.getUid()+"\t"+userById.getUname()); for (Role role : roles) { System.out.println(role); } //一个角色对应多个用户 RoleDao mapper1 = sqlSession.getMapper(RoleDao.class); Role rolebyCid = mapper1.getRolebyCid(2); Set<Useres> useres = rolebyCid.getUseres(); System.out.println(rolebyCid.getRid()+"\t"+rolebyCid.getRname()); for (Useres usere : useres) { System.out.println(useres); } } }
-
好了,以上就是使用注解的方式实现一对一,一对多,多对多关系