Spring - JDBC / DAO

public class StudentDAOImpl implements StudentDao{ ---implementation for all StudentDao interface methods----- ---> We can provide our methods inorder to improve code reusability along with DAO interface methods--- }

public class Student{ private String sid; private String sname; private String saddr; ----- ----- setXXX() and getXXX() ----- ----- }

public class StudentDaoFactory{ private static Student Dao dao; static{ dao = new StudentDaoImpl(); } public static StudentDao getStudentDao(){ return dao; } }

In Service layer StudentDao dao = StudentDao.getStudentDao();

5) We must not cache DAO references, because, Factory classes/ Factory methods are providing single instances of DAO to the service layer, if DAO is required in multiple modules then it is requyired to create more than one DAO reference.

6) In case of DAOs, It is suggestible to interact with Databases by using Connection Pooling mechanisms, not by using DriverManager approach. 7) DAO is not threadsafe, we must not use DAOs in multi threadded environment.

8) In DAOs we can access close() method inorder to close the resources like connections,.... , so here, before calling close() method we must ensure that whether the resources are going to be released or not with our close() method call.

9) We have make sure that all the objects which are used by DAOs are following Java bean conventions or not.

a)addform.html b)searchform.html c)deleteform.html d)existed.html e)notexisted.html f)success.html g)failure.html h)layout.html i)header.html j)menu.html k)welcome.html l)footer.html

• jsps a)display.jsp
• Servlets a)ControllerServlet
• Services:

a)StudentService

• DAOs a)StudentDao

6)DTOs a)StudentTo

7)Factories a)ConnectionFactory b)StudentServiceFactory c)StudentDaoFactory

a)ojdbc6.jar

a)DAO b)MVC c)DTO d)Factory

Example:

Durga Software Solutions, 202, Mitrivanam, Ameerpet, Hyd-38 </b> </font> </center> </body> </html>

Welcome To Durga Software Solutions </marquee> </b> </font> </center> </body> </html>

To provide support for DAOs kind of implementations in Spring Applications, Spring has provided a separate module called as ―Spring DAO‖. Spring DAO modules has provided a set of predefined classes and interfaces in order to provide DAO support in the form of ―org.springframework.dao‖ package.

Spring provides a convenient translation from technology-specific exceptions like SQLException , HibernateException,…… to its own exception class hierarchy with the DataAccessException as the root exception. Spring JDBC

In Enterprise Applications, to prepare Data Access Layer or DAO layer Spring has provided Modules in the form of JDBC and ORM . IN ORM we may use no of ORM implementation tools like Hibernate, JPA, Ibatis,....

Q)In Enterprise Applications, to prepare Data Access Layer we have already Plain JDBC tech. then what is the requirement to go for Spring JDBC Module?

• To prepare Data Access Layer in enterprise applications, if we use JDBC then we must take explicit responsibility to prepare the steps load and register the driver, Establisg Connection, creating Statement, executing SQl Queries and closing the resources like ResultSet, Statement and Connection.

If we use Spring JDBC module to prepare Data Access Layer, we must take explicit responsibility to write and execute SQL Queries only, not to take any responsibility to load and register driver, connection establishment, creating Statement and closing the resources.

• In case of Plain JDBC, almost all the exceptions are checked exceptions, we have to handle them explicitly by providing some java code.

In case of Spring JDBC module, all the internal checked exceptions are converted into Unchecked Exceptions which are defined by Spring DAO module , it is very simple to handle these unchecked Exceptions.

• In Plain JDBC, limited support is available for Transactions.

In Spring JDBC Module, very good support is available for transactions, we may use Transaction module also to provide transactions.

• In Plain JDBC, to hold the results we are able to use only ResultSet object, which is not implementing java.io.Serializable interface, which is not transferable in network.

In Spring JDBC, we are able to get results of SQL Queries in our required form like in the form of RowSet, Collections, ..... which are implementing java.io.Serializable interface and which are transferable in Network.

• In plain JDBC, we are able to get Connections either by using DriverManager or by using Datasource.

In Spring JDBC, we are able to get Connection internally by using Datasource only, that is through Connection Pooling only.

• In plain JDBC, to map records to Bean objects in the form of Collection Object we have to write java code explicitly, no predefined support is provided by JDBC tech.

In case of Spring JDBC, to map Database records to Bean objects in the form of Collection Spring JDBC has provided predefined support in the form of "RowMapper".

• In Plain JDBC, no callback interfaces support is available to create and execute the sql queries in PrfeparedStatement style.

In Spring JDBC, callback interfaces support is available to create and execute sql queries in PreparedStatement style.

To prepare Data Access Layer in enterprise applications, Spring JDBC module has provided the complete predefined library in the from of the following classes and interfaces in "org.springframework.jdbc" and its sub packages.

JdbcTemplate NamedParameterJdbcTemplate SimpleJdbcTemplate SimpleJdbcInsert and SimpleJdbcCall SQL Mapping through SQLUpdate and SQLInsert

In Spring Applications, if we want to JdbcTemplate[Jdbc Module] then we have to use the following steps.

1)Create DAO interface with the required methods. 2)Create DAO implementation class with implementation for DAO interface methods. 3)In Configuration File provide configuration for DataSource class , JdbcTemplate class and DAO implementation class. 4)Prepare Test Application to access Dao methods.

IN Spring configuration file we have to configure DataSource with the following properties.

driverClassName url username password In Spring applications, to configure DataSource Spring has provided a seperate a predefined Datasource class in the form of "org.springframework.jdbc.datasource.DriverManagerDataSource", it is not suggestible for production mode, it is suggestible for testing mode of our applications , In spring applications, it is always suggestible to use third party Connection Pooling mechanisms like dbcp, C3P0, Proxool,..

JdbcTemplate class is providing basic environment to interact with Database like Loading Driver class, Getting Connection between Java application and DB, Creating Statement , PreparedStatement and CallableStatement and closing the connection with the help of the provided Datasource and JdbcTemplate class has provided the following methods to execute SQL Queries. 1)For Non Select sql queries and DML SQL queries

public int update(String query) 2)For DDL Sql Queries

public void execute(String query) 3)For Select sql queries

public int queryForInt(String query) public int queryForLong(String query) public String queryForString(String query) public Object queryForObject(String query) public List query(String query) public List queryForList(String query) public Map queryForMap(String query) public RowSet queryForRowSet(String query)

While performing retrival operations to convert data from ResultSet object[records] to Bean objects Spring Framework has provided a predefined interface in the form of "org.springframework.jdbc.core.RowMapper" which contains the following method .

public Object mapRow(ResultSet rs, int rowCount)

Example

In Spring JDBC Applications, we will use positional parameters[?] also in sql queries which we are providing along with JdbcTemplate class provided query execution methods.

If we provide positional parameters in sql queries then JdbcTemplate class will use "PreparedStatement" internally to execute sql query instead of Statement.

To provide values to the Positional parameters in SQL Queries we have to use Object[] with values as parametyer to all JdbcTemplate class provided query execution methods.

public int update(String query, Object[] param_Values) public int queryForInt(String query, Object[] param_Values) public long queryForLong(String query, Object[] param_Values) public Object queryForObject(String query, Object[] param_Values, RowMapper rm) ----- ----- -----

Ex:

String query = "insert into student values(?,?,?)"; int rowCount = jdbcTemplate.update(query, new Object[]{"S-111", "AAA", "Hyd"});

Example:

NamedParameterJdbcTemplate class is same as JdbcTemplate class , but, NamedParameterJdbcTemplate class is able to define and run sql queries with Named Parameters instead of positional parameters.

EX:

String query = "insert into student values(:sid, :sname, :saddr)";

Where :sid, :sname, :saddr are named parameters for which we have to provide values.

In case of NamedParameterJdbcTemplate , we are able to provide values to the named parameters in the following two approaches.

• By Using Map directly.
• By using SqlParameterSource interface.

• By Using Map directly.

String query = "insert into student values(:sid, :sname, :saddr)"; Map map = new HashMap(); map.put("sid", "S-111"); map.put("sname", "AAA"); map.put("saddr", "Hyd"); namedParameterJdbcTemplate.update(query, map);

• By using SqlParameterSource interface.

To provide values to the Named parameters Spring has provided the following two implementation classes for SqlParameterSoure interface.

a)MapSqlParameterSource b)BeanPropertySqlParameterSource

To provide values to the named parameters if we want to use MapSqlParameterSource then first we have to create object for MapSqlParameterSource and we have to use the following method to add values to the named parameters.

public MapSqlParameterSource addValue(String name, Object val)

EX:

String query = "insert into student values(:sid, :sname, :saddr)"; SqlParameterSource param_Source = new MapSqlParameterSource("sid", "S-111"); param_Source = param_Source.addValue("sname", "AAA"); param_Source = param_Source.addValue("saddr", "Hyd"); namedParameterJdbcTemplate.update(query, param_Source);

To provide values to the named parameters if we want to use BeanPropertySqlParameterSource then first we have to create bean object with data then we have to create Object for BeanPropertySqlParameterSource with the generated Bean reference then provide BeanPropertySqlParameterSource object to query methods.

EX:

String query = "insert into student values(:sid, :sname, :saddr)"; Student std = new Student(); std.setSid("S-111"); std.setSname("AAA"); std.setSaddr("Hyd"); SqlParameterSource param_Source = new BeanPropertySqlParameterSource(std ); namedParameterJdbcTemplate.update(query, param_Source);

Note: JdbcTemplate is allowing DataSource object injection through setter method, but, NamedParameterJdbcTemplate class is allowing DataSource object injection through Constructor Dependency Injection.

Example

In Spring JDBC module, the main intention of SimpleJdbcTemplate class is to provide support for JDK5.0 version feratures like Auto Boxing, Auto Unboxing, Var-Arg methods,.....

SimpleJdbcTemplate class was provided in Spring2.5 version only and it was deprecated in the later versions Spring3.x and Spring4.x , in Spring5.x version SimpleJdbcTemplate class was removed.

If we want to use SimpleJdbcTemplate class we have to use Spring2.5 version jar files in Spring applications.

To execute SQL queries , SimpleJdbcTemplate class has provided the following methods.

public Object execute(String sqlQuery) Note: To use this method we have to get JdbcOperations class by using getJdbcOperations() method.

public int update(String query, Object ... params) public Object queryForInt(String query, Object ... params) public Object queryForLong(String query, Object ... params) public Object query(String query, Object ... params) public Object queryForObject(String query,Object ... params) ---- ---- Note: In case of SimpleJdbcTemplate class, to perform retrival operations, we have to use "ParameterizedRowMapper" inplace of RowMapper interface.

Example:

In Spring JDBC , we have to prepare DAO implementation classes with XXXTemplate property and the corresponding setXXX() method inorder to inject XXXTemplate class.

In Spring JDBC applications, if we want to get XXXTemplate classes with out declaring Template properties and corresponding setXXX() methods we have to use DAO Support classes provided Spring JDBC module.

There are three types of DAOSupport classes inorder to get Template object in DAO classes.

• JdbcDaoSupport
• NamedParameterJdbcDaoSupport
• SimpleJdbcDaoSupport

Where JdbcDaoSupport class will provide JdbcTemplate reference in DAO classes by using the following method.

public JdbcTemplate getJdbcTemplate()

Where NamesParameterJdbcDaoSupport class will provide NamedParameterJdbcTempate reference in DAO classes by using the following method.

public NamedParameterJdbcTemplate getNamedparameterJdbctemplate() Where SimpleJdbcDaoSupport class is able to provide SimpleJdbctemplate reference in Dao class by using the following method.

public SimpleJdbcTemplate getSimpleJdbcTemplate()

EX:

public class EmployeeDaoImpl extends JdbcDaoSupport implements EmployeeDao{

public String insert(int eno, String ename, float esal, String eaddr){ getJdbcTemplate().update("insert into emp1 values("+eno+",'"+ename+"',"+esal+",'"+eaddr+"')"); return "SUCCESS"; } ---- ---- }

To perform Batch Updations in Spring JDBC we have to use the following method from JdbcTemplate class.

public int[] batchUpdate(String sql_prepared_Statement, BatchPreparedStatementSetter setter)

Where BatchPreparedStatementSetter interface contains the following two methods public void setValues(PreparedStatement ps, int index) public int getBatchSize()

Where setValues() method will be executed for each and every record to set values to the positional parameters existed in PreparedStatement object by getting values from the provided List.

// TODO Auto-generated method stub return list.size(); } }); } catch (Exception e) { e.printStackTrace(); } return rowCounts; } }

jdbc.driverClassName = oracle.jdbc.OracleDriver jdbc.url = jdbc:oracle:thin:@localhost:1521:xe jdbc.username = system jdbc.password = durga

If we want to access stored procedures and functions ehich are available at database from Spring Jdbc application then we have to use "SimpleJdbcCall".

To use SimpleJdbcCall in Spring Jdbc applications we have to use the following steps. 1)Create DAO interface and its implementation class. 2)IN DAO implementation class, we have to declare DataSource and JdbcTemplate and its respective setter method . 3)In side setter method we have to create SimpleJdbcCall object.

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(); jdbcCall.withProcedureName("proc_Name"); 4)Configure DataSource and DAO implementation class in beans configuration file. 5)Access "execute" method by passing IN type parameters values in the form of "SQLParameterSource". public Map execute(Map m) pubhlic Map execute(SqlParameterSource paramSource) public Map execute(Object ... obj)

create or replace procedure getSalary(no IN number, sal OUT int) AS BEGIN select esal into sal from emp1 where eno = no; END getSalary; /