框架课程0710作业

原创 于 2020-07-11 16:05:30 发布 · 266 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细介绍了使用MyBatis进行复杂SQL查询的高级映射技巧，包括一对多关系映射、动态SQL及参数传递，通过具体示例展示了如何在Java应用中高效操作数据库。

0710作业

1.创建工程，导入所需要的的mybatis依赖jar包
2.配置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>

    <properties resource="db.properties"></properties>

    <typeAliases>
        <package name="com.openlab.pojo"></package>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"></property>
                <property name="url" value="${url}"></property>
                <property name="username" value="${username}"></property>
                <property name="password" value="${password}"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com//openlab//mapper//ProviderMapper.xml"></mapper>
    </mappers>

</configuration>

3.配置db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8
username=root
password=123456

4.封装SqlSessionFactoryUtil工具类

package com.openlab.util;

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 java.io.IOException;
import java.io.InputStream;

public class SqlSessionFactoryUtil {

    private static SqlSessionFactory factory;

    static {
        String str = "mybatis-config.xml";
        try {
            InputStream is = Resources.getResourceAsStream(str);
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        SqlSession sqlSession = factory.openSession();
        return sqlSession;
    }

    public static void closeSqlSession(SqlSession sqlSession){
        if(sqlSession!=null){
            sqlSession.close();
        }

    }
}

5.编写实体类Provider

package com.openlab.pojo;

import java.util.Date;
import java.util.List;

public class Provider {
    private int id;
    private String proCode;
    private String proName;
    private String proDesc;
    private String proContact;
    private String proPhone;
    private String proAddress;
    private String proFax;
    private int createdBy;
    private Date creationDate;
    private Date modifyDate;
    private int modifyBy;
    private String companyLicPicPath;
    private String orgCodePicPath;

    private List<Bill> bills;

    public List<Bill> getBills() {
        return bills;
    }

    public void setBills(List<Bill> bills) {
        this.bills = bills;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getProCode() {
        return proCode;
    }

    public void setProCode(String proCode) {
        this.proCode = proCode;
    }

    public String getProName() {
        return proName;
    }

    public void setProName(String proName) {
        this.proName = proName;
    }

    public String getProDesc() {
        return proDesc;
    }

    public void setProDesc(String proDesc) {
        this.proDesc = proDesc;
    }

    public String getProContact() {
        return proContact;
    }

    public void setProContact(String proContact) {
        this.proContact = proContact;
    }

    public String getProPhone() {
        return proPhone;
    }

    public void setProPhone(String proPhone) {
        this.proPhone = proPhone;
    }

    public String getProAddress() {
        return proAddress;
    }

    public void setProAddress(String proAddress) {
        this.proAddress = proAddress;
    }

    public String getProFax() {
        return proFax;
    }

    public void setProFax(String proFax) {
        this.proFax = proFax;
    }

    public int getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(int createdBy) {
        this.createdBy = createdBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    public int getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(int modifyBy) {
        this.modifyBy = modifyBy;
    }

    public String getCompanyLicPicPath() {
        return companyLicPicPath;
    }

    public void setCompanyLicPicPath(String companyLicPicPath) {
        this.companyLicPicPath = companyLicPicPath;
    }

    public String getOrgCodePicPath() {
        return orgCodePicPath;
    }

    public void setOrgCodePicPath(String orgCodePicPath) {
        this.orgCodePicPath = orgCodePicPath;
    }
}

6.编写Bill实体类

package com.openlab.pojo;

import java.util.Date;

public class Bill {
    private int id;
    private String billCode;
    private String productName;
    private String productDesc;
    private String productUnit;
    private double productCount;
    private double totalPrice;
    private int isPayment;
    private int createdBy;
    private Date creationDate;
    private int modifyBy;
    private Date modifyDate;
    private int providerId;

    private Provider provider;

    public Provider getProvider() {
        return provider;
    }

    public void setProvider(Provider provider) {
        this.provider = provider;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBillCode() {
        return billCode;
    }

    public void setBillCode(String billCode) {
        this.billCode = billCode;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public String getProductDesc() {
        return productDesc;
    }

    public void setProductDesc(String productDesc) {
        this.productDesc = productDesc;
    }

    public String getProductUnit() {
        return productUnit;
    }

    public void setProductUnit(String productUnit) {
        this.productUnit = productUnit;
    }

    public double getProductCount() {
        return productCount;
    }

    public void setProductCount(double productCount) {
        this.productCount = productCount;
    }

    public double getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(double totalPrice) {
        this.totalPrice = totalPrice;
    }

    public int getIsPayment() {
        return isPayment;
    }

    public void setIsPayment(int isPayment) {
        this.isPayment = isPayment;
    }

    public int getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(int createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public int getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(int modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    public int getProviderId() {
        return providerId;
    }

    public void setProviderId(int providerId) {
        this.providerId = providerId;
    }
}

7.编写ProviderMapper接口

package com.openlab.mapper;

import com.openlab.pojo.Provider;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface ProviderMapper {

    List<Provider> selectBillByProviderIdArray(Integer[] ids);

    List<Provider> selectBillByProviderIdList(List<Integer> ids);

    List<Provider> selectUnclear(@Param("code") String code ,@Param("name") String name,
                                 @Param("people") String people,@Param("year") String year);
}

8.编写ProviderMapper.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.openlab.mapper.ProviderMapper">

    <resultMap id="providerResultMap" type="Provider">
        <id property="id" column="id"></id>
        <result property="proCode" column="proCode"></result>
        <result property="proName" column="proName"></result>
        <result property="proContact" column="proContact"></result>
        <result property="proAddress" column="proAddress"></result>
        <result property="creationDate" column="creationDate"></result>
        <collection property="bills" ofType="Bill">
            <id property="id" column="bid"></id>
            <result property="billCode" column="billCode"></result>
            <result property="productName" column="productName"></result>
            <result property="totalPrice" column="totalPrice"></result>
            <result property="isPayment" column="isPayment"></result>
            <result property="creationDate" column="bDate"></result>
        </collection>
    </resultMap>

    <select id="selectBillByProviderIdArray" resultMap="providerResultMap">
        select p.*,b.id as bid,b.billCode,b.productName,b.totalPrice,b.isPayment,b.creationDate bDate
         from smbms_provider p
         left join smbms_bill b
         on b.providerId = p.id
         where b.providerId in
         <foreach collection="array" item="ids" open="(" separator="," close=")">
             #{ids}
         </foreach>
    </select>

    <select id="selectBillByProviderIdList" resultMap="providerResultMap">
        select p.*,b.id as bid,b.billCode,b.productName,b.totalPrice,b.isPayment,b.creationDate bDate
        from smbms_provider p
        left join smbms_bill b
        on b.providerId = p.id
        where b.providerId in
        <foreach collection="list" item="ids" open="(" separator="," close=")">
            #{ids}
        </foreach>
    </select>

    <select id="selectUnclear" resultType="Provider">
       select * from smbms_provider where 1 = 1
        <choose>
            <when test="code!=null and code!=''">
                and proCode like concat('%',#{code},'%')
            </when>
            <when test="name!=null and name!=''">
                and proName like concat('%',#{name},'%')
            </when>
            <when test="people!=null and people!=''">
                and proContact like concat('%',#{people},'%')
            </when>
            <when test="year!=null and year!=''">
                and year(creationDate) = #{year}
            </when>
        </choose>
    </select>
</mapper>

9.测试

package com.openlab.test;

import com.openlab.mapper.ProviderMapper;
import com.openlab.pojo.Bill;
import com.openlab.pojo.Provider;
import com.openlab.util.SqlSessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class TestExer {
    @Test
    public void test1(){
        SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
        Integer[] array = new Integer[]{4,8,9};
        List<Provider> providers = sqlSession.getMapper(ProviderMapper.class).selectBillByProviderIdArray(array);
        for(Provider provider:providers){
            System.out.println("供应商：");
            System.out.println(provider.getId()+"\t"+
                    provider.getProCode()+"\t"+
                    provider.getProName()+"\t"+
                    provider.getProContact()+"\t"+
                    provider.getProAddress()+"\t"+
                    provider.getCreationDate());
            System.out.println("该供应商的账单：");
            for (Bill bill : provider.getBills()){
                int num = bill.getIsPayment();
                String isPay = null;
                if(num==1){
                    isPay = "未付款";
                }else if(num==2){
                    isPay = "已付款";
                }
                System.out.println(bill.getBillCode() + "\t"
                        + bill.getProductName() + "\t"
                        + provider.getProName() + "\t"
                        + bill.getTotalPrice() + "\t"
                        + isPay + "\t"
                        + bill.getCreationDate());
            }
        }
        SqlSessionFactoryUtil.closeSqlSession(sqlSession);
    }

    @Test
    public void test2(){
        SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
        List<Integer> list = new ArrayList<>();
        list.add(4);
        list.add(8);
        list.add(9);
        List<Provider> providers = sqlSession.getMapper(ProviderMapper.class).selectBillByProviderIdList(list);
        for(Provider provider:providers){
            System.out.println("供应商：");
            System.out.println(provider.getId()+"\t"+
                    provider.getProCode()+"\t"+
                    provider.getProName()+"\t"+
                    provider.getProContact()+"\t"+
                    provider.getProAddress()+"\t"+
                    provider.getCreationDate());
            System.out.println("该供应商的账单：");
            for (Bill bill : provider.getBills()){
                int num = bill.getIsPayment();
                String isPay = null;
                if(num==1){
                    isPay = "未付款";
                }else if(num==2){
                    isPay = "已付款";
                }
                System.out.println(bill.getBillCode() + "\t"
                        + bill.getProductName() + "\t"
                        + provider.getProName() + "\t"
                        + bill.getTotalPrice() + "\t"
                        + isPay + "\t"
                        + bill.getCreationDate());
            }
        }
        SqlSessionFactoryUtil.closeSqlSession(sqlSession);
    }

    @Test
    public void test3(){
        SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
        List<Provider> providers =
                sqlSession.getMapper(ProviderMapper.class).selectUnclear("1", "", "", "");
        for(Provider provider : providers){
            System.out.println(provider.getId()+"\t"+
                    provider.getProCode()+"\t"+
                    provider.getProName()+"\t"+
                    provider.getProContact()+"\t"+
                    provider.getProAddress()+"\t"+
                    provider.getCreationDate());
        }
        SqlSessionFactoryUtil.closeSqlSession(sqlSession);
    }
}
wz1997123
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值