Inserting an Array of Characters into DB

Question

I want to insert a 2 dimensional array into a DB table. Is there any way to insert these values into DB with a single INSERT statement rather than using multiple INSERT statements? These multiple statements create a tendency for DB connection pool issues and can create a latency in the application.

String[][] a = new String[10][2];
for(int i =0;i<10;i++)
{
        st.executeUpdate("Insert into sap_details  VALUES a[i][0],a[i][1]);
}

What happens here is there are effectively 10 INSERT statements being called for each row. I don't want it to; it should happen with only one INSERT statement.

Is there any way to do that?

Dmitri · Accepted Answer · 2012-02-14 22:50:46Z

5

Use JDBC Batch Updates? Using prepared statements should also help.

Example

    String[][] a = new String[10][2];
    PreparedStatement pst = con.prepareStatement("INSERT INTO sap_details VALUES (?,?)");
    for (int i = 0; i < 10; i++) {
        pst.setString(1, a[i][0]);
        pst.setString(2, a[i][1]);
        pst.addBatch();
    }
    int[] results = pst.executeBatch();

edited Feb 14, 2012 at 22:50

answered Feb 14, 2012 at 22:38

Dmitri

9,1675 gold badges39 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nishanreads Over a year ago

Thanks a lot ! I think that should help me get away with any issues that may arise out of Connection pool issues / Deadlock or any DB issues arising out if multiple INSERT statements.

tres2k · Accepted Answer · 2012-02-14 22:46:49Z

1

With MySQL, something like this should do the trick, perfectly fine , Oracle won't like it. This feature is supported by DB2, SQL Server (since version 10.0 - i.e. 2008), PostgreSQL (since version 8.2), MySQL, and H2.

        String[][] a = new String[10][2];    
        StringBuilder sb = new StringBuilder("Insert into sap_details (a,b) VALUES ");               
        for(int i =0;i<a.length;i++){
                sb.append("(\'");
                sb.append(a[i][0]);
                sb.append("\',\'");
                sb.append(a[i][1]);
                sb.append("\')");
                if(i < a.length -1 )
                    sb.append(",");
         }
         st.executeUpdate(sb.toString());

edited Feb 14, 2012 at 22:46

answered Feb 14, 2012 at 20:27

tres2k

1465 bronze badges

3 Comments

Dmitri Over a year ago

It really is best to avoid hand-rolled SQL strings, if at all possible.

tres2k Over a year ago

With User Input you can't control, I definitely would go with prepared statements. But I kind of like this style.

nishanreads Over a year ago

Thanks, but i am using Oracle DB.

Collectives™ on Stack Overflow

Inserting an Array of Characters into DB

2 Answers 2

Example

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Example

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related