Java Code House

• 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

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