- 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 - Prepared Statement Object Examples
Following is the example, which makes use of the PreparedStatement along with opening and closing statements −
This sample code has been written based on the environment and database setup done in the previous chapters.
Using PreparedStatement to Update int Value Example
In this example, we've five static strings containing a dababase connection url, username, password, SELECT Query and an UDPATE query. UPDATE Query has placeholders for age and id which will be filled by PreparedStatement. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a PreparedStatement object using connection.prepareStatement() method. Now using setInt() method, we've set the placeholder values of age and id accordingly.
Using statement.executeUpdate(), we've run the update query and modified row count is stored in rows variable. 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 JDBCPreparedStatementExample.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCPreparedStatementExample { 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=? WHERE id=?"; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); PreparedStatement stmt = conn.prepareStatement(UPDATE_QUERY); ) { // Bind values into the parameters. stmt.setInt(1, 35); // This would set age stmt.setInt(2, 102); // This would set ID // Let us update age of the record with ID = 102; int rows = stmt.executeUpdate(); 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 JDBCPreparedStatementExample.java C:\>
When you run JDBCPreparedStatementExample, it produces the following result −
C:\>java JDBCPreparedStatementExample 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: 35, First: Zaid, Last: Khan ID: 103, Age: 30, First: Sumit, Last: Mittal C:\>
Using PreparedStatement to Update String Value Example
In this example, we've five static strings containing a dababase connection url, username, password, SELECT Query and an UDPATE query. UPDATE Query has placeholders for age and id which will be filled by PreparedStatement. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a PreparedStatement object using connection.prepareStatement() method. Now using setString() method, we've set the placeholder values of dept and using setInt() method, we've set the id accordingly.
Using statement.executeUpdate(), we've run the update query and modified row count is stored in rows variable. 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 JDBCPreparedStatementExample.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCPreparedStatementExample { 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, Dept FROM Students"; static final String UPDATE_QUERY = "UPDATE Students set Dept=? WHERE studentid=?"; public static void main(String[] args) { try{ // Open a connection Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); PreparedStatement pstmt = conn.prepareStatement(UPDATE_QUERY); // Bind values into the parameters. pstmt.setString(1, "Mathematics"); pstmt.setInt(2, 1000); // Updating Dept of the record with StudentID = 1000 int rows = pstmt.executeUpdate(); System.out.println("Rows impacted : " + rows ); // Let us select all the records and display them. ResultSet rs = pstmt.executeQuery(QUERY); // Extract data from result set while (rs.next()) { // Retrieve by column name System.out.print("ID: " + rs.getInt("StudentID")); System.out.print(", LastName: " + rs.getString("LastName")); System.out.print(", FirstName: " + rs.getString("FirstName")); System.out.println(", Dept: " + rs.getString("Dept")); } rs.close(); pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:\>javac JDBCPreparedStatementExample.java C:\>
When you run JDBCPreparedStatementExample, it produces the following result −
C:\>java JDBCPreparedStatementExample Rows impacted : 1 ID: 1000, LastName: Agarwal, FirstName: Bonny, Dept: Mathematics ID: 1001, LastName: Pandey, FirstName: Amit, Dept: Physics ID: 1002, LastName: Kumar, FirstName: Shefali, Dept: English ID: 1004, LastName: Ali, FirstName: Mohammed, Dept: Mathematics ID: 1005, LastName: Kumar, FirstName: Kishore, Dept: Biology C:\>
To Continue Learning Please Login
Login with Google