两种方法测试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:编译运行.以上代码经过测试，能够正常运行,