![JDBC Tutorial](/jdbc/images/jdbc-mini-logo.jpg)
- JDBC Tutorial
- JDBC - Home
- JDBC - Introduction
- JDBC - SQL Syntax
- JDBC - Environment
- JDBC - Sample Code
- JDBC - Driver Types
- JDBC - Connections
- JDBC - Statements
- JDBC - Result Sets
- JDBC - Data Types
- JDBC - Transactions
- JDBC - Exceptions
- JDBC - Batch Processing
- JDBC - Stored Procedure
- JDBC - Streaming Data
- JDBC Examples
- JDBC - Create Database
- JDBC - Select Database
- JDBC - Drop Database
- JDBC - Create Tables
- JDBC - Drop Tables
- JDBC - Insert Records
- JDBC - Select Records
- JDBC - Update Records
- JDBC - Delete Records
- JDBC - WHERE Clause
- JDBC - Like Clause
- JDBC - Sorting Data
- JDBC Useful Resources
- JDBC - Questions and Answers
- JDBC - Quick Guide
- JDBC - Useful Resources
- JDBC - Discussion
- Useful - Java Tutorials
JDBC - Statement Object Examples
Following is the example, which makes use of the following three queries along with the opening and closing statement −
boolean execute(String SQL) : Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use the truly dynamic SQL.
int executeUpdate(String SQL): Returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements, for which you expect to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.
ResultSet executeQuery(String SQL)− Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.
This sample code has been written based on the environment and database setup done in the previous chapters.
Updating Records in the Table Example
In this example, we've five static strings containing a dababase connection url, username, password, UPDATE Query and a SELECT query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a Statement object using connection.createStatement() method. Now using statement.executeUpdate(), we've run the update query. Lastly using statement.executeQuery(), the SELECT Query is executed and result is stored in a resultset. Now resultset is iterated and each record is printed to check the updated content.
Copy and paste the following example in JDBCStatementExample.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCStatementExample { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT id, first, last, age FROM Employees"; static final String UPDATE_QUERY = "UPDATE Employees set age=30 WHERE id=103"; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ) { // Let us check if it returns a true Result Set or not. Boolean ret = stmt.execute(UPDATE_QUERY); System.out.println("Return value is : " + ret.toString() ); // Let us update age of the record with ID = 103; int rows = stmt.executeUpdate(UPDATE_QUERY); System.out.println("Rows impacted : " + rows ); // Let us select all the records and display them. ResultSet rs = stmt.executeQuery(QUERY); // Extract data from result set while (rs.next()) { // Retrieve by column name System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:\>javac JDBCStatementExample.java C:\>
When you run JDBCStatementExample, it produces the following result −
C:\>java JDBCStatementExample Return value is : false Rows impacted : 1 ID: 100, Age: 18, First: Zara, Last: Ali ID: 101, Age: 25, First: Mehnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 30, First: Sumit, Last: Mittal C:\>
Deleting Records from the Table Example
In this example, we've four static strings containing a dababase connection url, username, password,SELECT query and DELETE query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a Statement object using connection.createStatement() method. Now using statement.executeUpdate(), we've run the delete query. Lastly using statement.executeQuery(), the SELECT Query is executed and result is stored in a resultset. Now resultset is iterated and each record is printed to check the deleted content.
Copy and paste the following example in DeleteExample.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCStatementExample { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT StudentID, LastName, FirstName, Address, Dept FROM Students"; static final String DELETE_QUERY = "DELETE FROM Students WHERE StudentID='1005'"; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ) { //Check if it returns a true Result Set or not. boolean ret = stmt.execute(DELETE_QUERY); // Insert a new record; int rows = stmt.executeUpdate(DELETE_QUERY); System.out.println("Rows impacted : " + rows ); // Select all the records and display them. ResultSet rs = stmt.executeQuery(QUERY); // Extract data from result set while (rs.next()) { // Retrieve by column name System.out.print("StudentID: " + rs.getInt("Studentid")); System.out.print(", LastName: " + rs.getString("LastName")); System.out.print(", FirstName: " + rs.getString("FirstName")); System.out.println(", Address: " + rs.getString("Address")); System.out.println(", Dept: " + rs.getString("Dept")); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:\>javac JDBCStatementExample.java C:\>
When you run JDBCStatementExample, it produces the following result −
C:\>java JDBCStatementExample Rows impacted : 1 StudentID: 1000, LastName: Agarwal, FirstName: Bonny Address: 12 Southern Ave Dept: Chemistry StudentID: 1001, LastName: Pandey, FirstName: Amit Address: 23 Bahadur Shah Zafar Marg Dept: Physics StudentID: 1002, LastName: Kumar, FirstName: Shefali Address: 4 Middleton Row Dept: English StudentID: 1004, LastName: Ali, FirstName: Mohammed Address: 49/2B Ganesh Ch Ave C:\>