步骤
- 建立两张表:用户表。账户表
- 让用户表和账户表之间具备一对多的关系:需要使用外键在账户表中添加
- 建立两个实体类:用户实体类和账户实体类
- 让用户和账户的实体类能体现出一对多的关系
- 建立两个配置文件
- 用户配置文件
- 账户配置文件
- 实现配置
- 当我们查询用户时,可以同时得到用户下所包含的账户信息
- 当我们查询账户时,可以同时得到账户的所属用户信息。
建立用户表和账户表
- 建立用户表
create table mybatisuser(
id int(11) not null auto_increment,
username varchar(32) not null comment '用户名称',
birthday datetime default null comment '生日',
sex char(1) default null comment '性别',
address varchar(256) default null comment '地址',
primary key (id)
)engine=innoDB default charset=utf8;
- 建立账户表
DROP TABLE IF EXISTS ACCOUNT;
CREATE TABLE ACCOUNT(
id INT(11) NOT NULL COMMENT '编号',
uid INT(11) DEFAULT NULL COMMENT '用户编号',
money DOUBLE DEFAULT NULL COMMENT '金额',
PRIMARY KEY (id),
KEY FK_Reference_8 (uid),
CONSTRAINT FK_Reference_8 FOREIGN KEY (uid) REFERENCES mybatisuser (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
- 查询所有账户同时包含用户名和地址
- 创建实体类
public class AccountUser extends Account { private String username; private String address; public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return super.toString() + "AccountUser{" + "username='" + username + '\'' + ", address='" + address + '\'' + '}'; } }
- sql语句
<select id="findAllAccount" resultType="domain.AccountUser"> SELECT a.*,u.username,u.address FROM ACCOUNT a, mybatisuser u WHERE a.uid=u.id; </select>
一对一查询
- 从表实体类应该包含一个主表实体类的对象引用
public class Account implements Serializable {
private int id;
private int uid;
private double money;
//从表实体类应该包含一个主表实体类的对象引用
private MybatisUser mybatisUser;
public MybatisUser getMybatisUser() {
return mybatisUser;
}
public void setMybatisUser(MybatisUser mybatisUser) {
this.mybatisUser = mybatisUser;
}
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 "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}';
}
}
- 定义封装account和user的resultMap
<resultMap id="accountUserMap" type="domain.Account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--一对一的关系映射,配置封装user的内容-->
<association property="mybatisUser" column="uid">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</association>
</resultMap>
- sql语句
<select id="findAll" resultMap="accountUserMap">
SELECT u.*,a.id as aid,a.uid,a.money FROM ACCOUNT a, mybatisuser u WHERE a.uid=u.id;
</select>
- 编写方法
public void testFindAll(){
//5. 使用代理对象执行方法
List<Account> accounts = accountDao.findAll();
for (Account account : accounts){
System.out.println("账户"+account.getId()+"----------------------------");
System.out.println(account);
System.out.println(account.getMybatisUser());
}
}
- 查询结果
一对多查询
- 一对多映射,主表实体类应包含从表实体类的集合引用
public class UserAccount implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
//一对多映射,主表实体类应包含从表实体类的集合引用
private List<Account> accounts;
public List<Account> getAccounts() {
return accounts;
}
public void setAccounts(List<Account> accounts) {
this.accounts = accounts;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return username;
}
public void setName(String name) {
this.username = name;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "MybatisUser{" +
"id=" + id +
", name='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
- 定义UserAccount的resultMap
<!-- 定义UserAccount的resultMap-->
<resultMap id="userAccountMap" type="domain.UserAccount">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<!--一对多的关系映射,配置UserAccount对象中accounts集合的内容-->
<collection property="accounts" ofType="domain.Account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
</collection>
</resultMap>
- sql语句
<select id="findAll" resultMap="userAccountMap">
SELECT u.*,a.id as aid,a.uid,a.money FROM mybatisuser u left outer join account a on u.id=a.uid;
</select>
- 编写查询方法
public void testFindAll(){
//5. 使用代理对象执行方法
List<UserAccount> users = userDao.findAll();
for (UserAccount user : users){
System.out.println("用户"+user.getId()+"--------------------");
System.out.println(user);
System.out.println(user.getAccounts());
}
}
- 查询结果
多对多查询
-
步骤
- 建立两张表:用户表,角色表
- 让用户表和账户表之间具备多对多的关系:需要使用中间表,中间表包含各自的主键,在中间表中是外键。
- 建立两个实体类:用户实体类和角色实体类
- 让用户和角色的实体类能体现出多对多的关系
- 各自包含对方一个集合引用
- 建立两个配置文件
- 用户配置文件
- 角色配置文件
- 实现配置
- 当我们查询用户时,可以同时得到用户所包含的角色信息
- 当我们查询账户时,可以同时得到角色所赋予的用户信息
- 建立两张表:用户表,角色表
-
建表
- 建立角色表
DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `ID` INT(11) NOT NULL COMMENT '编号', `ROLE_NAME` VARCHAR(30) DEFAULT NULL COMMENT '角色名称', `ROLE_DESC` VARCHAR(60) DEFAULT NULL COMMENT '角色描述', PRIMARY KEY (`ID`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) VALUES (1,'系主任','管理整个系'),(2,'院长','管理整个学院'),(3,'校长','管理整个学校');
- 建立中间表
DROP TABLE IF EXISTS `user_role`; CREATE TABLE `user_role` ( `UID` INT(11) NOT NULL COMMENT '用户编号', `RID` INT(11) NOT NULL COMMENT '角色编号', PRIMARY KEY (`UID`,`RID`), KEY `FK_Reference_10` (`RID`), CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `role` (`ID`), CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `mybatisuser` (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO `user_role`(`UID`,`RID`) VALUES (2,1),(4,1),(2,2),(5,3),(6,1),(1,2),(4,2);
-
查询角色,同时得到该角色下的所有用户
- 创建角色实体类,同时包含用户的集合
public class RoleUser implements Serializable { private Integer roleId; private String roleName; private String roleDesc; //生成多对多关系 private List<UserRole> userRoles; public List<UserRole> getUserRoles() { return userRoles; } public void setUserRoles(List<UserRole> userRoles) { this.userRoles = userRoles; } public Integer getRoleId() { return roleId; } public void setRoleId(Integer roleId) { this.roleId = roleId; } public String getRoleName() { return roleName; } public void setRoleName(String roleName) { this.roleName = roleName; } public String getRoleDesc() { return roleDesc; } public void setRoleDesc(String roleDesc) { this.roleDesc = roleDesc; } @Override public String toString() { return "RoleUser{" + "roleId=" + roleId + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } }
- 创建RoleUserDao接口
public interface RoleUserDao { List<RoleUser> findAll(); }
- 定义resultMap
<resultMap id="roleUserMap" type="domain.RoleUser"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> <!--多对多的关系映射,配置封装user的内容--> <collection property="userRoles" ofType="domain.UserRole"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="birthday" column="birthday"></result> <result property="sex" column="sex"></result> <result property="address" column="address"></result> </collection> </resultMap>
- sql语句
<select id="findAll" resultMap="roleUserMap"> SELECT u.*,r.id as rid,r.role_name,r.role_desc FROM role r left outer join user_role ur on r.id=ur.rid left outer join mybatisuser u on u.id=ur.uid; </select>
- 编写方法
public class RoleUserTest { public static void main(String[] args) throws IOException { } private InputStream in; private SqlSession sqlSession; private RoleUserDao roleUserDao; @Before public void init() throws IOException { //1. 读取配置文件 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2. 使用ServletContext对象的getRealPath() //2. 创建SqlSessionFactory工厂 SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(in); //3. 使用工厂生产SqlSession对象 sqlSession = factory.openSession(); //4. 使用SqlSession创建Dao接口的代理对象 roleUserDao = sqlSession.getMapper(RoleUserDao.class); } @After public void destory() throws IOException { //6.事务的提交 sqlSession.commit(); //7. 释放资源 sqlSession.close(); in.close(); } @Test public void testFindAll(){ //5. 使用代理对象执行方法 List<RoleUser> roleUsers = roleUserDao.findAll(); for (RoleUser roleUser : roleUsers){ System.out.println("角色"+roleUser.getRoleId()+"--------------------"); System.out.println(roleUser); System.out.println(roleUser.getUserRoles()); } } }
- 输出结果
-
查询所有用户,同时得到该用户的所有角色信息
- 创建user实体类,同时包含角色集合
public class UserRole implements Serializable { private Integer id; private String username; private Date birthday; private String sex; private String address; //生成多对多映射关系 private List<RoleUser> roleUsers; public List<RoleUser> getRoleUsers() { return roleUsers; } public void setRoleUsers(List<RoleUser> roleUsers) { this.roleUsers = roleUsers; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "UserRole{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}'; } }
- 创建UserRoleDao接口
public interface UserRoleDao { List<UserRole> findAll(); }
- 定义resultMap
<resultMap id="userRoleMap" type="domain.UserRole"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="birthday" column="birthday"></result> <result property="sex" column="sex"></result> <result property="address" column="address"></result> <!--一对一的关系映射,配置封装user的内容--> <collection property="roleUsers" ofType="domain.RoleUser"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> </collection> </resultMap>
- 定义sql语句
<select id="findAll" resultMap="userRoleMap"> SELECT r.id as rid,r.role_name,r.role_desc,u.* FROM mybatisuser u left outer join user_role ur on u.id=ur.uid left outer join role r on r.id=ur.rid; </select>
- 编写测试方法
public class UserRoleTest { public static void main(String[] args) throws IOException { } private InputStream in; private SqlSession sqlSession; private UserRoleDao userRoleDao; @Before public void init() throws IOException { //1. 读取配置文件 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2. 使用ServletContext对象的getRealPath() //2. 创建SqlSessionFactory工厂 SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(in); //3. 使用工厂生产SqlSession对象 sqlSession = factory.openSession(); //4. 使用SqlSession创建Dao接口的代理对象 userRoleDao = sqlSession.getMapper(UserRoleDao.class); } @After public void destory() throws IOException { //6.事务的提交 sqlSession.commit(); //7. 释放资源 sqlSession.close(); in.close(); } @Test public void testFindAll(){ //5. 使用代理对象执行方法 List<UserRole> userRoles = userRoleDao.findAll(); for (UserRole userRole : userRoles){ System.out.println("用户"+userRole.getId()+"--------------------"); System.out.println(userRole); System.out.println(userRole.getRoleUsers()); } } }
- 输出结果
JNDI
- 基本概念
- JNDI(Java Naming and Directory Interface,Java命名和目录接口)是SUN公司提供的一种标准的Java命名系统接口
- JNDI仿windows的注册表,通过key:value实现
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TBAg2OHA-1610768096586)(JNDI.png)]
- 新建MAVEN-webapp工程,配置目录
- 导入依赖
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
</dependency>
- 配置Mybatis主配置文件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>
<typeAliases>
<package name="domain"/>
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"/>
<dataSource type="JNDI">
<property name="data_source" value="java:comp/env/jdbc/my_jndi_test"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="dao/MybatisUserDao.xml"/>
</mappers>
</configuration>
- 在webapp目录下新建目录MATE-INF,在MATE-INF下创建context.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<!--
<Resource
name="jdbc/my_jndi_test" 数据源的名称
type="javax.sql.DataSource" 数据源类型
auth="Container" 数据源提供者
maxActive="20" 最大活动数
maxWait="10000" 最大等待时间
maxIdle="5" 最大空闲数
username="root" 用户名
password="fy123" 密码
driverClassName="com.mysql.cj.jdbc.Driver" 驱动类
url="jdbc:mysql://localhost:3306/my_db?serverTimezone=UTC" 连接url字符串
/>
-->
<Resource
name="jdbc/my_jndi_test"
type="javax.sql.DataSource"
auth="Container"
maxActive="20"
maxWait="10000"
maxIdle="5"
username="root"
password="fy123"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/my_db?serverTimezone=UTC"
/>
</Context>
- 编写index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import="org.apache.ibatis.io.Resources" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactoryBuilder" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactory" %>
<%@ page import="dao.MybatisUserDao" %>
<%@ page import="domain.MybatisUser" %>
<%@ page import="java.util.List" %>
<%@ page import="java.io.InputStream" %>
<%@ page import="org.apache.ibatis.session.SqlSession" %>
<html>
<body>
<h2>Hello World!</h2>
<%
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = sqlSessionFactoryBuilder.build(in);
SqlSession sqlSession = factory.openSession();
MybatisUserDao userDao = sqlSession.getMapper(MybatisUserDao.class);
List<MybatisUser> users = userDao.findAll();
for (MybatisUser user : users){
System.out.println(user);
}
sqlSession.close();
in.close();
%>
</body>
</html>