首页 > 代码库 > SpringJdbc之queryForXXX大全解读
SpringJdbc之queryForXXX大全解读
1.查询单个字段
Object queryForObject(String sql, Object[] args, Class requiredType)
其中Class requiredType 参数为返回值的类型(可以为常见的类库中的类类型)比如String.class
参数“requiredType”不可以是自定义的类
2.查询多个字段
Object queryForObject(String sql, Object[] args, RowMapper rowMapper)
其中Rowmapper 需要new一下 给该接口一个类类型,它负责给你返回一个对象(需要什么值要自己set,建议全部set)
如果要把查询结果封装为自定义的类,需要采用第2个方法
//只查询一列:name
String sql = "SELECT NAME FROM CUSTOMER WHERE CUST_ID = ?";
String name = (String)getJdbcTemplate().queryForObject(
sql, new Object[] { custId }, String.class);
return name;
//查询返回自定义的类
String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
Customer customer = (Customer)getJdbcTemplate().queryForObject(
sql, new Object[] { custId },
new BeanPropertyRowMapper(Customer.class));
String sql = "SELECT NAME FROM CUSTOMER WHERE CUST_ID = ?";
String name = (String)getJdbcTemplate().queryForObject(
sql, new Object[] { custId }, String.class);
return name;
//查询返回自定义的类
String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
Customer customer = (Customer)getJdbcTemplate().queryForObject(
sql, new Object[] { custId },
new BeanPropertyRowMapper(Customer.class));
return customer;
我们接着分析下BeanPropertyRowMapper
其实不用看源码都知道思路了:
new BeanPropertyRowMapper(User.class)
mapRow(ResultSet rs, int rowNumber)
new BeanPropertyRowMapper(User.class)
mapRow(ResultSet rs, int rowNumber)
遍历ResultSet,每一行对应一个User 对象
遍历每一行时,顺序遍历各列,取得该列的值通过反射调用对应的setter方法,赋值给到User 对象
注意到无论是取列名还是取列的值,都是通过index(1~columnCount)来取的
再看看BeanPropertyRowMapper的构造函数:
public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
T mappedObject = BeanUtils.instantiate(this.mappedClass);
BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<String>() : null);
for (int index = 1; index <= columnCount; index++) {
String column = JdbcUtils.lookupColumnName(rsmd, index);
PropertyDescriptor pd = this.mappedFields.get(column.replaceAll(" ", "").toLowerCase());
if (pd != null) {
try {
Object value = http://www.mamicode.com/getColumnValue(rs, index, pd);"Given ResultSet does not contain all fields " +
"necessary to populate object of class [" + this.mappedClass + "]: " + this.mappedProperties);
}
return mappedObject;
}
注意到mapRow方法里面,populatedProperties变量是用来检查是否所有的property都正确地实现了赋值 再看看BeanPropertyRowMapper的构造函数:
public BeanPropertyRowMapper(Class<T> mappedClass) {
initialize(mappedClass);
}
protected void initialize(Class<T> mappedClass) {
this.mappedClass = mappedClass;
/*保存field
注意到对于驼峰式命名的field:
例如,对于gameId, mappedFields 会同时保存"gameId"和"game_id"
因此在sql语句中,
select id as game_id, name from game和
select id as gameId, name from game的效果是一样的
*/
this.mappedFields = new HashMap<String, PropertyDescriptor>();
//保存property
this.mappedProperties = new HashSet<String>();
PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(mappedClass);
for (PropertyDescriptor pd : pds) {
if (pd.getWriteMethod() != null) {
this.mappedFields.put(pd.getName().toLowerCase(), pd);
String underscoredName = underscoreName(pd.getName());
if (!pd.getName().toLowerCase().equals(underscoredName)) {
this.mappedFields.put(underscoredName, pd);
}
this.mappedProperties.add(pd.getName());
}
}
}
//1. "game", return "game", unchanged
//2. "gameId", return "game_id"
private String underscoreName(String name) {
//......
initialize(mappedClass);
}
protected void initialize(Class<T> mappedClass) {
this.mappedClass = mappedClass;
/*保存field
注意到对于驼峰式命名的field:
例如,对于gameId, mappedFields 会同时保存"gameId"和"game_id"
因此在sql语句中,
select id as game_id, name from game和
select id as gameId, name from game的效果是一样的
*/
this.mappedFields = new HashMap<String, PropertyDescriptor>();
//保存property
this.mappedProperties = new HashSet<String>();
PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(mappedClass);
for (PropertyDescriptor pd : pds) {
if (pd.getWriteMethod() != null) {
this.mappedFields.put(pd.getName().toLowerCase(), pd);
String underscoredName = underscoreName(pd.getName());
if (!pd.getName().toLowerCase().equals(underscoredName)) {
this.mappedFields.put(underscoredName, pd);
}
this.mappedProperties.add(pd.getName());
}
}
}
//1. "game", return "game", unchanged
//2. "gameId", return "game_id"
private String underscoreName(String name) {
//......
}
二、查询返回对象或者list
思路:首先在Bean里面实现RowMapper和序列化接口(序列化可有可无)
接着在maprow方法中将rs取出来的值set到当前Bean对象里面
当然命名也要响应的变化下比如:UserPo,然后返回该对象最
后在new 的RowMapper接口中直接new 该Po就ok
【此方法适用于查询单个对象,或者多个对象组成的List或者Map】
/**
* 实现数据表与字段的映射
*
* @author andy
*
*/
public class UserInfo implements RowMapper<UserInfo>, Serializable {
/**
*
*/
private static final long serialVersionUID = -8823504831198719837L;
private Integer id;
private String uname;
private Integer unumber;
private Date uRegisterTime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname == null ? null : uname.trim();
}
public Integer getUnumber() {
return unumber;
}
public void setUnumber(Integer unumber) {
this.unumber = unumber;
}
public Date getuRegisterTime() {
return uRegisterTime;
}
public void setuRegisterTime(Date uRegisterTime) {
this.uRegisterTime = uRegisterTime;
}
@Override
public UserInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
UserInfo userInfo = new UserInfo();
userInfo.setId(rs.getInt("id"));
userInfo.setUname(rs.getString("uname"));
userInfo.setUnumber(rs.getInt("unumber"));
userInfo.setuRegisterTime(rs.getDate("uregister_time"));
return userInfo;
}
}
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Override
public UserInfo getById(Integer id) {
String sql = "SELECT * FROM user_info WHERE id = ?";
UserInfo userInfo = jdbcTemplate.queryForObject(sql, new UserInfo(),
new Object[] { id });
return userInfo;
}
@Override
public List<UserInfo> findAll() {
String sql = "SELECT * FROM user_info";
List<UserInfo> userInfos = jdbcTemplate.query(sql, new UserInfo());
return userInfos;
}
=======================================================================================
三、第三种方法
使用很少人使用的RowSet
SqlRowSet set = queryForRowSet(sql,对象参数数组);
if(set==null){
new AuthenticationException("null");
}
while(set.next){
遍历:
set.getString(此处参数类型为整形,表示你想要查出来的结果,按照顺序,依次是1、2、3.......索引从1开始)
}
注意该方法适用于:查询单个对象或者List,Map结果的情况
必须谨记若用他查出来一条数据或者一个对象时:
在判断条件的时候必须加set.next&&在跟其他条件,否则会报错"光标位置失效"
SpringJdbc之queryForXXX大全解读
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。