Processing SQL Statements with JDBC
We have to follow following steps in any SQL statement with JDBC:
1. Establishing a connection.
2. Create a statement.
3. Execute the query.
4. Process the ResultSet object.
5. Close the connection.
1. Establishing Connections
Establish a connection with the data source you want to use.
A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver.
This connection is represented by a Connection object.
2. Creating Statements
A Statement is an interface that represents a SQL statement.
You execute Statement objects and they generate ResultSet objects which is a table of data representing a database result set.
You need a Connection object to create a Statement object.
There are three different kinds of statements:
Statement: Used to implement simple SQL statements with no parameters.
PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters.
CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters.
3.Executing Queries
To execute a query, call an execute method from Statement such as the following:
execute: Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.
executeQuery: Returns one ResultSet object.
executeUpdate: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.
4. Processing ResultSet Objects
You access the data in a ResultSet object through a cursor.
This cursor is a pointer that points to one row of data in the ResultSet object.
Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.
5. Closing Connections
When you are finished using a Statement, call the method Statement.close to immediately release the resources it is using.
When you call this method, its ResultSet objects are closed.
Example:
{
Statement stmt = null;
try
{
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next())
{
String name= rs.getString("name");
int ID = rs.getInt("ID");
float price = rs.getFloat("PRICE");
}
}
catch (SQLException e )
{
e.printStackTrace();
}
finally
{
if (stmt != null)
{
stmt.close();
}
}
}
Prepared Statement:
The Prepared Statement is a slightly more powerful version of a Statement, and should always be at least as quick and easy to handle as a Statement.
The Prepared Statement may be parametrized Most relational databases handles a JDBC / SQL query in four steps:
1.Parse the incoming SQL query
2. Compile the SQL query
3. Plan/optimize the data acquisition path
4. Execute the optimized query / acquire and return data
A Statement will always proceed through the four steps above for each SQL query sent to the database.
A Prepared Statement pre-executes steps (1) - (3) in the execution process above. Thus, when creating a Prepared Statement some pre-optimization is performed immediately. The effect is to lessen the load on the database engine at execution time.
Advantages of a Prepared Statement:
1. 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.
2. Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters.
Why do we use connection pool:
Reason :Creating a new database connection is very costly.
Explanation:
Think about the amount of memory being allocated per DB Connection. What things must be allocated? According to MySQL 5.0 Certification Study Guide, page 357:
The server maintains several buffers for each client connection. One is used as a communications buffer for exchanging information with the client. Other buffers are maintained per client for reading tables and performing join and sort operations.
What settings govern the per-connection buffers?
join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
tmp_table_size / max_heap_table_size
net_buffer_length / max_allowed_packet
thread_stack
It takes time to allocate and deallocate these buffers when a connection comes into being. Don't forget to multiple the sum of those values by max_connections. As a side note, please refrain from usingmysql_pconnect as PHP and MySQL's persistent connections do not get along well. Here are two informative links on this topic:
Open bug on PHP Not Cleanly Closing MySQL Connections on Apache Death.
Requesting a Persistent ssh Connection.
In heavy-read, heavy-write environment, such as OLTP, this would be expensive in terms of RAM usage and possible inhibition due to swapping in the OS. On a low-write, low_read website, I would not worry as much.
for above terms use link : http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_join_buffer_size
The process of handshake spanning across multiple machines (if your db server is hosted over the network) which takes place on creating (establishing) a new connection makes it expensive. Thats why its recommended to use a connection pool, or in your case the session. This could include host lookup, initial connection, and subsequent control commands.
