How Does Java's Preparedstatement Work

How does Java's PreparedStatement work?

If you have variables use the '?'

int temp = 75;
PreparedStatement pstmt = con.prepareStatement(
"UPDATE test SET num = ?, due = now() ");
pstmt.setInt(1, temp);
pstmt.executeUpdate():

Produces an sql statment that looks like:

UPDATE test SET num = 75, due = now();

Does the prepared-statement work this way?

There is nothing about Prepared statement. Constraint can be broken by parameters you set to it. And you can run your PLSQL statement as anonimous block in PreparedStatement as well.

Just surround it with BEGIN ... END. only one thing is different - for JDBC parameters are ? mark not :parameter as for PLSQL and there is no way to use named parameter.

That means if you need to use parameter more than once for JDBC you have to have that many ? marks and set all of them.

So, focus on parameters you pass to and their sequence.

How does PreparedStatement.executeQuery work?

From the documentation:

Statement

public ResultSet executeQuery(String sql) throws SQLException

Executes the given SQL statement, which returns a single ResultSet
object.

ResultSet

A table of data representing a database result set, which is usually
generated by executing a statement that queries the database.

public boolean next() throws SQLException

Moves the cursor down one row from its current position. A ResultSet
cursor is initially positioned before the first row; the first call to
the method next makes the first row the current row; the second call
makes the second row the current row, and so on.

If an input stream is open for the current row, a call to the
method next will implicitly close it. A ResultSet object's warning
chain is cleared when a new row is read.


As a representation this means the statement is executed once and when iterated is iterated over the result of that execution.


But how the result from database is handled depends really on the implementation. To make a contrast I will refer two databases MSSQL, MYSQL.

MSSQL

The documentation of MSSQL driver that comments exactly how the results are handled you can find here:

There are two types of result sets: client-side and server-side.

Client-side result sets are used when the results can fit in the
client process memory. These results provide the fastest performance
and are read by the Microsoft JDBC Driver for SQL Server in their
entirety from the database. These result sets do not impose additional
load on the database by incurring the overhead of creating server-side
cursors. However, these types of result sets are not updatable.

Server-side result sets can be used when the results do not fit in the
client process memory or when the result set is to be updatable. With
this type of result set, the JDBC driver creates a server-side cursor
and fetches rows of the result set transparently as the user scrolls
through it.

MySQL

MySql implementation of JDBC interface that you can read here:

By default, ResultSets are completely retrieved and stored in memory.
In most cases this is the most efficient way to operate, and due to
the design of the MySQL network protocol is easier to implement. If
you are working with ResultSets that have a large number of rows or
large values, and cannot allocate heap space in your JVM for the
memory required, you can tell the driver to stream the results back
one row at a time.

PreparedStatement is faster in Java, How db do it?

The query is cached in the database server, and compile only once?

More precisely, the query plan is cached on the server. When you run a query, your RDBMS prepares a plan first, then executes it. Preparing a plan requires parsing the query, then analyzing and optimizing it, considering the indexes available and the statistics collected on the participating tables.

If yes, how the database server knows that this query was execute before?

By comparing the string of the query to other queries available in the cache. Since you use parameterized queries, the other query would be textually identical. Caching is the second major reason* to use query parameters: if you were to prepare statements like this

// WRONG! Don't do it like this!
String sql = "SELECT * FROM users u WHERE u.id = "+userId;
PreparedStatement pstmt = connenction.prepareStatement(sql);

all the performance improvements would be gone, because providing a different ID would make it a different query that needs a new plan.

* The top reason for parameterizing queries is, of course, avoiding injection attacks.



Related Topics



Leave a reply



Submit