Friday, March 14, 2014
In our last post we discussed about how to obtain a database connection.After we have it, we can carry out different JDBC operations. Here we will discuss about

  • How to insert new records/rows in database tables (INSERT operation)
  • How to find a record/row in database tables (SELECT operation)
  • How to update records/rows in tables (UPDATE operation)
  • How to delete a record/row from tables (DELETE operation)
Different JDBC operations
SQL is written for carrying out the operations and it is either passed to a Statement or a PreparedStatement. The latter is used as DBMS has to only recompile and carry out the task. There is no need of parsing if PreparedStatement is used. Also it prevents SQL injection. You should use it everywhere when you have to pass parameters.  It is recommended to use in all public applications.

First create a database table Student as follows :
  CREATE TABLE student(id number primary key,name varchar(30));

-------------------------------------------------------------------------------------------------------------------------
Java Source Code
-------------------------------------------------------------------------------------------------------------------------
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentService {

 private Connection con;
 public StudentService(Connection con){
  this.con = con;  //database connection object
 }
 //Inserting new row in table with given id and name
 public void insertRecord(int id, String name) throws SQLException{
  con.setAutoCommit(false);
  String sql = "INSERT INTO student VALUES(?,?)";
  PreparedStatement ps = con.prepareStatement(sql);
  ps.setInt(1, id);
  ps.setString(2, name);
  ps.executeUpdate();
  con.commit();
  ps.close();
 }
 //Find a record based on id
 public void findRecord(int id) throws SQLException{
  String sql = "SELECT * FROM student WHERE id=?";
  PreparedStatement ps = con.prepareStatement(sql);
  ps.setInt(1, id);
  ResultSet rs = ps.executeQuery();
  if(rs.next())
   System.out.println(rs.getInt("id")+"\t"+rs.getString("name"));
  else
   System.out.println("No records found");
  ps.close();
 }
 //Find all the records
 public void findAllRecord() throws SQLException{
  con.setAutoCommit(false);
  String sql = "SELECT * FROM student";
  PreparedStatement ps = con.prepareStatement(sql);
  ResultSet rs = ps.executeQuery();
  while(rs.next())
   System.out.println(rs.getInt("id")+"\t"+rs.getString("name"));
  ps.close();
 }
 //Update a record with new name based on id
 public void updateRecord(int id, String name) throws SQLException{
  con.setAutoCommit(false);
  String sql = "SELECT id,name FROM student WHERE id=?";
  PreparedStatement ps=con.prepareStatement(sql,
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_UPDATABLE);
  ps.setInt(1, id);
  ResultSet rs=ps.executeQuery();
  rs.updateString("name", name);
  rs.updateRow();
  con.commit();
  ps.close();
 }
 //Delete a record based on id
 public void deleteRecord(int id) throws SQLException{
  con.setAutoCommit(false);
  String sql = "DELETE FROM student WHERE id=?";
  PreparedStatement ps = con.prepareStatement(sql);
  ps.setInt(1, id);
  ps.executeUpdate();
  con.commit();
  ps.close();
 }
}
-------------------------------------------------------------------------------------------------------------------------
Download Links
-------------------------------------------------------------------------------------------------------------------------

0 comments:

Post a Comment

Total Pageviews

Subscribe via Email

Followers


Popular Posts

About Me

My photo

I am a student of BTech Computer Science Engineering from RCCIIT,Kolkata. I am a crazy lover of Java and wants to settle as a Java developer. I have a seven years Java experience with an application developer experience for 2 years. Recently from March 2012 I am a registered S40 app developer for Nokia and has corrected an app of them. I am currently writing blogs to encourage and grow interest in all those who don't know or learning Java.