42-Performing-Write-Operations-with-JdbcTemplate

public void insertCircle(Circle circle){

            String sql = "INSERT into circle (name) values(?)";

            jdbcTemplate.update(sql,new Object[]{circle.getName()});

      }

      public void createTriangleTable(){

            String sql = "CREATE TABLE Triangle(id integer, name varchar(50))";

            jdbcTemplate.execute(sql);

      }

JdbcDaoImpl.java

package org.yash.watertechsol.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import javax.sql.DataSource;

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

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

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

                                                }

                                }

                }

               

                @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;

                }

                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);

                }

               

                public Circle getCircleForId(int circleId){

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

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

                }

                private static final class CircleMapper implements RowMapper<Circle>{

                                @Override

                                public Circle mapRow(ResultSet resultSet, int rowNum) throws SQLException {

                                                Circle circle = new Circle();

                                                circle.setId(resultSet.getInt("id"));

                                                circle.setName(resultSet.getString("name"));

                                                return circle;

                                }

                               

                }

               

                public List<Circle> getAllCircle(){

                                String sql="SELECT * from circle";

                                return jdbcTemplate.query(sql, new CircleMapper());

                }

               

                public void insertCircle(Circle circle){

                                String sql = "INSERT into circle (name) values(?)";

                                jdbcTemplate.update(sql,new Object[]{circle.getName()});

                }

                public void createTriangleTable(){

                                String sql = "CREATE TABLE Triangle(id integer, name varchar(50))";

                                jdbcTemplate.execute(sql);

                }

}

JdbcDemo.java

package org.yash.watertechsol;

import java.util.List;

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){        

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

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

            //dao.insertCircle(new Circle(0, "Third Circle"));

            dao.createTriangleTable();

      }

}

Circle.java

package org.yash.watertechsol.model;

public class Circle {

      private int id;

      private String name;

     

      public Circle(){}

     

      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>