【笔记】Spring4框架系列 [ 6 ] 之 JdbcTemplate 小实战

spring+struts2+Jdbc实现【增删改查】

【web.xml】

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
  <display-name></display-name>

  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>

  <filter>
    <filter-name>struts2</filter-name>
    <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
  </filter>
  <filter-mapping>
    <filter-name>struts2</filter-name>
    <url-pattern>/*</url-pattern>
  </filter-mapping>

  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>

</web-app>

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

<!-- mysql -->
    <bean id="mysql_dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/athl_ajax"/>
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="user" value="root"/>
        <property name="password" value="root"/>
        <property name="initialPoolSize" value="3" />  
       <!--  连接的最大空闲时间  单位秒 默认是0-代表永远不会断开连接  超过设定时间的空闲连接将会断开   -->
        <property name="maxIdleTime" value="30"/>  
        <!-- 连接池中拥有的最大连接数 默认值为15个   -->
        <property name="maxPoolSize" value="20"/>  
        <!-- 连接池中保持的最小连接数  默认值为3个 -->
        <property name="minPoolSize" value="3"/>  
        <!-- 将连接池的连接数保持在minpoolsize 必须小于maxIdleTime设置  默认值为0代表不处理  单位秒   -->
        <property name="maxIdleTimeExcessConnections" value="15"/>
    </bean>

<!-- oracle -->
    <bean id="oracle_dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"></property>
        <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:orcl"></property>
        <property name="user" value="scott"></property>
        <property name="password" value="scott"></property>
        <property name="initialPoolSize" value="3"></property>
        <property name="maxPoolSize" value="10"></property>
        <property name="maxStatements" value="100"></property>
        <property name="acquireIncrement" value="2"></property>
    </bean>

    <bean id="jdbcTemplate"  class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="mysql_dataSource" />
        <!-- <constructor-arg name="dataSource" ref="oracle_dataSource" /> -->
    </bean>

    <bean id="dao" class="com.athl.dao.PersonDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
    <bean id="service" class="com.athl.service.PersonServiceImpl">
        <property name="dao" ref="dao"/>
    </bean>
    <bean id="action" class="com.athl.action.PersonAction" >
        <property name="service" ref="service" />
    </bean>

</beans>

【dao】

package com.athl.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.athl.entity.Person;

public class PersonDaoImpl {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void add(Person p) throws SQLException{
        String sql="insert into person(name,age) values(?,?)";
        jdbcTemplate.update(sql,p.getName(),p.getAge());
    }

    public void update(Person p) throws SQLException{
        String sql="update person set name=?,age=? where id=?";
        jdbcTemplate.update(sql,p.getName(),p.getAge(),p.getId());
    }

    public void del(int id) throws SQLException{
        String sql="delete from person where id=?";
        jdbcTemplate.update(sql,id);
    }
    public List<Person> query(){
        String sql="select * from person";
        return jdbcTemplate.query(sql,new RowMapper<Person>(){
            public Person mapRow(ResultSet rs, int arg1) throws SQLException {
                Person P = new Person();
                P.setId(rs.getInt("id"));
                P.setName(rs.getString("name"));
                P.setAge(rs.getInt("age"));
                return P;
            }
        });

    }
    public Person queryById(int id){
        String sql="select * from person where id=?";
        List<Person> list = jdbcTemplate.query(sql,new RowMapper<Person>(){
            public Person mapRow(ResultSet rs, int arg1) throws SQLException {
                Person P = new Person();
                P.setId(rs.getInt("id"));
                P.setName(rs.getString("name"));
                P.setAge(rs.getInt("age"));
                return P;
            }
        },id);
        return (list!=null && list.size()>0) ? list.get(0) : null;
    }
    public List<Person> queryLike(String search){
        String sql="select * from person where name like ? or age like ?";

        return jdbcTemplate.query(sql,new RowMapper<Person>(){
            public Person mapRow(ResultSet rs, int arg1) throws SQLException {
                Person P = new Person();
                P.setId(rs.getInt("id"));
                P.setName(rs.getString("name"));
                P.setAge(rs.getInt("age"));
                return P;
            }
        },"%"+search+"%","%"+search+"%");
    }

}

源码下载：http://download.csdn.net/detail/jul_11th/9741805

谢谢支持！