网站备案号注销查询,沈阳网站定制,wordpress商城 淘宝客,如何做闲置物品交换的网站MyBatis关联映射-一对一
1.1 实体关系 实体–数据实体#xff0c;实体关系指的就是数据与数据之间的关系 例如#xff1a;订单和商品#xff0c;用户和角色 实体关系分为以下四种#xff1a;
**一对一关联#xff1a;**用户表和用户详情表 数据表关系#xff1a; 主键关…MyBatis关联映射-一对一
1.1 实体关系 实体–数据实体实体关系指的就是数据与数据之间的关系 例如订单和商品用户和角色 实体关系分为以下四种
**一对一关联**用户表和用户详情表 数据表关系 主键关联用户表主键和用户详情表的主键相同时表示是匹配的数据 唯一外键关联用户表和详情表使用外键关联但需要将外键设置为唯一键 一对多关联or多对一关联 一对多班级对学生 多对一学生对班级 数据表关系 在多的一段添加外键和一的一段进行关联 多对多关联 用户和角色、订单和商品 数据表关系建立第三张关系表添加两个外键分别和两张表主键进行关联 用户表用户角色表角色表user_id外键FK: uiduser_id ridrole_idrole_id
1.2 一对一关联 实例用户表user–用户详情表 userDetail 1.2.1 创建数据表
-- 用户信息表
CREATE TABLE user(user_id int PRIMARY KEY auto_increment,user_name VARCHAR(20) NOT NULL UNIQUE,user_pwd VARCHAR(20) NOT NULL,user_realname VARCHAR(20) NOT NULL,user_img VARCHAR(100) NOT NULL
);-- 用户详情表
CREATE TABLE userDetail(detail_id INT PRIMARY KEY auto_increment,user_addr VARCHAR(50) NOT NULL,user_tel CHAR(11) NOT NULL,user_desc VARCHAR(200),uid INT NOT null UNIQUE
);1.2.2 创建工具类
MyBatisUtils
package com.feng.utils;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;/*** program: mybatis_project2* description: MyBatis工具类* author: FF* create: 2024-11-28 18:52**/
public class MyBatisUtils {private static SqlSessionFactory sqlSessionFactory;//为SqlSession加锁private static final ThreadLocalSqlSession sqlSessionlock new ThreadLocal();static {InputStream inputStream null;try {//构建sqlSession工厂inputStream Resources.getResourceAsStream(mybatis-config.xml);SqlSessionFactoryBuilder builder new SqlSessionFactoryBuilder();sqlSessionFactory builder.build(inputStream);inputStream.close();} catch (IOException e) {throw new RuntimeException(e);}}/*** Description: 通过sqlSessionFactory获取sqlSession对象private不对外* Param: [isAutoCommit]* return: org.apache.ibatis.session.SqlSession* Author: FF* Date: 2024/11/27*/private static SqlSession getSqlSession(boolean isAutoCommit) {SqlSession sqlSession sqlSessionlock.get();if (sqlSession null) {sqlSession sqlSessionFactory.openSession(isAutoCommit);sqlSessionlock.set(sqlSession);}return sqlSession;}/*** Description: 获取sqlSession对象默认手动提交事务public对外* Param: []* return: org.apache.ibatis.session.SqlSession* Author: FF* Date: 2024/11/27*/public static SqlSession getSqlSession() {return getSqlSession(false);}/*** Description: 提供getMapper方法获取mapper代理对象默认自动提交事务* Param: [c]* return: T* Author: FF* Date: 2024/11/27*/public static T extends Object T getMapper(ClassT c) {return getSqlSession(true).getMapper(c);}
}1.2.3 创建实体类
package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;/*** program: mybatis_project2* description: 用户实体类* author: FF* create: 2024-11-28 18:56**/
Data
AllArgsConstructor
NoArgsConstructor
ToString
public class User {private int userID;private String userName;private String password;private String realName;private String img;private UserDetail userDetail;
}
package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;/*** program: mybatis_project2* description: 用户详情实体类* author: FF* create: 2024-11-28 18:57**/
Data
AllArgsConstructor
NoArgsConstructor
ToString
public class UserDetail {public int detailId;public String userAddr;public String userTel;public String userDesc;public int uid;
}
1.2.4 创建Dao接口
package com.feng.dao;import com.feng.pojo.User;import java.util.List;public interface UserDao {public int addUser(User user);public ListUser selectAllUser();public User selectUserByUserName(String userName);public User selectUserByRealName(String realName);
}package com.feng.dao;import com.feng.pojo.UserDetail;public interface UserDetailDao {public int insertUserDetail(UserDetail userDetail);public UserDetail selectUserDetailByUid(int Uid);}1.2.5 创建MyBatis主配置文件
mybatis-config.xml
?xml version1.0 encodingUTF-8?
!DOCTYPE configuration PUBLIC -//mybatis.org//DTD Config 3.0//EN http://mybatis.org/dtd/mybatis-3-config.dtd
configurationproperties resourcejdbc.properties/typeAliasestypeAlias typecom.feng.pojo.User aliasUser/typeAlias typecom.feng.pojo.UserDetail aliasUserDetail//typeAliasesenvironments defaultmysqlenvironment idmysqltransactionManager typeJDBC/dataSource typePOOLEDproperty namedriver value${mysql_driver}/property nameurl value${mysql_url}/property nameusername value${mysql_username}/property namepassword value${mysql_password}//dataSource/environment/environmentsmappersmapper resourcemappers/userMapper.xml/mapper resourcemappers/userDetailMapper.xml//mappers
/configuration1.2.6 创建jdbc.properties
mysql_drivercom.mysql.jdbc.Driver
mysql_urljdbc:mysql://localhost:3306/db_test?characterEncodingutf-8
mysql_usernameroot
mysql_passwordroot1.2.7 创建Dao映射文件
useMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.feng.dao.UserDaoresultMap idUser typeUserid columnuser_id propertyuserID/result columnuser_name propertyuserName/result columnuser_pwd propertypassword/result columnuser_realname propertyrealName/result columnuser_img propertyimg/!--子查询通过主表userID关联到子表uid查询出子表信息赋值给User.userDetail--association propertyuserDetail selectcom.feng.dao.UserDetailDao.selectUserDetailByUid columnuser_id/!--result columndetail_id propertyuserDetail.detailId/result columnuser_addr propertyuserDetail.userAddr/result columnuser_tel propertyuserDetail.userTel/result columnuser_desc propertyuserDetail.userDesc/result columnuid propertyuserDetail.uid/--/resultMapinsert idaddUser useGeneratedKeystrue keyPropertyuserIDinsert into user(user_name,user_pwd,user_realname,user_img)values (#{userName},#{password},#{realName},#{img})/insertselect idselectAllUser parameterTypeint resultMapUserSELECT *FROM user/selectselect idselectUserByUserName resultMapUserselect * from user u inner join userDetail d on u.user_id d.detail_id where user_name #{userName}/selectselect idselectUserByRealName resultMapUser select * from user where user_realname #{realName}/select
/mapperuserDetailMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.feng.dao.UserDetailDaoresultMap idUserDetail typeUserDetailid columndetail_id propertydetailId/result columnuser_addr propertyuserAddr/result columnuser_tel propertyuserTel/result columnuser_desc propertyuserDesc/result columnuid propertyuid//resultMapinsert idinsertUserDetailinsert into userDetail(user_addr,user_tel,user_desc,uid)values (#{userAddr},#{userTel},#{userDesc},#{uid})/insertselect idgetById SELECT *FROM userDetailWHERE id #{id}/selectselect idselectUserDetailByUid resultMapUserDetailselect * from userDetail where uid #{Uid}/select
/mapper1.2.8 创建测试类
package com.feng.dao;import com.feng.pojo.User;
import com.feng.pojo.UserDetail;
import com.feng.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class UserDaoTest {Testpublic void selectAllUser() {UserDao mapper MyBatisUtils.getMapper(UserDao.class);ListUser list mapper.selectAllUser();for (User user : list) {System.out.println(user);}}Testpublic void addUser() {SqlSession sqlSession MyBatisUtils.getSqlSession();try {UserDao mapper sqlSession.getMapper(UserDao.class);User user new User();user.setUserName(admin2);user.setPassword(123456);user.setRealName(关羽);user.setImg(123);int i mapper.addUser(user);System.out.println(i);System.out.println(userID user.getUserID());int uid user.getUserID();UserDetail userDetail new UserDetail(0, 北京市延庆区, 134113311, test, uid);UserDetailDao userDetailDao sqlSession.getMapper(UserDetailDao.class);userDetailDao.insertUserDetail(userDetail);sqlSession.commit();}catch (Exception e){e.printStackTrace();sqlSession.rollback();}}Testpublic void selectUserByUserName() {UserDao mapper MyBatisUtils.getMapper(UserDao.class);User user mapper.selectUserByUserName(admin);System.out.println(user);}Testpublic void selectUserByRealName() {User user MyBatisUtils.getMapper(UserDao.class).selectUserByRealName(夏侯惇);System.out.println(user);}
}package com.feng.dao;import com.feng.pojo.UserDetail;
import com.feng.utils.MyBatisUtils;
import org.junit.Test;import static org.junit.Assert.*;public class UserDetailDaoTest {Testpublic void insertUserDetail() {UserDetailDao userDetailDao MyBatisUtils.getMapper(UserDetailDao.class);int i userDetailDao.insertUserDetail(new UserDetail(0,北京市昌平区,13566666,test,2));System.out.println(i);}Testpublic void selectUserDetailByUid() {UserDetailDao userDetailDao MyBatisUtils.getMapper(UserDetailDao.class);UserDetail userDetail userDetailDao.selectUserDetailByUid(2);System.out.println(userDetail);}
}1.3 一对多关联
1.3.1 创建数据表 班级对学生一个多级对多个学生关联 -- 创建班级表
CREATE TABLE classes(cid int PRIMARY KEY auto_increment,cname VARCHAR(30) NOT NULL UNIQUE,cdesc VARCHAR(100)
);-- 创建学生表
CREATE TABLE student(sid int PRIMARY KEY auto_increment,sname VARCHAR(20) NOT NULL,sage INT NOT NULL,scid int NOT NULL
);1.3.2 创建实体类
package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;/*** program: mybatis_project* description: 班级表实体类* author: FF* create: 2024-11-29 15:52**/
Data
AllArgsConstructor
NoArgsConstructor
ToString
public class Clazz {private int cid;private String cname;private String cdesc;
}
package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;/*** program: mybatis_project* description: 学生表实体类* author: FF* create: 2024-11-29 15:54**/
Data
AllArgsConstructor
NoArgsConstructor
ToString
public class Student2 {private int sid;private String sname;private int sage;private int scid;
}
1.3.3 关联查询 当查询一个班级时要关联查询出这个班级下的所有学生 连接查询
classMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.feng.dao.ClassDaoresultMap idclassMap typeclassid columncid propertycid/result columncname propertycname/result columncdesc propertycdesc/!--Clazz对象的student2List是个List集合需要使用collection--!--Collection标签的ofType属性声明集合中元素的类型--collection propertystudent2List ofTypeStudent2result columnsid propertysid/result columnsname propertysname/result columnsage propertysage//collection/resultMapselect idqueryClassInfo resultMapclassMapselect * from classes c INNER JOIN student s on c.cid s.scid where c.cid #{cid}/select
/mapper子查询
classMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.feng.dao.ClassDaoresultMap idclassMap typeClazzid columncid propertycid/result columncname propertycname/result columncdesc propertycdesc/!--连接查询--!-- collection propertystudentList ofTypeStudentresult columnsid propertysid/result columnsname propertysname/result columnsage propertysage//collection--!--子查询--collection propertystudentList selectcom.feng.dao.StudentDao.getStudentByScid columncidid columnsid propertysid/result columnsname propertysid/result columnsage propertysage/result columnscid propertyscid//collection/resultMap!--select * from classes c inner join student s on c.cid s.scid where c.cid #{cid} --select idqueryClassInfo resultMapclassMapselect * from classes c where c.cid #{cid}/select/mapper1.4 多对一 通过学生id查询学生所在班级 连接查询
StudentMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.feng.dao.Student2DaoresultMap idstudent2Map typeStudent2id columnsid propertysid/result columnsname propertysname/result columnsage propertysage/result columnscid propertyscid/result columncid propertyclazz.cid/result columncname propertyclazz.cname/result columncdesc propertyclazz.cdesc//resultMapselect idqueryStudent2 resultMapstudent2Mapselect * from student s INNER JOIN classes c ON s.scid c.cid where s.sid #{sid}/select/mapper子查询
studentMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.feng.dao.Student2DaoresultMap idstudent2Map typeStudent2id columnsid propertysid/result columnsname propertysname/result columnsage propertysage/result columnscid propertyscid/
!-- result columncid propertyclazz.cid/--
!-- result columncname propertyclazz.cname/--
!-- result columncdesc propertyclazz.cdesc/--!--子查询--association propertyclazz selectcom.feng.dao.ClassDao.queryClassInfo columnscid//resultMapselect idqueryStudent2 resultMapstudent2Mapselect * from student s where s.sid #{sid}/select/mapper1.4 多对多关联
1.4.1 数据准备
-- 课程表
CREATE TABLE courses(course_id int PRIMARY KEY auto_increment,course_name VARCHAR(50) not null
);-- 选课信息表/成绩表
CREATE TABLE greades(sid char(5) not NULL,course_id INT NOT NULL,score INT NOT NULL
);1.4.2 关联查询 查询学生时同时查询出学生选择的课程 连接查询
Student实体类
package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;import java.util.List;/*** program: mybatis_project* description: 学生表实体类* author: FF* create: 2024-11-29 15:54**/
Data
AllArgsConstructor
NoArgsConstructor
ToString
public class Student {private int sid;private String sname;private int sage;private int scid;private ListCourses coursesList;
}StudentMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.feng.dao.StudentDaoresultMap idStudentMap typeStudentid columnsid propertysid/result columnsname propertysname/result columnsage propertysage/result columnscid propertyscid/collection propertycoursesList ofTypeCoursesresult propertycourseId columncourse_id/result propertycourseName columncourse_name//collection/resultMapselect idgetStudentBySid resultMapStudentMapselect s.sid,s.sname,c.course_id,c.course_name from student s INNER JOIN greades g INNER JOIN courses cON s.sid g.sid AND g.course_id c.course_id where s.sid #{sid};/select
/mapper子查询 StudentMapper.xml ?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.feng.dao.StudentDaoresultMap idStudentMap typeStudentid columnsid propertysid/result columnsname propertysname/result columnsage propertysage/result columnscid propertyscid/!--连接查询--!--collection propertycoursesList ofTypeCoursesresult propertycourseId columncourse_id/result propertycourseName columncourse_name//collection--!--子查询--collection propertycoursesList selectcom.feng.dao.CoursesDao.getCourses columnsidid propertycourseId columncourse_id/result propertycourseName columncourse_name//collection/resultMapselect idgetStudentBySid resultMapStudentMapselect s.sid,s.sname,c.course_id,c.course_name from student s INNER JOIN greades g INNER JOIN courses cON s.sid g.sid AND g.course_id c.course_id where s.sid #{sid};/select
/mapper
