View RSS Feed


Java Database Connectivity (JDBC) examples. SQL with Java.

  1. Batch Updates and Transactions

    by , 04-27-2012 at 08:18 PM
    It is important to keep in mind, that each update added to a Statement or PreparedStatement is executed separately by the database. That means, that some of them may succeed before one of them fails. All the statements that have succeeded are now applied to the database, but the rest of the updates may not be. This can result in an inconsistent data in the database.

    To avoid this, you can execute the batch update inside a transaction. When executed inside a transaction you can make ...
  2. PreparedStatement Performance

    by , 04-27-2012 at 08:11 PM
    It takes time for a database to parse an SQL string, and create a query plan for it. A query plan is an analysis of how the database can execute the query in the most efficient way.
    If you submit a new, full SQL statement for every query or update to the database, the database has to parse the SQL and for queries create a query plan. By reusing an existing PreparedStatement you can reuse both the SQL parsing and query plan for subsequent queries. This speeds up query execution, by decreasing ...
  3. J2EE PreparedStatement Cache

    by , 04-27-2012 at 08:10 PM
    J2EE PreparedStatement Cache is implemented using a cache inside the J2EE server connection pool manager. The J2EE server keeps a list of prepared statements for each database connection in the pool. When an application calls prepareStatement on a connection, the application server checks if that statement was previously prepared. If it was, the PreparedStatement object will be in the cache and this will be returned to the application. If not, the call is passed to the jdbc driver and the query/preparedstatement ...
  4. PreparedStatements and J2EE servers

    by , 04-27-2012 at 08:09 PM
    Things can get more complicated when we use a J2EE server. Normally, a prepared statement is associated with a single database connection. When the connection is closed, the preparedstatement is discarded. Normally, a fat client application would get a database connection and then hold it for its lifetime. It would also create all prepared statements eagerly or lazily. Eagerly means that they are all created at once when the application starts. Lazily means that they are created as they are used. ...
  5. How does a database execute a statement?

    by , 04-27-2012 at 08:09 PM
    Obviously, don't expect alot of detail here; we'll only examine the aspects important to this article. When a database receives a statement, the database engine first parses the statement and looks for syntax errors. Once the statement is parsed, the database needs to figure out the most efficient way to execute the statement. This can be computationally quite expensive. The database checks what indexes, if any, can help, or whether it should do a full read of all rows in a table. Databases use ...
  6. Supplying Values for PreparedStatement Parameters

    by , 04-27-2012 at 08:08 PM
    You must supply values in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. Do this by calling one of the setter methods defined in the PreparedStatement class. The following statements supply the two question mark placeholders in the PreparedStatement named updateSales:

    Java Code:
    updateSales.setInt(1, e.getValue().intValue());
    updateSales.setString(2, e.getKey());
    The first argument for each of ...
  7. Overview of Prepared Statements

    by , 04-27-2012 at 08:06 PM
    Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, that you already know.

    If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.

    The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. ...
  8. Getting and Using Pooled Connections

    by , 04-27-2012 at 08:06 PM
    A connection pool is a cache of database connection objects. The objects represent physical database connections that can be used by an application to connect to a database. At run time, the application requests a connection from the pool. If the pool contains a connection that can satisfy the request, it returns the connection to the application. If no connections are found, a new connection is created and returned to the application. The application uses the connection to perform some work on ...
  9. Advantages of DataSource Objects

    by , 04-27-2012 at 08:05 PM
    Because of its properties, a DataSource object is a better alternative than the DriverManager class for getting a connection. Programmers no longer have to hard code the driver name or JDBC URL in their applications, which makes them more portable. Also, DataSource properties make maintaining code much simpler. If there is a change, the system administrator can update data source properties and not be concerned about changing every application that makes a connection to the data source. For example, ...
  10. Using DataSource Objects to Get a Connection

    by , 04-27-2012 at 08:04 PM
    Objects instantiated by classes that implement the DataSource represent a particular DBMS or some other data source, such as a file. A DataSource object represents a particular DBMS or some other data source, such as a file. If a company uses more than one data source, it will deploy a separate DataSource object for each of them. The DataSource interface is implemented by a driver vendor. It can be implemented in three different ways:

    • A basic DataSource implementation produces standard ...
  11. Establishing a Connection

    by , 04-27-2012 at 08:03 PM
    First, you need to 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. Typically, a JDBC application connects to a target data source using one of two classes:

    • DriverManager: This fully implemented class connects an application to a data source, which is specified by a database URL. When this class first attempts to establish a connection, it automatically
  12. JDBC: Query the Database

    by , 04-27-2012 at 08:03 PM
    Querying a database means searching through its data. You do so be sending SQL statements to the database. Once you have an open connection, you need to create a Statement object, like this:
    Java Code:
    Statement statement = connection.createStatement();
    Once you have created the Statement you can use it to execute SQL queries, like this:
    Java Code:
    String sql = "select * from people";
    ResultSet result = statement.executeQuery(sql);
  13. The IDS Driver

    by , 04-27-2012 at 08:02 PM
    The IDS Driver is a type 3 driver with the IDS Server as its backend. Database queries and results are sent back and forth between the IDS Driver and the IDS Server. As it is a 100% Pure Java implementation, the driver guarantees the "Write Once, Run Anywhere" promise of Java. Indeed, it runs in all Java-enabled browsers, the Java Plug-in, and the JDK, plus all your favorite Java development tools.

    IDS Server supports Oracle (native OCI or ODBC), Sybase (native CT-Lib or ...
  14. Type 4: Native-protocol/all-Java driver

    by , 04-27-2012 at 08:01 PM
    The native-protocol/all-Java driver (JDBC driver type 4) converts JDBC calls into the vendor-specific database management system (DBMS) protocol so that client applications can communicate directly with the database server. Level 4 drivers are completely implemented in Java to achieve platform independence and eliminate deployment administration issues.

    Since type 4 JDBC drivers don't have to translate database requests to ODBC or a native connectivity interface or to ...
  15. Type 3: Net-protocol/all-Java driver

    by , 04-27-2012 at 08:00 PM
    JDBC driver type 3 -- the net-protocol/all-Java driver -- follows a three-tiered approach whereby the JDBC database requests are passed through the network to the middle-tier server. The middle-tier server then translates the request (directly or indirectly) to the database-specific native-connectivity interface to further the request to the database server. If the middle-tier server is written in Java, it can use a type 1 or type 2 JDBC driver to do this.

    The net-protocol/all-Java ...
  16. Type 2: Native-API/partly Java driver

    by , 04-27-2012 at 07:59 PM
    JDBC driver type 2 -- the native-API/partly Java driver -- converts JDBC calls into database-specific calls for databases such as SQL Server, Informix, Oracle, or Sybase. The type 2 driver communicates directly with the database server; therefore it requires that some binary code be present on the client machine.

    Type 2 drivers typically offer significantly better performance than the JDBC-ODBC Bridge.

    The vendor database library needs to be ...
  17. Type 1: JDBC-ODBC Bridge

    by , 04-27-2012 at 07:58 PM
    The type 1 driver, JDBC-ODBC Bridge, translates all JDBC calls into ODBC (Open DataBase Connectivity) calls and sends them to the ODBC driver. As such, the ODBC driver, as well as, in many cases, the client database code, must be present on the client machine. Figure 1 shows a typical JDBC-ODBC Bridge environment.

    The JDBC-ODBC Bridge allows access to almost any database, since the database's ODBC drivers are already available. Type 1 drivers may be useful for those companies ...
  18. JDBC Driver Manager

    by , 04-27-2012 at 07:57 PM
    The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple.

    This is a very important class. Its main purpose is to provide a means of managing the different types of JDBC database driver. On running an application, it is the DriverManager's responsibility to load all the drivers found in the system property jdbc. drivers. For example, this ...
  19. What is a JDBC driver?

    by , 04-27-2012 at 07:13 PM
    The JDBC API defines the Java interfaces and classes that programmers use to connect to databases and send queries. A JDBC driver implements these interfaces and classes for a particular DBMS vendor.

    A Java program that uses the JDBC API loads the specified driver for a particular DBMS before it actually connects to a database. The JDBC DriverManager class then sends all JDBC API calls to the loaded driver.

    The four types of JDBC drivers are:

    • JDBC-ODBC ...
    Tags: jdbc driver Add / Edit Tags
  20. JDBC Architecture

    by , 04-27-2012 at 07:11 PM
    The JDBC API supports both two-tier and three-tier processing models for database access.

    In the two-tier model, a Java application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. ...
  21. JDBC Product Components

    by , 04-27-2012 at 07:10 PM
    JDBC includes four components:

    • The JDBC API — The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.
    • The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE
  22. JDBC Introduction

    by , 04-27-2012 at 07:09 PM
    The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database.
    JDBC helps you to write Java applications that manage these three programming activities:
    1. Connect to a data source, like a database
    2. Send queries and update statements to the database
    3. Retrieve and process the results received from the database in answer to your query
    The following simple code fragment gives a simple example of these three ...
  23. Working with JDBC in Java

    by , 04-04-2012 at 07:32 AM
    Java Database Connectivity (JDBC) is a technology that allows Java applications working with relational database management systems (RDBMS) such as MySQL, Microsoft SQL Server, Oracle, PostgreSQL, … via Structured Query Language (SQL) statement. The JDBC API is included in JDK, so any Java application can communicate with various database systems without requiring additional installation, as long as a JDBC driver is provided for the targeted database system.

    By the end of this article, ...