Header javaperspective.com
JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.3. Inserting rows in a table

2.3. Inserting rows in a table
Last updated: 1 January 2013.

This tutorial explains how to insert rows in a table with JDBC. All the classes and interfaces used in the code samples of this page belong to the package java.sql.

Let's assume that you want to insert a new row to the table SALES shown below containing information about the sales of a store.


ID PRODUCT REFERENCE PRICE DATE
1 Microwave oven AKR-342-G 90.5 2012-10-25 10:40:13
2 Freezer HGR-476-F 230.0 2012-10-25 17:55:35
3 Electric kettle HRC-159-P 25.45 2012-10-25 18:17:23
4 ... ... ... ...


Inserting a row into a table is very simple: the method insertRow shown below uses the JDBC API to insert a new row in the table SALES.

public void insertRow() throws SQLException {

   
Connection connection = null;
    PreparedStatement statement =
null;

   
try{
         
// Create a database connection
         
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "yourName", "yourPassword");

         
// Create a PreparedStatement
         
String st = "INSERT INTO SALES (PRODUCT, REFERENCE, PRICE, DATE) VALUES (?, ?, ?, ?)";
          statement = connection.prepareStatement
(st);

         
// Set the statement's parameters
         
statement.setString(1, "Washing machine");
          statement.setString
(2, "TFL-398-E");
          statement.setFloat
(3, 1300.0F);
          statement.setTimestamp
(4, new java.sql.Timestamp(System.currentTimeMillis()));

         
// Execute the SQL statement
         
statement.executeUpdate();
   
}
   
finally{
         
if(statement != null)
               
statement.close();
         
if(connection != null)
               
connection.close();
   
}

}


Code explanation

// Create a database connection
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "yourName", "yourPassword");

The method DriverManager.getConnection establishes a connection with the database and returns a Connection that you can use to issue SQL statements. The first parameter of the method DriverManager.getConnection is a database-specific string known as the database URL. In the example above, I am establishing a connection with a locally installed Oracle database. The second and third parameters are the user's name and password respectively.



// Create a PreparedStatement
String st = "INSERT INTO SALES (PRODUCT, REFERENCE, PRICE, DATE) VALUES (?, ?, ?, ?)";
statement = connection.prepareStatement
(st);

The method Connection.prepareStatement issues a parameterized SQL statement to the database and returns a PreparedStatement which allows you to execute the SQL statement multiple times with different parameters. If your SQL statement does not contain any parameters and if it is executed only once, you can use a Statement instead. However, PreparedStatement will meet your needs in all cases because it can be used once or multiple times, and with or without parameters.



// Set the statement's parameters
statement.setString(1, "Washing machine");
statement.setString
(2, "TFL-398-E");
statement.setFloat
(3, 1300.0F);
statement.setTimestamp
(4, new java.sql.Timestamp(new Date().getTime()));

In the SQL statement (the string st), the question marks (?) are placeholders for parameters, and each of which has an index within the SQL statement. The index of a parameter is its order of appearance in the SQL statement. You must provide a value for each parameter before executing the SQL statement. The setter method that is called depends on the parameter's SQL data type. There is a database-specific mapping between SQL and Java data types (see the documentation of your database). The interface PreparedStatement provides setter methods for every data type. In the above example, the two first parameters (at index 1 and 2) are set to the string values Washing machine and TFL-398-E respectively. The third parameter (at index 3) is set to the float value 1300.0 and the last parameter is set to the current date and time. If you don't want the time (hour, minutes, seconds and milliseconds) to be taken into account, you can use the method setDate instead. On the one hand, if you use setTimestamp, the inserted value will look something like this: 2012-10-25 19:17:23. On the other hand, if you use setDate, the hour, minutes, seconds and milliseconds are set to 0 and as a result, the inserted value will look like this: 2012-10-25 00:00:00.



// Execute the SQL statement
statement.executeUpdate();

The above statement simply executes the SQL statement. You might have noticed that the SQL statement ignores the column ID which is the primary key of the table SALES. For the most part, databases automatically generate a primary key for each newly inserted row, which is the reason why you can ignore the primary key when inserting a new row.


How to insert NULL values?

Use the method setNull(int parameterIndex, int sqlType) provided by the interface PreparedStatement. As an example, the next sample shows a modified version of the method insertRow that I have used earlier. In the new version, insertRow takes 4 arguments: the column values of the row to insert in the table SALES. If an argument is null, the corresponding parameter is set to NULL by the PreparedStatement object.

public void insertRow(String product, String reference, Float price, java.util.Date date) throws SQLException {

   
Connection connection = null;
    PreparedStatement statement =
null;

   
try{
         
// Create a database connection
         
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "yourName", "yourPassword");

         
// Create a PreparedStatement
         
String st = "INSERT INTO SALES (PRODUCT, REFERENCE, PRICE, DATE) VALUES (?, ?, ?, ?)";
          statement = connection.prepareStatement
(st);

         
// Set the statement's parameters
         
if(product != null)
               
statement.setString(1, product);
         
else
               
statement.setNull(1, java.sql.Types.VARCHAR);

         
if(reference != null)
               
statement.setString(2, reference);
         
else
               
statement.setNull(2, java.sql.Types.VARCHAR);

         
if(price != null)
               
statement.setFloat(3, price.floatValue());
         
else
               
statement.setNull(3, java.sql.Types.FLOAT);

         
if(date != null)
               
statement.setTimestamp(4, new java.sql.Timestamp(date.getTime()));
         
else
               
statement.setNull(4, java.sql.Types.TIMESTAMP);

         
// Execute the SQL statement
         
statement.executeUpdate();
   
}
   
finally{
         
if(statement != null)
               
statement.close();
         
if(connection != null)
               
connection.close();
   
}

}


You are here :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.3. Inserting rows in a table
Next tutorial :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.4. Updating rows in a table

Copyright © 2013. JavaPerspective.com. All rights reserved.  ( Terms | Contact | About ) 
Java is a trademark of Oracle Corporation
Image 1 Image 2 Image 3 Image 4 Image 5 Image 6 Image 7