JDBC operations - SELECT, INSERT, UPDATE, DELETE

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

























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

 }

}