Spring+MyBatis框架中sql语句的书写,数据集的传递以及多表关联查询

时间:2021-06-16 16:35:26

  在很多Java EE项目中,Spring+MyBatis框架经常被用到,项目搭建在这里不再赘述,现在要将的是如何在项目中书写,增删改查的语句,如何操作数据库,以及后台如何获取数据,如何进行关联查询,以及MyBatis的分页问题。

  首先先看看项目的架构,方便后边叙述。

Spring+MyBatis框架中sql语句的书写,数据集的传递以及多表关联查询

   这个项目中是一个Sping+MyBatis的完整demo(这边将页面没有展示。)这次的主题主要是后台数据处理逻辑。接下来为大家逐一介绍各个文件,

  org.config   Spring配置包括数据库的链接信息

  org.controller  逻辑控制,也就是MVC中的C

  org.dao      接口基类

  org.entity      实体以及MyBatis语句

  org.util       工具类

  在数据库中存在两张表,分别为Customer_info,order_info。用这两张表格最后实现多表格的关联查询。

  第一步骤:建立与数据库表格字段相一致的实体类:

  customerInfo.java

package org.entity;
//实现该接口--序列化,将对象写入文件
import java.io.Serializable;
import java.util.List; public class CustomerInfo implements Serializable {
private Integer customer_id;//客户信息的id
private String customer_name;//客户姓名
private String identity_no;//身份证号码
private String job_add;//工作单位
private String tel;//座机号码
private String cellphone;//移动电话
private String adds;//联系地址
private Integer post;//邮编
private String mail;//电子邮箱
//关联查询属性orderInfo
private List<OrderInfo> orderInfos; public List<OrderInfo> getOrderInfos() {
return orderInfos;
}
public void setOrderInfos(List<OrderInfo> orderInfos) {
this.orderInfos = orderInfos;
} public Integer getCustomer_id() {
return customer_id;
}
public void setCustomer_id(Integer customer_id) {
this.customer_id = customer_id;
}
public String getCustomer_name() {
return customer_name;
}
public void setCustomer_name(String customer_name) {
this.customer_name = customer_name;
}
public String getIdentity_no() {
return identity_no;
}
public void setIdentity_no(String identity_no) {
this.identity_no = identity_no;
}
public String getJob_add() {
return job_add;
}
public void setJob_add(String job_add) {
this.job_add = job_add;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getCellphone() {
return cellphone;
}
public void setCellphone(String cellphone) {
this.cellphone = cellphone;
}
public String getAdds() {
return adds;
}
public void setAdds(String adds) {
this.adds = adds;
}
public Integer getPost() {
return post;
}
public void setPost(Integer post) {
this.post = post;
}
public String getMail() {
return mail;
}
public void setMail(String mail) {
this.mail = mail;
} }

  OrderInfo.java

 package org.entity;

 import java.io.Serializable;
import java.sql.Date; //订单退货单
public class OrderInfo implements Serializable{
private Integer order_id;//订单(退货单)信息的id
private String order_type;//订单类型
private String order_status;//订单状态
private Integer product_id;//商品id
private Integer product_account;//商品数量
private Double pay_money;//总额
private Integer customer_id;//客户id
private Date start_date;//开始日期
private Date deadline;//要求完成日期
private String return_reason;//退货原因
public Integer getOrder_id() {
return order_id;
}
public void setOrder_id(Integer order_id) {
this.order_id = order_id;
} public String getOrder_type() {
return order_type;
}
public void setOrder_type(String order_type) {
this.order_type = order_type;
}
public String getOrder_status() {
return order_status;
}
public void setOrder_status(String order_status) {
this.order_status = order_status;
}
public Integer getProduct_id() {
return product_id;
}
public void setProduct_id(Integer product_id) {
this.product_id = product_id;
}
public Integer getProduct_account() {
return product_account;
}
public void setProduct_account(Integer product_account) {
this.product_account = product_account;
}
public Double getPay_money() {
return pay_money;
}
public void setPay_money(Double pay_money) {
this.pay_money = pay_money;
}
public Integer getCustomer_id() {
return customer_id;
}
public void setCustomer_id(Integer customer_id) {
this.customer_id = customer_id;
}
public Date getStart_date() {
return start_date;
}
public void setStart_date(Date start_date) {
this.start_date = start_date;
}
public Date getDeadline() {
return deadline;
}
public void setDeadline(Date deadline) {
this.deadline = deadline;
}
public String getReturn_reason() {
return return_reason;
}
public void setReturn_reason(String return_reason) {
this.return_reason = return_reason;
} }

  Page.java

 package org.entity;

 public class Page {
//显示第几页数据,默认第一页
private Integer page=1;
//一页显示几条,默认5条
private Integer pageSize = 3;
//最大页数
private Integer totalPage=1; public Integer getTotalPage() {
return totalPage;
}
public void setTotalPage(Integer totalPage) {
this.totalPage = totalPage;
}
//利用page和pageSize计算begin起点
public Integer getBegin(){
return (page-1)*pageSize;
}
//利用page和pageSize计算end结束点
public Integer getEnd(){
return page*pageSize+1;
} public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
}

  在接下来的这两个文件中,里边详细些了如何进行增删改查,以及多表之间的查询操作等。其中sql语句中的每一个id对应着dao方法中的方法名称。dao文件的内容如下;

  customer.xml

 <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="org.dao.CustomerInfoMapperDao"> <select id="findAll"
resultType="org.entity.CustomerInfo">
select * from customer_info
</select>
<delete id="deleteCustomer" parameterType="int">
delete from customer_info where customer_id=#{id}
</delete> <insert id="saveCustomer" parameterType="org.entity.CustomerInfo">
insert into Customer_info(customer_id,customer_name,identity_no,
job_add,tel,cellphone,Adds,post,mail)
values(customer_seq.nextval,#{customer_name,jdbcType=VARCHAR},#{identity_no,jdbcType=VARCHAR},
#{job_add,jdbcType=VARCHAR},#{tel,jdbcType=VARCHAR},#{cellphone,jdbcType=VARCHAR},
#{adds,jdbcType=VARCHAR},#{post,jdbcType=NUMERIC},#{mail,jdbcType=VARCHAR})
</insert> <select id="findByCustomerName"
parameterType="java.lang.String"
resultType="org.entity.CustomerInfo">
select * from customer_info where customer_name=#{customer_name}
</select>
<select id="findByCustomerId" parameterType="int" resultType="org.entity.CustomerInfo">
select * from customer_info where customer_id=#{customer_id,jdbcType=NUMERIC}
</select> <update id="updateCustomerInfo" parameterType="org.entity.CustomerInfo">
update customer_info set customer_name=#{customer_name,jdbcType=VARCHAR},
identity_no=#{identity_no,jdbcType=VARCHAR},
job_add=#{job_add,jdbcType=VARCHAR},tel=#{tel,jdbcType=VARCHAR},
cellphone=#{cellphone,jdbcType=VARCHAR},adds=#{adds,jdbcType=VARCHAR},
post=#{post,jdbcType=NUMERIC},mail=#{mail,jdbcType=VARCHAR}
where customer_id=#{customer_id,jdbcType=NUMERIC}
</update>
<select id="findPage" parameterType="org.entity.Page" resultType="org.entity.CustomerInfo" >
select *
FROM (select c1.*,rownum rn
FROM (select * FROM customer_info order by customer_id)c1)
where rn&gt;#{begin} and rn&lt;#{end} </select> <select id="findRows" resultType="int" >
select count(*) from customer_info
</select> <select id="somethingNotIn" resultType="返回所对应的实体">
SELECT * FROM user WHERE username NOT IN ('zhang','wang')
</select> <select id="findByCuId" parameterType="java.lang.Integer" resultMap="cuAndOrderResult">
select o.customer_id,o.order_id,o.order_type,o.order_status,o.product_id,o.product_account,o.start_date,
c.customer_name,c.cellphone,c.adds
from order_info o left join customer_info c on(o.customer_id=c.customer_id)
where o.customer_id=#{customer_id}
</select>
<resultMap id="cuAndOrderResult" type="org.entity.CustomerInfo" >
<id property="customer_id" column="customer_id"/>
<result property="customer_name" column="customer_name"/>
<result property="cellphone" column="cellphone"/>
<result property="adds" column="adds"/>
<collection ofType="org.entity.OrderInfo"
property="orderInfos" column="customer_id" javaType="java.util.List">
<id property="order_id" column="order_id"/>
<result property="order_type" column="order_type"/>
<result property="order_status" column="order_status"/>
<result property="product_id" column="product_id"/>
<result property="product_account" column="product_account"/>
<result property="start_date" column="start_date"/>
</collection>
</resultMap> <!--
<select id="findByCuId"
parameterType="java.lang.Integer"
resultMap="cuAndOrderResult">
select o.customer_id,o.order_id,o.order_type,o.order_status,o.product_id,o.product_account,
c.customer_name,c.cellphone,c.adds
from order_info o join customer_info c on(o.customer_id=c.customer_id)
where o.customer_id=#{customer_id}
select * from customer_info where customer_id=#{customer_id} </select>
<select id="selectOrderInfo"
parameterType="int"
resultType="org.entity.OrderInfo">
select * from order_info where customer_id=#{customer_id}
</select>
<resultMap id="cuAndOrderResult" type="org.entity.CustomerInfo">
<id column="customer_id" property="customer_id" />
<collection ofType="org.entity.OrderInfo"
property="orderInfos" javaType="java.util.ArrayList"
column="customer_id" select="selectOrderInfo">
</collection>
</resultMap> --> </mapper>

  OrderInfo.xml

 <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="org.dao.OrderInfoMapperDao"> <select id="findAll"
resultType="org.entity.OrderInfo">
select * from Order_Info
</select> <select id="findPage" parameterType="org.entity.Page" resultType="org.entity.OrderInfo" >
select *
FROM (select c1.*,rownum rn
FROM (select * FROM Order_Info order by order_id)c1)
where rn&gt;#{begin} and rn&lt;#{end} </select> <select id="findRows" resultType="int" >
select count(*) from Order_Info
</select> </mapper>

  CustomerInfoMapperDao.java

 package org.dao;

 import java.util.List;

 import org.entity.CustomerInfo;
import org.entity.Page;
import org.util.MyBatisDao; //xml文件中的方法,然后在Controller中调用该方法
@MyBatisDao
public interface CustomerInfoMapperDao {
public List<CustomerInfo> findAll();
public void deleteCustomer(int id);
public void saveCustomer(CustomerInfo customer);
public CustomerInfo findByCustomerName(String customer_name);
public CustomerInfo findByCustomerId(int id);
public List<CustomerInfo> findByCuId(int id);//根据cuid查询客户的订单信息
public void updateCustomerInfo(CustomerInfo customer);
//分页操作
public List<CustomerInfo> findPage(Page page);
public int findRows(); }

  接下来主要来说一下,MyBatis中的多表关联问题。多表关联主要对应customer.xml中的这段代码;

Spring+MyBatis框架中sql语句的书写,数据集的传递以及多表关联查询

在controller中的代码如下:

Spring+MyBatis框架中sql语句的书写,数据集的传递以及多表关联查询

从图中可以看到,在这边使用了一个增强的for循环来进行处理,这样就实现了多表之间的关联查询,接下来将数据显示到前台页面即可。

主要代码:

 List<CustomerInfo> list = dao.findByCuId(id);
String ls = "";
List<OrderInfo> cu = null;
for(CustomerInfo cus:list){
cu=cus.getOrderInfos();
}
model.addAttribute("customers", list);
model2.addAttribute("order",cu);