Spring的DAO模块

Spring的DAO模块提供了对JDBC、Hibernate、JDO等DAO层支持。

DAO模块依赖 commons-pool.jar、commons-collections.jar

package com.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class IPeopleDaoImpl extends JdbcDaoSupport implements IPeopleDao{

    public void initDatabase(){//创建表
        String sql="create table People(" +
                "id int primary key auto_increment," +
                "name varchar(100) not null," +
                "age int,"+
                "sex varchar(10)," +
                "birthday DateTime)";
        getJdbcTemplate().execute(sql);
    }
    
    
    
    
    
    
    
    
    public void addPeople(People people) {
        String sql="insert into people(name,sex,age,birthday)" +
                "values(?,?,?,?)";
        getJdbcTemplate().update(
                sql,new Object[]{
                        people.getName(),people.getSex(),people.getAge(),people.getBirthday()
                        });
    }

    public int getPeopleCount() {
        String sql="select count(*) from people";
        int count=getJdbcTemplate().queryForInt(sql);
        return count;
    }

    public String getPeopleName(int id) {
        String sql="select name from people where id="+id;
        String name=getJdbcTemplate().queryForObject(sql, String.class);
        return name;
    }

    public List<People> listPeoples() {
        String sql="select * from people";
        List<Map<String,Object>> list=getJdbcTemplate().queryForList(sql);
        List<People> peopleList=new ArrayList<People>();
        
        for(Map<String,Object> row: list){
            People people=new People();
            people.setId((Integer)row.get("id"));
            people.setName((String)row.get("name"));
            people.setSex((String)row.get("sex"));
            people.setBirthday((Date)row.get("birthday"));
            people.setAge((Integer)row.get("age"));
            peopleList.add(people);
        }
        return peopleList;
    }

}


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

    
    <!--<bean id="aopMethodBeforeInterceptor" class="org.springframework.aop.support.NameMatchMethodPointcutAdvisor">
        <property name="advice">
            <bean class="com.aop.MethodBeforeInterceptor"></bean>
        </property>
        <property name="mappedName" value="withAop"></property>
    </bean>
    
    --><!--<bean id="aopMethodAfterInterceptor" class="org.springframework.aop.support.NameMatchMethodPointcutAdvisor">
        <property name="advice">
            <bean class="com.aop.MethodAfterInterceptor">
            </bean>
        </property>
        <property name="mappedName" value="withAop"></property>
    </bean>
    -->
    
    <!--<bean id="aopMethodAfterInterceptor" class="org.springframework.aop.support.RegexpMethodPointcutAdvisor">
        <property name="advice">
            <bean class="com.aop.MethodAfterInterceptor">
            </bean>
        </property>
        <property name="patterns">
            <list>
                <value>.*withAop</value>
                <value>.*like.*</value>
            </list>
        </property>
    </bean>
    
    <bean id="aopThrowsInterceptor" class="org.springframework.aop.support.NameMatchMethodPointcutAdvisor">
        <property name="advice">
            <bean class="com.aop.ThrowsInterceptor"></bean>
        </property>
        <property name="mappedName" value="withAop"></property>
    </bean>
    
    --><!--  Service对象，安装到ProxyFactoryBean对象中 -->
    <!--<bean id="aopService" class="org.springframework.aop.framework.ProxyFactoryBean">
        <property name="interceptorNames">
            <list>
                <value>aopMethodBeforeInterceptor</value>
                <value>aopMethodAfterInterceptor</value>
                <value>aopThrowsInterceptor</value>
            </list>
        </property>
        <property name="target">
            <bean class="com.aop.AopServiceImpl">
                <property name="name" value="sss"></property>
            </bean>            
        </property>
    </bean>
-->
    <bean id="configproperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="location" value="jdbc.properties"/>
   </bean>
    
   <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
           <property name="properties" ref="configproperties"/>
   </bean>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="driverClassName" value="${jdbc.driver}" />
    </bean>
    <!--<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" >
        <property name="url" value="jdbc:mysql://localhost:3306/test_1?characterEncoding=UTF-8" />
        <property name="username" value="guodaxia" />
        <property name="password" value="guodaxia" />
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    </bean>
    
    --><bean id="peopleDao" class="com.dao.IPeopleDaoImpl" depends-on="dataSource" >
        <property name="dataSource" ref="dataSource" />
    </bean>
    
    
    
    

</beans>

package com.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class IPeopleDaoImpl extends JdbcDaoSupport implements IPeopleDao{

    public void initDatabase(){//创建表
        String sql="create table People(" +
                "id int primary key auto_increment," +
                "name varchar(100) not null," +
                "age int,"+
                "sex varchar(10)," +
                "birthday DateTime)";
        getJdbcTemplate().execute(sql);
    }
    
    
    
    
    
    
    
    
    public void addPeople(People people) {
        String sql="insert into people(name,sex,age,birthday)" +
                "values(?,?,?,?)";
        getJdbcTemplate().update(
                sql,new Object[]{
                        people.getName(),people.getSex(),people.getAge(),people.getBirthday()
                        });
    }

    public int getPeopleCount() {
        String sql="select count(*) from people";
        int count=getJdbcTemplate().queryForInt(sql);
        return count;
    }

    public String getPeopleName(int id) {
        String sql="select name from people where id="+id;
        String name=getJdbcTemplate().queryForObject(sql, String.class);
        return name;
    }

    public List<People> listPeoples() {
        String sql="select * from people";
        List<Map<String,Object>> list=getJdbcTemplate().queryForList(sql);
        List<People> peopleList=new ArrayList<People>();
        
        for(Map<String,Object> row: list){
            People people=new People();
            people.setId((Integer)row.get("id"));
            people.setName((String)row.get("name"));
            people.setSex((String)row.get("sex"));
            people.setBirthday((Date)row.get("birthday"));
            people.setAge((Integer)row.get("age"));
            peopleList.add(people);
        }
        return peopleList;
    }

}

package com.dao;

import java.util.List;

public interface IPeopleDao {
    public String getPeopleName(int id);
    public void addPeople(People people);
    public int getPeopleCount();
    public List<People> listPeoples();

}

package com.Test;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.dao.IPeopleDao;
import com.dao.People;

public class TestDao {
    
    /*public static void main(String[] args){
        String time="2011-12-07";
        System.out.println(TransactToDate(time).toLocaleString());
    }*/
    
    public static void main(String[] args){
        Test1();
    }
    
    
    public static void Test1(){
        ApplicationContext con=new ClassPathXmlApplicationContext("applicationContext.xml");
        IPeopleDao personDao=(IPeopleDao) con.getBean("peopleDao");
        
        /*People p=new People();
        p.setName("Helloween");
        p.setSex("男");
        p.setAge(12);
        p.setBirthday(TransactToDate("2005-12-3"));
        personDao.addPeople(p);*/
        System.out.println(personDao.getPeopleCount());
        List<People> peo=personDao.listPeoples();
        System.out.println(peo.get(0).getName());
        }
    
    public static Date TransactToDate(String d) {
         String pattern = "yyyy-MM-dd";
         SimpleDateFormat df = new SimpleDateFormat(pattern);
         Date date=null;
        try {
            date = df.parse(d);
        } catch (ParseException e) {
            e.printStackTrace();
        }
         return date;
    }

}

解析例子：

DaoImpl继承JdbcDaoSupport类

JdbcDaoSupport中有一个dataSource变量，在配置文件中配置数据源的值，使用getJdbcTemplate()获取JdbcTemplate对象，使用JdbcTemplate对象操作数据库

我这里用到的几个方法：

getJdbcTemplate().execute(sql);//我这里用来建表

getJdbcTemplate().update(
                sql,new Object[]{
                        people.getName(),people.getSex(),people.getAge(),people.getBirthday()
                        });//执行update操作，并在sql语句中使用了?通配符使用数组赋值

getJdbcTemplate().queryForInt(sql);//查询操作返回int值

getJdbcTemplate().queryForObject(sql, String.class);//将查询的结果转化成对象

getJdbcTemplate().queryForList(sql);//将查询的结构转化成结果集的List集合

 

Template()对象具有一些局限性，所以我们可以使用其他类进行操作

MappingSQLQquery抽象类查询

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.object.MappingSqlQuery;

public class PeopleMappingQuery extends MappingSqlQuery{

    @Override
    protected Object mapRow(ResultSet rs, int columnIndex) throws SQLException {
        People people=new People();
        people.setId(rs.getInt("id"));
        people.setName(rs.getString("name"));
        people.setBirthday(rs.getDate("birthday"));
        people.setAge(rs.getInt("age"));
        people.setSex(rs.getString("sex"));    
        return people;
    }

}

@SuppressWarnings("unchecked")
    public List<People> findPeople(int age,String name){
        PeopleMappingQuery peopleQuery=new PeopleMappingQuery();
        peopleQuery.setDataSource(getDataSource());//因为我是在JdbcDao的继承类中操作的，所以使用getDataSource获取dateSource，当然我在配置文件中将参数设置进去了
        peopleQuery.setSql("select * from people where age=? and name=?");
        peopleQuery.declareParameter(new SqlParameter(Types.INTEGER));
        peopleQuery.declareParameter(new SqlParameter(Types.VARCHAR));
        peopleQuery.compile();//遍历结果集
        
        return peopleQuery.execute(new Object[]{age,name});
    }
    @SuppressWarnings("unchecked")
    public List<People> findAllPeople(){
        PeopleMappingQuery peopleQuery=new PeopleMappingQuery();
        peopleQuery.setDataSource(getDataSource());
        peopleQuery.setSql("select * from people");
        peopleQuery.compile();//遍历结果集
        
        return peopleQuery.execute(new Object[]{});
    }

package com.dao;

import java.util.List;

public interface IPeopleDao {
    public String getPeopleName(int id);
    public void addPeople(People people);
    public int getPeopleCount();
    public List<People> listPeoples();
    public List<People> findAllPeople();
    public List<People> findPeople(int age,String name);

}

package com.Test;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.dao.IPeopleDao;
import com.dao.People;

public class TestDao {
    
    /*public static void main(String[] args){
        String time="2011-12-07";
        System.out.println(TransactToDate(time).toLocaleString());
    }*/
    
    public static void main(String[] args){
        Test1();
    }
    
    
    public static void Test1(){
        ApplicationContext con=new ClassPathXmlApplicationContext("applicationContext.xml");
        IPeopleDao personDao=(IPeopleDao) con.getBean("peopleDao");
        
        /*People p=new People();
        p.setName("allo");
        p.setSex("男");
        p.setAge(3);
        p.setBirthday(TransactToDate("2013-4-3"));
        personDao.addPeople(p);*/
//        System.out.println(personDao.getPeopleCount());
//        List<People> peo=personDao.listPeoples();
//        List<People>peo=personDao.findAllPeople();
        List<People>peo=personDao.findPeople(3,"allo");
        for(int i=0;i<peo.size();i++){
            System.out.println(peo.get(i).getId()+" "+peo.get(i).getName()+" "+peo.get(i).getSex()+" "+peo.get(i).getAge()+" "+peo.get(i).getBirthday().toLocaleString());
        }
        }
    
    public static Date TransactToDate(String d) {
         String pattern = "yyyy-MM-dd";
         SimpleDateFormat df = new SimpleDateFormat(pattern);
         Date date=null;
        try {
            date = df.parse(d);
        } catch (ParseException e) {
            e.printStackTrace();
        }
         return date;
    }

}

SQLUpdate更新

package com.dao;

import java.sql.Types;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;



public class PeopleUpdate extends SqlUpdate{
    public int UpdatePeople(BasicDataSource ds,String name,int id){
        setDataSource(ds);
        setSql("update people set name=? where id=?");
        
        declareParameter(new SqlParameter(Types.VARCHAR));
        declareParameter(new SqlParameter(Types.INTEGER));
        compile();//编译sql
        return update(new Object[]{name,id});
    }
    public int addPeople(BasicDataSource ds,People p){
        setDataSource(ds);
        
        setSql("insert into People(name,sex,age,birthday) values(?,?,?,?)");
        
        declareParameter(new SqlParameter(Types.VARCHAR));
        declareParameter(new SqlParameter(Types.VARCHAR));
        declareParameter(new SqlParameter(Types.INTEGER));
        declareParameter(new SqlParameter(Types.DATE));
        compile();//编译sql
        return update(new Object[]{p.getName(),p.getSex(),p.getAge(),p.getBirthday()});
    }
}

public void addPeopleSQLUpadate(People p){
        PeopleUpdate up=new PeopleUpdate();
        up.addPeople((BasicDataSource) getDataSource(), p);
    }
    
    public void update(String name,int id){
        PeopleUpdate up=new PeopleUpdate();
        up.UpdatePeople((BasicDataSource) getDataSource(), name, id);
    }

public static void Test1(){
        ApplicationContext con=new ClassPathXmlApplicationContext("applicationContext.xml");
        IPeopleDao personDao=(IPeopleDao) con.getBean("peopleDao");
        
        /*People p=new People();
        p.setName("guofei");
        p.setSex("女");
        p.setAge(18);
        p.setBirthday(TransactToDate("1998-04-03"));
        personDao.addPeopleSQLUpadate(p);*/
        personDao.update("wuzhi",1);
        }

SQLFunction返回单一结果

默认返回int类型，也可以重载返回其他类型。相当于JdbcTemplate的queryForInt等