private JdbcTemplate template;
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
public void insertCustomerDetails(int id, String name, String address,
String phone) {
String query = "insert into customer (id,name,address,phone) values (?,?,?,?)";
template.update(query, id, name, address, phone);
System.out.println("Record inserted successfully");
}
public void callStoredProcedure() {
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(template)
.withProcedureName("customerDetails");
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("id", 1);
SqlParameterSource in = new MapSqlParameterSource(inParamMap);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
System.out.println(simpleJdbcCallResult);
}
Another code
public class EmployeeSP extends StoredProcedure{
private static final String SPROC_NAME = "usp_GetEmployeeName";
public EmployeeSP(DataSource datasource ){
super( datasource, SPROC_NAME );
declareParameter( new SqlParameter( "id", Types.INTEGER) );
//declaring sql in parameter to pass input
declareParameter( new SqlOutParameter( "name", Types.VARCHAR ) );
//declaring sql out parameter compile();
}
public Object execute(int emp_id){
Map<String,Object> results = super.execute(emp_id);
return results.get("name"); //reading output of stored procedure using out parameters
}
}
Another Code Snippet
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
public void insertCustomerDetails(int id, String name, String address,
String phone) {
String query = "insert into customer (id,name,address,phone) values (?,?,?,?)";
template.update(query, id, name, address, phone);
System.out.println("Record inserted successfully");
}
public void callStoredProcedure() {
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(template)
.withProcedureName("customerDetails");
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("id", 1);
SqlParameterSource in = new MapSqlParameterSource(inParamMap);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
System.out.println(simpleJdbcCallResult);
}
Another code
public class EmployeeSP extends StoredProcedure{
private static final String SPROC_NAME = "usp_GetEmployeeName";
public EmployeeSP(DataSource datasource ){
super( datasource, SPROC_NAME );
declareParameter( new SqlParameter( "id", Types.INTEGER) );
//declaring sql in parameter to pass input
declareParameter( new SqlOutParameter( "name", Types.VARCHAR ) );
//declaring sql out parameter compile();
}
public Object execute(int emp_id){
Map<String,Object> results = super.execute(emp_id);
return results.get("name"); //reading output of stored procedure using out parameters
}
}
Another Code Snippet
List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>();
declaredParameters.add(new SqlOutParameter("id", Types.INTEGER));
declaredParameters.add(new SqlParameter("name", Types.VARCHAR));
declaredParameters.add(new SqlParameter("date", Types.DATE));
this.jdbcTemplate.call(new CallableStatementCreator() {
@Override
CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement stmnt = con.createCall("{mypkg.doSomething(?, ?, ?)}");
stmnt.registerOutParameter("id", Types.INTEGER);
stmnt.setString("name", "<name>");
stmnt.setDate("date", <date>);
return stmnt;
}
}, declaredParameters);
