FB

As a Java developer, it’s very likely you’ll need to connect to a database.

There are several ways to do this in Java, from creating a simple connection using JDBC in Java to using a complete framework for managing data storage or persistence.

Improve your knowledge of Java and database connections with this collection of guides and resources.

JDBC in Java

Table of Contents

 

1. Setting Up Simple Connections with JDBC in Java

(Back to Top)

In order to connect to a database from a Java application, you’ll need an API (Application Programming Interface), which is a set of standard code that allows you to interact with another part of a system.

One of the most common APIs is JDBC, which stands for Java Database Connectivity. JDBC is used by Java applications to connect to databases. It works with many database types such as Oracle, SQL Server, MySQL, Microsoft Access, SQLite, and PostgreSQL.

Also, because it is platform independent, it works on Windows, Mac, and Linux computers.

JDBC is written in Java code and it is included in both Java SE (Standard Edition) and Java EE (Enterprise Edition).

So, how can you get started with JDBC in Java? Here’s a collection of resources for doing just that.

Getting Started with Java

If you are just getting started with Java, you can follow these tutorials to get up and running. You’ll learn how to download an IDE (Integrated Development Environment) and set up Java.

Setting Up a Connection

These guides explain how to set up a connection and access the database.

More JDBC Features

These tutorials explain many of the other features included in JDBC in Java, such as:

  • Creating and populating tables
  • Retrieving and modifying values with SQL
  • Using Prepared Statements
  • Transactions
  • RowSet objects

The tutorials are listed here:

Get Your Free PDF: 9 Ways to Improve your Database Skills

 

2. Learning the JPA Specification (Java Persistence API)

(Back to Top)

JPA stands for Java Persistence API. It’s a specification that defines an API for managing data between Java objects and a relational database.

JPA is a lightweight alternative to Enterprise JavaBeans (EJB), and was developed to be easier and use fewer resources. You can also use JPA instead of writing SQL code using JDBC.

However, JPA can’t be used by itself to access the database. It’s just a specification. There is no code that will allow you to access the database. You’ll need to use a framework in order to be able to access the database. I’ve listed a few of these in the next section on Object Relational Mapping.

JPA can be thought of as an interface, and a framework that uses it can be thought of as a class that uses the interface.

 

3. Using an Object Relational Mapping Tool to Handle Database Access

(Back to Top)

An Object Relational Mapping (ORM) tool is a set of code that is used by developers to handle database access. But it’s much more than just accessing a database. It allows developers to map their object-oriented domain (classes in Java) to the database tables.

It’s used as an alternative to creating your own code and writing SQL using JDBC.

Why would you use an ORM? There are several reasons:

  • It can increase the speed of development, as you save time having to map your objects to your database tables.
  • You can easily perform CRUD operations (Create, Read, Update, and Delete) on your data.
  • You can write business logic in your data access layer, instead of stored procedures and triggers, making it easier to debug and possibly easier for your team to work with.

There are some reasons not to use an ORM though:

  • Your team may have very strong database skills but not so strong Java skills.
  • You may need to use complex logic that is hard or not possible with an ORM.

Several users on this StackOverflow question have mentioned some disadvantages of using ORMs:

“I have stopped using ORMs.

The reason is not any great flaw in the concept. Hibernate works well. Instead, I have found that queries have low overhead and I can fit lots of complex logic into large SQL queries, and shift a lot of my processing into the database.

So consider just using the JDBC package.“ – David Crawshaw

Another quote on ORMs:

“None, because having an ORM takes too much control away with small benefits. The time savings gained are easily blown away when you have to debug abnormalities resulting from the use of the ORM. Furthermore, ORMs discourage developers from learning SQL and how relational databases work and using this for their benefit.” – simon

There are a few ORM tools that are available. The most widely known is Hibernate, but that’s not the only one:

Alternatively, you can use jOOQ, which is an abstraction that uses JDBC and encourages the use of SQL-equivalent code.

If you want to get started with using an ORM or jOOQ, check out these tutorials.

Introduction to ORMs

Hibernate

JOOQ

Other ORMs

 

4. Efficient Use of Database Connections with Connection Pooling

(Back to Top)

When you want to perform an operation on a database, such as reading data, you need to connect to it. Creating a connection to a database only usually takes a few lines of code, but it’s quite a resource-intensive process and includes a few steps:

  • Creating a network session
  • Authenticating the user (that you’re a valid user on the system)
  • Checking user authorization (that you’re allowed to view that you are trying to view)

Rather than creating a new connection each time someone wants to connect to the database, and closing it when they are finished, the concept of connection pooling allows you to define a set of connections that are already available and ready. This is known as a “connection pool”.

When a user or client wants to access the database, they take one of the available connections from the connection pool and use it. Once the client is finished with it, they return it to the connection pool for others to use. If there are no free connections (if they are all being used by other clients), the system creates a new connection.

You can define an initial number of connections and a maximum number of connections. Developing a connection pool like this will ensure that your users or clients can access the database efficiently and improve the performance of your application.

 

5. Storing and Using Logic in the Database with Stored Procedures

(Back to Top)

Along with storing data and letting you access it, databases can also store code in the form of stored procedures. A stored procedure is a piece of code that executes a specific function, similar to a method in Java.

There are several advantages to using stored procedures, such as:

  • Business logic is close to the data that it uses
  • Business logic can be used by other applications that use the database, ensuring it is consistent.

However, there are some disadvantages:

  • Harder to debug if you’re a Java developer, as you may need a different tool and may not have the same features as the IDE you’re used to
  • Harder to manage with source control. It’s possible, but it just takes a slightly different approach.

Read some of the articles below on how to create and access stored procedures on a database with Java.

 

6. Tips for Writing Good SQL as a Java Developer

(Back to Top)

If you want to use SQL as a Java developer, then there are a few tips that can really help you out. These tips can improve the quality of your SQL and avoid performance issues:

  • Avoid SQL injection by using features such as Prepared Statements
  • Avoid using functions on columns in the WHERE clause unless they are necessary, as they may slow down your query
  • Consider setting up indexes set up on columns that are being joined or columns used in WHERE clauses. If you’re not sure how to do this or which indexes or columns are important, speak to your resident DBA or database developer.
  • Use connection pooling to improve the efficiency of server resources
  • Only select the columns you need, so avoid using SELECT *
  • If your query is slow, start by running an EXPLAIN PLAN to get the execution plan. This can help work out where the issues in the query are.
  • There are usually several ways to write a SELECT statement to get the data you need. If one method is slow or incorrect, try another method.

Here are some articles on how to improve performance with SQL and JDBC:

 

Conclusion

(Back to Top)

Accessing a database using Java is possible in several different ways. Each of them has their advantages and disadvantages. Whether you use JDBC with SQL, an ORM framework like Hibernate, or an abstraction like jOOQ, it’s important to write effective and efficient code.

Which is your preferred method of accessing a database using Java? Let me know in the comments below.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Get Your FREE PDF: 9 Ways to Improve your Database Skills