Practical 9 :- Practical based on SPRING JDBC
Write a program to insert, update and delete records from the given table.
code
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")); } }
Write a program to demonstrate PreparedStatement in Spring JdbcTemplate
code
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
Write a program in Spring JDBC to demonstrate ResultSetExtractor Interface
code
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 aaa 23 bbb 21 ccc 23 ddd 25 eee 21 fff 23 ggg 21 Do you wish to cotinue(y/n)
Write a program to demonstrate RowMapper interface to fetch the records from thedatabase.
code