Spring-jdbctemplate Execute Stored Procedures

spring-jdbctemplate Execute stored procedures

1 Create JdbcTemplate First

Package com.test;

Import Org.springframework.jdbc.core.JdbcTemplate;
Import Com.mchange.v2.c3p0.ComboPooledDataSource;

public class Configc3po {
Generating JdbcTemplate objects
private static jdbctemplate jdbc;
/***
* Return JdbcTemplate Object
* @return
*/
public static JdbcTemplate Getjdbc () {
if (jdbc==null) {
Generate C3PO Connection Pool
Combopooleddatasource data = new Combopooleddatasource ();
Set maximum number of connections
Data.setmaxpoolsize (5);
Set minimum number of connections
Data.setminpoolsize (2);
try {
Set driver
Data.setdriverclass ("Com.microsoft.jdbc.sqlserver.SQLServerDriver");
Setting up connection Users
Data.setuser ("sa");
Set Connection password '
Data.setpassword ("");
Set connection string
Data.setjdbcurl ("Jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=pubs");
catch (Exception e) {
Todo:handle exception
}
Generating JdbcTemplate objects
JDBC = new JdbcTemplate (data);
}
return JDBC;
}
}

2 defines a class implementation Connectioncallback interface

Package com.test;

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.ArrayList;
Import Java.util.HashMap;
Import java.util.List;
Import Java.util.Map;

Import org.springframework.dao.DataAccessException;
Import Org.springframework.jdbc.core.ConnectionCallback;

Import com.hygj.show.share.CoreAccess;

public class Dispose implements Connectioncallback {
The stored procedure to execute
Private String call;
constructor receives the stored procedure to be executed
Public Dispose (String call) {
This.call = call;
}


public static void Main (string[] args) {
Coreaccess core = Createconn.getcore ();
List List = Core.queryforlist ("SELECT * from Jobs", bean.class);
Object obj = Core.getjdbc (). Execute (new Connectioncallback () {

Public Object doinconnection (Connection arg0) throws SQLException, DataAccessException {
CallableStatement call = Arg0.preparecall ("{Call Up_call}");
System.out.println (arg0);
ResultSet rs = Call.executequery ();
System.out.println (RS);
while (Rs.next ()) {
System.out.println (Rs.getmetadata (). getColumnName (1));
System.out.println (rs.getstring (1));
}
return null;
}

});
System.out.println (obj);
}
/**
* The search to data is encapsulated as a ArrayList
* Stored in the column name: a collection of values
*/
Public Object doinconnection (Connection arg0) throws SQLException, DataAccessException {
Build execution stored Procedure class
CallableStatement able = arg0.preparecall (call);
Executing stored procedures
ResultSet rs = Able.executequery ();
Define container Storage data
List List = new ArrayList ();
Traverse all data
while (Rs.next ()) {
Defines a collection of map storage names and values
Map map = new HashMap ();
Store all the column names and values in the map
for (int i = 1; I<=rs.getmetadata (). getColumnCount (); i++) {
Map.put (Rs.getmetadata (). getColumnName (i), rs.getobject (i));
}
List.add (map);
}
Returns all data that is being traced
return list;
}
}

3 Transfer Stored procedures

Package com.test;

Import java.util.List;

public class Display {
public static void Main (string[] args) {
List List = (list) Configc3po.getjdbc (). Execute (New Dispose ("{Call Up_call}"));
for (int i = 0; I<list.size (); i++) {
System.out.println (List.get (i));
}
}
}

Summary
through the Execute method in JdbcTemplate, which receives a Connectioncallback interface, implemented at the Connectioncallback interface, and doinconnection in this interface ( Connection a) method to execute the corresponding stored procedure