| ☰ See All Chapters |
JDBC Query
JDBC query is executed using Statement objects. A Statement is an interface that represents a SQL query. 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.
Example
Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select * from emp"); |
Three types of Statement
Statement: Represents a simple SQL statement with no parameters.
PreparedStatement (Extends Statement.): Prepared- Statement object represents a precompiled SQL statement that contains parameters that need to be set before the statement is executed.
CallableStatement (Extends PreparedStatement.): CallableStatement object is used to call a stored procedure in the database.
Different methods provided for execution of SQL queries which are present in Statement and sub interfaces of Statement (PreparedStatement and CallableStatement
int executeUpdate(String SQL) | We can do CUD operations |
boolean execute(String SQL) | We can do CRUD operations |
int[] executeBatch() | We can do CUD operations |
ResultSet executeQuery(String SQL) | We can do only R operations. |
C-Create, R- Read, U-Update, D-Delete | |
int executeUpdate(String SQL):-(CUD) Returns a number value which represents the number of rows getting affected on execution of the SQL query.
boolean execute (String SQL):- (CRUD) Executes a SQL statement that may return multiple results. If the query is of type insert, update or delete then it is always false because it does not return any values to the java program. If the query is of type select then return type is always true because select will return values to java program.
int[] executeBatch():- (CUD)It is used for doing batch updates or it is used to submit multiple SQL queries at once to database. It returns an array of int, each index containing a number value which indicates number of rows getting affected on execution of every query. We can do only three operations with batch updates but cannot write select queries. Batch updates avoids multiple calls between application and database, because of this network traffic will be reduced and performance of the application will be increased.
ResultSet executeQuery(String SQL):- (R)It is used to execute select SQL statements. When you use executeQuery() for executing select statements, it returns ResultSet object which is capable of storing multiple rows and columns returned by select SQL statement. We cannot write insert, update, delete queries using executeQuery() because these queries cannot generate ResultSet, due to which exception gets created.
| Statement | PreparedStatement | CallableStatement |
Executing SQL statements(CRUD) | Y | Y | Y |
Executing multiple SQL statements with single object | Y | N | N |
Compiling and executing the SQL query every time | Y | N | N |
Compiling once and executing every time (pre compiled) | N | Y | Y |
Executing stored procedures | N | N | Y |
Providing placeholders mechanism | N | Y | Y |
When to use | Executing a SQL statement that will likely only occur once | SQL statement that executes a number of times | Invoking a stored procedure in a database |
JDBC Batch Processing
JDBC provides the facility to submit multiple queries at once to the database.
Batch processing avoids multiple calls between application and database server, because of this network traffic will be reduced and performance of the application will be increased.
For examples if you want to execute 100 SQL statements then you need to interact with database 100 times which will increase the network traffic between the application and the database server. Instead of sending the SQL statements one by one to the database, you can group all the statements into a batch and submit them with one call to the database. By doing batch processing you can reduce the network traffic between your application and the database server.
We can do only INSERT, UPDATE & DELETE operations with batch updates but cannot write SELECT statements.
Following are the different methods which can be used for batch processing.
All Chapters
