What is a JDBC Statement?
The JDBC Statement API is used to execute SQL queries against the database. You can get the Statement object using the Connection.getStatement () method. Calling the execute (), executeQuery (), executeUpdate (), and other methods, you can execute various static SQL queries.
In the case of dynamically created SQL queries inside the java program, when the user input can be unverified, you can use SQL injection.
By default, only one ResultSet for each Statement can be opened at the same time. Thus, if it is necessary to work with several ResultSet objects at the same time, we must use different Statement objects. All execute () methods in the Statement interface will close the current open ResultSet object when executed.
What are the differences between executing, executeQuery, executeUpdate?
There are several ways to execute SQL queries, depending on the type of this query. For this, the Statement interface has three different methods: executeQuery () , executeUpdate () , and execute () . Let’s consider them separately.
The most basic executeQuery () method is required for queries that result in a single set of values, such as SELECT queries. Returns a ResultSet, which cannot be null even if the query result did not contain values.
The method executes () is used when SQL statements return more than one set of data, more than one update counter, or both. The method returns true if the result is ResultSet, as in the SELECT query. Returns false if the ResultSet is not present, for example, for requests of the type Insert, Update. Using the getResultSet () methods, we can get ResultSet, and getUpdateCount () is the number of updated records.
The executeUpdate () method is used to execute INSERT, UPDATE, or DELETE statements, as well as DDL (Data Definition Language) operators, such as CREATE TABLE and DROP TABLE. The result of the INSERT, UPDATE or DELETE statement is the modification of one or more columns in the zero or more rows of the table. MethodexecuteUpdate () returns an integer indicating how many rows have been modified. For expressions of type CREATE TABLE and DROP TABLE that do not operate on strings, the value returned by the executeUpdate () method is always zero.
All methods of executing SQL queries close the previous result set for this Statement object. This means that before processing the next request on the same Statement object, you must finish processing the results of the previous (ResultSet).
What is JDBC PreparedStatement?
The PreparedStatement object is used to perform precompiled SQL queries with or without input (IN) parameters. We can use setters to set values in a query. Because PreparedStatement is precompiled, then it can be effectively used many times. PreparedStatement is considered a better choice than Statement, because it automatically processes special characters, and also prevents the so-called SQL injection attack (when you can substitute your code into the query).
How to set NULL values in JDBC PreparedStatement?
Using the setNull () method to set the null variable as a parameter. This method takes an index and an SQL type as arguments: s.setNull (10, java.sql.Types.INTEGER);
How is the getGeneratedKeys () method used in the Statement?
If the table uses crucial automatic generation, then use the Statement getGeneratedKeys () method to get them, which will return the generated key.
What are the advantages of using PreparedStatement on a Statement?
PreparedStatement allows you to prevent SQL injection attacks. It automatically escapes special characters.
PreparedStatement allows you to use dynamic queries with the implementation of parameters.
PreparedStatement is faster than Statement. This is especially noticeable with frequent use of PreparedStatement or when using to query a group of queries.
PreparedStatement allows you to write object-oriented code using setters/getters. At that time, when using Statement, you must use string concatenation to create a query. For large questions, the concatenation looks, at least, large, and also carries a high risk of error in the query.
What are the limitations of PreparedStatement and how to overcome them?
In PreparedStatement you cannot use direct queries with IN (input) parameters. There are some workarounds:
Run Single Queries – low performance and generally not recommended to do.
Use Stored Procedure (stored procedures) – are specific to a specific database and therefore are bad for applications with the ability to connect to different databases.
Creating PreparedStatement Query dynamically is a good solution, but with the loss of PreparedStatement caching.
Using NULL in PreparedStatement Query is a good solution if you know the maximum number of IN variables. You can expand to use an unlimited number of parameters by splitting it into parts.