两个表：
Customer 顾客表
create table if not exists customer(
customer_id int primary key auto_increment,
first_name varchar(20),
last_name varchar(20),
company varchar(20),
address varchar(20),
city varchar(20),
state int ,
country varchar(20),
postal_code varchar(20),
phone varchar(11),
fax varchar(11),
email varchar(20),
support_repld int ); Invoice 发票表
create table if not exists invoice(
invoice_id int primary key auto_increment,
invoice_date date,
billing_address varchar(20),
billing_city varchar(20),
billing_state int ,
billing_country varchar(20),
billing_postalCode varchar(20),
total decimal(10,2),
customer_id int references coustomer(customer_id)
); 1、根据两个表合理创建
2、三个类能正确创建出来，并建立友好关系 要求：1、依据发票的id查询发票的信息，请配置映射，要求Invoice与BillingInfo的实例都能正确创建出来。
2、假定要依据客户的id，查询出客户及其关联的发票信息，请配置映射。 实体类
Customer
package com.oukele.entity_invoice;
import java.util.List;
public class Customer {
private int customerId;
private String firstName;
private String lastName;
private String company;
private String address;
private String city;
private int state;
private String country;
private String postalCode;
private String phone;
private String fax;
private String email;
private int supportRepId;
private List<Invoice> invoices;
public int getCustomerId() {
return customerId;
}
public void setCustomerId(int customerId) {
this.customerId = customerId;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getCompany() {
return company;
}
public void setCompany(String company) {
this.company = company;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public int getState() {
return state;
}
public void setState(int state) {
this.state = state;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getPostalCode() {
return postalCode;
}
public void setPostalCode(String postalCode) {
this.postalCode = postalCode;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getFax() {
return fax;
}
public void setFax(String fax) {
this.fax = fax;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public int getSupportRepId() {
return supportRepId;
}
public void setSupportRepId(int supportRepId) {
this.supportRepId = supportRepId;
}
public List<Invoice> getInvoices() {
return invoices;
}
public void setInvoices(List<Invoice> invoices) {
this.invoices = invoices;
}
@Override
public String toString() {
return "Customer{" +
"customerId=" + customerId +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
", company='" + company + '\'' +
", address='" + address + '\'' +
", city='" + city + '\'' +
", state=" + state +
", country='" + country + '\'' +
", postalCode='" + postalCode + '\'' +
", phone='" + phone + '\'' +
", fax='" + fax + '\'' +
", email='" + email + '\'' +
", supportRepId=" + supportRepId +
", invoices=" + invoices +
'}';
}
}BillingInfo
package com.oukele.entity_invoice;
public class BillingInfo {
private String billingAddress;
private String billingCity;
private int billingState;
private String billingCountry;
private String billingPostalCode;
public BillingInfo() {
}
public String getBillingAddress() {
return billingAddress;
}
public void setBillingAddress(String billingAddress) {
this.billingAddress = billingAddress;
}
public String getBillingCity() {
return billingCity;
}
public void setBillingCity(String billingCity) {
this.billingCity = billingCity;
}
public int getBillingState() {
return billingState;
}
public void setBillingState(int billingState) {
this.billingState = billingState;
}
public String getBillingCountry() {
return billingCountry;
}
public void setBillingCountry(String billingCountry) {
this.billingCountry = billingCountry;
}
public String getBillingPostalCode() {
return billingPostalCode;
}
public void setBillingPostalCode(String billingPostalCode) {
this.billingPostalCode = billingPostalCode;
}
@Override
public String toString() {
return "BillingInfo{" +
"billingAddress='" + billingAddress + '\'' +
", billingCity='" + billingCity + '\'' +
", billingState=" + billingState +
", billingCountry='" + billingCountry + '\'' +
", billingPostalCode='" + billingPostalCode + '\'' +
'}';
}
}Invoice
package com.oukele.entity_invoice;
import java.util.Date;
public class Invoice {
private int invoiceId;
private Date invoiceDate;
private Customer customer;
private BillingInfo billingInfo;
private long total;
public Invoice() {
}
public int getInvoiceId() {
return invoiceId;
}
public Customer getCoustomer() {
return customer;
}
public void setCoustomer(Customer customer) {
this.customer = customer;
}
public void setInvoiceId(int invoiceId) {
this.invoiceId = invoiceId;
}
public Date getInvoiceDate() {
return invoiceDate;
}
public void setInvoiceDate(Date invoiceDate) {
this.invoiceDate = invoiceDate;
}
public BillingInfo getBillingInfo() {
return billingInfo;
}
public void setBillingInfo(BillingInfo billingInfo) {
this.billingInfo = billingInfo;
}
public long getTotal() {
return total;
}
public void setTotal(long total) {
this.total = total;
}
@Override
public String toString() {
return "Invoice{" +
"invoiceId=" + invoiceId +
", invoiceDate=" + invoiceDate +
", customer=" + customer +
", billingInfo=" + billingInfo +
", total=" + total +
'}';
}
}mybatis配置文件
<?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>
<!--定义一些可以复用的变量-->
<properties resource="jdbc.properties"></properties> <settings>
<!--<setting name="autoMappingBehavior " value="FULL"/>-->
<!--配置驼峰映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings> <!--设置别名,简化映射xml的完全限制名的使用-->
<typeAliases>
<!--手动-->
<!--<typeAlias alias="Lnvoice" type="com.oukele.entity.LnvoiceEntity"/>-->
<!--自动-->
<package name="com.oukele.entity_invoice"/> <!-- 可以使用 @Alias 注解 -->
<package name="com.oukele.entity"/>
</typeAliases> <!--数据源-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射文件-->
<mappers>
<mapper resource="mapper/EntityMapper2.xml"/>
</mappers> </configuration>
接口方法
映射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.oukele.dao.IEntity2"> <!-- start 关联查询-->
<!-- 得到属于Invoice的数据-->
<select id="getInvoiceById" resultMap="rmInvoice">
select i.*,c.* from invoice i left join customer c on i.customer_id=c.customer_id where i.invoice_id=#{id}
</select> <!-- 得到属于Customer的数据 -->
<select id="getCustomerById" resultMap="rmCustomer">
select i.*,c.* from invoice i left join customer c on i.customer_id=c.customer_id where i.customer_id=#{id}
</select> <!--填充Customer类的数据 -->
<resultMap id="rmCustomer" autoMapping="true" type="Customer">
<!--使用递归关系 Customer类中的 invoices 要输出要删除，不然会造成递归死循环 -->
<collection property="invoices" ofType="Invoice" resultMap="rmInvoice" autoMapping="true" javaType="java.util.ArrayList"/>
</resultMap> <!--填充 Invoice类 的数据 -->
<resultMap id="rmInvoice" autoMapping="true" type="Invoice">
<association property="billingInfo" autoMapping="true" javaType="BillingInfo"/>
<association property="customer" autoMapping="true" resultMap="rmCustomer" javaType="Customer"/>
</resultMap>
<!--end 关联查询--> <!-- start 嵌套查询-->
<select id="getInvoiceById" parameterType="int" resultMap="rmInvoice">
select * from invoice where invoice_id=#{id}
</select> <select id="getBillingInfoById" parameterType="int" resultType="BillingInfo" >
select
billing_address,
billing_city,
billing_country,
billing_postalCode,
billing_state
from invoice where invoice_id=#{id}
</select> <select id="getCustomerById" resultType="Customer" parameterType="int">
select c.*,i.* from customer c left join invoice i on c.customer_id=i.customer_id where i.invoice_id=#{id}
</select> <resultMap id="rmInvoice" autoMapping="true" type="Invoice">
<association property="billingInfo" autoMapping="true" column="invoice_id" select="getBillingInfoById"></association>
<collection property="customer" ofType="Customer" column="invoice_id" select="getCustomerById"></collection>
</resultMap>
<!--end 嵌套查询--> </mapper>