springbootmybatis一对一、一对多查询应用

这几天入门java&＃xff0c;一直在看关联映射&＃xff0c;琢磨了挺长时间&＃xff0c;为了其他网友今后少踩坑。先总结一下

有三张表&＃xff1a;user 、book、address

DROP TABLE IF EXISTS &＃96;book&＃96;;
CREATE TABLE &＃96;book&＃96; (&＃96;bookid&＃96; int(11) unsigned NOT NULL AUTO_INCREMENT,&＃96;userid&＃96; int(11) DEFAULT NULL,&＃96;bookname&＃96; varchar(255) DEFAULT NULL,&＃96;totalpage&＃96; int(10) DEFAULT NULL,&＃96;price&＃96; int(11) DEFAULT NULL,PRIMARY KEY (&＃96;bookid&＃96;)
) ENGINE&＃61;MyISAM AUTO_INCREMENT&＃61;4 DEFAULT CHARSET&＃61;utf8;


DROP TABLE IF EXISTS &＃96;user&＃96;;
CREATE TABLE &＃96;user&＃96; (&＃96;userid&＃96; int(8) unsigned NOT NULL AUTO_INCREMENT,&＃96;username&＃96; varchar(25) NOT NULL DEFAULT &＃39;&＃39; COMMENT &＃39;用户名&＃39;,&＃96;userpassword&＃96; varchar(255) DEFAULT NULL,&＃96;userage&＃96; varchar(255) DEFAULT NULL,&＃96;useremail&＃96; varchar(255) DEFAULT NULL,PRIMARY KEY (&＃96;userid&＃96;)
) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;107 DEFAULT CHARSET&＃61;utf8 COMMENT&＃61;&＃39;管理员列表&＃39;;

DROP TABLE IF EXISTS &＃96;address&＃96;;
CREATE TABLE &＃96;address&＃96; (&＃96;addressid&＃96; int(11) unsigned NOT NULL AUTO_INCREMENT,&＃96;userid&＃96; int(11) DEFAULT NULL,&＃96;province&＃96; varchar(32) DEFAULT NULL,&＃96;city&＃96; varchar(16) DEFAULT NULL,PRIMARY KEY (&＃96;addressid&＃96;)
) ENGINE&＃61;MyISAM AUTO_INCREMENT&＃61;4 DEFAULT CHARSET&＃61;utf8;


user与book是一对多

user与address是一对一

新建完项目&＃xff0c;我的目录结构是这样的。

 1、首先数据库连接及配置&＃xff1a;

打开application.properties

server.port&＃61;8080
server.servlet.context-path&＃61;/demo
#数据库配置
spring.datasource.url&＃61;jdbc:mysql://localhost:3306/ldzy?useUnicode&＃61;true&characterEncoding&＃61;utf8&useSSL&＃61;false
spring.datasource.username&＃61;root
spring.datasource.password&＃61;root
spring.datasource.driver-class-name&＃61;com.mysql.cj.jdbc.Driver
#加载Mapper.xml路径
mybatis.mapper-locations&＃61;classpath:mapper/*Mapper.xml
#设置config-location位置
mybatis.config-location&＃61;classpath:mybatis-config.xml
#设置type-aliases-package中指定实体扫描包类让mybatis自定扫描到自定义的路径
mybatis.type-aliases-package&＃61;com.dm.batis.bean#热部署生效
spring.devtools.restart.enabled&＃61;true
#设置重启的目录
spring.devtools.restart.additional-paths&＃61;src/main/java
#classpath目录下的WEB-INF文件夹内容修改不重启
spring.devtools.restart.exclude&＃61;WEB-INF/**

mybatis-config.xml

2、新建user、book、address实体类

在com.dm.batis.bean创建

package com.dm.batis.bean;import java.util.List;public class User {private int userId;private String userName;private String userPassword;private int userAge;private String userEmail;private Address userAddress;//用户地址private List books;//喜欢的书public List getBooks() {return books;}public void setBooks(List books) {this.books &＃61; books;}public Address getAddress() {return userAddress;}public void setAddress(Address address) {this.userAddress &＃61; address;}public int getUserAge() {return userAge;}public void setUserAge(int userAge) {this.userAge &＃61; userAge;}public String getUserEmail() {return userEmail;}public void setUserEmail(String userEmail) {this.userEmail &＃61; userEmail;}public int getUserId() {return userId;}public void setUserId(int userId) {this.userId &＃61; userId;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName &＃61; userName;}public String getUserPassword() {return userPassword;}public void setUserPassword(String userPassword) {this.userPassword &＃61; userPassword;}
}


package com.dm.batis.bean;public class Book
{private int bookId;private String bookName;private int totalPage;private int price;public int getBookId() {return bookId;}public void setBookId(int bookId) {this.bookId &＃61; bookId;}public String getBookName() {return bookName;}public void setBookName(String bookName) {this.bookName &＃61; bookName;}public int getTotalPage() {return totalPage;}public void setTotalPage(int totalPage) {this.totalPage &＃61; totalPage;}public int getPrice() {return price;}public void setPrice(int price) {this.price &＃61; price;}}


package com.dm.batis.bean;public class Address
{private int addressId;private String province;private String city;public int getAddressId() {return addressId;}public void setAddressId(int addressId) {this.addressId &＃61; addressId;}public String getProvince() {return province;}public void setProvince(String province) {this.province &＃61; province;}public String getCity() {return city;}public void setCity(String city) {this.city &＃61; city;}&＃64;Overridepublic String toString(){return this.province&＃43;this.city;}
}


3、创建mapper.xml文件

resources目录下面建立目录mapper

里面新建三个配置文件UserMapper.xml,AddressMapper.xml,BookMapper.xml














SELECT * FROM user Uinner JOIN address A ON U.userId&＃61;A.userId WHEREA.userId&＃61;#{userId}



SELECT * FROM user Uinner JOIN book B ON U.userId&＃61;B.userId WHEREB.userId&＃61;#{userId}


SELECT * FROM bookWHERE userid &＃61; #{userId}



SELECT * FROM addressWHERE userid &＃61; #{userId}

4、创建mapper接口文件

在包目录下面建立目录mapper

里面新建三个配置文件UserMapper&＃xff0c;AddressMapper&＃xff0c;BookMapper

package com.dm.batis.mapper;import com.dm.batis.bean.Book;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;import com.dm.batis.bean.User;import java.util.List;&＃64;Mapper
public interface UserMapper
{//一对一public User UserAddressById(int userId);//一对多public User UserBookById(int userId);}


package com.dm.batis.mapper;import java.util.List;import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;import com.dm.batis.bean.Book;
import org.springframework.data.repository.query.Param;&＃64;Mapper
public interface BookMapper
{&＃64;Select("select * from book where userId &＃61; #{userId}")public List getBooksByUserId(int userId);public List UserBookList(int userId);}


package com.dm.batis.mapper;import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;import com.dm.batis.bean.Address;
&＃64;Mapper
public interface AddressMapper
{&＃64;Select("select * from address where userId&＃61;#{userId}")public Address getAddressByUserId(int userId);public Address getAddressById(int userId);
}


5、在包目录下面建立目录controller

里面新建index

package com.dm.batis.controller;import com.dm.batis.bean.User;
import com.dm.batis.mapper.BookMapper;
import com.dm.batis.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;&＃64;RestController
public class Index {&＃64;Autowiredprivate UserMapper userMapper;&＃64;Autowiredprivate BookMapper bookMapper;&＃64;RequestMapping("/index")public User index(){User user &＃61; userMapper.UserAddressById(1);//User user&＃61;userMapper.UserBookById(1);return user;}
}

6、测试

一对一

一对多

至此演示结束&＃xff0c;因本人也是才接触java学习&＃xff0c;有不足之处&＃xff0c;请见谅&＃xff01;&＃xff01;

欢迎加微信&＃xff1a;598194221&＃xff0c;共同探讨学习java交流心得&＃xff01;