spring jdbc模板 数据库访问例子

import java.io.*;
import java.sql.*;
import javax.sql.DataSource;
import java.util.*;
import junit.framework.*;
import org.apache.commons.logging.*;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.datasource.*;

public class TestSpringDAO extends TestCase {
 private static Log  logger = LogFactory.getLog(TestSpringDAO.class);
 private static DataSource datasource = null;
 public TestSpringDAO()  throws Exception {
  logger.info("新建 "+this.getClass().getName());
  try {
   datasource = this.getDataSource();
   JdbcTemplate template = new JdbcTemplate(datasource);
  }catch(Exception e) {
   e.printStackTrace();
  }
 }
 public void setUp() {
  logger.info("--------------------------------");
 }
 public void tearDown() {
 // logger.info("--------------------------------");
 }
 /*private DataSource getDataSourceFromXML() throws Exception {
  InputStream is = new FileInputStream("beans.xml");
  XmlBeanFactory factory = new XmlBeanFactory(is);
 // XmlBeanFactory factory = new XmlBeanFactory(new InputStreamResource(is));
  DataSource ds = (DataSource) factory.getBean("dataSource");
  return ds;
 }*/
 private DataSource getDataSource() throws Exception {
 // BasicDataSource ds = new org.apache.commons.dbcp.BasicDataSource();
  DriverManagerDataSource ds = new DriverManagerDataSource();
  ds.setDriverClassName("org.gjt.mm.mysql.Driver");
  ds.setUrl("jdbc:mysql://localhost/test");
  ds.setUsername("root");
  ds.setPassword("123456");
  return ds;
 }
 public void _testJdbc() {
  DataSource ds = null;
  Connection conn = null;
  Statement stmt = null;
  try {
   conn = datasource.getConnection();
   stmt = conn.createStatement();
   stmt.executeUpdate("UPDATE user SET username = 'testJdbc' WHERE id = 4");
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("_testJdbc()"+e);
  }finally {
   if (stmt != null) {
    try {
     stmt.close();
    } catch (SQLException ex) {
     logger.warn("Exception in closing JDBC Statement", ex);
    }
   }
   if (conn != null) {
    try {
     conn.close();
    } catch (SQLException ex) {
     logger.warn("Exception in closing JDBC Connection", ex);
    }
   }
  }
 }
 public void _testUpdate() {
  try {
   JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
   jdbcTemplate.update("UPDATE user SET username = 'testUpdate' WHERE id = 4");
   logger.info("testSpringDAO()...ok");
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("testUpdate()"+e);
  }
 }
 public void _testUpdate2() {
  try {
   JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
   jdbcTemplate.update(
    "UPDATE user SET password = ? WHERE id = ?",
    new PreparedStatementSetter() {
     public void setValues(PreparedStatement ps) throws SQLException {
      ps.setString(1, "testUpdate2");
      ps.setInt(2, 4);
     
     }
    }
   );
   logger.info("testUpdate2()...ok");
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("testUpdate2()"+e);
  }
 }
 public void _testInsert() {
  try {
   JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
   jdbcTemplate.update("INSERT INTO user (id, username, password) VALUES ('', 'testInsert', 'testInsert')");
   logger.info("testInsert()...ok");
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("testInsert()"+e);
  }
 }
 public void _testQuery() {
  class User {
   int id;
   String username;
   String password;
   void setId(String ids)  { id = Integer.parseInt(ids); }
   void setUsername(String user) { username = user;  }
   void setPassword(String pass) { password = pass;  }
   public String toString() {
    return "Id."+id+" "+username+":"+password;
   }
  }
  final List userList = new ArrayList();
  try {
   JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
   jdbcTemplate.query(
    "SELECT id, username, password FROM user WHERE id>0",
    new RowCallbackHandler() {
     public void processRow(ResultSet rs) throws SQLException {
      User user = new User();
      user.setId(rs.getString("id"));
      user.setUsername(rs.getString("username"));
      user.setPassword(rs.getString("password"));
      userList.add(user);
     }
    }
   );
   //打印输出
   for(Iterator i=userList.iterator()
; i.hasNext(); ) {
    User user = (User)i.next();
    System.out.println(user.toString());  
   }
   logger.info("testQuery()...ok");
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("testInsert()"+e);
  }
 }
 public void testQueryCount() {
  try {
   JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
          int count = jdbcTemplate.queryForInt("select count(*) from user");
   logger.info("testQueryCount()..."+count);
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("testQueryCount()"+e);
  }
 }
 public void testQueryString() {
  try {
   JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
          String name = (String)jdbcTemplate.queryForObject("select username from user where id=1", java.lang.String.class);
   logger.info("testQueryString()..."+name);
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("testQueryString()"+e);
  }
 }
 public void testQueryList() {
  try {
   JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
          List rows = jdbcTemplate.queryForList("select * from user");
   logger.info("testQueryList()..."+rows);
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("testQueryList()"+e);
  }
 }
 public void _testDelete() {
  try {
   JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
   jdbcTemplate.update("DELETE FROM user WHERE username='testInsert'");
   logger.info("testDelete()...ok");
  }catch(Exception e) {
   e.printStackTrace();
   logger.warn("testInsert()"+e);
  }
 }
 public static void main(String[] args) {
  com.bs.mpsp.common.MyTestRunner.run(TestSpringDAO.class);
 // junit.textui.TestRunner.run(TestSpringDAO.class);
 }
 
}