Practical 9 :- Practical based on SPRING JDBC

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

}


}

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

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)