存储过程:
1.取一个返回的参数,比如是否执行成功:“success”
2.取查询返回值
查询:
1.
2.
3.
修改:
批量:
1.
2.
1.取一个返回的参数,比如是否执行成功:“success”
public class TestDaoImpl extends NamedParameterJdbcDaoSupport{
private SimpleJdbcCall getSingleParameterSjc;
@Override
protected void initDao() throws Exception {
super.initDao();
this.getSingleParameterSjc=
new SimpleJdbcCall(this.getDataSource())
.withProcedureName("sp_getSingleParameter");
}
public Boolean getSjcSingleParameter(String sId) {
Map<String,Object> param = new HashMap<String, Object>();
param.put("sId", sId);
return (Boolean)this.getSingleParameterSjc.execute(param).get("success");
}
}
private final static RowMapper<Perform> PERFORM_MAPPER = BeanPropertyRowMapper.newInstance(Perform.class);
private final static RowMapper<Integer> SINGLE_INTEGER_MAPPER = new SingleColumnRowMapper<Integer>(Integer.class);
2.取查询返回值
private final static String
LIST_RESULT = "ListResult",
ROWCOUNT_RESULT = "totalCount";
private SimpleJdbcCall findListResultSjc;
@Override
protected void initDao() throws Exception {
super.initDao();
this.findRestaurantsSjc = new SimpleJdbcCall(this.getDataSource())
.withProcedureName("sp_function_find_restaurants")
.declareParameters(
new SqlReturnResultSet(ROWCOUNT_RESULT, SINGLE_INTEGER_MAPPER),
new SqlReturnResultSet(RESTAURANT_RESULT, PERFORM_MAPPER));
}
public Map<String,Object> findListResultSjc(Parameter para) {
Map<String, Object> data = this.findListResultSjc.
execute(new BeanPropertySqlParameterSource(para));
List<Perform> list = (List<Perform>) data.get(RESTAURANT_RESULT);
List<Integer> total = ((List<Integer>) data.get(ROWCOUNT_RESULT));
Integer totalCount = null == total
|| total.size() == 0? 0 : total.get(0);
return data;
}
查询:
1.
private final static String
SQL_SELECT_PERFORM_BY_ID = "SELECT id,name from perform where id = ? ";
public Perform findEvalPerformById(String id) {
return this.getJdbcTemplate().
queryForObject(SQL_SELECT_PERFORM_BY_ID, PERFORM_MAPPER,id);
2.
private final static String SQL_FIND_PRIORITIZED_BY_ID =
"SELECT prioritized FROM perform WHERE id = :Id";
public Boolean findPrioritizedByPerformId(String Id) {
Map<String,Object> params = new HashMap<String, Object>();
params.put("Id", Id);
return this.getNamedParameterJdbcTemplate()
.queryForObject(SQL_FIND_PRIORITIZED_BY_PERFORMID,
params, SINGLE_BOOLEAN_MAPPER);
}
3.
private final static String SQL_FIND_PERFORM=
"SELECT * FROM perform WHERE id = :Id and type = :type ";
public List<Perform> findPerform(String Id, String type) {
Map<String,Object> params = new HashMap<String, Object>();
params.put("type", type);
params.put("Id", Id);
return this.getNamedParameterJdbcTemplate().
query(SQL_FIND_PERFORM, params, PERFORM_MAPPER );
}
修改:
private final static String SQL_UPDATE_PERFORM=
"update perform set name = :name ,type = :type WHERE id = :Id";
public int update(Perform perform) {
return this.getNamedParameterJdbcTemplate()
.update(SQL_UPDATE_PERFORM,
new BeanPropertySqlParameterSource(perform));
}
批量:
1.
public void insertBatchNamedParameter2(final List<Customer> customers){
SqlParameterSource[] params =
SqlParameterSourceUtils.createBatch(customers.toArray());
getSimpleJdbcTemplate().batchUpdate(
"INSERT INTO CUSTOMER (CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)",
params);
}
2.
private final static String SQL_UPDATEPRIORITIZE =
" UPDATE perform"
+" SET priority = :priority WHERE id = :Id "
+" and s_id = :sId ";
public int updatePrioritize(List<Perform> performList) {
int[] updateCounts =
this.getNamedParameterJdbcTemplate().
batchUpdate(SQL_UPDATEPRIORITIZE,SqlParameterSourceUtils
.createBatch(performList.toArray()));
int result = 0;
for (int i = 0; i < updateCounts.length; i++) {
result += updateCounts[i];
}
return result;
}