JDBC in Java, Hibernate, and ORMs: The Ultimate Resource

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.

Table of Contents

 

1. Setting Up Simple Connections with JDBC in Java

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.

• Java Tutorial (Tutorials Point)
• Java tutorial: Learn Java Programming with examples (Beginner’s Book)
• Java Tutorial (Guru99)
• Oracle’s Java Tutorial
• LearnJavaOnline.org
• Codecademy’s Java Tutorial
• JavaTPoint Java Tutorial
• Java Tutorial (Baeldung)
• CodeGym

Setting Up a Connection

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

• JavaTPoint: Connect to MySQL
• Oracle’s JDBC Tutorial
• Establishing a JDBC Connection in Java (Geeks for Geeks)
• Connection string examples (Alvin Alexander)
• JDBC: An example to connect MS Access database in Java 8
• Java JDBC Tutorials (HowToDoInJava)
• JDBC MySQL Connection

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:

• Processing SQL statements
• MySQL and Java JDBC (includes code to connect to a database, create, and run SQL)
• Java JDBC Select Examples
• Using JDBC PreparedStatements
• JDBC PreparedStatement
• An explanation and example of transactions
• StackOverflow question about starting a transaction
• A simple example that shows insert, update, delete, and select
• JDBC Tutorial: SQL Insert, Select, Update, and Delete Examples

 

2. Learning the JPA Specification (Java Persistence API)

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.

• Oracle’s introduction to JPA
• JPA Tutorial
• What’s the difference between JPA, Hibernate, and EclipseLink?

 

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

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:

• Hibernate
• EclipseLink
• MyBatis
• SimpleORM
• Toplink

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

• StackOverflow – Advantages of ORM
• What are the reasons to use an ORM? (Quora)
• ORM vs SQL
• Why ORM shouldn’t be your best bet
• Should you use Hibernate for your next project?
• ORM Is an Offensive Anti-Pattern
• Martin Fowler – ORM Hate
• Ten advantages of an ORM (Object Relational Mapper)
• StackOverflow advantages and disadvantages of an ORM
• Why Should You Use an ORM?
• “What Java ORM do You Prefer, and Why?” – SQL of Course!

Hibernate

• 5 Reasons to use JPA/Hibernate
• Official Hibernate site
• Getting Started With Hibernate

JOOQ

• jOOQ Official Site
• Getting Started with jOOQ
• Java Object Oriented Querying (jOOQ) article on what it is and how to get started
• Introduction to jOOQ with Spring
• jOOQ Tips: Implementing a Read-Only One-to-Many Relationship
• jOOQ – a short guide

Other ORMs

• EclipseLink – Tutorial
• Introduction to EclipseLink
• Java Persistence (JPA) Tutorial With EclipseLink
• A Guide to EclipseLink with Spring
• Mybatis – getting started
• Quick Guide to MyBatis
• MyBatis Tutorial
• Hibernate vs iBatis (StackOverflow)
• Hibernate vs MyBatis (StackExchange)
• TopLink: Best Object-Relational Solution For Oracle DB
• When to use Hibernate/JPA/Toplink? (StackOverflow)
• TopLink summary by OraFAQ

 

4. Efficient Use of Database Connections with Connection Pooling

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.

• Connection pool (Wikipedia)
• What is database pooling? (StackOverflow)
• Database Connection Pooling with Java
• A Simple Guide to Connection Pooling in Java
• How to establish a connection pool in JDBC?
• Understanding JDBC Connection Pooling

 

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

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.

• How to call a stored procedure in JDBC (StackOverflow)
• Developing Java Stored Procedures
• Using spwrap to Simplify Calling Stored Procedures From Java
• Using JDBC CallableStatements to Execute Stored Procedures
• JDBC – Stored Procedure (Tutorials Point)
• When should you use java stored procedures with an Oracle database … what are the drawbacks? (StackOverflow)

 

6. Tips for Writing Good SQL as a Java Developer

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:

• JDBC performance tips
• SQL Tricks And Tuning posts on jOOQ
• JDBC Coding Tips
• 6 Simple Performance Tips for SQL SELECT Statements
• 10 SQL tips to speed up your database
• 5 Tips for Managing Long SQL Queries
• The Difference Between a JDBC Statement and Prepared Statement

 

Conclusion

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.