CODEWITHSUNDEEP
javasql
UsefulUserName
UsefulUserName

Reputation: 595

Updating a value in a random row through java in a sql table

Image of my table as it is when I start this program.

What I have is a mostly empty table and I am trying to assign a value to a fixed number of elements. The column I am trying to edit is "Geschlecht" and the number of rows I want to edit is "copyMaen" (~50.000 entries). I would like to only select the rows where the value of "Geschlecht" was NULL before and I would like to select the rows randomly.

I am using SQLite through a JDBC driver.

This is the first time for me working with sql. This is how I tried to do it.

                try {
        Statement stmt = DBController.connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT Geschlecht FROM individuen WHERE Geschlecht IS NULL;");
        PreparedStatement ps = DBController.connection.prepareStatement("UPDATE individuen");
        while (copyMaen != 0) {
            if (rs.getRowId((int) (Math.random() * ReadCSV.sumBev)) == null) {
                ps.setInt(2, 0);
                ps.executeUpdate();
                copyMaen--;
            }
        }
        rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

Obviosly this throws me Errors and I am not really sure how to go on from there. Could somebody point me in the right direction?

Upvotes: 0

Views: 327

Answers (2)

UsefulUserName
UsefulUserName

Reputation: 595

For anybody interested this is the solution: 

try {
        Statement stmt = DBController.connection.createStatement();
        String select = "SELECT ID FROM individuen WHERE Geschlecht is NULL ORDER BY RANDOM()" +
        " LIMIT " + Integer.toString(copyMaen);
        ResultSet rs = stmt.executeQuery(select);
        PreparedStatement ps = DBController.connection.prepareStatement("UPDATE individuen set Geschlecht  = ? WHERE ID = ?;");
        // rs.beforeFirst();
        int count = 0;
        while (rs.next()) {
            ps.setInt(1, 0);
            ps.setInt(2, rs.getInt(1));
            ps.addBatch();
            if (count%100==0) {
                System.out.println(count);
            }
            count++;
        }
        DBController.connection.setAutoCommit(false);
        ps.executeBatch();
        DBController.connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Upvotes: 1

Vishal Gajera
Vishal Gajera

Reputation: 4207

Try this,

Below update query is enough for update all row where Geschlecht is null.

try {
        Statement stmt = DBController.connection.createStatement();
        String updateTableSQL = "UPDATE individuen set Geschlecht  = ? where Geschlecht IS NULL";
        PreparedStatement preparedStatement = dbConnection.prepareStatement(updateTableSQL);
        preparedStatement.setString(1, "0"); // set zero where Geschlecht null found
        // execute update SQL stetement
        preparedStatement.executeUpdate();
} catch (SQLException e) {
        e.printStackTrace();
}

Upvotes: 0

Related Questions