CRUD(Create, Read, Update and Delete)
operations using JDBC
===============================================
CRUD stands for Create, Read, Update, Delete.
Student Table CREATE TABLE STUDENT(StudentID NUMBER PRIMARY KEY, StudentName VARCHAR2(30), Age NUMBER(3));
Create Statement:
INSERT INTO STUDENT(StudentID, StudentName, Age) VALUES(1, 'Ranga', 24);READ Statement:
SELECT * FROM STUDENT WHERE StudentID = 1;UPDATE Statement:
UPDATE STUDENT SET StudentName='RangaReddy', Age=25 WHERE StudentID =1
DELETE Statement:
DELETE FROM STUDENT WHERE StudentID = 1
Generating the Primary Key Values
In Oracle, by using Sequence we can generate the Automatic Primary key values
Creating a Sequence:
CREATE SEQUENCE Student_Id_Seq START WITH 1 INCREMENT BY 1;
By using sequence, insert the Student data
INSERT INTO STUDENT(StudentID, StudentName, Age) VALUES(Student_Id_Seq.nextval, 'Ranga', 25);We can also get the Primary key values by using Max() function
int studentID = "SELECT MAX(STUDENTID) FROM STUDENT";then adding 1 to the studentIDfinally the studentID is "SELECT MAX(STUDENTID) FROM STUDENT" + 1============================================================================================ Now we can see the CRUD operations by using JDBC 
=================================================
package com.ranga;
import java.io.Serializable;
@SuppressWarnings("serial")
public class Student implements Serializable {
private long studentId;
private String studentName;
private int age;
public Student(long studentId, String studentName, int age) {
super();
this.studentId = studentId;
this.studentName = studentName;
this.age = age;
}
public Student() {
super();
}
public long getStudentId() {
return studentId;
}
public void setStudentId(long studentId) {
this.studentId = studentId;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student [studentId=" + studentId + ", studentName="
+ studentName + ", age=" + age + "]";
}
}
-----------------------------------------------------------------------------------------------------------------------------------
DBUtil.java
==================================================
package com.ranga;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
private static Connection con;
private static String username ="ranga";
private static String password = "ranga";
private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
private static String driverClass = "oracle.jdbc.driver.OracleDriver";
static {
try {
Class.forName(driverClass);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
return con = DriverManager.getConnection(url, username, password);
}
public static void beginTransaction(){
if(con!=null){
try {
con.setAutoCommit(false);
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void commit(){
if(con!=null){
try {
con.commit();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void rollback(){
if(con!=null){
try {
con.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void closeDBUtil(ResultSet rs, Statement stmt, Connection con){
try {
if(rs!=null) {
rs.close();
rs =null;
}
}
catch (SQLException ex) {
ex.printStackTrace();
}
try {
if(stmt!=null) {
stmt.close();
stmt =null;
}
}
catch (SQLException ex) {
ex.printStackTrace();
}
try {
if(con!=null) {
con.close();
con =null;
}
}
catch (SQLException ex) {
ex.printStackTrace();
}
}
}
--------------------------------------------------------------------------------------------------------------------------------
App1.java
=================================================
package com.ranga;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author ranga
* @date 16-Jul-2013
*/
public class App1 {
public static void main(String args[]) throws Exception {
// Creating a Student object and setting the values
Student st = new Student();
st.setStudentName("Ranga");
st.setAge(20);
// inserting Student object and getting the studentId
long studentId = createStudent(st);
// fetching the Student object by using studentId
Student student = getStudent(studentId);
// displaying student data before update
System.out.println("Before Update : " + student.toString());
// updating the student object values
student.setStudentName("RangaReddy");
student.setAge(25);
// updating the modified student
updateStudent(student);
student = getStudent(studentId);
// displaying the updated data
System.out.println("After Update : " + student);
// deleting the student
deleteStudent(studentId);
}
private static void deleteStudent(long studentId) throws SQLException {
Connection con = DBUtil.getConnection();
String sql = "DELETE FROM STUDENT WHERE StudentID = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setLong(1, studentId);
DBUtil.beginTransaction();
int res = pstmt.executeUpdate();
if (res != 0) {
DBUtil.commit();
} else {
DBUtil.rollback();
}
DBUtil.closeDBUtil(null, pstmt, con);
}
private static void updateStudent(Student stu) throws SQLException {
String sql = "UPDATE STUDENT SET StudentName=?, Age=? WHERE StudentID =?";
Connection con = DBUtil.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, stu.getStudentName());
pstmt.setInt(2, stu.getAge());
pstmt.setLong(3, stu.getStudentId());
DBUtil.beginTransaction();
int result = pstmt.executeUpdate();
if (result != 0) {
DBUtil.commit();
} else {
DBUtil.rollback();
}
DBUtil.closeDBUtil(null, pstmt, con);
}
private static Student getStudent(long studentId) throws SQLException {
String sql = "SELECT * FROM STUDENT WHERE StudentID = " + studentId;
Connection con = DBUtil.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
DBUtil.beginTransaction();
Student student = null;
if (rs.next()) {
student = new Student();
student.setStudentId(rs.getLong(1));
student.setStudentName(rs.getString(2));
student.setAge(rs.getInt(3));
DBUtil.commit();
} else {
DBUtil.rollback();
}
DBUtil.closeDBUtil(rs, stmt, con);
return student;
}
private static long createStudent(Student stu) throws Exception {
long studentId = generateStudentId() + 1;
String sql = "INSERT INTO STUDENT(StudentID, StudentName, Age) VALUES(?, ?, ?)";
Connection con = DBUtil.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setLong(1, studentId);
pstmt.setString(2, stu.getStudentName());
pstmt.setInt(3, stu.getAge());
DBUtil.beginTransaction();
int result = pstmt.executeUpdate();
if (result != 0) {
DBUtil.commit();
} else {
DBUtil.rollback();
}
DBUtil.closeDBUtil(null, pstmt, con);
return studentId;
}
private static long generateStudentId() {
String query = "SELECT MAX(StudentID) from STUDENT";
Connection con = null;
Statement stmt = null;
long res = 0;
try {
con = DBUtil.getConnection();
stmt = con.createStatement();
res = stmt.executeUpdate(query);
} catch (SQLException ex) {
ex.printStackTrace();
}
DBUtil.closeDBUtil(null, stmt, con);
return res;
}
}
---------------------------------------------------------------------------------------------------------------------------
Output:
Before Update : Student [studentId=2, studentName=Ranga, age=20]
After Update : Student [studentId=2, studentName=RangaReddy, age=25]
Download
0 comments:
Post a Comment