两种方法测试spring中的jdbc

 

两种方法测试spring中的jdbc

  JDBC是一个非常基础的数据存取API,spring对其进行简单的封装,
  下面以sqlserver中自带的pubs数据库Authors表进行测试.

  1):编写Authors.java，其每个对象对应于数据库中的一条记录

  package jdbc;
public class Authors {
   String  lname=null;
   String fname=null;
   String phone=null;
   String address=null;
    public String getAddress() {
 return address;
    }
    public String getFname() {
 return fname;
    }
    public String getLname() {
 return lname;
    }
    public String getPhone() {
 return phone;
    }
    public void setPhone(String phone) {
 this.phone = phone;
    }
    public void setLname(String lname) {
 this.lname = lname;
    }
    public void setFname(String fname) {
 this.fname = fname;
    }
    public void setAddress(String address) {
 this.address = address;
    }
}

  2):编写AuthorsQuery类

package jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.jdbc.object.MappingSqlQuery;
import javax.sql.DataSource;

/**
 * <p>Title: 测试spring中的jdbc</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author hanker
 * @version 1.0
 */

public class AuthorsQuery extends MappingSqlQuery {

    public AuthorsQuery(DataSource ds) {
   this.setDataSource(ds);
   this.setSql("SELECT * FROM authors");
   compile();
    }

     protected Object mapRow(ResultSet rs, int rownum) throws SQLException {

   Authors po = new Authors();
   po.setLname(rs.getString("au_lname"));
   po.setFname(rs.getString("au_fname"));
   po.setPhone(rs.getString("phone"));
   po.setAddress(rs.getString("address"));
   return po;
     }

}

  3:写spring的配置文件

  <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<!--
  - Application context definition for Petclinic on Hibernate.
 -->
<beans>
       <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName"><value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value></property>
  <property name="url"><value>jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=pubs;SelectMethod=Cursor</value></property>
  <property name="username"><value>sa</value></property>
  <property name="password"><value></value></property>
 </bean>

    <bean id="AuthorsQuery" class="jdbc.AuthorsQuery" singleton="false">
     <constructor-arg index="0"><ref bean="dataSource"/></constructor-arg>
 </bean>
 
</beans>

  4:写junit进行测试

package jdbc;

import junit.framework.*;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import java.util.List;
import java.util.Map;
/**
 * <p>Title: 测试spring中的jdbc</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author hanker
 * @version 1.0
 */

public class TestAuthorsQuery extends TestCase {
        ApplicationContext ctx=null;
 protected void setUp() throws Exception {
      ctx
   = new FileSystemXmlApplicationContext("D:\\work\\jpetstore\\src\\jdbc\\Context-jdbc.xml");
 }
 public void testQuery(){
      System.out.println("[Test1....");
     AuthorsQuery qry=(AuthorsQuery)ctx.getBean("AuthorsQuery");
     List list = qry.execute();
     if(list!=null&&list.size()>0){
  for(int i=0;i<list.size();i++){
      Authors author=(Authors)list.get(i);
      StringBuffer buf=new StringBuffer();
      buf.append(author.getLname())
   .append("|")
   .append(author.getFname())
   .append("|")
   .append(author.getPhone())
   .append("|")
   .append(author.getAddress());
      System.out.println(buf.toString());
                }
            }

        }
}

   
 5:编译运行，ok》是不是得到了你想要的结果

 White|Johnson|408 496-7223|10932 Bigge Rd.
Green|Marjorie|415 986-7020|309 63rd St. #411
Carson|Cheryl|415 548-7723|589 Darwin Ln.
O'Leary|Michael|408 286-2428|22 Cleveland Av. #14
Straight|Dean|415 834-2919|5420 College Av.
Smith|Meander|913 843-0462|10 Mississippi Dr.
Bennet|Abraham|415 658-9932|6223 Bateman St.
Dull|Ann|415 836-7128|3410 Blonde St.
Gringlesby|Burt|707 938-6445|PO Box 792
Locksley|Charlene|415 585-4620|18 Broadway Av.
Greene|Morningstar|615 297-2723|22 Graybar House Rd.
Blotchet-Halls|Reginald|503 745-6402|55 Hillsdale Bl.

6:下面测试另处一种调jdbc的方法

   编写AuthorsQuery2.java文件
 
   package jdbc;

   import java.util.List;
   import org.springframework.context.ApplicationContextException;
   import org.springframework.dao.DataAccessException;
   import org.springframework.jdbc.core.SqlParameter;
   import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * <p>Title: 测试spring中的jdbc</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author hanker
 * @version 1.0
 */

public class AuthorsQuery2 extends JdbcDaoSupport {
    public AuthorsQuery2() {
    }
    public List query(String sql) throws DataAccessException {
 
 return this.getJdbcTemplate().queryForList(sql);
    }

}

7:配置,在上面加入下面几行：

       <bean id="AuthorsQuery2" class="jdbc.AuthorsQuery2">
  <property name="dataSource"><ref local="dataSource"/></property>
 </bean>

8:写junit.
   在TestAuthorsQuery中加入函数
   public void testQuery2(){
 System.out.println("[Test2....");
  AuthorsQuery2 qry=(AuthorsQuery2)ctx.getBean("AuthorsQuery2");
  List list = qry.query("SELECT * FROM authors");
  if(list!=null&&list.size()>0){
      for(int i=0;i<list.size();i++){
   Map map=(Map)list.get(i);
   StringBuffer buf=new StringBuffer();
   buf.append(map.get("au_lname") )
       .append("|")
       .append(map.get("au_fname"))
       .append("|")
       .append(map.get("phone"))
       .append("|")
       .append(map.get("address"));
   System.out.println(buf.toString());
      }
  }
   
     }
   

9:编译运行.以上代码经过测试，能够正常运行