jdbcexamples

program1:

import java.sql.*;
public class CreateTable
{
public static void main(String rags[])
{
try
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
System.out.println(“driver loaded”);

Connection con =DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
System.out.println(“connection established”);
Statement stmt=con.createStatement();
System.out.println(“statement object created”);

stmt.executeUpdate(“create table employee(eno number,ename varchar2(15),esal number(10,2),eaddr varchar2(20))”);

System.out.println(“table created”);
stmt.close();
con.close();
}//try
catch(SQLException se)
{
System.out.println(“database problems”+se);
}
catch(ClassNotFoundException cnf)
{
System.out.println(cnf);
}
catch(Exception e)
{
System.out.println(e);
}

}//main
}//class

program2:

import java.sql.*;
public class InsertingValues
{
public static void main(String args[])
{
try
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement stmt = con.createStatement();
stmt.executeUpdate(“insert into employee values(101,’ram’,9899.56,’dsnr’)”);
stmt.executeUpdate(“insert into Employee values(102,’raj’,9999.99,’hyderabad’)”);
stmt.executeUpdate(“insert into Employee values(103,’sam’,7777.77,’chennai’)”);
System.out.println(“Records inserted”);
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println(se);
}
catch(ClassNotFoundException e)
{
System.out.println(e);
}
catch(Exception e)
{
System.out.println(e);
}

}//main
}//class

program3:
import java.util.*;
import java.sql.*;
public class UpdateRecord
{
public static void main(String[] args)throws Exception
{
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
Connection con =DriverManager.getConnection(“jdbc:odbc:ksreddyDSN”,”scott”,”tiger”);
Statement st = con.createStatement();
Scanner s= new Scanner(System.in);
System.out.println(“enter employee number”);
int empno=s.nextInt();
int updateCount = st.executeUpdate(“update employee set esal =esal+5000 where eno=”+empno);
System.out.println(“records updated”+updateCount);
st.close();
con.close();
}
}

program4:

import java.sql.*;
public class DeleteRecord
{
public static void main(String[] args)throws Exception
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Connection con = DriverManager.getConnection(“jdbc:odbc:ksreddyDSN”,”scott”,”tiger”);

Statement st = con.createStatement();

int updateCount = st.executeUpdate(“delete from employee where esal<=1000”);
System.out.println(“records deleted—–“+updateCount);
st.close();
con.close();
}
}
/*
for deleting all the records
st.executeUpdate(“delete from employee”);
*/

program5:
import java.sql.*;
import java.util.*;
public class DropTable
{
public static void main(String[] args)
{
Connection con=null;
Statement st=null;
Scanner s=null;
try
{
s=new Scanner(System.in);
System.out.println(“Enter table name to delete “);
String tab=s.next();
//Db code
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
con=DriverManager.getConnection(“jdbc:odbc:ksreddyDSN”,”system”,”system”);

st=con.createStatement();
st.executeUpdate(“drop table “+tab);
System.out.println(“deleted table is”+tab);
}//try
catch(ClassNotFoundException cnf)
{
//cnf.printStackTrace();
System.out.println(cnf);
}
catch(SQLException se)
{
se.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
System.out.println(e);
}
}//main
}//class

program6:

/*
ResultSet is an interface of java.sql package.
It is a table of data representing a database query result.
ResultSet cursor points before the fist row of the data.
To move the cursor to the next record we call next()
method.
The ResultSet provides getter methods(getBoolean((), getInt(),getString()) for retrieving column values from the current row.
Values can be retrieved using either the index number of the column or the name of the column.
There is a get method for each data type,and each get method has two versions which takes column name and column index.
XXX getXXX(String columnName)
XXX getXXX(int columnIndex)
methods for Navigation
1]beforeFirst()
Moves the cursor to just before the first row
2]afterLast()
Moves the cursor to just after the last row
3]first()
Moves the cursor to the first row
4]void last()
Moves the cursor to the last row.
5]absolute(int row)
Moves the cursor to the specified row
6]relative(int row)
Moves the cursor the given number of rows forward or backwards from where it currently is pointing.
7]boolean previous()
Moves the cursor to the previous row.
8]boolean next()
Moves the cursor to the next row. This method returns false if there are no more rows in the result set
9]public int getRow()
Returns the row number that the cursor is pointing to.
10]moveToInsertRow()
Moves the cursor to a special row in the result set that can be used to insert a new row into the database.
*/
import java.sql.*;
public class RetrieveRecords
{
public static void main(String args[]) throws Exception
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:ksreddyDSN”,”scott”,”tiger”);
Statement stmt = con.createStatement();

ResultSet res = stmt.executeQuery(“select * from employee”);
while(res.next())
{
System.out.println(res.getInt(1)+”\t” + res.getString(2) + “\t” + res.getDouble(3)+”\t”+res.getString(4));
}
res.close();
stmt.close();
con.close();
}
}
/* different ways
System.out.println(res.getInt(“eno”)+”\t” + res.getString(“ename”) + “\t” + res.getDouble(“esal”)+”\t”+res.getString(“eaddr”));
System.out.println(res.getString(“eno”)+”\t” + res.getString(“ename”) + “\t” + res.getString(“esal”)+”\t”+res.getString(“eaddr”));
System.out.println(res.getString(1)+”\t” + res.getString(2) + “\t” + res.getString(3)+”\t”+res.getString(4));
*/

program7:

import java.util.*;
import java.sql.*;
import java.io.*;
public class UpdatableResultDemo
{
public static void main(String[] args)throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from employee”);
Scanner sn= new Scanner(System.in);
rs.moveToInsertRow();
while(true)
{
System.out.println(“enter employee number”);
int eno = sn.nextInt();
System.out.println(“enter employee name”);
sn.nextLine();
String ename = sn.nextLine();
System.out.println(“enter employee salary”);
float esal = sn.nextFloat();
System.out.println(“enter employee address”);
sn.nextLine();
String eaddr = sn.nextLine();
rs.updateInt(1,eno);
rs.updateString(2,ename);
rs.updateFloat(3,esal);
rs.updateString(4,eaddr);
rs.insertRow();
System.out.println(“record successfully inserted”);
System.out.println(“one more record[y/n]”);
String option = sn.next();
if(option.equals(“n”))
break;
}}}

program8:

import java.sql.*;
public class UpdatableResultDemo1
{
public static void main(String[] args)throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con= DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from employee”);
rs.absolute(1);// move to first record
double sal = rs.getDouble(3);
System.out.println(sal);
sal = sal +500;
rs.updateDouble(3,sal);
rs.updateRow();
System.out.println(rs.getDouble(3));
} }

program9&10(sensitive and Insensitive)

import java.sql.*;
public class Sensitive
{
public static void main(String[] args)throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery(“select * from employee”);
rs.next();
System.out.println(“old salary o f the employee”+rs.getFloat(3));
System.in.read();
//application is in pause, perform database updations
rs.refreshRow();
System.out.println(“new salary of the employee”+rs.getFloat(3));
}
}
/* JDBC VERSIONS

java 1.1 JDBC 1.0
java 1.2 JDBC 2.0
java 1.3 JDBC 2.0
java 1.4 JDBC 3.0
java 1.5 JDBC 3.0
java 1.6 JDBC 4.0
java 1.7 JDBC 4.1
java 1.8 JDBC 4.2

• Scroll forward and backward in a result set or move to a specific row.
• Make updates to a database tables, using methods in the Java programming language instead of using SQL commands.
• Send multiple SQL statements to the database as a unit, or batch.
• Use the new SQL3 data types as column values

SQL 99 Datatypes(SQL 3 types)
Blob
Clob
Array
Struct
ref
———————————————–

Batch updates or batch processing:

A batch update is a set of multiple update statements that can be executed at a time as a batch on the database.

batch processing reduces network round trips,network trffic b/w client and db s/w.in the batch of sql queries we should not place select query.

In the batch of sql queries we should not place select query.
Batch updates can be made with Statement, PreparedStatement and CallableStatement objects.
Generally, for all SQL statements, by default, autocommit is called.
For executing batch statements, first we must set autocommit to false so that a transaction is not implicitly committed or rolled back.

The following code illustrates:

con.setAutoCommit(fasle);
Statement stmt = con.createStatement( );

stmt.addBatch(“insert into Employee values(100, ’ksreddy’, 2000.50)”);
stmt.addBatch(“insert into Employee values(101, ’3sha’, 4500.50)”);
stmt.addBatch(“insert into Employee values(102, ’9tara’, 3450.75)”);
stmt.executeBatch( );
con.setAutoCommit(true);

With addBatch( ) method, we can group all the SQL statements as a single lot. We can empty all the above insert statements from the Statement object stmt by calling clearBatch( ) method.

*/

program 11

import java.sql.*;
public class BatchUpdatesDemo
{
public static void main(String args[]) throws Exception
{
Connection con=null;
Statement st=null;
ResultSet rs=null;
try
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
con.setAutoCommit(false);
st=con.createStatement();
st.addBatch(“insert into employee values(111,’ram’,5550.89,’hyderabad’)”);
st.addBatch(“insert into employee values(112,’sam’,666.89,’secbad’)”);
st.addBatch(“delete from employee where eno=102”);
st.executeBatch();
con.commit();
}
catch(Exception e)
{
con.rollback();
}}}

program 12

/*
PreparedStatement
it is an interface available in java.sql package and extends Statement interface
PreparedStatement object represents the precompiled SQL statement. Whenever, the SQL statement is precompiled then it stored in the PreparedStatement object which executes the statement many times and it reduces the time duration of execution.

when we submit the sql statement using PreparedStatement object then the SQL statement will be compiled only once first time and precompiled sql statement will be executed every time.

totaltime=req time+compile time+execution time +response time
5ms+5ms+5ms+5ms=20ms
first time =1sql statement= 20 ms
next onwards=5ms+0ms+sms+5ms=15ms

PreparedStatement gives you the plcace holder mechanism for providing the data dynamic to the query. we use ? as place holder

public void setXXX(int paramindex, XXXvalue)
xxx can be int,String,long,float,Date

we can create the PreparedStatemetnt using the following methods of Connection inteface.
1]public PreparedStatement prepareStatement(String sql)
2]public PreparedStatement prepareStatement(String sql,int,int)
3]public PreparedStatement prepareStatement(String sql,int,int,int)

After creating the PreparedStatement we can use any of the following method to submit sql sttement

1]public int executeUpdate()
2]public boolean execute()
3]public ResultSet executeQuery()
*/
import java.sql.*;
import java.io.*;
import java.util.*;
public class PSDemo
{
public static void main(String args[])
{
try
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);

PreparedStatement pst=con.prepareStatement(“insert into Employee(eno,ename,esal,eaddr)values(?,?,?,?)”);

//? is the place holder for the data to be inserted dynamically later.
Scanner sn= new Scanner(System.in);

System.out.print(“Employee ID No:”);
int no = sn.nextInt();

System.out.print(“Employee name:”);
sn.nextLine();
String name = sn.nextLine();

System.out.print(“Employee Salary:”);
double sal = sn.nextDouble();
System.out.print(“Employee addr:”);
sn.nextLine();
String address =sn.nextLine();
pst.setInt(1,no);
pst.setString(2,name);
pst.setDouble(3,sal) ;
pst.setString(4,address) ;
pst.executeUpdate();
pst.close();
con.close();

}
catch(ClassNotFoundException e)
{
System.out.println(e);
}
catch(SQLException e)
{
System.out.println(e);
}
catch(Exception e)
{
System.out.println(e);
}
}}

program13(CallableStatement)

it is used to call procedures and function.

/*
create or replace function myfunction(i in number) return number as
begin
return i *100;
end;
/
sql>select myfunction(5) from dual;

500
*/
import java.sql.*;
public class CallableDemo1
{
public static void main(String args[])throws Exception
{
oracle.jdbc.driver.OracleDriver od=new oracle.jdbc.driver.OracleDriver();
Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
CallableStatement cst=con.prepareCall(“{?=call myfunction(?)}”);
// set input parameters
cst.setInt(2,5);
//2 represents 2nd question mark
//register output types
cst.registerOutParameter(1,Types.INTEGER);
cst.execute();
System.out.println(“Value returned by function =” + cst.getInt(1));
}}

program 14:

/*
Creating a procedure at the database:
create or replace procedure first_pro(x in number,y out number) as
begin
y := x*x;
end ;
/

SQL> var result number;
SQL> exec first_pro(10,:result);
SQL> print result;
100
*/
import java.sql.* ;
public class CallableDemo2
{
public static void main( String args[])throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
CallableStatement cst=con.prepareCall(“{CALL first_pro(?,?)}”);
cst.registerOutParameter(2,Types.INTEGER) ;
cst.setInt(1,10);
cst.execute();
//OUT parameter, the second place holder ? is be registered
int i=cst.getInt(2);
System.out.println(“Result is: ” +i);
}
}

program15: inserting a image into the database

/*
BLOB (binary large object)
it is datatype to store images like .gif or jpg into the database table
when we create a table to insert a image we need to declare the type as blob
create table emp(empno int,photo blob);

*/
import java.sql.*;
import java.io.*;
class BlobInsert
{
public static void main(String args[]) throws Exception
{
File f = new File(“t9.gif”);
FileInputStream fis =new FileInputStream(f);
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con
=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
PreparedStatement stmt = con.prepareStatement(“insert into emp values(?,?)”);
stmt.setInt(1,101);
stmt.setBinaryStream(2,fis,(int)f.length());
stmt.executeUpdate();
System.out.println(“image inserted”);
con.close();
}
}

sql>select * from emp;

empno photo

101 0010010132100100

we canot view images on sql command line

program 16: retrieving the image from database

/*
Blob:
it is a interface from java.sql which represents sql blob datatype value;
it contains the binary data.
we can get Blob object by using the method getBlob() of ResultSet.
Blob b=rs.getBlob();
*/
import java.sql.*;
import java.io.*;
class BlobGet
{
public static void main(String args[]) throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con
=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement stmt =con.createStatement();
ResultSet rs = stmt.executeQuery(“select * from emp”);
rs.next();
Blob b =rs.getBlob(2);
System.out.println(“picture is Retrived”);
FileOutputStream fos = new FileOutputStream(“h.gif”);
byte ab[]=b.getBytes(1,(int)b.length());
fos.write(ab);
con.close();
}}

program 17: inserting text file data into the database.

/*
clob:
it is a datatype in oracle to store text(a group of characters)
to insert a text file into the data base
create table text(col1 clob,col2 int);
insert into text(col2) values(100);
*/
import java.io.*;
import java.sql.*;
class ClobDemo
{
public static void main(String[] args) throws IOException,SQLException
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver( ));
Connection con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
PreparedStatement stmt = con.prepareStatement(“update text set col1 = ? where col2=100”);
File f= new File(“abc.txt”);
FileReader fr = new FileReader(f);
//store the file into col1 as character stream
stmt.setCharacterStream(1,fr,(int)f.length());
//display the file size
System.out.println(“FileSize”+f.length());
System.out.println(“NO of rows effected “+stmt.executeUpdate());
stmt.close();
con.close();
}}

program 18: retriveing the text from database and create a file

/*
Clob
it is a interface from java.sql which contains sql clob datatype value
getClob() of ResultSet returns the Clob object.
Clob c=rs.getClob();
use getCharacterStream () method to get the file data from the Clob object into the reader object
Reader r=c.getCharacterStream();
store the data from Reader into a new file with the name ba.txt

blob vs Blob
clob vs Clob

**/
import java.io.*;
import java.sql.*;
public class ClobGet
{
public static void main(String[] args) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver( ));
Connection con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement stmt=con.createStatement();
ResultSet rs =stmt.executeQuery(“select * from text”);
//go to first row
rs.next();
// read the data from the col1
Clob c = rs.getClob(“col1”);
//display file length
System.out.println(“file size=”+c.length());
//read file data from c and store into Reader object
Reader r=c.getCharacterStream();
// read and write tinto file
FileWriter fw= new FileWriter(“ba.txt”,true);
int ch;
while((ch=r.read()) != -1)
{
System.out.print((char)ch);
fw.write((char)ch);
}
fw.close();
con.close();
}}

NetBeans
->Type : IDE software for developing for java,j2ee &frame work software based applications
->version 7.0
->vendor sunmicrosystem(oracle)
->compatible jdk 1.5 and above
->opensource software
->Gives glass fish as in built server
->down load www.netbeans.org
psvm+tabspace
ctrl+shift+i–>package import
————————————————–
procedure to develop java application by using netbeans IDE to drop databse table.

step1: launch netbeans IDE
step2:create java project from netbeans IDE

filemenu->new project->java->java application->next->projectname ->deselect create main class–>select main project finish
step3: add java class to the project
right click on the project –>new –>java class->class name(Test)–>finish
step4: run the application
right click in the source code of file ->run file–>click on it

meta-data
———
data about data is called as meta data that means gathering much information about given information is nothing but meta-data.

in jdbc meta data programming is usefull to know limitations,capabilities and facilities of underlying database software and its resources.

jdbc supports 3 meta data programming concepts:
1]DatabaseMetaData
2]ResultSetMetaData
3]ParameterMetaData

DatabaseMetaData:
it is a interface in java.sql packagae
we can get this object by using getMetaData() of Connection

EX]DataBaseMetaData dbmd=con.getMetaData();

by invoking various methods on DatabaseMetaData object we can gather the details such has
1]Database name and version
2]support for PL/SQL procedures and functions
3]max characters in table name and column name etc

program19: //data base meta program
package com.configindia;
import java.io.*;
import java.sql.*;
public class DBcap
{
public static void main(String args[])throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
DatabaseMetaData dbmd =con.getMetaData();
System.out.println(“database name “+dbmd.getDatabaseProductName());
System.out.println(“database version “+dbmd.getDatabaseProductVersion());
System.out.println(“jdbc driver version “+dbmd.getDriverVersion());
System.out.println(“usesLocalFiles”+dbmd.usesLocalFiles());
System.out.println(“sql key words =”+dbmd.getSQLKeywords());
System.out.println(“numeric functions “+dbmd.getNumericFunctions());
}}
——————————————————————
ResultSetMetaData:–
it is a interface from java.sql
it gives varoius details about the database table that is represented by jdbc Resultset object like column name,column data types,column count.

ResultSet rs=st.executeQuery(“select * from student”);
ResultSetMetaData rsmd=rs.getMetaData();

program20:

package com.configindia;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class RSMD
{
public static void main( String args[]) throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement stmt=con.createStatement();
ResultSet rs =stmt.executeQuery(“select * from employee”);
ResultSetMetaData rsmd=rs.getMetaData();
// to print the column labels
for(int i=0;i<rsmd.getColumnCount( );i++)
System.out.print( rsmd.getColumnLabel(i+1)+”\t”);
System.out.println();

while(rs.next())
{
System.out.println(rs.getInt(1)+”\t”+rs.getString(2)+”\t”+ rs.getDouble(3)+”\t”+rs.getString(4));
}
rs.close();
stmt.close();
con.close();
}}
——————————————————-
ParameterMetaData:
It is a interface from java.sql
gives details about parameters available in sql queries of jdbc PreparedStatement and callableStatement

PreparedStatement ps=con.PrepareStatement(“insert into student values(?,?,?)”);
(? ? ? are parametrs of query)

ParameterMetaData pmd=ps.getParameterMetaData();

using pmd object we can gather the details of parameters like parameter name,type and other details.

if any method calls of MetaData programming returns 0 or null then we need to understand the underlying jdbc driver is not capable of gathering that information.

program21:

package com.configindia;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class PMData
{
public static void main( String args[]) throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
PreparedStatement ps=con.prepareStatement(“INSERT Into student VALUES(?, ?, ?)”);
ParameterMetaData pmd = ps.getParameterMetaData();
for (int i = 1; i < pmd.getParameterCount(); i++) {
System.out.println(“Parameter number ” + i);
System.out.println(“Class name is ” + pmd.getParameterClassName(i));
// Note: Mode relates to input, output or inout
System.out.println(” Mode is ” + pmd.getParameterMode(i));
System.out.println(” Type is ” + pmd.getParameterType(i));
System.out.println(” Type name is ” + pmd.getParameterTypeName(i));
System.out.println(” Precision is ” + pmd.getPrecision(i));
System.out.println(” Scale is ” + pmd.getScale(i));
System.out.println(” Nullable? is ” + pmd.isNullable(i));
System.out.println(” Signed? is ” + pmd.isSigned(i));

}}}

reading the data from properies file

db.properties

#db.properties
#type 4
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:xe
user=system
password=system

program 22(crud)

import java.sql.*;
import java.util.*;
import java.io.*;
public class PDemo
{
public static void main(String rags[])
{
try
{
Properties p= new Properties();
p.load(new FileInputStream(“db.properties”));

String driver=p.getProperty(“driver”);
String url=p.getProperty(“url”);
String user=p.getProperty(“user”);
String pass=p.getProperty(“password”);
Class.forName(driver);
Connection con=DriverManager.getConnection(url,user,pass);
Statement stmt=con.createStatement();
System.out.println(“statement object created”);
stmt.executeUpdate(“create table student(sno number,sname varchar2(30),saddr varchar2(45))”);
System.out.println(“table created”);
stmt.executeUpdate(“insert into student values(101,’ram’,’dsnr’)”);
stmt.executeUpdate(“insert into student values(102,’raj’,’koti’)”);
System.out.println(“records inserted”);
//process the result

ResultSet rs=stmt.executeQuery(“select * from student”);
while(rs.next()==true)
{
System.out.println(rs.getInt(1)+” “+rs.getString(2)+” “+rs.getString(3));
}
//close jdbc objects
rs.close();
stmt.close();
con.close();
}//try
catch(SQLException se)
{
System.out.println(“database problems”+se);
}
catch(ClassNotFoundException ce)
{
System.out.println(“class not available”+ce);
}
catch(Exception e)
{
System.out.println(e);
}
}//main
}//class
/*
advantage of using properties file is ,we can migrate from one driver to another driver with out effecting the appliaction logic
*/

==========================================

=====(jdbc 3.0 )=====================
1)multiple ResultSet
2)Savepoint interface
3)RowSet interface
4)ParameterMetaData interface
5)Connection pooling
1)MultipleResultSet:-
in jdbc 1.0 & jdbc 2.0 specifications it is possible to open a second ResultSet then automatically the first ResultSet will be closed.

In jdbc 3.0 it is possible to open more than one ResultSet and then we can work with them simultaneouly,by using CallableStatement

The Multiple ResultSet must be of type scrollable.it means we need to pass (type,mode) parameters,while creating a statement object.

In case of callableStatement object creation,we need to pass,the SQL command ,type & mode parameters to the prepareCall() method

Eg>
CallableStatement cst1=con.prepareCall(“select * from emp”,type,mode);
CallableStatement cst2=con.prepareCall(“select * from emp”,type,mode);

ResultSet rs1=cst1.executeQuery();
ResultSet rs2=cst2.executeQuery();

================================================
rowsets
1) RowSets are introduced in JDBC3.0
2) It comes with java 5 version
3) Difference between ResultSet and RowSet objects are
– RowSet can survive without database connection object (also called as disconnected ResultSet object) but ResultSet cannot survive with out database connection object.
– RowSet can be serialized where as ResultSet canot be Serialized.
– New approach was given to connect to DB.
setUrl(String url)
setUsername(String user)
setPassword(String pass)
setCommand(String command)
execute()
——————————————————————————
interface JdbcRowSet
{

}
class JdbcRowSetImpl implements JdbcRowSet
{

}

JDBC RowSet API is organized into three packages:
i) javax.sql, javax.sql.rowset package
ii)implementation classes are available in com.sun.jdbc.rowset package
===============================================
JdbcRowSet
Connected rowset object and it is similar to ResultSet
JdbcRowSet is scrollable and updatable
======================================
CachedRowSet
Disconnected rowset and it can survive without connection
it is Serializable, scrollable and updatable
========================================
WebRowSet
Web Row set can optionally generate XML documents
There are 2 classes available where WebRowSet canread and write on XML document.
a) WebRowSetXmlReader
b) WebRowSetXmlWriter
=======================================
JoinRowSet
This interface mechanism is used for combining data retrieved from different database objects.
====================================================
FilteredRowSet
it is an extension of a CachedRowSet object and a programmer use a filtered subset of data from a rowset.
=========================================
================================
program24:
import java.sql.*;
import javax.sql.*;
import javax.sql.rowset.*;
import com.sun.rowset.*;
import java.io.*;
public class JDBCRSDemo
{
public static void main(String rags[]) throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
JdbcRowSet jrs=new JdbcRowSetImpl();
jrs.setUrl(“jdbc:oracle:thin:@localhost:1521:xe”);
jrs.setUsername(“system”);
jrs.setPassword(“system”);
jrs.setCommand(“select * from employee”);
jrs.execute();
ResultSetMetaData meta=jrs.getMetaData();
int colCount=meta.getColumnCount();
while(jrs.next())
{
for(int i=1;i<=colCount;i++)
{
System.out.print(jrs.getString(i)+”\t”);
}
System.out.println();
}//while()
new ObjectOutputStream(new FileOutputStream(“temp.ser”)).writeObject(jrs);
}//main()
}//class
=============================
program25
import java.sql.*;
import javax.sql.*;
import javax.sql.rowset.*;
import com.sun.rowset.*;
import java.io.*;
public class CRSDemo
{
public static void main(String args[]) throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(“select *from employee”);
CachedRowSet crs = new CachedRowSetImpl();
crs.populate(rs); //get the data from ResultSet to CachedRowSet
con.close();//closing the connection

while(crs.next())
{
System.out.println(crs.getInt(1)+”\t”+crs.getString(2)+”\t”+crs.getDouble(3)+”\t”+crs.getString(4));
}
new ObjectOutputStream(new FileOutputStream(“temp.ser”)).writeObject(crs);
crs.close();
stmt.close();
}
}
====================================
program26:
import javax.sql.rowset.*;

import java.io.*;
public class CRSRestoreDemo
{
public static void main(String rags[]) throws Exception
{
FileInputStream fis= new FileInputStream(“temp.ser”);
ObjectInputStream ois=new ObjectInputStream(fis);
Object o=ois.readObject();
CachedRowSet crs=(CachedRowSet)o;
while(crs.previous())
{
System.out.println(crs.getString(1)+”\t”+crs.getString(2)+”\t”+crs.getString(3)+”\t”+crs.getString(4));
}// while()
}// main()
}// class
====================================================
program27:
import java.sql.*;
public class SavePointEx
{
public static void main(String[] args) throws Exception
{
Connection con=null;
try
{
con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
con.setAutoCommit(false);//disable auto commit
Statement st=con.createStatement();
st.executeUpdate(“insert into employee values(111,’raj’,8000,’dsnr’)”);
Savepoint sp=con.setSavepoint();
st.executeUpdate(“insert into employee values(222,’samuel’,7000,’koti’) “);
con.rollback(sp);
st.executeUpdate(“insert into employee values(333,’tom’,9000,’abids’)”);
con.commit();// all the savepoints are released
}
catch(Exception e)
{
con.rollback();
System.out.println(e);
}
}
}
===================================================
program 28
working with date values
create table wishes(sname varchar2(15),dob date);
import java.sql.*;
import java.io.*;
import java.text.*;
import java.util.*;
public class DateDemo
{
public static void main(String args[])throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con=DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
PreparedStatement pst = con.prepareStatement(“insert into wishes values(?,?)”);
Scanner sn= new Scanner(System.in);
System.out.println(“enter name”);
String s1=sn.nextLine();
sn.nextLine();
System.out.println(“enter Date of Birth”);
String s2 = sn.nextLine();
SimpleDateFormat sdf=new SimpleDateFormat(“dd-mm-yyyy”);
java.util.Date d1 =sdf.parse(s2);
long ms=d1.getTime();
java.sql.Date d2= new java.sql.Date(ms);
pst.setString(1,s1);
pst.setDate(2,d2) ;
pst.executeUpdate();
pst.close();
con.close();
}}
========================================
program29

/*
selecting date values :
when we select a date value from a database we get the date in database format.so we need to store sql date into java date object
*/

import java.sql.*;
import java.io.*;
import java.text.*;
import java.util.*;
public class DateDemo1
{
public static void main(String args[])throws Exception
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con=DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”,”system”,”system”);
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(“select dob from wishes where sname=’ram'”);
rs.next();
java.sql.Date d1=rs.getDate(1);
java.util.Date d2=(java.util.Date)d1;
SimpleDateFormat sdf=new SimpleDateFormat(“yyyy/mm/dd”);
String s=sdf.format(d2);
System.out.println(s);
rs.close();
con.close();
}}
==============================================

 

=============================================

database MYSQL
Developer(s) Oracle corporation
Stable release 5.6
Written in C, C++
Operating system [Cross-platform]
Type RDBMS(opensource)
Website www.mysql.com
dev.mysql.com
defaultportno 3306
defaultuser root/root

type4
——-
driverclass– com.mysql.jdbc.Driver
url——-jdbc:mysql://localhost:3306/mysql
jar file–mysql-connector-java-5.1.19-bin.jar
—————————————————–
mysql>show databases;
mysql> use mysql
Database changed
mysql>show tables;
mysql> create table student(sno int,name varchar(20));
———————————————–
package com.configindia.hyd;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class MySQLType4 {
public static void main(String args[]) {

try {
Class.forName(“com.mysql.jdbc.Driver”);
System.out.println(“Driver is loaded”);
Connection c = DriverManager.getConnection(“jdbc:mysql://localhost:3306/mysql”, “root”, “root”);
System.out.println(“Connection created”);
String s = “insert into student(sno,name) values (?,?)”;
PreparedStatement ps = c.prepareStatement(s);
ps.setInt(1, 101);
ps.setString(2, “ram”);
ps.execute();
c.close();
System.out.println(“Inserted”);
} catch (Exception e) {
System.out.println(“Exception : ” + e);

}
}

}