OceanBase学习记录3|SpringJDBC 连接示例

本文介绍 SpringJDBC 连接示例。

配置依赖

<dependency>
   <groupId>com.alipay.oceanbase</groupId>
   <artifactId>oceanbase-client</artifactId>
   <version>3.2.3</version>
</dependency>
<dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>5.0.9.RELEASE</version>
</dependency>

示例代码

预加载静态代码块(为方便测试，此处使用 druid 连接池)：

static {
        Map<String, String> map = new HashMap<String, String>();
        map.put("url", "jdbc:oceanbase://10.100.xxx.xxx:18815/test");
        map.put("driverClassName", "com.alipay.oceanbase.jdbc.Driver");
        map.put("username", "admin@oracle");
        map.put("password", "admin");
        try {
            Class.forName(map.get("driverClassName"));
            jdbcTemplate = new JdbcTemplate(DruidDataSourceFactory.createDataSource(map));
            //防止异常语句,没有这两句，会出错
            jdbcTemplate.execute("set transaction_isolation = 'READ-COMMITTED';");
            jdbcTemplate.execute("set tx_isolation = 'READ-COMMITTED';");
        } catch (Exception e) {
            e.printStackTrace();
        }
}

 @Test
    public void createByOrcTypeDate(){
        sql ="create table D_DPRECORD(DEV_ID VARCHAR2(50),"+
             "CAR_SPEED NUMBER(3),"+
             "CAP_DATE TIMESTAMP WITH LOCAL TIME ZONE," +
             "DEV_CHNID VARCHAR2(50) not null," +
             "TRSFMARK NUMBER(1) default 0," +
             "CREATE_TIME DATE default sysdate" +
             ");";
        jdbcTemplate.execute(sql);
    }

 @Test
    public void addTest(){
        int i = 1;
        for (;i<=100;i++){
            sql = "insert into orc_type_test values ("+i+",'测试数据"+i+"');";
            jdbcTemplate.execute(sql);
        }
    } 

 @Test
    public void selectTest(){
        sql = "select * from orc_type_test;";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        for (Map<String,Object> m : maps){
            System.out.println(m);
        }
    }

 @Test
    public void rownumQueryTest() {
        sql = "select * from D_DPRECORD where rownum <=70 minus (select * from D_DPRECORD where rownum<60);";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> m : maps) {
            System.out.println(m);
        }
    }