Spring is a big framework which has many ways of handling data. One of the most popular way is of working with relational databases is to use JdbcTemplate class by Spring framework. JdbcTemplate is the main Spring JDBC framework class that helps manage data and exceptions.
The objects created of the JdbcTemplate class are thread-safe. That means that multiple users can try accessing these objects without data corruption.

Let us start with the database table Accounts, which we created in Oracle with the statement below.

CREATE TABLE Accounts
(email varchar(60), Password varchar(60), enabled varchar(60), lastUpdate varchar(60) );

In Spring we configure the DataSource in the XML below and another bean as Data Access Object (DAO) for the Accounts table. A collection of bean descriptions is called bean factory.




   class="org.springframework.jdbc.datasource.DriverManagerDataSource">

   

   

   

   

 

 

     

 





We use traditional naming conventions with the bean accountsDAO.

There is a reference to the implementation class AccountsDAOImpl.

Code for Java interface AccountsDAO implementation class AccountsDAOImpl will be provided below.

In the XML above we configured Spring framework to recognize and connect to the DataSource.



Then we create the Accounts class to mimic the Accounts table. 

Keep in mind that the examples below require Spring libraries to compile. These examples help understanding Spring concepts, but not expected to be entered into Eclipse at this point.



package its.day11.db.spring;

public class Accounts {

   // data

   private String email;

   private String password;

   private String enabled;

   private String lastUpdate;

   // methods

   // provide get and set methods for each variable

   // usually this is done with Eclipse � right mouse click on the Eclipse source page � SOURCE � Generate Getters and Setters

}



The next step is to provide object-relational mapping to our tables.

This is done with custom implementation of the RowMapper interface of Spring.

In the example below we implement RowMapper and map the Accounts table to the Accounts class.



package its.day11.db.spring;



import java.sql.ResultSet;

import java.sql.SQLException;

 

import org.springframework.jdbc.core.RowMapper;

 

public class AccountsRowMapper implements RowMapper

{

	public Accounts mapRow(ResultSet rs, int rowNum) throws SQLException {

		Accounts account = new Accounts();

		account.setEmail(rs.getString("email"));

		account.setPassword(rs.getString("Password"));

		account.setEnabled(rs.getString("enabled"));

		account.setLastUpdated(rs.getString("lastUpdated"));

		return account;

	}

 }



Now let us plan and implement Data Access Object for Accounts.

We will capture our plan in the interface we will provide.



We plan to have two methods to access the Accounts table: one to insert data and another to find an account by email. 

Let us name the interface AccountsDAO. This is a usual naming convention, call an interface as {tableName}DAO and call implementation as {tableName}DAOImpl.

The interface will include two method signatures.



package its.day11.db.spring;

/**

 * AccountsDAO interface - agreement on two methods that need implementations

 */

public interface AccountsDAO {

    // pass an account object as an argument to insert a record in the table

    public void insert(Accounts account);

    // pass an email to receive back an account object

    public Accounts findByEmail(String email);

}



Now it is time to implement the plan.



package its.day11.db.spring;

/**

 * AccountsDAOImpl class implements the AccountsDAO interface

 */

public class AccountsDAOImpl implements AccountDAO {

    private DataSource dataSource;

    private JdbcTemplate jdbcTemplate;



    /**

     * The insert() method takes an account object to insert into the table

     */

     public void insert(Accounts account){

	 String sql = "INSERT INTO ACCOUNTS" +

	   "VALUES (?, ?, ?, ?)"; // 4 arguments to be replaced with run-time values



         jdbcTemplate = new JdbcTemplate(dataSource);

	  

         jdbcTemplate.update(sql, new Object[] { 

           // 4 run-time variables to replace "?" in SQL

           account.getEmail(), account.getPassword(), account.getEnabled(), account.getLastUpdate() });

     }  



     /**

      * The findByEmail() method uses JdbcTemplate object and the mapping above.

      */

      public Accounts findByEmail(String email){

 

	  String sql = "SELECT * FROM Accounts WHERE email = ?";

 

	  Accounts account = (Accounts)getJdbcTemplate().queryForObject(

	    sql, new Object[] { email }, new AccountsRowMapper());

 

	  return account;

      }



}

Was it clear so far?

Note:
In the example above note an array of parameters, Object[] {...}. They are the values, which replace "?".
The parameters can be String, Long, Date and so on types, so a common name is Object[] (array of object-parameters, created on the fly) {... param values...}.

Spring framework does heavy work behind the scene while demonstrating its main principles, Inversion of Control and Dependency Injection. Spring uses descriptions of the objects and gives the framework control on routine functions of the code.

Similar to the case with Hibernate, we would need Spring data framework library and the skills of working with Java libraries and project management tools, such as Apache Maven�

But do not be upset that we are not jumping to coding right away. The next section is about Data Service framework by ITS. Here is the opportunity to touch databases with Java code and create your own utilities based on the concepts of Data Service framework.

Assignments:
1. Answer QnAs and follow recommendations to add at least one-two more QnAs based on the current materials and more reading about Spring JDBC Framework:
http://www.tutorialspoint.com/spring/spring_jdbc_framework.htm
2. Add one method, such as changePassword(String newPassword), to the AccountsDAO interface and AccountsDAOImpl implementation class and send the updated source to dean@ituniversity.us (no Eclipse typing is necessary)
3. Helpful links recommended by students:
https://spring.io/guides/gs/relational-data-access/

<beans xmlns="http://www.springframework.org/schema/beans"> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value=" oracle.jdbc.driver.OracleDriver "/> <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" /> <property name="username" value="its"/> <property name="password" value="its"/> </bean> <bean id="accountDAO" class="its.day11.db.spring.AccountsDAOImpl"> <property name="dataSource" ref="dataSource" /> </bean> </beans> We use traditional naming conventions with the bean accountsDAO. There is a reference to the implementation class AccountsDAOImpl. Code for Java interface AccountsDAO implementation class AccountsDAOImpl will be provided below. In the XML above we configured Spring framework to recognize and connect to the DataSource. Then we create the Accounts class to mimic the Accounts table. Keep in mind that the examples below require Spring libraries to compile. These examples help understanding Spring concepts, but not expected to be entered into Eclipse at this point. package its.day11.db.spring; public class Accounts { // data private String email; private String password; private String enabled; private String lastUpdate; // methods // provide get and set methods for each variable // usually this is done with Eclipse � right mouse click on the Eclipse source page � SOURCE � Generate Getters and Setters } The next step is to provide object-relational mapping to our tables. This is done with custom implementation of the RowMapper interface of Spring. In the example below we implement RowMapper and map the Accounts table to the Accounts class. package its.day11.db.spring; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; public class AccountsRowMapper implements RowMapper { public Accounts mapRow(ResultSet rs, int rowNum) throws SQLException { Accounts account = new Accounts(); account.setEmail(rs.getString("email")); account.setPassword(rs.getString("Password")); account.setEnabled(rs.getString("enabled")); account.setLastUpdated(rs.getString("lastUpdated")); return account; } } Now let us plan and implement Data Access Object for Accounts. We will capture our plan in the interface we will provide. We plan to have two methods to access the Accounts table: one to insert data and another to find an account by email. Let us name the interface AccountsDAO. This is a usual naming convention, call an interface as {tableName}DAO and call implementation as {tableName}DAOImpl. The interface will include two method signatures. package its.day11.db.spring; /** * AccountsDAO interface - agreement on two methods that need implementations */ public interface AccountsDAO { // pass an account object as an argument to insert a record in the table public void insert(Accounts account); // pass an email to receive back an account object public Accounts findByEmail(String email); } Now it is time to implement the plan. package its.day11.db.spring; /** * AccountsDAOImpl class implements the AccountsDAO interface */ public class AccountsDAOImpl implements AccountDAO { private DataSource dataSource; private JdbcTemplate jdbcTemplate; /** * The insert() method takes an account object to insert into the table */ public void insert(Accounts account){ String sql = "INSERT INTO ACCOUNTS" + "VALUES (?, ?, ?, ?)"; // 4 arguments to be replaced with run-time values jdbcTemplate = new JdbcTemplate(dataSource); jdbcTemplate.update(sql, new Object[] { // 4 run-time variables to replace "?" in SQL account.getEmail(), account.getPassword(), account.getEnabled(), account.getLastUpdate() }); } /** * The findByEmail() method uses JdbcTemplate object and the mapping above. */ public Accounts findByEmail(String email){ String sql = "SELECT * FROM Accounts WHERE email = ?"; Accounts account = (Accounts)getJdbcTemplate().queryForObject( sql, new Object[] { email }, new AccountsRowMapper()); return account; } }