Spring (13) SQL stored procedure

The Simplejdbccall class can be used to invoke a stored procedure that contains in and out parameters. You can use this approach when working with any RDBMS, like Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase.

Or the example of the spring-based JDBC Framework continues

(1) Create a corresponding stored procedure based on the test library

DELIMITER $ $DROP PROCEDURE IF EXISTS ' test '. ' Getrecord ' $ $CREATE PROCEDURE ' test '. ' Getrecord ' (in in_id integer,out ou T_name VARCHAR, outout_age  INTEGER) BEGIN   SELECT name, age into   out_name, out_age   = in_id; END $ $DELIMITER;

(2) Create an entity

 PackageCom.tutorialspoint; Public classStudent {PrivateInteger age; PrivateString name; PrivateInteger ID;  Public voidsetage (Integer age) { This. Age =Age ; }    PublicInteger getage () {returnAge ; }    Public voidsetName (String name) { This. Name =name; }    PublicString GetName () {returnname; }    Public voidsetId (Integer id) { This. ID =ID; }    PublicInteger getId () {returnID; }}

(3) Writing Studentdao

 PackageCom.tutorialspoint;Importjava.util.List;ImportJavax.sql.DataSource; Public InterfaceStudentdao {/*** This was the method to being used to initialize * database resources ie. connection. */    Public voidSetdatasource (DataSource DS); /*** This was the method to being used to create * A record in the Student table. */    Public voidCreate (String name, Integer age); /*** This was the method to being used to list down * A record from the Student table corresponding * to a passed    Student ID. */    PublicStudent getstudent (Integer ID); /*** This was the method to being used to list down * All the records from the Student table. */    PublicList<student>liststudents ();}

(4) Writing Studentmapper.java

 PackageCom.tutorialspoint;ImportJava.sql.ResultSet;Importjava.sql.SQLException;ImportOrg.springframework.jdbc.core.RowMapper; Public classStudentmapperImplementsRowmapper<student> {    PublicStudent Maprow (ResultSet RS,intRowNum)throwsSQLException {Student Student=NewStudent (); Student.setid (Rs.getint ("id")); Student.setname (Rs.getstring ("Name")); Student.setage (Rs.getint ("Age")); returnstudent; }}

(5) Writing studentjdbctemplate. java

 PackageCom.tutorialspoint;Importjava.util.List;ImportJava.util.Map;ImportJavax.sql.DataSource;Importorg.springframework.jdbc.core.JdbcTemplate;ImportOrg.springframework.jdbc.core.namedparam.MapSqlParameterSource;ImportOrg.springframework.jdbc.core.namedparam.SqlParameterSource;ImportOrg.springframework.jdbc.core.simple.SimpleJdbcCall; Public classStudentjdbctemplateImplementsStudentdao {PrivateDataSource DataSource; PrivateSimplejdbccall Jdbccall;  Public voidSetdatasource (DataSource DataSource) { This. DataSource =DataSource;  This. Jdbccall =NewSimplejdbccall (DataSource). Withprocedurename ("Getrecord"); }    Public voidCreate (String name, Integer age) {JdbcTemplate jdbctemplateobject=NewJdbcTemplate (DataSource); String SQL= "INSERT into Student (name, age) VALUES (?,?)";      Jdbctemplateobject.update (SQL, name, age); System.out.println ("Created Record name =" + name + "Age =" +Age ); return; }    PublicStudent getstudent (Integer id) {Sqlparametersource in=NewMapsqlparametersource (). AddValue ("IN_ID", id); Map<string, object> out =Jdbccall.execute (in); Student Student=NewStudent ();      Student.setid (ID); Student.setname (String) out.get ("Out_name")); Student.setage ((Integer) Out.get ("Out_age")); returnstudent; }    PublicList<student>liststudents () {JdbcTemplate jdbctemplateobject=NewJdbcTemplate (DataSource); String SQL= "SELECT * FROM Student"; List<Student> students =jdbctemplateobject.query (SQL,NewStudentmapper ()); returnstudents; }}

(6) write the corresponding Mainapp.java

 PackageCom.tutorialspoint;Importjava.util.List;ImportOrg.springframework.context.ApplicationContext;ImportOrg.springframework.context.support.ClassPathXmlApplicationContext;Importcom.tutorialspoint.StudentJDBCTemplate; Public classMainapp { Public Static voidMain (string[] args) {ApplicationContext context=NewClasspathxmlapplicationcontext ("Beans.xml"); Studentjdbctemplate studentjdbctemplate=(studentjdbctemplate) Context.getbean ("Studentjdbctemplate"); System.out.println ("------Records Creation--------" ); Studentjdbctemplate.create ("Zara", 11); Studentjdbctemplate.create ("Nuha", 2); Studentjdbctemplate.create ("Ayan", 15); System.out.println ("------Listing multiple Records--------" ); List<Student> students =studentjdbctemplate.liststudents ();  for(Student record:students) {System.out.print ("ID:" +Record.getid ()); System.out.print (", Name:" +record.getname ()); System.out.println (", Age:" +record.getage ()); } System.out.println ("----Listing Record with ID = 2-----" ); Student Student= Studentjdbctemplate.getstudent (2); System.out.print ("ID:" +Student.getid ()); System.out.print (", Name:" +student.getname ()); System.out.println (", Age:" +student.getage ()); }}

(7) Writing Beans.xml

<?xml version= "1.0" encoding= "UTF-8"? ><beans xmlns= "Http://www.springframework.org/schema/beans"Xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance"xsi:schemalocation= "Http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd "><!--initialization forData source--<bean id= "DataSource"class= "Org.springframework.jdbc.datasource.DriverManagerDataSource" > <property name= "driverclassname" value= " Com.mysql.jdbc.Driver "/> <property name=" url "value=" jdbc:mysql://localhost:3306/test "/> <property N Ame= "username" value= "root"/> <property name= "password" value= "1234"/> </bean> <!--Definition forStudentjdbctemplate Beans--<bean id= "Studentjdbctemplate"class= "Com.tutorialspoint.StudentJDBCTemplate" > <property name= "dataSource" ref= "DataSource"/> </bean& Gt;</beans>

(8) Run the Main method in Mainapp.java

Results:

Spring (13) SQL stored procedure