Spring-JDBCTemplate

The JdbcTemplate class is the central class in the JDBC core package. It simplifies the use of JDBC since it handles the creation and release of resources. This helps to avoid common errors such as forgetting to always close the connection.


How to Configure:

Step 1:

Configure the Data source in the xml as below.

<bean  id="DataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

                <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

                <property name="url" value="jdbc:mysql://dxx-3306/test"/>

                <property name="username" value="DurgaPrasad"/>

                <property name="password" value="******"/>

   </bean>

Step 2 :
Inject the  data source to the DAO  bean.Here the dao is DCTDao.


<bean id="DCTDAO" class="com.dct.DAO.DCTDao" >
          <property name="dataSource">
              <ref bean="DataSource" />
          </property>
         </bean> 

Step 3:
 Sample DAO class.

package com.dct.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

/**
 *
 * @author DurgaPrasad
 *
 * This bean would be injected to the DCTController and this class would take care of the DB interactions.
 * This in turn uses JDBCTemplate to avoid the boiler plate jdbc code.
 *
 */
public class DCTDao {

    String resultString = "";
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    /**
     *
     * @param team
     * @param activity
     * @return
     */
    public int getCount(String team, String activity, String date) {
        return this.jdbcTemplate.queryForInt("select count(*) from dct where team='" + team + "' and ACTIVITY ='" + activity + "' and DEFECT_LOGGED_DATE='" + date + "'");

    }

    /**
     *
     * @param team
     * @param activity
     * @return
     */
    public int getDefectCount(String team, String activity) {
        return this.jdbcTemplate.queryForInt("select DEFECT_COUNT from dct where team='" + team + "' and ACTIVITY ='" + activity + "'");
    }

    /**
     *
     * @param team
     * @param activity
     * @param dCount
     * @param date
     */
    public void createDCT(String team, String activity, int dCount, String date) {
        System.out.println("INSERT into dct values('" + team + "','" + activity + "'," + dCount + ", '" + date + "')");
        this.jdbcTemplate.update("INSERT into dct values('" + team + "','" + activity + "'," + dCount + ", '" + date + "')");
    }

    /**
     *
     * @param team
     * @param activity
     * @param dCount
     * @param date
     */
    public void updateDCT(String team, String activity, int dCount, String date) {
        this.jdbcTemplate.update("update dct set DEFECT_COUNT=" + dCount + " where Team='" + team + "' and DEFECT_LOGGED_DATE='" + date + "' and Activity='" + activity + "'");
    }

    public String getName() {
        return (String) this.jdbcTemplate.queryForObject("select name from DCT", String.class);
    }

    /**
     *
     * @param team
     * @param activity
     * @return
     */
    public String getDefectCountAndDate(String team, String activity, String date) {
        String result = (String) this.jdbcTemplate.query("select DEFECT_COUNT ,DEFECT_LOGGED_DATE from dct where team='" + team + "' and ACTIVITY ='" + activity + "'", new ResultSetExtractor() {
            public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                if (resultSet.next()) {
                    return resultSet.getInt(1) + "|" + resultSet.getString(2);
                }
                return null;
            }
        });
        return result;
    }

    /**
     *
     * @param team
     * @param activity
     * @return
     */
    public int getTotalDefectCount(String team, String activity) {
        return this.jdbcTemplate.queryForInt("select DEFECT_COUNT from dct where team='" + team + "' and ACTIVITY ='" + activity + "'");
    }

    public String getReport(String fromDate, String toDate) {
        System.out.println("select * from dct where  DEFECT_LOGGED_DATE >'" + fromDate + "'and   DEFECT_LOGGED_DATE<'" + toDate + "'");
        resultString = "";
        String result = (String) this.jdbcTemplate.query(" select TEAM,ACTIVITY,DEFECT_COUNT,DEFECT_LOGGED_DATE from dct where  DEFECT_LOGGED_DATE >='" + fromDate + "' and   DEFECT_LOGGED_DATE<='" + toDate + "'", new ResultSetExtractor() {
            public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                while (resultSet.next()) {
                    System.out.println(resultSet.getString(1) + "|" + resultSet.getString(2) + "|" + resultSet.getInt(3) + "|" + resultSet.getString(4));
                    resultString = resultString + resultSet.getString(1) + "|" + resultSet.getString(2) + "|" + resultSet.getInt(3) + "|" + resultSet.getString(4) + "^";

                }
                return resultString;
            }
        });
        return result;
    }
}












This is basic step to configure datasource and injecting the jdbcTemplate.The strring returnedh in some methods here are used with ajax and hence the delimiter stuff I've used,can be still made better based on the purpose.


cheers !! ;-)