SpringJPA 连接 OceanBase 数据库

<dependency>
    <groupId>com.oceanbase</groupId>
    <artifactId>oceanbase-client</artifactId>
    <version>2.4.2</version>
</dependency>
<dependency>
   <groupId>org.springframework.data</groupId>
   <artifactId>spring-data-jpa</artifactId>
    <version>1.10.1.RELEASE</version>
</dependency>


<!--spring其他依赖，此处省略。。。。。。。。。-->

<?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"
        >

    <!-- 开启IOC注解扫描 -->
    <context:component-scan base-package="com.bjyada.demo" />
    <!-- 开启MVC注解扫描 -->
    <mvc:annotation-driven />

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
          destroy-method="close">
        <!-- 连接信息 -->
        <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"/>
        <!-- 连接Idle一个小时后超时 -->
        <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>
                <!-- 外部 -->
                <!--<value>file:${user.dir}/dbcp.properties</value>-->
                <!-- 内部 -->
                <value>classpath*:dbcp.properties</value>
            </list>
        </property>
    </bean>

    <!-- 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>
                <!-- 将update改为none，禁止Hibernate每次启动都创建表
                自动创建|更新|验证数据库表结构
                validate    加载hibernate时，验证创建数据库表结构
                create    每次加载hibernate，重新创建数据库表结构
                create-drop    加载hibernate时创建，退出是删除表结构
                update      加载hibernate自动更新数据库结构-->
                <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>

    <!-- 配置事务管理器 -->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>

    <!-- 启用 annotation事务 -->
    <tx:annotation-driven transaction-manager="transactionManager" />

    <!-- 配置Spring Data JPA扫描目录 -->
    <jpa:repositories base-package="com.bjyada.demo" />
    <bean class="com.bjyada.demo.ExceptionHandler"></bean>
</beans>

#OceanBase 数据库
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

  <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>
  <!-- 其余部分略 -->

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

public class User implements Serializable {
    private Integer id;
    private String username;
    //先注释一些字段，以便验证使用框架建表后，自动根据实体类新增属性修改表
    //private Date birthday;
    //private String sex;
    //private String address;
    //。。。。。。构造器、get、set方法此处省略。。。。。。。。
}

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

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

obclient> drop table user;
Query OK, 0 rows affected
obclient› select * from user;
+----+-------------+
| id | username    |
+----+-------------+
| 1  | 测试数据     |
+----+-------------+
1 row in set

@Test
    public void testAlert(){
        User user = new User();
        user.setId(1);
        user.setUsername("测试数据");
        user.setAddress("北京");
        user.setSex("男");
        user.setBirthday(new Date());
        userDao.save(user);
    }

obclient› select * from user;
+----+-------------+
| id | username    |
+----+-------------+
| 1 | 测试数据     |
+----+-------------+
1 row in set

obclient› select * from user;
+----+-------------+---------+----------------------+------+
| id | username    | address | birthday             | sex  |
+----+-------------+---------+----------------------+------+
| 1  | 测试数据     | NULL    | NULL                 | NULL  |
| 2  | 测试数据     | 北京     | 2020-09-18 18：23：55| 男    |
+----+-------------+---------+----------------------+------+

@Test
    public void testInsert(){
        List<User> list = new ArrayList<User>();
        list.add(new User(3,"asd", new Date(), "男", "漳州"));
        list.add(new User(4,"qwe", new Date(), "女", "杭州"));
        list.add(new User(5,"zxc", new Date(), "男", "上海"));
        list.add(new User(6,"xcv", new Date(), "女", "杭州"));
        list.add(new User(7,"sdf", new Date(), "男", "杭州"));
        list.add(new User(8,"wer", new Date(), "女", "杭州"));
        list.add(new User(9,"ert", new Date(), "男", "漳州"));
        list.add(new User(10,"rty", new Date(), "女", "上海"));
        list.add(new User(11,"tyu", new Date(), "男", "杭州"));

        list.forEach(s -> userDao.save(s));
    }

+----+----------+---------+---------------------+-----+
| id | username | address | birthday            | sex |
+----+----------+---------+---------------------+-----+
| 1  | 测试数据  | NULL    | NULL                | NULL|
| 2  | 测试数据  | 北京    | 2020-09-18 18:23:55 | 男  |
| 3  | asd      | 漳州    | 2020-09-18 18:31:35 | 男  |
| 4  | qwe      | 杭州    | 2020-09-18 18:31:35 | 女  |
| 5  | zxc      | 上海    | 2020-09-18 18:31:35 | 男  |
| 6  | xcv      | 杭州    | 2020-09-18 18:31:35 | 女  |
| 7  | sdf      | 杭州    | 2020-09-18 18:31:35 | 男  |
| 8  | wer      | 杭州    | 2020-09-18 18:31:35 | 女  |
| 9  | ert      | 漳州    | 2020-09-18 18:31:35 | 男  |
| 10 | rty      | 上海    | 2020-09-18 18:31:35 | 女  |
| 11 | tyu      | 杭州    | 2020-09-18 18:31:35 | 男  |
+----+----------+---------+---------------------+-----+

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

   @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

@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);
    }

| 9996   | aaa      | aaa    | 21-SEP-20  | 010203  |
| 9999   | NULL     | NULL   | NULL       | NULL    |

@Test
    public void testChange(){
        Table_Test one = table_testDao.findOne("9996");
        System.out.println("修改前："+one);
        one.setVarchar2_test("已修改");
        one.setNchar_test("已修改");
        table_testDao.save(one);
        one = table_testDao.findOne("9996");
        System.out.println("修改后："+one);
    }

   @Test
  public void test3(){}
      Table_Test one = table_testDao. findone(id: "9996");
      System.out.println（"修改前："+one）：
      one.setVarchar2_test（"已修改"）：
      one.setNchar_tes（"已修改"）；
      table_testbao, save(one) ;
      one = table_testDao. findOne( id: "9996");
      System.out.println（"修改后: "+one);
}

✔️ Tests passed: 1 of 1 test - 187 ms
修改前: Table_Test{char_test='9996', varchar2_test='aaa', nchar_test='aaa
修改后: Table_Test{char_test='9996', varchar2_test='已修改', nchar_test='已修改'
Process finished with exit code 0

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

@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='测试数据', birthday=null, sex='null', address='null'｝
User{id=2, username='测试数据', birthday=2020-09-18 18:23:55.0, sex='男, 'address='北京'｝
User{id=3, username='asd', birthday=2020-09-18 18:31:35.0, sex='男', address='漳州'｝
User{id=4, username='qwe', birthday=2020-09-18 18:31:35.0, sex='女', address='杭州'｝
User{id=5, username='zxc', birthday=2020-09-18 18:31:35.0, sex='男', address='上海"｝
User{id=6, username='xcv', birthday=2020-09-18 18:31:35.0, sex='女', address='杭州'｝
User{id=7, username='sdf', birthday=2020-09-18 18:31:35.0, sex='男, address='杭州'｝
Useriid=8, username='wer', birthday=2020-09-18 18:31:35.0, sex='女', address='杭州'｝
User{id=9, username='ert', birthday=2020-09-18 18:31:35.0,sex='男, address='漳州'｝
User{id=10, username='rty', birthday=2020-09-18 18:31:35.0, sex='男'， address='上海'｝
User{id=11, username='tyu', birthday=2020-09-18 18:31:35.0, sex='男', address='杭州'}