Mybatis数据库一对多查询

最新推荐文章于 2024-04-11 10:00:49 发布

Xadiel

最新推荐文章于 2024-04-11 10:00:49 发布

阅读量228

点赞数

分类专栏： #Mybatis 文章标签： mysql mybatis 开发语言 maven java Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_69348383/article/details/129743447

版权

#Mybatis 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1，进行一对多查询，首先准备两个mysql数据表，查询读者与借书信息的关联关系

#创建readers表用来储存读者信息

CREATE TABLE `readers`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`phone` DOUBLE(30,0) NOT NULL,
`code` VARCHAR(50) NOT NULL
);
#插入数据
INSERT INTO readers VALUES(1,'admin',19872675353,'AS0001');

#创建lend_books表用来储存书籍信息

CREATE TABLE `lend_books`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`bksName` VARCHAR(255) NOT NULL,
`price` DOUBLE(30,2) NOT NULL,
`date` DATE NOT NULL,
`bksCode` VARCHAR(50) NOT NULL,
`lend_id` INT UNIQUE,
FOREIGN KEY (lend_id) REFERENCES readers(id)
);
#插入数据
INSERT INTO lend_books VALUES(NULL,'《测试》',100.00,'2023-3-24',2023843,NULL);

2，在IDEA创建maven项目并命名JavaEE

在项目src/main/java下创建com.three.pojo包

#在pojo包下创建Readers类封装属性值及返回成员变量：

package com.three.pojo.reader;

import java.util.List;

public class Readers {
    private int id;
    private String name;
    private String phone;
    private String code;
    private List<Lend_Books> lend_booksList;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public List<Lend_Books> getLend_booksList() {
        return lend_booksList;
    }

    public void setLend_booksList(List<Lend_Books> lend_booksList) {
        this.lend_booksList = lend_booksList;
    }

    @Override
    public String toString() {
        return "Readers{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", phone='" + phone + '\'' +
                ", code='" + code + '\'' +
                ", lend_booksList=" + lend_booksList +
                '}';
    }
}

#在pojo包下创建Lend_Books类封装属性值及返回成员变量：

package com.three.pojo.reader;

import java.sql.Date;

public class Lend_Books {
    private int id;
    private String bksName;
    private Double price;
    private Date date;
    private String bksCode;

    public int getId() {
        return id;
    }

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

    public String getBksName() {
        return bksName;
    }

    public void setBksName(String bksName) {
        this.bksName = bksName;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public String getBksCode() {
        return bksCode;
    }

    public void setBksCode(String bksCode) {
        this.bksCode = bksCode;
    }

    @Override
    public String toString() {
        return "Lend_Books{" +
                "id=" + id +
                ", bksName='" + bksName + '\'' +
                ", price=" + price +
                ", date=" + date +
                ", bksCode='" + bksCode + '\'' +
                '}';
    }
}

在项目com.three下创建dao包并在当前位置创建接口类InformationMapper

package com.three.dao;

import com.three.pojo.person.PersonInformation;
import com.three.pojo.reader.Readers;

import java.util.List;

public interface InformationMapper {
    public List<Readers> selectOne_for_many(Integer id);

}

在com.three下创建包untils包存放工具类Mybatis

package com.three.utils;

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

public class Mybatis {
    private static SqlSessionFactory sqlSessionFactory=null;
    static {
        try {
            Reader reader= Resources.getResourceAsReader("mybatis-config.xml");
            SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
            sqlSessionFactory=builder.build(reader);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }
}

#为了美观查询界面，为此准备了一个Swing窗口JFrame，JTable的TableOne_for_many

package com.three.utils;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;

public class TableOne_for_many extends JFrame {
    private static JLabel jLabel1,jLabel2;
    private static DefaultTableModel dTableModel1,dTableModel2;
    private static JTable table1,table2;
    private static JScrollPane jScrollPane1,jScrollPane2;
    private static JPanel jPanelA,jPanelB,jPanel1,jPanel2;
    private static Object[] columns1 = {"读者ID","读者姓名","电话","卡号"};
    private static Object[] columns2 = {"书籍ID", "书籍名称","借书价格", "借书日期", "书籍编号"};

    private static Object[][] data = null;

    {
        jScrollPane1=new JScrollPane();
        jPanel1=new JPanel();
        jPanel1.setBackground(Color.magenta);
        dTableModel1=new DefaultTableModel(data,columns1);
        dTableModel1.setRowCount(0);
        table1=new JTable(dTableModel1);
        table1.setBackground(Color.CYAN);
        jScrollPane1.setViewportView(table1);

        jScrollPane2=new JScrollPane();
        jPanel2=new JPanel();
        jPanel2.setBackground(Color.blue);
        dTableModel2=new DefaultTableModel(data,columns2);
        dTableModel2.setRowCount(0);
        table2=new JTable(dTableModel2);
        table2.setBackground(Color.yellow);
        jScrollPane2.setViewportView(table2);

        jPanel1.add(jScrollPane1);;
        jPanel2.add(jScrollPane2);

        this.setTitle("读者借书信息：(一对多查询)");
        this.setBounds(100,0,1000,500);
        this.setBackground(Color.PINK);
        this.setLayout(new FlowLayout(FlowLayout.CENTER));
        this.add(jPanel1);
        this.add(jPanel2);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setVisible(true);
    }
    public DefaultTableModel addTable1(){
        return dTableModel1;
    }
    public DefaultTableModel addTable2(){
        return dTableModel2;
    }
}

在当前dao包下创建impl包并在此包下实现类InformationMapperImpl

package com.three.dao.impl;

import com.three.dao.InformationMapper;
import com.three.pojo.person.PersonInformation;
import com.three.pojo.reader.Readers;
import com.two.utils.Mybatis;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class InformationMapperImpl implements InformationMapper {
    @Override
    public List<Readers> selectOne_for_many(Integer id) {
        SqlSession session=Mybatis.getSqlSession();
        return session.selectList("selectOne_for_many",id);
    }

}

在resource下创建文件夹mappers，并在文件夹下创建InformationMapper.xml编写SQL语句

<?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" >
<!--namespace的值通常为接口的全限定名-->
<mapper namespace="com.three.dao.InformationMapper">
<!--    一对多查询借阅者与借书信息。-->
    <select id="selectOne_for_many" parameterType="Integer" resultMap="lend_BooksInformation">
        select r.*,l.id as bkId,l.bksName,l.price,l.date,l.bksCode
        from readers r,lend_books l
        where r.id=l.lend_id
        and r.id=#{id}
    </select>
    <resultMap id="lend_BooksInformation" type="Readers">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="phone" column="phone"/>
        <result property="code" column="code"/>
        <collection property="lend_booksList" ofType="Lend_Books">
            <id property="id" column="bkId"/>
            <result property="bksName" column="bksName"/>
            <result property="price" column="price"/>
            <result property="date" column="date"/>
            <result property="bksCode" column="bksCode"/>
        </collection>
    </resultMap>
</mapper>

在测试包下编写测试类Three

import com.three.dao.InformationMapper;
import com.three.dao.impl.InformationMapperImpl;
import com.three.pojo.person.Person;
import com.three.pojo.person.PersonInformation;
import com.three.pojo.reader.Lend_Books;
import com.three.pojo.reader.Readers;
import com.three.utils.TableOne_for_many;
import com.three.utils.TableOne_for_one;

import java.util.List;

public class Three {
    public static void main(String[] args) {
        Three three=new Three();
        three.one_for_many();
    }
    public void one_for_many(){
        InformationMapper informationMapper=new InformationMapperImpl();
        List<Readers> informations = informationMapper.selectOne_for_many(1);
        TableOne_for_many table=new TableOne_for_many();
        for (Readers readers:informations){
            String[] arr=new String[4];
            arr[0]= String.valueOf(readers.getId());
            arr[1]= readers.getName();
            arr[2]= readers.getPhone();
            arr[3]= readers.getCode();
            table.addTable1().addRow(arr);

            List<Lend_Books> lend_books=readers.getLend_booksList();
            for (Lend_Books lendBooks:lend_books) {
                String[] brr = new String[5];
                brr[0] = String.valueOf(lendBooks.getId());
                brr[1] = lendBooks.getBksName();
                brr[2] = String.valueOf(lendBooks.getPrice());
                brr[3] = String.valueOf(lendBooks.getDate());
                brr[4] = lendBooks.getBksCode();
                table.addTable2().addRow(brr);
            }
        }
        System.out.println(informations);
    }
}

测试结果如下