samedi 30 avril 2016

Wrong number of rows updated in SQLite DB

I am trying to change a set amount of rows randomly in a SQLite DB. The problem is that the number of rows changed is alsways greater than the one intended. Could somebody point out my mistake?

"probM" and "probF" should be the probability for the rows to be changed. For example, when probM is set to 5, countM goes up to about 120. But instead of 120 rows being updated 970 are.

"DBUpdater.count" returns the number of rows for a given collumn and WHERE statement.

public static void verwitwetStmt(int probM, int probF) {
    String select;
    String preparedStmt;
    Random r = new Random();
    int rnd;
    int countW = 0;
    int countM = 0;
    int anzahl = DBUpdater.count("Jahre", "Jahre BETWEEN 66 AND 70 AND Geschlecht IS 0");

    for (int i = 0; i < anzahl; i++) {
        rnd = r.nextInt(100);
        if (rnd <= probM)
            countM++;
        }
    System.out.println(anzahl + ", " +countM);
    select = "SELECT ID FROM individuen WHERE Jahre BETWEEN 66 AND 70 AND Familienstand IS NULL "
            + "AND Geschlecht IS 0 ORDER BY RANDOM() LIMIT " + Integer.toString(countM);
    preparedStmt = "UPDATE individuen SET Familienstand = ? WHERE ID = ?";

    DBUpdater.blankUpdate(select, preparedStmt, 3);

    anzahl = DBUpdater.count("Jahre", "Jahre BETWEEN 66 AND 70 AND Geschlecht IS 1");

    for (int i = 0; i < anzahl; i++) {
        rnd = r.nextInt(100);
        if (rnd <= probF)
            countW++;
        }

    select = "SELECT ID FROM individuen WHERE Jahre BETWEEN 66 AND 70 AND Familienstand IS NULL "
            + "AND Geschlecht IS 1 ORDER BY RANDOM() LIMIT " + Integer.toString(countW);
    preparedStmt = "UPDATE individuen SET Familienstand = ? WHERE ID = ?";

    DBUpdater.blankUpdate(select, preparedStmt, 3);
}

Here is the blankUpdate method:

public static void blankUpdate(String selectQuery, String preparedStatement, int wert) {
    DBController dbc = DBController.getInstance();
    dbc.initDBConnection();
    try {
        Statement stmt = DBController.connection.createStatement();
        ResultSet rs = stmt.executeQuery(selectQuery);
        PreparedStatement ps = DBController.connection.prepareStatement(preparedStatement);

        while (rs.next()) {
            ps.setInt(1, wert);
            ps.setInt(2, rs.getInt(1));
            ps.addBatch();
        }
        DBController.connection.setAutoCommit(false);
        ps.executeBatch();
        DBController.connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

And here the count method:

public static int count(String zeile, String whereStmt) {
    DBController dbc = DBController.getInstance();
    dbc.initDBConnection();
    int anzahl = 0;
    try {
        Statement stmt = DBController.connection.createStatement();
        String select = "SELECT COUNT(" + zeile + ") FROM individuen WHERE " + whereStmt + ";";
        ResultSet rs = stmt.executeQuery(select);

        select = rs.getString(1);
        anzahl = Integer.parseInt(select);
        rs.close();
        stmt.close();

    } catch (SQLException e) {
        e.printStackTrace();
    }
    return anzahl;
}

Aucun commentaire:

Enregistrer un commentaire