For Developers

What is JDBC? Creating a Java JDBC Connection

What is JDBC? Creating a Java JDBC Connection

Inspired by the original ODBC (Open Database Connectivity), the JDBC stands for Java Database Connectivity. JDBC is a Java database API that was created as an industry standard for making connections between Java applications and other databases. The Java JDBC connection in Java API defines all classes that handle various aspects of the database, such as connection details, result sets, and database metadata. Originally, JDBC was intended to be a client-side API that could connect to a data source. In version 2.0, it was added with an optional package that supports server-side connections. Since then, every new release of JDBC has included updates to both the client-side and server-side packages. The most recent version, which was released in September 2017, was part of Java SE 9.

More so, it supports various types of relational databases which use SQL (Structured Query Language) such as DB2, ORACLE, and Sybase. Each of these possesses a unique JDBC driver name, that encodes access request statements, issues commands, and handles result sets obtained from the database.

Understanding JDBC in Java

Before we delve into the detailed processes of creating a JDBC connection with databases like SQL, we have to understand all the concepts, tools, classes, and interfaces involved such as JDBC components, JDBC drivers, as well as the relations between relational databases like SQL and JDBC.

java jdbc connection

JDBC components

The JDBC core comes with the following interfaces and classes:

  • Driver: This is the interface that controls communication with the database server. It also withdraws information associated with driver objects.
  • Driver Manager: It manages any required set of JDBC drivers
  • Connection: This is an interface or session that houses all the methods to connect to any database.
  • Statements: This is used to carry out a static SQL statement
  • ResultSet: This is used to access the result row-by-row

java jdbc connection

JDBC drivers

JBDC drivers are unique implementation tools used for connecting to a particular database for interaction with the database server. There are various types of these drivers:

  • JDBC-ODBC bridge driver (Type 1): This JDBC bridge driver contains a mapping to another data access API, such as ODBC drivers, which requires configuring on each system a Data Source Name (DSN) that represents the targeted database.

  • JDBC-Native API (Type 2): This is a driver that utilizes the client-side libraries of the target database, also called a native-API driver.

  • JDBC-Net pure Java (Type 3): Type 3 drivers use a three-tier approach to access databases through middleware to change JDBC calls into each database-specific call

  • 100% pure Java (Type 4): This type of driver is the highest performance driver and is used for databases such as Oracle, IBM, and Sybase. It works directly with the vendor’s database using a socket connection.

Relationship between SQL (a relational database) and JDBC

A relational database like SQL is a structured repository that stores records in tables with columns and rows. Although NoSQL has gained popularity over the past decade, relational databases like SQL are still the most commonly used type of datastore. The main language used by data architects to perform various tasks related to a relational database is SQL. It can perform various tasks such as creating, reading, and updating records.

In Java JDBC connection, the JDBC acts as an adapter layer that provides adaptability to SQL from Java. This enables the Java developers to connect to a database and allows them to perform various tasks like managing responses and queries.

How to create a Java JDBC connection

We have learned what a JDBC is, what it is used for, its components, driver types, and its relationship with relational databases like SQL. Now, we will be going through detailed steps through which we use JDBC to create a connection to a database in Java. Here is the 7 step process to create a Java JDBC connection:

1. Import the packages:

This includes uploading all the packages containing the JDBC classes, interfaces, and subclasses used during the database programming. More often than not, using import java.sql.* is enough. However, other classes can be imported if needed in the program.

2. Register the drivers:

Before connecting to the database, we’ll need to load or register the drivers once per database. This is done to create a communication channel with the database. Loading a driver can be done in two ways:

Class.forName()

DriverManager.registerDriver()

3. Establish a connection:

For the next step here, the getConnection() method is used to create a connection object that will correspond to a physical connection with the database. To get the getConnection() to access the database, the three parameters are a username, string data type URL, and a password. Two methods can be used to achieve this:

  • getConnection(URL, username, password): This uses three parameters URL, a password, and a username

  • getConnection(URL): This has only one parameter - URL. The URL has both a username and password. There are several JDBC connection strings for different relational databases and some are listed below:

    a. IBM DB2 database: jdbc:db2://HOSTNAME:PORT/DATABASE_NAME

    b. Oracle database: jdbc:oracle:thin:@HOST_NAME:PORT:SERVICE_NAME

    c. My SQL database: jdbc:mysql://HOST_NAME:PORT/DATABASE_NAME

4. Create a statement:

The statement can now be created to perform the SQL query when the connection has been established. There are three statements from the createStatement method of the connection class to establish the query. These statements are

  • Statement: This is used to create simple SQL statements with no parameter. An example is: Statement statemnt1 = conn.createStatement();. This statement returns the ResultSet object.
  • PreparedStatement: This extends the Statement interface. It improves the application's performance because it has more features and compiles the query only once. It is used for precompiled SQL statements that have parameters.
  • CallableStatement: CallableStatements also extends the PreparedStatement interface. It is used for SQL statements with parameters that invoke procedure or function in the database. It is simply created by calling the prepare all method of the connection object.

5. Execute the query:

This uses a type statement object to build and submit SQL statements to a database. It has four distinct methods:

  • ResultSet executeQuery(String sql)
  • executeUpdate(String sql)
  • execute(String sql)
  • executeBatch()

6. Retrieve results:

When queries are executed using the executeQuery() method, it produces results stored in the ResultSet object. The ResultSet object is then used to access the retrieved data from the database.

7. Close the connections:

The JDBC connection can now be closed after all is done. The resource has to be closed to avoid running out of connections. It can be done automatically using ‘conn.close();’. But for versions of Java 7 and above, it can be closed using a try-catch block

Conclusion

The JDBC API is a universal data access mechanism that can be used by any program that uses Java. With the JDBC API, you can access almost any type of data source, such as relational databases and flat files. It also provides a common base for developing tools and alternate interfaces. After creating the connection, it can allow the programmer to access request statements and issue commands and handle result sets obtained from the database. The process of things done within the Java application is summarized in three steps, and they are:

  • Establishing a connection with a data source
  • Send queries and update statements to the data source
  • Processing the results

Author

  • Author

    Gospel Bassey

    Gospel Bassey is a creative technical writer who harnesses the power of words to break down complex concepts into simple terms. He is an experienced Technical Writer who has developed content in various fields of technology, such as Blockchain technology, Information Technology, and Data Science, to mention but a few.

Frequently Asked Questions

Java uses the JDBC to handle connections to relational databases such as Microsoft SQL Server, Oracle Database, MySQL, and IBM DB2. Java uses JDBC as an adaptive layer for these databases.

JDBC is used in Java because JDBC enables the establishment of a connection with data sources, send queries, update statements, and process the results.

There are four types of JDBC drivers and they are JDBC-ODBC bridge driver (Type 1), Native-API driver (Type 2), Network Protocol driver (Type 3), and Thin driver (Type 4).

View more FAQs
Press

Press

What's up with Turing? Get the latest news about us here.
Blog

Blog

Know more about remote work.
Checkout our blog here.
Contact

Contact

Have any questions?
We'd love to hear from you.

Hire and manage remote developers

Tell us the skills you need and we'll find the best developer for you in days, not weeks.

Hire Developers