數據庫準備：
1
CREATE TABLE mytable(
2 id INTEGER NOT NULL,
3 name VARCHAR(50),
4 PRIMARY KEY(id));
5
package com.spring.test1;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.jdbc.object.MappingSqlQuery;
public class GenreQuery extends MappingSqlQuery 
{
private static String SQL_GENRE_QUERY = "select id,name from mytable Order By id";
public GenreQuery(DataSource ds) {
super(ds, SQL_GENRE_QUERY);
//如果有參數，在這里設置
//declareParameter(new SqlParameter("id", Types.INTEGER));
compile();
}
public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
Genre bean = new Genre();
bean.setId(rs.getLong("id"));
bean.setName(rs.getString("name"));
return bean;
}
}
package com.spring.test1;
/** *//**
* Represents a genere of shows.
*/
public class Genre {
// the id of this genere
private long id;
// the name of this genre
private String name;
/** *//**
* Empty contructor for this genere (to support javabean spec.)
*/
public Genre() {
}
/** *//**
* Constructs a new genre with a given name.
* @param name The name of this genre.
*/
public Genre(String name) {
this.name = name;
}
/** *//**
* Returns the id of this genre.
* @return The id of this genre.
*/
public long getId() {
return id;
}
/** *//**
* Sets the id of this genre.
* @param id The id of this genre.
*/
public void setId(long id) {
this.id = id;
}
/** *//**
* Returns the name of this genre.
* @return The name of this genre.
*/
public String getName() {
return name;
}
/** *//**
* Sets the name of this genre.
* @param name The name of this genre.
*/
public void setName(String name) {
this.name = name;
}
}
package com.spring.test1;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import junit.framework.TestCase;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class JdbcQueryTest extends TestCase {
private DriverManagerDataSource dataSource;
public void setUp() {
dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
dataSource.setUrl("jdbc:oracle:thin:@gsgaofei:1521:fangj");
dataSource.setUsername("test2");
dataSource.setPassword("test2");
JdbcTemplate jt = new JdbcTemplate(dataSource);
jt.execute("delete from mytable");
jt.execute("insert into mytable (id, name) values(1, 'Rock')");
jt.execute("insert into mytable (id, name) values(2, 'Ballet')");
jt.execute("insert into mytable (id, name) values(3, 'Theatre')");
jt.execute("insert into mytable (id, name) values(4, 'Classical')");
jt.execute("insert into mytable (id, name) values(5, 'Musical')");
jt.execute("insert into mytable (id, name) values(6, 'Opera')");
}
/** *//**
* 方法1
*/
public List getAllGenre1() {
StringBuffer sqlBuffer = null;
final List list = new ArrayList();
sqlBuffer = new StringBuffer();
sqlBuffer.append("SELECT * FROM mytable ORDER BY ID");
JdbcTemplate jt = new JdbcTemplate(dataSource);
jt.query(sqlBuffer.toString(), new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
Genre bean = new Genre();
bean.setId(rs.getLong("ID"));
bean.setName(rs.getString("NAME"));
list.add(bean);
}
});
return list;
}
public void testGetAllGenre1() {
System.out.println("------- getAllGenre1 result --------");
List list = getAllGenre1();
for (Iterator it = list.iterator(); it.hasNext();) {
Genre bean = (Genre) it.next();
System.out.println("ID : " + bean.getId());
System.out.println("NAME : " + bean.getName());
}
}
/** *//**
* 方法2
*/
public List getAllGenre2() {
StringBuffer sqlBuffer = null;
final List list = new ArrayList();
sqlBuffer = new StringBuffer();
sqlBuffer.append("SELECT * FROM mytable ORDER BY ID");
JdbcTemplate jt = new JdbcTemplate(dataSource);
Object obj = jt.query(sqlBuffer.toString(), new ResultSetExtractor() {
public Object extractData(ResultSet rs) throws SQLException,
DataAccessException {
while (rs.next()) {
Genre bean = new Genre();
bean.setId(rs.getLong("ID"));
bean.setName(rs.getString("NAME"));
list.add(bean);
}
return list;
}
});
return (List) obj;
}
public void testGetAllGenre2() {
System.out.println("------- getAllGenre2 result --------");
List list = getAllGenre2();
for (Iterator it = list.iterator(); it.hasNext();) {
Genre bean = (Genre) it.next();
System.out.println("ID : " + bean.getId());
System.out.println("NAME : " + bean.getName());
}
}
/** *//**
* 方法3
*/
public void testGetAllGenre3() {
System.out.println("------- getAllGenre3 result --------");
GenreQuery genreQuery = new GenreQuery(dataSource);
List list = genreQuery.execute();//這里如果有參數，可以參考API文檔設置
for (Iterator it = list.iterator(); it.hasNext();) {
Genre bean = (Genre) it.next();
System.out.println("ID : " + bean.getId());
System.out.println("NAME : " + bean.getName());
}
}
}
測試結果如下：
2005-10-09 09:21:31,316 INFO [org.springframework.jdbc.datasource.DriverManagerDataSource] - Loaded JDBC driver: oracle.jdbc.OracleDriver
------- getAllGenre1 result --------
ID : 1
NAME : Rock
ID : 2
NAME : Ballet
ID : 3
NAME : Theatre
ID : 4
NAME : Classical
ID : 5
NAME : Musical
ID : 6
NAME : Opera
2005-10-09 09:21:32,753 INFO [org.springframework.jdbc.datasource.DriverManagerDataSource] - Loaded JDBC driver: oracle.jdbc.OracleDriver
------- getAllGenre2 result --------
ID : 1
NAME : Rock
ID : 2
NAME : Ballet
ID : 3
NAME : Theatre
ID : 4
NAME : Classical
ID : 5
NAME : Musical
ID : 6
NAME : Opera
2005-10-09 09:21:33,206 INFO [org.springframework.jdbc.datasource.DriverManagerDataSource] - Loaded JDBC driver: oracle.jdbc.OracleDriver
------- getAllGenre3 result --------
ID : 1
NAME : Rock
ID : 2
NAME : Ballet
ID : 3
NAME : Theatre
ID : 4
NAME : Classical
ID : 5
NAME : Musical
ID : 6
NAME : Opera
說明：上述例子使用了三種query方法，因為這里為了方便測試所以在方法里使用了
JdbcTemplate jt = new JdbcTemplate(dataSource); 而在實際應用中我們
可以把JdbcQueryTest extends JdbcDaoSupport，這樣例子里的jt可以替換為
getJdbcTemplate()。