两个表:
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>