spring的持久化数据库访问

spring的吃接话数据库访问

 

 

---

 

 

1.通用DAO的支持

2.spring对JDBC的支持

3.spring数据操作实例

4.数据访问异常

 

 

---

 

1.通用DAO的支持

 

spring对通用dao的支持，通常可以如下的方式进行：通用dao类继承JdbcDaoSupport，在其中直接使用getJdbcTemplate实现数据的访问操作，同时在通用dao类中添加属性dataSource，同时设置dataSource的set方法。配置文件中首先需要设置DataSource，然后将datasource注入到通用的DAO中。

 

 

---

 

2.spring对JDBC的支持

 

spring对于JDBC的支持主要是通过JdbcTemplate实现。JdbcTemplate提供了处理JDBC的统一方式和线程安全。在实际的web项目中，常常是通过spring的注入机制，实现JdbcTemplate的注入。

 

 

---

 

3.spring数据操作实例

 

3.1Quering

int rowCount = this.jdbcTemplate.queryForInt("select count(0) from t_accrual");

int countOfActorsNamedJoe

    = this.jdbcTemplate.queryForInt("select count(0) from t_actors where first_name = ?", new 

   Object[]{"Joe"});

String surname = (String) this.jdbcTemplate

    .queryForObject("select surname from t_actor where id = ?", new Object[]{new Long(1212)}, 

    String.class);

Actor actor = (Actor) this.jdbcTemplate.queryForObject(

    "select first_name, surname from t_actor where id = ?",

    new Object[]{new Long(1212)},

    new RowMapper() {



        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

            Actor actor = new Actor();

            actor.setFirstName(rs.getString("first_name"));

            actor.setSurname(rs.getString("surname"));

            return actor;

        }

    });

Collection actors = this.jdbcTemplate.query(

    "select first_name, surname from t_actor",

    new RowMapper() {



        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

            Actor actor = new Actor();

            actor.setFirstName(rs.getString("first_name"));

            actor.setSurname(rs.getString("surname"));

            return actor;

        }

    });

3.2Updating

this.jdbcTemplate.update("insert into t_actor (first_name, surname) values (?, ?)", new Object[] {

    "Leonor", "Watling"});

this.jdbcTemplate.update("delete from orders");

this.jdbcTemplate.update("update t_actor set weapon = ? where id = ?", new Object[] {

   "Banjo", new Long(5276)});

3.3 Other Operations

this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

调用存储过程：

this.jdbcTemplate.update("call SUPPORT.REFRESH_ACTORS_SUMMARY(?)", new Object[]{

   new Long(unionId)})

3.4 Best Practices

 

1.在使用JdbcTemplate时，最好的方法是使用spring的依赖注入。A common idiom when using the  JdbcTemplate   class (and the associated  SimpleJdbcTemplate   and  NamedParameterJdbcTemplate   classes) is to configure a  DataSource   in your Spring configuration file, and then dependency inject that shared  DataSource   bean into your DAO classes; the  JdbcTemplate   is created in the setter for the  DataSource . This leads to DAOs that look in part like this:

 

public class JdbcCorporateEventDao implements CorporateEventDao {



    private JdbcTemplate jdbcTemplate;



    public void setDataSource(DataSource dataSource) {

        this.jdbcTemplate = new JdbcTemplate(dataSource);



    }



    // JDBC-backed implementations of the methods on the CorporateEventDao
 follow...



}

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">



    <bean id="corporateEventDao" class="com.example.JdbcCorporateEventDao">

        <property name="dataSource" ref="dataSource"/>

    </bean>



    <!-- the DataSource
 (parameterized for configuration via a PropertyPlaceHolderConfigurer

) -->



    <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">

        <property name="driverClassName" value="${jdbc.driverClassName}"/>

        <property name="url" value="${jdbc.url}"/>

        <property name="username" value="${jdbc.username}"/>

        <property name="password" value="${jdbc.password}"/>

    </bean>



</beans>

3.5NamedParameterJdbcTemplate的使用

 

The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters (as opposed to programming JDBC statements using only classic placeholder ('?') arguments. The NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work. 5NamedParameterJdbcTemplate和JdbcTemplate的区别是在其中使用named parameters，而不是？。下面是实例代码：

 

// some JDBC-backed DAO class...



private NamedParameterJdbcTemplate namedParameterJdbcTemplate;



public void setDataSource(DataSource dataSource) {

    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

}



public int countOfActorsByFirstName(String firstName) {



    String sql = "select count(0) from T_ACTOR where first_name = :first_name";



    SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);



    return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);

}

3.6DataSource的实例

 

DriverManagerDataSource dataSource = new DriverManagerDataSource();

dataSource.setDriverClassName("org.hsqldb.jdbcDriver");

dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");

dataSource.setUsername("sa");

dataSource.setPassword("");

---

 

4.数据访问异常

 

Spring provides a convenient translation from technology-specific exceptions like SQLException to its own exception class hierarchy with the DataAccessException as the root exception. These exceptions wrap the original exception so there is never any risk that one might lose any information as to what might have gone wrong.spring提供了将SQLException转换成spring自己的异常类型的机制，这样做的好处是能够避免丢失exception中任何有效信息。