{{announcement.body}}
{{announcement.title}}

Evolution of Database Connectivity in Java

DZone 's Guide to

Evolution of Database Connectivity in Java

Explore the evolution of database connectivity in Java and look at different solutions.

· Database Zone ·
Free Resource

Image title

Let’s take a look at database connectivity in Java.

Today, there are many alternatives you can use when writing database applications with Java. Certainly, JPA is one of the most-used of these alternatives. You can find many articles and tutorials about JPA on the internet. My goal in this article is not to teach you JPA, but to take a look at the world before JPA so that you can understand what problem JPA is developed for.

For this reason, to make things clear, let's define a simple problem and take a look at the probable solutions to this problem. Suppose that we have an application that we use to manage the list of all the books in our library. For this, we have a table named "Books" in our database and perform various CRUD (Create, Read, Update, Delete) operations on this table using our application.

You may also like: [Video] What Is Java Database Connectivity (JDBC)

The first solution below is a simple Java class using JDBC, Java's basic database connection model. As you might notice at first glance, a very long code has been written even for a simple update and a simple query operation.

package com.lets.learn.jdbc;

//STEP 1 --> import libraries
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCExample {
// Driver
     static final String dbURL = "jdbc:oracle:thin:@localhost:1521:testDB"; 

    // Connection URL
     static final String jdbcDriver = "oracle.jdbc.driver.OracleDriver";

 // Database credentials
 static final String userName = "testDbUser";
 static final String password = "password1";

    public static void main(String[] args) {
    Book book = new Book(1, "J.K. ROWLING", "Harry Potter");
    updateBook(book);
    Book myBook = queryBook(1);
    }

    private static void updateBook(Book book) {
    // Define variables
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
         // Step 2 --> Load and register the driver
Class.forName(jdbcDriver);

// Step 3 --> Create connection
connection = DriverManager.getConnection(dbURL, userName, password); 

// Step 4 --> Create statement
statement = connection.prepareStatement("UPDATE BOOKS SET AUTHOR=?,TITLE=? WHERE ID=?");
statement.setString(1, book.getAuthor());
statement.setString(2, book.getTitle());
statement.setInt(3, book.getId());

// Step 5 --> Execute query
// preparedStatement'a çevir, parametrelerin nasıl set edildiğini göster
resultSet = statement.executeQuery();

// Step 6 --> Retrieve data
// noop here

// Step 7: Clean-up the environment
resultSet.close();
statement.close();
connection.close();

        } catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (ClassNotFoundException e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// use finally block to close resources
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException se) {
se.printStackTrace();
} 
} 
    }

    private static Book queryBook(int id) {
    // Define variables
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        Book book = null;
        try {
         // Step 2 --> Load and register the driver
Class.forName(jdbcDriver);

// Step 3 --> Create connection
connection = DriverManager.getConnection(dbURL, userName, password); 

// Step 4 --> Create statement
statement = connection.prepareStatement("SELECT * FROM BOOKS WHERE ID=?");
statement.setInt(1, id);

// Step 5 --> Execute query
resultSet = statement.executeQuery();

// Step 6 --> Retrieve data

if (resultSet.next()) {
// populate a java object
book = new Book();
book.setId(resultSet.getInt("ID"));
                book.setTitle(resultSet.getString("TITLE"));
                book.setAuthor(resultSet.getString("AUTHOR"));
            }

// Step 7: Clean-up the environment
resultSet.close();
statement.close();
connection.close();

        } catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (ClassNotFoundException e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// use finally block to close resources
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException se) {
se.printStackTrace();
} 
} 
        return book;
    }

}

So, what does this code snippet do?

  • Creates a database connection
  • Updates or queries the data on the database
  • Manages the life cycles of all the objects used for database connectivity
  • Has Exception handling for the exceptions that may occur when creating or closing a connection

As you can see, writing a program with JDBC involves some routine steps that you don't want to do over and over again in every CRUD operation. The errors that could be made in connection management with these steps, which senior developers might remember very well, could sometimes cause Connection Pool problems that could cause the entire application to crash. For this reason, the blocks of code that create and close the connection are defined once in their own functions, and then, these functions are called to avoid duplication by calling these functions where necessary.

In addition to all this, there is one more thing we do in the code above. It also makes a continuous conversion between our data model in Java and our data model in the database. This conversion becomes compulsory both when reading and writing data. When querying data, we use various parameters in our query, the values of these parameters coming from other Java objects in our program.

We read these values from related Java objects and place them in the query. After running the query, we read the values from the result set and write them back to the Java objects. The solution to this problem does not come as easily as before. Here comes Spring JDBC to our rescue.

With thousands of users around the world, Spring offers a nice solution to this problem with Spring Data JDBC. In the following example, you see the solution to the same problem with Spring Data JDBC.

package com.lets.learn.springjdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class SpringJDBCExample {
// Driver
static final String dbURL = "jdbc:oracle:thin:@localhost:1521:testDB";

// Connection URL
static final String jdbcDriver = "oracle.jdbc.driver.OracleDriver";

// Database credentials
static final String userName = "testDbUser";
static final String password = "password1";

private static DriverManagerDataSource dataSource = new DriverManagerDataSource();
private static JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());

public static void main(String[] args) throws Exception {

Book book = new Book(1, "J.K. ROWLING", "Harry Potter");

updateBook(book);
Book myBook = queryBook(1);

}

public static void updateBook(Book book) {
jdbcTemplate.update("UPDATE BOOKS SET AUTHOR=?,TITLE=? WHERE ID=?", book.getAuthor(), book.getTitle(), book.getId());
}

public static Book queryBook(int id) {
return jdbcTemplate.queryForObject(
    "SELECT * FROM BOOKS WHERE ID = ?", new Object[] { id }, new BookRowMapper());
}


    private static DriverManagerDataSource getDataSource() {

        dataSource.setDriverClassName(jdbcDriver);
        dataSource.setUrl(dbURL);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);

        return dataSource;
    }

}


package com.lets.learn.springjdbc;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class BookRowMapper implements RowMapper<Book> {
   @Override
    public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
        Book book = new Book();

        book.setId(rs.getInt("ID"));
        book.setTitle(rs.getString("TITLE"));
        book.setAuthor(rs.getString("AUTHOR"));

        return book;
    }
}

When you looked at the code, I think you probably noticed a few differences immediately. What are these?

  • The task of creating a connection before each operation and closing the connection after the operation is now taken from the programmer and transferred to the Spring Context. Code duplication is minimized
  • The objects in the result set are not manually converted to Java objects, but the RowMapper structure is used instead. In this way, how to make the conversion is defined once and the code duplication is avoided.

But still, there is an unresolved problem. Our queries are still in Java classes. Finding and updating these queries when necessary — for example, when a major change in our database model occurs — is a huge cost for large-scale applications. That's where JPA comes in.

Let's take a look at the solution of the same problem with JPA.

package com.lets.learn.jpa;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.lets.learn.jpa.entity.Book;
import com.lets.learn.jpa.repository.BookJpaRepository;

@SpringBootApplication
public class JpaDemoApplication implements CommandLineRunner {

@Autowired
BookJpaRepository repository;

public static void main(String[] args) {
SpringApplication.run(JpaDemoApplication.class, args);
}

@Override
public void run(String... args) throws Exception {

Book firstBook =repository.findById(10001);

// insert new book 
repository.insert(new Book(1 ,"Hobbit", "J.R.R Tolkien"));

// update a book 
repository.update(new Book(10003, "A Tale Of Two Cities", "Charles Dickens"));

// delete a book
repository.deleteById(10002);

//get all books
repository.findAll();
}
}


package com.lets.learn.jpa.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.transaction.Transactional;

import org.springframework.stereotype.Repository;

import com.lets.learn.jpa.entity.Book;


@Repository
@Transactional
public class BookJpaRepository {

// connect to the database
@PersistenceContext
EntityManager entityManager;

public List<Book> findAll() {
TypedQuery<Book> namedQuery = entityManager.createNamedQuery("find_all_books", Book.class);
return namedQuery.getResultList();
}

public Book findById(int id) {
return entityManager.find(Book.class, id);// JPA
}

public Book update(Book book) {
return entityManager.merge(book);
}

public Book insert(Book book) {
return entityManager.merge(book);
}

public void deleteById(int id) {
Book book = findById(id);
entityManager.remove(book);
}

}


package com.lets.learn.jpa.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedQuery;

@Entity
@NamedQuery(name="find_all_books", query="select b from Book b")
public class Book {

@Id
@GeneratedValue
private int id;

private String title;
private String author;

public Book() {

}

public Book(int id, String title, String author) {
super();
this.id = id;
this.title = title;
this.author = author;

}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getTitle() {
return title;
}

public void setTitle(String title) {
this.title = title;
}

public String getAuthor() {
return author;
}

public void setAuthor(String author) {
this.author = author;
}
}

As you can see in the example above, JPA:

  • Allows us to write simpler, more understandable codes for CRUD operations
  • Prevents us from reinventing the wheel with the structures it already provides for frequently used operations
  • Eliminates the need for a continuous conversion between Java objects and database tables
  • When the model in the database changes, it makes it easier to reflect the change to the Java layer

I hope this article has helped you understand the way we've gone so far towards developing database applications with Java.


Further Reading

The ABCs of JDBC, Part 1 — Getting Started With JDBC

Spring Tips: JDBC [Video]

Topics:
java ,spring jdbc ,jdbc ,database connection ,jpa ,orm ,database ,tutorial ,database connectivity

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}