Java一对多右外连接程序错误排查求助

问题描述 #

编写实现**一对多右外连接（One To Many Right Outer Join）**的Java程序时出现错误，尝试修改数据库列名、使用Eclipse调试均未定位到问题根源。

代码文件 #

MainClass.java #

package OneToManyRightJoin;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MainClass {

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/person","root","1234");
            Statement st1 = con.createStatement();
            Statement st2 = con.createStatement();
            
            String query1 = "SELECT * from person";
            String query2 = "SELECT * from address";
            
            ResultSet rs1 = st1.executeQuery(query1);
            ResultSet rs2 = st2.executeQuery(query2);   
    
            List<Address> addrList = new ArrayList<>();
            List<Person> personList = new ArrayList<>();
            
            while(rs1.next()) {
                Address a = new Address();
                a.setPid(rs1.getInt("pid"));
                a.setAid(rs1.getInt("Aid"));
                a.setAddress1(rs1.getString("Ad1"));
                a.setAddress2(rs1.getString("Ad2"));
                addrList.add(a);
            }
     
            while(rs2.next()) {
                Person p = new Person();
                p.setPid(rs2.getInt("pid"));
                p.setName(rs2.getString("name"));
                personList.add(p);
            }   
    
            List matchedList = new ArrayList<List>();
            
            for(int i=0;i<addrList.size();i++) {
                List innerPersonList = new ArrayList();
                Address pkgInnerAddress = addrList.get(i);
                
                for(int j=0;j<personList.size();j++) {                     
                    if(addrList.get(i).getPid() == personList.get(j).getPid()) {
                        Person innerPerson = personList.get(j);
                        innerPersonList.add(innerPerson);
                    }   
                }
                pkgInnerAddress.setLst(innerPersonList);
                matchedList.add(pkgInnerAddress);
            }   
    
            System.out.println("Right Outer join details" +"\n");
            for (int i=0; i<matchedList.size(); i++) {
                Address a = (Address)matchedList.get(i);
                System.out.println("\naddress details Id Ad1 Ad2 are "+a.getAid()+" "+a.getAddress1()+" "+a.getAddress2()+" "+a.getPid()+"\n");
                
                for (int j=0; j<a.getLst().size(); j++) {
                    System.out.println(" Person Pid is " +a.getLst().get(j).getPid());  
                }
            }
        } catch (SQLException e) {
            System.out.println(e);          
        } catch(ClassNotFoundException e1) {
            System.out.println(e1);     
        }
    }
}

Address.java #

package OneToManyRightJoin;

import java.util.List;

public class Address {
    
    int aid;
    int Pid;
    String Address1;
    String Address2;
    List<Person> lst;
    
    public int getAid() {
        return aid;
    }
    public void setAid(int aid) {
        this.aid = aid;
    }
    public int getPid() {
        return Pid;
    }
    public void setPid(int pid) {
        Pid = pid;
    }
    public String getAddress1() {
        return Address1;
    }
    public void setAddress1(String address1) {
        Address1 = address1;
    }
    public String getAddress2() {
        return Address2;
    }
    public void setAddress2(String address2) {
        Address2 = address2;
    }
    public List<Person> getLst() {
        return lst;
    }
    public void setLst(List<Person> lst) {
        this.lst = lst;
    }
}

Person.java #

package OneToManyRightJoin;

public class Person {
    int pid;
    String Name;
    
    Person() {}

    public int getPid() {
        return pid;
    }
    public void setPid(int pid) {
        this.pid = pid;
    }
    public String getName() {
        return Name;
    }
    public void setName(String name) {
        Name = name;
    }
}

数据库结构 #

Address表 #

Person表 #

错误详情 #

java.sql.SQLException: Column 'Aid' not found.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:541)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:806)
    at OneToManyRightJoin.MainClass.main(MainClass.java:33)

问题排查与修复 #

核心问题 #

1. 查询与对象填充完全混淆：query1查询的是person表，但代码中用该结果集rs1去填充Address对象。person表不存在Aid、Ad1、Ad2字段，直接引发Column 'Aid' not found错误。
2. 右外连接逻辑错误：一对多右外连接（以Person为基准）需要保留所有Person记录，匹配对应的Address；但当前代码是遍历Address找Person，完全颠倒了逻辑。
3. 列名大小写不匹配：数据库中字段为小写aid，代码中用大写Aid，易引发不必要的匹配问题。
4. 集合类型不规范：matchedList使用原始类型List<List>，存在类型转换风险。

修复后的MainClass代码 #

package OneToManyRightJoin;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MainClass {

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/person","root","1234");
            Statement stAddr = con.createStatement();
            Statement stPerson = con.createStatement();
            
            // 修正查询：queryAddr查address表，queryPerson查person表
            String queryAddr = "SELECT * from address";
            String queryPerson = "SELECT * from person";
            
            ResultSet rsAddr = stAddr.executeQuery(queryAddr);
            ResultSet rsPerson = stPerson.executeQuery(queryPerson);   
    
            List<Address> addrList = new ArrayList<>();
            List<Person> personList = new ArrayList<>();
            
            // 用address的结果集填充Address列表
            while(rsAddr.next()) {
                Address a = new Address();
                a.setPid(rsAddr.getInt("pid"));
                a.setAid(rsAddr.getInt("aid")); // 修正列名为小写aid
                a.setAddress1(rsAddr.getString("Ad1"));
                a.setAddress2(rsAddr.getString("Ad2"));
                addrList.add(a);
            }
     
            // 用person的结果集填充Person列表
            while(rsPerson.next()) {
                Person p = new Person();
                p.setPid(rsPerson.getInt("pid"));
                p.setName(rsPerson.getString("name"));
                personList.add(p);
            }   
    
            // 右外连接：以Person为基准，保留所有Person，匹配对应的Address
            System.out.println("Right Outer join details\n");
            for(Person person : personList) {
                List<Address> matchedAddrs = new ArrayList<>();
                // 查找当前Person对应的所有Address
                for(Address addr : addrList) {
                    if(addr.getPid() == person.getPid()) {
                        matchedAddrs.add(addr);
                    }
                }
                
                // 打印右外连接结果
                System.out.println("Person: pid=" + person.getPid() + ", name=" + person.getName());
                if(matchedAddrs.isEmpty()) {
                    System.out.println("  无关联地址\n");
                } else {
                    System.out.println("  关联地址：");
                    for(Address addr : matchedAddrs) {
                        System.out.println("    aid=" + addr.getAid() + ", Ad1=" + addr.getAddress1() + ", Ad2=" + addr.getAddress2());
                    }
                    System.out.println();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();          
        } catch(ClassNotFoundException e1) {
            e1.printStackTrace();     
        }
    }
}

额外优化建议 #

• 给Person类添加List<Address>属性，方便存储关联的地址数据。
• 使用PreparedStatement替代Statement，提升安全性和性能。
• 直接用SQL实现右外连接，比Java代码手动匹配更高效：

  -- 保留所有Person记录，关联对应的Address（无则显示null）
  SELECT p.*, a.aid, a.Ad1, a.Ad2 
  FROM address a 
  RIGHT JOIN person p ON a.pid = p.pid;
  

内容的提问来源于stack exchange，提问作者Sourabh