JDBC MYSQL FK实现

问题描述

我正在尝试使用JDBC从MySQL到JAVA实现FK关系.我有一个Garaz对象的列表，每个Garaz都有一个Auto(汽车)对象的列表.我的数据非常复杂.

I am trying to implement FK relation from MySQL into JAVA using JDBC. I have a list of Garaz objects and each Garaz has a list of Auto (cars) objects. I have very mixed data.

我的MySQl DB正常，我尝试这样做:

My MySQl DB is ok and I try to do it like this:

public static ArrayList <Garaz> selectRecords() throws SQLException {
    Connection dbConnection = null;
    Statement statement = null;

    String selectTableSQL = "SELECT Garaz.G_ID, Garaz.Nazwa, Garaz.Adres, Garaz.LiczbaMiejsc, Garaz.LiczbaPoziomow, " +
        "Garaz.Czynny, Auta.A_Id, Auta.Model, Auta.Kolor, Auta.IloscDrzwi, Auta.Rejestracja\n" +
        "FROM Garaz\n" +
        "LEFT JOIN Auta\n" +
        "ON Garaz.G_Id=Auta.G_Id\n" +
        "ORDER BY Garaz.G_Id; ";

    // ArrayList lista = new ArrayList <Garaz>();

    try {
        dbConnection = getDBConnection();
        statement = dbConnection.createStatement();

        System.out.println(selectTableSQL);

        // execute select SQL stetement
        ResultSet rs = statement.executeQuery(selectTableSQL);

        while (rs.next()) {
            int g_id = rs.getInt("G_ID");
            String nazwa = rs.getString("NAZWA");
            String adres = rs.getString("ADRES");
            int lmiejsc = rs.getInt("LICZBAMIEJSC");
            int lpoz = rs.getInt("LICZBAPOZIOMOW");
            boolean czynny = rs.getBoolean("CZYNNY");

            ArrayList lista2 = new ArrayList <Auto>();

            int a_id = rs.getInt("A_Id");
            String model = rs.getString("Model");
            String kolor = rs.getString("Kolor");
            int ildrzwi = rs.getInt("IloscDrzwi");
            String rejestracja = rs.getString("Rejestracja");

            Auto d = new Auto(a_id, model, kolor, ildrzwi, rejestracja);
            if (a_id !=0){
                lista2.add(d);
            }
            Garaz f = new Garaz(g_id, nazwa, lista2, adres, lmiejsc, lpoz, czynny);
            lista.add(f);

            //System.out.println("nazwa : " + nazwa);
            //System.out.println("adres : " + adres);
            // return lista;
        }

    } catch (SQLException e) {
        System.out.println(e.getMessage());
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (dbConnection != null) {
            dbConnection.close();
        }
    }
    return lista;
}

我不明白如何以下列方式从ResultSet rs中读取:ArrayList Garaz包含对象(Garaz)，每个Garaz对象包含ArrayList Auto.因此，通过从rs(ResultSet)读取数据来创建2个列表(一个是另一个的一部分)存在很大的问题.我有数据库表中的所有Garaz和所有Auto，但是关系是混杂的.就像Garaz1包含随机的Auto(汽车).

I don't understand how to read from ResultSet rs in the way that: ArrayList Garaz contains objects (Garaz) and each Garaz object contains ArrayList Auto. So I have big problem with creating 2 lists (one is part of another) by reading data from rs (ResultSet). I have all Garaz and all Auto from DB tables, but the relation is mixed. Like Garaz1 contains random Auto (cars).

如何创建2个列表(一个是另一个的一部分)以保持关系Auto是基于G_ID的Garaz的一部分?

How do I create 2 lists (one is part of another) to keep the relation Auto is part of Garaz based on G_ID?

推荐答案

您需要遍历结果，检查是否已经为该行的G_ID创建了Garaz对象，然后使用该对象或创建一个新的一个.可以通过对G_ID字段进行排序来简化此操作，并在G_ID更改时仅创建一个新的Garaz对象.

You need to iterate over the result, check if you already created the Garaz object for the G_ID of the row and either use that or create a new one. This can be simplified by sorting on the G_ID field and just create a new Garaz object when the G_ID changes.

在您评论您不知道如何执行操作时，下面是一个完整的示例:

As you comment that you don't know how to do this, here is a full example:

public List<Garaz> getAllGaraz() throws SQLException {
    List<Garaz> garazList = new ArrayList<Garaz>();
    try (
        Connection con = getDBConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(
            "SELECT Garaz.G_ID, /* other garaz columns */ " +
                   "Auta.A_Id /*other auta columns */\n" +
            "FROM Garaz\n" +
            "LEFT JOIN Auta\n" +
            "ON Garaz.G_Id=Auta.G_Id\n" +
            "ORDER BY Garaz.G_Id");
    ) {
        Garaz currentGaraz = null;
        while (rs.next()) {
            int garazId = rs.getInt("G_ID");
            // Create Garaz only if it is different
            if (currentGaraz == null || currentGaraz.getId() != garazId) {
                // retrieve other columns
                currentGaraz = new Garaz(g_id /* + other garaz columns */);
                garazList.add(currentGaraz);
            }

            int a_id = rs.getInt("A_Id");
            // replacement of your condition of a_id != 0
            // 0 could be a valid value, check for null instead
            if (!rs.wasNull()) {
                // retrieve other columns
                Auto auta = new Auta(a_id /* + other auta columns */);
                // The list of Auta is part of the garaz
                currentGaraz.addAuta(auta);
            }
        }
        return garazList;
    }
}

public class Garaz {
    private final List<Auta> autaList = new ArrayList<Auta>();
    private final int id;

    public Garaz(int g_id /* + other fields*/) {
        id = g_id;
    }

    public int getId() {
        return id;
    }

    public void addAuta(Auta auta) {
        autaList.add(auta);
    }

    public List<Auta> getAutaList() {
        return new ArrayList<Auta>(autaList);
    }
}

这篇关于JDBC MYSQL FK实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！