Use JDBC for
industrial-strength performance, Part 1 - JavaWorld January
2000
Tutorial Details:
Use JDBC for industrial-strength performance, Part 1
Use JDBC for industrial-strength performance, Part 1
By: By Lennart Jörelid
Java server-side data mining patterns made powerful with JDBC
JDBC usage for industrial-strength performance: Read the whole series!
Part 1. Java server-side data mining patterns made powerful with JDBC
Part 2. Follow these Java server-side data mining patterns with multiple ResultSets for maximum performance
odern application systems tend to rely heavily on server-side data storage support using one or more databases. The best way to contact a database from a server-side Java application is by using the class hierarchy in the java.sql package. However, while most JDBC classes and interfaces are well designed, my experience is that corporations building data-mining systems in Java often use simpler, rather than more powerful, JDBC usage patterns. By augmenting the JDBC API as shown in this article, you'll become aware of the possibilities it provides. The usage patterns I describe here can be implemented with JDBC version 1.22 and 2.0.
Current obvious usage scenarios
Two obvious data-mining scenarios, the standard ResultSet and CallableStatement scenarios, exist for creating server-side applications that talk to a database. You can likely use these scenarios on any database for industrial-strength application deployment, since the JDBC API is developed to support them properly. For the specific purposes of this article, let's assume that we are working with an industrial-strength database that can handle database-compiled stored procedures.
The table below describes our two scenarios.
Usage Scenario
Description
Diagram of Scenario
Standard ResultSet scenario
Create a Statement (or PreparedStatement ) from the open database Connection . Pass the SQL query to the Statement and receive a ResultSet with the logical view from the database.
Standard ResultSet Scenario
Standard CallableStatement scenario
Create a CallableStatement from the open database Connection . Pass an SQL procedure call (executing a database stored procedure) to the CallableStatement and receive a primitive or string from the database.
Standard CallableStatement Scenario
Table 1. Two obvious data-mining scenarios
Below is the skeleton code for the standard ResultSet scenario:
// Database connection supposedly
// already made.
Connection conn;
Statement aStatement = null;
ResultSet aResultSet = null;
try
{
// Create the database statement.
aStatement = conn.createStatement();
// Ship the question to the database
aResultSet = aStatement.executeQuery("");
// Handle the result
while(aResultSet.next())
{
// ...
}
}
catch(Exception ex)
{
// Log an error message
System.err.println("Database communication error: " + ex);
}
And here is the skeleton code for the standard CallableStatement scenario:
// Database connection supposedly
// already made.
Connection conn;
CallableStatement aStatement = null;
try
{
// Create the database callable statement.
// For example, assume that the ""
// is something like "{call calculate_interest_rate(?)}"
// where the '?' denotes an output parameter.
aStatement = conn.prepareCall("");
// Register a primitive output parameter, in this
// example case of SQL type INTEGER. All type constants
// can be found in the java.sql.Types class.
aStatement.registerOutParameter(1, Types.INTEGER);
// Call the procedure in the database
aStatement.executeUpdate();
// Handle the output
int valueReceived = aStatement.getInt(1);
}
catch(Exception ex)
{
// Log an error message
System.err.println("Database communication error: " + ex);
}
The CallableStatement interface provides a rich variety of getXXX methods. As a result, it can receive parameters of various types from the database stored procedure call. The registered parameters must be declared as OUT parameters in the database stored procedure if getXXX is to receive them.
Limitations of current obvious usage scenarios
It seems that speed and stability are the issues most important to database-application engineers when they begin developing server-side Java applications. Although the two standard scenarios described above are quite stable, a hungry developer seeking cutting-edge performance may find them lacking. Depending on the query type and results expected, the two scenarios may be quick enough for all purposes, or they may be terribly lacking in speed.
Question
Answer
What type of JDBC query would make the two scenarios above lose performance?
An SQL query (returning multiple rows and columns) that wouldbe called multiple times with sufficiently different search conditions to make the database SQL compilation cumbersome -- in other words, the archetype for creating a stored procedure which would return a ResultSet .
Why would I gain speed by creating a database stored procedure instead of sending an SQL query to the database engine?
Although you may not be sure how the database driver class implements its method calls, it is safe to assume that the result is handled more quickly as it is produced closer to the database engine. In general, system bottlenecks are introduced anywhere a network or interprocess connection is established.
Thus, the quickest way to assemble complex two-dimensional ResultSets (i.e., those containing data from several tables, and perhaps calculated values as well) is to create a database stored procedure to call.
Table 2. Performance issues affecting the scenarios in Table 1
Thus, the quickest way to assemble complex, two-dimensional ResultSets (containing data from several tables, and perhaps calculated values as well), is to create a database stored procedure to call.
From a design standpoint, system encapsulation increases if important database functionality is hidden behind a procedure name. By sharing advantages similar to object-oriented encapsulation, system flexibility increases, assuming that you can restructure database data without affecting the Java server application. In other words, decoupling the database physical view from the logical view as seen by the application leads to a faster and more maintainable system.
You could improve server-side database communication performance by using a hybrid of these two scenarios, as described in the table below:
Usage Scenario
Description
Diagram of Scenario
ResultSet CallableStatement scenario
Create a CallableStatement from the open database Connection . Pass an SQL procedure call (executing a database stored procedure) to the CallableStatement and receive a ResultSet from the database.
ResultSet CallableStatement Scenario
Table 3. The hybrid ResultSet CallableStatement scenario
Although the ResultSet CallableStatement scenario seems simple enough to realize in Java code, the differences between databases and JDBC database drivers are now growing painfully visible to the developer. Despite the fact that JDBC is supposed to be mostly platform- and database-independent, the specifics of each database are visible not only when defining a stored procedure, but also when trying to return a ResultSet from one. Some database engines cannot return values from stored procedures; values that should be returned to the Java application must instead be declared as output parameters. Other databases may return only some kinds of types from a stored procedure call (generally INTEGER values to indicate the error code status of a procedure call). There are still other database types that might be able to return a full JDBC ResultSet equivalent (in some cases, this would correspond to a database CURSOR type), either as a result from a stored procedure call or as an output parameter. Depending on the capabilities of the stored procedure execution engine being called, different calling methods may be used, as described in the table below:
Scenario
Database Capabilities
Description
1
Stored procedures cannot return values, but may return output parameters.
Input and output parameters alike must be set/registered in the CallableStatement prior to executing the query.
2
Stored procedures can return values, but only primitives (such as INTEGER error codes).
Stored procedures may return output parameters of any kind.
Unless the desired return value of a stored procedure is of the specific type(s) that may be returned, all parameters must be set/registered in the CallableStatement prior to executing the query.
3
Stored procedures may return values of any type.
ResultSets may be returned directly or as output parameters from a stored procedure call. All input parameters must be set before executing the query.
4
Stored procedures may return (multiple) implicit ResultSets without using output parameters or return values.
(Multiple) ResultSets may be returned implicitly from the stored procedure call. No output parameters are required.
Table 4. Different calling methods for various database capabilities
Below is the database pseudocode to match the above scenarios. A detailed description of each scenario follows in the next section.
Scenario
Database pseudocode
1
CREATE PROCEDURE
(theResult CURSOR OUTPUT, )
AS
/* Gather data from all over database */
/* .... */
END PROCEDURE
2
CREATE PROCEDURE
(theResult CURSOR OUTPUT, )
AS
/* Gather data from all over database */
/* .... */
RETURN
END PROCEDURE
3
CREATE PROCEDURE
()RETURNING CURSOR
AS
/* Gather data from all over database */
/* .... */
RETURN
END PROCEDURE
4
CREATE PROCEDURE
()
AS
/* Gather data from all over database */
/* .... */
SELECT .... /* Creates an output ResultSet */
END PROCEDURE
Table 5. Database pseudocode for the various scenarios in Table 4
The JDBC calling structure (and the c
Read
Tutorial at: Click here to view the tutorial
Rate Tutorial: Use JDBC for
industrial-strength performance, Part 1 - JavaWorld January
2000
View Tutorial: Use JDBC for
industrial-strength performance, Part 1 - JavaWorld January
2000
Related
Tutorials:
|
Displaying 1 - 50 of about 3511 Related Tutorials.
|
 JDO UNPLUGGED - PART 1
it differs from the rest of the technologies.
When we use JDBC, we have to manage... UNPLUGGED - PART I
JDO UNPLUGGED - PART I... JDBC and EJB with CMP and BMP. Also there are a number of ORM
tools like
|
The Ultima Linux 8 has been released
desktop or an industrial-strength server. It packs in over
350 unique packages... for first-time users
Download
Disc 1 via BitTorrent
Download
Disc...:
High-performance, bleeding-edge 2.6.14.6, or
Time-tested and stable 2.4.32
|
The Ultima Linux 8 has been released
desktop or an industrial-strength server. It packs in over
350 unique packages... for first-time users
Download
Disc 1 via BitTorrent
Download
Disc...:
High-performance, bleeding-edge 2.6.14.6, or
Time-tested and stable 2.4.32
|
Console I/O
) and
industrial strength file and Internet I/O.
No attempt was made to have simple... is rarely used, but programmers
often use System.out.println
for output during... that use the console.
These are equivalent to Unix standard input,
error
|
JDBC Components
. JDBC includes four components:
1. The JDBC
API
The JDBC API
gives access... statements and retrieve results and
updation to the database. The JDBC API is part...
important part of the JDBC architecture.
3. JDBC Test
Suite
The JDBC driver
test
|
Understanding the JDBC Architecture
relational databases. JDBC is a core part of the Java platform and is included... to the JDBC drivers and the servlet is shown in Figure
1.
Layers of the JDBC... distinct types of JDBC drivers.
Type 1 JDBC-ODBC Bridge. Type 1 drivers act
|
JDBC Versions
;
1).
The JDBC 1.0 API.
2... and monitor distributed virtual databases.
Features of JDBC 1.2 API
1....
Features of The JDBC 2.0 Optional Pacakage API
1
|
Free PHP Books
leading PHP developers show you how to make the most of PHP 5's industrial-strength... are testament to its popularity and ease of use. PHP is used by both programmers.... As discussed in Chapter 1, HTTP is a stateless protocol. Each HTTP request a browser sends
|
Product Components of JDBC
;
JDBC has four Components:
1...-ODBC Bridge.
1. The JDBC API.
The JDBC application.... JDBC application programmming interface is a part of the Java
|
JDBC Driver and Its Types
Better performance than Type 1 since no jdbc to odbc translation....
Type 1 Driver- the JDBC-ODBC bridge
The JDBC type 1 driver, also... Bridge Driver.
Type 1 drivers are "bridge" drivers. They use another
|
JDO UNPLUGGED - PART II
UNPLUGGED - PART II
JDO UNPLUGGED - PART II... find the following four jar files
1. jdo.jar : It contains the standard... needed for our application. They are,
1. jta.jar : The synchronization interface
|
First Step towards JDBC!
are used.
In the later sections we will show you how to use JDBC from your.... These are:
Type 1: JDBC-ODBC
Bridge Driver...
JDBC-ODBC bridge is example of type 1 driver.
Type 2: Native
|
Important JDBC Concepts
a log file name for the JDBC log.
Attributes of Logging:
1) Enable... for a JDBC transaction, use the
Connection.setTransaction(int level) method...
Important JDBC Concepts
Important JDBC Concepts
|
Introduction to the JDBC
JDBC driver types. These are:
Type 1: JDBC... connectivity and high-performance,
DBMS-independent JDBC middleware...
Introduction to the JDBC
|
JDBC, JDBC Tutorial, JDBC Tutorials
a
uniform interface for accessing various relational databases. JDBC is a core
part...
This example shows you how to use JDBC's rich data type BLOB.
The BLOB type...
JDBC - Java Database Connectivity Tutorials
JDBC
|
Features of JDBC 4.0
-of-development features in JDBC 4
In addition to ease-of-use, JDBC 4 introduces... that had been in use in
database products even before it became part...
Features of JDBC 4.0
Features of JDBC 4.0
|
JDBC (Java Database Connectivity) -Tutorials
of today's database applications use relational databases. The
JDBC API... can be only be accessed using the JDBC-ODBC
bridge.
Part Java, Part Native Driver - This approach
use a mixture of Java implementation
|
JDBC Prepared Statement Insert
JDBC Prepared Statement Insert
JDBC Prepared...;
The Tutorial illustrates a program in JDBC Prepared... as follow -1)Importing a java.sql provides you a network
interface that enables you
|
String Exercises 1 - Answers
Java: String Exercises 1 - Answers
Java: String Exercises 1 - Answers
Answers to the String Exercises 1.
3 -- s refers to exactly the same string as a.
ERROR -- t
|
JDBC vs ORM
good performance with large amount of data
Small JDBC programs can be developed...
JDBC vs ORM
JDBC vs ORM...;
In this section we will examine the differences and similarities between JDBC
|
First Step towards JDBC!
for accessing various relational databases. JDBC is a core
part of the Java...
JDBC - Java Database Connectivity Tutorials
JDBC - Java Database Connectivity Tutorials
|
Navigable Map Example
;String>();
navMap.put(1, "January");
...=June, 5=May, 4=April, 3=March, 2=February, 1=January}
First data: 1... key: 6=June
Removing First: 1=January
Removing Last: 12
|
JDBC Insert Null
JDBC Insert Null
JDBC Insert Null...;
In
this Tutorial we want to describe you a code that help in understanding JDBC
InsertNull. The program include a class JDBC Insert Null, Inside the class we
|
Difference between JDBC 3.0 & JDBC 4.0
JDBC 4 is a major new release with a strong focus
ease-of-use... ease-of-development features in JDBC 4
In addition to ease-of-use, JDBC 4... type that had been in use in
database products even before it became part
|
Wi-Fi as a part of LBS
Wi-Fi as a part of LBS
Wi-Fi as a part of LBS
... in the network together. By the use of emitted radio signals one can connect computers
|
JDBC Insert Preparedstatement
JDBC Insert Preparedstatement
JDBC Insert... you a code that helps you in understanding
JDBC Insert Prepared Statement. For this we have a class Jdbc Insert Prepared statement, Inside
this class we have
|
JDBC Insert Preparedstatement
JDBC Insert Preparedstatement
JDBC Insert... you a code that helps you in understanding
JDBC Insert Prepared Statement. For this we have a class Jdbc Insert Prepared statement, Inside
this class we have
|
JDBC - Java Database Connectivity Tutorial
a
uniform interface for accessing various relational databases. JDBC is a core
part...
This example shows you how to use JDBC's rich data type BLOB.
The BLOB type...
JDBC - Java Database Connectivity Tutorials
JDBC
|
Use of "preceding" axis in XPath expression
;
In this part of tutorial we are going to describe you
the use of "preceding" axis...
Use of "preceding" axis in XPath expression
Use...; <person id="1">
|
Test Performance Tools Platform (TPTP) Training
the developers to
build the unique test and use it as the performance
tool?both open...
Test
Performance Tools Platform (TPTP) Training
Test
Performance Tools Platform (TPTP) Training
|
WEBSERVICE USING APACHE AXIS - TUTORIAL-2 AXIS FOR EJB-WEBSERVICE (part-5)
had seen parts 1 to 4 of this tutorial on exposing an EJB as XML-Webservice using Axis. This is a 7 part
article.?
part-1 : Overview
part-2 : deploying... approach is preferable because, the best method is to use
a stateless-session bean
|
JDBC Fetch
JDBC Fetch
JDBC Fetch
... to describe you an
example from Jdbc Fetch. The code illustrates a class Jdbc Fetch, Inside the
main method we have the list of steps to be followed - 1
|
JDBC Next
JDBC Next
JDBC Next
... help you to understand an example from JDBC Next. The code
include a class JDBC... below -1)Import a package java.sql.* - This
package provides you set of all
|
Open Source Database
To date, there has been no easy way to benchmark the performance of a database system. The choices were
1. Hire a consulting group... offers excellent concurrency, high performance, and powerful language support
|
What is JDBC?
What is JDBC?
What is JDBC?
...;
JDBC is Java application programming interface that allows the Java programmers... Connectivity in short called as JDBC. It is a java API which enables the java
|
DataScope JDBC
DataScope JDBC
DataScope JDBC...;
DataScope is an extensible JDBC plugin for the Eclipse IDE
that allows....
DataScope is highly customizable: Just about every part of the DataScope
UI can
|
Jdbc connection
Jdbc connection
Jdbc connection
... we want to describe you a code that helps you in
understanding a Jdbc Connection. The code include a class Jdbc Connection, Inside
the main method, the list
|
JDBC Functions
JDBC Functions
JDBC Functions
... want to describe you a
code that helps you easy to understand Jdbc Functions. The code include a class
Jdbc Functions, Inside the main method, the list
|
JDBC Exception
JDBC Exception
JDBC Exception
...;
In
this Tutorial we want to describe you a code that help you to understand JDBC... follow the
list of following steps - 1) The first step is to import a package
|
Jdbc-Odbc Connectivity
Jdbc-Odbc Connectivity
Jdbc-Odbc Connectivity...;
The code illustrates an example from JDBC-ODBC Connectivity...
method ( ) follows a list of step in it -1) Importing a package
java.sql,provides
|
Jdbc ConnectionUrl
Jdbc ConnectionUrl
Jdbc ConnectionUrl
...;
In this Tutorial we want to describe you a code that
helps you to understand Jdbc... used are as follows-
1)We need to import a package java.sql* - This import
|
Jdbc autocommit
Jdbc autocommit
Jdbc autocommit
...;
Jdbc connection...
In this Tutorial we want to describe you a code that
helps in understanding JDBC auto commit
|
Jdbc batch
Jdbc batch
Jdbc batch
...;
JDBC is simply a Java... be
SQL-2000,2005,MySql. A batch in Jdbc is a set of sql statement
|
Jdbc Prepared Statement Addbatch
Jdbc Prepared Statement Addbatch
Jdbc Prepared...;
The
code illustrate an example from JDBC Prepared statement Add batch. In this code
we have defined a class Jdbc
|
JDBC Prepared Statement Update
JDBC Prepared Statement Update
JDBC Prepared... that help you in understanding JDBC Prepared Statement Update. The code
include a class Jdbc Prepared Statement, this class include a main method ( ),
Inside
|
Common Interview Questions Page -1
Common Interview Questions Page -1
Common Interview Questions Page -1
...;
Question:1. Tell Me a Little
|
JDBC Insert Record
JDBC Insert Record
JDBC Insert Record...;
In
this Tutorial we want to explain you an example from JDBC Insert Record. The
code include a class JDBC Insert Record, Inside the class we have a main
|
JDBC Prepared statement Close
JDBC Prepared statement Close
JDBC Prepared...;
The code illustrates you an example from JDBC Prepared
Statement Close. In this code, the class Jdbc Preparedstaement Close include
|
Accessing Database from servlets through JDBC!
Accessing Database from servlets through JDBC... and Sun's JDBC-ODBC bridge to connect to access data source. First...
border=1 cellPadding=1 cellSpacing=1
width=\"75
|
JDBC Get Metadata
Jdbc Get Metadata
JDBC Get Metadata...;
The
Tutorial illustrate a program that helps you to understand JDBC Get... that
follows the list of following steps - 1) The first step is to import
|
|
|
|
