1

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();
3
  • 3
    First I recommand you start using Prepared Statements, I'd also recommand having a look at JDBC Database Access and read the section on batch updates. To answer your question change 1 to something like someOtherValue where someOtherValue is number of rows you want to insert Commented Mar 12, 2017 at 20:54
  • 5
    You can do that with just a single insert, no loop required: INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) select i, random() * 10000 + 1, random() * 80 + 1, random() * 10000 + 1, random() * 100 + 1 from generate_series(1,1000000) i Commented Mar 12, 2017 at 20:57
  • On the top of what @MadProgrammer said, remove semicolon at the end of the for statement, and put some brackets ({ }) around the code to be repeated. Commented Mar 12, 2017 at 20:57

1 Answer 1

1

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).

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

5 Comments

Sorry, but the sql statement will just be too long and the DB won't be able to process it (you'll get an SQL exception) even if it reaches the DB. So, it should be either what a_horse_with_no_name suggested of batch updates.
I've used SQL statements INSERTING several hundred thousand rows, using both psql and pgAdmin. The DB doesn't have a problem. I guess whether this is feasible or not will depend on the JDBC driver limitations. If it cannot handle such a large statement, you can always double loop: 1,000 x 1,000 would probably be good for any driver.
The problem is not the number of rows inserted, but the length of the sql statement sent to the DB (the sql insert statement is just too long). I'm pretty sure that the above solution isn't going to work. And, even if the DB had no problem with it, how much time would it take to send it to the DB server for processing.
In your given example the StringBuilder expects a method call, but i can't find the problem. Any suggestions?
I think I made a mistake in its declaration... it should be StringBuilder.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.