使用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.wulf.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.wulf.chinanet.point.dao; import java.sql.Date; import java.util.List; import java.util.Map; /** * 数据库增删查改接口 * * @author wulf * @version C10 2018年1月25日 * @since SDP V300R003C10 */ public interface JdbcInterface { /** * 查询所有多边形 * * @author wulf * @return */ public List<Map<String, Object>> queryCircles(); /** * 根据日志查询点 * * @author wulf * @return */ public List<Map<String, Object>> queryPoints(Date orderDate); /** * 查询每一个点 * * @author wulf * @return */ public List<Map<String, Object>> querySinglePoint(Date orderDate, String openNumber); /** * 更新点所对应的多边形编码 * * @author wulf * @param circleId */ public int updateCircleIdToPoint(Date orderDate, String openNumber, String longitude, String latitude, String gridId); }
实现类
package com.wulf.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 wulf * @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 wulf * @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 wulf * @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 wulf * @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 wulf * @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:@110.111.11.1:1521:nwom
db.username=wlf
db.password=wlf
