Spring JdbcTemplate用法整理：
xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:p="http://www.springframework.org/schema/p" xmlns:util="http://www.springframework.org/schema/util"
xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd">
<bean id="dbcpds" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.OracleDriver"></property>
<property name="url" value="jdbc:oracle:thin:@10.1.7.148:1521:ora11"></property>
<property name="username" value="tds1ml1"></property>
<property name="password" value="tds1ml1"></property>
<property name="initialSize" value="5"></property>
<property name="maxActive" value="10"></property>
</bean>
<bean id="sprds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
<property name="url" value="jdbc:oracle:thin:@10.1.7.148:1521:ora11"></property>
<property name="username" value="tds1ml1" />
<property name="password" value="tds1ml1" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg ref="dbcpds"></constructor-arg>
</bean>
<bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dbcpds"></constructor-arg>
</bean>
<bean id="jdbcSpitterDAO" class="com.stono.sprjdbc.JdbcSpitterDAO">
<property name="template" ref="jdbcTemplate"></property>
<property name="namedJdbcTemplate" ref="namedJdbcTemplate"></property>
</bean>
<bean id="jdbcSpitter2DAO" class="com.stono.sprjdbc.JdbcSpitter2Dao">
<property name="dataSource" ref="dbcpds"></property>
</bean>
</beans>
DAO Interface:
package com.stono.sprjdbc;
import java.util.Map;
public interface SpitterDAO {
void insertSpitter(SpitterObj obj);
void namedInsertSpitter(Map<String, Object> map);
void updateSpitter(SpitterObj obj);
void deleteSpitter(SpitterObj obj);
SpitterObj getSpitter(String ljdm);
}
POJO:
package com.stono.sprjdbc;
public class SpitterObj {
private String ljdm;
private String ljpym;
public String getLjdm() {
return ljdm;
}
public void setLjdm(String ljdm) {
this.ljdm = ljdm;
}
public String getLjpym() {
return ljpym;
}
public void setLjpym(String ljpym) {
this.ljpym = ljpym;
}
}
Use JdbcTemplate:
package com.stono.sprjdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
public class JdbcSpitterDAO implements SpitterDAO {
private JdbcTemplate template;
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
private NamedParameterJdbcTemplate namedJdbcTemplate;
public void setNamedJdbcTemplate(NamedParameterJdbcTemplate namedJdbcTemplate) {
this.namedJdbcTemplate = namedJdbcTemplate;
}
private static String INSERT_SQL = "insert into ljzdtemp(ljdm,ljpym) values(?,?)";
private static String GET_SQL = "select * from ljzdtemp where ljdm = ?";
private static String UPDATE_SQL = "update ljzdtemp set ljpym=? where ljdm = ?";
private static String DEL_SQL = "delete ljzdtemp where ljdm = ?";
private static String NAMED_INSERT_SQL = "insert into ljzdtemp(ljdm,ljpym) values(:ljdm,:ljpym)";
@Override
public void insertSpitter(SpitterObj obj) {
template.update(INSERT_SQL, obj.getLjdm(), obj.getLjpym());
}
@Override
public void updateSpitter(SpitterObj obj) {
template.update(UPDATE_SQL, "aa", "a");
}
@Override
public void deleteSpitter(SpitterObj obj) {
template.update(DEL_SQL, "a");
}
@Override
public SpitterObj getSpitter(String ljdm) {
return template.queryForObject(GET_SQL, new RowMapper<SpitterObj>() {
@Override
public SpitterObj mapRow(ResultSet rs, int rowNum) throws SQLException {
SpitterObj obj = new SpitterObj();
obj.setLjdm(rs.getString(4));
obj.setLjpym(rs.getString(5));
return obj;
}
}, 'a');
}
@Override
public void namedInsertSpitter(Map<String, Object> map) {
namedJdbcTemplate.update(NAMED_INSERT_SQL, map);
}
}
Extends NamedParameterJdbcDaoSuport:
package com.stono.sprjdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
public class JdbcSpitter2Dao extends NamedParameterJdbcDaoSupport implements SpitterDAO {
private static String INSERT_SQL = "insert into ljzdtemp(ljdm,ljpym) values(?,?)";
private static String GET_SQL = "select * from ljzdtemp where ljdm = ?";
private static String UPDATE_SQL = "update ljzdtemp set ljpym=? where ljdm = ?";
private static String DEL_SQL = "delete ljzdtemp where ljdm = ?";
private static String NAMED_INSERT_SQL = "insert into ljzdtemp(ljdm,ljpym) values(:ljdm,:ljpym)";
@Override
public void insertSpitter(SpitterObj obj) {
getJdbcTemplate().update(INSERT_SQL, obj.getLjdm(), obj.getLjpym());
}
@Override
public void namedInsertSpitter(Map<String, Object> map) {
getNamedParameterJdbcTemplate().update(NAMED_INSERT_SQL, map);
}
@Override
public void updateSpitter(SpitterObj obj) {
getJdbcTemplate().update(UPDATE_SQL, "aa", "a");
}
@Override
public void deleteSpitter(SpitterObj obj) {
getJdbcTemplate().update(DEL_SQL, "a");
}
@Override
public SpitterObj getSpitter(String ljdm) {
return getJdbcTemplate().queryForObject(GET_SQL, new RowMapper<SpitterObj>() {
@Override
public SpitterObj mapRow(ResultSet rs, int rowNum) throws SQLException {
SpitterObj obj = new SpitterObj();
obj.setLjdm(rs.getString(4));
obj.setLjpym(rs.getString(5));
return obj;
}
}, 'a');
}
}
AppBean:
package com.stono.sprjdbc;
import java.sql.Connection;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class AppBeans14 {
public static void main(String[] args) throws Exception {
@SuppressWarnings("resource")
ApplicationContext context = new ClassPathXmlApplicationContext("appbeans14.xml");
BasicDataSource dataSource = (BasicDataSource) context.getBean("dbcpds");
Connection connection = dataSource.getConnection();
System.out.println(connection);
DriverManagerDataSource sprDataSource = (DriverManagerDataSource) context.getBean("sprds");
Connection connection2 = sprDataSource.getConnection();
System.out.println(connection2);
// 普通sql查询
// String sql = "select * from ljzdtemp";
// PreparedStatement prepareStatement = connection.prepareStatement(sql);
// PreparedStatement prepareStatement = connection2.prepareStatement(sql);
// ResultSet resultSet = prepareStatement.executeQuery();
// while(resultSet.next()){
// System.out.println(resultSet.getString(1));
// }
SpitterDAO dao = (SpitterDAO) context.getBean("jdbcSpitterDAO");
// JdbcTemplate 插入
// SpitterObj obj = new SpitterObj();
// obj.setLjdm("a");
// obj.setLjpym("a");
// dao.insertSpitter(obj);
// Map<String, Object> map = new HashMap<String, Object>();
// map.put("ljdm", "a");
// map.put("ljpym", "b");
// dao.namedInsertSpitter(map);
SpitterDAO dao2 = (SpitterDAO) context.getBean("jdbcSpitter2DAO");
Map<String, Object> map = new HashMap<String, Object>();
map.put("ljdm", "b");
map.put("ljpym", "c");
dao2.namedInsertSpitter(map);
}
}
dbcp连接池需要的jar包：
commons-dbcp-1.4.jar commons-pool-1.6.jar