前言：相对于Mybatis（ibatis），个人感觉springJDBC更灵活，主要实现类JdbcTemplate；它替我们完成了资源的创建以及释放工作，从而简化了我们对JDBC的使用。它还可以帮助我们避免一些常见的错误，比如忘记关闭数据库连接。JdbcTemplate将完成JDBC核心处理流程，比如SQL语句的创建、执行，而把SQL语句的生成以及查询结果的提取工作留给我们的应用代码。它可以完成SQL查询、更新以及调用存储过程，可以对ResultSet进行遍历并加以提取。它还可以捕获JDBC异常并将其转换成org.springframework.dao包中定义的，通用的，信息更丰富的异常。
概述：
1）core
即核心包，它包含了JDBC的核心功能。此包内有很多重要的类，包括：JdbcTemplate类、SimpleJdbcInsert类，SimpleJdbcCall类，以及NamedParameterJdbcTemplate类。
2）datasource
即数据源包，访问数据源的实用工具类。它有多种数据源的实现，可以在JavaEE容器外部测试JDBC代码。
3）object
即对象包，以面向对象的方式访问数据库。它允许执行查询并返回结果作为业务对象。它可以在数据表的列和业务对象的属性之间映射查询结果。
4）support
即支持包，是core包和object包的支持类。例如提供了异常转换功能的SQLException类。
springJDBC完整实例：
工程结构：
结构说明：工程结构也不难理解，分为控制层（controller）、服务层（service）、持久层（dao），下面一步一步说明；
1.导入相关的springjdbcjar包，jar包分享到百度云盘，如果无法下载可以在评论留下邮箱我私发；
jar包分享地址：http://pan.baidu.com/s/1kVNQvIn
2.web配置文件：
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<display-name>springJDBCDemo</display-name>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list> <servlet>
<servlet-name>dispatcherServlet</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>
classpath:conf/spring.xml
</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>dispatcherServlet</servlet-name>
<url-pattern>/do/*</url-pattern>
</servlet-mapping> <filter>
<filter-name>encodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
说明：<display-name>一般就写工程名了，<servlet>加载配置文件和请求路径配置；
3.spring.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:p="http://www.springframework.org/schema/p"
xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:context="http://www.springframework.org/schema/context"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd"> <!-- 读取配置文件 -->
<bean id="propertyConfigurer" class="com.sf.springJDBC.base.http.CustomizedPropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:conf/config.properties</value>
</list>
</property>
</bean> <!-- 对web包中的所有类进行扫描，以完成Bean创建和自动依赖注入的功能 -->
<context:component-scan base-package="com.sf.springJDBC" />
<!-- 该类设置后台编码为utf-8，如果没有该类可能导致jsp请求返回的数据中文显示为问号 -->
<bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
<property name="messageConverters">
<list>
<bean id="UTF8StringHttpMessageConverter" class="com.sf.springJDBC.base.UTF8StringHttpMessageConverter">
</bean>
</list>
</property>
</bean> <!-- 支持spring3.0新的mvc注解 -->
<mvc:annotation-driven/> <!-- 启动Spring MVC的注解功能，完成请求和注解POJO的映射 -->
<bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
<property name="cacheSeconds" value="0" />
<property name="messageConverters">
<list>
<bean class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter"></bean>
</list>
</property>
</bean> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="102400000" />
<property name="resolveLazily" value="true"/>
<property name="maxInMemorySize" value="4096"/>
</bean> <!-- 数据库配置-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${dataSource.driver}" />
<property name="url" value="${dataSource.url}" />
<property name="username" value="${dataSource.username}" />
<property name="password" value="${dataSource.password}" />
<property name="initialSize" value="${dataSource.initialSize}" />
<property name="maxIdle" value="${dataSource.minIdle}" />
<property name="maxActive" value="${dataSource.maxActive}" />
<property name="maxWait" value="${dataSource.maxWait}" />
<property name="timeBetweenEvictionRunsMillis" value="${dataSource.timeBetweenEvictionRunsMillis}" />
<property name="minEvictableIdleTimeMillis" value="${dataSource.minEvictableIdleTimeMillis}" />
<property name="validationQuery" value="select 1 from dual" />
<property name="poolPreparedStatements" value="true" />
<property name="defaultAutoCommit" value="true" />
</bean> <!-- 给jdbc模板注入数据源-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean> </beans>
上面的注解说的很清楚了；
4.数据源配置文件config.properties：
#oracle
dataSource.driver=oracle.jdbc.driver.OracleDriver #test 14-2
dataSource.url=jdbc:oracle:thin:@ip:port:dbname
dataSource.username=username
dataSource.password=password #Initialize connection
dataSource.initialSize=5
dataSource.minIdle=10
dataSource.maxActive=500
dataSource.maxWait=6000
dataSource.timeBetweenEvictionRunsMillis=3000
dataSource.minEvictableIdleTimeMillis=300000
5.工程主代码：
⑴控制层Controller
package com.sf.springJDBC.controller; import java.util.HashMap;
import java.util.Map; import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody; import com.sf.springJDBC.base.BaseController;
import com.sf.springJDBC.service.TestService; @Controller
@RequestMapping("/test")
public class TestController extends BaseController{
@Resource
private TestService testService; @ResponseBody
@RequestMapping(value = "/gettest")
public String getTest(HttpServletRequest request, HttpServletResponse response) {
Map<String,Object> map = new HashMap<String, Object>();
map = testService.getTest();
String data = resMapToJson(map);
return data;
}
}
⑵服务层：service
package com.sf.springJDBC.service;
import java.util.Map;
public interface TestService {
public Map<String, Object> getTest();
}
（3）服务实现接口service.impl
package com.sf.springJDBC.service.impl; import java.util.HashMap;
import java.util.Map; import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service; import com.sf.springJDBC.dao.BaseDao;
import com.sf.springJDBC.dao.sql.TestSql;
import com.sf.springJDBC.service.TestService; @Service()
public class TestServiceImpl implements TestService { @Autowired
private BaseDao baseDao;
public Map<String, Object> getTest() {
Map<String,Object> map = new HashMap<String, Object>();
String sql = TestSql.getQuestionsql;
map = baseDao.selectOne(sql, null);
return map;
} }
持久层dao
package com.sf.springJDBC.dao; import java.util.List;
import java.util.Map; public interface BaseDao { public Map<String, Object> selectOne(String sql, Object[] params); public List<Map<String, Object>> selectList(String sql, Object[] params); public int selectForInt(String sql, Object[] params); public long selectForLong(String sql, Object[] params); public int update(String sql, Object[] params); public int executeBatch(String[] sql); public int executeBatch(String sql, List<Object[]> objList); }
（4）实现接口：dao.impl
package com.sf.springJDBC.dao.impl; import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Repository; import com.sf.springJDBC.dao.BaseDao; @Repository
public class BaseDaoImpl implements BaseDao { @Autowired
private JdbcTemplate jdbcTemplate; public Map<String, Object> selectOne(String sql, Object[] params) {
Map<String, Object> result = new HashMap<String, Object>();
try {
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, params);
if (list.size() > 0) {
result = list.get(0);
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
} public List<Map<String, Object>> selectList(String sql, Object[] params) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
try {
list = jdbcTemplate.queryForList(sql, params);
} catch (Exception e) {
e.printStackTrace();
}
return list;
} public int selectForInt(String sql, Object[] params) {
int count = 0;
try {
count = jdbcTemplate.queryForInt(sql, params);
} catch (Exception e) {
e.printStackTrace();
}
return count;
} public long selectForLong(String sql, Object[] params) {
long count = 0l;
try {
count = jdbcTemplate.queryForLong(sql, params);
} catch (Exception e) {
e.printStackTrace();
}
return count;
} public int update(String sql, Object[] params) {
int count = 0;
try {
count = jdbcTemplate.update(sql, params);
} catch (Exception e) {
e.printStackTrace();
}
return count;
} public int executeBatch(String[] sql) {
int count = 0;
try {
count = jdbcTemplate.batchUpdate(sql).length;
} catch (Exception e) {
e.printStackTrace();
}
return count;
} public int executeBatch(String sql, List<Object[]> objList) { /* Object[] objs = new Object[]{1,2,3};
objs = new Object[]{1,2,3};
objs = new Object[]{1,2,3};
objList.add(objs);*/ int count = 0;
final List<Object[]> paramList = objList;
try {
count = jdbcTemplate.execute(sql, new PreparedStatementCallback<int[]>() { public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
ps.getConnection().setAutoCommit(false);
for (int i = 0; i < paramList.size(); i++) {
Object[] objs = paramList.get(i);
for (int j = 0; j < objs.length; j++) {
Object obj = objs[j];
int num = j + 1;
if (obj instanceof Integer) {
ps.setInt(num, (Integer) obj);
} else if (obj instanceof Long) {
ps.setLong(num, (Long) obj);
} else if (obj instanceof java.sql.Date) {
ps.setDate(num, (java.sql.Date)obj);
} else if (obj instanceof Timestamp) {
ps.setTimestamp(num, (Timestamp) obj);
} else if(obj instanceof Double){
ps.setDouble(num,(Double)obj);
}
else { ps.setString(num, String.valueOf(obj));
}
}
ps.addBatch();
}
int[] o = ps.executeBatch();
ps.getConnection().commit();
ps.getConnection().setAutoCommit(true);
// 如果用<aop:config> 来控制事务，需要把上一行注掉，否则会报错
return o;
}
}).length;
} catch (Exception e) {
e.printStackTrace();
}
return count;
} }
（5）sql语句单独写在一个类里面，方便管理：
package com.sf.springJDBC.dao.sql;
public class TestSql {
public static String getQuestionsql = "select * from bs_question_info where question_busi_id='ZJ16111000192'" ;
}
当sql需要传参数时，参数用?传入，后台服务层传参数方式：baseDao.selectOne(sql, new Object[]{param1,param2});
（6）读取配置文件CustomizedPropertyPlaceholderConfigurer.java
package com.sf.springJDBC.base.http; import java.util.HashMap;
import java.util.Map;
import java.util.Properties; import org.springframework.beans.BeansException;
import org.springframework.beans.factory.config.ConfigurableListableBeanFactory;
import org.springframework.beans.factory.config.PropertyPlaceholderConfigurer; /*
* 读取配置文件
*/
public class CustomizedPropertyPlaceholderConfigurer extends PropertyPlaceholderConfigurer { private static Map<String, Object> ctxPropertiesMap; @Override
protected void processProperties(ConfigurableListableBeanFactory beanFactoryToProcess, Properties props) throws BeansException {
super.processProperties(beanFactoryToProcess, props);
ctxPropertiesMap = new HashMap<String, Object>();
for (Object key : props.keySet()) {
String keyStr = key.toString();
String value = props.getProperty(keyStr);
ctxPropertiesMap.put(keyStr, value);
}
} public static Object getContextProperty(String name) {
return ctxPropertiesMap.get(name);
}
}
（7）控制层继承的基础类：BaseController.java
package com.sf.springJDBC.base; import java.io.IOException;
import java.io.StringReader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern; import javax.servlet.http.HttpServletRequest;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException; import org.codehaus.jackson.JsonParseException;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException; import net.sf.json.JSONArray; import com.alibaba.fastjson.JSON;
import com.oncon.core.common.util.JsonUtils;
import com.oncon.core.common.web.controller.SuperController; public class BaseController extends SuperController {
protected List<Map<String,Object>> list;
protected Map<String, Object> result; public BaseController() {
// 初始化
result = new HashMap<String, Object>();
result.put("status", "1");
result.put("desc", "失败");
} /**
* 返回给前端json
*
* @param map
* @return
*/
protected String resMapToJson(Map<String, Object> map) {
return JsonUtils.getObjectString(map);
}
protected String resMapToJson2(Map<String, String> map) {
return JsonUtils.getObjectString(map);
} protected String resObjToJson(Object obj) {
return JSONArray.fromObject(obj).toString();
} /**
* 获取登录用户信息
*
* @param request
* @return
*/
// protected UserInfoBean getUserInfo(HttpServletRequest request) {
// if (request.getSession().getAttribute("USERINFO") == null) {
// return null;
// }
// return (UserInfoBean) request.getSession().getAttribute("USERINFO");
// } /**
* 转化传入的json,示例:{"version":"1"}
*
* @param requestJson
* @return
*/
// @RequestBody String requestJson
@SuppressWarnings("unchecked")
protected Map<String, Object> resJsonToMap(String requestJson) {
if (requestJson == null || "".equals(requestJson)) {
return new HashMap<String, Object>();
}
return JsonUtils.toObject(requestJson, HashMap.class);
} /**
* json转为map
*
* @param map
* @param key
* @return
*/
protected String getMapToParamVal(Map<String, Object> map, String key) {
String val = getStringValueFromMap(key, map);
if (val == null) {
return "";
} else {
return val.trim();
}
}
/** * 调用JsonUtils jar包的方法 返回给前端json
*
* @param map
* @return
*/
protected String resListToJson(List<Map<String, Object>> list) {
return JSON.toJSONString(list);
}
/**
* 获取url传入参数
*
* @param request
* @param key
* @return
*/
protected String getResToParamVal(HttpServletRequest request, String key) {
String val = request.getParameter(key);
if (val == null) {
return "";
} else {
return val.trim();
}
} /**
* Used to convert from JSON to XML or XML to JSON
*/
protected ObjectMapper objectMapper = new ObjectMapper(); /**
* Judge the date type is XML or JSON
* @return
*/
protected String analyseDataType(String strData) {
if (strData == null || strData.trim().length() <= 0) {
return "";
} Pattern pattern = Pattern.compile("^<\\?xml.*");
Matcher matcher = pattern.matcher(strData);
boolean b = matcher.matches();
if (b) {
return "xml";
} pattern = Pattern.compile("^\\{.\\}$");
matcher = pattern.matcher(strData);
b = matcher.matches();
if (b) {
return "json";
}
return "";
}
/**
* Get the request parameters, return map object
* @param strParam
* @return
*/
@SuppressWarnings("unchecked")
protected Map<String, String> getParam(String strParam) {
Map<String, String> mapParam = null; // Judge the date type is XML or JSON
String dataType = analyseDataType(strParam); // XML
if ("xml".equalsIgnoreCase(dataType)) {
StringReader stringReader = new StringReader(strParam);
InputSource inputSource = new InputSource(stringReader);
Document doc = null; try {
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
doc = builder.parse(inputSource);
NodeList nodeList = doc.getElementsByTagName("ws:pin");
if (nodeList == null || nodeList.getLength() <= 0) {
return null;
} Node node = nodeList.item(0);
if (node == null) {
return null;
} //
strParam = node.getFirstChild().getNodeValue(); } catch (ParserConfigurationException e) {
e.printStackTrace();
} catch (SAXException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} // JSON
try {
//System.out.println("strParam"+strParam);
mapParam = objectMapper.readValue(strParam, HashMap.class);
//System.out.println("mapParam"+mapParam);
} catch (JsonParseException e) {
e.printStackTrace();
//logger.error(e.getMessage());
} catch (JsonMappingException e) {
e.printStackTrace();
//logger.error(e.getMessage());
} catch (IOException e) {
e.printStackTrace();
//logger.error(e.getMessage());
} finally {
mapParam = mapParam != null ? mapParam : new HashMap<String, String>();
} return mapParam;
}
}
（8）设置后台编码为utf-8，spring.xml配置文件需要加载该类，如果前台获取数据中文不是问号胡乱码，可以注释；
package com.sf.springJDBC.base; import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List; import org.springframework.http.HttpInputMessage;
import org.springframework.http.HttpOutputMessage;
import org.springframework.http.MediaType;
import org.springframework.http.converter.AbstractHttpMessageConverter;
import org.springframework.http.converter.HttpMessageNotReadableException;
import org.springframework.http.converter.HttpMessageNotWritableException;
import org.springframework.util.FileCopyUtils; public class UTF8StringHttpMessageConverter extends AbstractHttpMessageConverter<String> { public static final Charset DEFAULT_CHARSET = Charset.forName("UTF-8"); private final List<Charset> availableCharsets; private boolean writeAcceptCharset = true; public UTF8StringHttpMessageConverter() {
super(new MediaType("text", "plain", DEFAULT_CHARSET), MediaType.ALL);
this.availableCharsets = new ArrayList<Charset>(Charset.availableCharsets().values());
} /**
* Indicates whether the {@code Accept-Charset} should be written to any
* outgoing request.
* <p>
* Default is {@code true}.
*/
public void setWriteAcceptCharset(boolean writeAcceptCharset) {
this.writeAcceptCharset = writeAcceptCharset;
} @Override
public boolean supports(Class<?> clazz) {
return String.class.equals(clazz);
} @Override
protected String readInternal(Class clazz, HttpInputMessage inputMessage) throws IOException {
Charset charset = getContentTypeCharset(inputMessage.getHeaders().getContentType());
return FileCopyUtils.copyToString(new InputStreamReader(inputMessage.getBody(), charset));
} @Override
protected Long getContentLength(String s, MediaType contentType) {
Charset charset = getContentTypeCharset(contentType);
try {
return (long) s.getBytes(charset.name()).length;
} catch (UnsupportedEncodingException ex) {
// should not occur
throw new InternalError(ex.getMessage());
}
} @Override
protected void writeInternal(String s, HttpOutputMessage outputMessage) throws IOException {
if (writeAcceptCharset) {
outputMessage.getHeaders().setAcceptCharset(getAcceptedCharsets());
}
Charset charset = getContentTypeCharset(outputMessage.getHeaders().getContentType());
FileCopyUtils.copy(s, new OutputStreamWriter(outputMessage.getBody(), charset));
} /**
* Return the list of supported {@link Charset}.
*
* <p>
* By default, returns {@link Charset#availableCharsets()}. Can be
* overridden in subclasses.
*
* @return the list of accepted charsets
*/
protected List<Charset> getAcceptedCharsets() {
return this.availableCharsets;
} private Charset getContentTypeCharset(MediaType contentType) {
if (contentType != null && contentType.getCharSet() != null) {
return contentType.getCharSet();
} else {
return DEFAULT_CHARSET;
}
} /* @Override
protected String readInternal(Class<? extends String> arg0,
HttpInputMessage arg1) throws IOException,
HttpMessageNotReadableException {
// TODO Auto-generated method stub
return null;
} @Override
protected boolean supports(Class<?> arg0) {
// TODO Auto-generated method stub
return false;
} @Override
protected void writeInternal(String arg0, HttpOutputMessage arg1)
throws IOException, HttpMessageNotWritableException {
// TODO Auto-generated method stub }*/ }
5.部署到tomcat启动服务，请求后台数据地址：http://localhost:8080/工程名/do/test/gettest
6.总结：
这种方式获取数据都是存到map里面，很容易解析数据；穿参数也相当灵活，而且对参数类型随意定义；
以上是一个完整的springjdbc实例，如果有什么不妥或不正确的地方，欢迎指正。。。