网站开发三大元素,什么是网络搭建,网站建设设计制作培训,抖音代运营协议准备工作#xff1a; 1.创建Maven工程#xff0c;还没有配置Maven的和还不会的去看这里啦#xff1a;maven的下载安装与配置环境变量#xff01;#xff01;#xff01;#xff08;全网最详细#xff09;-CSDN博客 Account.java : (pojo类) #xff08;这里我…准备工作 1.创建Maven工程还没有配置Maven的和还不会的去看这里啦maven的下载安装与配置环境变量全网最详细-CSDN博客 Account.java : (pojo类) 这里我们说明一下根据我们下边的需求我们需要将两张表查询出来的数据存到Account中所以我们在Account这个实体类中添加了一个User的属性
package com.by.pojo;import java.io.Serializable;public class Account implements Serializable {private Integer id;private Integer uid;private Double money;private User user;Overridepublic String toString() {return Account{ id id , uid uid , money money , user user };}public User getUser() {return user;}public void setUser(User user) {this.user user;}public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public Integer getUid() {return uid;}public void setUid(Integer uid) {this.uid uid;}public Double getMoney() {return money;}public void setMoney(Double money) {this.money money;}
} User.java
/** Copyright (c) 2020, 2023, All rights reserved.**/
package com.by.pojo;import java.io.Serializable;
import java.util.Date;/*** pProject: mybatis - User/p* pPowered by scl On 2023-12-18 11:38:09/p* p描述p** author 孙臣龙 [1846080280qq.com]* version 1.0* since 17*/
public class User implements Serializable {private Integer id;private String username;private Date birthday;private String sex;private String address;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;}Overridepublic String toString() {return User{ id id , username username \ , birthday birthday , sex sex \ , address address \ };}
} 2.导入依赖到pom.xml文件中 dependenciesdependencygroupIdorg.mybatis/groupIdartifactIdmybatis/artifactIdversion3.4.5/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion5.1.47/version/dependencydependencygroupIdlog4j/groupIdartifactIdlog4j/artifactIdversion1.2.17/version/dependencydependencygroupIdjunit/groupIdartifactIdjunit/artifactIdversion4.12/version/dependency/dependenciesbuild!-- 如果不添加此节点src/main/java目录下的所有配置文件都会被漏掉。 --resourcesresourcedirectorysrc/main/java/directoryincludesinclude**/*.xml/include/includes/resourceresourcedirectorysrc/main/resources/directory/resource/resources/build 3.在resources中创建log4j.properites和mybatis-config.xml log4j.properites: log4j.properites:# Global logging configurationlog4j.rootLoggerDEBUG, stdout# Console output...log4j.appender.stdoutorg.apache.log4j.ConsoleAppenderlog4j.appender.stdout.layoutorg.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern%5p [%t] - %m%n 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.dtdconfiguration!--使用dev环境--environments defaultdev!--dev环境--environment iddevtransactionManager typeJDBC/transactionManager!--使用连接池中的数据源--dataSource typePOOLEDproperty namedriver valuecom.mysql.jdbc.Driver/property nameurl valuejdbc:mysql://127.0.0.1:3305/mybatis?characterEncodingUTF-8/property nameusername valueroot/property namepassword value//dataSource/environment/environments!-- 扫描映射文件 --mappersmapper resourcecom/by/mapper/UserMapper.xml//mappers/configuration 4.创建RolerMapper接口和RoleMapper.xml文件 AccountMapper.java:
package com.by.mapper;import com.by.pojo.Account;import java.util.List;public interface AccountMapper {}AccountMapper.xml:
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.by.mapper.AccountMapper/mapper 5.创建测试类MyBatisTest /** Copyright (c) 2020, 2023, All rights reserved.**/package com.by;import com.by.mapper.RoleMapper;import com.by.pojo.Role;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.After;import org.junit.Before;import org.junit.Test;import java.io.IOException;import java.io.InputStream;import java.util.List;public class MyBatisTestRole {private InputStream inputStream;private SqlSession sqlSession;Beforepublic void init() throws IOException {// 加载配置文件String resource mybatis-config.xml;inputStream Resources.getResourceAsStream(resource);// 创建sqlSessionFActorySqlSessionFactory sessionFactory new SqlSessionFactoryBuilder().build(inputStream);// 获得数据的绘画实例sqlSession sessionFactory.openSession();}Afterpublic void close() throws IOException {inputStream.close();sqlSession.close();}} 6.数据库文件 /*
Navicat MySQL Data TransferSource Server : localhost_3306
Source Server Version : 50732
Source Host : localhost:3306
Source Database : mybatisTarget Server Type : MYSQL
Target Server Version : 50732
File Encoding : 65001Date: 2021-12-24 11:51:18
*/SET FOREIGN_KEY_CHECKS0;-- ----------------------------
-- Table structure for account
-- ----------------------------
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)
) ENGINEInnoDB DEFAULT CHARSETutf8;-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO account VALUES (1, 41, 1000);
INSERT INTO account VALUES (2, 45, 1000);
INSERT INTO account VALUES (3, 41, 2000);-- ----------------------------
-- Table structure for role
-- ----------------------------
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)
) ENGINEInnoDB DEFAULT CHARSETutf8;-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO role VALUES (1, 院长, 管理整个学院);
INSERT INTO role VALUES (2, 总裁, 管理整个公司);
INSERT INTO role VALUES (3, 校长, 管理整个学校);-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS user;
CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,username varchar(32) NOT NULL COMMENT 用户名称,password varchar(20) DEFAULT NULL,birthday datetime DEFAULT NULL COMMENT 生日,sex char(1) DEFAULT NULL COMMENT 性别,address varchar(256) DEFAULT NULL COMMENT 地址,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT49 DEFAULT CHARSETutf8;-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO user VALUES (41, 张三丰, 111, 2018-02-27 17:47:08, 男, 上海徐汇);
INSERT INTO user VALUES (42, 宋远桥, 111, 2018-03-02 15:09:37, 女, 北京昌平);
INSERT INTO user VALUES (43, 俞莲舟, 111, 2018-03-04 11:34:34, 女, 陕西西安);
INSERT INTO user VALUES (45, 张翠山, 111, 2018-03-04 12:04:06, 男, 山东济南);
INSERT INTO user VALUES (46, 殷梨亭, 111, 2018-03-07 17:37:26, 男, 河北张家口);
INSERT INTO user VALUES (48, 莫声谷, 111, 2018-03-08 11:44:00, 女, 山西太原);-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS user_role;
CREATE TABLE user_role (id int(11) NOT NULL AUTO_INCREMENT,uid int(11) DEFAULT NULL,rid int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8;-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO user_role VALUES (1, 45, 1);
INSERT INTO user_role VALUES (2, 41, 1);
INSERT INTO user_role VALUES (3, 45, 2);准备工作完成后开始功能的开发
1.一对一的关联查询 需求现在有两张表根据account表中的uid和user表中的id将这两张表结合起来查询并返回数据。
根据需求我们开始开发
1AccountMapper.java中添加一个方法
Account findAll(Integer id);
2在AccountMapper.xml文件中映射相应的方法
resultMap idfindAllResultMap typeaccountid columnid propertyid/idresult columnuid propertyuid/resultresult columnmoney propertymoney/resultassociation propertyuser javaTypeuserid columnid propertyid/idresult columnusername propertyusername/resultresult columnbirthday propertybirthday/resultresult columnsex propertysex/resultresult columnaddress propertyaddress/result/association/resultMapselect idfindAll resultMapfindAllResultMapselect a.id aid ,a.uid uid,a.money money ,u.* from account a left join user u on a.uidu.id where a.id#{id};/select
3测试类添加这个方法
Testpublic void findAccountById(){AccountMapper accountMapper sqlSession.getMapper(AccountMapper.class);Account account accountMapper.findAll(2);System.out.println(account);}4结果展示 2.一对多关联查询一个用户可以有多个账户
1首先应该在User Mapper.java文件中添加一个方法。
package com.by.mapper;import com.by.pojo.User;import java.util.List;/*** pProject: mybatis - UserMapper/p* pPowered by scl On 2023-12-22 15:52:05/p* p描述p** author 孙臣龙 [1846080280qq.com]* version 1.0* since 17*/
public interface UserMapper {/*** 一对多*/ListUser findAllUserAccount(Integer id);
}2在UserMapper.xml文件在实现这个方法。
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.by.mapper.UserMapperresultMap idfindAllResultMap typeuserid columnid propertyid/idresult columnusername propertyusername/resultresult columnbirthday propertybirthday/resultresult columnsex propertysex/resultresult columnaddress propertyaddress/resultcollection propertyaccountList ofTypeaccountid columnaid propertyid/idresult columnuid propertyuid/resultresult columnmoney propertymoney/result/collection/resultMapselect idfindAllUserAccount parameterTypeint resultMapfindAllResultMapselect a.id aid, a.uid uid, a.money money, u.*from user uleft join account a on u.ida.uidwhere u.id#{id}/select
/mapper
3在User实体类中添加ListAccount accountList;并重写toString方法。
private ListAccount accountList;public ListAccount getAccountList() {return accountList;}public void setAccountList(ListAccount accountList) {this.accountList accountList;}
4编写测试类
/** Copyright (c) 2020, 2023, All rights reserved.**/
package com.by;import com.by.mapper.AccountMapper;
import com.by.mapper.RoleMapper;
import com.by.mapper.UserMapper;
import com.by.pojo.Role;
import com.by.pojo.User;
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.After;
import org.junit.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;/*** pProject: mybatis - MyBatisTest/p* pPowered by scl On 2023-12-18 11:44:53/p* p描述p** author 孙臣龙 [1846080280qq.com]* version 1.0* since 17*/
public class MyBatisTestRole {private InputStream inputStream;private SqlSession sqlSession;Beforepublic void init() throws IOException {// 加载配置文件String resource mybatis-config.xml;inputStream Resources.getResourceAsStream(resource);// 创建sqlSessionFActorySqlSessionFactory sessionFactory new SqlSessionFactoryBuilder().build(inputStream);// 获得数据的绘画实例sqlSession sessionFactory.openSession();}/*** 一对多,查询一个用户名下有几张卡*/Testpublic void findAllUserAccount() {UserMapper userMapper sqlSession.getMapper(UserMapper.class);ListUser allUserAccount userMapper.findAllUserAccount(41);for (User user : allUserAccount) {System.out.println(user);}}Afterpublic void close() throws IOException {inputStream.close();sqlSession.close();}}
5结果展示 3.多对多关联查询查询角色及角色赋予的用户信息
1RoleMapper接口中定义一个方法
package com.by.mapper;import com.by.pojo.Role;
import com.by.pojo.User;import java.util.List;/*** pProject: mybatis - RoleMapper/p* pPowered by scl On 2023-12-20 14:37:16/p* p描述p** author 孙臣龙 [1846080280qq.com]* version 1.0* since 17*/
public interface RoleMapper {ListRole findAllRoleByUser(Integer id);
}2在RoleMapper.xml文件中实现这个方法。
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.by.mapper.RoleMapperresultMap idfindAllRoleByUserResult typeroleid columnrid propertyid/idresult columnrole_name propertyroleName/resultresult columnrole_desc propertyroleDesc/resultcollection propertyuserList ofTypeuserid columnid propertyid/idresult columnusername propertyusername/resultresult columnaddress propertyaddress/resultresult columnsex propertysex/resultresult columnbirthday propertybirthday/result/collection/resultMapselect idfindAllRoleByUser parameterTypeint resultMapfindAllRoleByUserResultselect * from role r ,user_role ur,user uwhere ur.ridr.id and ur.uidu.id and u.id#{id}/select
/mapper
3实体类Role:
/** Copyright (c) 2020, 2023, All rights reserved.**/
package com.by.pojo;import java.util.List;/*** pProject: mybatis - Role/p* pPowered by scl On 2023-12-20 14:36:04/p* p描述p** author 孙臣龙 [1846080280qq.com]* version 1.0* since 17*/
public class Role {private int id;private String roleName;private String roleDesc;private ListUser userList;public ListUser getUserList() {return userList;}public void setUserList(ListUser userList) {this.userList userList;}Overridepublic String toString() {return Role{ id id , roleName roleName \ , roleDesc roleDesc \ , userList userList };}public int getId() {return id;}public void setId(int id) {this.id id;}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;}
}
4测试类
/** Copyright (c) 2020, 2023, All rights reserved.**/
package com.by;import com.by.mapper.AccountMapper;
import com.by.mapper.RoleMapper;
import com.by.mapper.UserMapper;
import com.by.pojo.Role;
import com.by.pojo.User;
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.After;
import org.junit.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;/*** pProject: mybatis - MyBatisTest/p* pPowered by scl On 2023-12-18 11:44:53/p* p描述p** author 孙臣龙 [1846080280qq.com]* version 1.0* since 17*/
public class MyBatisTestRole {private InputStream inputStream;private SqlSession sqlSession;Beforepublic void init() throws IOException {// 加载配置文件String resource mybatis-config.xml;inputStream Resources.getResourceAsStream(resource);// 创建sqlSessionFActorySqlSessionFactory sessionFactory new SqlSessionFactoryBuilder().build(inputStream);// 获得数据的绘画实例sqlSession sessionFactory.openSession();}/*** 多对多一个人对应多个身份一个身份包含不同的人** throws IOException*/Testpublic void findAllRoleByUser() {RoleMapper roleMapper sqlSession.getMapper(RoleMapper.class);ListRole allRoleByUser roleMapper.findAllRoleByUser(45);for (Role role : allRoleByUser) {System.out.println(role);}}Afterpublic void close() throws IOException {inputStream.close();sqlSession.close();}}
5结果展示
