前言
本篇紧接着spring入门详细教程（三），建议阅读本篇前，先阅读第一篇，第二篇以及第三篇。链接如下：
Spring入门详细教程（一） https://www.cnblogs.com/jichi/p/10165538.html
Spring入门详细教程（二） https://www.cnblogs.com/jichi/p/10176601.html
Spring入门详细教程（三） https://www.cnblogs.com/jichi/p/10177004.html
本篇主要讲解spring的jdbcTemplate相关。
一、spring整合jdbc继承jdbcdaosupport的方式
1、导入所需jar包。
除了之前介绍的spring的基础包，还需要导入数据库连接池包，jdbc驱动包，spring的jdbc包，spring的事务。
2、书写dao层代码。
public class UserDaoImpl extends JdbcDaoSupport implements UserDao {
@Override
public void save(User u) {
String sql = "insert into user values('1',?) ";
super.getJdbcTemplate().update(sql, u.getName());
}
@Override
public void delete(Integer id) {
String sql = "delete from user where id = ? ";
super.getJdbcTemplate().update(sql,id);
}
@Override
public void update(User u) {
String sql = "update user set name = ? where id=? ";
super.getJdbcTemplate().update(sql, u.getName(),u.getId());
}
@Override
public User getById(Integer id) {
String sql = "select * from user where id = ? ";
return super.getJdbcTemplate().queryForObject(sql,new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
return u;
}}, id);
}
@Override
public int getTotalCount() {
String sql = "select count(*) from user ";
Integer count = super.getJdbcTemplate().queryForObject(sql, Integer.class);
return count;
}
@Override
public List<User> getAll() {
String sql = "select * from user ";
List<User> list = super.getJdbcTemplate().query(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
return u;
}});
return list;
}
}
3、建立数据库链接配置文件
jdbc.jdbcUrl=jdbc:mysql:///spring
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=1234
4、在spring容器中进行配置
<!-- 指定spring读取db.properties配置 -->
<context:property-placeholder location="classpath:db.properties" />
<!-- 将连接池放入spring容器 -->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
<property name="jdbcUrl" value="${jdbc.jdbcUrl}" ></property>
<property name="driverClass" value="${jdbc.driverClass}" ></property>
<property name="user" value="${jdbc.user}" ></property>
<property name="password" value="${jdbc.password}" ></property>
</bean>
<!-- 将UserDao放入spring容器 -->
<bean name="userDao" class="com.jichi.jdbctemplate.UserDaoImpl" >
<property name="dataSource" ref="dataSource" ></property>
</bean>
5、由于userDaoImpl已经继承了jdbcDaoSupport。jdbcDaoSupport中已经定义了jdbcTemplate，同时内置了setDataSource。可以自动将连接池放入。源码如下：
/*
* Copyright 2002-2012 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/ package org.springframework.jdbc.core.support; import java.sql.Connection;
import javax.sql.DataSource; import org.springframework.dao.support.DaoSupport;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.SQLExceptionTranslator; /**
* Convenient super class for JDBC-based data access objects.
*
* <p>Requires a {@link javax.sql.DataSource} to be set, providing a
* {@link org.springframework.jdbc.core.JdbcTemplate} based on it to
* subclasses through the {@link #getJdbcTemplate()} method.
*
* <p>This base class is mainly intended for JdbcTemplate usage but can
* also be used when working with a Connection directly or when using
* {@code org.springframework.jdbc.object} operation objects.
*
* @author Juergen Hoeller
* @since 28.07.2003
* @see #setDataSource
* @see #getJdbcTemplate
* @see org.springframework.jdbc.core.JdbcTemplate
*/
public abstract class JdbcDaoSupport extends DaoSupport { private JdbcTemplate jdbcTemplate; /**
* Set the JDBC DataSource to be used by this DAO.
*/
public final void setDataSource(DataSource dataSource) {
if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
this.jdbcTemplate = createJdbcTemplate(dataSource);
initTemplateConfig();
}
} /**
* Create a JdbcTemplate for the given DataSource.
* Only invoked if populating the DAO with a DataSource reference!
* <p>Can be overridden in subclasses to provide a JdbcTemplate instance
* with different configuration, or a custom JdbcTemplate subclass.
* @param dataSource the JDBC DataSource to create a JdbcTemplate for
* @return the new JdbcTemplate instance
* @see #setDataSource
*/
protected JdbcTemplate createJdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
} /**
* Return the JDBC DataSource used by this DAO.
*/
public final DataSource getDataSource() {
return (this.jdbcTemplate != null ? this.jdbcTemplate.getDataSource() : null);
} /**
* Set the JdbcTemplate for this DAO explicitly,
* as an alternative to specifying a DataSource.
*/
public final void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
initTemplateConfig();
} /**
* Return the JdbcTemplate for this DAO,
* pre-initialized with the DataSource or set explicitly.
*/
public final JdbcTemplate getJdbcTemplate() {
return this.jdbcTemplate;
} /**
* Initialize the template-based configuration of this DAO.
* Called after a new JdbcTemplate has been set, either directly
* or through a DataSource.
* <p>This implementation is empty. Subclasses may override this
* to configure further objects based on the JdbcTemplate.
* @see #getJdbcTemplate()
*/
protected void initTemplateConfig() {
} @Override
protected void checkDaoConfig() {
if (this.jdbcTemplate == null) {
throw new IllegalArgumentException("'dataSource' or 'jdbcTemplate' is required");
}
} /**
* Return the SQLExceptionTranslator of this DAO's JdbcTemplate,
* for translating SQLExceptions in custom JDBC access code.
* @see org.springframework.jdbc.core.JdbcTemplate#getExceptionTranslator()
*/
protected final SQLExceptionTranslator getExceptionTranslator() {
return getJdbcTemplate().getExceptionTranslator();
} /**
* Get a JDBC Connection, either from the current transaction or a new one.
* @return the JDBC Connection
* @throws CannotGetJdbcConnectionException if the attempt to get a Connection failed
* @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection(javax.sql.DataSource)
*/
protected final Connection getConnection() throws CannotGetJdbcConnectionException {
return DataSourceUtils.getConnection(getDataSource());
} /**
* Close the given JDBC Connection, created via this DAO's DataSource,
* if it isn't bound to the thread.
* @param con Connection to close
* @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection
*/
protected final void releaseConnection(Connection con) {
DataSourceUtils.releaseConnection(con, getDataSource());
} }
6、编写测试类
@Test
public void fun2() throws Exception{
User u = new User();
u.setName("tom");
ud.save(u);
}
7、执行成功
二、spring整合jdbctemplate
1、导入所需jar包。
除了之前介绍的spring的基础包，还需要导入数据库连接池包，jdbc驱动包，spring的jdbc包，spring的事务。
2、配置jdbctemplate
<!-- 指定spring读取db.properties配置 -->
<context:property-placeholder location="classpath:db.properties" />
<!-- 1.将连接池放入spring容器 -->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
<property name="jdbcUrl" value="${jdbc.jdbcUrl}" ></property>
<property name="driverClass" value="${jdbc.driverClass}" ></property>
<property name="user" value="${jdbc.user}" ></property>
<property name="password" value="${jdbc.password}" ></property>
</bean>
<!-- 2.将JDBCTemplate放入spring容器 -->
<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
<property name="dataSource" ref="dataSource" ></property>
</bean>
<!-- 3.将UserDao放入spring容器 -->
<bean name="userDao" class="com.jichi.jdbctemplate.UserDaoImpl" >
<property name="jdbcTemplate" ref="jdbcTemplate" ></property>
</bean>
3、书写dao层代码
public class UserDaoImpl implements UserDao {
@Resource
private JdbcTemplate jdbcTemplate;
@Override
public void save(User u) {
String sql = "insert into user values('1',?) ";
jdbcTemplate.update(sql, u.getName());
}
@Override
public void delete(Integer id) {
String sql = "delete from user where id = ? ";
jdbcTemplate.update(sql,id);
}
@Override
public void update(User u) {
String sql = "update user set name = ? where id=? ";
jdbcTemplate.update(sql, u.getName(),u.getId());
}
@Override
public User getById(Integer id) {
String sql = "select * from user where id = ? ";
return jdbcTemplate.queryForObject(sql,new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
return u;
}}, id);
}
@Override
public int getTotalCount() {
String sql = "select count(*) from user ";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
@Override
public List<User> getAll() {
String sql = "select * from user ";
List<User> list = jdbcTemplate.query(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
return u;
}});
return list;
}
}
4、书写测试方法
@Test
public void fun2() throws Exception{
User u = new User();
u.setName("tom");
ud.save(u);
}
三、spring中jdbctemplate的相关方法
1、update
用来执行insert，update，delete语句。
@Override
public void save(User u) {
String sql = "insert into user values('1',?) ";
jdbcTemplate.update(sql, u.getName());
}
@Override
public void delete(Integer id) {
String sql = "delete from user where id = ? ";
jdbcTemplate.update(sql,id);
}
@Override
public void update(User u) {
String sql = "update user set name = ? where id=? ";
jdbcTemplate.update(sql, u.getName(),u.getId());
}
2、查询某一具体类
@Override
public int getTotalCount() {
String sql = "select count(*) from user ";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
3、将查询的数据封入实体类（单个对象，实现rowmapper接口）
@Override
public User getById(Integer id) {
String sql = "select * from user where id = ? ";
return jdbcTemplate.queryForObject(sql,new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
return u;
}}, id); }
4、将查询的数据封入实体类（list对象，实现rowmapper接口）
@Override
public List<User> getAll() {
String sql = "select * from user ";
List<User> list = jdbcTemplate.query(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
return u;
}});
return list;
}
5、根据数据库查出的字段与实体类字段名自动对应
@Override
public List<User> getAll() {
String sql = "select * from user ";
List<User> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));
return list;
}