JDBC MYSQL FK implementation

问题:

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.

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;
}

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).

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

回答1:

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);
    }
}

回答2:

your result set will provide one result (aka line) for each Garaz and Auto, since thats what the select statement does. So you can...

either parse the resultset as it is, and manually create each Garaz & Auto record you want, but you will have to deal with the duplicate Garaz data.

OR

You can either use a framework like MyBatis to get the get the objects back, or.

OR

Perform a SELECT statement for the list of Garaz, then perform another SELECT statement to get the list of AUTO's back for each Garaz.

---

Sudo code.....  


@Repository
public class StoreDbDAO 
{
    @Autowired  
    public void init(@Qualifier("dataSourceCDB") DataSource dataSource) {
        this.dataSource = dataSource;
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    private static final String GET_USABLE_RECORDS = "SELECT d.ID, d.HARDWARE_ID " +
            " FROM DEVICE d " +
            " LEFT JOIN TABLEB o on o.X_ID = d.X_ID " +
            " WHERE " +
            " d.DEVC_HARDWARE_ID IS NOT NULL " +
            " AND o.CODE = ?  "";


    public List<Map<String, Object>> getStores(String cCode)
    {
        return simpleJdbcTemplate.queryForList(GET_USABLE_RECORDS, code);
    }
}

---

@Autowired StoreDbDAO storeDbDAO;

public void caller() { List> stores = storeDbDAO.getStores();

List<Stores> storeRecords = new ArrayList[stores.size()];
for (Map<String, Object> store: stores)
{

  final String storeId = (String) store.get("HARDWARE_ID");
  StoreRecord x = new StoreRecord(storeId)
  storeRecords.add(x);


  List<Map<String, Object>> devicesInTheStore = storeDbDAO.getDevicesForStore(storeId);
  // convert these into something useful.
  x.setDevicesInStore(convertToList(devicesInTheStore));
}

}