40-Returning-Other-Datatypes-from-JdbcTemplate

Fetch data from database.

      public String getCircleName(int circleId){

            String sql = "SELECT name FROM circle where id =?";

            return jdbcTemplate.queryForObject(sql, new Object[]{circleId}, String.class);

      }

JdbcDemo.java

package org.yash.watertechsol;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.yash.watertechsol.dao.JdbcDaoImpl;

import org.yash.watertechsol.model.Circle;

public class JdbcDemo {

      public static void main(String[] args){        

            //Circle circle = new JdbcDaoImpl().getCircle(1);

            //System.out.println(circle.getName());

            ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");

            JdbcDaoImpl dao = context.getBean("jdbcDaoImpl", JdbcDaoImpl.class);         

            System.out.println(dao.getCircleName(1));

      }

}

JdbcDaoImpl.java

package org.yash.watertechsol.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Component;

import org.yash.watertechsol.model.Circle;

@Component

public class JdbcDaoImpl {

      @Autowired

      private DataSource dataSource;

      private JdbcTemplate jdbcTemplate;// = new JdbcTemplate();

      public Circle getCircle(int circleId){

            Connection conn =null;

            try{

                  /*

                  String driver = "com.mysql.jdbc.Driver";

                  Class.forName(driver).newInstance();

                  conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb","root","");

                  */

                  conn = dataSource.getConnection();

                  PreparedStatement ps = conn.prepareStatement("select * from circle where id=?");

                  ps.setInt(1, circleId);

                  Circle circle =null;

                  ResultSet rs = ps.executeQuery();

                  if(rs.next()){

                        circle = new Circle(circleId, rs.getString("name"));

                  }

                  rs.close();

                  ps.close();

                  return circle;

            }catch(Exception e){

                  throw new RuntimeException(e);

            }

           

            finally{

                  try {

                        conn.close();

                  } catch (Exception e2) {

                        // TODO: handle exception

                  }

            }

      }

      public int getCircleCount(){       

            String sql = "SELECT COUNT(*) FROM circle";

            //jdbcTemplate.setDataSource(dataSource);

            return jdbcTemplate.queryForInt(sql);

      }

     

      public String getCircleName(int circleId){

            String sql = "SELECT name FROM circle where id =?";

            return jdbcTemplate.queryForObject(sql, new Object[]{circleId}, String.class);

      }

     

      @Autowired

      public void setDataSource(DataSource dataSource) {

            this.jdbcTemplate = new JdbcTemplate(dataSource);

            //this.dataSource = dataSource;

      }

      public DataSource getDataSource() {

            return dataSource;

      }

     

      public JdbcTemplate getJdbcTemplate() {

            return jdbcTemplate;

      }

      public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

            this.jdbcTemplate = jdbcTemplate;

      }

}

Circle.java

package org.yash.watertechsol.model;

public class Circle {

      private int id;

      private String name;

     

      public Circle(int circleId, String name){

            setId(circleId);

            setName(name);

      }

     

      public int getId() {

            return id;

      }

      public void setId(int id) {

            this.id = id;

      }

      public String getName() {

            return name;

      }

      public void setName(String name) {

            this.name = name;

      }

}

spring.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"

       xmlns:context="http://www.springframework.org/schema/context"

       xsi:schemaLocation="http://www.springframework.org/schema/beans

           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd

           http://www.springframework.org/schema/context

           http://www.springframework.org/schema/context/spring-context-3.0.xsd">

     

      <context:annotation-config />

      <context:component-scan base-package="org.yash.watertechsol"></context:component-scan>

     

      <!-- <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> -->

      <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> <!-- this we will use for connection pooling -->

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

            <property name="url" value="jdbc:mysql://localhost:3306/testdb"></property>

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

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

            <property name="initialSize" value="2"></property>

            <property name="maxActive" value="5"></property>

      </bean>

</beans>