这几天入门java,一直在看关联映射,琢磨了挺长时间,为了其他网友今后少踩坑。先总结一下
有三张表:user 、book、address
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`bookid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT NULL,
`bookname` varchar(255) DEFAULT NULL,
`totalpage` int(10) DEFAULT NULL,
`price` int(11) DEFAULT NULL,
PRIMARY KEY (`bookid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`userid` int(8) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(25) NOT NULL DEFAULT '' COMMENT '用户名',
`userpassword` varchar(255) DEFAULT NULL,
`userage` varchar(255) DEFAULT NULL,
`useremail` varchar(255) DEFAULT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8 COMMENT='管理员列表';
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
`addressid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT NULL,
`province` varchar(32) DEFAULT NULL,
`city` varchar(16) DEFAULT NULL,
PRIMARY KEY (`addressid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
user与book是一对多
user与address是一对一
新建完项目,我的目录结构是这样的。
1、首先数据库连接及配置:
打开application.properties
server.port=8080
server.servlet.context-path=/demo
#数据库配置
spring.datasource.url=jdbc:mysql://localhost:3306/ldzy?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#加载Mapper.xml路径
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
#设置config-location位置
mybatis.config-location=classpath:mybatis-config.xml
#设置type-aliases-package中指定实体扫描包类让mybatis自定扫描到自定义的路径
mybatis.type-aliases-package=com.dm.batis.bean
#热部署生效
spring.devtools.restart.enabled=true
#设置重启的目录
spring.devtools.restart.additional-paths=src/main/java
#classpath目录下的WEB-INF文件夹内容修改不重启
spring.devtools.restart.exclude=WEB-INF/**
mybatis-config.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>
<settings>
<!-- Globally enables or disables any caches configured in any mapper under this configuration -->
<setting name="cacheEnabled" value="true"/>
<!-- Sets the number of seconds the driver will wait for a response from the database -->
<setting name="defaultStatementTimeout" value="3000"/>
<!-- Enables automatic mapping from classic database column names A_COLUMN to camel case classic Java property names aColumn -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- Allows JDBC support for generated keys. A compatible driver is required.
This setting forces generated keys to be used if set to true,
as some drivers deny compatibility but still work -->
<setting name="useGeneratedKeys" value="true"/>
</settings>
<!-- Continue going here -->
</configuration>
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<Book> books;//喜欢的书
public List<Book> getBooks() {
return books;
}
public void setBooks(List<Book> books) {
this.books = books;
}
public Address getAddress() {
return userAddress;
}
public void setAddress(Address address) {
this.userAddress = address;
}
public int getUserAge() {
return userAge;
}
public void setUserAge(int userAge) {
this.userAge = userAge;
}
public String getUserEmail() {
return userEmail;
}
public void setUserEmail(String userEmail) {
this.userEmail = userEmail;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = 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 = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = 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 = addressId;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
@Override
public String toString()
{
return this.province+this.city;
}
}
3、创建mapper.xml文件
resources目录下面建立目录mapper
里面新建三个配置文件UserMapper.xml,AddressMapper.xml,BookMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dm.batis.mapper.UserMapper">
<!-- Association:处理单一的关联对象 处理单一属性的关联关系-->
<!-- property:注入给员工中哪个属性-->
<!-- select:发送哪一条sql语句-->
<!-- javaType:把sql语句查询出的结果集,封装给哪个类的对象(可以省略)-->
<!-- column:查询员工的结果集中,用哪个列的值作为条件去查询关联的对象column作为select查询条件的参数,但是这个条件一般都是外键-->
<!--OneToOne-->
<resultMap id="UserBaseMap" type="User">
<id column="userId" property="userId" />
<result column="userName" property="userName"/>
<result column="userPassword" property="userPassword"/>
<result column="userAge" property="userAge"/>
<result column="user_pwd" property="userPwd"/>
<result column="userEmail" property="userEmail" />
<!-- 第二种方案 -->
<association property="userAddress" column="UserId" javaType="Address">
<id property="addressId" column="addressId" />
<result property="province" column="province" />
<result property="city" column="city" />
</association>
<!-- 第一种方案 -->
<!-- <association property="userAddress" javaType="Address"-->
<!-- column="UserId" select="com.dm.batis.mapper.AddressMapper.getAddressById"></association>-->
</resultMap>
<!-- 第一种方案 -->
<!-- <select id="UserAddressById" parameterType="int" resultMap="UserBaseMap">-->
<!-- SELECT * FROM user WHERE userId=#{userId}-->
<!-- </select>-->
<!-- 第二种方案 -->
<select id="UserAddressById" parameterType="int" resultMap="UserBaseMap">
SELECT * FROM user U
inner JOIN address A ON U.userId=A.userId WHERE
A.userId=#{userId}
</select>
<!--OneToMany-->
<resultMap id="UserBookMap" type="User">
<id column="userId" property="userId" />
<result column="userName" property="userName"/>
<result column="userPassword" property="userPassword"/>
<result column="userAge" property="userAge"/>
<result column="user_pwd" property="userPwd"/>
<result column="userEmail" property="userEmail" />
<!--第一种方案-->
<!-- <collection property="books" ofType="Book" column="userId"-->
<!-- select="com.dm.batis.mapper.BookMapper.UserBookList"></collection>-->
<collection property="books" ofType="Book" column="userId">
<id property="bookId" column="bookId"/>
<result property="bookName" column="bookName"/>
<result property="totalPage" column="totalPage"/>
<result property="price" column="price"/>
</collection>
</resultMap>
<!-- <select id="UserBookById" parameterType="int" resultMap="UserBookMap">-->
<!-- SELECT * FROM user WHERE userId=#{userId}-->
<!-- </select>-->
<select id="UserBookById" parameterType="int" resultMap="UserBookMap">
SELECT * FROM user U
inner JOIN book B ON U.userId=B.userId WHERE
B.userId=#{userId}
</select>
</mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dm.batis.mapper.BookMapper">
<resultMap id="BookBaseMap" type="Book">
<id property="bookId" column="bookId"/>
<result property="bookName" column="bookName"/>
<result property="totalPage" column="totalPage"/>
<result property="price" column="price"/>
</resultMap>
<select id="UserBookList" parameterType="int" resultType="Book">
SELECT * FROM book
WHERE userid = #{userId}
</select>
</mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dm.batis.mapper.AddressMapper">
<resultMap id="AddressBaseMap" type="Address">
<id column="addressId" property="addressId" />
<result column="province" property="province"/>
<result column="city" property="city"/>
</resultMap>
<select id="getAddressById" parameterType="int" resultMap="AddressBaseMap">
SELECT * FROM address
WHERE userid = #{userId}
</select>
</mapper>
4、创建mapper接口文件
在包目录下面建立目录mapper
里面新建三个配置文件UserMapper,AddressMapper,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;
@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;
@Mapper
public interface BookMapper
{
@Select("select * from book where userId = #{userId}")
public List<Book> getBooksByUserId(int userId);
public List<Book> 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;
@Mapper
public interface AddressMapper
{
@Select("select * from address where userId=#{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;
@RestController
public class Index {
@Autowired
private UserMapper userMapper;
@Autowired
private BookMapper bookMapper;
@RequestMapping("/index")
public User index(){
User user = userMapper.UserAddressById(1);
//User user=userMapper.UserBookById(1);
return user;
}
}
6、测试
一对一
一对多