Spring集成JDBC操作数据库实例教程

首先，我们需要安装mysql数据库，并下载相应的驱动包

库脚本如下：

 

CREATE   TABLE  `test` (
  `testid`  varchar ( 10 )  default   NULL ,
  `testname`  varchar ( 10 )  default   NULL
) ENGINE = InnoDB  DEFAULT  CHARSET = gb2312;

表中数据为：
1   name1
2   name2
3   name3
4   name4
5   name5

 

 DAO接口

 

package  ch8.jdbcTemplage;

import  java.util.List;

public   interface  ITestDAO  ... {
  public List getAll();
}

 

对应数据表的JavaBean

 

package  ch8.jdbcTemplage;

public   class  User  ... {
  private String name;
  private String id;
public User(String name, String id) ...{

    this.name = name;
    this.id = id;
}
public String getId() ...{
    return id;
}
public void setId(String id) ...{
    this.id = id;
}
public String getName() ...{
    return name;
}
public void setName(String name) ...{
    this.name = name;
}

public String toString() ...{
    
    return this.id+"--"+this.name;
}

}

 

DAO实现

 

package  ch8.jdbcTemplage;

import  java.sql.Connection;
import  java.sql.PreparedStatement;
import  java.sql.ResultSet;
import  java.sql.SQLException;
import  java.util.ArrayList;
import  java.util.List;

import  org.springframework.dao.DataAccessException;
import  org.springframework.jdbc.core.PreparedStatementCallback;
import  org.springframework.jdbc.core.PreparedStatementCreator;
import  org.springframework.jdbc.core.support.JdbcDaoSupport;

public   class  TestDAOImpl  extends  JdbcDaoSupport  implements  ITestDAO  ... {

    public List getAll() ...{
        return (List)this.getJdbcTemplate().execute(
          new PreparedStatementCreator()...{
               public PreparedStatement createPreparedStatement(Connection connection) throws SQLException...{
                  return connection.prepareStatement("select * from test");
               }
              
          },new PreparedStatementCallback()...{
              public Object doInPreparedStatement(PreparedStatement ps) throws SQLException,DataAccessException...{
                  List result=new ArrayList();
                  ResultSet rs=ps.executeQuery();
                  User t=null;
                  while(rs.next())...{
                      t=new User(rs.getString("testid"),rs.getString("testname"));
                      result.add(t);
                  }
                  return result;
              }
              
          }        
        );
    }

}

 

配置文件：

 

<? 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 ="dataSource"  class ="org.apache.commons.dbcp.BasicDataSource" >
   < property  name ="driverClassName" >
     < value > com.mysql.jdbc.Driver </ value >
   </ property >
   < property  name ="username" >
     < value > root </ value >
   </ property >
   < property  name ="password" >
     < value > 1234 </ value >
   </ property >
   < property  name ="url" >
     < value > jdbc:mysql://localhost:3306/spring </ value >
   </ property >
</ bean >

< bean  id ="testDAO"  class ="ch8.jdbcTemplage.TestDAOImpl" >
   < property  name ="dataSource" >
     < ref  bean ="dataSource" />
   </ property >
</ bean >

</ beans >

 

测试代码：

 

package  ch8.jdbcTemplage;

import  java.util.Iterator;
import  java.util.List;

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

public   class  Test  ... {

    /** *//**
     * @param args
     */
    public static void main(String[] args) ...{
        ApplicationContext context=new ClassPathXmlApplicationContext("ch8/jdbcTemplage/applicationContext.xml");
        TestDAOImpl testDAOImpl=(TestDAOImpl)context.getBean("testDAO");
        List result=testDAOImpl.getAll();
        for (Iterator iter = result.iterator(); iter.hasNext();) ...{
            User element = (User) iter.next();
            System.out.println(element);            
        }    
     }

}