Spring集成JdbcTemplate

Spring集成JdbcTemplate

一、环境搭建

开发工具：Spring Tool Suite v_3.9.3（简称STS）

依赖包管理（pom.xml）：

<!-- Spring JDBC -->
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>${spring.version}</version>
</dependency>

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.0.25</version>
</dependency>

<!-- Oracle数据库支持 -->
<dependency>
	<groupId>com.oracle</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>11.2.0.1.0</version>
</dependency>

<!-- MySQL数据库支持 -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.9</version>
</dependency>

配置DataSource（spring-context.xml）：

<?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:util="http://www.springframework.org/schema/util"
	xmlns:jee="http://www.springframework.org/schema/jee"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
		http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd
		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">

	<!-- 开启注解 -->
	<context:annotation-config />

	<!-- 配置组件扫描 -->
	<context:component-scan base-package="com.xxxxinfo.*"></context:component-scan>

	<!-- 加载配置文件 -->
	<context:property-placeholder location="classpath*:application.properties" />

	<!-- 配置数据连接池 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<!-- <property name="driverClassName" value="${jdbc.driverClassName}" /> --> <!-- MySQL数据库需要指定驱动 -->

		<!-- 连接池初始值 -->
		<property name="initialSize" value="1" />
		<!-- 连接池最大值 -->
		<property name="maxActive" value="300" />
		<!-- 连接池最小空闲值 -->
		<property name="minIdle" value="1" />
		<!-- 配置获取连接等待超时的时间 -->
		<property name="maxWait" value="60000" />
		<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="60000" />
		<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="300000" />
		<property name="validationQuery" value="SELECT 'x' FROM DUAL" />
		<property name="testWhileIdle" value="true" />
		<property name="testOnBorrow" value="false" />
		<property name="testOnReturn" value="false" />
	</bean>
	
	<!-- 配置JdbcTempalte -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg index="0" ref="dataSource" /> <!-- ref属性值是DataSource的id -->
	</bean>
	
</beans>

application.properties

# JDBC配置（Oracle）
jdbc.url = jdbc:oracle:thin:@136.6.161.200:1521:orcl
jdbc.username = cath_thinkops
jdbc.password = cath_thinkops#2018

# JDBC配置（MySQL）
#jdbc.driverClassName = com.mysql.jdbc.Driver
#jdbc.url = jdbc:mysql://192.168.80.191:3306/ithink_idcmon?useUnicode=true&characterEncoding=UTF-8
#jdbc.username = ithink_idcmon
#jdbc.password = idcmon2016

二、测试

编写数据层代码访问数据库

package com.xxxxinfo.dao;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class AlarmDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public void query() {
		List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM IDC_ALARM_BORD");
		System.out.println(list.size()); 
	}
}

Junit4测试类

package com.xxxxinfo.dao;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@ContextConfiguration(locations = { "classpath:spring-context.xml" }) // 加载Spring配置文件
public class AlarmDaoTest {

	@Autowired
	private AlarmDao alarmDao;

	@Test
	public void queryTest() {
		alarmDao.query();
	}
}

三、JdbcTemplate方法

package com.xxxxinfo.dao;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.xxxxinfo.entity.AlarmBord;

@Repository
public class AlarmDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public void query() {
		
		/*
		 *  queryForObject(String sql, Object[] params, Class<T> cla)：查询返回某一个值：查询表的总数
		 */
		Integer total = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM IDC_ALARM_BORD", Integer.class);
		
		/*
		 *  queryForList(String sql, Object[] params, Class<T> cla)：查询返回多条记录
		 */
		List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM IDC_ALARM_BORD");
		
		/*
		 * Spring提供了自带的映射器可以返回实体对象类型
		 */
		AlarmBord alarmBord = jdbcTemplate.queryForObject("", new BeanPropertyRowMapper<AlarmBord>(AlarmBord.class));
		List<AlarmBord> alarmBordList = jdbcTemplate.query("", new BeanPropertyRowMapper<AlarmBord>(AlarmBord.class));
		
		// 插入和更新用update
		int i = jdbcTemplate.update("");
	}
}