Header javaperspective.com
JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.5. Deleting rows from a table

2.5. Deleting rows from a table
Last updated: 1 January 2013.

This tutorial explains how to delete rows from 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 delete a row from 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 deleteRow shown below uses the JDBC API to delete the second row of the table SALES.

public void deleteRow() 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 = "DELETE FROM SALES WHERE ID = ?";
          statement = connection.prepareStatement
(st);

         
// Set the statement's parameter
         
statement.setInt(1, 2);

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

}


Code explanation

The code sample above is very similar to the code samples of the previous tutorials. If you have read them, 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 = "DELETE FROM SALES 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 parameter
statement.setInt(1, 2);

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 one and only parameter (at index 1) is set to the integer value 2.



// 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.5. Deleting rows from a table
Next tutorial :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.6. Connection pooling

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