Practical 9.1

Write a program to insert, update and delete records from the given table

Student Interface iStudent.java

package com.hiraymca; import java.util.List;

import javax.sql.DataSource; public interface IStudent {

//setting data source

public void setDataSource(DataSource datasource);

//creating records- inserting record into table public void create(int rollno,String name, int age);

//reading specific records

public Student readStudent(int id);

//reading all the records from table public List<Student> listStudents();

//update record

public void update(int id,int rollno,String name, int age);

//delete record

public void delete(int id);

}

Student.java

package com.hiraymca; import java.util.List;

import javax.sql.DataSource;

public class Student {int id;

int rollno; String sname; int age;

public int getId() {

return id;

}

public void setId(int id) {this.id = id;

}

public int getRollno() {return rollno;

}

public void setRollno(int rollno) {this.rollno = rollno;

}

public String getSname() {return sname;

}

public void setSname(String sname) {this.sname = sname;

}

public int getAge() {return age;

}

public void setAge(int age) {this.age = age;

}

}

StudentJDBCTemplate.java

package com.hiraymca; import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class StudentJDBCTemplate implements IStudent {private DataSource dataSource;

private JdbcTemplate jdbcTemplateObject;

@Override

public void setDataSource(DataSource datasource) {

// TODO Auto-generated method stub this.dataSource=datasource; this.jdbcTemplateObject=new JdbcTemplate(datasource);

}

@Override

//inserting a new record into database

public void create(int rollno, String name, int age) {

// TODO Auto-generated method stub

String sql=“insert into student2(rollno,sname,age)values(?,?,?)”; jdbcTemplateObject.update(sql,rollno,name,age);

System.out.println(“Record inserted successfully”);

}

@Override

public Student readStudent(int id) {

// TODO Auto-generated method stub

String sql=“select * from student2 where id=?”;

Student student=(Student)jdbcTemplateObject.queryForObject(sql,new Object[]

{id},new StudentMapper());

return student;

}

@Override

public List<Student> listStudents() {

// TODO Auto-generated method stub String sql=“select * from student2”;

List<Student> students=jdbcTemplateObject.query(sql, new StudentMapper());

return students;

}

@Override

public void update(int id, int rollno, String sname, int age) {

// TODO Auto-generated method stub

String SQL = “update Student2 set rollno=?,sname=?,age = ? where id = ?”;

jdbcTemplateObject.update(SQL, rollno,sname,age,id); System.out.println(“Updated Record with ID = “ + id);

return;

}

@Override

public void delete(int id) {

// TODO Auto-generated method stub

String SQL = “delete from Student2 where id = ?”; jdbcTemplateObject.update(SQL, id); System.out.println(“Deleted Record with ID = “ + id);

}

}

StudentMapper.java

package com.hiraymca; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;

public class StudentMapper implements RowMapper<Student>

{

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

Student student = new Student(); student.setId(rs.getInt(“id”)); student.setRollno(rs.getInt(“rollno”)); student.setSname(rs.getString(“sname”)); student.setAge(rs.getInt(“age”));

return student;

}

}

Beans.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” xsi:schemaLocation=“http://www.springframework.org/schema/beans

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

<bean id=“dataSource”

class = “org.springframework.jdbc.datasource.DriverManagerDataSource”>

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

<property name = “url” value =

“jdbc:mysql://localhost:3306/studentdb”/>

<property name = “username” value = “root”/>

<property name = “password” value = ““/>

</bean>

<bean id=“studentJDBCTemplate” class=“com.hiraymca.StudentJDBCTemplate”>

<property name=‘dataSource’ ref=“dataSource”></property>

</bean>

</beans>

MainApp.java

package com.hiraymca; import java.util.List; import java.util.Scanner; import java.util.ArrayList;

import org.springframework.context.ApplicationContext; import

org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.dao.EmptyResultDataAccessException;

public class MainApp {

public static void main(String[] args) {

// TODO Auto-generated method stub ApplicationContext context=new

ClassPathXmlApplicationContext(“Beans.xml”); StudentJDBCTemplate studentJDBCTemplate;

studentJDBCTemplate=(StudentJDBCTemplate)context.getBean(“stude ntJDBCTemplate”);

int choice; int id;

int rollno; String sname; int age;

String ans=null;

Scanner sc=new Scanner(System.in);

do

{

System.out.println(“Select the choice”); System.out.println(“1.Insert record “); System.out.println(“2.Read record”); System.out.println(“3.List all the records”); System.out.println(“4.Delete the record”); System.out.println(“5.Exit”);

System.out.print(“Enter your choice(1..5):”);

choice=sc.nextInt();

switch(choice)

{

case 1:

//inserting a record

System.out.print(“Enter roll no”);

rollno=sc.nextInt(); System.out.println(“Enter name”); sname=sc.next(); System.out.println(“Enter age”); age=sc.nextInt();

studentJDBCTemplate.create(rollno, sname, age);

break;

case 2:

//reading a record

Student student;

System.out.println(“Enter record id”);

id=sc.nextInt();

try

{

student=studentJDBCTemplate.readStudent(id); System.out.println(“id=“+student.getId()); System.out.println(“Rollno=“+student.rollno); System.out.println(“Name=“+student.getSname()); System.out.println(“Age=“+student.getAge());

}catch(Exception ex)

{

}

case 3:

System.out.println(“Record not found”);

break;//listing all the records

List<Student>

students = studentJDBCTemplate.listStudents();

System.out.println(“id”+”\t”+”Rollno”+”\t”+”Name”+”\t\t”+”Age”);

for (Student record : students) {

System.out.println(record.getId()+”\t”+record.getRollno()+”\t”+record. getSname()+”\t\t”+record.getAge());

}

break;

case 4:

// delete the record System.out.println(“Enter record id”);

id=sc.nextInt();

try

{

studentJDBCTemplate.delete(id);

}

catch(Exception ex)

{

}

case 5:

}

System.out.println(“Record not found”); break;

System.exit(0);

break;

System.out.println(“Do you wish to continue(y/n)”); ans=sc.next();

}while(ans.equals(“Y”)||ans.equals(“y”));

}

}

Practical 9.2

Write a program to demonstrate PreparedStatement in Spring JdbcTemplate

IStudent.java

package com.hiraymca; java.util.List;

import javax.sql.DataSource; public interface IStudent {

//setting data source

public void setDataSource(DataSource datasource);

//creating records- inserting record into table public void create(int rollno,String name, int age);

//listing all records

public List<Student> listStudents();

}

Student.java

package com.hiraymca; import java.util.List;

import javax.sql.DataSource;

public class Student {int id;

int rollno; String sname; int age;

public Student(int rollno, String sname, int age) {

this.rollno = rollno; this.sname = sname; this.age = age;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public int getRollno() {

return rollno;

}

public void setRollno(int rollno) {this.rollno = rollno;

}

public String getSname() {

return sname;

}

public void setSname(String sname)

{this.sname = sname;

}

public int getAge() {

return age;

}

public void setAge(int age) {this.age = age;

}

}

StudentDAO.java

package com.hiraymca;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementCallback;

public class StudentDAO {

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

public Boolean saveStudent(final Student s)

{

String query=“insert into student2(rollno,sname,age)values(?,?,?)”;

return jdbcTemplate.execute(query,new PreparedStatementCallback<Boolean>() {

@Override

public Boolean doInPreparedStatement(PreparedStatement ps)

throws SQLException, DataAccessException

{

ps.setInt(1,s.getRollno()); ps.setString(2,s.getSname()); ps.setFloat(3,s.getAge());

return ps.execute();

}

});

}

}

MainApp.java

package com.hiraymca; import java.util.List;

import java.util.Scanner; import java.util.ArrayList;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.dao.EmptyResultDataAccessException;

public class MainApp {

private static int rollno; private static String sname; private static int age;

public static void main(String[] args) {

// TODO Auto-generated method stub

ApplicationContext context=new ClassPathXmlApplicationContext(“Beans.xml”); Scanner sc=new Scanner(System.in);

StudentDAO stud=(StudentDAO)context.getBean(“studentDAO”); System.out.println(“Enter rollno”);

rollno=sc.nextInt();

System.out.println(“Enter student name”);

sname=sc.next(); System.out.println(“Enter age”); age=sc.nextInt();

stud.saveStudent(new Student(rollno,sname,age)); System.out.println(“Record inserted successfully”);

}

}

Beans.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” xsi:schemaLocation=“http://www.springframework.org/schema/beans

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

<bean id=“ds”

class =

“org.springframework.jdbc.datasource.DriverManagerDataSource”>

<property name = “driverClassName” value =

“com.mysql.jdbc.Driver”/>

<property name = “url” value =

“jdbc:mysql://localhost:3306/studentdb”/>

<property name = “username” value = “root”/>

<property name = “password” value = ““/>

</bean>

<bean id=“jdbcTemplate” class=“org.springframework.jdbc.core.JdbcTemplate”>

<property name=“dataSource” ref=“ds”></property>

</bean>

<bean id=“studentDAO” class=“com.hiraymca.StudentDAO”>

<property name=‘jdbcTemplate’ ref=“jdbcTemplate”></property>

</bean>

</beans>

Output

Enter rollno 4

Enter student name ddd Enter age 25

Record inserted successfully

Practical 9.3

Write a program in Spring JDBC to demonstrate ResultSetExtractor Interface

iStudent.java [Student interface]

package com.hiraymca;

import java.util.List;

import javax.sql.DataSource;

public interface IStudent {

//setting data source

public void setDataSource(DataSource datasource);

//creating records- inserting record into table public void create(int rollno,String name, int age);

//listing all records

public List<Student> listStudents();

}

Student.java

package com.hiraymca;

import java.util.List;

import javax.sql.DataSource;

public class Student {

int id;

int rollno; String sname; int age;

public Student()

{

}

public Student(int rollno, String sname, int age) {

this.rollno = rollno; this.sname = sname; this.age = age;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public int getRollno() {

return rollno;

}

public void setRollno(int rollno) {this.rollno = rollno;

}

public String getSname() {

return sname;

}

public void setSname(String sname) {this.sname = sname;

}

public int getAge() {

return age;

}

public void setAge(int age) {this.age = age;

}

}

StudentDAO.java

package com.hiraymca;

import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList;

import java.util.List;

import java.sql.ResultSet;

import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementCallback;

import org.springframework.jdbc.core.ResultSetExtractor;

public class StudentDAO {

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

public Boolean saveStudent(final Student s)

{

String query=“insert into student2(rollno,sname,age)values(?,?,?)”;

return jdbcTemplate.execute(query,new PreparedStatementCallback<Boolean>() {

@Override

public Boolean doInPreparedStatement(PreparedStatement

ps)

throws SQLException, DataAccessException {

ps.setInt(1,s.getRollno()); ps.setString(2,s.getSname()); ps.setFloat(3,s.getAge());

return ps.execute();

}

});

}

public List<Student> getAllStudents(){

return jdbcTemplate.query(“select * from student2”,

new ResultSetExtractor<List<Student>>() {

@Override

public List<Student> extractData(ResultSet rs)

throws SQLException, DataAccessException {

// TODO Auto-generated method stub List<Student>list=new ArrayList<Student>(); while(rs.next())

{

Student s=new Student(); s.setId(rs.getInt(1));

s.setRollno(rs.getInt(2));

s.setSname(rs.getString(3));

s.setAge(rs.getInt(4));

list.add(s);

}

return list;

}

});

}

}

MainApp.java

package com.hiraymca; import java.util.List; import java.util.Scanner; import java.util.ArrayList; import java.util.Iterator;

import org.springframework.context.ApplicationContext; import

org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.dao.EmptyResultDataAccessException;

public class MainApp {

private static int rollno; private static String sname; private static int age; private static int choice; private static String ans;

public static void main(String[] args) {

// TODO Auto-generated method stub

ApplicationContext context=new ClassPathXmlApplicationContext(“Beans.xml”);

Scanner sc=new Scanner(System.in);

StudentDAO stud=(StudentDAO)context.getBean(“studentDAO”); do

{

System.out.println(“1.Insert record”); System.out.println(“2.List all record”); System.out.println(“3.Exit”); System.out.print(“Enter your choice(1..3)”); choice=sc.nextInt();

switch(choice)

{

case 1:

//inserting record System.out.print(“Enter rollno”); rollno=sc.nextInt(); System.out.print(“Enter student name”); sname=sc.next(); System.out.print(“Enter age”); age=sc.nextInt();

stud.saveStudent(new Student(rollno,sname,age)); System.out.println(“Record inserted successfully”); break;

case 2:

//listing record List<Student>slist=stud.getAllStudents(); System.out.println(“Rollno”+”\t”+”Name”+”\t”+”Age”); for (Student student : slist) {

System.out.println(student.getRollno()+”\t”+student.getSname()+”\ t”+student.getAge());

}

break; case 3:

//exiting from application System.exit(0);

}

System.out.print(“Do you wish to cotinue(y/n) “); ans=sc.next();

}while(ans.equals(“y”)||ans.equals(“Y”));

}

}

Beans.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” xsi:schemaLocation=“http://www.springframework.org/schema/beans

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

<bean id=“ds”

class =

“org.springframework.jdbc.datasource.DriverManagerDataSource”>

<property name = “driverClassName” value =

“com.mysql.jdbc.Driver”/>

<property name = “url” value =

“jdbc:mysql://localhost:3306/studentdb”/>

<property name = “username” value = “root”/>

<property name = “password” value = ““/>

</bean>

<bean id=“jdbcTemplate” class=“org.springframework.jdbc.core.JdbcTemplate”>

<property name=“dataSource” ref=“ds”></property>

</bean>

<bean id=“studentDAO” class=“com.hiraymca.StudentDAO”>

<property name=‘jdbcTemplate’ ref=“jdbcTemplate”></property>

</bean>

</beans>

Output

1.Insert record 2.List all record 3.Exit

Enter your choice(1..3)2 Rollno Name Age

Do you wish to cotinue(y/n)

Practical 9.4

Write a program in Spring JDBC to demonstrate ResultSetExtractor Interface

iStudent.java [Student interface]

package com.hiraymca; import java.util.List;

import javax.sql.DataSource; public interface IStudent {

//setting data source

public void setDataSource(DataSource datasource);

//creating records- inserting record into table public void create(int rollno,String name, int age);

//listing all records

public List<Student> listStudents();

}

Student.java

package com.hiraymca; import java.util.List;

import javax.sql.DataSource; public class Student {

int id;

int rollno;

String sname;

int age;

public Student()

{

}

public Student(int rollno, String sname, int age) {

this.rollno = rollno; this.sname = sname; this.age = age;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public int getRollno() {

return rollno;

}

public void setRollno(int rollno) {this.rollno = rollno;

}

public String getSname() {

return sname;

}

public void setSname(String sname) {this.sname = sname;

}

public int getAge() {

return age;

}

public void setAge(int age)

{

this.age = age;

}

}

StudentDAO.java

package com.hiraymca;

import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList;

import java.util.List; import java.sql.ResultSet;

import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementCallback; import org.springframework.jdbc.core.ResultSetExtractor;

public class StudentDAO {

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;

}

public Boolean saveStudent(final Student s)

{

String query=“insert into student2(rollno,sname,age)values(?,?,?)”;

return jdbcTemplate.execute(query,new PreparedStatementCallback<Boolean>() {

@Override

public Boolean doInPreparedStatement(PreparedStatement

ps)

throws SQLException, DataAccessException {

ps.setInt(1,s.getRollno()); ps.setString(2,s.getSname()); ps.setFloat(3,s.getAge());

return ps.execute();

}

});

}

public List<Student> listStudents() {

// TODO Auto-generated method stub String sql=“select * from student2”;

List<Student> students=jdbcTemplate.query(sql, new StudentMapper());

return students;

}

}

MainApp.java

package com.hiraymca;

import java.util.List;

import java.util.Scanner; import java.util.ArrayList; import java.util.Iterator;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.dao.EmptyResultDataAccessException;

public class MainApp {

private static int rollno; private static String sname; private static int age; private static int choice; private static String ans;

public static void main(String[] args) {

// TODO Auto-generated method stub

ApplicationContext context=new ClassPathXmlApplicationContext(“Beans.xml”);

Scanner sc=new Scanner(System.in);

StudentDAO stud=(StudentDAO)context.getBean(“studentDAO”); do

{

System.out.println(“1.Insert record”); System.out.println(“2.List all record”); System.out.println(“3.Exit”); System.out.print(“Enter your choice(1..3)”); choice=sc.nextInt();

switch(choice)

{

case 1:

//inserting record System.out.print(“Enter rollno”); rollno=sc.nextInt(); System.out.print(“Enter student name”); sname=sc.next(); System.out.print(“Enter age”); age=sc.nextInt();

stud.saveStudent(new Student(rollno,sname,age)); System.out.println(“Record inserted successfully”); break;

case 2:

//listing record List<Student>slist=stud.listStudents(); System.out.println(“Rollno”+”\t”+”Name”+”\t”+”Age”); for (Student student : slist) {

System.out.println(student.getRollno()+”\t”+student.getSname()+”\ t”+student.getAge());

}

break; case 3:

//exiting from application System.exit(0);

}

System.out.print(“Do you wish to cotinue(y/n) “); ans=sc.next();

}while(ans.equals(“y”)||ans.equals(“Y”));

}

}

Beans.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” xsi:schemaLocation=“http://www.springframework.org/schema/beans

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

<bean id=“ds”

class =

“org.springframework.jdbc.datasource.DriverManagerDataSource”>

<property name = “driverClassName” value =

“com.mysql.jdbc.Driver”/>

<property name = “url” value =

“jdbc:mysql://localhost:3306/studentdb”/>

<property name = “username” value = “root”/>

<property name = “password” value = ““/>

</bean>

<bean id=“jdbcTemplate” class=“org.springframework.jdbc.core.JdbcTemplate”>

<property name=“dataSource” ref=“ds”></property>

</bean>

<bean id=“studentDAO” class=“com.hiraymca.StudentDAO”>

<property name=‘jdbcTemplate’ ref=“jdbcTemplate”></property>

</bean>

</beans>

Output

1.Insert record 2.List all record 3.Exit

Enter your choice(1..3)2 Rollno Name Age

Do you wish to cotinue(y/n)