mysql 一对多映射_mybatis关系映射之一对多和多对一

本实例使用用户和订单的例子做说明: 一个用户可以有多个订单, 一个订单只对应一个用户。(其中应用到注释)

1.代码的结构

2. 建表语句:

CREATE DATABASE test;

USE test;

CREATE TABLE person(

personId VARCHAR(36) PRIMARY KEY,

personName VARCHAR(64),

personAddress VARCHAR(128),

personTel VARCHAR(11)

);

CREATE TABLE orders(

orderId VARCHAR(36) PRIMARY KEY,

orderNumber VARCHAR(20),

orderPrice INT,

pid VARCHAR(36)

);

INSERT INTO person VALUES('1', '木子', '湖北', '110');

INSERT INTO person VALUES('2', '木子大大', '武汉', '120');

INSERT INTO person VALUES('1', '木子苗苗', '天门', '119');

INSERT INTO orders VALUES('1', '001', 100, '1');

INSERT INTO orders VALUES('2', '002', 200, '1');

INSERT INTO orders VALUES('3', '003', 300, '2');

INSERT INTO orders VALUES('4', '004', 400, '2');

INSERT INTO orders VALUES('5', '005', 500, '3');

SELECT p.*, o.* FROM person p JOIN orders o ON (p.personId=o.pid) WHERE p.personId = '1' ;

*指显示所有字段

3. 用户实体:

package com.mybatis.domain;

import java.util.List;

import lombok.Data;

@Data//注释(Person为单方)

public class Person {

private String personid;

private String personname;

private String personaddress;

private String persontel;

//这个代表多方里面的内容(Orders)

private List orders;

@Override

public String toString() {

return "Person [personid=" + personid + ", personname=" + personname

+ ", personaddress=" + personaddress + ", persontel="

+ persontel + ", orders=" + orders + "]";

}

}

4. 订单实体:

package com.mybatis.domain;

import lombok.Data;

@Data//(Orders为多方)

public class Orders {

private String orderid;

private String ordernumber;

private Integer orderprice;

//对象(单方Person)与外键进行关联

private Person person;

}

5.写PersonMapper.java的接口

package com.mybatis.dao.mapper;

import com.mybatis.domain.Orders;

import com.mybatis.domain.Person;

import java.util.List;

public interface PersonMapper {

int deleteByPrimaryKey(String personid);

int insert(Person record);

Person selectByPrimaryKey(String personid);

List selectAll();

int updateByPrimaryKey(Person record);

//一对多查询(根据id查询)

public List findPersonAndOrders(String pid);

//一对多查询返回一个对象

public Person selectPersonById(String id);

}

6. 一对多实体配置: PersonMapper.xml

SELECT p.*,o.* FROM person o,orders b WHERE o.personid=#{pid};

select p.*, o.* from person p, orders o where p.personId = o.pid and p.personId = #{id}

7.写OrdersMapper.java的接口

package com.mybatis.dao.mapper;

import com.mybatis.domain.Orders;

import java.util.List;

public interface OrdersMapper {

int deleteByPrimaryKey(String orderid);

int insert(Orders record);

Orders selectByPrimaryKey(String orderid);

List selectAll();

int updateByPrimaryKey(Orders record);

//多查一 根据id

public Orders selectOrderById(String oid);

//多查一 根据orderNumber

public Orders selectOrderNumber(String number);

}

8.多对一实体配置:OrdersMapper.xml

select p.*, o.* from person p, orders o where p.personId = o.pid and o.orderId = #{oid}

select p.*, o.* from person p, orders o where p.personId = o.pid and o.orderId = #{number}

9.其他配置

db.properties配置(sql语句的基本链接)

db.driver=com.mysql.jdbc.Driver

db.url=jdbc:mysql://localhost:3306/wang1?useUnicode=true&characterEncoding=utf8

db.username=root

db.password=123456

log4j.properties配置(注释)

# Global logging configuration

log4j.rootLogger=DEBUG, stdout

# Console output...

log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

mybatis.xml(逆向生成domain、dao层)

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

generatorConfig.xml配置(对MySQL进行操作)下面标红部分根据自己建立的进行修改

/p>

PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"

"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

D盘中要有此包mysql-connector-java-5.1.7-bin.jar

connectionURL="jdbc:mysql://localhost:3306/wang1" userId="root" password="123456">

10.测试文件

package com.mybatis.test;

import java.io.InputStream;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.Iterator;

import java.util.List;

import lombok.Data;

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.Before;

import org.junit.Test;

import com.mybatis.dao.mapper.OrdersMapper;

import com.mybatis.dao.mapper.PersonMapper;

import com.mybatis.domain.Orders;

import com.mybatis.domain.Person;

public class TestStudentMapper {

SqlSessionFactory sessionFactory = null;

// 这方法之前

@Before

public void setup() throws Exception {

String resource = "mybatis.xml";

// 这个是加载配置文件

InputStream inputStream = Resources.getResourceAsStream(resource);

// 得到会话工厂

sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

}

//查询一对多 根据这个person里面的id号就能查询出这个用户有多少个订单记录

// @Test

public void testSelectPersonById(){

SqlSession sq = sessionFactory.openSession();

// 得到dao层的实现类

PersonMapper u = sq.getMapper(PersonMapper.class);

Person person = u.selectPersonById("2");

System.out.println(person);

}

//多对一 根据多对一id进行查询

// @Test//多对一关联查询

public void testSelectOrderById(){

SqlSession sq = sessionFactory.openSession();

// 得到dao层的实现类

OrdersMapper u = sq.getMapper(OrdersMapper.class);

Orders od = u.selectOrderById( "2");

System.out.println(od.getPerson().getPersonname());

System.out.println(od.getPerson().getPersonaddress());

}

@Test//多对一关联查询

public void testSelectOrderNumber(){

SqlSession sq = sessionFactory.openSession();

// 得到dao层的实现类

OrdersMapper u = sq.getMapper(OrdersMapper.class);

Orders od = u.selectOrderNumber("001");

System.out.println(od.getPerson().getPersonname());

System.out.println(od.getPerson().getPersonaddress());

}

}

如有问题请多多指教！希望给您带来帮助！祝您生活愉快。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对脚本之家的支持。如果你想了解更多相关内容请查看下面相关链接