What are the commonly used methods of PreparedStatement interface in Java?

In Java, the PreparedStatement interface is a crucial interface primarily used for executing SQL statements with parameters to prevent SQL injection and improve the performance of database operations. The following are some commonly used PreparedStatement interface methods:

1. setString(int parameterIndex, String x) This method sets a string parameter into the precompiled SQL statement. parameterIndex specifies the parameter's index, and x is the string value to set. For example, to query user information for a specific username, you can write:

   java
   PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM users WHERE username = ?");
   pstmt.setString(1, "alice");

2. setInt(int parameterIndex, int x) This method sets an integer parameter into the precompiled SQL statement. For example, to query a user by their ID:

   java
   PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM users WHERE user_id = ?");
   pstmt.setInt(1, 1001);

3. executeQuery() This method executes SQL statements that return a result set (such as SELECT). It returns a ResultSet object, which can be used to read the query results.

   java
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
       String username = rs.getString("username");
       // Process each row
   }

4. executeUpdate() This method executes SQL statements that do not return a result set (such as INSERT, UPDATE, DELETE). It returns an integer representing the number of affected rows.

   java
   int affectedRows = pstmt.executeUpdate();
   System.out.println("Number of affected rows: " + affectedRows);

5. setDouble(int parameterIndex, double x) This method sets a double-precision floating-point number into the precompiled SQL statement. For example, to update a product's price:

   java
   PreparedStatement pstmt = connection.prepareStatement("UPDATE products SET price = ? WHERE product_id = ?");
   pstmt.setDouble(1, 19.99);
   pstmt.setInt(2, 202);

6. clearParameters() This method clears all parameters previously set in the current PreparedStatement object. It is particularly useful when reusing the same PreparedStatement with different parameters across multiple operations.

   java
   pstmt.clearParameters();

7. setDate(int parameterIndex, Date x) This method sets a java.sql.Date parameter for handling date data.

   java
   java.sql.Date sqlDate = java.sql.Date.valueOf("2021-12-31");
   pstmt.setDate(1, sqlDate);

8. close() This method closes the PreparedStatement object and releases associated resources. It is essential to close the PreparedStatement after completing database operations to avoid resource leaks.

   java
   pstmt.close();

These methods provide developers with powerful tools for database operations, effectively preventing SQL injection attacks, and compared to the standard Statement, PreparedStatement typically executes faster.