spring整合jdbc

来源：互联网发布：linux延时函数编辑：程序博客网时间：2024/06/05 02:40

1.引入jar包 (spring开发包,ojdbc.jar,dbcp开发包)

2.新建applicationContext-jdbc.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:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
xmlns:jee="http://www.springframework.org/schema/jee"
xsi:schemaLocation="
            http://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-2.5.xsd
            http://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-2.5.xsd
            http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
            http://www.springframework.org/schema/jeehttp://www.springframework.org/schema/jee/spring-jee-2.5.xsd">
<bean id="mydataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
  <property name="url" value="jdbc:mysql://127.0.0.1:3306/test">
  </property>
  <property name="username" value="root">
  </property>
  <property name="password" value="870304">
  </property>
  <property name="initialSize" value="2">
  </property>
  <property name="maxActive" value="15">
  </property>
</bean>
</beans>

连接池的说明:
a.<property name="initialSize" value="2">
表示连接池创建后，初始时有2个Connection
b.<property name="maxActive" value="10">
在连接池中最大创建10个Connection
c.<property name="minIdle" value="2">
表示最小的空闲数量，用于控制空闲的Connection的数量，表示最小空闲数量不能低于2个
d.<property name="maxIdle" value="3">
表示最大的空闲数量，表示最大空闲数量不能超过3个

3.编写实体类

4.编写dao接口

5.编写dao接口实现类

实现类继承JdbcDaoSupport可以帮助我们完成dataSource(连接池)的注入

6.spring配置文件中配置dao实现类,注入dataSource属性

7.spring提供的dao方便的操作方法

DAO实现类继承了org.springframework.jdbc.core.support.JdbcDaoSupport，
那么Spring框架会提供一些便利的方法，可以直接调用

8.result与对象映射

如果我们还想加入一些查询方法，比如findAll()，或者findById()，
这种操作对应select语句，jdbc操作将返回ResultSet，
我们需要考虑，如何把ResultSet和对象建立对应关系。
Spring的解决方案是，定义一个映射类FeeMapper，
完成结果集中的字段值与Fee属性之间的映射关系。

9.新建FeeMapper，FeeMapper实现RowMapper接口，

覆盖方法mapRow完成从结果集中解析出结果做操作

10.映射的例子:

public class FeeMapper implements RowMapper{
    public Object mapRow(ResultSet rs, int arg1) throws SQLException {
        Fee fee = new Fee();
        fee.setId(rs.getInt("ID"));
        fee.setFeeName(rs.getString("NAME"));
        fee.setBaseDuration(rs.getInt("BASE_DURATION"));
        fee.setBaseCost(rs.getFloat("BASE_COST"));
        fee.setUnitCost(rs.getFloat("UNIT_COST"));
        fee.setCreateTime(rs.getDate("CREATIME"));
        fee.setStartTime(rs.getDate("STARTIME"));
        fee.setStatus(rs.getString("STATUS"));
        fee.setDescr(rs.getString("DESCR"));
        fee.setCostType(rs.getString("COST_TYPE"));
        return fee;
    }
}

11.dao实现类中的例子

public class JdbcFeeDAO extends JdbcDaoSupport implements FeeDAO {
    private static final String save = "insert into COST (ID, NAME, BASE_DURATION, BASE_COST, UNIT_COST, CREATIME, STARTIME, STATUS, DESCR,COST_TYPE) "
        + "values (fee_seq.nextval,?,?,?,?,?,?,?,?,?)";
    private static final String update ="update COST set NAME=?,BASE_DURATION=?,BASE_COST=?,UNIT_COST=?,"
        + " CREATIME=?,STARTIME=?,STATUS=?,DESCR=?,COST_TYPE=? where ID=?";
    private static final String delete = "delete from COST where id=?";
    private static final String findById = "select * from COST where ID=?";
    private static final String findAll = "select * from COST";
    public void delete(Fee fee) {
        Object[] params = {fee.getId()};
        this.getJdbcTemplate().update(delete,params);
    }
    public List<Fee> findAll() {
        FeeMapper feeMapper = new FeeMapper();
        List<Fee> list = this.getJdbcTemplate().query(findAll, feeMapper);
        return list;
    }
    public Fee findById(int id) {
        Object[] params = {id};
        FeeMapper feeMapper = new FeeMapper();
        Fee fee = (Fee)this.getJdbcTemplate()
            .queryForObject(findById, params,feeMapper);
        return fee;
    }
    public void save(Fee fee) {
        Object[] params = {
            fee.getFeeName(),
            fee.getBaseDuration(),
            fee.getBaseCost(),
            fee.getUnitCost(),
            fee.getCreateTime(),
            fee.getStartTime(),
            fee.getStatus(),
            fee.getDescr(),
            fee.getCostType()
        };
        this.getJdbcTemplate().update(save,params);
    }
    public void update(Fee fee) {
        Object[] params = {
                fee.getFeeName(),
                fee.getBaseDuration(),
                fee.getBaseCost(),
                fee.getUnitCost(),
                fee.getCreateTime(),
                fee.getStartTime(),
                fee.getStatus(),
                fee.getDescr(),
                fee.getCostType(),
                fee.getId()
        };
        this.getJdbcTemplate().update(update,params);
    }
}