How insert java data generation function into PostgreSQL table?

Question

I have PostgreSQL db and I use JDBC to connect and generate table, but I want to generate table with random data. so I create java methods which generate random names and surnames which I want to set into my table into special columns (name, surname). My db :

DROP TABLE IF EXISTS groups;
CREATE TABLE groups(
name VARCHAR(50),
surname VARCHAR(50),
);

and I try to set name and surname using this code:

    String URL = "jdbc:postgresql://localhost:5432/school";
    String user = "principal";
    String password = "123";

    String sqlScript = new String(Files.readAllBytes(Path.of("src/main/resources/database/dbScript.sql")));

    Connection connection = DriverManager.getConnection(URL, user, password);
    System.out.println("Success.........");
    Statement script = connection.createStatement();
    script.execute(sqlScript);
    //class with random gen.
    StudentsGenerator generator = new StudentsGenerator();
    
    String query1 = "INSERT INTO groups " +"VALUES (generator.student().stream().map(names -> names.getName()))";
    script.executeUpdate(query1);

Nowhere Man · Accepted Answer · 2020-11-15 15:46:19Z

If the syntax of INSERT statement with multiple values is allowed:

INSERT INTO tbl_name (col1, col2) VALUES 
('val11', 'val12'),
('val21', 'val22'),
...
('valN1', 'valN2');

such statement can be generated:

String manyInserts = "INSERT INTO groups VALUES " 
    + students.stream()
              .map(student -> String.format("('%s', '%s')", student.getName(), student.getSurname()))
              .collect(Collectors.joining(","));
script.executeUpdate(manyInserts);

However, this approach is not protected from SQL injections and it is more preferable to use batch inserts for PreparedStatement:

PreparedStatement ps = connection.prepareStatement("INSERT INTO groups (name, surname) VALUES (?, ?)");
students.forEach(student -> {
    ps.setString(1, student.getName());
    ps.setString(2, student.getSurname());
    ps.addBatch();
});
ps.executeBatch();

Collectives™ on Stack Overflow

How insert java data generation function into PostgreSQL table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related