This topic provides a Spring JDBC connection example.
Configure dependencies
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.9.RELEASE</version>
</dependency>
Sample code
Preload static code blocks. To facilitate testing, a Druid connection pool is used in this example.
static {
Map<String, String> map = new HashMap<String, String>();
map.put("url", "jdbc:mysql://10.100.xxx.xxx:18815/test");
map.put("driverClassName", "com.mysql.cj.jdbc.Driver");
map.put("username", "admin@oracle");
map.put("password", "admin");
try {
Class.forName(map.get("driverClassName"));
jdbcTemplate = new JdbcTemplate(DruidDataSourceFactory.createDataSource(map));
// These statements avoid errors. Without these two statements, errors may occur.
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+",'Test data"+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);
}
}