Related project: SQL, JDBC and Hibernate

Today we will do the final project on the fourth JRU module. What will it be? Let's try to work with different technologies: MySQL, Hibernate, Redis, Docker. Now more subject.

Task: we have a relational MySQL database with a schema (country-city, language by country). And there is a frequent request of the city, which slows down. We came up with a solution - to move all the data that is requested frequently to Redis (in memory storage of the key-value type).

And we need not all the data that is stored in MySQL, but only a selected set of fields. The project will be in the form of a tutorial. That is, here we will raise the problem and immediately solve it.

So, let's start with what software we will need:

1. IDEA Ultimate (who ran out of the key - write to Roman in the Slack)
2. Workbench (or any other client for MySQL)
3. Docker
4. redis-insight - optional

Our action plan:

1. Set up docker (I won’t do this in the tutorial, because each OS will have its own characteristics and there are a lot of answers on the Internet to questions like “how to install docker on windows”), check that everything works.
2. Run MySQL server as a docker container.
3. Expand dump .
4. Create a project in Idea, add maven dependencies.
5. Make layer domain.
6. Write a method to get all data from MySQL.
7. Write a data transformation method (in Redis we will write only the data that is requested frequently).
8. Run the Redis server as a docker container.
9. Write data to Redis.
10. Optional: install redis-insight, look at the data stored in Redis.
11. Write a method for getting data from Redis.
12. Write a method for getting data from MySQL.
13. Compare the speed of getting the same data from MySQL and Redis.

Docker setup

Docker is an open platform for developing, delivering and operating applications. We will use it in order not to install and configure Redis on the local machine, but to use a ready-made image. You can read more about docker here or see it here . If you are not familiar with docker, I recommend looking at just the second link.

To make sure you have docker installed and configured, run the command:docker -v

If everything is OK, you will see the docker version

Run MySQL server as docker container

In order to be able to compare the time of returning data from MySQL and Redis, we will also use MySQL in the docker. In PowerShell (or another console terminal if you're not using Windows), run the command:

docker run --name mysql -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root --restart unless-stopped -v mysql:/var/lib/mysql mysql:8 

Consider what we are doing with this command:

• docker run– launching (and downloading, if it has not yet been downloaded to the local machine) the image. As a result of the launch, we get a running container.
• --name mysql- set the name of the mysql container.
• -d- a flag that says that the container should continue to work, even if you close the terminal window from which this container was launched.
• -p 3306:3306- specifies ports. Before the colon - the port on the local machine, after the colon - the port in the container.
• -e MYSQL_ROOT_PASSWORD=root– passing the environment variable MYSQL_ROOT_PASSWORD with the value root to the container. Flag specific to the mysql/ image
• --restart unless-stopped- setting the behavior policy (whether the container should be restarted when closed). The unless-stopped value means to always restart, except when the container was stopped /
• -v mysql:/var/lib/mysql – add volume (image for storing information).
• mysql:8 – the name of the image and its version.

After executing the command in the terminal, the docker will download all the layers of the image and start the container:

Important note: if you have MySQL installed as a service on your local computer and it is running, you need to specify a different port in the start command, or stop this running service.

Expand dump

To expand the dump, you need to create a new connection to the database from Workbench, where you specify the parameters. I used the default port (3306), didn't change the username (root by default), and set the password for the root user (root).

In Workbench, do Data Import/Restoreand select Import from Self Contained File. Specify where you downloaded the dump as a file . You don't need to create the schema beforehand - its creation is included in the dump file. After a successful import, you will have a world schema with three tables:

1. city ​​is a table of cities.
2. country - country table.
3. country_language - a table that indicates what percentage of the population in the country speaks a particular language.

Since we used a volume when starting the container, after stopping and even deleting the mysql container and re-executing the start command ( docker run --name mysql -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root --restart unless-stopped -v mysql:/var/lib/mysql mysql:8), there will be no need to deploy the dump again - it is already deployed in the volume.

Create project in Idea, add maven dependencies

You already know how to create a project in the Idea - this is the easiest point in today's project.

Add dependencies to the pom file:

<dependencies> 
   <dependency> 
      <groupId>mysql</groupId> 
      <artifactId>mysql-connector-java</artifactId> 
      <version>8.0.30</version> 
   </dependency> 
 
   <dependency> 
      <groupId>org.hibernate</groupId> 
      <artifactId>hibernate-core-jakarta</artifactId> 
      <version>5.6.14.Final</version> 
   </dependency> 
 
   <dependency> 
      <groupId>p6spy</groupId> 
      <artifactId>p6spy</artifactId> 
      <version>3.9.1</version> 
   </dependency> 
    
   <dependency> 
      <groupId>io.lettuce</groupId> 
      <artifactId>lettuce-core</artifactId> 
      <version>6.2.2.RELEASE</version> 
   </dependency> 
 
   <dependency> 
      <groupId>com.fasterxml.jackson.core</groupId> 
      <artifactId>jackson-databind</artifactId> 
      <version>2.14.0</version> 
   </dependency> 
</dependencies> 

The first three dependencies have long been familiar to you.

lettuce-coreis one of the available Java clients for working with Redis.

jackson-databind– dependency for using ObjectMapper (to transform data for storage in Redis (key-value of type String)).

Also in the resources folder (src/main/resources) add spy.properties to view the requests with parameters that Hibernate executes. File contents:

driverlist=com.mysql.cj.jdbc.Driver 
dateformat=yyyy-MM-dd hh:mm:ss a 
appender=com.p6spy.engine.spy.appender.StdoutLogger 
logMessageFormat=com.p6spy.engine.spy.appender.MultiLineFormat 

Make layer domain

Create package com.codegym.domain

It is convenient for me when mapping tables on an entity to use the table structure in the Idea, so let's add a database connection in the Idea.

I suggest creating entities in this order:

• Country
• City
• CountryLanguage

It is desirable that you perform the mapping yourself.

Country class code:

package com.codegym.domain;

import jakarta.persistence.*;

import java.math.BigDecimal;
import java.util.Set;

@Entity
@Table(schema = "world", name = "country")
public class Country {
    @Id
    @Column(name = "id")
    private Integer id;

    private String code;

    @Column(name = "code_2")
    private String alternativeCode;

    private String name;

    @Column(name = "continent")
    @Enumerated(EnumType.ORDINAL)
    private Continent continent;

    private String region;

    @Column(name = "surface_area")
    private BigDecimal surfaceArea;

    @Column(name = "indep_year")
    private Short independenceYear;

    private Integer population;

    @Column(name = "life_expectancy")
    private BigDecimal lifeExpectancy;

    @Column(name = "gnp")
    private BigDecimal GNP;

    @Column(name = "gnpo_id")
    private BigDecimal GNPOId;

    @Column(name = "local_name")
    private String localName;

    @Column(name = "government_form")
    private String governmentForm;

    @Column(name = "head_of_state")
    private String headOfState;

    @OneToOne
    @JoinColumn(name = "capital")
    private City city;

    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "country_id")
    private Set<CountryLanguage> languages;


    //Getters and Setters omitted

}

There are 3 interesting points in the code.

The first is the Continent enam , which is stored in the database as ordinal values. In the structure of the country table, in the comments to the continent field, you can see which numerical value corresponds to which continent.

package com.codegym.domain;

public enum Continent {
    ASIA,
    EUROPE,
    NORTH_AMERICA,
    AFRICA,
    OCEANIA,
    ANTARCTICA,
    SOUTH_AMERICA
}

The second point is a set of entitiesCountryLanguage . Here's a link @OneToManythat wasn't in the second draft of this module. By default, Hibernate will not pull the value of this set when requesting a country entity. But since we need to subtract all values ​​from the relational database for caching, the FetchType.EAGER.

The third is the city field . Communication @OneToOne- like everything is familiar and understandable. But, if we look at the foreign key structure in the database, we see that the country (country) has a link to the capital (city), and the city (city) has a link to the country (country). There is a cyclical relationship.

We won’t do anything with this yet, but when we get to the “Write a method for getting all data from MySQL” item, let’s see what queries Hibernate executes, look at their number, and remember this item. Hibernate

City class code:

package com.codegym.domain;

import jakarta.persistence.*;

@Entity
@Table(schema = "world", name = "city")
public class City {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    @ManyToOne
    @JoinColumn(name = "country_id")
    private Country country;

    private String district;

    private Integer population;


    //Getters and Setters omitted

}

CountryLanguage class code:

package com.codegym.domain;

import jakarta.persistence.*;
import org.hibernate.annotations.Type;

import java.math.BigDecimal;

@Entity
@Table(schema = "world", name = "country_language")
public class CountryLanguage {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @ManyToOne
    @JoinColumn(name = "country_id")
    private Country country;

    private String language;

    @Column(name = "is_official", columnDefinition = "BIT")
    @Type(type = "org.hibernate.type.NumericBooleanType")
    private Boolean isOfficial;

    private BigDecimal percentage;


    //Getters and Setters omitted
}

Write a method to get all data from MySQL

In the Main class, we declare the fields:

private final SessionFactory sessionFactory;
private final RedisClient redisClient;

private final ObjectMapper mapper;

private final CityDAO cityDAO;
private final CountryDAO countryDAO;

and initialize them in the constructor of the Main class:

public Main() {
    sessionFactory = prepareRelationalDb();
    cityDAO = new CityDAO(sessionFactory);
    countryDAO = new CountryDAO(sessionFactory);

    redisClient = prepareRedisClient();
    mapper = new ObjectMapper();
}

As you can see, there are not enough methods and classes - let's write them.

Declare a package com.codegym.dao and add 2 classes to it:

package com.codegym.dao;

import com.codegym.domain.Country;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;

import java.util.List;

public class CountryDAO {
    private final SessionFactory sessionFactory;

    public CountryDAO(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    public List<Country> getAll() {
        Query<Country> query = sessionFactory.getCurrentSession().createQuery("select c from Country c", Country.class);
        return query.list();
    }
}

package com.codegym.dao;

import com.codegym.domain.City;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;

import java.util.List;

public class CityDAO {
    private final SessionFactory sessionFactory;

    public CityDAO(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    public List<City> getItems(int offset, int limit) {
        Query<City> query = sessionFactory.getCurrentSession().createQuery("select c from City c", City.class);
        query.setFirstResult(offset);
        query.setMaxResults(limit);
        return query.list();
    }

    public int getTotalCount() {
        Query<Long> query = sessionFactory.getCurrentSession().createQuery("select count(c) from City c", Long.class);
        return Math.toIntExact(query.uniqueResult());
    }
}

Now you can import these 2 classes into Main. Still missing two methods:

private SessionFactory prepareRelationalDb() {
    final SessionFactory sessionFactory;
    Properties properties = new Properties();
    properties.put(Environment.DIALECT, "org.hibernate.dialect.MySQL8Dialect");
    properties.put(Environment.DRIVER, "com.p6spy.engine.spy.P6SpyDriver");
    properties.put(Environment.URL, "jdbc:p6spy:mysql://localhost:3306/world");
    properties.put(Environment.USER, "root");
    properties.put(Environment.PASS, "root");
    properties.put(Environment.CURRENT_SESSION_CONTEXT_CLASS, "thread");
    properties.put(Environment.HBM2DDL_AUTO, "validate");
    properties.put(Environment.STATEMENT_BATCH_SIZE, "100");

    sessionFactory = new Configuration()
            .addAnnotatedClass(City.class)
            .addAnnotatedClass(Country.class)
            .addAnnotatedClass(CountryLanguage.class)
            .addProperties(properties)
            .buildSessionFactory();
    return sessionFactory;
}

We have not yet reached the radish, so the implementation of the initialization of the radish client will remain a stub for now:

private void shutdown() {
    if (nonNull(sessionFactory)) {
        sessionFactory.close();
    }
    if (nonNull(redisClient)) {
        redisClient.shutdown();
    }
}

Finally, we can write a method in which we pull out all the cities:

private List<City> fetchData(Main main) {
    try (Session session = main.sessionFactory.getCurrentSession()) {
        List<City> allCities = new ArrayList<>();
        session.beginTransaction();

        int totalCount = main.cityDAO.getTotalCount();
        int step = 500;
        for (int i = 0; i < totalCount; i += step) {
            allCities.addAll(main.cityDAO.getItems(i, step));
        }
        session.getTransaction().commit();
        return allCities;
    }
}

The implementation feature is such that we get 500 cities each. This is necessary because there are restrictions on the amount of transmitted data. Yes, in our case, we will not get to them, because. we have a total of 4079 cities in the database. But in production applications, when you need to get a lot of data, this technique is often used.

And the implementation of the main method:

public static void main(String[] args) {
    Main main = new Main();
    List<City> allCities = main.fetchData(main);
    main.shutdown();
}

Now we can run our application in debug for the first time and see how it works (or does not work - yes, it often happens).

Cities are getting. Each city gets a country, if it has not been previously subtracted from the database for another city. Let's roughly calculate how many queries Hibernate will send to the database:

• 1 request to find out the total number of cities (needed to iterate over 500 cities in order to know when to stop).
• 4079 / 500 = 9 requests (list of cities).
• Each city gets a country, if it has not been subtracted earlier. Since there are 239 countries in the database, this will give us 239 queries.

Total 249 requests. And we also said that together with the country we would immediately receive a set of languages, otherwise there would be darkness in general. But it's still a lot, so let's tweak the behavior a bit. Let's start with reflections: what to do, where to run? But seriously - why are there so many requests. If you look at the request log, we see that each country is requested separately, so the first simple solution: let's request all the countries together, because we know in advance that we will need all of them in this transaction.

In the fetchData() method, immediately after the start of the transaction, add the following line:

List<Country> countries = main.countryDAO.getAll(); 

We count requests:

• 1 - get all countries
• 239 - query for each country of its capital
• 1 - request for the number of cities
• 9 - request for lists of cities

Total 250. The idea is good, but it didn't work. The problem is that the country has a connection with the capital (city) @OneToOne. And such a link is loaded immediately by default ( FetchType.EAGER). Let's put FetchType.LAZY, because anyway, we will load all the cities later in the same transaction.

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "capital")
private City city;

Capitals are no longer requested separately, but the number of requests has not changed. Now, for each country, the CountryLanguage list is requested by a separate query . That is, there is progress, and we are moving in the right direction. If you remember, the lectures suggested the “join fetch” solution in order to request an entity with dependent data in one request by adding an additional join to the request. In CountryDAO , rewrite the HQL query in the method getAll()to:

"select c from Country c join fetch c.languages" 

Launch. We look at the log, count requests:

• 1 - all countries with languages
• 1 - number of cities
• 9 - lists of cities.

Total 11- we succeeded)) If you not only read all this text, but also tried to run it after each step of tuning the application, you should even visually note the acceleration of the entire application several times.

Write a data transformation method

Let's create a package com.codegym.redisin which we add 2 classes: CityCountry (data on the city and the country in which this city is located) and Language (data on the language). Here are all the fields that are often requested “by task” in the “braking request”.

package com.codegym.redis;

import com.codegym.domain.Continent;

import java.math.BigDecimal;
import java.util.Set;

public class CityCountry {
    private Integer id;

    private String name;

    private String district;

    private Integer population;

    private String countryCode;

    private String alternativeCountryCode;

    private String countryName;

    private Continent continent;

    private String countryRegion;

    private BigDecimal countrySurfaceArea;

    private Integer countryPopulation;

    private Set<Language> languages;

    //Getters and Setters omitted
}

package com.codegym.redis;

import java.math.BigDecimal;

public class Language {
    private String language;
    private Boolean isOfficial;
    private BigDecimal percentage;

    //Getters and Setters omitted
}

In the main method, after getting all the cities, add the line

List<CityCountry>> preparedData = main.transformData(allCities); 

And implement this method:

private List<CityCountry> transformData(List<City> cities) {
    return cities.stream().map(city -> {
        CityCountry res = new CityCountry();
        res.setId(city.getId());
        res.setName(city.getName());
        res.setPopulation(city.getPopulation());
        res.setDistrict(city.getDistrict());

        Country country = city.getCountry();
        res.setAlternativeCountryCode(country.getAlternativeCode());
        res.setContinent(country.getContinent());
        res.setCountryCode(country.getCode());
        res.setCountryName(country.getName());
        res.setCountryPopulation(country.getPopulation());
        res.setCountryRegion(country.getRegion());
        res.setCountrySurfaceArea(country.getSurfaceArea());
        Set<CountryLanguage> countryLanguages = country.getLanguages();
        Set<Language> languages = countryLanguages.stream().map(cl -> {
            Language language = new Language();
            language.setLanguage(cl.getLanguage());
            language.setOfficial(cl.getOfficial());
            language.setPercentage(cl.getPercentage());
            return language;
        }).collect(Collectors.toSet());
        res.setLanguages(languages);

        return res;
    }).collect(Collectors.toList());
}

I think this method is self-explanatory: we just create a CityCountry entity and fill it with data from City , Country , CountryLanguage .

Run Redis server as a docker container

There are 2 options here. If you do the optional step "install redis-insight, look at the data stored in Redis", then the command is for you:

docker run -d --name redis-stack -p 6379:6379 -p 8001:8001 redis/redis-stack:latest 

If you decide to skip this step, then just:

docker run -d --name redis -p 6379:6379 redis:latest 

The difference is that in the first option, port 8001 is forwarded to the local machine, to which you can connect with an external client to see what is stored inside. And I used to give meaningful names, therefore, redis-stackor redis.

After launch, you can see the list of running containers. To do this, run the command:

docker container ls 

And you will see something like this:

If you need to find some command, you can either look at the help in the terminal (docker help) or google "how to ..." (for example, docker how to remove running container).

And we also called the initialization of the radish client in the Main constructor, but did not implement the method itself. Add implementation:

private RedisClient prepareRedisClient() {
    RedisClient redisClient = RedisClient.create(RedisURI.create("localhost", 6379));
    try (StatefulRedisConnection<String, String> connection = redisClient.connect()) {
        System.out.println("\nConnected to Redis\n");
    }
    return redisClient;
}

sout was added for educational purposes so that in the launch log you can see that everything is OK and the connection through the radish client passed without errors.

Write data to Redis

Add a call to the main method

main.pushToRedis(preparedData); 

With this method implementation:

private void pushToRedis(List<CityCountry> data) {
    try (StatefulRedisConnection<String, String> connection = redisClient.connect()) {
        RedisStringCommands<String, String> sync = connection.sync();
        for (CityCountry cityCountry : data) {
            try {
                sync.set(String.valueOf(cityCountry.getId()), mapper.writeValueAsString(cityCountry));
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
        }

    }
}

Here, a synchronous connection is opened with the radish client, and sequentially each object of the CityCountry type is written to the radish. Since the radish is a String key-value store , the key (city id) is converted to a string. And the value is also to the string, but using ObjectMapper in JSON format.

It remains to run and check that there are no errors in the log. Everything worked.

Install redis-insight, look at the data stored in Redis (optional)

Download redis-insight from the link and install it. After starting, it immediately shows our radish instance in the docker container:

If you log in, we will see a list of all keys:

And you can go to any key to see what data is stored on it:

Write a method for getting data from Redis

For testing, we use the following test: we get 10 CityCountry records. Each with a separate request, but in one connection.

Data from radish can be obtained through our radish client. To do this, let's write a method that takes a list of id's to get.

private void testRedisData(List<Integer> ids) {
    try (StatefulRedisConnection<String, String> connection = redisClient.connect()) {
        RedisStringCommands<String, String> sync = connection.sync();
        for (Integer id : ids) {
            String value = sync.get(String.valueOf(id));
            try {
                mapper.readValue(value, CityCountry.class);
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
        }
    }
}

The implementation, I think, is intuitive: we open a synchronous connection, and for each id we get a JSON String , which we convert into the object of the CityCountry type we need .

Write a method to get data from MySQL

In the CityDAO class, add a method getById(Integer id)in which we will get the city along with the country:

public City getById(Integer id) {
    Query<City> query = sessionFactory.getCurrentSession().createQuery("select c from City c join fetch c.country where c.id = :ID", City.class);
    query.setParameter("ID", id);
    return query.getSingleResult();
}

By analogy with the previous paragraph, let's add a similar method for MySQL to the Main class:

private void testMysqlData(List<Integer> ids) {
    try (Session session = sessionFactory.getCurrentSession()) {
        session.beginTransaction();
        for (Integer id : ids) {
            City city = cityDAO.getById(id);
            Set<CountryLanguage> languages = city.getCountry().getLanguages();
        }
        session.getTransaction().commit();
    }
}

Of the features, in order to be sure to get the full object (without proxy stubs), we explicitly request a list of languages ​​from the country.

Compare the speed of getting the same data from MySQL and Redis

Here I will immediately give the code of the main method, and the result that is obtained on my local computer.

public static void main(String[] args) {
    Main main = new Main();
    List<City> allCities = main.fetchData(main);
    List<CityCountry> preparedData = main.transformData(allCities);
    main.pushToRedis(preparedData);

    // close the current session in order to make a query to the database for sure, and not to pull data from the cache
    main.sessionFactory.getCurrentSession().close();

    //choose random 10 id cities
    //since we did not handle invalid situations, use the existing id in the database
    List<Integer> ids = List.of(3, 2545, 123, 4, 189, 89, 3458, 1189, 10, 102);

    long startRedis = System.currentTimeMillis();
    main.testRedisData(ids);
    long stopRedis = System.currentTimeMillis();

    long startMysql = System.currentTimeMillis();
    main.testMysqlData(ids);
    long stopMysql = System.currentTimeMillis();

    System.out.printf("%s:\t%d ms\n", "Redis", (stopRedis - startRedis));
    System.out.printf("%s:\t%d ms\n", "MySQL", (stopMysql - startMysql));

    main.shutdown();
}

When testing, there is a feature - data from MySQL is only read, so it can not be restarted between launches of our application. And in Redis they are written.

Although when you try to add a duplicate for the same key, the data will simply be updated, I would recommend that you run the commands to stop the container docker stop redis-stackand delete the container between application launches in the terminal docker rm redis-stack. After that, raise the container with the radish again docker run -d --name redis-stack -p 6379:6379 -p 8001:8001 redis/redis-stack:latestand only after that execute our application.

Here are my test results:

In total, we have achieved an increase in the performance of the response to the "braking frequent" request by one and a half times. And this is taking into account the fact that in testing we used not the fastest deserialization through ObjectMapper . If you change it to GSON, most likely, you can "win" a little more time.

At this moment, I remember a joke about a programmer and time: read and think about how to write and optimize your code.