spring JDBC的应用

1:首先在类路径下面配置访问数据的一些基本信息，包括连接数据库的地址，用户，密码
jdbc.properties

jdbc.main.server=localhost:3306/test
jdbc.main.user=root
jdbc.main.password=123456


2：在spring的配置文件中配置NamedParameterJdbcTemplate，并且要注入DataSource，因为NamedParameterJdbcTemplate需要引用它来访问数据库

applicatonContext.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="placeholderPrefix" value="$${" />
<property name="locations">
<list>
<value>/WEB-INF/jdbc.properties</value>
</list>
</property>
</bean>

<bean name="parentDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource"
abstract="true">
<property name="maximumConnectionCount" value="40" />
<property name="minimumConnectionCount" value="2" />
<property name="simultaneousBuildThrottle" value="40" />
<property name="prototypeCount" value="2" />
<property name="trace" value="true" />
<property name="verbose" value="false" />
</bean>

<bean id="mainDataSource" parent="parentDataSource">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="driverUrl">
<value>jdbc:mysql://$${jdbc.main.server}?useUnicode=true&amp;characterEncoding=gbk&amp;user=$${jdbc.main.user}&amp;password=$${jdbc.main.password}&amp;zeroDateTimeBehavior=convertToNull</value>
</property>
<property name="user" value="$${jdbc.main.user}"/>
<property name="password" value="$${jdbc.main.password}"/>
<property name="alias" value="main"/>
<property name="maximumConnectionCount" value="200" />
<property name="simultaneousBuildThrottle" value="20"/>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="mainDataSource" />
</bean>


<bean id="corporateEventDao" class="com.example.JdbcCorporateEventDao">
    <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>


<context:property-placeholder location="jdbc.properties"/>

</beans>

3：配置需要持久化的对象实体JAVA bean

public class Actor {

    private Long id;
    private String firstName;
    private String lastName;
    private int age;
    //专业
    private String specialty;
    public String getFirstName() {
        return this.firstName;
    }
   
    public String getLastName() {
        return this.lastName;
    }
   
    public Long getId() {
        return this.id;
    }
   
    // setters omitted...

}


4：定义DAO对实体对象需要的操作

public interface CorporateEventDao{

public int countOfActors(Actor exampleActor);


public long addActor(Actor exampleActor);

public boolean updateActor(long userId);

public Actor findActorById(long userId);

public List<Actor> getAllUser();

}

5：实现DAO，并且将NamedParameterJdbcTemplate注入到DAO中。

public class CorporateEventDaoImpl implements CorporateEventDao {


private NamedParameterJdbcTemplate jdbcTemplate;

public void setAppJdbc(NamedParameterJdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
private ParameterizedBeanPropertyRowMapper<Actor> rowMapper=ParameterizedBeanPropertyRowMapper.newInstance(Actor.class);


@Override
public int countOfActors(Actor exampleActor) {

String sql = "select count(*) from t_actor where first_name =fastName and last_name=:lastName";
SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);

return this.jdbcTemplate.queryForInt(sql, namedParameters);
}

@Override
public long addActor(Actor exampleActor) {
// TODO Auto-generated method stub
String sql = "insert into t_actor(first_name,last_name,age,specialty) values(?,?,?,?)";
SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
return this.jdbcTemplate.update(sql, namedParameters);
}

@Override
public boolean updateActor(long userId) {
// TODO Auto-generated method stub
String sql = "update t_actor set id=?";
return this.jdbcTemplate.getJdbcOperations().update(sql,userId)>0;
}

@Override
public Actor findActorById(long userId) {
// TODO Auto-generated method stub
String sql = "select first_name,last_name,age,specialty from t_actor where userId=?";

return this.jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{userId}, rowMapper);
}

@Override
public List<Actor> getAllUser() {
// TODO Auto-generated method stub
String sql = "select first_name,last_name,age,specialty from t_actor";
return this.jdbcTemplate.getJdbcOperations().queryForList(sql, null, rowMapper);
}

public Actor findActor(String specialty, int age) {

    String sql = "select id, first_name, last_name from T_ACTOR" +
            " where specialty = ? and age = ?";
   
    // notice the wrapping up of the argumenta in an array
    return (Actor) jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[] {specialty, age}, rowMapper);
}

}