Performing Write Operations with JdbcTemplate

We'll now implement some database write operations. We'll write a DAO method to update a value in the database, as well as use the execute() method to run a DDL SQL query.



package com.venkat;


import java.util.Iterator;
import java.util.List;


import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;


import com.venkat.JdbcDao.JdbcDaoImpl;
import com.venkat.model.Student;


public class JDBCDemo {


/**
* @param args
* @throws Exception 
*/
public static void main(String[] args)   {
// TODO Auto-generated method stub

ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
 JdbcDaoImpl dao=context.getBean("jdbcDaoImpl",JdbcDaoImpl.class);
//dao.createPersonTable();

 
 System.out.println(dao.getStudentCount());
 System.out.println(dao.getStudentName(121));
 System.out.println(dao.getStudentForId(121).getStudentrank());
dao.insertStudent(new Student(345, "Sandeep", 8));
 
List<Student> list=dao.getAllStudents();
System.out.println("Student Details...............");
for (Iterator iterator = list.iterator(); iterator.hasNext();) 
{
Student student = (Student) iterator.next();
System.out.print(student.getStudentid()+"\t");
System.out.print(student.getStudentname()+"\t");
System.out.println(student.getStudentrank());
}
}


}

*************************************************************************

package com.venkat.JdbcDao;

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 com.venkat.model.Student;

@Component

public class JdbcDaoImpl {

private DataSource datasource;

private JdbcTemplate jdbcTemplate;

public DataSource getDatasource() {

return datasource;

}

public JdbcTemplate getJdbcTemplate() {

return jdbcTemplate;

}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

@Autowired

public void setDatasource(DataSource datasource) {

this.jdbcTemplate = new JdbcTemplate(datasource);

}

public int getStudentCount()

{

String sql="select count(*) from STUDENT";

return jdbcTemplate.queryForInt(sql);

}

public String getStudentName(int studentid)

{

String sql="select studentname from STUDENT where studentid=?";

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

}

public Student getStudentForId(int studentid)

{

String sql="select * from STUDENT where studentid=?";

return jdbcTemplate.queryForObject(sql, new Object[]{studentid}, new StudentMapper());

}

public void insertStudent(Student student)

{

String sql="insert into STUDENT (studentid,studentname,studentrank) values(?,?,?) ";

jdbcTemplate.update(sql,new Object[]{student.getStudentid(),student.getStudentname(),student.getStudentrank()});

}

public List<Student> getAllStudents()

{

String sql="select * from STUDENT ";

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

}

public void createPersonTable()

{

String sql="create table Person (id Number(3),name varchar2(12))";

jdbcTemplate.execute(sql);

}

private static final class StudentMapper implements RowMapper<Student>

{

@Override

public Student mapRow(ResultSet resultset, int rowNum) throws SQLException {

Student s=new Student();

s.setStudentid(resultset.getInt("studentid"));

s.setStudentname(resultset.getString("studentname"));

s.setStudentrank(resultset.getInt("studentrank"));

return s;

}

}

}

************************************************************************

package com.venkat.model;

public class Student {

private int studentid;

private String studentname;

private int studentrank;

public int getStudentid() {

return studentid;

}

public void setStudentid(int studentid) {

this.studentid = studentid;

}

public String getStudentname() {

return studentname;

}

public void setStudentname(String studentname) {

this.studentname = studentname;

}

public int getStudentrank() {

return studentrank;

}

public void setStudentrank(int studentrank) {

this.studentrank = studentrank;

}

public Student(int studentid, String studentname, int studentrank) {

super();

this.studentid = studentid;

this.studentname = studentname;

this.studentrank = studentrank;

}

public Student() {

// TODO Auto-generated constructor stub

}

}

************************************************************

<?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:aop="http://www.springframework.org/schema/aop"

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

  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd

    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd

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

<context:annotation-config/>

<context:component-scan base-package="com.venkat"></context:component-scan>

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

<property name="driverClassName"

value="oracle.jdbc.driver.OracleDriver">

</property>

<property name="url"

value="jdbc:oracle:thin:@localhost:1521:xe">

</property>

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

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

</bean></beans>