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>com.alipay.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 files
applicationContext.xml
The following example shows the content of the configuration file:
<?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
The following example shows the content of the configuration file:
# OceanBase Database
jdbc.driver=com.alipay.oceanbase.jdbc.Driver
jdbc.url=jdbc:oceanbase://10.100.xxx.xxx:18815/test
jdbc.username=admin
jdbc.password=******
database.dialect=MYSQL
dbcp.maxIdle=5
dbcp.maxActive=40
useUnicode=true&characterEncoding=utf-8
pom.xml
The following example shows the content of the configuration file:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>****</version>
</dependency>
<dependency>
<groupId>com.alipay.oceanbase</groupId>
<artifactId>oceanbase-client</artifactId>
<version>2.4.0</version>
</dependency>
<!-- The rest is omitted. -->
application.properties
Configure the data source and JPA part in the configuration file as follows:
spring.datasource.url=jdbc:oceanbase://10.100.xxx.xxx:18815/test
spring.datasource.username=admin@oracle
spring.datasource.password=******
spring.datasource.driver-class-name=com.alipay.oceanbase.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.Oracle12cDialect
Notice
In Oracle mode, set spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle12cDialect. In MySQL mode, set the value to org.hibernate.dialect.MySQL5Dialect instead, or leave this parameter unconfigured.
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 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
The test method is as follows:
@Test
public void testInsert(){
User user = new User();
user.setId(1);
user.setUsername("Test data");
userDao.save(user);
}
Here is the execution result:
obclient> drop table user;
Query OK, 0 rows affected
obclient› select * from user;
+----+-------------+
| id | username |
+----+-------------+
| 1 | Test data |
+----+-------------+
1 row in set
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);
}
Here is the execution result:
obclient› select * from user;
+----+-------------+
| id | username |
+----+-------------+
| 1 | Test data |
+----+-------------+
1 row in set
obclient› select * from user;
+----+-------------+---------+----------------------+------+
| id | username | address | birthday | sex |
+----+-------------+---------+----------------------+------+
| 1 | Test data | NULL | NULL | NULL |
| 2 | Test data | Beijing | 2020-09-18 18:23:55 | Male|
+----+-------------+---------+----------------------+------+
The test result shows that OceanBase Database supports the Alter Table feature of Spring Data JPA.
Persist data
The test method is as follows:
@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));
}
Here is the execution result:
+----+----------+---------+---------------------+------+
| id | username | address | birthday | sex |
+----+----------+---------+---------------------+------+
| 1 |Test data | NULL | NULL | NULL |
| 2 |Test data | Beijing | 2020-09-18 18:23:55 | Male |
| 3 | asd |Zhangzhou| 2020-09-18 18:31:35 | Male |
| 4 | qwe | Hangzhou| 2020-09-18 18:31:35 |Female|
| 5 | zxc | Shanghai| 2020-09-18 18:31:35 | Male |
| 6 | xcv | Hangzhou| 2020-09-18 18:31:35 |Female|
| 7 | sdf | Hangzhou| 2020-09-18 18:31:35 | Male |
| 8 | wer | Hangzhou| 2020-09-18 18:31:35 |Female|
| 9 | ert |Zhangzhou| 2020-09-18 18:31:35 | Male |
| 10 | rty | Shanghai| 2020-09-18 18:31:35 |Female|
| 11 | tyu | Hangzhou| 2020-09-18 18:31:35 | Male |
+----+----------+---------+---------------------+------+
The test result shows that OceanBase Database supports the Insert feature of Spring Data JPA.
Query by primary key
The test method is as follows:
@Test
public void testFindOne(){
Table_Test one = table_testDao.findOne("aaa");
System.out.println(one);
}
Here is the execution result:
@Test
public void test2(){
Table_Test one = table_testDao. findOne(id: "aaa");
System.out.println(one);
}
✔️ Tests passed: 1 of 1 test - 87 ms
Table_Test {char_test='aaa', varchar2_test='aaa', nchar_ test=' aaa'
Process finished with exit code 0
The test result shows that OceanBase Database supports the findOne feature of Spring Data JPA.
Delete records by primary key or object
The test method is as follows:
@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);
}
Here is the execution result:
| 9996 | aaa | aaa | 21-SEP-20 | 010203 |
| 9999 | NULL | NULL | NULL | NULL |
The test result shows that OceanBase Database supports the Delete feature of Spring Data JPA.
Modify records
The test method is as follows:
@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);
}
Here is the execution result:
@Test
public void test3(){}
Table_Test one = table_testDao. findone(id: "9996");
System.out.println("Before modification: "+one):
one.setVarchar2_test("Modified"):
one.setNchar_tes("Modified");
table_testbao, save(one) ;
one = table_testDao. findOne( id: "9996");
System.out.println("After modification: "+one);
}
✔️ Tests passed: 1 of 1 test - 187 ms
Before modification: Table_Test{char_test='9996', varchar2_test='aaa', nchar_test='aaa
After modification: Table_Test{char_test='9996', varchar2_test='Modified', nchar_test='Modified'
Process finished with exit code 0
The test result shows that OceanBase Database supports the data modification feature of Spring Data JPA.
Query a whole table
The test method is as follows:
@Test
public void testFindAll(){
List<User> all = userDao.findAll();
all.forEach(System.out::println);
}
Here is the execution result:
@Test
public void testFindAll(){
List<User> all = userDao.findAllO;
all.forEach(System.out::println);
｝
✔️ Tests passed: 1 of 1 test - 235 ms
INFO: HHH000232: Schema update complete
User{id=1, username='Test data', birthday=null, sex='null', address='null'｝
User{id=2, username='Test data', birthday=2020-09-18 18:23:55.0, sex='Male, 'address='Beijing'｝
User{id=3, username='asd', birthday=2020-09-18 18:31:35.0, sex='Male', address='Zhangzhou'｝
User{id=4, username='qwe', birthday=2020-09-18 18:31:35.0, sex='Female', address='Hangzhou'｝
User{id=5, username='zxc', birthday=2020-09-18 18:31:35.0, sex='Male', address='Shanghai"｝
User{id=6, username='xcv', birthday=2020-09-18 18:31:35.0, sex='Female', address='Hangzhou'｝
User{id=7, username='sdf', birthday=2020-09-18 18:31:35.0, sex='Male, address='Hangzhou'｝
Useriid=8, username='wer', birthday=2020-09-18 18:31:35.0, sex='Female', address='Hangzhou'｝
User{id=9, username='ert', birthday=2020-09-18 18:31:35.0,sex='Male, address='Zhangzhou'｝
User{id=10, username='rty', birthday=2020-09-18 18:31:35.0, sex='Male', address='Shanghai'｝
User{id=11, username='tyu', birthday=2020-09-18 18:31:35.0, sex='Male', address='Hangzhou'}
The test result shows that OceanBase Database supports the FindAll feature of Spring Data JPA.