Connect to OceanBase Cloud by using Spring Data JPA|OceanBase Cloud |docs|OceanBase Documentations

This topic provides a Spring Data JPA connection example and tests the performance of several general Spring Data JPA features against Oracle.

Configure dependencies

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.25</version>
</dependency>
<dependency>
   <groupId>org.springframework.data</groupId>
   <artifactId>spring-data-jpa</artifactId>
   <version>1.10.1.RELEASE</version>
</dependency>


<!--Other Spring dependencies, which are omitted here.-->

Configuration files

applicationContext.xml

The file content is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:jpa="http://www.springframework.org/schema/data/jpa"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
         http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
         http://www.springframework.org/schema/context
         http://www.springframework.org/schema/context/spring-context-4.2.xsd
         http://www.springframework.org/schema/tx
         http://www.springframework.org/schema/tx/spring-tx-4.2.xsd
         http://www.springframework.org/schema/aop
         http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
         http://www.springframework.org/schema/mvc
         http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd
         http://www.springframework.org/schema/data/jpa
         http://www.springframework.org/schema/data/jpa/spring-jpa.xsd"
        >

    <!-- Enable IOC annotation scan -->
    <context:component-scan base-package="com.bjyada.demo" />
    <!-- Enable MVC annotation scan -->
    <mvc:annotation-driven />

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
          destroy-method="close">
        <!-- Connection information -->
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>

        <!-- Connection pooling information -->
        <property name="maxActive" value="${dbcp.maxActive}"/>
        <property name="maxIdle" value="${dbcp.maxIdle}"/>
        <property name="defaultAutoCommit" value="true"/>
        <!-- Evict a session that has been idle for one hour -->
        <property name="timeBetweenEvictionRunsMillis" value="3600000"/>
        <property name="minEvictableIdleTimeMillis" value="3600000"/>
    </bean>

    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE"/>
        <property name="ignoreResourceNotFound" value="true"/>
        <property name="locations">
            <list>
                <!-- External -->
                <!--<value>file: ${user.dir}/dbcp.properties</value>-->
                <!-- Internal -->
                <value>classpath*:dbcp.properties</value>
            </list>
        </property>
    </bean>

    <!-- Configure JPA Entity Manager -->
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="jpaVendorAdapter" ref="hibernateJpaVendorAdapter"/>
        <property name="packagesToScan" value="com.bjyada.demo.entity"/>
        <property name="persistenceUnitName" value="primary"/>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
                <!-- Change update to none to prevent Hibernate from creating tables and
                automatically creating | updating | validating the schema each time when Hibernate is loaded.
                validate: validates the schema each time when Hibernate is loaded.
                create: creates the schema each time when Hibernate is loaded.
                create-drop: creates the schema each time when Hibernate is loaded and drops the schema each time when Hibernate exits.
                update: updates the schema each time when Hibernate is loaded.-->
                <prop key="hibernate.hbm2ddl.auto">update</prop>
                <prop key="hibernate.show_sql">false</prop>
                <prop key="hibernate.format_sql">false</prop>
                <prop key="hibernate.temp.use_jdbc_metadata_defaults">false</prop>
            </props>
        </property>
    </bean>

    <bean id="hibernateJpaVendorAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="database" value="${database.dialect}"/>
    </bean>

    <!-- Configure the transaction manager -->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>

    <!-- Enable annotation transactions -->
    <tx:annotation-driven transaction-manager="transactionManager" />

    <!-- Configure the Spring Data JPA scan directory -->
    <jpa:repositories base-package="com.bjyada.demo" />
    <bean class="com.bjyada.demo.ExceptionHandler"></bean>
</beans>

dbcp.properties

The file content is as follows:

# OceanBase Database
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test
jdbc.username=admin
jdbc.password=******
database.dialect=MYSQL
dbcp.maxIdle=5
dbcp.maxActive=40
useUnicode=true&characterEncoding=utf-8

pom.xml

The file content is as follows:

  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
      <version>****</version>
  </dependency>
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.25</version>
  </dependency>
  <!-- The rest is omitted. -->

application.properties

The following example shows the content of the file (including only database source and JPA configurations):

spring.datasource.url=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test
spring.datasource.username=admin
spring.datasource.password=******
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.Oracle12cDialect

Notice

In the Oracle compatible mode, set spring.jpa.properties.hibernate.dialect to org.hibernate.dialect.Oracle12cDialect. In the MySQL compatible mode, set the parameter value to org.hibernate.dialect.MySQL5Dialect instead, or leave this parameter unspecified.

Test preparations

Entity class

The relevant code is as follows:

public class User implements Serializable {
    private Integer id;
    private String username;
    // Comment out some fields first to verify that the table is automatically modified based on the new attributes of the entity class after the table is created based on the framework.
    //private Date birthday;
    //private String sex;
    //private String address;
    //The constructor and the get and set methods are omitted here.
}

Database access interface

public interface UserDao extends JpaRepository<User,Serializable>{
    User findById(Integer id);
}