This topic provides a Spring Data JPA connection example and tests the performance of several general Spring Data JPA features against Oracle.
Configuration 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 following example shows the content of the 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 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 following example shows the content of the file:
# OceanBase Database
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://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 file:
<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://10.100.xxx.xxx:18815/test
spring.datasource.username=admin@oracle
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 mode of OceanBase Database, set spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle12cDialect. In the 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 based on 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);
}
The execution result is as follows:
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 columns)
Uncomment the commented-out attribute in 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);
}
The execution result is as follows:
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));
}
The execution result is as follows:
+----+----------+---------+---------------------+------+
| 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);
}
The execution result is as follows:
@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);
}
The execution result is as follows:
| 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);
}
The execution result is as follows:
@Test
public void test3(){}
Table_Test one = table_testDao. findone(id: "9996");
System.out.println("Before: "+one):
one.setVarchar2_test("Modified"):
one.setNchar_test("Modified");
table_testbao, save(one);
one = table_testDao. findOne( id: "9996");
System.out.println("After: "+one);
}
✔️ Tests passed: 1 of 1 test - 187 ms
Before: Table_Test{char_test='9996', varchar2_test='aaa', nchar_test='aaa
After: 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);
}
The execution result is as follows:
@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.