Ambert Lency

Ambert Lency

1567155412

Understand to Spring Boot: MySQL and JPA, Hibernate

Before we start, this post is mostly aimed at Java developers, specifically Java developers who use the Spring umbrella of frameworks for development. If you are not a Java developer, you can still read on, chances are you haven't found the love of your life - in terms of languages - yet.

Also, I apologise in advance for the markup mishaps that will pop up here and there in various code sections. You could check out the original blog post here Original Post if you have trouble understanding/copy pasting the code.

First off, what really is Spring?

If you are a java developer, you've probably stumbled upon the term 'Spring' at some point in your journey, and possibly fell face first at either its learning curve, or its complexity. One thing most developers fail to understand - I also struggled with this at first - is that Spring represents a collection of frameworks that are tailored to meet specific development needs. As an example, if you're a java web developer, Spring provides the Web Servlet Framework for web development where Spring MVC (Included in this framework) is built on top of the Servlet API. Therefore, you need not learn all the frameworks that Spring provides, but rather the frameworks that fit your specific use case. Yeah, that's a shortcut, and yes, you're welcome.

If you've never heard about Spring before, Spring is an Inversion of Control and Dependency injection framework. This are fairly big terms but this comprehensive post will help you understand the meanings of these two concepts: IoC and Dependency Injection

Now onto Spring Boot

If you have used Spring MVC before, you've definitely have had to wrestle with Spring MVC's pre-configurations like Setting up the Dispatcher Servlet etc. etc. before you were able to get the framework up and running. This is where Spring Boot comes in. Spring Boot is an auto-configuration tool for setting up your Spring-powered applications. You can now put away those boxing gloves cause you might not need to wrestle with Spring Boot.

To help you understand Spring Boot further, and shine a light on why you should be using it if you already aren't, we'll build a simple Netflix API that allows client devices to register themselves, suggest movies and query movies.

Let us begin

Step 1: Setting up Spring Boot on your application.

Spring offers a project initializer, Spring Initialzr that allows you to select your project specifications and download an already configured Spring Boot project as a zip file or a maven build file. You could skip to step 2 if you have done this.

If you're a more of a hands on type of person who enjoys understanding what's happening under the hood, you can continue with this step.

Folder Structure.

Create a new Java project with you favourite IDE and configure your folder structure to mimic the following design:

└── src
    └── main
        └── controllers
        └── models
        └── repositories
        └── resources
            └── templates
                └── error.html
            └── application.properties
        └── Application.java

contollers - This folder will contain the controllers we define for this project

repositories - This folder will contain the repositories we'll define for our models that will be used to fetch data from the database.

resources - this folder will contain our project resources. The templatesfolder contains our template files that will be rendered by Spring. You can include other folders like static which will be used to server static content like javascript and css files.

Maven dependencies

Spring Boot allows us to include in our pom.xml file all the Spring dependencies that we'll use in our project. Copy paste the following dependencies, together with the Spring Boot Maven Plugin to your pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
&lt;groupId&gt;org.springframework&lt;/groupId&gt;
&lt;artifactId&gt;gs-spring-boot&lt;/artifactId&gt;
&lt;version&gt;0.1.0&lt;/version&gt;

&lt;parent&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-parent&lt;/artifactId&gt;
    &lt;version&gt;2.1.6.RELEASE&lt;/version&gt;
&lt;/parent&gt;

&lt;dependencies&gt;
    &lt;!--Spring dependencies--&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-web&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-thymeleaf&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-devtools&lt;/artifactId&gt;
    &lt;optional&gt;true&lt;/optional&gt;
&lt;/dependency&gt;

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!–Spring JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
</dependencies>

&lt;properties&gt;
    &lt;java.version&gt;1.8&lt;/java.version&gt;
&lt;/properties&gt;


&lt;build&gt;
    &lt;plugins&gt;
        &lt;plugin&gt;
            &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
            &lt;artifactId&gt;spring-boot-maven-plugin&lt;/artifactId&gt;
        &lt;/plugin&gt;
    &lt;/plugins&gt;
&lt;/build&gt;

</project>

Our dependencies overview:

1. spring-boot-starter-web - When building web applications using java, we often need other external dependencies that we include in our pom.xml like tomcat and Spring MVC. What spring-boot-starter-web does is add all these dependencies through one single dependency.

2. spring-boot-starter-thymeleaf - If you’ve never used thymeleaf before, thymeleaf is a templating engine for processing and creating HTML, XML, JavaScript, CSS, and text whose template files retain the .html extension and therefore a better alternative to JSPs (Java Server Pages). What this basically means is that you can run thymeleaf template files like normal web pages without a backend server for template processing as in the case of JSPs.

3. spring-boot-devtools - These tools grease your gears of development therefore making the overall development process more bearable. To learn more about what these tools offer, you can check out this link: spring-boot-devtools

4. mysql-connector-java - These is the MySQL JDBC implementation that we’ll use to make connections to our MySQL database.

5. spring-boot-starter-data-jpa - Most if not all web applications need some form of persistence, which in java cases, is often JPA (Java Persistence API). If spring-boot-data-jpa is in the classpath, Spring boot will automatically configure our data-source through reading our database configuration from the application.properties file that we will configure next.

Note that we’ve set our java version to 1.8 since JDK 11 does not offer a lot of things out of the box and therefore you may run into errors like: springboot: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.entity.SingleTableEntityPersister

Application.properties file

Spring boot automatically reads configuration settings from this file and configures our spring boot environment accordingly. We’ll configure our database here and also at the same time disable Spring boot’s whitelabel error page which we’ll replace with our own custom error page. You can copy paste all this into your own application.properties file if you do not intended to make any changes.

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/netflix?useSSL=false
spring.datasource.username = netflix
spring.datasource.password = netflix

Hibernate Properties

The SQL dialect makes Hibernate generate better SQL for the chosen database

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

Hibernate ddl auto (create, create-drop, validate, update)

spring.jpa.hibernate.ddl-auto = update

#Disabling the whitelabel error page
server.error.whitelabel.enabled=false

In the above application.properties file, We’ve configured our database, username and password to netflix . You can configure this if you want to. Spring JPA automatically uses the Hibernate implementation of JPA. 

We’ve set spring.jpa.hibernate.ddl-auto to update which will ensure that any changes we make to our models will be reflected in our Database, which also includes creating a new model. Please note that this option is only suitable for development environments rather than production environments. For more information, you can check this link: Database Initialization.

We’ve also set server.error.whitelabel.enabled to false to disable Spring boot’s whitelabel error pages which we’ll replace with our own custom error page.

Configuring our Application.java file

This file will contain the main method which we’ll use to ignite our Spring Application with. Copy paste the following to your Application.java file:

package main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableJpaRepositories(basePackages=“main.repositories”)
@EnableTransactionManagement
@EnableJpaAuditing
@EntityScan(basePackages={“main.entities”,“main.models”})
public class Application {

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

}

@SpringBootApplication is a combination of the following more specific spring annotations -

1. @Configuration : Any class annotated with @Configuration annotation is bootstrapped by Spring and is also considered as a source of other bean definitions.

2. @EnableAutoConfiguration : This annotation tells Spring to automatically configure your application based on the dependencies that you have added in the pom.xml file. For example, If spring-data-jpa or spring-jdbc is in the classpath, then it automatically tries to configure a DataSource by reading the database properties from application.properties file.

3. @ComponentScan : It tells Spring to scan and bootstrap other components defined in the current package (main) and all the sub-packages.

@EnableJpaAuditing is used to support the automatic filling of fields that we’ll annotate with @CreatedDate.

@EnableJpaRepositories tells Spring where to find our defined Repositories, since we’ll not be using the @Repository annotation.


Step 2. Coding our Controllers.

We’ll create only 3 Contollers namely: CustomErrorController that we’ll use to format and serve our custom error page, MoviesController that will perform movie related functions and UsersContoller that will perform user related functions.

CustomErrorController

In this controller, we’ll register a route error that will be mapped to our renderErrorPage method. Therefore all requests made through the errorroute will be recieved by our method.

Note that here we’ll use the @Controller annotation since we’d like to return a view rather than plain text and therefore our method returning a string will return the name of the view. To return plain text rather than views, use the @RestController annotation.

We will also format our error messages to make them more user friendly when we display them on our error page.

We’ve also implemented the ErrorController interface and overridden the getErrorPath() method which will automatically be invoked when Spring encounters an error.

@Controller
public class CustomErrorController implements ErrorController {

@RequestMapping(value = "error",produces = "application/json;charset=UTF-8")
public String renderErrorPage(HttpServletRequest request, Model model) {
     String errorMsg = "";
    Object status = request.getAttribute(RequestDispatcher.ERROR_STATUS_CODE);
    int httpErrorCode = 404;
    if(status != null){
        httpErrorCode = Integer.valueOf(status.toString());
    }
    switch (httpErrorCode) {
        case 400: {
            errorMsg = "Http Error Code: 400. Bad Request";
            break;
        }
        case 401: {
            errorMsg = "Http Error Code: 401. Unauthorized";
            break;
        }
        case 404: {
            errorMsg = "Http Error Code: 404. Resource not found";
            break;
        }
        case 500: {
            errorMsg = "Http Error Code: 500. Internal Server Error";
            break;
        }
    }
    model.addAttribute("error",errorMsg);
    return "error";
}

@Override
public String getErrorPath() {
    return "/error";
}

}

Movies Controller

As we have stated earlier, this Controller will store functionalities related to our movies.

Since we are creating an api, we’ll map api requests to url patterns that start with /api. Therefore, we add a @RequestMapping annotation on top of the class, rather than the method so that every request url we map on our methods will be appended to /api.

@RestController
@RequestMapping(value = “/api”,produces = “application/json;charset=UTF-8”) //All our api request URLs will start with /api and will return Json
public class MoviesController {

private MoviesRepository moviesRepository;
private CategoriesRepository categoriesRepository;
private UserRepository userRepository;

@Autowired
public MoviesController(MoviesRepository moviesRepository, CategoriesRepository categoriesRepository, UserRepository userRepository){
    this.moviesRepository = moviesRepository;
    this.categoriesRepository = categoriesRepository;
    this.userRepository = userRepository;
}

//Suggest A movie
@GetMapping(value = "/suggestMovie")
public String suggestMovie(@RequestParam(name = "category_id") Long categoryId,@RequestParam(name = "name")String name
,@RequestParam(name = "suggested_by")Long suggestedBy){
    //Movies added through this API route are automatically marked as suggested.
    String movieType = Movies.MovieType.SUGGESTED.getMovieType();
    Movies movies = new Movies();

    //Provided category id should be in our categories table.
    if(categoriesRepository.findById(categoryId).isPresent()){

        if(userRepository.findById(suggestedBy).isPresent()){
            movies.setCategoryId(categoryId);
            movies.setName(name);
            movies.setType(movieType);
            movies.setSuggestedBy(suggestedBy);
            return moviesRepository.save(movies).toString();
        } else {
            return "{'error':'The specified user id does not exist.'}";
        }

    } else {
        return "{'error':'The specified category id does not exist.'}";
    }



}

//delete a suggested movie
@GetMapping(value = "/deleteMovie")
public String deleteMovie(@RequestParam(name = "movie_id") Long movieId,@RequestParam(name = "user_id")Long userId) {
    if(userRepository.findById(userId).isPresent()){
        Optional&lt;Movies&gt; movies = moviesRepository.findById(movieId);
        if(movies.isPresent()){
            List&lt;Movies&gt; movie = moviesRepository.findBySuggestedByEqualsAndIdEquals(userId,movieId);
            if(movie.size()&gt;0){
                moviesRepository.delete(movie.get(0));
                return movie.toString();
            } else {
                return generateErrorResponse("The user specified cannot delete this movie");
            }


        } else {
            return  generateErrorResponse("Specified movie id does not exist");
        }

    } else {
        return generateErrorResponse("Specified user id does not exist");
    }
}

//update a suggested movie. Supports only updating of the movie name or category.
@GetMapping(value = "/updateMovie/{movie_id}")
public String updateMovie(@PathVariable(name = "movie_id") Long movieId,@RequestParam(name = "user_id")Long userId,
                          @RequestParam(name = "movie_name",required = false)String movieName, @RequestParam(name = "movie_category",required = false) Long movieCategory) {
    List&lt;Movies&gt; movie = moviesRepository.findBySuggestedByEqualsAndIdEquals(userId,movieId);
    if(!(movie.size()&gt;0)){
        return generateErrorResponse("The user specified cannot update this movie");
    }

    if(moviesRepository.findById(movieId).isPresent()){
        Movies movies = moviesRepository.findById(movieId).get();
        if(movieName != null &amp;&amp; !movieName.isEmpty()){
            movies.setName(movieName);
        }
        if(movieCategory != null &amp;&amp; categoriesRepository.findById(movieCategory).isPresent()){
            movies.setCategoryId(movieCategory);
        }

        return moviesRepository.save(movies).toString();
    } else {
        return generateErrorResponse("The specified movie id does not exist");
    }
}

//query available movies
@GetMapping(value = "/queryMovies/{categoryId}")
public String queryMovies(@PathVariable Long categoryId,@RequestParam(name = "type") String type){
    JsonObjectBuilder jsonResponse = Json.createObjectBuilder();
    JsonObjectBuilder temp = Json.createObjectBuilder();
    int count = 0;
    for(Movies movie:moviesRepository.findAllByCategoryIdEqualsAndTypeEquals(categoryId,type)){
        temp.add("id",movie.getId());
        temp.add("name",movie.getName());
        temp.add("type",movie.getType());
        temp.add("category_id",movie.getCategoryId());
        temp.add("created_at",movie.getCreatedAt().toString());
        jsonResponse.add(count + "",temp);
        temp = Json.createObjectBuilder();
        count++;
    }

    return jsonResponse.build().toString();
}

private String generateErrorResponse(String message){
    return "{\"error\":\"" + message + "\"";
}

//add categories
@GetMapping(value = "/addCategories")
public String addCategories(@RequestParam(name = "name") String name){
    Categories categories = new Categories();
    categories.setName(name);

    return categoriesRepository.save(categories).toString();
}

}

In this Class, you may have noticed annotations that you might have not seen before. Let’s go through them quickly:

1. @Autowired - As the annotation itself suggests, this annotation automatically injects an implementation of the movies, users and categories repository interface which we assign the the field variables we have declared. As we mentioned earlier, you need a repository to be able to access database contents, which explains these three repositories. I’ll explain this further when we reach the repositories section.

2. @GetMapping - This annotation is the same as @RequestMapping except that it only maps get requests to the specified url.

3. @RequestParam - This annotation automatically injects the specified query parameter name to this variable.

4. @PathVariable` - This annotation automatically injects the path value - enclosed in curly braces - to this variable.

Users Controller

This controller will contain functionalities related to users. In this case, we’ll define only a single method that will be responsible for creating a user.

@RestController

@RequestMapping(value = “/api”,produces = “application/json;charset=UTF-8”) //All our api request URLs will start with /api and return Json

public class UsersController {

private UserRepository userRepository;

@Autowired
public UsersController(UserRepository userRepository){
this.userRepository = userRepository;
}

@GetMapping(path = “/addUser”)
public String addUser(@RequestParam(name = “id”)Long id, @RequestParam(name=“name”) String name) {
Users users = new Users();
users.setId(id);
users.setName(name);

users = userRepository.save(users);
return users.toString();

}
}

Our user IDs in this case will not be auto-generated but instead, we’ll provide users with an option to define their own IDs.

Step 3. Defining our Repositories

Repositories will be used by our models to query data from the Database. spring-jpa comes with a JpaRepository interface that defines all CRUD operations that we can perform on an Entity. We’ll use the CrudRepositoryimplementation of JpaRespository as it offers many CRUD operations out of the box through methods like findAll(), save() etc. At the same time, CrudRepository automatically generated for us dynamic queries based on method names as we’ll see in the following example.

We’ll define three repositories for our three entities: CategoriesRepository , MoviesRepository and UsersRepository, which will all be interfaces extending CrudRepository.

CategoriesRepository

public interface CategoriesRepository extends

CrudRepository<Categories,Long> {

}

MoviesRepository

public interface MoviesRepository extends CrudRepository {

List<Movies> findAllByCategoryIdEqualsAndTypeEquals(Long categoryId,String type);

List<Movies> findBySuggestedByEqualsAndIdEquals(Long suggestedBy,Long movieId);

In this repository, notice the abstract methods we have defined. Extending CrudRepository will automatically compel Spring to create an implementation of these methods automatically at run-time just from the definition of the method name. To add Custom methods, we can add them in the following ways:

  1. We can start our query method names with find…Byread…Byquery…Bycount…By, and get…By. Before By we can add expression such as Distinct . After By we need to add property names of our entity.
  2. To get data on the basis of more than one property we can concatenate property names using And and Or while creating method names.
  3. If we want to use completely custom name for our method, we can use @Query annotation to write query.

UsersRepository

@Repository

public interface UserRepository extends CrudRepository {

Final Step: Defining our models.

The models (Entities) that we define will be used to store our table structures as will be defined in the database. We will therefore have three models for our three tables: Categories , Movies and Users.

Categories Model

@Entity

@Table(name = “categories”)

public class Categories {

@Id
@GeneratedValue
private Long id;

@NotBlank
private String name;

public Long getId() {
return id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

@Override
public String toString() {
JsonObjectBuilder builder = Json.createObjectBuilder();

//serialize to Json only if the data was persisted.
if(!Objects.isNull(id)){
    builder.add("id",id);
}
if(!Objects.isNull(name)){
    builder.add("name",name);
}

return builder.build().toString();

}

}

`

An entity is a plain old Java object (POJO) class that is mapped to the database and configured for usage through JPA using annotations and/or XML.

Note that we’ve included a @Table annotation to explicitly define the name of our table. The @Id annotation automatically declares the created field as a primary key for our table in our database. At the same time, the @GeneratedValue annotation will automatically generate a value and store it in the database during saving of a record, pretty much like an auto-increment field. The @NotBlank annotation will automatically validate values that will be inserted into the name variable we defined and ensure that this field is not blank.

We’ve also defined our own toString method (overriding the superclass’s toString method) that will convert our model to a Json string that we return as a response in our controllers.

Movies Model

@Entity

@Table(name = “movies”)

@EntityListeners(AuditingEntityListener.class)

@JsonIgnoreProperties(value = {“createdAt”},allowGetters = true)

public class Movies implements Serializable {

@Id
@GeneratedValue
private Long id;

private Long categoryId;

@NotBlank
private String type;

@NotBlank
private String name;

private Long suggestedBy;

@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
@CreatedDate
private Date createdAt; //Stores the date at which a user was created.

@PrePersist
public void prePersist(){
createdAt = new Date();
}

public Long getId() {
return id;
}

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

public Long getCategoryId() {
return categoryId;
}

public void setCategoryId(Long categoryId) {
this.categoryId = categoryId;
}

public String getType() {
return type;
}

public void setType(String type) {
this.type = type;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Date getCreatedAt() {
return createdAt;
}

@Override
public String toString() {
JsonObjectBuilder builder = Json.createObjectBuilder();

//serialize to Json only if the data was persisted.
if(!Objects.isNull(id)){
    builder.add("id",id);
}
if(!Objects.isNull(name)){
    builder.add("name",name);
}

if(!Objects.isNull(categoryId)){
    builder.add("category_id",categoryId);
}

if(!Objects.isNull(createdAt)) {
    builder.add("created_at",createdAt.toString());
}
return builder.build().toString();

}

public Long getSuggestedBy() {
return suggestedBy;
}

public void setSuggestedBy(Long suggestedBy) {
this.suggestedBy = suggestedBy;
}

public enum MovieType{
SUGGESTED(“suggested”),ORIGINAL(“original”);

private String movieType;

 MovieType(String movieType){
    this.movieType = movieType;
}

public String getMovieType() {
    return movieType;
}

}

In this model, note the annotations below:

1. @EntityListeners(AuditingEntityListener.class) - This will attach an entity listener to our model class that will automatically fill the fields we’ve annotated with @CreatedAt.

2. `@PrePersist - This annotation will ensure that the automatically generated value for the createdAt field is stored in this field whenever we’ll need access. For more information on Database Auditing you can check this link: Database Auditing

Users Model

@Entity
@Table(name = “users”)
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value = {“createdAt”},
allowGetters = true)
public class Users implements Serializable {
private static final long serialVersionUID = 2L;

@Column(updatable = false)
@Id
private Long id;

@NotBlank(message = "The field 'name' is mandatory.")
private String name;

@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
@CreatedDate
private Date createdAt; //Stores the date at which a user was created.

@PrePersist
public void prePersist(){
    createdAt = new Date();
}

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

public long getId() {
    return id;
}

public void setName(String name) {
    this.name = name;
}

public String getName() {
    return name;
}



@Override
public String toString() {
    JsonObjectBuilder builder = Json.createObjectBuilder();

    //serialize to Json only if the data was persisted.
    if(!Objects.isNull(id)){
        builder.add("id",id);
    }
    if(!Objects.isNull(name)){
        builder.add("name",name);
    }

    if(!Objects.isNull(createdAt)) {
        builder.add("created_at",createdAt.toString());
    }
    return builder.build().toString();
}


public Date getCreatedAt() {
    return createdAt;
}

}

The Custom Error Page Template

In the templates folder we defined, create a html page and name it error.html and copy paste the following code into it:

<html xmlns:th=“http://www.thymeleaf.org”>
<head>
    <title>Error</title>
</head>
<body>
    <div>Web Application. Error : th:text=“${error}”</div>
</body>
</html>

thymeleaf will automatically parse this html page and render our error message by replacing the th:text attribute.

Finally

Run your Application.java’s main method and test out your netflix api on your browser by navigation to localhost:8080/. You should be able to see your json responses on your browser. Alternatively, you can check out my git repository for the source code and a client you can test your code with: github repo

Conclusion

You’ve successfully made a netflix api using Spring boot, mysql and JPA. Congrats!

Thanks For Visiting, Keep Visiting.

#java #sql #hibernate #jpa #spring-boot

What is GEEK

Buddha Community

Understand to Spring Boot: MySQL and JPA, Hibernate

Enhance Amazon Aurora Read/Write Capability with ShardingSphere-JDBC

1. Introduction

Amazon Aurora is a relational database management system (RDBMS) developed by AWS(Amazon Web Services). Aurora gives you the performance and availability of commercial-grade databases with full MySQL and PostgreSQL compatibility. In terms of high performance, Aurora MySQL and Aurora PostgreSQL have shown an increase in throughput of up to 5X over stock MySQL and 3X over stock PostgreSQL respectively on similar hardware. In terms of scalability, Aurora achieves enhancements and innovations in storage and computing, horizontal and vertical functions.

Aurora supports up to 128TB of storage capacity and supports dynamic scaling of storage layer in units of 10GB. In terms of computing, Aurora supports scalable configurations for multiple read replicas. Each region can have an additional 15 Aurora replicas. In addition, Aurora provides multi-primary architecture to support four read/write nodes. Its Serverless architecture allows vertical scaling and reduces typical latency to under a second, while the Global Database enables a single database cluster to span multiple AWS Regions in low latency.

Aurora already provides great scalability with the growth of user data volume. Can it handle more data and support more concurrent access? You may consider using sharding to support the configuration of multiple underlying Aurora clusters. To this end, a series of blogs, including this one, provides you with a reference in choosing between Proxy and JDBC for sharding.

1.1 Why sharding is needed

AWS Aurora offers a single relational database. Primary-secondary, multi-primary, and global database, and other forms of hosting architecture can satisfy various architectural scenarios above. However, Aurora doesn’t provide direct support for sharding scenarios, and sharding has a variety of forms, such as vertical and horizontal forms. If we want to further increase data capacity, some problems have to be solved, such as cross-node database Join, associated query, distributed transactions, SQL sorting, page turning, function calculation, database global primary key, capacity planning, and secondary capacity expansion after sharding.

1.2 Sharding methods

It is generally accepted that when the capacity of a MySQL table is less than 10 million, the time spent on queries is optimal because at this time the height of its BTREE index is between 3 and 5. Data sharding can reduce the amount of data in a single table and distribute the read and write loads to different data nodes at the same time. Data sharding can be divided into vertical sharding and horizontal sharding.

1. Advantages of vertical sharding

  • Address the coupling of business system and make clearer.
  • Implement hierarchical management, maintenance, monitoring, and expansion to data of different businesses, like micro-service governance.
  • In high concurrency scenarios, vertical sharding removes the bottleneck of IO, database connections, and hardware resources on a single machine to some extent.

2. Disadvantages of vertical sharding

  • After splitting the library, Join can only be implemented by interface aggregation, which will increase the complexity of development.
  • After splitting the library, it is complex to process distributed transactions.
  • There is a large amount of data on a single table and horizontal sharding is required.

3. Advantages of horizontal sharding

  • There is no such performance bottleneck as a large amount of data on a single database and high concurrency, and it increases system stability and load capacity.
  • The business modules do not need to be split due to minor modification on the application client.

4. Disadvantages of horizontal sharding

  • Transaction consistency across shards is hard to be guaranteed;
  • The performance of associated query in cross-library Join is poor.
  • It’s difficult to scale the data many times and maintenance is a big workload.

Based on the analysis above, and the available studis on popular sharding middleware, we selected ShardingSphere, an open source product, combined with Amazon Aurora to introduce how the combination of these two products meets various forms of sharding and how to solve the problems brought by sharding.

ShardingSphere is an open source ecosystem including a set of distributed database middleware solutions, including 3 independent products, Sharding-JDBC, Sharding-Proxy & Sharding-Sidecar.

2. ShardingSphere introduction:

The characteristics of Sharding-JDBC are:

  1. With the client end connecting directly to the database, it provides service in the form of jar and requires no extra deployment and dependence.
  2. It can be considered as an enhanced JDBC driver, which is fully compatible with JDBC and all kinds of ORM frameworks.
  3. Applicable in any ORM framework based on JDBC, such as JPA, Hibernate, Mybatis, Spring JDBC Template or direct use of JDBC.
  4. Support any third-party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP;
  5. Support any kind of JDBC standard database: MySQL, Oracle, SQLServer, PostgreSQL and any databases accessible to JDBC.
  6. Sharding-JDBC adopts decentralized architecture, applicable to high-performance light-weight OLTP application developed with Java

Hybrid Structure Integrating Sharding-JDBC and Applications

Sharding-JDBC’s core concepts

Data node: The smallest unit of a data slice, consisting of a data source name and a data table, such as ds_0.product_order_0.

Actual table: The physical table that really exists in the horizontal sharding database, such as product order tables: product_order_0, product_order_1, and product_order_2.

Logic table: The logical name of the horizontal sharding databases (tables) with the same schema. For instance, the logic table of the order product_order_0, product_order_1, and product_order_2 is product_order.

Binding table: It refers to the primary table and the joiner table with the same sharding rules. For example, product_order table and product_order_item are sharded by order_id, so they are binding tables with each other. Cartesian product correlation will not appear in the multi-tables correlating query, so the query efficiency will increase greatly.

Broadcast table: It refers to tables that exist in all sharding database sources. The schema and data must consist in each database. It can be applied to the small data volume that needs to correlate with big data tables to query, dictionary table and configuration table for example.

3. Testing ShardingSphere-JDBC

3.1 Example project

Download the example project code locally. In order to ensure the stability of the test code, we choose shardingsphere-example-4.0.0 version.

git clone https://github.com/apache/shardingsphere-example.git

Project description:

shardingsphere-example
  ├── example-core
  │   ├── config-utility
  │   ├── example-api
  │   ├── example-raw-jdbc
  │   ├── example-spring-jpa #spring+jpa integration-based entity,repository
  │   └── example-spring-mybatis
  ├── sharding-jdbc-example
  │   ├── sharding-example
  │   │   ├── sharding-raw-jdbc-example
  │   │   ├── sharding-spring-boot-jpa-example #integration-based sharding-jdbc functions
  │   │   ├── sharding-spring-boot-mybatis-example
  │   │   ├── sharding-spring-namespace-jpa-example
  │   │   └── sharding-spring-namespace-mybatis-example
  │   ├── orchestration-example
  │   │   ├── orchestration-raw-jdbc-example
  │   │   ├── orchestration-spring-boot-example #integration-based sharding-jdbc governance function
  │   │   └── orchestration-spring-namespace-example
  │   ├── transaction-example
  │   │   ├── transaction-2pc-xa-example #sharding-jdbc sample of two-phase commit for a distributed transaction
  │   │   └──transaction-base-seata-example #sharding-jdbc distributed transaction seata sample
  │   ├── other-feature-example
  │   │   ├── hint-example
  │   │   └── encrypt-example
  ├── sharding-proxy-example
  │   └── sharding-proxy-boot-mybatis-example
  └── src/resources
        └── manual_schema.sql  

Configuration file description:

application-master-slave.properties #read/write splitting profile
application-sharding-databases-tables.properties #sharding profile
application-sharding-databases.properties       #library split profile only
application-sharding-master-slave.properties    #sharding and read/write splitting profile
application-sharding-tables.properties          #table split profile
application.properties                         #spring boot profile

Code logic description:

The following is the entry class of the Spring Boot application below. Execute it to run the project.

The execution logic of demo is as follows:

3.2 Verifying read/write splitting

As business grows, the write and read requests can be split to different database nodes to effectively promote the processing capability of the entire database cluster. Aurora uses a reader/writer endpoint to meet users' requirements to write and read with strong consistency, and a read-only endpoint to meet the requirements to read without strong consistency. Aurora's read and write latency is within single-digit milliseconds, much lower than MySQL's binlog-based logical replication, so there's a lot of loads that can be directed to a read-only endpoint.

Through the one primary and multiple secondary configuration, query requests can be evenly distributed to multiple data replicas, which further improves the processing capability of the system. Read/write splitting can improve the throughput and availability of system, but it can also lead to data inconsistency. Aurora provides a primary/secondary architecture in a fully managed form, but applications on the upper-layer still need to manage multiple data sources when interacting with Aurora, routing SQL requests to different nodes based on the read/write type of SQL statements and certain routing policies.

ShardingSphere-JDBC provides read/write splitting features and it is integrated with application programs so that the complex configuration between application programs and database clusters can be separated from application programs. Developers can manage the Shard through configuration files and combine it with ORM frameworks such as Spring JPA and Mybatis to completely separate the duplicated logic from the code, which greatly improves the ability to maintain code and reduces the coupling between code and database.

3.2.1 Setting up the database environment

Create a set of Aurora MySQL read/write splitting clusters. The model is db.r5.2xlarge. Each set of clusters has one write node and two read nodes.

3.2.2 Configuring Sharding-JDBC

application.properties spring boot Master profile description:

You need to replace the green ones with your own environment configuration.

# Jpa automatically creates and drops data tables based on entities
spring.jpa.properties.hibernate.hbm2ddl.auto=create-drop
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.show_sql=true

#spring.profiles.active=sharding-databases
#spring.profiles.active=sharding-tables
#spring.profiles.active=sharding-databases-tables
#Activate master-slave configuration item so that sharding-jdbc can use master-slave profile
spring.profiles.active=master-slave
#spring.profiles.active=sharding-master-slave

application-master-slave.properties sharding-jdbc profile description:

spring.shardingsphere.datasource.names=ds_master,ds_slave_0,ds_slave_1
# data souce-master
spring.shardingsphere.datasource.ds_master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_master.password=Your master DB password
spring.shardingsphere.datasource.ds_master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_master.jdbc-url=Your primary DB data sourceurl spring.shardingsphere.datasource.ds_master.username=Your primary DB username
# data source-slave
spring.shardingsphere.datasource.ds_slave_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_slave_0.password= Your slave DB password
spring.shardingsphere.datasource.ds_slave_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_slave_0.jdbc-url=Your slave DB data source url
spring.shardingsphere.datasource.ds_slave_0.username= Your slave DB username
# data source-slave
spring.shardingsphere.datasource.ds_slave_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_slave_1.password= Your slave DB password
spring.shardingsphere.datasource.ds_slave_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_slave_1.jdbc-url= Your slave DB data source url
spring.shardingsphere.datasource.ds_slave_1.username= Your slave DB username
# Routing Policy Configuration
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ds_ms
spring.shardingsphere.masterslave.master-data-source-name=ds_master
spring.shardingsphere.masterslave.slave-data-source-names=ds_slave_0,ds_slave_1
# sharding-jdbc configures the information storage mode
spring.shardingsphere.mode.type=Memory
# start shardingsphere log,and you can see the conversion from logical SQL to actual SQL from the print
spring.shardingsphere.props.sql.show=true

 

3.2.3 Test and verification process description

  • Test environment data initialization: Spring JPA initialization automatically creates tables for testing.

  • Write data to the master instance

As shown in the ShardingSphere-SQL log figure below, the write SQL is executed on the ds_master data source.

  • Data query operations are performed on the slave library.

As shown in the ShardingSphere-SQL log figure below, the read SQL is executed on the ds_slave data source in the form of polling.

[INFO ] 2022-04-02 19:43:39,376 --main-- [ShardingSphere-SQL] Rule Type: master-slave 
[INFO ] 2022-04-02 19:43:39,376 --main-- [ShardingSphere-SQL] SQL: select orderentit0_.order_id as order_id1_1_, orderentit0_.address_id as address_2_1_, 
orderentit0_.status as status3_1_, orderentit0_.user_id as user_id4_1_ from t_order orderentit0_ ::: DataSources: ds_slave_0 
---------------------------- Print OrderItem Data -------------------
Hibernate: select orderiteme1_.order_item_id as order_it1_2_, orderiteme1_.order_id as order_id2_2_, orderiteme1_.status as status3_2_, orderiteme1_.user_id 
as user_id4_2_ from t_order orderentit0_ cross join t_order_item orderiteme1_ where orderentit0_.order_id=orderiteme1_.order_id
[INFO ] 2022-04-02 19:43:40,898 --main-- [ShardingSphere-SQL] Rule Type: master-slave 
[INFO ] 2022-04-02 19:43:40,898 --main-- [ShardingSphere-SQL] SQL: select orderiteme1_.order_item_id as order_it1_2_, orderiteme1_.order_id as order_id2_2_, orderiteme1_.status as status3_2_, 
orderiteme1_.user_id as user_id4_2_ from t_order orderentit0_ cross join t_order_item orderiteme1_ where orderentit0_.order_id=orderiteme1_.order_id ::: DataSources: ds_slave_1 

Note: As shown in the figure below, if there are both reads and writes in a transaction, Sharding-JDBC routes both read and write operations to the master library. If the read/write requests are not in the same transaction, the corresponding read requests are distributed to different read nodes according to the routing policy.

@Override
@Transactional // When a transaction is started, both read and write in the transaction go through the master library. When closed, read goes through the slave library and write goes through the master library
public void processSuccess() throws SQLException {
    System.out.println("-------------- Process Success Begin ---------------");
    List<Long> orderIds = insertData();
    printData();
    deleteData(orderIds);
    printData();
    System.out.println("-------------- Process Success Finish --------------");
}

3.2.4 Verifying Aurora failover scenario

The Aurora database environment adopts the configuration described in Section 2.2.1.

3.2.4.1 Verification process description

  1. Start the Spring-Boot project

2. Perform a failover on Aurora’s console

3. Execute the Rest API request

4. Repeatedly execute POST (http://localhost:8088/save-user) until the call to the API failed to write to Aurora and eventually recovered successfully.

5. The following figure shows the process of executing code failover. It takes about 37 seconds from the time when the latest SQL write is successfully performed to the time when the next SQL write is successfully performed. That is, the application can be automatically recovered from Aurora failover, and the recovery time is about 37 seconds.

3.3 Testing table sharding-only function

3.3.1 Configuring Sharding-JDBC

application.properties spring boot master profile description

# Jpa automatically creates and drops data tables based on entities
spring.jpa.properties.hibernate.hbm2ddl.auto=create-drop
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.show_sql=true
#spring.profiles.active=sharding-databases
#Activate sharding-tables configuration items
#spring.profiles.active=sharding-tables
#spring.profiles.active=sharding-databases-tables
# spring.profiles.active=master-slave
#spring.profiles.active=sharding-master-slave

application-sharding-tables.properties sharding-jdbc profile description

## configure primary-key policy
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds.t_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123
# configure the binding relation of t_order and t_order_item
spring.shardingsphere.sharding.binding-tables[0]=t_order,t_order_item
# configure broadcast tables
spring.shardingsphere.sharding.broadcast-tables=t_address
# sharding-jdbc mode
spring.shardingsphere.mode.type=Memory
# start shardingsphere log
spring.shardingsphere.props.sql.show=true

 

3.3.2 Test and verification process description

1. DDL operation

JPA automatically creates tables for testing. When Sharding-JDBC routing rules are configured, the client executes DDL, and Sharding-JDBC automatically creates corresponding tables according to the table splitting rules. If t_address is a broadcast table, create a t_address because there is only one master instance. Two physical tables t_order_0 and t_order_1 will be created when creating t_order.

2. Write operation

As shown in the figure below, Logic SQL inserts a record into t_order. When Sharding-JDBC is executed, data will be distributed to t_order_0 and t_order_1 according to the table splitting rules.

When t_order and t_order_item are bound, the records associated with order_item and order are placed on the same physical table.

3. Read operation

As shown in the figure below, perform the join query operations to order and order_item under the binding table, and the physical shard is precisely located based on the binding relationship.

The join query operations on order and order_item under the unbound table will traverse all shards.

3.4 Testing database sharding-only function

3.4.1 Setting up the database environment

Create two instances on Aurora: ds_0 and ds_1

When the sharding-spring-boot-jpa-example project is started, tables t_order, t_order_itemt_address will be created on two Aurora instances.

3.4.2 Configuring Sharding-JDBC

application.properties springboot master profile description

# Jpa automatically creates and drops data tables based on entities
spring.jpa.properties.hibernate.hbm2ddl.auto=create
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.show_sql=true

# Activate sharding-databases configuration items
spring.profiles.active=sharding-databases
#spring.profiles.active=sharding-tables
#spring.profiles.active=sharding-databases-tables
#spring.profiles.active=master-slave
#spring.profiles.active=sharding-master-slave

application-sharding-databases.properties sharding-jdbc profile description

spring.shardingsphere.datasource.names=ds_0,ds_1
# ds_0
spring.shardingsphere.datasource.ds_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_0.jdbc-url= spring.shardingsphere.datasource.ds_0.username= 
spring.shardingsphere.datasource.ds_0.password=
# ds_1
spring.shardingsphere.datasource.ds_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_1.jdbc-url= 
spring.shardingsphere.datasource.ds_1.username= 
spring.shardingsphere.datasource.ds_1.password=
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds_$->{user_id % 2}
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.sharding.broadcast-tables=t_address
spring.shardingsphere.sharding.default-data-source-name=ds_0

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds_$->{0..1}.t_order
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds_$->{0..1}.t_order_item
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123
# sharding-jdbc mode
spring.shardingsphere.mode.type=Memory
# start shardingsphere log
spring.shardingsphere.props.sql.show=true

 

3.4.3 Test and verification process description

1. DDL operation

JPA automatically creates tables for testing. When Sharding-JDBC’s library splitting and routing rules are configured, the client executes DDL, and Sharding-JDBC will automatically create corresponding tables according to table splitting rules. If t_address is a broadcast table, physical tables will be created on ds_0 and ds_1. The three tables, t_address, t_order and t_order_item will be created on ds_0 and ds_1 respectively.

2. Write operation

For the broadcast table t_address, each record written will also be written to the t_address tables of ds_0 and ds_1.

The tables t_order and t_order_item of the slave library are written on the table in the corresponding instance according to the slave library field and routing policy.

3. Read operation

Query order is routed to the corresponding Aurora instance according to the routing rules of the slave library .

Query Address. Since address is a broadcast table, an instance of address will be randomly selected and queried from the nodes used.

As shown in the figure below, perform the join query operations to order and order_item under the binding table, and the physical shard is precisely located based on the binding relationship.

3.5 Verifying sharding function

3.5.1 Setting up the database environment

As shown in the figure below, create two instances on Aurora: ds_0 and ds_1

When the sharding-spring-boot-jpa-example project is started, physical tables t_order_01, t_order_02, t_order_item_01,and t_order_item_02 and global table t_address will be created on two Aurora instances.

3.5.2 Configuring Sharding-JDBC

application.properties springboot master profile description

# Jpa automatically creates and drops data tables based on entities
spring.jpa.properties.hibernate.hbm2ddl.auto=create
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.show_sql=true
# Activate sharding-databases-tables configuration items
#spring.profiles.active=sharding-databases
#spring.profiles.active=sharding-tables
spring.profiles.active=sharding-databases-tables
#spring.profiles.active=master-slave
#spring.profiles.active=sharding-master-slave

application-sharding-databases.properties sharding-jdbc profile description

spring.shardingsphere.datasource.names=ds_0,ds_1
# ds_0
spring.shardingsphere.datasource.ds_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_0.jdbc-url= 306/dev?useSSL=false&characterEncoding=utf-8
spring.shardingsphere.datasource.ds_0.username= 
spring.shardingsphere.datasource.ds_0.password=
spring.shardingsphere.datasource.ds_0.max-active=16
# ds_1
spring.shardingsphere.datasource.ds_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_1.jdbc-url= 
spring.shardingsphere.datasource.ds_1.username= 
spring.shardingsphere.datasource.ds_1.password=
spring.shardingsphere.datasource.ds_1.max-active=16
# default library splitting policy
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds_$->{user_id % 2}
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.sharding.broadcast-tables=t_address
# Tables that do not meet the library splitting policy are placed on ds_0
spring.shardingsphere.sharding.default-data-source-name=ds_0
# t_order table splitting policy
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds_$->{0..1}.t_order_$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123
# t_order_item table splitting policy
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds_$->{0..1}.t_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123
# sharding-jdbc mdoe
spring.shardingsphere.mode.type=Memory
# start shardingsphere log
spring.shardingsphere.props.sql.show=true

 

3.5.3 Test and verification process description

1. DDL operation

JPA automatically creates tables for testing. When Sharding-JDBC’s sharding and routing rules are configured, the client executes DDL, and Sharding-JDBC will automatically create corresponding tables according to table splitting rules. If t_address is a broadcast table, t_address will be created on both ds_0 and ds_1. The three tables, t_address, t_order and t_order_item will be created on ds_0 and ds_1 respectively.

2. Write operation

For the broadcast table t_address, each record written will also be written to the t_address tables of ds_0 and ds_1.

The tables t_order and t_order_item of the sub-library are written to the table on the corresponding instance according to the slave library field and routing policy.

3. Read operation

The read operation is similar to the library split function verification described in section2.4.3.

3.6 Testing database sharding, table sharding and read/write splitting function

3.6.1 Setting up the database environment

The following figure shows the physical table of the created database instance.

3.6.2 Configuring Sharding-JDBC

application.properties spring boot master profile description

# Jpa automatically creates and drops data tables based on entities
spring.jpa.properties.hibernate.hbm2ddl.auto=create
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.show_sql=true

# activate sharding-databases-tables configuration items
#spring.profiles.active=sharding-databases
#spring.profiles.active=sharding-tables
#spring.profiles.active=sharding-databases-tables
#spring.profiles.active=master-slave
spring.profiles.active=sharding-master-slave

application-sharding-master-slave.properties sharding-jdbc profile description

The url, name and password of the database need to be changed to your own database parameters.

spring.shardingsphere.datasource.names=ds_master_0,ds_master_1,ds_master_0_slave_0,ds_master_0_slave_1,ds_master_1_slave_0,ds_master_1_slave_1
spring.shardingsphere.datasource.ds_master_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_master_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_master_0.jdbc-url= spring.shardingsphere.datasource.ds_master_0.username= 
spring.shardingsphere.datasource.ds_master_0.password=
spring.shardingsphere.datasource.ds_master_0.max-active=16
spring.shardingsphere.datasource.ds_master_0_slave_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_master_0_slave_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_master_0_slave_0.jdbc-url= spring.shardingsphere.datasource.ds_master_0_slave_0.username= 
spring.shardingsphere.datasource.ds_master_0_slave_0.password=
spring.shardingsphere.datasource.ds_master_0_slave_0.max-active=16
spring.shardingsphere.datasource.ds_master_0_slave_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_master_0_slave_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_master_0_slave_1.jdbc-url= spring.shardingsphere.datasource.ds_master_0_slave_1.username= 
spring.shardingsphere.datasource.ds_master_0_slave_1.password=
spring.shardingsphere.datasource.ds_master_0_slave_1.max-active=16
spring.shardingsphere.datasource.ds_master_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_master_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_master_1.jdbc-url= 
spring.shardingsphere.datasource.ds_master_1.username= 
spring.shardingsphere.datasource.ds_master_1.password=
spring.shardingsphere.datasource.ds_master_1.max-active=16
spring.shardingsphere.datasource.ds_master_1_slave_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_master_1_slave_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_master_1_slave_0.jdbc-url=
spring.shardingsphere.datasource.ds_master_1_slave_0.username=
spring.shardingsphere.datasource.ds_master_1_slave_0.password=
spring.shardingsphere.datasource.ds_master_1_slave_0.max-active=16
spring.shardingsphere.datasource.ds_master_1_slave_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds_master_1_slave_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds_master_1_slave_1.jdbc-url= spring.shardingsphere.datasource.ds_master_1_slave_1.username=admin
spring.shardingsphere.datasource.ds_master_1_slave_1.password=
spring.shardingsphere.datasource.ds_master_1_slave_1.max-active=16
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds_$->{user_id % 2}
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.sharding.broadcast-tables=t_address
spring.shardingsphere.sharding.default-data-source-name=ds_master_0
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds_$->{0..1}.t_order_$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds_$->{0..1}.t_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123
# master/slave data source and slave data source configuration
spring.shardingsphere.sharding.master-slave-rules.ds_0.master-data-source-name=ds_master_0
spring.shardingsphere.sharding.master-slave-rules.ds_0.slave-data-source-names=ds_master_0_slave_0, ds_master_0_slave_1
spring.shardingsphere.sharding.master-slave-rules.ds_1.master-data-source-name=ds_master_1
spring.shardingsphere.sharding.master-slave-rules.ds_1.slave-data-source-names=ds_master_1_slave_0, ds_master_1_slave_1
# sharding-jdbc mode
spring.shardingsphere.mode.type=Memory
# start shardingsphere log
spring.shardingsphere.props.sql.show=true

 

3.6.3 Test and verification process description

1. DDL operation

JPA automatically creates tables for testing. When Sharding-JDBC’s library splitting and routing rules are configured, the client executes DDL, and Sharding-JDBC will automatically create corresponding tables according to table splitting rules. If t_address is a broadcast table, t_address will be created on both ds_0 and ds_1. The three tables, t_address, t_order and t_order_item will be created on ds_0 and ds_1 respectively.

2. Write operation

For the broadcast table t_address, each record written will also be written to the t_address tables of ds_0 and ds_1.

The tables t_order and t_order_item of the slave library are written to the table on the corresponding instance according to the slave library field and routing policy.

3. Read operation

The join query operations on order and order_item under the binding table are shown below.

3. Conclusion

As an open source product focusing on database enhancement, ShardingSphere is pretty good in terms of its community activitiy, product maturity and documentation richness.

Among its products, ShardingSphere-JDBC is a sharding solution based on the client-side, which supports all sharding scenarios. And there’s no need to introduce an intermediate layer like Proxy, so the complexity of operation and maintenance is reduced. Its latency is theoretically lower than Proxy due to the lack of intermediate layer. In addition, ShardingSphere-JDBC can support a variety of relational databases based on SQL standards such as MySQL/PostgreSQL/Oracle/SQL Server, etc.

However, due to the integration of Sharding-JDBC with the application program, it only supports Java language for now, and is strongly dependent on the application programs. Nevertheless, Sharding-JDBC separates all sharding configuration from the application program, which brings relatively small changes when switching to other middleware.

In conclusion, Sharding-JDBC is a good choice if you use a Java-based system and have to to interconnect with different relational databases — and don’t want to bother with introducing an intermediate layer.

Author

Sun Jinhua

A senior solution architect at AWS, Sun is responsible for the design and consult on cloud architecture. for providing customers with cloud-related design and consulting services. Before joining AWS, he ran his own business, specializing in building e-commerce platforms and designing the overall architecture for e-commerce platforms of automotive companies. He worked in a global leading communication equipment company as a senior engineer, responsible for the development and architecture design of multiple subsystems of LTE equipment system. He has rich experience in architecture design with high concurrency and high availability system, microservice architecture design, database, middleware, IOT etc.

Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

Edward Jackson

Edward Jackson

1562205651

Building a Simple CRUD App using Spring Boot, MySQL, JPA/Hibernate

In this article, we will walk through how to build a simple CRUD application using Spring Boot, MySQL, JPA/Hibernate and Okta OpenID Connect (OIDC) Single Sign-On (SSO).

The Java Persistence API (JPA) provides a specification for persisting, reading, and managing data from your Java object to relational tables in the database. The default implementation of JPA via Spring Boot is Hibernate. Hibernate saves you a lot of time writing code to persist data to a database, allowing you to focus on the business logic.

In this example, we will use MySQL for the database.

The application you will build will have two main parts. The first is the authentication piece. This will use Okta OIDC SSO to authenticate admin or ordinary users into the application. The second part will be the management of user events. These events are a record of any time a new token is issued for this application. The event will store the user’s name, internal id from Okta, and store additional information per token issuance including the time of the original login and the time the token was last used within the application to view a page. If you are wondering what the difference is between these three dates/times, here is a cheat-sheet:

  • Login Date/Time - This is the time at which the user actually had to enter their credentials. There can be more than one token issued against the same original login. This could be the time the user logged into this application, the Okta console, or another application tied to the same developer account (since this is an SSO example).
  • **Token Date/Time ** - When the user starts a session with the application described in this post, a new token is issued by Okta. This token is valid for a certain amount of time and is reused across page refreshes until it expires
  • Last View Date/Time - This is the last time the application was viewed. If you refresh the page, this time will update, while the Token Date/Time and Login Date/Time should remain the same.

Summary of CRUD actions

CRUD - Create When any user visits the application with a new session, they will be issued a new token. When this token is issued, a userEvent will be generated and persisted in the MySQL Database.

CRUD - Read Ordinary users will be able to see a list of all of the times they have received a new Okta token for this application. The application will also display the Login Date/Time and Last View Date/Time for that token (described earlier).

Admin users will be able to see the same info as the normal user, but for all users who have logged into the system.

CRUD - Update Each time any user visits the application, it will check if the token that they are using has already been saved as a userEvent. If so, it will update that entry with a new Last View Date/Time

CRUD - Delete Admin users will have the option to delete any of the user events in the system. Pro Tip: In a production system, you wouldn’t want to ever delete a log history, particularly a log regarding authentication or authorization actions. If this were production code and you had a requirement to delete log entries in order to hide them from ordinary users, you would likely want to implement this by setting a deleted flag on that record and only displaying records that are not flagged as deleted.

Prerequisites

MySQL - You must have installed a local instance of MySQL or have access to a remote instance of MySQL. For this exercise, I recommend that you have a fresh empty database prepared and have the username and password for that database handy. They will be required in the application.properties file later.

Setup your Okta OIDC Application, Authorization Server, groups and users

Before we can dive into the code, we will want to first get our Okta configuration in place. If you haven’t already, head on over to developer.okta.com to create yourself a free-forever developer account. Look for the email to complete the initialization of your Okta org.

Once you have your developer account, we will need to set up your web application, authorization server, group, and users!

Setup you Okta OIDC Application

Navigate to Applications in the admin console and click: Add Application. Choose Web and click Next. Populate the fields with these values:

Click Done. Scroll down and copy the Client ID and Client Secret. You’ll use those values shortly.

Setup your Okta Authorization Server

Next, you’ll set up an Authorization Server with custom claims and access policies. This drives whether or not Okta will issue a token when one is requested. Navigate to API > Authorization Servers. Click Add Authorization Server. Fill in the values as follows:

Click Done. Click the Claims tab. Click Add Claim. Fill in the fields with these values and click Create (leave those not mentioned as their defaults):

Note that there are double quotes (“) around “Beyond Authentication Application”.

Next, click on Add Claim again. Fill in the fields with these values and click Create (leave those not mentioned as their defaults):

Click the Access Policies tab. Click Add Policy. Fill in the fields with these values and click Create Policy

This binds the policy to your OIDC app.

Next click Add Rule. Enter: Beyond Authentication Application for the Rule Name field. Deselect all the grant types except for Authorization Code. Click Create Rule. This ensures that the request must use the Authorization Code flow in order for Okta to create tokens. This is the most secure flow of all the available OAuth flows. It ensures that all sensitive information (like tokens) are delivered via a response to a POST request. Check out the references at the end of this post for more on OAuth flows.

Click the Settings tab and copy the Issuer URL. You’ll make use of this value shortly.

Create Okta Admin Group for your application

In order to complete this application, we need to set up an “Admin” group for our application. To do this, within your Okta developer console, click on Users > Groups and then click on Add Group. Enter the following values:

Create Okta Users for your application

Finally, we need to create two users. The first will be an ordinary user and the second will be an admin user. From the developers console, click on Users > People and then click on Add Person. Fill out the form with the information for the ordinary (non-admin) user using the table below. Repeat this for the Admin user, also using the table below.

Take note of the username and password for each of the two users you created as you will use them to login later on.

Let’s code it

The structure of our project will look like this:

NOTE: For this post, I will be using Eclipse, as it is my preferred IDE. However, you can use any IDE or editor you please.

Dependencies

The Spring Initializr makes it super easy to create the scaffolding for your project. What’s even better is that it has a RESTful API, so you can get everything setup right from the command line. Run this command:

curl -s https://start.spring.io/starter.zip \
    -d dependencies=data-jpa,web,okta,thymeleaf,mysql \
    -d  packageName=com.okta.examples.jpa \
    -d groupId=com.okta.examples.jpa \
    -d name=BeyondAuthentication \
    > BeyondAuthentication.zip

Create a folder named: BeyondAuthentication, switch into it and unzip the resulting file.You’ll see the whole project has been setup for you. Let’s take a look at the pom.xml file, which has all the dependencies for the project.

The file should be set up as follows


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        4.0.0
        
                org.springframework.boot
                spring-boot-starter-parent
                2.1.6.RELEASE
                 
        
        com.okta.examples.jpa
        demo
        0.0.1-SNAPSHOT
        BeyondAuthentication
        Demo project for Spring Boot

        
                1.8
        

        
                
                        org.springframework.boot
                        spring-boot-starter-data-jpa
                
                
                        org.springframework.boot
                        spring-boot-starter-thymeleaf
                
                
                        org.springframework.boot
                        spring-boot-starter-web
                
                
                        com.okta.spring
                        okta-spring-boot-starter
                        1.2.1
                
           
                
                        mysql
                        mysql-connector-java
                        runtime
                
                
                        org.springframework.boot
                        spring-boot-starter-test
                        test
                
        

        
                
                        
                                org.springframework.boot
                                spring-boot-maven-plugin
                        
                
        

     

The dependencies above tell the system that you want to use Thymeleaf for your web templates, MySQL for your database, and Hibernate (the default JPA implementation) to persist and retrieve data from MySQL.

Templates

Let’s also knock out the HTML template now, so you will have an idea what the application is trying to do/show later. Create the home.html file in the src/main/resources/templates folder.



    
        
    

    
        
            
                
                    
                    <div th:if="${user.claims.containsKey('appName')}"
                         class="alert alert-success text-center" role="alert"
                    >
                        ## 

                    
                    
                    <div th:if="${user.claims.containsKey('name')}"
                         class="alert alert-primary text-center" role="alert"
                    >
                        ### Welcome [[${user.claims.name}]]!

                    
                    
                        
                            
                                ## No User Events yet!

                                
                                    
                                        
                                        
                                            Name
                                            Login Date/Time
                                            Token Date/Time
                                            Last View Date/Time
                                            Delete
                                        
                                        
                                        
                                        
                                            
                                            
                                            
                                            
                                            
                                        
                                        
                                    
                                
                            
                        
                    
                
            
        
    

This template is very basic. It shows a list of user events and related fields. If the logged in user is an admin, it will show a delete button by each event. This template doesn’t control which events are shown, that will be determined in the controller later.

Project Configuration

We will configure the project via the application.properties file located in src/main/resources. In this file, we will configure the database connection, the Okta account information, and a few other fields.

okta.oauth2.issuer={authServerUrl}
okta.oauth2.clientId={clientId}
okta.oauth2.clientSecret={clientSecret}
okta.oauth2.scope=openid

## MySQL
spring.datasource.url=jdbc:mysql://localhost:3306/{dbName}

spring.datasource.username={dbUser}
spring.datasource.password={dbPassword}

# drop in to create the table, good for testing, comment this in production. This will create the table for you on each load of application, so you may want to comment this out after the first load
spring.jpa.hibernate.ddl-auto=create

The first section of the properties file is the Okta configuration. Earlier, you copied a few items including your Okta Web Application’s client id, client secret, and authorization server issuer URL. Paste those values into the application.properties file.

The next section is the MySql configuration. Replace the values inside the {} with the appropriate database name, user, and password. Note, you will have to replace the whole data source URL if you are not running MySql locally.

The following line in the properties file is very important: spring.jpa.hibernate.ddl-auto=create

This property tells the system to create (or re-create) the table from scratch to match the definition in the POJO Entity class (described below). The first time you run your application, or any time you change the structure of the entity class, you will want to have this line uncommented. However, after the table is constructed as you like, you will want to comment this line out or else it will drop and recreate every time you run it and you may wonder why your data isn’t persisting!

POJO Entity

Under the com.okta.examples.jpa package, create the UserEvent class. This class is a POJO that represents the fields in the UserEvent object that will persist to the database.

@Entity
public class UserEvent {

    private static SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm'Z'");

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;
    
    @Lob 
    private String token;

    private String userId;

    @Temporal(TemporalType.TIMESTAMP)
    private Date loginAt;

    @Temporal(TemporalType.TIMESTAMP)
    private Date tokenIssuedAt;

    @Temporal(TemporalType.TIMESTAMP)
    private Date lastViewedAt;
    
    public UserEvent() {}

    public UserEvent(String userId, String name,String token,Date loginAt,Date issueAt) {
        this.userId=userId;
        this.name = name;
        this.token = token;
        this.loginAt = loginAt;
        this.lastViewedAt=loginAt;
        this.tokenIssuedAt=issueAt;
    }

    @Override
    public String toString() {
        return "UserEvent{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", token='" + token + '\'' +
            ", loginAt='" + loginAt + '\'' +
            '}';
    }

    // put boilerplate getters and setters here

    @Transient
    public String getTokenIssuedAtString() {
        return formatter.format(tokenIssuedAt);
    }
    
    @Transient
    public String getLastViewedAtString() {
        return formatter.format(lastViewedAt);
    }
    
    @Transient
    public String getLoginAtString() {
        return formatter.format(loginAt);
    }
}

The @Entity notation tells the system that this object will represent a row in a table. It tells Hibernate that this data should be stored in a table called user_event (from lowercasing the class name and adding “_” between words that were capitalized). If you want the table named something different, you can use the @table(name="tbl_something") annotation. The @Entity class also tells Hibernate that all of the non-static fields and/or getter methods should be persisted to the database. It will ignore any fields/methods that have the @Transient annotation and it will ignore methods that are a getter for a corresponding field. In this case, the entity will store the following fields in the database: id, name, token, userId, loginAt, tokenIssuedAt, and lastViewedAt. In most cases, Hibernate will understand the type of field that the values should be stored as in the database, but there are some times when you need to explicitly tell it what field type to use. In this example, I have used the annotation Lob to tell the system that the token field should be set to longtext. I have also used the @Temporal(TemporalType.TIMESTAMP) annotation to specify that three of the fields should be saved as datetime values. Lastly, I have used the following annotations to indicate that the id field is the primary key that should be autogenerated by the database:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)

Repository

Create the UserEventRepository class. This class is a POJO that represents the fields in the UserEventRepository object that will expose the ability to read and write from the user_event table.

public interface UserEventRepository extends JpaRepository {

    List findByName(String name);
    List findByToken(String token);
    List findByUserId(String userId);
}

This class must extend JpaRepository with the type UserEvent specified so that it knows the type of objects that will be passed in and out. The JpaRepository interface already specifies basic read/write methods, like findById, findAll, and save. However, if you want other find methods specific to the fields, you would include them here, as I did for findByName, findByToken, and findByUserId. The magic is that you do not need to create the implementation class for the UserEventRepository as Spring will automatically create the implementation class at runtime.

Main Application Class

Take a look at the BeyondAuthenticationApplication that was automatically created by the Spring Initializr project. This class is the main application class that runs the application.

@SpringBootApplication
public class BeyondAuthenticationApplication{

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

Create a Controller Class to Route Requests

Last, but far from least, is the controller class. Create the HomeController class. This class does all of the work to read and write from the database. It also prepares the objects needed for the template to render including the list of events to show (all or just the user’s) and whether the logged in user is an admin or not.

@RestController
public class HomeController {

    private final UserEventRepository userEventRepository;

    public HomeController(UserEventRepository userEventRepository) {
        this.userEventRepository = userEventRepository;
    }
    
    @GetMapping("/")
    public ModelAndView home(@AuthenticationPrincipal OidcUser user) {
        String token = user.getIdToken().getTokenValue();
        
        //check if first time with this token, if so record new auth event
        List userEventsForToken = userEventRepository.findByToken(token);
        UserEvent event;
        if (userEventsForToken.size() == 0) {
            //add new event
            event = new UserEvent(
                user.getSubject(), user.getClaims().get("name").toString(),
                token, Date.from(user.getAuthenticatedAt()), Date.from(user.getIssuedAt())
            );
        } else {
            //edit existing event
            event = userEventsForToken.get(0); //there will only ever be one because we update it if it exists already
            event.setLastViewedAt(Date.from(Instant.now()));
        }
        userEventRepository.save(event);
        
        List eventsToShow;
        boolean isAdmin = user.getUserInfo().getClaimAsStringList("groups").contains("Admin");
        if (isAdmin) {
            eventsToShow = userEventRepository.findAll();
        } else {
            eventsToShow = userEventRepository.findByUserId(user.getSubject());
        }
        
        ModelAndView mav = new ModelAndView();
        mav.addObject("user", user.getUserInfo());
        mav.addObject("idToken", user.getIdToken().getTokenValue());
        mav.addObject("userEvents",eventsToShow);
        mav.addObject("isAdmin",isAdmin);
        mav.setViewName("home");
        return mav;
    }
    
    @GetMapping("/delete/{id}")
    public RedirectView deleteUser(@AuthenticationPrincipal OidcUser user,@PathVariable("id") long id, Model model) {
        UserEvent userEvent = userEventRepository.findById(id)
          .orElseThrow(() -> new IllegalArgumentException("Invalid event Id:" + id));
        userEventRepository.delete(userEvent);
        return new RedirectView("/");
    }
}

The constructor of this class automatically passes in the UserEventRepository implementation that Spring auto generated for you. This is the modern way to handle dependency injection. We don’t need the @Autowired annotation anymore.

The home method is annotated with the @GetMapping("/") annotation which tells the system to serve this model/view anytime someone accesses the application with no other path information specified. This method builds up the model and view for the main page of the site that uses the home.xml template. It does the following:

  • Uses the token to look up old user events to see if one already exists for this token. If so, it updates the lastViewedAt time. If not, it creates a new user event.
  • Determines if the logged in user is admin or not
  • Gets list of user events. If user is admin, it gets all of the events. If the user is not an admin, it only gets events for that user.
  • It returns the model and view with the appropriate variables set to use for rendering the template

The deleteUser method is annotated with the @GetMapping("/delete/{id}") annotation which tells it to execute this method when a user calls a URL or clicks on a link with the href that passes in the id of the event to delete. The home.html template included links with this URL configured for an admin to delete user events. This is the code that will get triggered when the user clicks on the delete button. The code simply finds the user event, deletes it, and then redirects back to the main listing page, where you will see the updated list without the deleted item.

Let’s Run it!

Let’s see it in action! In Eclipse, just right click on the BeyondAuthenticationApplication file, click run as > Java application, and it will kick it off. You can now test your application! Type [http://localhost:8080](http://localhost:8080) into your favorite web browser. You should be presented with an Okta login screen:

Note: If you don’t see the Okta login screen, it is likely because you recently logged into the dev console and it is recognizing that login as valid for this application (after all, it is SSO). If that is the case, just use a different browser or an incognito browser tab.

From the login screen, login as your admin user. It will then redirect you back to your application, which should look something like this:

Next, let’s login with a non-admin user. Do the same steps as above in a different browser or within a new incognito tab. It should present you with the login screen again. From there, log in with your non-admin user and you will see something like this:

Notice, the delete button is missing. When you refresh your screen for the admin user, you will now see events for both your admin and your non-admin. However, the view for your non-admin user will only ever show their own events.

Play around with the delete button from the admin’s view and try refreshing the page and notice that the last view date/time of the entry will update. If you log in again later on, you will see multiple entries for the user once a new token is issued.

#web-development #spring-boot #mysql #jpa #hibernate

Ambert Lency

Ambert Lency

1567155412

Understand to Spring Boot: MySQL and JPA, Hibernate

Before we start, this post is mostly aimed at Java developers, specifically Java developers who use the Spring umbrella of frameworks for development. If you are not a Java developer, you can still read on, chances are you haven't found the love of your life - in terms of languages - yet.

Also, I apologise in advance for the markup mishaps that will pop up here and there in various code sections. You could check out the original blog post here Original Post if you have trouble understanding/copy pasting the code.

First off, what really is Spring?

If you are a java developer, you've probably stumbled upon the term 'Spring' at some point in your journey, and possibly fell face first at either its learning curve, or its complexity. One thing most developers fail to understand - I also struggled with this at first - is that Spring represents a collection of frameworks that are tailored to meet specific development needs. As an example, if you're a java web developer, Spring provides the Web Servlet Framework for web development where Spring MVC (Included in this framework) is built on top of the Servlet API. Therefore, you need not learn all the frameworks that Spring provides, but rather the frameworks that fit your specific use case. Yeah, that's a shortcut, and yes, you're welcome.

If you've never heard about Spring before, Spring is an Inversion of Control and Dependency injection framework. This are fairly big terms but this comprehensive post will help you understand the meanings of these two concepts: IoC and Dependency Injection

Now onto Spring Boot

If you have used Spring MVC before, you've definitely have had to wrestle with Spring MVC's pre-configurations like Setting up the Dispatcher Servlet etc. etc. before you were able to get the framework up and running. This is where Spring Boot comes in. Spring Boot is an auto-configuration tool for setting up your Spring-powered applications. You can now put away those boxing gloves cause you might not need to wrestle with Spring Boot.

To help you understand Spring Boot further, and shine a light on why you should be using it if you already aren't, we'll build a simple Netflix API that allows client devices to register themselves, suggest movies and query movies.

Let us begin

Step 1: Setting up Spring Boot on your application.

Spring offers a project initializer, Spring Initialzr that allows you to select your project specifications and download an already configured Spring Boot project as a zip file or a maven build file. You could skip to step 2 if you have done this.

If you're a more of a hands on type of person who enjoys understanding what's happening under the hood, you can continue with this step.

Folder Structure.

Create a new Java project with you favourite IDE and configure your folder structure to mimic the following design:

└── src
    └── main
        └── controllers
        └── models
        └── repositories
        └── resources
            └── templates
                └── error.html
            └── application.properties
        └── Application.java

contollers - This folder will contain the controllers we define for this project

repositories - This folder will contain the repositories we'll define for our models that will be used to fetch data from the database.

resources - this folder will contain our project resources. The templatesfolder contains our template files that will be rendered by Spring. You can include other folders like static which will be used to server static content like javascript and css files.

Maven dependencies

Spring Boot allows us to include in our pom.xml file all the Spring dependencies that we'll use in our project. Copy paste the following dependencies, together with the Spring Boot Maven Plugin to your pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
&lt;groupId&gt;org.springframework&lt;/groupId&gt;
&lt;artifactId&gt;gs-spring-boot&lt;/artifactId&gt;
&lt;version&gt;0.1.0&lt;/version&gt;

&lt;parent&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-parent&lt;/artifactId&gt;
    &lt;version&gt;2.1.6.RELEASE&lt;/version&gt;
&lt;/parent&gt;

&lt;dependencies&gt;
    &lt;!--Spring dependencies--&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-web&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-thymeleaf&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-devtools&lt;/artifactId&gt;
    &lt;optional&gt;true&lt;/optional&gt;
&lt;/dependency&gt;

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!–Spring JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
</dependencies>

&lt;properties&gt;
    &lt;java.version&gt;1.8&lt;/java.version&gt;
&lt;/properties&gt;


&lt;build&gt;
    &lt;plugins&gt;
        &lt;plugin&gt;
            &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
            &lt;artifactId&gt;spring-boot-maven-plugin&lt;/artifactId&gt;
        &lt;/plugin&gt;
    &lt;/plugins&gt;
&lt;/build&gt;

</project>

Our dependencies overview:

1. spring-boot-starter-web - When building web applications using java, we often need other external dependencies that we include in our pom.xml like tomcat and Spring MVC. What spring-boot-starter-web does is add all these dependencies through one single dependency.

2. spring-boot-starter-thymeleaf - If you’ve never used thymeleaf before, thymeleaf is a templating engine for processing and creating HTML, XML, JavaScript, CSS, and text whose template files retain the .html extension and therefore a better alternative to JSPs (Java Server Pages). What this basically means is that you can run thymeleaf template files like normal web pages without a backend server for template processing as in the case of JSPs.

3. spring-boot-devtools - These tools grease your gears of development therefore making the overall development process more bearable. To learn more about what these tools offer, you can check out this link: spring-boot-devtools

4. mysql-connector-java - These is the MySQL JDBC implementation that we’ll use to make connections to our MySQL database.

5. spring-boot-starter-data-jpa - Most if not all web applications need some form of persistence, which in java cases, is often JPA (Java Persistence API). If spring-boot-data-jpa is in the classpath, Spring boot will automatically configure our data-source through reading our database configuration from the application.properties file that we will configure next.

Note that we’ve set our java version to 1.8 since JDK 11 does not offer a lot of things out of the box and therefore you may run into errors like: springboot: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.entity.SingleTableEntityPersister

Application.properties file

Spring boot automatically reads configuration settings from this file and configures our spring boot environment accordingly. We’ll configure our database here and also at the same time disable Spring boot’s whitelabel error page which we’ll replace with our own custom error page. You can copy paste all this into your own application.properties file if you do not intended to make any changes.

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/netflix?useSSL=false
spring.datasource.username = netflix
spring.datasource.password = netflix

Hibernate Properties

The SQL dialect makes Hibernate generate better SQL for the chosen database

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

Hibernate ddl auto (create, create-drop, validate, update)

spring.jpa.hibernate.ddl-auto = update

#Disabling the whitelabel error page
server.error.whitelabel.enabled=false

In the above application.properties file, We’ve configured our database, username and password to netflix . You can configure this if you want to. Spring JPA automatically uses the Hibernate implementation of JPA. 

We’ve set spring.jpa.hibernate.ddl-auto to update which will ensure that any changes we make to our models will be reflected in our Database, which also includes creating a new model. Please note that this option is only suitable for development environments rather than production environments. For more information, you can check this link: Database Initialization.

We’ve also set server.error.whitelabel.enabled to false to disable Spring boot’s whitelabel error pages which we’ll replace with our own custom error page.

Configuring our Application.java file

This file will contain the main method which we’ll use to ignite our Spring Application with. Copy paste the following to your Application.java file:

package main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableJpaRepositories(basePackages=“main.repositories”)
@EnableTransactionManagement
@EnableJpaAuditing
@EntityScan(basePackages={“main.entities”,“main.models”})
public class Application {

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

}

@SpringBootApplication is a combination of the following more specific spring annotations -

1. @Configuration : Any class annotated with @Configuration annotation is bootstrapped by Spring and is also considered as a source of other bean definitions.

2. @EnableAutoConfiguration : This annotation tells Spring to automatically configure your application based on the dependencies that you have added in the pom.xml file. For example, If spring-data-jpa or spring-jdbc is in the classpath, then it automatically tries to configure a DataSource by reading the database properties from application.properties file.

3. @ComponentScan : It tells Spring to scan and bootstrap other components defined in the current package (main) and all the sub-packages.

@EnableJpaAuditing is used to support the automatic filling of fields that we’ll annotate with @CreatedDate.

@EnableJpaRepositories tells Spring where to find our defined Repositories, since we’ll not be using the @Repository annotation.


Step 2. Coding our Controllers.

We’ll create only 3 Contollers namely: CustomErrorController that we’ll use to format and serve our custom error page, MoviesController that will perform movie related functions and UsersContoller that will perform user related functions.

CustomErrorController

In this controller, we’ll register a route error that will be mapped to our renderErrorPage method. Therefore all requests made through the errorroute will be recieved by our method.

Note that here we’ll use the @Controller annotation since we’d like to return a view rather than plain text and therefore our method returning a string will return the name of the view. To return plain text rather than views, use the @RestController annotation.

We will also format our error messages to make them more user friendly when we display them on our error page.

We’ve also implemented the ErrorController interface and overridden the getErrorPath() method which will automatically be invoked when Spring encounters an error.

@Controller
public class CustomErrorController implements ErrorController {

@RequestMapping(value = "error",produces = "application/json;charset=UTF-8")
public String renderErrorPage(HttpServletRequest request, Model model) {
     String errorMsg = "";
    Object status = request.getAttribute(RequestDispatcher.ERROR_STATUS_CODE);
    int httpErrorCode = 404;
    if(status != null){
        httpErrorCode = Integer.valueOf(status.toString());
    }
    switch (httpErrorCode) {
        case 400: {
            errorMsg = "Http Error Code: 400. Bad Request";
            break;
        }
        case 401: {
            errorMsg = "Http Error Code: 401. Unauthorized";
            break;
        }
        case 404: {
            errorMsg = "Http Error Code: 404. Resource not found";
            break;
        }
        case 500: {
            errorMsg = "Http Error Code: 500. Internal Server Error";
            break;
        }
    }
    model.addAttribute("error",errorMsg);
    return "error";
}

@Override
public String getErrorPath() {
    return "/error";
}

}

Movies Controller

As we have stated earlier, this Controller will store functionalities related to our movies.

Since we are creating an api, we’ll map api requests to url patterns that start with /api. Therefore, we add a @RequestMapping annotation on top of the class, rather than the method so that every request url we map on our methods will be appended to /api.

@RestController
@RequestMapping(value = “/api”,produces = “application/json;charset=UTF-8”) //All our api request URLs will start with /api and will return Json
public class MoviesController {

private MoviesRepository moviesRepository;
private CategoriesRepository categoriesRepository;
private UserRepository userRepository;

@Autowired
public MoviesController(MoviesRepository moviesRepository, CategoriesRepository categoriesRepository, UserRepository userRepository){
    this.moviesRepository = moviesRepository;
    this.categoriesRepository = categoriesRepository;
    this.userRepository = userRepository;
}

//Suggest A movie
@GetMapping(value = "/suggestMovie")
public String suggestMovie(@RequestParam(name = "category_id") Long categoryId,@RequestParam(name = "name")String name
,@RequestParam(name = "suggested_by")Long suggestedBy){
    //Movies added through this API route are automatically marked as suggested.
    String movieType = Movies.MovieType.SUGGESTED.getMovieType();
    Movies movies = new Movies();

    //Provided category id should be in our categories table.
    if(categoriesRepository.findById(categoryId).isPresent()){

        if(userRepository.findById(suggestedBy).isPresent()){
            movies.setCategoryId(categoryId);
            movies.setName(name);
            movies.setType(movieType);
            movies.setSuggestedBy(suggestedBy);
            return moviesRepository.save(movies).toString();
        } else {
            return "{'error':'The specified user id does not exist.'}";
        }

    } else {
        return "{'error':'The specified category id does not exist.'}";
    }



}

//delete a suggested movie
@GetMapping(value = "/deleteMovie")
public String deleteMovie(@RequestParam(name = "movie_id") Long movieId,@RequestParam(name = "user_id")Long userId) {
    if(userRepository.findById(userId).isPresent()){
        Optional&lt;Movies&gt; movies = moviesRepository.findById(movieId);
        if(movies.isPresent()){
            List&lt;Movies&gt; movie = moviesRepository.findBySuggestedByEqualsAndIdEquals(userId,movieId);
            if(movie.size()&gt;0){
                moviesRepository.delete(movie.get(0));
                return movie.toString();
            } else {
                return generateErrorResponse("The user specified cannot delete this movie");
            }


        } else {
            return  generateErrorResponse("Specified movie id does not exist");
        }

    } else {
        return generateErrorResponse("Specified user id does not exist");
    }
}

//update a suggested movie. Supports only updating of the movie name or category.
@GetMapping(value = "/updateMovie/{movie_id}")
public String updateMovie(@PathVariable(name = "movie_id") Long movieId,@RequestParam(name = "user_id")Long userId,
                          @RequestParam(name = "movie_name",required = false)String movieName, @RequestParam(name = "movie_category",required = false) Long movieCategory) {
    List&lt;Movies&gt; movie = moviesRepository.findBySuggestedByEqualsAndIdEquals(userId,movieId);
    if(!(movie.size()&gt;0)){
        return generateErrorResponse("The user specified cannot update this movie");
    }

    if(moviesRepository.findById(movieId).isPresent()){
        Movies movies = moviesRepository.findById(movieId).get();
        if(movieName != null &amp;&amp; !movieName.isEmpty()){
            movies.setName(movieName);
        }
        if(movieCategory != null &amp;&amp; categoriesRepository.findById(movieCategory).isPresent()){
            movies.setCategoryId(movieCategory);
        }

        return moviesRepository.save(movies).toString();
    } else {
        return generateErrorResponse("The specified movie id does not exist");
    }
}

//query available movies
@GetMapping(value = "/queryMovies/{categoryId}")
public String queryMovies(@PathVariable Long categoryId,@RequestParam(name = "type") String type){
    JsonObjectBuilder jsonResponse = Json.createObjectBuilder();
    JsonObjectBuilder temp = Json.createObjectBuilder();
    int count = 0;
    for(Movies movie:moviesRepository.findAllByCategoryIdEqualsAndTypeEquals(categoryId,type)){
        temp.add("id",movie.getId());
        temp.add("name",movie.getName());
        temp.add("type",movie.getType());
        temp.add("category_id",movie.getCategoryId());
        temp.add("created_at",movie.getCreatedAt().toString());
        jsonResponse.add(count + "",temp);
        temp = Json.createObjectBuilder();
        count++;
    }

    return jsonResponse.build().toString();
}

private String generateErrorResponse(String message){
    return "{\"error\":\"" + message + "\"";
}

//add categories
@GetMapping(value = "/addCategories")
public String addCategories(@RequestParam(name = "name") String name){
    Categories categories = new Categories();
    categories.setName(name);

    return categoriesRepository.save(categories).toString();
}

}

In this Class, you may have noticed annotations that you might have not seen before. Let’s go through them quickly:

1. @Autowired - As the annotation itself suggests, this annotation automatically injects an implementation of the movies, users and categories repository interface which we assign the the field variables we have declared. As we mentioned earlier, you need a repository to be able to access database contents, which explains these three repositories. I’ll explain this further when we reach the repositories section.

2. @GetMapping - This annotation is the same as @RequestMapping except that it only maps get requests to the specified url.

3. @RequestParam - This annotation automatically injects the specified query parameter name to this variable.

4. @PathVariable` - This annotation automatically injects the path value - enclosed in curly braces - to this variable.

Users Controller

This controller will contain functionalities related to users. In this case, we’ll define only a single method that will be responsible for creating a user.

@RestController

@RequestMapping(value = “/api”,produces = “application/json;charset=UTF-8”) //All our api request URLs will start with /api and return Json

public class UsersController {

private UserRepository userRepository;

@Autowired
public UsersController(UserRepository userRepository){
this.userRepository = userRepository;
}

@GetMapping(path = “/addUser”)
public String addUser(@RequestParam(name = “id”)Long id, @RequestParam(name=“name”) String name) {
Users users = new Users();
users.setId(id);
users.setName(name);

users = userRepository.save(users);
return users.toString();

}
}

Our user IDs in this case will not be auto-generated but instead, we’ll provide users with an option to define their own IDs.

Step 3. Defining our Repositories

Repositories will be used by our models to query data from the Database. spring-jpa comes with a JpaRepository interface that defines all CRUD operations that we can perform on an Entity. We’ll use the CrudRepositoryimplementation of JpaRespository as it offers many CRUD operations out of the box through methods like findAll(), save() etc. At the same time, CrudRepository automatically generated for us dynamic queries based on method names as we’ll see in the following example.

We’ll define three repositories for our three entities: CategoriesRepository , MoviesRepository and UsersRepository, which will all be interfaces extending CrudRepository.

CategoriesRepository

public interface CategoriesRepository extends

CrudRepository<Categories,Long> {

}

MoviesRepository

public interface MoviesRepository extends CrudRepository {

List<Movies> findAllByCategoryIdEqualsAndTypeEquals(Long categoryId,String type);

List<Movies> findBySuggestedByEqualsAndIdEquals(Long suggestedBy,Long movieId);

In this repository, notice the abstract methods we have defined. Extending CrudRepository will automatically compel Spring to create an implementation of these methods automatically at run-time just from the definition of the method name. To add Custom methods, we can add them in the following ways:

  1. We can start our query method names with find…Byread…Byquery…Bycount…By, and get…By. Before By we can add expression such as Distinct . After By we need to add property names of our entity.
  2. To get data on the basis of more than one property we can concatenate property names using And and Or while creating method names.
  3. If we want to use completely custom name for our method, we can use @Query annotation to write query.

UsersRepository

@Repository

public interface UserRepository extends CrudRepository {

Final Step: Defining our models.

The models (Entities) that we define will be used to store our table structures as will be defined in the database. We will therefore have three models for our three tables: Categories , Movies and Users.

Categories Model

@Entity

@Table(name = “categories”)

public class Categories {

@Id
@GeneratedValue
private Long id;

@NotBlank
private String name;

public Long getId() {
return id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

@Override
public String toString() {
JsonObjectBuilder builder = Json.createObjectBuilder();

//serialize to Json only if the data was persisted.
if(!Objects.isNull(id)){
    builder.add("id",id);
}
if(!Objects.isNull(name)){
    builder.add("name",name);
}

return builder.build().toString();

}

}

`

An entity is a plain old Java object (POJO) class that is mapped to the database and configured for usage through JPA using annotations and/or XML.

Note that we’ve included a @Table annotation to explicitly define the name of our table. The @Id annotation automatically declares the created field as a primary key for our table in our database. At the same time, the @GeneratedValue annotation will automatically generate a value and store it in the database during saving of a record, pretty much like an auto-increment field. The @NotBlank annotation will automatically validate values that will be inserted into the name variable we defined and ensure that this field is not blank.

We’ve also defined our own toString method (overriding the superclass’s toString method) that will convert our model to a Json string that we return as a response in our controllers.

Movies Model

@Entity

@Table(name = “movies”)

@EntityListeners(AuditingEntityListener.class)

@JsonIgnoreProperties(value = {“createdAt”},allowGetters = true)

public class Movies implements Serializable {

@Id
@GeneratedValue
private Long id;

private Long categoryId;

@NotBlank
private String type;

@NotBlank
private String name;

private Long suggestedBy;

@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
@CreatedDate
private Date createdAt; //Stores the date at which a user was created.

@PrePersist
public void prePersist(){
createdAt = new Date();
}

public Long getId() {
return id;
}

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

public Long getCategoryId() {
return categoryId;
}

public void setCategoryId(Long categoryId) {
this.categoryId = categoryId;
}

public String getType() {
return type;
}

public void setType(String type) {
this.type = type;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Date getCreatedAt() {
return createdAt;
}

@Override
public String toString() {
JsonObjectBuilder builder = Json.createObjectBuilder();

//serialize to Json only if the data was persisted.
if(!Objects.isNull(id)){
    builder.add("id",id);
}
if(!Objects.isNull(name)){
    builder.add("name",name);
}

if(!Objects.isNull(categoryId)){
    builder.add("category_id",categoryId);
}

if(!Objects.isNull(createdAt)) {
    builder.add("created_at",createdAt.toString());
}
return builder.build().toString();

}

public Long getSuggestedBy() {
return suggestedBy;
}

public void setSuggestedBy(Long suggestedBy) {
this.suggestedBy = suggestedBy;
}

public enum MovieType{
SUGGESTED(“suggested”),ORIGINAL(“original”);

private String movieType;

 MovieType(String movieType){
    this.movieType = movieType;
}

public String getMovieType() {
    return movieType;
}

}

In this model, note the annotations below:

1. @EntityListeners(AuditingEntityListener.class) - This will attach an entity listener to our model class that will automatically fill the fields we’ve annotated with @CreatedAt.

2. `@PrePersist - This annotation will ensure that the automatically generated value for the createdAt field is stored in this field whenever we’ll need access. For more information on Database Auditing you can check this link: Database Auditing

Users Model

@Entity
@Table(name = “users”)
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value = {“createdAt”},
allowGetters = true)
public class Users implements Serializable {
private static final long serialVersionUID = 2L;

@Column(updatable = false)
@Id
private Long id;

@NotBlank(message = "The field 'name' is mandatory.")
private String name;

@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
@CreatedDate
private Date createdAt; //Stores the date at which a user was created.

@PrePersist
public void prePersist(){
    createdAt = new Date();
}

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

public long getId() {
    return id;
}

public void setName(String name) {
    this.name = name;
}

public String getName() {
    return name;
}



@Override
public String toString() {
    JsonObjectBuilder builder = Json.createObjectBuilder();

    //serialize to Json only if the data was persisted.
    if(!Objects.isNull(id)){
        builder.add("id",id);
    }
    if(!Objects.isNull(name)){
        builder.add("name",name);
    }

    if(!Objects.isNull(createdAt)) {
        builder.add("created_at",createdAt.toString());
    }
    return builder.build().toString();
}


public Date getCreatedAt() {
    return createdAt;
}

}

The Custom Error Page Template

In the templates folder we defined, create a html page and name it error.html and copy paste the following code into it:

<html xmlns:th=“http://www.thymeleaf.org”>
<head>
    <title>Error</title>
</head>
<body>
    <div>Web Application. Error : th:text=“${error}”</div>
</body>
</html>

thymeleaf will automatically parse this html page and render our error message by replacing the th:text attribute.

Finally

Run your Application.java’s main method and test out your netflix api on your browser by navigation to localhost:8080/. You should be able to see your json responses on your browser. Alternatively, you can check out my git repository for the source code and a client you can test your code with: github repo

Conclusion

You’ve successfully made a netflix api using Spring boot, mysql and JPA. Congrats!

Thanks For Visiting, Keep Visiting.

#java #sql #hibernate #jpa #spring-boot

Hibernate Naming Under Spring Boot and Embedded JPA Entities

Hibernate Naming Under Spring Boot and Embedded JPA Entities

I encountered a little gotcha when setting up multiple Embedded JPA entities while developing a module for my Spring Core online course. In this video, I show you a simple solution to support multiple Embedded JPA Entities without additional JPA annotations to customize the default column names.

You can learn more about my courses on the Spring Framework here: http://bit.ly/1RPhI2A

#spring boot #spring #jpa #hibernate