Spring的jdbcTemplate的queryForXXX,update,delete等方法可以帮我们大大简化JDBC操作,像数据库连接的打开关闭都无需我们手动去做。
一:添加jar包
这个没什么说的,不过还是贴上吧
二:web.xml
<!-- 配置Spring ioc容器 -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext.xml</param-value>
</context-param>
三:applicationContext.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:task="http://www.springframework.org/schema/task" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.1.xsd
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-3.1.xsd">
<context:component-scan base-package="com.windblow"></context:component-scan>
<!-- 配置c3p0数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="root"></property>
<property name="password" value="root1234"></property>
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/yourdbname"></property>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
<property name="dataSource">
<ref bean="dataSource"/>
</property>
</bean>
四:SprintUtil
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class SpringUtil {
private static ApplicationContext ctx = new ClassPathXmlApplicationContext(
"applicationContext.xml");
public static Object getBean(String beanName) {
return ctx.getBean(beanName);
}
}
五:Emp类和EmpDao类
public class Emp {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
EmpDao类
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import com.windblow.util.SpringUtil;
public class EmpDao {
private JdbcTemplate jdbcTemplate=(JdbcTemplate) SpringUtil.getBean("applicationContext.xml");
//插入
public void insert(Emp emp){
String sql="insert into emp(id,ename) value(?,?)";
}
//除sql语句不一样,删除、更新很类似
public void delete(int id){
String sql="delete from emp where id=?";
jdbcTemplate.update(sql, id);
//也可jdbcTemplate.update(sql, new Object[]{id});
}
//查询一个值
protected <E> E getValue(String sql, Object... args) {
//System.out.println(jdbcTemplate);
E result = null;
List list = jdbcTemplate.queryForList(sql, args);
if (list != null && list.size() > 0) {
Map map = (Map) list.get(0);
result = (E) map.values().iterator().next();
}
return result;
}
public String getName(int id){
String result="";
String sql="select ename from emp where id=?";
List list=jdbcTemplate.queryForList(sql, new Object[]{id});
if(list!=null && list.size()>0){
Map map=(Map) list.get(0);
result=(String) map.values().iterator().next();
}
return result;
}
//全查,注意的是这里需要转一下,不能直接返回queryForlist,否则会报异常org.springframework.jdbc.IncorrectResultSetColumnCountException:
public List<Emp> findAll(){
List<Emp> empList=new ArrayList<Emp>();
String sql="select id,ename from emp";
List list=jdbcTemplate.queryForList(sql);
Iterator it=list.iterator();
Emp emp=null;
while(it.hasNext()){
emp=new Emp();
Map map=(Map) it.next();
emp.setId((Integer) map.get("id"));
emp.setName((String) map.get("ename"));
empList.add(emp);
}
return empList;
}
}
此外,还可通过依赖注入来降低类之间的耦合度,让spring来维护类之间的关系
在aplicationContext.xml中添加一组:
<bean id="bookDao" class="com.test.dao.EmpDao">
<property name="jdbcTemplate">
<ref bean="jdbcTemplate" />
</property>
</bean>
或者直接在类中通过注解的方式来改写(待续)