Using the JDBC API in Java Spring Framework programs to manipulate the database _java

While working with the database using normal old JDBC work, it becomes cumbersome to write unnecessary code to handle exceptions, open and close database connections, etc., but the spring JDBC framework requires all the low-level details from open connections, prepare and execute SQL statements, process exceptions, handle transactions, and finally close connections.

So all you have to do is define the connection parameters and specify the SQL statements to execute, and do the work necessary to get the data from the database each time you iterate.

Spring JDBC provides methods and corresponding different classes to interact with the database. I want to take the classic and most popular approach, using the Jdbctemplateclass framework. This is the central framework class for managing communications and exception handling for all databases.

JdbcTemplate class
the JdbcTemplate class executes SQL queries, updates statements and stored procedure calls, and iterates through the result set and fetch return parameter values. It also captures JDBC exceptions and converts them to generic, richer information, in addition to the hierarchies defined in the Org.springframework.dao package.

An instance of the JdbcTemplate class is a configured thread. So, you can configure an instance of a jdbctemplate and safely inject that share reference to multiple DAO.

When using the JdbcTemplate class, the usual practice is to configure a DataSource in the spring configuration file, and then the dependency is injected into the shared data source bean to the DAO class, JdbcTemplate or is created in the setter data source.

Configure a data source
Let's create the database test database table student. Suppose you use a MySQL database, and if you use a different database, you can change your DDL and SQL queries accordingly.

CREATE TABLE Student (
  ID  int not null auto_increment, NAME VARCHAR is not NULL, age
  INT is not null,
  PR Imary KEY (ID)
);

Now we need to provide a data source to the JdbcTemplate class, so it can be configured to get database access. You can configure a piece of code in the XML file for the data source, as shown in the following illustration:

<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 name=" username "value=" "
  root"/> <property name= "password"
  value= "Password"/>
</bean>

Data Access Objects (DAO)
DAO means that this is a data access object that is typically used for database interaction. DAO exists to provide read and write data to the database, and they should expose this functionality through the rest of the application by accessing their interfaces.

The support of the spring data Access object (DAO) makes it easy to do data access technology in a consistent way with JDBC,HIBERNATE,JPA and JDO.

Execute SQL statement
Let's look at how to perform crud (create, read, update, and delete) operations using the SQL and the tables in the JdbcTemplate object database.

Query an integer:

String SQL = "SELECT count (*) from Student";
int rowcount = Jdbctemplateobject.queryforint (SQL);

Query Long integer:

String SQL = "SELECT count (*) from Student";
Long rowcount = Jdbctemplateobject.queryforlong (SQL);

A simple query that uses a binding variable:

String SQL = "Select age from Student where id =?";
int age = Jdbctemplateobject.queryforint (SQL, New object[]{10});

In the query string:

String SQL = "SELECT name from Student where id =?";
String name = Jdbctemplateobject.queryforobject (SQL, New object[]{10}, String.class);

Query and return an object:

String SQL = "SELECT * from Student where id =?";
Student Student = Jdbctemplateobject.queryforobject (SQL, 
         new object[]{10}, New Studentmapper ());

public class Studentmapper implements rowmapper<student> {public
  Student Maprow (ResultSet rs, int rownum) thro WS SQLException {
   Student Student = new Student ();
   Student.setid (Rs.getint ("id"));
   Student.setname (rs.getstring ("name"));
   Student.setage (Rs.getint ("Age"));
   return student
  }
}

Query and return multiple objects:

String SQL = "SELECT * from Student";
list<student> students = Jdbctemplateobject.query (SQL,
             new Studentmapper ());

public class Studentmapper implements rowmapper<student> {public
  Student Maprow (ResultSet rs, int rownum) thro WS SQLException {
   Student Student = new Student ();
   Student.setid (Rs.getint ("id"));
   Student.setname (rs.getstring ("name"));
   Student.setage (Rs.getint ("Age"));
   return student
  }
}

Insert Row to table:

String SQL = "INSERT into Student (name, age) VALUES (?,?)";
Jdbctemplateobject.update (SQL, New object[]{"Zara", 11});

To update a row to a table:

String SQL = "Update Student set name =?" WHERE id =? ";
Jdbctemplateobject.update (SQL, New object[]{"Zara", 10});

To delete a row from a table:

String SQL = "Delete Student where id =?";
Jdbctemplateobject.update (SQL, New object[]{20});

Executing DDL statements
You can use the Execute (...) method to execute any SQL statement or DDL statement jdbctemplate. Here is an example of creating a table using the Create statement:

String SQL = ' CREATE TABLE Student (' +
  ' ID  INT not NULL auto_increment, ' +
  ' NAME VARCHAR ' not null, ' +
    "Age INT not NULL," +
  "PRIMARY KEY (ID);"

Jdbctemplateobject.execute (SQL);

SQL stored Procedures
Simplejdbccall classes can be used to invoke stored procedures with in and out parameters. You can use this approach while working with any of your favorite Apache derby,db2,mysql and Microsoft SQL Servers, Oracle and Sybase RDBMS.

Second, consider the following MySQL stored procedure which requires student ID and the return of the student's name and age corresponding to the out parameter. So let's use the MySQL command prompt to create the stored procedure in the test database:

DELIMITER $$

DROP PROCEDURE IF EXISTS ' test '. ' Getrecord ' $$
CREATE PROCEDURE ' test '. ' Getrecord ' (in
in_id Teger, out
out_name VARCHAR (M), out
out_age INTEGER)
BEGIN
  SELECT name, age into
  out_name, out_ Age from
  Student where id = in_id;
End $$

DELIMITER;

Now let's write a spring JDBC application that will perform simple creation and read operations on our student tables.
To create a spring application:
The following is the contents of the data Access object interface file Studentdao.java:

Package Com.yiibai;

Import java.util.List;
Import Javax.sql.DataSource;

Public interface Studentdao {/** * the ' is ' to ' used to ' 
  initialize
  * Database resources ie. conne Ction.
  * * Public
  void Setdatasource (DataSource ds);
  /** * This is the "method" to 
  "used" to create * a record in the
  Student table.
  *
  /public void Create (String name, Integer age);
  /** * This is the "method" to "used" to "a" record from the
  Student table corresponding
  * to a pas SED student ID.
  * * Public
  Student getstudent (Integer ID);
  /** * This is the 
  "method" to "used to
  " and "All" the records from the Student table.
  * * Public
  list<student> liststudents ();

}

The following are the contents of the Student.java file:

Package Com.yiibai;

public class Student {
  private Integer age;
  private String name;
  Private Integer ID;

  public void Setage (Integer age) {
   this.age = age;
  }
  Public Integer Getage () {return age
   ;
  }

  public void SetName (String name) {
   this.name = name;
  }
  Public String GetName () {return
   name;
  }

  public void SetId (Integer id) {
   this.id = ID;
  }
  Public Integer GetId () {return
   ID;
  }
}

The following are the contents of the Studentmapper.java file:

Package Com.yiibai;

Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import Org.springframework.jdbc.core.RowMapper;

public class Studentmapper implements rowmapper<student> {public
  Student Maprow (ResultSet rs, int rownum) thro WS SQLException {
   Student Student = new Student ();
   Student.setid (Rs.getint ("id"));
   Student.setname (rs.getstring ("name"));
   Student.setage (Rs.getint ("Age"));
   return student
  }
}

The following is the implementation class file Studentjdbctemplate.java definition DAO interface Studentdao:

Package Com.yiibai;

Import Java.util.Map;
Import Javax.sql.DataSource;
Import Org.springframework.jdbc.core.JdbcTemplate;
Import Org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
Import Org.springframework.jdbc.core.namedparam.SqlParameterSource;

Import Org.springframework.jdbc.core.simple.SimpleJdbcCall;
  public class Studentjdbctemplate implements Studentdao {private DataSource DataSource;

  Private Simplejdbccall Jdbccall;
   public void Setdatasource (DataSource DataSource) {this.datasource = DataSource;
            This.jdbccall = new Simplejdbccall (DataSource).
  Withprocedurename ("Getrecord");
   public void Create (String name, Integer age) {JdbcTemplate jdbctemplateobject = new JdbcTemplate (DataSource);

   String SQL = "INSERT into Student (name, age) VALUES (?,?)";
   Jdbctemplateobject.update (SQL, name, age);
   System.out.println ("Created record Name =" + name + ' age = ' + age);
  Return
  Public Student getstudent (Integer ID) { Sqlparametersource in = new Mapsqlparametersource ().
   AddValue ("in_id", id);

   Map<string, object> out = Jdbccall.execute (in);
   Student Student = new Student ();
   Student.setid (ID);
   Student.setname ((String) out.get ("Out_name"));

   Student.setage ((Integer) out.get ("Out_age"));
  return student;
   
   Public list<student> liststudents () {String SQL = ' select * from Student ';
   List <Student> students = jdbctemplateobject.query (SQL, New Studentmapper ());
  return students;

 }

}

About the above program a few words: You write the call to execute code, you need to create a sqlparametersource containing in parameters. It is important to match the name of the input value provided with the parameter name declared in the stored procedure. The Execute method receives the incoming arguments and returns a mapping that contains any columns specified in the stored procedure with the name type parameters. Now let's modify the main application file Mainapp.java, which is as follows:

Package Com.yiibai;
Import java.util.List;
Import Org.springframework.context.ApplicationContext;
Import Org.springframework.context.support.ClassPathXmlApplicationContext;

Import Com.yiibai.StudentJDBCTemplate; public class Mainapp {public static void main (string[] args) {ApplicationContext context = new Classpathxml

   ApplicationContext ("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 ());

 }
}

The following are profiles Beans.xml files:

 <?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 for data source--> <bean id= "DataSource" class= Ource. Drivermanagerdatasource "> <property name=" driverclassname "value=" Com.mysql.jdbc.Driver "/> <property na Me= "url" value= "jdbc:mysql://localhost:3306/test"/> <property name= "username" value= "root"/> <property n Ame= "password" value= "password"/> </bean> <!--Definition for studentjdbctemplate Bean--> Id= "Studentjdbctemplate" class= "com.yiibai.StudentJDBCTemplate" > <property name= "dataSource" ref= "DataSource" "/> </bean> </beans> 

After creating the source code and the bean configuration file, let's run the application. If all goes well, this will print the following information:

------Records creation--------
Created Record name = Zara age = Created the record
name = Nuha age = 2
Created Record Name = Ayan age =
------Listing multiple Records--------id:1, Name:zara, age:11
id:2, name : Nuha, Age:2
id:3, Name:ayan, age:15
----Listing record with ID = 2-----
id:2, Name:nuha, Age: 2