JDBC and PostgreSQL - how to insert int in same row based on another int

Question

I am working on my first personal project to try and learn some fundamentals of postgresql and Java.

I am trying to generate a randomly generated integers in a database with 45000 entries with int representing date and time using a for loop in the insert. I've managed to be able to do that part on my own and searching SX, but I ran into a problem.

If you can check out the code below, I would like to randomly generate an integer based on a previous field in the same row. For example, if my TIMEHST (which represents the hour of the time that a process starts) is randomly generated to be 8 on that row, I want my TIMEHED (which represents the hour of the time that a process ends) to be a random integer between (TIMEHST and TIMEHST+5). Please excuse me if the code below looks like spaghetti, I've only been coding for 5 months and Java for 1 month.

package generateSamepleDataSet;

import java.sql.*;

/**
 * Created by jkyju_000 on 2/16/2016.
 */
public class GenerateProcessSqlDatabase {
    public static void main(String args[]) {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection("jdbc:postgresql://localhost:5432/postgres",
                            "postgres", "adfadf12");
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();

            for (int i = 0; i < 45000; i++) {
                String sql = "INSERT INTO ARRIVALS (DATEY,DATEM,DATED,TIMEHST,TIMEMST,TIMEHED,TIMEMED) "
                        + "VALUES ( " + (2012 + (int)(Math.random() * ((2015 - 2012) + 1))) + ", " + (1 + (int)(Math.random() * ((12 - 1) + 1))) + ", " + (1 + (int)(Math.random() * ((30 - 1) + 1))) + ", " + (7 + (int)(Math.random() * ((19 - 1) + 1))) + ", " + (0 + (int)(Math.random() * ((59 - 0) + 1))) + ", "I have no idea what to put in TIMEHED", "Also have no idea what to put in TIMEMED" );";
                stmt.executeUpdate(sql);
            }

            stmt.close();
            c.commit();
            c.close();
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Records created successfully");
    }
}

Could you post the table description of ARRIVALS, including the data types of each column? — Mick Mnemonic
– Mick Mnemonic, Commented Feb 17, 2016 at 21:44
Arrivals has 7 columns, all int. DATEY - year of the date DATEM - month of the date DATED - day of the date TIMEHST - hour of the time that a process starts TIMEMST - minute of the time that a process starts TIMEHED - hour of the time the process ends TIMEMED - minute of the time the process ends So basically, I am generating a random data set with 45000 rows in the for-loop using Math.random to provide ranges of values to be randomized within. For example: TIMEHST - can be any value between 7 and 19 hrs TIMEMST - can be any value between 0 and 59 mins — JkyJustin
– JkyJustin, Commented Feb 18, 2016 at 1:11
So if I want my processes to take a length between 5 to 10 minutes, I would like to fill my TIMEHED on the same row as the value of TIMEHST. And I want to fill TIMEMED to be a random number between TIMEMST + [5 to 10]. — JkyJustin
– JkyJustin, Commented Feb 18, 2016 at 1:16

JB Nizet · Accepted Answer · 2016-02-17 10:36:28Z

1

First of all, you need to learn about prepared statements to pass parameters, instead of concatenating values. This will make your code faster, and, even more impoortant, safer and easier to read.

You also need to decompose your whole problem into parts, and code these parts inside methods:

int processStart = generateRandomProcessStart();
int processEnd = processStart + generateRandomDurationBetween0And5();
preparedStatement.setInt(4, processStart);
preparedStatement.setInt(6, processEnd);

answered Feb 17, 2016 at 10:36

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

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

2 Comments

Basil Bourque Over a year ago

As suggested, make a habit of using PreparedStatement rather than Statement to eliminate SQL Injection security risks. And it makes your code more readable as you can see in the correct answer.

JkyJustin Over a year ago

Thanks for the advice, I'll check out how to write PreparedStatements and try to re-write the code. This is all new to me so thanks for the explanations.

Collectives™ on Stack Overflow

JDBC and PostgreSQL - how to insert int in same row based on another int

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related