Samuel Tucker

Samuel Tucker

1549237772

How to join results of multiple tables in Spring JPA repository

I'm new to Spring and I'm unable to figure out how to join multiple tables to return some result. I tried to implement a small Library application as shown below.

My Entity Classes - Book, Customer, Bookings

Book.java - books available in the library

@Entity
@Table(name = "books")
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private int id;

@NotNull(message = "Book name cannot be null")
@Column(name = "book_name", columnDefinition = "VARCHAR(255)")
private String bookName;

@Column(name = "author", columnDefinition = "VARCHAR(255)")
private String author;

// getters and setters

public Book() {}

public Book(String bookName, String author) {
    this.bookName = bookName;
    this.author = author;
}

}

Customer.java - Customers registered in the library

@Entity
@Table(name = “customer”, uniqueConstraints = {@UniqueConstraint(columnNames = {“phone”})})
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = “id”, columnDefinition = “int”)
private int id;

@NotNull(message = "Customer name cannot be null")
@Column(name = "name", columnDefinition = "VARCHAR(255)")
private String name;

@Column(name = "phone", columnDefinition = "VARCHAR(15)")
private String phone;

@Column(name = "registered", columnDefinition = "DATETIME")
private String registered;

// getters and setters

public Customer() {}

public Customer(String name, String phone, String registered) {
    this.name = name;
    this.phone = phone;
    this.registered = registered;
}

}

Booking.java - All the bookings made by the customers

@Entity
@Table(name = “bookings”)
public class Booking {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = “id”, columnDefinition = “int”)
private int id;

@NotNull(message = "Book id cannot be null")
@Column(name = "book_id", columnDefinition = "int")
private int bookId;

@NotNull(message = "Customer id cannot be null")
@Column(name = "customer_id", columnDefinition = "int")
private int customerId;

@Column(name = "issue_date", columnDefinition = "DATETIME")
private String issueDate;

@Column(name = "return_date", columnDefinition = "DATETIME")
private String returnDate;

// getters and setters

public Booking() {}

public Booking(int bookId, int customerId, String issueDate) {
    this.bookId = bookId;
    this.customerId = customerId;
    this.issueDate = issueDate;
}

}

Now the table schemas for the respective entities are as follows:

books:
±----------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| book_name | varchar(255) | NO | | NULL | |
| author | varchar(255) | YES | | NULL | |
±----------±-------------±-----±----±--------±---------------+
id - primary key

customer:
±-----------±-------------±-----±----±------------------±------------------+
| Field | Type | Null | Key | Default | Extra |
±-----------±-------------±-----±----±------------------±------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| registered | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| phone | varchar(15) | YES | UNI | NULL | |
±-----------±-------------±-----±----±------------------±------------------+
id - primary key

bookings:
±------------±---------±-----±----±------------------±------------------+
| Field | Type | Null | Key | Default | Extra |
±------------±---------±-----±----±------------------±------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| book_id | int(11) | NO | MUL | NULL | |
| customer_id | int(11) | NO | MUL | NULL | |
| issue_date | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| return_date | datetime | YES | | NULL | |
±------------±---------±-----±----±------------------±------------------+
id - primary key
book_id - foreign key references books.id
customer_id - foreign key references customer.id

Now What I want to do is given some booking critieria like customer phone or author name etc., I want to return all the bookings related to that order. I’ll show a sample Booking api to explain.

Booking Controller:

@RestController
@RequestMapping(“/bookings”)
public class BookingController {
@Autowired
BookingService bookingService;

// some booking apis which return Booking objects

@GetMapping
public List<Booking> getAllBookingsBy(@RequestParam("phone") String phone,
                                     @RequestParam("authors") List<String> authors) {
    return bookingService.getAllBy(phone, authors);
}

}

Booking Service class:

@Service
public class BookService {
@Autowired
private BookRepository bookRepository;

// some booking service methods

// get all bookings booked by a customer with matching phone number and books written by a given list of authors
public List<Booking> getAllBy(String phone, List<String> authors) {
return bookingRepository.queryBy(phone, authors);

}
}

Booking Repository Class:

@Repository
public interface BookingRepository extends JpaRepository<Booking, Integer> {
// some booking repository methods

@Query(value = "SELECT * FROM bookings bs WHERE " +
        "EXISTS (SELECT 1 FROM customer c WHERE bs.customer_id = c.id AND c.phone = :phone) " +
        "AND EXISTS (SELECT 1 FROM books b WHERE b.id = bs.book_id AND b.author IN :authors)",
        nativeQuery = true)
List&lt;Booking&gt; queryBy(@Param("phone") String phone,
                        @Param("authors") List&lt;String&gt; authors);

}

Now hitting the shown booking controller 'll return a booking object which looks like this :

[
{
“id”: 3,
“book_id”: 5,
“customer_id”: 2,
“issue_date”: “2019-02-04 01:45:21”,
“return_date”: null
}
]

But I don’t want it like that, I want to return along with them the name of the customer for that booking and also the name of the book. So I want the booking objects returned by the controller to look like this:

[
{
“id”: 3,
“book_id”: 5,
“customer_id”: 2,
“issue_date”: “2019-02-04 01:45:21”,
“return_date”: null,
“customer_name”: “Cust 2”,
“book_name”: “Book_2_2”,
}
]

Can someone please help in doing this? I’m stuck as I’m unable to proceed from here.

#java #spring

What is GEEK

Buddha Community

Tubo Man

1550025192

What you do is wrong. You are returning Booking and you expect that it magicaly deserialize into an entity that contains join information like Book Name. But in your select query on the repository you have selected the Booking. The way things are at your implementation the Booking does not hold information about the Book.

First you need to separate what you will deserialize as JSON and what you will use as persistence layer towards your spring data.

1.Make a @OneToOne/@OneToMany relationship from Booking to Book as a start. 
2.Change your query to do eager fetching on the entity/collection you have mapped as Book.
3. Make a POJO and annotate it with JSON annotations the way you want it to be returned by the controller.
4. Map between your persistence object / Booking with hidrated collection on Book and your newly created POJO

Actualy if you map as OneToOne the default initialization becomes EAGER so your query becomes a bit unnessesary.

If we presume you have your mappings right in the persistent layer your query will look like this:

@Query(value = "SELECT * FROM bookings bs WHERE " +
            "bs.customer.phone = :phone) " +
            "AND  bs.book.author IN :authors)")

Here is your mapping documentation from Hibernate>http://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#associations

Samuel Tucker

Samuel Tucker

1549237772

How to join results of multiple tables in Spring JPA repository

I'm new to Spring and I'm unable to figure out how to join multiple tables to return some result. I tried to implement a small Library application as shown below.

My Entity Classes - Book, Customer, Bookings

Book.java - books available in the library

@Entity
@Table(name = "books")
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private int id;

@NotNull(message = "Book name cannot be null")
@Column(name = "book_name", columnDefinition = "VARCHAR(255)")
private String bookName;

@Column(name = "author", columnDefinition = "VARCHAR(255)")
private String author;

// getters and setters

public Book() {}

public Book(String bookName, String author) {
    this.bookName = bookName;
    this.author = author;
}

}

Customer.java - Customers registered in the library

@Entity
@Table(name = “customer”, uniqueConstraints = {@UniqueConstraint(columnNames = {“phone”})})
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = “id”, columnDefinition = “int”)
private int id;

@NotNull(message = "Customer name cannot be null")
@Column(name = "name", columnDefinition = "VARCHAR(255)")
private String name;

@Column(name = "phone", columnDefinition = "VARCHAR(15)")
private String phone;

@Column(name = "registered", columnDefinition = "DATETIME")
private String registered;

// getters and setters

public Customer() {}

public Customer(String name, String phone, String registered) {
    this.name = name;
    this.phone = phone;
    this.registered = registered;
}

}

Booking.java - All the bookings made by the customers

@Entity
@Table(name = “bookings”)
public class Booking {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = “id”, columnDefinition = “int”)
private int id;

@NotNull(message = "Book id cannot be null")
@Column(name = "book_id", columnDefinition = "int")
private int bookId;

@NotNull(message = "Customer id cannot be null")
@Column(name = "customer_id", columnDefinition = "int")
private int customerId;

@Column(name = "issue_date", columnDefinition = "DATETIME")
private String issueDate;

@Column(name = "return_date", columnDefinition = "DATETIME")
private String returnDate;

// getters and setters

public Booking() {}

public Booking(int bookId, int customerId, String issueDate) {
    this.bookId = bookId;
    this.customerId = customerId;
    this.issueDate = issueDate;
}

}

Now the table schemas for the respective entities are as follows:

books:
±----------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| book_name | varchar(255) | NO | | NULL | |
| author | varchar(255) | YES | | NULL | |
±----------±-------------±-----±----±--------±---------------+
id - primary key

customer:
±-----------±-------------±-----±----±------------------±------------------+
| Field | Type | Null | Key | Default | Extra |
±-----------±-------------±-----±----±------------------±------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| registered | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| phone | varchar(15) | YES | UNI | NULL | |
±-----------±-------------±-----±----±------------------±------------------+
id - primary key

bookings:
±------------±---------±-----±----±------------------±------------------+
| Field | Type | Null | Key | Default | Extra |
±------------±---------±-----±----±------------------±------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| book_id | int(11) | NO | MUL | NULL | |
| customer_id | int(11) | NO | MUL | NULL | |
| issue_date | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| return_date | datetime | YES | | NULL | |
±------------±---------±-----±----±------------------±------------------+
id - primary key
book_id - foreign key references books.id
customer_id - foreign key references customer.id

Now What I want to do is given some booking critieria like customer phone or author name etc., I want to return all the bookings related to that order. I’ll show a sample Booking api to explain.

Booking Controller:

@RestController
@RequestMapping(“/bookings”)
public class BookingController {
@Autowired
BookingService bookingService;

// some booking apis which return Booking objects

@GetMapping
public List&lt;Booking&gt; getAllBookingsBy(@RequestParam("phone") String phone,
                                     @RequestParam("authors") List&lt;String&gt; authors) {
    return bookingService.getAllBy(phone, authors);
}

}

Booking Service class:

@Service
public class BookService {
@Autowired
private BookRepository bookRepository;

// some booking service methods

// get all bookings booked by a customer with matching phone number and books written by a given list of authors
public List&lt;Booking&gt; getAllBy(String phone, List&lt;String&gt; authors) {
return bookingRepository.queryBy(phone, authors);

}
}

Booking Repository Class:

@Repository
public interface BookingRepository extends JpaRepository<Booking, Integer> {
// some booking repository methods

@Query(value = "SELECT * FROM bookings bs WHERE " +
        "EXISTS (SELECT 1 FROM customer c WHERE bs.customer_id = c.id AND c.phone = :phone) " +
        "AND EXISTS (SELECT 1 FROM books b WHERE b.id = bs.book_id AND b.author IN :authors)",
        nativeQuery = true)
List&lt;Booking&gt; queryBy(@Param("phone") String phone,
                        @Param("authors") List&lt;String&gt; authors);

}

Now hitting the shown booking controller 'll return a booking object which looks like this :

[
{
“id”: 3,
“book_id”: 5,
“customer_id”: 2,
“issue_date”: “2019-02-04 01:45:21”,
“return_date”: null
}
]

But I don’t want it like that, I want to return along with them the name of the customer for that booking and also the name of the book. So I want the booking objects returned by the controller to look like this:

[
{
“id”: 3,
“book_id”: 5,
“customer_id”: 2,
“issue_date”: “2019-02-04 01:45:21”,
“return_date”: null,
“customer_name”: “Cust 2”,
“book_name”: “Book_2_2”,
}
]

Can someone please help in doing this? I’m stuck as I’m unable to proceed from here.

#java #spring

Were  Joyce

Were Joyce

1620736200

Introduction to Spring Boot and JDBCTemplate: Refactoring to SpringData JPA

Introduction to Spring Boot and JDBCTemplate: Refactoring to SpringData JPA.

Refactoring is the process of modifying a software system without changing its desirable behavior. It was necessary to have an application integrated with the relational database using the Spring JDBC Template in the first parts. The Spring JDBC Template is a powerful tool that facilitates productivity. However, there is a way to simplify the code even further with Spring Data JPA. The purpose of this post is to refactor the project to use Spring Data JPA.

Spring Data JPA, part of the larger Spring Data family, makes it easy to implement JPA-based repositories easily. This module deals with enhanced support for JPA-based data access layers. It makes it easier to build Spring-powered applications that use data access technologies.

A safe code refactoring requires the use of tests to ensure that the compartment is not changed. The use of tests, fortunately, is adopted as a minimum standard, including several methodologies such as TDD that preach the creation of tests at the beginning of the development process.

#java #tutorial #spring #spring data #java tutorial #spring tutorial #spring data jpa

Brad  Hintz

Brad Hintz

1599302760

Apache Spark’s Join Algorithms

One of the most frequently used transformations in Apache Spark is Join operation. Joins in Apache Spark allow the developer to combine two or more data frames based on certain (sortable) keys. The syntax for writing a join operation is simple but some times what goes on behind the curtain is lost. Internally, for Joins Apache Spark proposes a couple of Algorithms and then chooses one of them. Not knowing what these internal algorithms are, and which one does spark choose might make a simple Join operation expensive.

While opting for a Join Algorithm, Spark looks at the size of the data frames involved. It considers the Join type and condition specified, and hint (if any) to finally decide upon the algorithm to use. In most of the cases, Sort Merge join and Shuffle Hash join are the two major power horses that drive the Spark SQL joins. But if spark finds the size of one of the data frames less than a certain threshold, Spark puts up Broadcast Join as it’s top contender.

Broadcast Hash Join

Looking at the Physical plan of a Join operation, a Broadcast Hash Join in Spark looks like this

Joins in Apache Spark: Broadcast Join

The above plan shows that the data frame from one of the branches broadcasts to every node containing the other data frame. In each node, Spark then performs the final Join operation. This is Spark’s per-node communication strategy.

Spark uses the Broadcast Hash Join when one of the data frame’s size is less than the threshold set in spark.sql.autoBroadcastJoinThreshold. It’s default value is 10 Mb, but can be changed using the following code

spark.conf.set("spark.sql.autoBroadcastJoinThreshold", 100 * 1024 * 1024)

This algorithm has the advantage that the other side of the join doesn’t require any shuffle. If this other side is very large, not doing the shuffle will bring notable speed-up as compared to other algorithms that would have to do the shuffle.

Broadcasting large datasets can also lead to timeout errors. A configuration spark.sql.broadcastTimeout sets the maximum time that a broadcast operation should take, past which the operation fails. The default timeout value is 5 minutes, but it can be set as follows:

spark.conf.set("spark.sql.broadcastTimeout", time_in_sec)

Sort Merge Join

If neither of the data frames can be broadcasted, then Spark resorts to Sort Merge Join. This algorithm uses the node-node communication strategy, where Spark shuffles the data across the cluster.

Sort Merge Join requires both sides of the join to have correct partitioning and order. Generally, this is ensured by** shuffle and sort** in both branches of the join as depicted below

#apache spark #scala #tech blogs #broadcast join #join opertaions #join optimization #joins in spark #shuffled hash join #sort merge join

Were  Joyce

Were Joyce

1636522080

Learn More About Spring Boot with Spring Data JPA

In the video below, we take a closer look at Spring Boot With Spring Data JPA [Book] | Spring Boot CRUD Example with RESTful APIs and JPA. Let's get started!

#spring-boot #spring #jpa #api #crud 

I Dev

1608437416

Spring Boot JPA + H2 example: Build a CRUD Rest APIs

In this tutorial, we’re gonna build a Spring Boot Rest CRUD API example with Maven that use Spring Data JPA to interact with H2 database. You’ll know:

  • How to configure Spring Data, JPA, Hibernate to work with Database
  • How to define Data Models and Repository interfaces
  • Way to create Spring Rest Controller to process HTTP requests
  • Way to use Spring Data JPA to interact with H2 Database

For more details, please visit:
https://bezkoder.com/spring-boot-jpa-h2-example/

Overview of Spring Boot JPA + H2 example

We will build a Spring Boot Rest Apis using Spring Data JPA with H2 Database for a Tutorial application in that:

  • Each Tutotial has id, title, description, published status.
  • Apis help to create, retrieve, update, delete Tutorials.
  • Apis also support custom finder methods such as find by published status or by title.

These are APIs that we need to provide:

  • POST /api/tutorials: create new Tutorial
  • GET /api/tutorials: retrieve all Tutorials
  • GET /api/tutorials/[id]: retrieve a Tutorial by :id
  • PUT /api/tutorials/[id]: update a Tutorial by :id
  • DELETE /api/tutorials/[id]: delete a Tutorial by :id
  • DELETE /api/tutorials: delete all Tutorials
  • GET /api/tutorials/published: find all published Tutorials
  • GET /api/tutorials?title=[keyword]: find all Tutorials which title contains keyword

– We make CRUD operations & finder methods with Spring Data JPA’s JpaRepository.
– The database will be H2 Database (in memory or on disk) by configuring project dependency & datasource.

Technology

  • Java 8
  • Spring Boot 2.4 (with Spring Web MVC, Spring Data JPA)
  • H2 Database
  • Maven 3.6.1

Project Structure

spring-boot-jpa-h2-database-example-crud-project-structure

Let me explain it briefly.

Tutorial data model class corresponds to entity and table tutorials.
TutorialRepository is an interface that extends JpaRepository for CRUD methods and custom finder methods. It will be autowired in TutorialController.
TutorialController is a RestController which has request mapping methods for RESTful requests such as: getAllTutorials, createTutorial, updateTutorial, deleteTutorial, findByPublished
– Configuration for Spring Datasource, JPA & Hibernate in application.properties.
pom.xml contains dependencies for Spring Boot and H2 Database.

Source Code

For more details, implementation and Github, please visit:
https://bezkoder.com/spring-boot-jpa-h2-example/

Using other databases:
Spring JPA + PostgreSQL
Spring JPA + MySQL
Spring Data + MongoDB

If you want to add Pagination to this Spring project, you can find the instruction at:
Spring Boot Pagination & Filter example | Spring JPA, Pageable

To sort/order by multiple fields:
Spring Data JPA Sort/Order by multiple Columns | Spring Boot

Handle Exception for this Rest APIs is necessary:
Spring Boot @ControllerAdvice & @ExceptionHandler example
@RestControllerAdvice example in Spring Boot

Or way to write Unit Test for the JPA Repository:
Spring Boot Unit Test for JPA Repositiory with @DataJpaTest

#java #spring #spring-boot #h2 #spring-framework #jpa