Database access in Spring.
Tags: java, programming, tutorial, beginners
Published: 2021-03-25 05:40:52

---
title: Database access in Spring.
published: true 
description: Get rid of complex sql queries in your Java code.
tags: Java, programming, tutorial, beginner
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o7f8biti4vsp2gpj5ovu.png
---

JPA is a standard for ORM. It is an API layer that maps Java objects to the database tables. All you need is to annotate your Domain object with @Entity. 

In Spring Boot, Spring Data JPA brings the goodness of Spring Data and JPA together. 

In the JDBC world we have to embed sql queries inside the codebase to access the database, JPA hides all that complexity by providing many CRUD related methods via JpaRepository.

*Let's take a look*

![carbon(2).png](https://cdn.hashnode.com/res/hashnode/image/upload/v1614642552926/vqae0Mb9C.png)

Above code is mapping User Object to User table in the database. User table consists two columns "first_name" and "last_name".  @Column annotation is defining the column inside the User table.

JPA supports many annotations e.g. @Table annotation can define the indexes on the table. Take a look at the sample code below.

![carbon(3).png](https://cdn.hashnode.com/res/hashnode/image/upload/v1614644104258/jBZ1KFZKp.png)

Above we are creating an index name “name_index” on the first_name column.

### Data access layer without JPA:

JDBC is the prominent method to access the database. JDBC requires one to write sql queries to communicate with the database, it makes use of Statement, ResultSet etc. to prepare a sql statement and gets the result out of database respectively.

__Let's take a look below__-:

```
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT first_name FROM Customers WHERE last_name = 'Doe'");
``` 
Here we create a statement object and call executeQuery on it with supplying the SQL query. ResultSet object will contain all the returned result from the query. We can now use an iterator to iterate over items inside result set.

Similarly an insert statement could look like this.

```
Statement stmt = connection.createStatement();
stmt.execute("INSERT INTO user (FIRST_NAME,LAST_NAME) VALUES (1,'John','Dow')");

``` 

###  Data access layer with JPA

Let's see how JPA does above two database operations.

Spring Data Jpa expose JpaRepository which is comprises on all the CRUD methods. Once we define a repository for the domain object we are ready to call these methods.

Let's create a repository interface for User.

![carbon(5).png](https://cdn.hashnode.com/res/hashnode/image/upload/v1614644818460/fmRbLKzye.png)

@Repository annotation tells Spring Boot to scan interface and make it available at startup. You'll notice that we haven't create any method inside our interface because we don't need to, atleast for the simple CRUD use cases. 
All those operations are supported by JpaRepository out of the box and we are extending it.

Now let's compare same select and insert operation of Jdbc here in Jpa.

Like Select if we have to retrieve all the objects we can do

```
User user = userRepository.findAll();
```

Like Insert if we have to insert a user we can do

```
userRepository.save(user);
```
Much simpler than the JDBC implementation. 

Let's take a look at another example. 

Finding all the users with the given first name. 

Use “findBy” to search on a particular column.

```
// find rows by the first name.
userRepository.findByFirstName(String firstName);
```
Similarly,

```
// find rows by the last name.
userRepository.findByLastName(String lastName);
```

I'll stop here for this post. In the next post I'll write how to handle more complex sql queries using JPA.

If you like to walk through video tutorial rather I have a Spring Boot playlist on YouTube 👉 [Spring Boot Tutorial Series](https://youtu.be/fiKYlmTdG2g)

I post daily about programming on different platforms with the intention of writing good tech content and keep them less spammy.

You can also find me 👇

 [Youtube](https://youtu.be/fiKYlmTdG2g)  |  [Twitter](https://twitter.com/S_AWDESH)  |  [Instagram](https://instagram.com/awdeshcodes) 

Let’s learn together 💪💪

Happy Coding 💻