ApsaraDB for OceanBase:Connect to an OceanBase database by using SpringJPA

Configure dependencies

<dependency>
   <groupId>com.oceanbase</groupId>
   <artifactId>oceanbase-client</artifactId>
   <version>2.4.0</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 file

applicationContext.xml

File content:

<?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 Info -->
        <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 | verifying the database table structure each time when Hibernate is loaded.
                validate: Verify the database table structure each time when Hibernate is loaded.
                create: Re-create the database table structure each time when Hibernate is loaded.
                create-drop: Create the database table structure each time when Hibernate is loaded and drop the database table structure each time when Hibernate exits.
                update: Update the database table structure 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

File content:

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

pom.xml

File content:

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
   <version>****</version>
</dependency>
<dependency>
   <groupId>com.oceanbase</groupId>
   <artifactId>oceanbase-client</artifactId>
   <version>2.4.0</version>
</dependency>

<!-- The rest is omitted. -->

application.properties

File content (on the data source and JPA part):

spring.datasource.url=jdbc:oceanbase://xxx.xxx.xxx.xxx:3306/test
spring.datasource.username=a****
spring.datasource.password=******
spring.datasource.driver-class-name=com.oceanbase.jdbc.Driver

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

Test preparation

Entity class

Sample code:

public class User implements Serializable {
    private Integer id;
    private String username;
    //Comment out some fields first to verify that the table is automatically modified according to the new properties 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);
}

Sample code

Automatic table creation

Test method:

@Test
public void testInsert(){
  User user = new User();
  user.setId(1);
  user.setUsername("Test data");
  userDao.save(user);
}

Execution results:

Modify a table (add fields)

Enable the commented-out attribute of the User class and execute the following method:

@Test
public void testAlert(){
    User user = new User();
    user.setId(1);
    user.setUsername("Test data");
    user.setAddress("Beijing");
    user.setSex("Male");
    user.setBirthday(new Date());
    userDao.save(user);
}

Execution results:

The test results show that OceanBase Database supports the Alert Table feature of SpringJPA.

Persist data

Test method:

@Test
public void testInsert(){
    List<User> list = new ArrayList<User>();
    list.add(new User(3,"asd", new Date(), "Male", "Zhangzhou"));
    list.add(new User(4,"qwe", new Date(), "Female", "Hangzhou"));
    list.add(new User(5,"zxc", new Date(), "Male", "Shanghai"));
    list.add(new User(6,"xcv", new Date(), "Female", "Hangzhou"));
    list.add(new User(7,"sdf", new Date(), "Male", "Hangzhou"));
    list.add(new User(8,"wer", new Date(), "Female", "Hangzhou"));
    list.add(new User(9,"ert", new Date(), "Male", "Zhangzhou"));
    list.add(new User(10,"rty", new Date(), "Female", "Shanghai"));
    list.add(new User(11,"tyu", new Date(), "Male", "Hangzhou"));
    
    list.forEach(s -> userDao.save(s));
}

Execution results:

The test results show that OceanBase Database supports the Insert feature of SpringJPA.

Query by primary key

Test method:

@Test
public void testFindOne(){
    Table_Test one = table_testDao.findOne("aaa");
    System.out.println(one);
}

Execution results:

The test results show that OceanBase Database supports the findOne feature of SpringJPA.

Delete records by primary key or object

Test method:

@Test
public void testDelete(){
    table_testDao.delete("9998");
}

@Test
public void test6(){
    Table_Test a = new Table_Test();
    a.setChar_test("9997");
    table_testDao.delete(a);
}

Execution results:

The test results show that OceanBase Database supports the Delete feature of SpringJPA.

Modify records

Test method:

@Test
public void testChange(){
    Table_Test one = table_testDao.findOne("9996");
    System.out.println("Before modification: "+one);
    one.setVarchar2_test("Modified");
    one.setNchar_test("Modified");
    table_testDao.save(one);
    one = table_testDao.findOne("9996");
    System.out.println("After modification: "+one);
}

Execution results:

The test results show that OceanBase Database supports the data modification feature of SpringJPA.

Query a whole table

Test method:

@Test
public void testFindAll(){
    List<User> all = userDao.findAll();
    all.forEach(System.out::println);
}

Execution results:

The test results show that OceanBase Database supports the FindAll feature of SpringJPA.