Database Test - yibinericxia/documents GitHub Wiki

Use Database Tools

Use Docker

Use embedded runtime database

Custom Sequence Generator Test

We can use H2 in-memory db for testing. There are two options in testing db configuration:

  1. Use hibernate.cfg.xml under resources/ for H2 database configuration
<?xml version="1.0" encoding="UTF-8"?>
<hibernate-configuration>     
  <session-factory>
    <property name="connection.url">jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS foo</property>
    <property name="connection.username">sa</property>
    <property name="connection.password"></property>
    <property name="dialect">org.hibernate.dialect.H2Dialect</property>
    <property name="hibernate.hbm2ddl.auto">create-drop</property>
    <property name="hibernate.show_sql">false</property>
    <property name="hibernate.format_sql">true</property>
      
    <mapping class="com.company.project.db.model.Class1" />
    <mapping class="com.company.project.db.model.Class2" />
    <mapping class="com.company.project.db.model.Class3" />
    ...
  </session-factory>
</hibernate-configuration>

Along with hibernate.cfg.xml, we can have a simple code below:

    public static SessionFactory getSessionFactory() {
        final StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
                .configure() // read from hibernate.cfg.xml
                .build();
        return new MetadataSources(registry)
                .buildMetadata().buildSessionFactory();
    }
  1. Use the hibernate property file, such as hibernate-test.properties
hibernate.connection.driver_class=org.h2.Driver
hibernate.connection.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS foo
hibernate.connection.username=sa
hibernate.connection.password=
hibernate.connection.autocommit=true

hibernate.dialect=org.hibernate.dialect.H2Dialiect
hibernate.show_sql=false
hibernate.hbm2ddl.auto=create-drop
hibernate.default.schema=foo
hibernate.current_session_context_class=org.hibernate.context.internal.ThreadLocalSessionContext

We will retrieve properties from the file and configure them:

   public static SessionFactory getSessionFactory(String propertyFileName) {
        ServiceRegistry serviceRegistry = getServiceRegistry(propertyFileName);
        MetadataSources metadataSources = new MetadataSources(serviceRegistry);

        metadataSources.addPackage("com.company.project.db.model");

        metadataSources.addAnnotatedClass(Class1.class);
        metadataSources.addAnnotatedClass(Class2.class);
        metadataSources.addAnnotatedClass(Class3.class);
        ...

        Metadata metadata = metadataSources.buildMetadata();
        return metadata.getSessionFactoryBuilder().build();
   }

   public static ServiceRegistry getServiceRegistry(String propertyFileName) {
        Properties properties = getProperties();
        URL propertiesURL = Thread.currentThread()
                .getContextClassLoader()
                .getResource(StringUtils.defaultString(propertyFileName, "hibernate-test.properties"));
        try (FileInputStream is = new FileInputStream(propertiesURL.getFile())) {
            properties.load(is);
        }
        return new StandardServiceRegistryBuilder().applySettings(properties).build();
   }

JDBC Tuning

In Apache Tomcat application server the following attributes for JDBC connection pool need to be adjusted for performance

  • initialSize: the initial size of connections when the connection pool starts
  • maxActive: the max number of active connections allocated at the same time
  • minIdle: the min number of active connections established
  • maxIdle: the max number of connection kept in the pool at all times
  • timeBetweenEvictionRunsMillis: the number of milliseconds to sleep between runs of the idle connection validation/cleaner thread
  • minEvictableIdleTimeMillis: the min number of milliseconds an object may sit idle before it is eligible for eviction
  • removeAbandoned: the flag for removing abandoned connections if they exceed removeAbandonedTimeout
  • removeAbandonedTimeout: the number of seconds of timeout before a connection can be removed

Debugging

  1. Turn on the debug mode, for example, add the following in the application property file in Spring project using JPA
spring.jpa.show-sql=true

  1. Dump the HQL query string, such as query.unwrap(org.hibernate.Query.class).getQueryString() if JPA is used

  2. Catch exception locally using try/catch

Test Data Generation

Test data should not contain any sensitive information. We can generate test data from the production data with database security procedures.

Synthetic data can also be created to mock the production data, especially for initial development. How to ensure data integrity, including table relationship, functional and technical data consistency, is a big challenge for test data generation.

⚠️ **GitHub.com Fallback** ⚠️