使用Spring提供的MappingSqlQuery的方法,可以很好的封装JDBC返回的结果集,创建相应的domain对象,MappingSqlQuery的子类必须要实现mapRow方法,mapRow方法从底层的ResultSet中获取数据,返回对应的domain对象
数据库脚本(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
`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.MappingSqlQuery;
import java.util.List;
public interface ITestDAO ... {
public List getUserByName(String name);
}
import java.util.List;
public interface ITestDAO ... {
public List getUserByName(String name);
}
Domain对象
package
ch8.MappingSqlQuery;
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;
}
}
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;
}
}
MappingSqlQuery抽象子类及实现类
这里之所以进行了2层设计,主要是考虑到每一个查询sql的参数不一定相同,所以把sql参数的配置放到了单独的子类中,如SelectByName
package
ch8.MappingSqlQuery;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.jdbc.object.MappingSqlQuery;
public abstract class AbstractSelect extends MappingSqlQuery ... {
public AbstractSelect(DataSource dataSource,String sql)...{
super(dataSource,sql);
}
protected Object mapRow(ResultSet rs, int rowNum) throws SQLException ...{
User user=new User(rs.getString("testid"),rs.getString("testname"));
return user;
}
}
package ch8.MappingSqlQuery;
import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
public class SelectByName extends AbstractSelect ... {
public SelectByName(DataSource dataSource,String sql)...{
super(dataSource,sql);
this.declareParameter(new SqlParameter(Types.VARCHAR)); //sql中注入一个参数
}
}
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.jdbc.object.MappingSqlQuery;
public abstract class AbstractSelect extends MappingSqlQuery ... {
public AbstractSelect(DataSource dataSource,String sql)...{
super(dataSource,sql);
}
protected Object mapRow(ResultSet rs, int rowNum) throws SQLException ...{
User user=new User(rs.getString("testid"),rs.getString("testname"));
return user;
}
}
package ch8.MappingSqlQuery;
import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
public class SelectByName extends AbstractSelect ... {
public SelectByName(DataSource dataSource,String sql)...{
super(dataSource,sql);
this.declareParameter(new SqlParameter(Types.VARCHAR)); //sql中注入一个参数
}
}
DAO实现类
package
ch8.MappingSqlQuery;
import java.util.List;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
public class TestDAOImpl extends JdbcDaoSupport implements ITestDAO ... {
private SelectByName selectByName;
private final String sql="select * from test where testname=?"; //查询SQL
public SelectByName getSelectByName() ...{
return selectByName;
}
public void setSelectByName(SelectByName selectByName) ...{
this.selectByName = selectByName;
}
public List getUserByName(String name) ...{
selectByName=new SelectByName(getDataSource(),sql); //构造SelectByName对象,以便使用MappingSqlQuery封装Resultset
//return selectByName.execute(name);
return selectByName.execute(new Object[]...{name});
}
}
import java.util.List;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
public class TestDAOImpl extends JdbcDaoSupport implements ITestDAO ... {
private SelectByName selectByName;
private final String sql="select * from test where testname=?"; //查询SQL
public SelectByName getSelectByName() ...{
return selectByName;
}
public void setSelectByName(SelectByName selectByName) ...{
this.selectByName = selectByName;
}
public List getUserByName(String name) ...{
selectByName=new SelectByName(getDataSource(),sql); //构造SelectByName对象,以便使用MappingSqlQuery封装Resultset
//return selectByName.execute(name);
return selectByName.execute(new Object[]...{name});
}
}
测试代码:
package
ch8.MappingSqlQuery;
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/MappingSqlQuery/applicationContext.xml");
TestDAOImpl testDAOImpl=(TestDAOImpl)context.getBean("testDAO");
List result=testDAOImpl.getUserByName("name1");
for (Iterator iter = result.iterator(); iter.hasNext();) ...{
User element = (User) iter.next();
System.out.println(element);
}
}
}
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/MappingSqlQuery/applicationContext.xml");
TestDAOImpl testDAOImpl=(TestDAOImpl)context.getBean("testDAO");
List result=testDAOImpl.getUserByName("name1");
for (Iterator iter = result.iterator(); iter.hasNext();) ...{
User element = (User) iter.next();
System.out.println(element);
}
}
}
结果:
name1--1
可以看到,我们并没有自己去处理ResultSet,同样也获得了user对象