jdbc: December 2013

//jdbc program using PreparedStatement
import java.sql.*;

public class PStatEx1 {

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

Class.forName("com.mysql.jdbc.Driver");
Connection con= DriverManager.getConnection ("jdbc:mysql://localhost:3306/test","root","admin");

String query="insert into mytable values (?,?,?)";

//Step1: Get PreparedStatement
PreparedStatement ps=con.prepareStatement (query);

//Step2: set parameters
ps.setInt(1,26);
ps.setString(2,"Rajendra");

ps.setString(3,"hyd");

//Step3: execute the query
int i=ps.executeUpdate();

System.out.println("record inserted count:"+i);

//To execute the query once again
ps.setInt(1,39);
ps.setString(2,"pavan");

ps.setString(3,"Blore");

i=ps.executeUpdate();
System.out.println("query executed for the second time count: "+i);
con.close();
}//main
}//class

jdbc program using joins

//jdbc program using joins
/*
* CREATE TABLE `user` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`course` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
table2s name course
CREATE TABLE `course` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

step 3:
ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;

*/

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class Joins {

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_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//INNER JOIN (or just JOIN)
// String query="SELECT user.name, course.name FROM `user` INNER JOIN `course` on user.course = course.id;";
//LEFT JOIN
String query="SELECT user.name, course.name FROM `user` LEFT JOIN `course` on user.course = course.id;";
//
/*
SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id

UNION

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

*/

ResultSet rs= st.executeQuery(query);
//Now the cursor of resultset will at beforeFirst & the result set produced is scrollable

System.out.println("EmpNo\tName");

while (rs.next()) {
System.out.println(rs.getString(1)+"\t");
System.out.println(rs.getString(2));

}//while
//Now the cursor of resultset will at afterLast

System.out.println("Reading Data, moving the cursor in backward direction\n");

while (rs.previous()){
System.out.println(rs.getString(1)+"\t");
System.out.println(rs.getString(2));

}//while
con.close();
}//main

}

jdbc program for inserting the image file into the database

//before executing this program you have to create table
create table personaldetails (
empno int(3) not null default '0',

photo BLOB(1000) NULL,
permanent_address varchar(25) not null default '',
present_address varchar(25) not null default '',

);

2) here i have kept one photo graph that photo file name is raj with gif extension in D:\jdbc\prog\wed\Blob\raj.gif file
*/
import java.sql.*;
import java.io.*;

class SaveImageToDatabase {
public static void main(String[] args) throws SQLException {
// declare a connection by using Connection interface
Connection connection = null;
/* Create string of connection url within specified format with machine
name, port number and database name. Here machine name id localhost
and database name is test. */
String connectionURL = "jdbc:mysql://localhost:3306/test";
/*declare a resultSet that works as a table resulted by execute a specified
sql query. */
ResultSet rs = null;
// Declare prepare statement.
PreparedStatement psmnt = null;
// declare FileInputStream object to store binary stream of given image.
FileInputStream fis;
try {
// Load JDBC driver "com.mysql.jdbc.Driver"
Class.forName("com.mysql.jdbc.Driver").newInstance();

/* Create a connection by using getConnection() method that takes
parameters of string type connection url, user name and password to
connect to database. */
connection = DriverManager.getConnection(connectionURL, "root", "admin");
// create a file object for image by specifying full path of image as parameter.
//File image = new File("C:/image.jpg");
File image = new File("D:/jdbc/prog/wed/Blob/raj.GIF");
/* prepareStatement() is used for create statement object that is
used for sending sql statements to the specified database. */
psmnt = connection.prepareStatement
//("insert into save_image(name, city, image, Phone) "+ "values(?,?,?,?)");
("insert into personaldetails(empno,photo,permanent_address)"+ "values(?,?,?)");

psmnt.setInt(1,3);
fis = new FileInputStream(image);
psmnt.setBinaryStream(2, (InputStream)fis, (int)(image.length()));
psmnt.setString(3,"Hyd");
//psmnt.setString(4,"123456");
//fis = new FileInputStream(image);
//psmnt.setBinaryStream(3, (InputStream)fis, (int)(image.length()));
/* executeUpdate() method execute specified sql query. Here this query
insert data and image from specified address. */
int s = psmnt.executeUpdate();
if(s>0) {
System.out.println("Uploaded successfully !");
}
else {
System.out.println("unsucessfull to upload image.");
}
}

// catch if found any exception during rum time.
catch (Exception ex) {
System.out.println("Found some error : "+ex);
}
finally {
// close all the connections.
connection.close();
psmnt.close();
}
}
}

jdbc program for reading the content from the note pad

/*prepare properties text file having DB username, pwd
user=root.
pwd=admin
D:\jdbc\prog\wed\Properties\props.txt
Note: in the above file the keys user, password are fixed names.
*/
import java.sql.*;
import java.sql.DriverManager;
import java.util.*;
import java.io.*;
public class SelectTest {
public static void main(String args[]) throws Exception
{
FileInputStream fis=new FileInputStream("D:\\jdbc\\prog\\wed\\Properties\\props.txt");
Properties p=new Properties();
p.load(fis);
System.out.println("data in 'p' object is"+p.toString());
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","admin");
if(con==null)
System.out.println("Connection not established");
else
System.out.println("connection established");

}

}
/*
the above code is very useful to pass DB username and password from outside the application when DBA
is changing the DB username and passwords at regular intervals for security reasons
*/

jdbc program for reading the data from Excel sheet

/*step1: create the data in excel sheet, and save (same folder).
here i created: id, ename sal
click->start->control panel->select administrative tool and then Data Sources (ODBC), TO OPEN ODBC Data Source
Administrator dialog box
see the ppt for navigation
*/

import java.sql.*;

public class GetExcelSheet {

public static void main(String args[])throws SQLException, ClassNotFoundException {

Connection con=prepareConnection();
Statement st=con.createStatement();
String query = "select * from [Sheet1$]";
ResultSet rs=st.executeQuery(query);

System.out.println ("id\t ename\tsal:");
while (rs.next()){
System.out.print (rs.getInt(1)+ "\t");
System.out.print(rs.getString("ename")+ "\t");
System.out.println (rs.getDouble("sal"));
}//while
}//main

public static Connection prepareConnection()throws SQLException, ClassNotFoundException {

String driverClassName="sun.jdbc.odbc.JdbcOdbcDriver";
String url="jdbc:odbc:rajdsn";
Class.forName(driverClassName);
return DriverManager.getConnection(url);
}//prepareConnection
}//class
/*
D:\jdbc\prog\wed>javac GetExcelSheet.java

D:\jdbc\prog\wed>set classpath=D:\softwares\MySQL\new\mysql-connector-java-5.1.1
8-bin.jar;.;

D:\jdbc\prog\wed>java GetExcelSheet
*/

jdbc program far BatchUpdates ex2

/*CREATE TABLE `emp` (
`empno` int(4) NOT NULL AUTO_INCREMENT,
`ename` varchar(25) DEFAULT NULL,
`sal` double(10,2) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`deptno` int(4) DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=206 DEFAULT CHARSET=latin1

*/

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

public class BatchUpdateEx1 {

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();
//statement1:empno,ename,sal
//st.addBatch("insert into emp(empno,sal,deptno) values("+s[0]+",1000,10)");
st.addBatch("insert into emp(empno,ename,sal,deptno) values(102,'Ashwini',4000,30)");
//statement2
st.addBatch("update emp set sal=2000 where empno=20");
//statement3
st.addBatch("insert into emp(empno,sal,deptno) values(202,1000,10)");
//statement4
st.addBatch("insert into emp(empno,sal,deptno) values(203,1000,10)");

try {
int[] counts=st.executeBatch();
System.out.println("Batch Executed Successfully");
for (int i=0;i<counts.length;i++){
System.out.println("Number of records effected by statement"+(i+1)+": "+counts[i]);
}//for
}//try
catch(BatchUpdateException e){
System.out.println("Batch terminated with an abnormal condition");

int[] counts=e.getUpdateCounts();
System.out.println("Batch terminated at statement"+ (counts.length+1));

for (int i=0;i<counts.length;i++) {
System.out.println("Number of records effected by the statement"+ (i+1)+": "+counts[i]);
}//for
}//catch
con.close();
}//main
}//class
/*
* Batch terminated at statement5
Number of records effected by the statement1: 1
Number of records effected by the statement2: 1
Number of records effected by the statement3: -3
Number of records effected by the statement4: -3
*/

jdbc program for BatchUpdate using prepared Statement

//jdbc program for BatchUpdate using prepared Statement
/*BatchUpdate: inserting multiple records in a single program*/

import java.sql.*;
import java.util.*;
import java.io.*;
public class BatchUpdateEx2 {

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);
//st.addBatch("insert into emp(empno,ename,sal,deptno) values(102,'Ashwini',4000,30)");

PreparedStatement ps= con.prepareStatement("insert into emp(empno,ename,sal,deptno) values(?,?,?,?)");

ps.setInt(1,204);
ps.setString(2, "santosh");
ps.setDouble(3,1000);
ps.setInt(4,10);

ps.addBatch();

ps.setInt(1,205);
ps.setString(2, "rakesh");
ps.setDouble(3,3000);
ps.setInt(4,5);

ps.addBatch();

try {
int counts[]= ps.executeBatch();
System.out.println("Batch Executed Successfully");
for (int i=0;i<counts.length;i++){
System.out.println("Number of records effected by statement"+(i+1)+": "+counts[i]);
}//for
}//try
catch(BatchUpdateException e){
System.out.println("Batch terminated with an abnormal condition");

int[] counts=e.getUpdateCounts();
System.out.println("Batch terminated at statement"+ (counts.length+1));

for (int i=0;i<counts.length;i++) {
System.out.println("Number of records effected by the statement"+ (i+1)+": "+counts[i]);
}//for
}//catch
con.close();
}//main
}//class

Example to demonstrate Scrollable ResultSet

//Example to demonstrate Scrollable ResultSet

import java.sql.*;
import java.util.*;
import java.io.*;
public class ScrollableRSEx1 {
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_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

String query="select * from emp where deptno=10";

ResultSet rs= st.executeQuery(query);
//Now the cursor of resultset will at beforeFirst & the result set produced is scrollable

System.out.println("EmpNo\tName");

while (rs.next()) {
System.out.print(rs.getInt(1)+"\t");
System.out.println(rs.getString(2));
}//while
//Now the cursor of resultset will at afterLast

System.out.println("Reading Data, moving the cursor in backward direction\n");

while (rs.previous()){
System.out.print(rs.getInt(1)+"\t");
System.out.println(rs.getString(2));
}//while
con.close();
}//main
}//class
/*
EmpNo Name
2 uday kumar
Reading Data, moving the cursor in backward direction

2 uday kumar

*/

What is ResultSet?

Result set ppt

jdbc program for deleting a record

//jdbc program for deleting a record
//STEP 1. Import required packages
import java.sql.*;

public class DelEmp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/test";

// Database credentials
static final String USER = "root";
static final String PASS = "admin";

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");

//STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");

//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql = "DELETE FROM emp " +
"WHERE empno = 101";
stmt.executeUpdate(sql);

// Now you can extract all the records
// to see the remaining records
sql = "SELECT empno, ename, sal, hiredate,deptno FROM emp";
ResultSet rs = stmt.executeQuery(sql);

while(rs.next()){
//Retrieve by column name
int id = rs.getInt("empno");
String first = rs.getString("ename");
double sal = rs.getInt("sal");
// date hiredate=res.getDate("hiredate");
int deptno = rs.getInt("deptno");

//Display values
System.out.print("empno: " + id);
System.out.print(", ename: " + first);
System.out.print(", sal: " + sal);
// System.out.println(", hiredate: " + hiredate);
}
rs.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end JDBCExample

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
*/

jdbc program for inserting the values into the table

import java.sql.*;
public class Ex3
{
public static void main(String arg[]) throws SQLException, ClassNotFoundException
{
String driverClassName="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/test";
String userName="root";
String password="admin";

//load driver class,
Class.forName(driverClassName);
//get the connection
Connection con=DriverManager.getConnection(url,userName,password);
//get the statement
Statement st=con.createStatement();
//execute the query

int count=st.executeUpdate("insert into students values(103,'ram','sec')");
System.out.println("no of rows "+count);
//close the connection
con.close();
}//main
}//class
/*
/*
D:\jdbc>set classpath=D:\softwares\MySQL\new\mysql-connector-java-5.1.18-bin.jar;.;
D:\jdbc>javac Ex3.java
D:\jdbc>java Ex3
*/

*/

inserting the values connecting to the db using user id and password in Mysql

//connecting to the db using user id and password

import java.sql.*;
public class Ex2
{
public static void main(String arg[]) throws SQLException, ClassNotFoundException
{
String driverClassName="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/test";
String userName="root";
String password="admin";
// String q="insert into students values(101,\'kumar\')";//or
// String q="insert into students values(102,'raj')";
//load driver class,
Class.forName(driverClassName);
//get the connection
Connection con=DriverManager.getConnection(url,userName,password);
//get the statement
Statement st=con.createStatement();
//execute the query
// int count=st.executeUpdate(q);
//or
int count=st.executeUpdate("insert into students values(103,'ram','sec')");
System.out.println("no of rows "+count);
//close the connection
con.close();
}//main
}//class
/*
D:\jdbc>set classpath=D:\softwares\MySQL\new\mysql-connector-java-5.1.18-bin.jar;.;
D:\jdbc>javac Ex2.java
D:\jdbc>java Ex2
*/

inserting into the table using url

//connecting to the db using url
import java.sql.*;//jdbc api
class jdbcdemo
{

public static void main(String[] args) throws Exception
{

//Class.forName("oracle.jdbc.driver.OracleDriver");
//Connection conn = DriverManager.getConnection("jdbc:odbc:oracleXE","sai","sai");
//Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","sai","sai" );
//loading the jdbc driver
Class.forName("com.mysql.jdbc.Driver");

//establish the connection with db sw
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "admin");

Statement st= con.createStatement();

st.executeUpdate("insert into emp values(20,'sai',200)");
System.out.println("Rows inserteded");
st.close();
con.close();

}

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

creating table in MYSQL

//using Mysql and database name is test, so create one database in mysql(ex: in this prog
//i have already created one database in 'test'. If u wont create u get error
import java.sql.*;//jdbc api
public class CreateTable
{
public static void main(String args[]) throws SQLException, ClassNotFoundException
{
//get connection
Connection con=prepareConnection();
//obtain a statement
Statement st=con.createStatement();
String q= "CREATE TABLE `test`.`mytable`( `id` INT(3) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20), `add` VARCHAR(40), PRIMARY KEY (`id`) )";

//execute the query
st.executeUpdate(q);
System.out.println("Table created successfully");
con.close();

}
public static Connection prepareConnection() throws SQLException, ClassNotFoundException
{
String d="com.mysql.jdbc.Driver";//driver class name
String url="jdbc:mysql://localhost:3306/test";
String uname="root";
String pwd="admin";
//load driver class
Class.forName(d);
//obtain the connection
Connection con=DriverManager.getConnection(url,uname,pwd);
return(con);

}
}

/* TO RUN THIS PROGRAM

1) Install MySql , i gave my username is "root", password is "admin"

2) 2) download jar files of MySql: "mysql-connector-java-5.1.18-bin.jar" and save in any folder i saved this jar file in "D:\softwares\MySQL\new"

3) set the class path:

example

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

;.;

4) now compile the program

D:\jdbc>javac CreateTable.java

5) execute the program

D:\jdbc>java CreateTable

Thursday, 5 December 2013

RowSets

• We can send only those java objects over the network that are Serializable.

• In order to make object as Serializable object, the class of the object must implement java.io.Serializable interface.

• In order to store data of the object in a file, the object must be serializable object.

• ResultSet object is not Serializable object, so we can not send this object over the network directly.

• To overcome this problem, there are two solutions

• 1. Use RowSets in the place of ResultSets.

• 2. copy data of ResultSet object to RowSet

• Rowsets are Serializable objects and given as enhancement of ResultSet

• All collection framework dataStructures are Serializable objects by default.

• RowSet object means it is the object of a class that implements javax.sql.RowSet interface

• RowSets are introduced to JDBC from JDBC 2.0

TYPES OF ROWSETS

• There are 3 types of RowSets

• 1. Cached RowSet

• Disconnected Rowset, like insensitive ResultSet.

• 2.JDBC Rowset: like sensitive ResultSet

• 3.Web Rowset: connected RowSet->like Sensitive ResultSet, userful to store xml data collected from the table

• Cached RowSet, JDBC RowSet can not deal with xml data as column values. But web RowSet can do this work.

• The oracle thin driver, OCI driver is giving support for two types of RowSets. (catched RowSet, JDBC RowSet).

• The oracle corporation supplies two classes having the functionality of cached, JDBC RowSet.

• They are :

• 1. oracle.jdbc.RowSet.OracleCachedRowSet.

• 2.

• These two classes are there oracle-home\ora92\jdbc\lib\ocrs12.jar file

• So we need to add it in classpath.

• If one jar file classes are using the classes of other jar files then other jar files are called dependent jar files to main jar file.

• When java application is using classes of main jar file and these classes are having dependent classes in other jar files, then we need to execute both main and dependent jar files in the classpath.

• Ojdbc14.jar file is not having dependent jar files. But this ojdbc14.jar file is the dependent jar file for ocrs12.jar file.

• In order to work with Oracle corporation supplied RowSets, we need to add ocrs12.jar file, ojdbc14.jar files in claspath environment variable.

Advantages of RowSets

• 1. RowSet object are Serializable objects. So we can send them directly over the network.

• 2. we can perform select operations on the table by simply working with setXXX(), getXXX() methods and there is no need of working with the regular JDBC objects like Connection, ResultSet etc.

Limitations of RowSets

• 1. RowSets are given only for select operations. So non-select operations are not possible.

RowSet example program

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.sql.rowset.JdbcRowSet;

import com.sun.rowset.JdbcRowSetImpl;

public class JavaJDBCRowSetExample {

public static void main(String[] args) throws SQLException {

try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection("jdbc:odbc:swing");

String sql = "select * from emp";

Statement ps = con.createStatement(1004, 1008);

ResultSet rs = ps.executeQuery(sql);

JdbcRowSet jdbcRs = new JdbcRowSetImpl(rs);

jdbcRs.afterLast();

System.out.println("Records before deleting of row");

System.out.println("id \t fName \t lName \t address");

while (jdbcRs.previous())

{

int id = jdbcRs.getInt("id");

String fName = jdbcRs.getString("fName");

String lName = jdbcRs.getString("lName");

String address = jdbcRs.getString("address");

System.out.println(id+" \t "+fName+" \t "+lName+" \t "+address);

}

boolean bol = jdbcRs.absolute(3);

int i = jdbcRs.getRow();

System.out.println("Deleting row no. "+i+".....");

jdbcRs.deleteRow();

jdbcRs.refreshRow();

System.out.println("row no. "+i+" is deleted");

jdbcRs.afterLast();

System.out.println("Records after deleting of row");

System.out.println("id \t fName \t lName \t address");

while (jdbcRs.previous())

{

int id = jdbcRs.getInt("id");

String fName = jdbcRs.getString("fName");

String lName = jdbcRs.getString("lName");

String address = jdbcRs.getString("address");

System.out.println(id+" \t "+fName+" \t "+lName+" \t "+address);

}

rs.close();

ps.close();

}

catch(Exception ex)

{

System.out.println(ex);

}

OTHER BLOGS

Wednesday, 11 December 2013

Thursday, 5 December 2013