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

2.4. Updating rows in a table
Last updated: 1 January 2013.

This tutorial explains how to update 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.

Suppose that you want to update a row in the following table named SALES 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 ... ... ... ...


The method updateRow shown below uses the JDBC API to update the third row of the table SALES by replacing Electric kettle with Coffee machine.

public void updateRow() 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 = "UPDATE SALES SET PRODUCT = ? WHERE ID = ?";
          statement = connection.prepareStatement
(st);

         
// Set the statement's parameters
         
statement.setString(1, "Coffee machine");
          statement.setInt
(2, 3);

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

}


Code explanation

The code above is very similar to the code in the previous tutorial. If you have read it, you can skip the rest of this page.

// 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 = "UPDATE SALES SET PRODUCT = ? WHERE ID = ?";
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, "Coffee machine");
statement.setInt
(2, 3);

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 first parameter (at index 1) is set to the string value Coffee machine and the second parameter is set to the integer value 3.



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

The above statement simply executes the SQL statement.


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

Copyright © 2013 - 2016. 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