Batch didn't run, JDBC

Question

I got a little problem with running my batch process..

First of all, I'm still at learning JDBC etc. so please excuse me, if my mistake is really stupid..

I have to insert some SQL queries into my database. Therefore, I'm building my PreparedStatements like this:

String add = "INSERT INTO company_type VALUES (?,?)";
PreparedStatement company = write.getConnection().prepareStatement(add);

ResultSet com = types.getCompanyType();

while(com.next()) {
    System.out.print("1");
    company.setInt(1, com.getInt(1));
    company.setString(2, com.getString(2));
    company.addBatch();

    if(i > size) {
        company.executeBatch();
        i = 0;
    }
    i++;
} 

company.executeBatch();

After going through with the debugger, I know something went wrong with the company.executeBatch(); line.

It just didn't execute and it seems like the program i running into a loop or standing still.

Is there anybody, how can see a mistake?

Thanks

P.s. I did not get any Exception.

public Connection getConnection(){

    Connection connection = null;

    try{
        Class.forName( "oracle.jdbc.driver.OracleDriver" );
    } catch(ClassNotFoundException e) {
        this.loginmessage.setText("Fehler beim Laden des Oracle JDBC Treibers!");
        e.printStackTrace();
        return connection;
    }

    try {
        connection = DriverManager.getConnection("j******************);
    } catch (SQLException e) {
        this.loginmessage.setText("Verbindung fehlgeschlagen!");
        e.printStackTrace();
        return connection;
    }

    if (connection != null) {
        loginmessage.setText("Verbindung hergestellt!");
        return connection;
    } else {
        loginmessage.setText("Verbindung fehlgeschlagen!");
        System.out.print("huaha");
    }
    return connection;
}

Do you get any exception? If yes which one? ([Edit] your question - do **not** post code or additional information in comments) — , Jun 18 '18 at 08:52
Hey, first of all, thanks for answering.. No, i didn't get any exception :S — Jonas Ricke, Jun 18 '18 at 08:55
It could be nice to know the DB and to know what is `write` type and `write.getConnection` (if it is your own implementation or not) — AxelH, Jun 18 '18 at 09:17
I'm reading from a PostgreSQL Database and try to write into a Oracle Database I'll post the methods upside — Jonas Ricke, Jun 18 '18 at 09:21
You might need to check [Batch update in Postgresql JDBC driver rolls back in autocommit](https://stackoverflow.com/questions/29485287/batch-update-in-postgresql-jdbc-driver-rolls-back-in-autocommit). Check the result of the `executeBatch` method. It might tell you that some rows have failed to be inserted, aborting the transaction for the batch, as PostgreSQL seems to have decide to do so. — AxelH, Jun 18 '18 at 09:32
FYI: don't return `connection` (being `null`) in case of an `Exception`..., this doesn't make sense, throw an exception instead. — AxelH, Jun 18 '18 at 09:38

Ravindra Ranwala · Answer 1 · 2020-02-01T06:38:04.283

2

You have to commit the transaction like so,

write.getConnection().commit();

Here are the steps involved in batch processing,

First turn off autocommit, so that all the Batch statements execute in a single transaction and no operation in the batch is committed individually. After all this is what we need.
Using addBatch add as many statements as required to the Batch.
Then execute the Batch of statements by invoking the executeBatch()
Finally commit or roll-back the transaction.

A sample code is given below.

public void saveLogEntries(List<LogEntry> entries) {
    try (Connection connObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS);
        PreparedStatement prepareStatement = connObj
            .prepareStatement("INSERT INTO logentry (date, ip, request) values(?, ?,?)")) {
                connObj.setAutoCommit(false);
                for(LogEntry entry : entries) {
                    try {
                        prepareStatement.setString(1, entry.getDate());
                        prepareStatement.setString(2, entry.getIp());
                        prepareStatement.setString(3, entry.getRequest());
                        prepareStatement.addBatch();
                    } catch (SQLException e) {
                        // Handle SQL exception here.
                    }
                };

                prepareStatement.executeBatch();
                connObj.commit();
    } catch (SQLException e) {
        // Handle SQL exception here.
    }
}

However Class.forName() is NOT needed anymore. Check out this post for further details. You may change the way you obtain the connection and try it again. The code seems bit clumsy to me.

edited Feb 01 '20 at 06:38

answered Jun 18 '18 at 08:56

Ravindra Ranwala

20,744
6
45
63

I've read about that but before I added some new querrys, everything worked in the way i posted. I'll try it with the commit().. Thanks :) – Jonas Ricke Jun 18 '18 at 08:59
I added it behind my executeBatch() lines but still something went wrong.. It seems like it didn't solve the problem :/ Oh you edited your answer, i'll be back in a few minuits :D – Jonas Ricke Jun 18 '18 at 09:06
Try following the steps mentioned in my answer and sample code. – Ravindra Ranwala Jun 18 '18 at 09:07
Why you have two `executeBatch` statements? what is the purpose? – Ravindra Ranwala Jun 18 '18 at 09:09
1

**Assuming OP did set the auto commit to false.** Or is it design to only use a batch in one transaction ? FYI: About the two execute batch, if you have 100k row to insert, you don't want to send one batch, you send multiple small batch – AxelH Jun 18 '18 at 09:12
My prof told us, we have to use :D Guess because we're working with the imdbDatabase and some of the querrys return some big insert statements – Jonas Ricke Jun 18 '18 at 09:13
Ok then it is used to limit the batch size. Did you add `.commit()` to both the places where you use `executeBatch()` ? – Ravindra Ranwala Jun 18 '18 at 09:15

score 1 · Answer 2 · answered Jun 18 '18 at 08:57

1

One possibility is you haven't committed so the server-side transaction times out and rolls back.

answered Jun 18 '18 at 08:57

Erwin Smout

18,113
4
33
52

Batch didn't run, JDBC

2 Answers2