jdbc program removing the record

//jdbc program removing the record

import java.sql.*;
import java.util.*;

public class RemoveEmployee  {
public static void main(String s[]) throws Exception {

Driver d= (Driver) ( Class.forName(
"com.mysql.jdbc.Driver").newInstance());

Properties p=new Properties ();
p.put("user","root");
p.put("password","admin");

Connection con=d.connect(
"jdbc:mysql://localhost:3306/test",p);

Statement st=con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs= st.executeQuery("select empno, ename, sal, deptno from emp");

DatabaseMetaData dbmd=con.getMetaData();

if(dbmd.ownDeletesAreVisible (ResultSet.TYPE_SCROLL_SENSITIVE))
System.out.println("Deletions are visible as a change for this ResultSet object");
else
System.out.println("Deletions are not visible as a change for this ResultSet object");

System.out.println();
System.out.println("RowNo\tEmpno\tName\tSalary\tDeptno");
System.out.println("------------------------------");

while (rs.next()) {
System.out.print(rs.getRow()+"\t");
System.out.print(rs.getInt(1)+"\t");
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getDouble(3)+"\t");
System.out.print(rs.getInt(4)+"\t");

if (rs.getInt(1)==Integer.parseInt(s[0])) {
rs.deleteRow();
System.out.print("Employee Removed");
}//if
System.out.println();
}//while
con.close();
}//main
}//class
//d:\jdbc\javac RemoveEmployee.java
//d:\jdbc\java RemoveEmployee 100
//now emp no 100 will be delted from the database

jdbc program for updating the record (one field)

//jdbc program for updating the record (one field)
import java.sql.*;
import java.util.*;
public class IncrementSalary  {
public static void main(String s[]) throws Exception {

Driver d= (Driver) ( Class.forName(
"com.mysql.jdbc.Driver").newInstance());

Properties p=new Properties ();
p.put("user","root");
p.put("password","admin");

Connection con=d.connect(
"jdbc:mysql://localhost:3306/test",p);

Statement st=con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs= st.executeQuery("select empno, ename, sal, deptno from emp");

System.out.println("Empno\tName\tSalary\tDeptno");
System.out.println("------------------------------");
while (rs.next()) {
System.out.print(rs.getInt(1)+"\t");
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getDouble(3)+"\t");
System.out.print(rs.getInt(4)+"\t");

if (rs.getDouble(3)>2000) {
double amt=rs.getDouble(3)*1.1;
rs.updateDouble(3,amt);
rs.updateRow();
System.out.print("Salary Incremented");
}//if
System.out.println();
}//while
con.close();
}//main
}//class
/*

D:\jdbc>set classpath=D:\softwares\MySQL\new\mysql-connector-java-5.1.18-bin.jar;.;
D:\jdbc>javac IncrementSalary.java
D:\jdbc>java IncrementSalary


1 ram rao 1200.23 2
2 uday kumar 37400.98 10 Salary Incremented
20 sai 200.0 23
100 villa 3.35 4
101 null 0.0 5
*/

Scrollable ResultSet:jdbc program for adding a new record

//Scrollable ResultSet:jdbc program for adding a new record 

import java.sql.*;
import java.util.*;

public class AddEmployee {
public static void main(String s[]) throws Exception {

Driver d= (Driver) ( Class.forName("com.mysql.jdbc.Driver").newInstance());

Properties p=new Properties ();
p.put("user","root");
p.put("password","admin");

Connection con=d.connect(
"jdbc:mysql://localhost:3306/test",p);

Statement st=con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs= st.executeQuery("select empno, ename, sal, deptno from emp");

rs.moveToInsertRow();
rs.updateInt(1,102);
rs.updateString(2,"kalyan");
rs.updateDouble(3,950);
//rs.updateInt(1,20);//dont give any duplicate prime no:Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '20' for key 'PRIMARY'
rs.updateInt(1,40);
rs.insertRow();
System.out.print("Employee Inserted");
con.close();
}//main
}//class

jdbc program for using ResultSetMetaData

import java.sql.*;
import java.util.*;

public class ResultSetMDEx {

public static void main(String s[]) throws Exception {
Class.forName("com.mysql.jdbc.Driver");


Properties p= new Properties();
p.put("user", "root");
p.put("password", "admin");
//DriverManager.getConnection(url,userName,password);
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test",p);

Statement st = con.createStatement();//it creates a object and returns Statement obj

ResultSet rs=st.executeQuery("select * from "+s[0]);
//Table name is taken as an command line arg

ResultSetMetaData rsmd=rs.getMetaData();

System.out.println("Table Name : "+s[0]);

int colcount=rsmd.getColumnCount();

for (int i=1;i<=colcount;i++) {
System.out.print(rsmd.getColumnName(i)+"\t");
System.out.println(rsmd.getColumnTypeName(i));
}//for

con.close();
}//main
}//class
/*


D:\>cd jdbc

D:\jdbc>cd prog

D:\jdbc\prog>cd monday

D:\jdbc\prog\monday>javac ResultSetMDEx.java

D:\jdbc\prog\monday>set classpath=D:\softwares\MySQL\new\mysql-connector-java-5.
1.18-bin.jar;.;

D:\jdbc\prog\monday>java ResultSetMDEx emp;
Table Name : emp;
empno   INT
ename   VARCHAR
sal     DOUBLE
hiredate        DATE

D:\jdbc\prog\monday>
*/

jdbc Example program for ResultSet object using Normal Statement

import java.sql.*;
public class GetData
{
 public static void main(String arg[]) throws SQLException,ClassNotFoundException
 {
  Connection con=prepareConnection();
  Statement st=con.createStatement();
  String q="SELECT empno,ename,sal FROM emp;";
  //execute query
  ResultSet rs=st.executeQuery(q);
  //
  while(rs.next())
  {
   System.out.println(rs.getInt(1)+"\t");
   System.out.println(rs.getString(2));
   System.out.println(rs.getDouble(3));
   }
 // System.out.println(rs.getString(2)+"\t");
 
}
public static Connection prepareConnection() throws SQLException,ClassNotFoundException
{
 String d="com.mysql.jdbc.Driver";
 String url="jdbc:mysql://localhost:3306/test";
 String uname="root";
 String pwd="admin";
 //load
 Class.forName(d);
 //obtain the connection
 return(DriverManager.getConnection(url,uname,pwd));
 }//prepared connection()
}

  

jdbc program for retrieving the values (data) from the database using MySql

import java.sql.*;//jdbc api
//import com.mysql.jdbc.*;
public class SelectTest1
{
 public static void main(String args[]) throws Exception
 {
 // try
 // {
    //load the jdbc driver class
 //   Class.forName("com.mysql.jdbc.Driver");
    com.mysql.jdbc.Driver dr=new com.mysql.jdbc.Driver();
    System.out.println("jdbc driver is registered with Driver manager Service");

   //establish the connection with DB SW
    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","admin");
    System.out.println("connection is established with D/B S/W");

    //create the statement object
    Statement st=con.createStatement();
    System.out.println("statement object is ready ");

    //send query to db s/w make a query executing in db s/w and get result
    ResultSet rs=st.executeQuery("select * from students");
    System.out.println("query executed in db s/w & ResultSet object is created");

   //process the ResultSet object and display the student
   while(rs.next())
   {
    int n=rs.getInt("stid");
    String s=rs.getString("sname");
    String addr=rs.getString("sadd");
    System.out.println(n+"  "+s+"  "+addr);
   }
   System.out.println("Result set is displayed");
  //close the jdbc stream objects
   rs.close();
   st.close();
   con.close();

// }
// catch(ClassNotFoundException cnf)
// {
//  cnf.printStackTrace();
// }
// catch(SQLException se)
// {
//  se.printStackTrace();
// }
// catch(Exception e)
// {
//  e.printStackTrace();
// }
}
 }
/*
D:\jdbc>set classpath=D:\softwares\MySQL\new\mysql-connector-java-5.1.18-bin.jar;.;
D:\jdbc>javac SelectTest1.java
D:\jdbc>java SelectTest1
*/

jdbc program for retrieving the values (data) from the database using MySql

import java.sql.*;//jdbc api
public class SelectTest
{
 public static void main(String args[])
 {
  try
  {
    //load the jdbc driver class
    Class.forName("com.mysql.jdbc.Driver");
    System.out.println("jdbc driver is registered with Driver manager Service");

   //establish the connection with DB SW
    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","admin");
    System.out.println("connection is established with D/B S/W");
    //create the statement object
    Statement st=con.createStatement();
    System.out.println("statement object is ready ");
    //send query to db s/w make a query executing in db s/w and get result

    ResultSet rs=st.executeQuery("select * from students");
    System.out.println("query executed in db s/w & ResultSet object is created");
   //process the ResultSet object and display the student
   while(rs.next())
   {
    int n=rs.getInt("stid");
    String s=rs.getString("sname");
    String addr=rs.getString("sadd");
    System.out.println(n+"  "+s+"  "+addr);
   }
   System.out.println("Result set is displayed");
  //close the jdbc stream objects
   rs.close();
   st.close();
   con.close();
   System.out.println("jdbc stream objects are closed");
  //to know the class names of jdbc objects
   System.out.println("classname of st obje="+st.getClass().getName());
   System.out.println("classname of con obje="+con.getClass().getName());
   System.out.println("classname of rs obje="+rs.getClass().getName());
 }
 catch(ClassNotFoundException cnf)
 {
  cnf.printStackTrace();
 }
 catch(SQLException se)
 {
  se.printStackTrace();
 }
 catch(Exception e)
 {
  e.printStackTrace();
 }
}
}
/*
D:\jdbc>set classpath=D:\softwares\MySQL\new\mysql-connector-java-5.1.18-bin.jar;.;
D:\jdbc>javac SelectTest.java
D:\jdbc>java SelectTest
*/