ClusterJ - working with MySQL NDB Cluster from Java

Hello, Habr! In this article I want to consider a library for Java such as ClusterJ



, which makes it very easy to work with the MySQL NDBCLUSTER



engine from Java



code, which is a high-level API similar in concept to JPA



and Hibernate



.

In the framework of this article, we will create a simple application on SpringBoot



, and also make a starter with ClusterJ



on board for convenient use in applications using autoconfiguration. We will write simple tests using JUnit5



and TestContainers



, which will show the basic use of the API.

I will also talk about several shortcomings that I had to face in the process of working with her.

Who cares, welcome to cat.

Introduction

MySQL NDB Cluster



actively used at work, and in one of the projects, for the sake of speed, the task was to use the ClusterJ



library instead of the usual JDBC



, which in its API is very similar to JPA



, and, in fact, it is a wrapper over the libndbclient.so



library that it uses through JNI



.

For those who are not in the know, MySQL NDB Cluster is a highly accessible and redundant MySQL version adapted for a distributed computing environment that uses the NDB



storage NDB



( NDBCLUSTER



) to operate in a cluster. I don’t want to dwell on this here in detail, you can read more here and here

There are two ways to work from Java code with this database:

• Standard, through JDBC
  
  
  
  and SQL
  
  
  
  queries
• Via ClusterJ
  
  
  
  , for high-performance data access in the MySQL Cluster
  
  
  
  .

ClusterJ is built around 4 key concepts:

• SessionFactory
  
  
  
  - an analogue of the connection pool, used to get a session. Each cluster instance must have its own SessionFactory.
• Session
  
  
  
  - is a direct connection to a MySQL
  
  
  
  cluster.
• Domain Object
  
  
  
  - An annotated interface representing a mapping of a table into Java
  
  
  
  code, similar to JPA
  
  
  
  .
• Transaction
  
  
  
  - is an atomic unit of work. At any given time, in one session, one transaction is executed. Any operation (receive, insert, update, delete) is performed in a new transaction.

ClusterJ limitations:

• Lack of JOINs
• There is no way to create a table and indexes. To do this, use JDBC
  
  
  
  .
• No delayed loading ( Lazy
  
  
  
  ). The entire record is downloaded at one time.
• In domain objects, it is not possible to define relationships between tables. The similarity of OneToMany
  
  
  
  , ManyToOne
  
  
  
  , ManyToMany
  
  
  
  completely absent.

Practice. Talk is cheap. Show me the code.

Well, enough theory, let's move on to practice.

The first problem to be faced is the lack of ClusterJ



in the central Maven repository. Install the library with pens in the local repository. It is clear that for good, it should lie in Nexus



or some Artifactory



, but for our example this is unnecessary.

So, go here and choose your operating system. If you are on a Linux



like OS, download the package called mysql-cluster-community-java



and install the given rpm / deb package. If you have Windows



, download the full mysql-cluster-gp



archive.

One way or another, we will have a jar file of the form: clusterj-{version}.jar



. We put it through maven



:

 mvn install:install-file -DgroupId=com.mysql.ndb -DartifactId=clusterj -Dversion={version} -Dpackaging=jar -Dfile=clusterj-{version}.jar -DgeneratePom=true
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

We also need the libndbclient



library, which is a set of C++



functions for working with the NDB API



that ClusterJ



calls through the JNI



. For Windows



this library (.dll) is in the mysql-cluster-gp



archive; for Linux



you need to download the ndbclient_{version}



package.

Next, create a project. We will use SpringBoot



, JUnit5



+ TestContainers



for tests.

The project consists of two modules:

• clusterj-spring-boot-starter
  
  
  
  is a starter that contains ClusterJ
  
  
  
  , as well as atoconfiguration. Thanks to this starter, we can describe the connection to MySQL NDB
  
  
  
  in our appliation.yml
  
  
  
  file as follows:

 clusterj: connectString: localhost:1186 dataBaseName: NDB_DB
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

After that, SpringBoot



will create for us the necessary SessionFactory



factory for the connection.

• clusterj-app
  
  
  
  is the application itself, which our starter will use. Let us dwell on it in more detail.

To get started, we need to create a domain model, like JPA



. Only in this case we need to do this in the form of an interface, the implementation of which in clusterj



will be clusterj



:

 import com.mysql.clusterj.annotation.Column; import com.mysql.clusterj.annotation.PersistenceCapable; import com.mysql.clusterj.annotation.PrimaryKey; @PersistenceCapable(table = "user") public interface User { @PrimaryKey int getId(); void setId(int id); @Column(name = "firstName") String getFirstName(); void setFirstName(String firstName); @Column(name = "lastName") String getLastName(); void setLastName(String lastName); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

There is a problem right away. The PersistenceCapable



annotation has the ability to specify the name of the schema or database in which the table lies, however this does not work. Absolutely. In ClusterJ



this is not implemented. Therefore, all the tables that are working through ClusterJ



should be in the same schema, which results in a dump of tables that logically should be in different schemas.

Now let's try to use this interface. To do this, we write a simple test.

In order not to bother with installing MySQL Cluster



, we will use the wonderful library for integration testing TestContainers and Docker . Since we are using JUnit5 we will write a simple Extension



:

 import com.github.dockerjava.api.model.Network; import lombok.extern.slf4j.Slf4j; import org.junit.jupiter.api.extension.Extension; import org.testcontainers.containers.BindMode; import org.testcontainers.containers.GenericContainer; import org.testcontainers.containers.wait.strategy.Wait; import org.testcontainers.shaded.com.google.common.collect.ImmutableMap; import java.time.Duration; import java.util.stream.Stream; @Slf4j class MySQLClusterTcExtension implements Extension { private static final String MYSQL_USER = "sys"; private static final String MYSQL_PASSWORD = "qwerty"; private static final String CLUSTERJ_DATABASE = "NDB_DB"; private static Network.Ipam getIpam() { Network.Ipam ipam = new Network.Ipam(); ipam.withDriver("default"); Network.Ipam.Config config = new Network.Ipam.Config(); config.withSubnet("192.168.0.0/16"); ipam.withConfig(config); return ipam; } private static org.testcontainers.containers.Network network = org.testcontainers.containers.Network.builder() .createNetworkCmdModifier(createNetworkCmd -> createNetworkCmd.withIpam(getIpam())) .build(); private static GenericContainer ndbMgmd = new GenericContainer<>("mysql/mysql-cluster") .withNetwork(network) .withClasspathResourceMapping("mysql-cluster.cnf", "/etc/mysql-cluster.cnf", BindMode.READ_ONLY) .withClasspathResourceMapping("my.cnf", "/etc/my.cnf", BindMode.READ_ONLY) .withCreateContainerCmdModifier(createContainerCmd -> createContainerCmd.withIpv4Address("192.168.0.2")) .withCommand("ndb_mgmd") .withExposedPorts(1186) .waitingFor(Wait.forListeningPort().withStartupTimeout(Duration.ofSeconds(150))); private static GenericContainer ndbd1 = new GenericContainer<>("mysql/mysql-cluster") .withNetwork(network) .withClasspathResourceMapping("mysql-cluster.cnf", "/etc/mysql-cluster.cnf", BindMode.READ_ONLY) .withClasspathResourceMapping("my.cnf", "/etc/my.cnf", BindMode.READ_ONLY) .withCreateContainerCmdModifier(createContainerCmd -> createContainerCmd.withIpv4Address("192.168.0.3")) .withCommand("ndbd"); private static GenericContainer ndbMysqld = new GenericContainer<>("mysql/mysql-cluster") .withNetwork(network) .withCommand("mysqld") .withCreateContainerCmdModifier(createContainerCmd -> createContainerCmd.withIpv4Address("192.168.0.10")) .withClasspathResourceMapping("mysql-cluster.cnf", "/etc/mysql-cluster.cnf", BindMode.READ_ONLY) .withClasspathResourceMapping("my.cnf", "/etc/my.cnf", BindMode.READ_ONLY) .waitingFor(Wait.forListeningPort()) .withEnv(ImmutableMap.of("MYSQL_DATABASE", CLUSTERJ_DATABASE, "MYSQL_USER", MYSQL_USER, "MYSQL_PASSWORD", MYSQL_PASSWORD)) .withExposedPorts(3306) .waitingFor(Wait.forListeningPort()); static { log.info("Start MySQL Cluster testcontainers extension...\n"); Stream.of(ndbMgmd, ndbd1, ndbMysqld).forEach(GenericContainer::start); String ndbUrl = ndbMgmd.getContainerIpAddress() + ":" + ndbMgmd.getMappedPort(1186); String mysqlUrl = ndbMysqld.getContainerIpAddress() + ":" + ndbMysqld.getMappedPort(3306); String mysqlConnectionString = "jdbc:mysql://" + mysqlUrl + "/" + CLUSTERJ_DATABASE + "?useUnicode=true" + "&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false"; System.setProperty("clusterj.connectString", ndbUrl); System.setProperty("clusterj.dataBaseName", CLUSTERJ_DATABASE); System.setProperty("spring.datasource.username", MYSQL_USER); System.setProperty("spring.datasource.password", MYSQL_PASSWORD); System.setProperty("spring.datasource.url", mysqlConnectionString); } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

In this Extension, we raise the control node of the cluster, one date for the node, and the MySQL



node. After that, we set the appropriate connection settings for use by SpringBoot, just those that we described in the starter auto-configuration:

 System.setProperty("clusterj.connectString", ndbUrl); System.setProperty("clusterj.dataBaseName", CLUSTERJ_DATABASE); System.setProperty("spring.datasource.username", MYSQL_USER); System.setProperty("spring.datasource.password", MYSQL_PASSWORD); System.setProperty("spring.datasource.url", mysqlConnectionString);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Next, we write an annotation that will allow us to declaratively raise containers in tests. Everything is very simple here, we use our Extension:

 @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.TYPE) @ExtendWith(MySQLClusterTcExtension.class) public @interface EnableMySQLClusterContainer { }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Finally, we write the test:

 @Test void shouldGetUserViaClusterJ() { User newUser = session.newInstance(User.class); newUser.setId(1); newUser.setFirstName("John"); newUser.setLastName("Jonson"); session.persist(newUser); User userFromDb = session.find(User.class, 1); assertAll( () -> assertEquals(userFromDb.getId(), 1), () -> assertEquals(userFromDb.getFirstName(), "John"), () -> assertEquals(userFromDb.getLastName(), "Jonson")); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

This test shows how we can get the record by primary key. This query is equivalent to SQL



query:

 SELECT * FROM user WHERE id = 1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Let's do another test, with more complex logic:

 @Test void queryBuilderTest() { QueryBuilder builder = session.getQueryBuilder(); QueryDomainType<User> userQueryDomainType = builder.createQueryDefinition(User.class); // parameter PredicateOperand propertyIdParam = userQueryDomainType.param("lastName"); // property PredicateOperand propertyEntityId = userQueryDomainType.get("lastName"); userQueryDomainType.where(propertyEntityId.equal(propertyIdParam)); Query<User> query = session.createQuery(userQueryDomainType); query.setParameter("lastName", "Jonson"); List<User> foundEntities = query.getResultList(); Optional<User> firstUser = foundEntities.stream().filter(u -> u.getId() == 1).findFirst(); Optional<User> secondUser = foundEntities.stream().filter(u -> u.getId() == 2).findFirst(); assertAll( () -> assertEquals(foundEntities.size(), 2), () -> assertTrue(firstUser.isPresent()), () -> assertTrue(secondUser.isPresent()), () -> assertThat(firstUser.get(), allOf( hasProperty("firstName", equalTo("John")), hasProperty("lastName", equalTo("Jonson")) ) ), () -> assertThat(secondUser.get(), allOf( hasProperty("firstName", equalTo("Alex")), hasProperty("lastName", equalTo("Jonson")) ) ) ); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

QueryBuilder



used to build complex queries with in



, where



, equal



, like



QueryBuilder



. In this test, we pull out all users whose last name = Jonson. This query is equivalent to the following SQL



:

 SELECT * FROM user WHERE lastName = 'Jonson';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Here, too, ran into a problem. Unable to compile a query of the form

 SELECT * FROM user WHERE (lastName = 'Jonson' and firstName = 'John') or id = 2;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

This feature is not currently implemented. You can see the test: andOrNotImplemented



.

 @SpringBootTest @ExtendWith(SpringExtension.class) @EnableAutoConfiguration @EnableMySQLClusterContainer class NdbClusterJTest { @Autowired private JdbcTemplate jdbcTemplate; @Autowired private SessionFactory sessionFactory; private Session session; @BeforeEach void setUp() { jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `user` (id INT NOT NULL PRIMARY KEY," + " firstName VARCHAR(64) DEFAULT NULL," + " lastName VARCHAR(64) DEFAULT NULL) ENGINE=NDBCLUSTER;"); session = sessionFactory.getSession(); } @Test void shouldGetUserViaClusterJ() { User newUser = session.newInstance(User.class); newUser.setId(1); newUser.setFirstName("John"); newUser.setLastName("Jonson"); session.persist(newUser); User userFromDb = session.find(User.class, 1); assertAll( () -> assertEquals(userFromDb.getId(), 1), () -> assertEquals(userFromDb.getFirstName(), "John"), () -> assertEquals(userFromDb.getLastName(), "Jonson")); } @Test void queryBuilderTest() { User newUser1 = session.newInstance(User.class); newUser1.setId(1); newUser1.setFirstName("John"); newUser1.setLastName("Jonson"); User newUser2 = session.newInstance(User.class); newUser2.setId(2); newUser2.setFirstName("Alex"); newUser2.setLastName("Jonson"); session.persist(newUser1); session.persist(newUser2); QueryBuilder builder = session.getQueryBuilder(); QueryDomainType<User> userQueryDomainType = builder.createQueryDefinition(User.class); // parameter PredicateOperand propertyIdParam = userQueryDomainType.param("lastName"); // property PredicateOperand propertyEntityId = userQueryDomainType.get("lastName"); userQueryDomainType.where(propertyEntityId.equal(propertyIdParam)); Query<User> query = session.createQuery(userQueryDomainType); query.setParameter("lastName", "Jonson"); List<User> foundEntities = query.getResultList(); Optional<User> firstUser = foundEntities.stream().filter(u -> u.getId() == 1).findFirst(); Optional<User> secondUser = foundEntities.stream().filter(u -> u.getId() == 2).findFirst(); assertAll( () -> assertEquals(foundEntities.size(), 2), () -> assertTrue(firstUser.isPresent()), () -> assertTrue(secondUser.isPresent()), () -> assertThat(firstUser.get(), allOf( hasProperty("firstName", equalTo("John")), hasProperty("lastName", equalTo("Jonson")) ) ), () -> assertThat(secondUser.get(), allOf( hasProperty("firstName", equalTo("Alex")), hasProperty("lastName", equalTo("Jonson")) ) ) ); } @Test void andOrNotImplemented() { QueryBuilder builder = session.getQueryBuilder(); QueryDomainType<User> userQueryDomainType = builder.createQueryDefinition(User.class); // parameter PredicateOperand firstNameParam = userQueryDomainType.param("firstName"); // property PredicateOperand firstName = userQueryDomainType.get("firstName"); // parameter PredicateOperand lastNameParam = userQueryDomainType.param("lastName"); // property PredicateOperand lastName = userQueryDomainType.get("lastName"); // parameter PredicateOperand idParam = userQueryDomainType.param("id"); // property PredicateOperand id = userQueryDomainType.get("id"); Executable executable = () -> userQueryDomainType.where(firstNameParam.equal(firstName) .and(lastNameParam.equal(lastName)) .or(idParam.equal(id))); UnsupportedOperationException exception = assertThrows(UnsupportedOperationException.class, executable); assertEquals("Not implemented.", exception.getMessage()); } @AfterEach void tearDown() { session.deletePersistentAll(User.class); session.close(); } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Thanks to our annotation @EnableMySQLClusterContainer



, we hid the details of preparing the environment for tests. Also, thanks to our starter, we can simply inject SessionFactory into our test, and use it for our needs, without worrying about the fact that it needs to be created manually.

All this concentrates us on writing the business logic of the tests, rather than the serving infrastructure.

I also want to pay attention to the fact that you need to run an application that uses ClusterJ



with the parameter:

 -Djava.library.path=/usr/lib/x86_64-linux-gnu/
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

which shows the path to libndbclient.so



. Without it, nothing will work.

Conclusion

As for me, ClusterJ



good thing in those systems that are critical to data access speed, but minor flaws and limitations spoil the overall impression. If you have the opportunity to choose and you do not care about the speed of access, I think it is better to use JDBC



.

The article did not consider working with transactions and locks, and so it turned out quite a lot.

That's it, Happy Coding!

Useful links:

All code with the project lies here

Download Page

Information about ClusterJ

Work with Java and NDB Cluster

Pro MySQL NDB Cluster Book

More about MySQL NDB Cluster here and here

More test examples in MySQL



repository itself