Statement vs Prepared Statement vs Callable

· SQL — Query execution steps
· Statement
· Prepared Statement
∘ Basics
∘ Why prepared statement
∘ Rules for parameter in Prepared statement

Follow this link for all System design articles

SQL — Query execution steps

Query Processing includes translations on high level Queries into low level expressions that can be used at physical level of file system, query optimization and actual execution of query to get the actual result.

Step-1

Parser: During parse call, the database performs the following checks- Syntax check, Semantic check and Shared pool check, after converting the query into relational algebra.

• Parser performs the following checks as (refer detailed diagram)

1. Syntax check — concludes SQL syntactic validity. Example:

SELECT * FORM employee

Here error of wrong spelling of FROM is given by this check.

2. Semantic check — determines whether the statement is meaningful or not. Example: query contains a tablename which does not exist is checked by this check.

3. Shared Pool check — Every query possess a hash code during its execution. So, this check determines existence of written hash code in shared pool if code exists in shared pool then database will not take additional steps for optimization and execution.

Hard Parse and Soft Parse –
If there is a fresh query and its hash code does not exist in shared pool then that query has to pass through from the additional steps known as hard parsing otherwise if hash code exists then query does not passes through additional steps. It just passes directly to execution engine (refer detailed diagram). This is known as soft parsing.
Hard Parse includes following steps — Optimizer and Row source generation.

Step-2

Optimizer —

During optimization stage, database must perform a hard parse at least for one unique DML statement and perform optimization during this parse. This database never optimizes DDL unless it includes a DML component such as subquery that require optimization.

• It is a process in which multiple query execution plan for satisfying a query are examined and most efficient query plan is satisfied for execution.
  Database catalog stores the execution plans and then optimizer passes the lowest cost plan for execution.

Row Source Generation —

The Row Source Generation is a software that receives a optimal execution plan from the optimizer and produces an iterative execution plan that is usable by the rest of the database. the iterative plan is the binary program that when executes by the sql engine produces the result set.

Step-3

Execution Engine: Finally runs the query and display the required result.

Statement

• static
• Suitable for DDL
• queries which don’t occur often
• Runtime

execution_time = no_of_run*(request_time + 
                            compile_time [parsing + execution plan] +  
                            execution+time + 
                            response_time)

Prepared Statement

Basics

• When a prepared statement is created, it requires a query as parameter. This query is sent to Database in advance and will be compiled by DB (parsing + execution plan)

PreparedStatement pst = connection.prepareStatement(query)

• Only execution will take place (NO compiling)

pst.executeQuery()

• Runtime

runtime = per_query*(request_time + execution_time + response_time)
compile_time*1 (only once)

Why prepared statement

• Precompiled query
• Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.
  → Databases store caches of execution plans for previously executed statements. This allows the database engine to reuse the plans for statements that have been executed previously. Because PreparedStatement uses parameters, each time it is executed it appears as the same SQL, the database can reuse the previous access plan, reducing processing. Statements "inline" the parameters into the SQL string and so do not appear as the same SQL to the DB, preventing cache usage.
• Binary communications protocol means less bandwidth and faster comms calls to DB server
  → Prepared statements are normally executed through a non-SQL binary protocol. This means that there is less data in the packets, so communications to the server is faster. As a rule of thumb network operations are an order of magnitude slower than disk operations which are an order of magnitude slower than in-memory CPU operations. Hence, any reduction in amount of data sent over the network will have a good effect on overall performance.
• They provide stronger separation between the query code and the parameter values (compared to concatenated SQL strings), boosting readability and helping code maintainers quickly understand inputs and outputs of the query.
• Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatement setXxx() methods to set the values and thus don’t inline the values in the SQL string by string-concatenating.

simple statement = "SELECT * FROM users WHERE name = '" + userName +   
                   "';"
username: ' OR '1'='1
resultant statement = SELECT * FROM users WHERE name = '' OR   
                     '1'='1';

→ using parameterized queries can definitely prevent SQL injection. This mainly means that your variables aren’t query strings that would accept arbitrary SQL inputs, however, some parameters of given types are definitely necessary. Parameterized queries require the developer to define all the code. Therefore, without parameterized queries, anyone could put any kind of SQL code into the field, and have the database erased. But if the parameters were to set to ‘@username’ then the person would only be able to put in a username without any kind of code

• In java, can call getMetadata() and getParameterMetadata() to reflect on the result set fields and the parameter fields, respectively
• In java, accepts SQL ARRAYs, as parameter type via setArray method
• In java, accepts CLOBs, BLOBs, OutputStreams and Readers as parameter “feeds” via setClob/setNClob, setBlob, setBinaryStream, setCharacterStream/setAsciiStream/setNCharacterStream methods, respectively
• In java, allows DB-specific values to be set for SQL DATALINK, SQL ROWID, SQL XML, and NULL via setURL, setRowId, setSQLXML ans setNull methods
• In java, inherits all methods from Statement. It inherits the addBatch method, and additionally allows a set of parameter values to be added to match the set of batched SQL commands via addBatch method.
• In java, a special type of PreparedStatement (the subclass CallableStatement) allows stored procedures to be executed — supporting high performance, encapsulation, procedural programming and SQL, DB administration/maintenance/tweaking of logic, and use of proprietary DB logic & features
• In java, intelligently accepts java objects as parameter types via setObject, setBoolean, setByte, setDate, setDouble, setDouble, setFloat, setInt, setLong, setShort, setTime, setTimestamp — it converts into JDBC type format that is comprehendible to DB (not just toString() format).
  → Eases setting of non-standard Java objects in a SQL string, e.g. Date, Time, Timestamp, BigDecimal, InputStream (Blob) and Reader (Clob). On most of those types you can't "just" do a toString() as you would do in a simple Statement. You could even refactor it all to using PreparedStatement#setObject() inside a loop as demonstrated in the utility method below:

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

Which can be used as below:

preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");
setValues(preparedStatement, person.getName(), person.getEmail(), new Timestamp(person.getBirthdate().getTime()), person.getPhoto());
preparedStatement.executeUpdate();

• Prepared statement in batch

public void executeBatch(List<Entity> entities) throws SQLException { 
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL);
    ) {
        for (Entity entity : entities) {
            statement.setObject(1, entity.getSomeProperty());
            // ...

            statement.addBatch();
        }

        statement.executeBatch();
    }
}

Rules for parameter in Prepared statement

• Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.
• we can have named parameters and unnamed parameters