Jdbc Insert Statement Example in Java

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:

1. Establish a database connection to get a Connection object.
2. Create a Statement object from the Connection object.
3. Execute the INSERT statement.
4. 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.

            
                              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);     }            
                          Code language:              Java              (              java              )                      

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_KEYS to the preparedStatement() 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.

            
                                                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;     }            
                          Code language:              Java              (              java              )                      

The Actor class is as follows:

            
                              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;     } }            
                          Code language:              Java              (              java              )                      

Inserting multiple rows into a table

The steps of inserting multiple rows into a table is as follows:

1. Create a database connection.
2. Create a PreparedStatement object.
3. Call the addBatch() method of the PreparedStatement object.
4. Call the executeBatch() method to submit a batch of the INSERT statements to the PostgreSQL database server for execution.
5. 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.

            
                              /**      * 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());         }     }            
                          Code language:              Java              (              java              )                      

In this tutorial, you have learned how to insert one or multiple rows into the PostgreSQL database using the JDBC API.

Was this tutorial helpful ?

mazuryeand2001.blogspot.com

Source: https://www.postgresqltutorial.com/postgresql-jdbc/insert/

Share this post