Mybatis数据库一对一查询

Xadiel

已于 2023-03-23 15:53:13 修改

阅读量168

点赞数 6

分类专栏： #Mybatis 文章标签： mybatis 数据库 java maven Powered by 金山文档

于 2023-03-23 15:53:05 首次发布

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

版权

#Mybatis 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1，进行一对一查询，首先准备两个mysql数据表

#创建user表用来储存用户个人用户名及密码

CREATE TABLE `user`(
`uId` INT(32) PRIMARY KEY AUTO_INCREMENT,
`uName` VARCHAR(255) NOT NULL,
`uPassword` VARCHAR(255) NOT NULL
);
#插入数据：
INSERT INTO `user` VALUES (1, 'admin', '35433');

#创建user_information表用来储存用户个人信息

CREATE TABLE `user_information`  (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` varchar(50),
`address` varchar(255),
`email` varchar(255) ,
`phone` DOUBLE(60,0),
`user_id` INT UNIQUE,
FOREIGN KEY (user_id) REFERENCES user(uId)
);
#插入数据：
INSERT INTO `user_information`(id,name,address,email) VALUES (1, '安娜', '湖南省长沙市', '204854854@qq.com');

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

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

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

package com.three.pojo.person;

public class Person {
    private Integer uId;
    private String uName;
    private String uPassword;

    public Integer getuId() {
        return uId;
    }

    public void setuId(int uId) {
        this.uId = uId;
    }

    public String getuName() {
        return uName;
    }

    public void setuName(String uName) {
        this.uName = uName;
    }

    public String getuPassword() {
        return uPassword;
    }

    public void setuPassword(String uPassword) {
        this.uPassword = uPassword;
    }

    @Override
    public String toString() {
        return "{" +
                "用户ID:" + uId +
                ", 用户名:'" + uName + '\'' +
                ", 用户密码:'" + uPassword + '\'' +
                '}';
    }
}

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

package com.three.pojo.person;

public class PersonInformation {
    private Integer id;
    private String name;
    private String address;
    private String email;
    private Double phone;
    private Person person;

    public Integer 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 getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Double getPhone() {
        return phone;
    }

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

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

    @Override
    public String toString() {
        return "用户信息:{" +
                "ID:" + id +
                ", 姓名:'" + name + '\'' +
                ", 地址:'" + address + '\'' +
                ", 邮箱:'" + email + '\'' +
                ", 电话:" + phone +
                ", 用户账号:" + person +
                '}';
    }
}

在项目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<PersonInformation> selectOne_for_one(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_one

package com.three.utils;

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

public class TableOne_for_one 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<PersonInformation> selectOne_for_one(Integer id) {
        SqlSession session= Mybatis.getSqlSession();
        return session.selectList("selectOne_for_one",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_one" parameterType="Integer" resultMap="personInformations">
        select find.*,serch.uName,serch.uPassword
        from user_information find,user serch
        where find.user_id=serch.uId
        and find.id=#{id}
    </select>
    <resultMap id="personInformations" type="PersonInformation">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="address" column="address"/>
        <result property="email" column="email"/>
        <result property="phone" column="phone"/>
        <association property="person" javaType="Person">
            <id property="uId" column="user_id"/>
            <result property="uName" column="uName"/>
            <result property="uPassword" column="uPassword"/>
        </association>
    </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_one();
    }
    public void one_for_one() {
        InformationMapper informationMapper=new InformationMapperImpl();
        List<PersonInformation> informations = informationMapper.selectOne_for_one(6);
        TableOne_for_one table=new TableOne_for_one();
        for (PersonInformation personInformation:informations){
            String[] arr=new String[5];
            arr[0]= String.valueOf(personInformation.getId());
            arr[1]=personInformation.getName();
            arr[2]=personInformation.getAddress();
            arr[3]=personInformation.getEmail();
            arr[4]= String.valueOf(personInformation.getPhone());
            table.addTable1().addRow(arr);

            Person person=personInformation.getPerson();
            String[] brr=new String[3];
            brr[0]= String.valueOf(person.getuId());
            brr[1]=person.getuName();
            brr[2]= person.getuPassword();
            table.addTable2().addRow(brr);
        }
        System.out.println(informations);

    }
   
}

测试结果如下