CODEWITHSUNDEEP
javapostgresqljdbcrandominsert
Marius Vengalis
Marius Vengalis

Reputation: 23

Inserting multiple rows into database table using JAVA

I need to insert multiple rows (about 1 mln.) containing random numbers into a Postgresql database. This code generates one row with random numbers into a database. How can i make the statement loop itself for any amount of times?

        Random rand = new Random();
        for (int j=0;j < 1;j++);

            stmt = c.createStatement();
            String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
                    + "VALUES ('" + rand.nextInt() + "', '" + rand.nextInt() + "', '" + rand.nextInt() + "', '" + rand.nextInt() + "'," +
                    " '" + rand.nextInt() + "')";
            stmt.executeUpdate(sql);

        stmt.close();
        c.commit();
        c.close();

Upvotes: 1

Views: 4358

Answers (1)

joanolo
joanolo

Reputation: 6328

You basically have two options to do that

Have the database do all the work

As suggested by @a_horse_with_no_name: use just one INSERT, and let the database compute all the random values:

INSERT INTO COMPANY 
  (ID
  ,NAME
  ,AGE
  ,ADDRESS
  ,SALARY) 
SELECT
   i
   ,random() * 10000 + 1
   ,random() * 80 + 1
   ,random() * 10000 + 1
   ,random() * 1000000 + 1 
FROM
  generate_series(1,1000000) i

This would be the usual way of filling a table with random values (to simulate data, for instance) when working with PostgreSQL. Note one thing: the ID column, assuming it is a PRIMARY KEY (i.e.: UNIQUE and NOT NULL) should never be assigned a random value, that could be repeated.

Have all the values computed by your program, but generate just one statement:

If, for some reason, the randomness of PostgreSQL is not good enough for your application, or you want to control through your program how the (pseudo)random values are generated, you could take advantage of the fact that you can have several rows stated in VALUES.

That is, the following statement is valid:

INSERT INTO some_table(a_column) VALUES (101), (102), (103), (104) ;

and would insert four rows into some_table.

You would change your program to generate values this way:

Random rand = new Random();
StringBuilder sql = 
       new StringBuilder("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES ;");

for (int j=0; j < 1000000; j++)
{
  if (j) sql.append (",")
  sql.append ("(" + j.toString() +                      /* ID should NOT be random() */
              ",'" + rand.nextInt().toString() + "'" +  /* NAME    */
              ",'" + rand.nextInt().toString() + "'" +  /* AGE     */
              ",'" + rand.nextInt().toString() + "'" +  /* ADDRESS */
              ",'" + rand.nextInt().toString() + "'" +  /* SALARY  */
              ")") ;
}

stmt = c.createStatement();
stmt.executeUpdate(sql.toString());
stmt.close();
c.commit();
c.close();

NOTE 1: the SQL statement generated this way is not "dangerous" because you are completely controlling the data used to generate it. If you would use user input, or some information whose origin or format cannot be trusted, use PREPARED STATEMENTS, to avoid risks of SQL injection.

NOTE 2: Use a StringBuilder (not a String) to generate such a large String.

NOTE 3: As the SQL string can be too large to be handled by either the JDBC or the database itself (as pointed out by @dsp_user), it might be necessary to limit the number of iterations within the loop; and have a second loop over it (obviously, the use of the j variable should change in this scenario).

Upvotes: 1

Related Questions