Summary: in this tutorial, you will learn how to insert data into a table in the PostgreSQL database using JDBC API.
We will use the actor table in the sample database for the demonstration.
Inserting one row into a table
To insert a row into a table, you follow these steps:
- Establish a database connection to get a Connection object.
- Create a Statement object from the Connection object.
- Execute the INSERT statement.
- Close the database connection.
To connect to a PostgreSQL database server, you have to provide a connection string that specifies the location of the database server as well as the database name. In addition, you need to provide the username and password to authenticate with the database server.
The following connect() method creates a database connection and returns a Connection object.
1 2 3 4 5 6 7 8 9 10 11 12 | private final String url = "jdbc:postgresql://localhost/dvdrental"; private final String user = "postgres"; private final String password = "postgres"; /** * Connect to the PostgreSQL database * * @return a Connection object */ public Connection connect() throws SQLException { return DriverManager.getConnection(url, user, password); } |
Check it out how to connect a PostgreSQL database server for the detailed information.
When we insert a row into a table that has auto generated id, we often want to get the id value back for further processing.
To get the auto generated id, you have to:
- Pass the
Statement.RETURN_GENERATED_KEYSto thepreparedStatement()object when you create the Statement object. - Call the
getGeneratedKeys()method of the Statement object to get the id value.
The following insertActor() method inserts a row into the actor table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | public long insertActor(Actor actor) { String SQL = "INSERT INTO actor(first_name,last_name) " + "VALUES(?,?)"; long id = 0; try (Connection conn = connect(); PreparedStatement pstmt = conn.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS)) { pstmt.setString(1, actor.getFirstName()); pstmt.setString(2, actor.getLastName()); int affectedRows = pstmt.executeUpdate(); // check the affected rows if (affectedRows > 0) { // get the ID back try (ResultSet rs = pstmt.getGeneratedKeys()) { if (rs.next()) { id = rs.getLong(1); } } catch (SQLException ex) { System.out.println(ex.getMessage()); } } } catch (SQLException ex) { System.out.println(ex.getMessage()); } return id; } |
The Actor class is as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | package com.postgresqltutorial; /** * * @author postgresqltutorial.com */ public class Actor { private String firstName; private String lastName; public Actor(String firstName, String lastName) { this.firstName = firstName; this.lastName = lastName; } public Actor() { } /** * @return the firstName */ public String getFirstName() { return firstName; } /** * @param firstName the firstName to set */ public void setFirstName(String firstName) { this.firstName = firstName; } /** * @return the lastName */ public String getLastName() { return lastName; } /** * @param lastName the lastName to set */ public void setLastName(String lastName) { this.lastName = lastName; } } |
Inserting multiple rows into a table
The steps of inserting multiple rows into a table is as follows:
- Create a database connection.
- Create a
PreparedStatementobject. - Call the
addBatch()method of thePreparedStatementobject. - Call the
executeBatch()method to submit a batch of theINSERTstatements to the PostgreSQL database server for execution. - Close the database connection.
Because the length of an SQL statement that you send to PostgreSQL is limited, therefore, you should call the executeBatch() for a certain number of rows or less e.g., for every 100 rows.
The following insertActors() method inserts a list of actors into the actor table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | /** * insert multiple actors */ public void insertActors(List<Actor> list) { String SQL = "INSERT INTO actor(first_name,last_name) " + "VALUES(?,?)"; try ( Connection conn = connect(); PreparedStatement statement = conn.prepareStatement(SQL);) { int count = 0; for (Actor actor : list) { statement.setString(1, actor.getFirstName()); statement.setString(2, actor.getLastName()); statement.addBatch(); count++; // execute every 100 rows or less if (count % 100 == 0 || count == list.size()) { statement.executeBatch(); } } } catch (SQLException ex) { System.out.println(ex.getMessage()); } } |
In this tutorial, we have shown you how to insert one or multiple rows into the PostgreSQL database using JDBC API.