使用spring框架的JdbcTemplate实现对Oracle数据库的简单操作实例

时间:2023-03-09 15:50:05
使用spring框架的JdbcTemplate实现对Oracle数据库的简单操作实例

  最近实现了一个小功能,针对Oracle数据库两张关联表进行查询和修改,因为比较简单,所以选择了spring框架里的JdbcTemplate。JdbcTemplate算是老古董了,是当年spring为了封装JDBC对数据库繁琐的操作而搞出来一个模板,相比起spring框架后来搞的JPA就显得比较low了。但技术只有合适的,没有最好的。这次因为时间紧,功能简单,使用JPA反而有点杀猪用牛刀了。直接上例子:

  maven项目,当然先把依赖配置好了,spring框架的几个需要的jar包,这里只贴出来pom.xml里的依赖节点

    <dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>4.3.12.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<dependency>
<groupId>com.vividsolutions</groupId>
<artifactId>jts</artifactId>
<version>1.8</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc14</artifactId>
<version>10.2.0.2.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.quartz-scheduler</groupId>
<artifactId>quartz</artifactId>
<version>2.3.0</version>
</dependency>
</dependencies>

  JdbcTemplate在spring-jdbc这个包里,如果需要用到事务,那么再引入一个tx包,我这里比较简单,无须用事务。commons-dbcp和ojdbc14这两个包是用来连接oracle数据库用的。接下来看spring的配置文件

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <context:annotation-config /> <bean id="PropertyConfig" class="com.inspur.chinanet.point.util.PropertiesConfigUtil">
<property name="locations">
<list>
<value>classpath:global.properties</value>
</list>
</property>
<property name="fileEncoding">
<value>UTF-8</value>
</property>
<property name="ignoreResourceNotFound" value="true" />
<property name="ignoreUnresolvablePlaceholders" value="true" />
</bean> <bean id="jdbcImpl" class="com.inspur.chinanet.point.dao.JdbcImpl" /> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${driver.class}" />
<property name="url" value="${driver.url}" />
<property name="username" value="${db.username}" />
<property name="password" value="${db.password}" />
</bean> <!-- 使用MethodInvokingJobDetailFactoryBean,任务类可以不实现Job接口,通过targetMethod指定调用方法 -->
<bean id="taskJob" class="com.inspur.chinanet.point.task.CirclePointTask" />
<bean id="jobDetail"
class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
<property name="group" value="circle_point_group" />
<property name="name" value="circle_point_update" />
<!--false表示等上一个任务执行完后再开启新的任务 -->
<property name="concurrent" value="false" />
<property name="targetObject">
<ref bean="taskJob" />
</property>
<property name="targetMethod">
<value>run</value>
</property>
</bean> <!-- 调度触发器 -->
<bean id="myTrigger"
class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
<property name="name" value="circle_point_update" />
<property name="group" value="circle_point_group" />
<property name="jobDetail">
<ref bean="jobDetail" />
</property>
<property name="cronExpression">
<value>0 11 13 ? * *</value>
</property>
</bean> <!-- 调度工厂 -->
<bean id="scheduler"
class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
<property name="triggers">
<list>
<ref bean="myTrigger" />
</list>
</property>
</bean>
</beans>

  这里先配置数据库的连接参数dataSource,然后把JdbcTemplate作为一个bean加载到spring容器中,然后我们就可以在DAO里执行数据库操作了。这里按spring提倡的面向接口编程,提供一个DAO的接口和实现类

package com.inspur.chinanet.point.dao;

import java.sql.Date;
import java.util.List;
import java.util.Map; /**
* 数据库增删查改接口
*
* @author wulinfeng
* @version C10 2018年1月25日
* @since SDP V300R003C10
*/
public interface JdbcInterface
{
/**
* 查询所有多边形
*
* @author wulinfeng
* @return
*/
public List<Map<String, Object>> queryCircles(); /**
* 根据日志查询点
*
* @author wulinfeng
* @return
*/
public List<Map<String, Object>> queryPoints(Date orderDate); /**
* 查询每一个点
*
* @author wulinfeng
* @return
*/
public List<Map<String, Object>> querySinglePoint(Date orderDate, String openNumber); /**
* 更新点所对应的多边形编码
*
* @author wulinfeng
* @param circleId
*/
public int updateCircleIdToPoint(Date orderDate, String openNumber, String longitude, String latitude,
String gridId); }

  实现类

package com.inspur.chinanet.point.dao;

import java.sql.Date;
import java.util.List;
import java.util.Map; import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate; public class JdbcImpl implements JdbcInterface
{
@Autowired
private JdbcTemplate jdbcTemplate; /**
* 查询所有多边形
*
* @author wulinfeng
* @return
*/
public List<Map<String, Object>> queryCircles()
{
return jdbcTemplate.queryForList(
"select OID, t.x LONGITUDE, t.y LATITUDE from I2_GRID_INFO_CUR,table (sdo_util.getvertices(LOCA_TION)) t");
} /**
* 根据日志查询点
*
* @author wulinfeng
* @return
*/
public List<Map<String, Object>> queryPoints(Date orderDate)
{
return jdbcTemplate.queryForList(
"select OPEN_NUMBER from ORDER_2I_NEW where ORDER_DATE = ? and LONGITUDE != null and LATITUDE != null",
orderDate);
} /**
* 查询每一个点
*
* @author wulinfeng
* @return
*/
public List<Map<String, Object>> querySinglePoint(Date orderDate, String openNumber)
{
return jdbcTemplate.queryForList(
"select LONGITUDE,LATITUDE from ORDER_2I_NEW where ORDER_DATE = ? and OPEN_NUMBER = ?",
new Object[] {orderDate, openNumber});
} /**
* 更新网格id
*
* @author wulinfeng
* @return
*/
public int updateCircleIdToPoint(Date orderDate, String openNumber, String longitude, String latitude,
String gridId)
{
return jdbcTemplate.update(
"update ORDER_2I_NEW set GRID_ID = ? where ORDER_DATE = ? and OPEN_NUMBER = ? and LONGITUDE = ? and LATITUDE = ?",
new Object[] {gridId, orderDate, openNumber, longitude, latitude});
}
}

  我们在实现类里通过JdbcTemplate执行query和update。因为这里查到的数据有多条记录,所以使用了queryForList,然后再把查到的记录通过map的key,即记录的字段名,来获取字段值。具体来看下第一条查询的处理:

    /**
* 查询多边形,封装数据到map
*
* @author wulinfeng
* @param jdbcImpl
* @return
*/
public static Map<String, String> dealCircles(JdbcImpl jdbcImpl)
{
LOG.info("Start to deal with Table : [I2_GRID_INFO_CUR] *****"); List<Map<String, Object>> circleDatas = jdbcImpl.queryCircles(); if (circleDatas == null || circleDatas.size() == 0)
{
LOG.warning("Table: [I2_GRID_INFO_CUR] is empty.");
return null;
} Map<String, String> circleMap = null;
circleMap = new HashMap<>(); String oId = null;
String longitude_circle = null;
String latitude_circle = null; // 先遍历数据库查询的多边形列表,按oId为key,value拼接经纬度成类似:"39.8160521200 116.6385841370"
for (Map<String, Object> circleItem : circleDatas)
{
oId = circleItem.get("OID").toString();
longitude_circle = circleItem.get("LONGITUDE").toString();
latitude_circle = circleItem.get("LATITUDE").toString(); // 数据校验
if (StringUtils.isEmpty(oId))
{
LOG.warning("Field OID in Table [I2_GRID_INFO_CUR] is null.");
continue;
} if (StringUtils.isEmpty(longitude_circle) || StringUtils.isEmpty(latitude_circle))
{
LOG.warning("Field LOCA_TION in Table [I2_GRID_INFO_CUR] has problem query by OID : " + oId);
continue;
} // 合并相同oId经纬度
if (!circleMap.containsKey(oId))
{
circleMap.put(oId, latitude_circle + " " + longitude_circle);
}
else
{
circleMap.put(oId, circleMap.get(oId) + "," + latitude_circle + " " + longitude_circle);
}
} // 再遍历一次合并后的map,拼接成POLYGON ((39.8160521200 116.6385841370,39.8078766971 116.6742897034))"
Iterator<String> it = circleMap.keySet().iterator();
while (it.hasNext())
{
String key = it.next();
circleMap.put(key, "POLYGON ((" + circleMap.get(key) + "))");
} LOG.info("Table: [I2_GRID_INFO_CUR] has " + circleMap.size() + " records."); return circleMap;
}

  我们看到这里查出I2_GRID_INFO_CUR表里所有记录,然后遍历记录list,通过map取出每条记录的3个字段值:OID、LONGITUDE和LATITUDE。最后我们回到spring配置文件,看下数据库连接的配置dataSource,这里是从global.properties里取到的配置参数:

driver.class=oracle.jdbc.driver.OracleDriver
driver.url=jdbc:oracle:thin:@192.168.12.3:1521:nwom
db.username=wlf
db.password=wlf