INTRODUCTION
Mr. Girraj Sharma(javaprofessional)
Director of JAVA PROFESSIONAL INDORE
=========================================================================
ADVANCE JAVA NOTE'S BY GIRRAJ SIR
=========================================================================
Agenda:
> Introduction
> Purpose
> Architecture
> Layer and Tier
> Jdbc Driver
Type:
1. Type1
2. Type2
3. Type3
4. Type4
> What is Connection?
Four way
> What is Statement?
> Statement
> PreperedStatement
> CallableStatement
> What is ResultSet (5)
> Metadata
> Database metadata
> ResultSet metadata
> Types of JDBC query
> execute()
> executeQuery()
> executeBatch()
> executeUpdate()
> ResultSet vs RowSet
> ConnectionPooling (Imp)
> Executing PL/SQL procedure or function
> How to store binary data?
Video
Audio
Image
> Resuource Bundle(Properties)
> Trasaction Management
...............
=========================================================================
JSE(JAVA STANDARD ED)-Core Java
> Stand Alone
> Desktop app
------------------------------------------------------------------------------------------------------------------
JEE(Java enterprise ed)
> client-server arch based network
> Web(website) app
> Enterprise app
> Distributed app
etc.......
-------------------------------------------------------------------------------------------------------------------
JME(Java Micro ed)
> mobile app
> small hand held app
etc
-------------------------------------------------------------------------------------------------------------------
JEE:
> jee is a specification
Sub-Specification of JEE:
Advance Java
> JDBC (Java Database Connectivity)-33%
> Servlet -33%
> JSP (Java Server Pages)-33%
> RMI (Remote Method Invokation/Calling)
> JNDI (Java Naming Directory Interface)
> EJB (Enterprise Java Beans)
> CORBA (Common object remote broker app)
> JTA (Java Transaction API)
> JPA (Java Persitance API)-ORM based framework
> JSF (Java Server Faces) - Web framework
> JMS (Java Messaging Services)
> Java Mail API
> Webservices
> etc
Advance Java:
1. JDBC
2. Servlet
3. JSP
JDBC:
> JDBC is a technology
> JDBC is an open specification supplied from sun/oracle
> JDBC stands for Java Database Connectivity
Overview of JDBC(Imp)
What is JDBC?
> jdbc is an api by using which java application can communicate with database server
> jdbc api specification written by using java code.
What is ODBC?
> ODBC stands for open database connectivity
> ODBC is an open specification supplied by x-open community/group
> By using ODBC driver any non-java application(.net,python,php) can communicate with database server.
What is the difference Layer and Tier?
What is layer?
Ans: Layer is a logical seperation of a project/application
What is Tier?
> Tier is a physical seperation of a project/application
What is JDBC?
> JDBC is an API by using which java program can deal with database server
Q: In which jar file contains JDBC specification?
> Since JDBC specification is a part of JSE module. Therefore its relation api classes and interfaces and packages are available inside rt.jar file.
Q: In which package containing JDBC specification related packages?
Ans:
> java.sql package
> javax.sql package
Important interface and classes of JDBC API:
------------------------------------------
Interface:
> Driver
> Connection
> Statement
> PreparedStatement
> CallableStatement
> ResultSet
> ResultSetMetaData
> DatabaseMetaData
Class:
> DriverManager
> Date
> Time
> Type
Steps for developing JDBC based java application:
======================================
1. Registering JDBC driver to the DriverManager service
2. Getting database Connection
3. Creating/preparing Statement
4. Executing SQL query
5. Processing ResultSet
6. Closing DB connection
What is driver?
> Driver is an interface which is part of JDBC api (java.sql)
> Any java class which is inherited from java.sql.Driver interface either directly or indirectly known as jdbc driver class.
> driver class is responsible for providing logics for creating database connection. These DB connection creation logics are available inside connect() method.
What is DriverManager?
> DriverManager is a class which is a part of jdbc specification because it is available inside java.sql package.
> This class will provides service methods to the jdbc driver
a. registering a driver
b. deregistering a deriver
c. getting connection
class DriverManager extends Object{
private static Driver driver=null;
................
public static void registerDriver(Driver driver){
this.driver = driver;
.......
}
public static Connection getConnection(....){
Connection con = driver.connect(...);
..............
return con;
}
}
> DriverManager members are declared as static.
> Its constructor is declared as private therefore DriverManager class cant be instantiated.
Steps of JDBC application in details:
 |
Step1: Registering JDBC driver object to the DriverManager class(service) by using registerDriver(Driver) method (5approach)
Type1: sun.jdbc.odbc.JdbcOdbcDriver (driver class)
import sun.jdbc.odbc.JdbcOdbcDriver;
.............
Driver driver=new JdbcOdbcDriver();
DriverManager.registerDriver(driver);
Step2: Getting database connection(4approach)
Connection con = driver.connect(url,properties(username,password));
Step3: creating statement by using Connection interface given createStatement() method
Statement st = con.createStatement();
Step4: executing SQL query
String query = "SELECT *FROM Dept";
ResultSet rs = st.executeXxx(query);
Step5: Processing ResultSet data
while(rs.next()){
.............
}
Step6: Closing connections (resources)
rs.close();
st.close();
con.close();
Developing first jdbc application using Type1 Driver?
Softare requirement
> install any jdk version prior to 1.8
> install oracle db (11g)
> configure odbc driver for oracle 11g
How to checking odbc driver list
> Control panel > Administrative Tool > ODBC data source> Drivers
> search: ODBC
How to configuring ODBC dsn for oracle database?
> Control panel > Administrative Tool > ODBC data source> system dsn tab
> add > OracleOraDB11g_home1> ok >
dsn name: oradsn
desc : ...
TNS ser : orcl
user ID : scott
> Click on Test Connection button to test the connection
> Enter database password > ok
> Writing source code for jdbc application by following given step
Program1: Type1OracleTestApp1.java
import java.sql.*;
import java.util.*;
class Type1OracleTestApp1{
public static void main(String[] args)throws Exception{
//step1: creating driver object
Driver d = new sun.jdbc.odbc.JdbcOdbcDriver();
//step2: registering driver
DriverManager.registerDriver(d);
//step3: getting connection
Properties p = new Properties();
p.setProperty("user","scott");
p.setProperty("password","tiger");
Connection con = d.connect("jdbc:odbc:oradsn",p);
if(con!=null)
System.out.println("connected to DB");
else
System.out.println("Failed to connect");
Statement st=con.createStatement();
String sql="INSERT INTO Employee VALUES(10,'VIKASH',99999)";
st.execute(sql);
System.out.println("Employee inserted successfully!");
}
}
/*
connect(): public Connection connect(String url,Properties p);
1. name of the driver class: JdbcOdbcDriver
2. DB url: jdbc:odbc:oradsn
3. DB username: scott
4. DB password: tiger
*/
Q: What are the required information for jdbc application to connecting with database?
a. driver name (Driver class name)
b. Database URL
c. Database username
d. Database password
Oracle:
Type1 information for oracle: (dsn: oradsn)
Driver Class: sun.jdbc.odbc.JdbcOdbcDriver
Database URL: jdbc:odbc:oradsn
Database username: scott
Database Password: tiger
Type2 information for oracle:
Driver Class: oracle.jdbc.driver.OracleDriver
Database URL: jdbc:oracle:oci:@localhost:1521:orcl
Database username: scott
Database Password: tiger
Type4 information for oracle:
Driver Class: oracle.jdbc.driver.OracleDriver
Database URL: jdbc:oracle:thin:@localhost:1521:orcl
Database username: scott
Database Password: tiger
Q: How many way of establishing database connection?
Q: How many way of supplying database information to the JDBC application?
Q: How many way of registering JDBC driver? (Imp)
We can register a jdbc driver by using registerDriver() method of DriverManager.
What is mean by registering driver?
> It means creating driver object and make available to DriverManager service by using registerDriver() method DriverManager class.
How to make available OracleDriver class?
> download ojdbc6.jar file
or
> copy ojdbc6.jar file from oraclehome\product\11.1.0\db_1\jdbc\lib\ojdbc6.jar to c:\temp
...ojdbc6.jar => c:\temp
> set classpath => c:\temp\ojdbc6.jar;%classpath%
or
copy ojdbc6.jar file inside C:\Java\jre6\lib\ext
ojdbc6.jar: oracle.jdbc.driver.OracleDriver
"C:\oracle11g\product\11.1.0\db_1\jdbc\lib\ojdbc6.jar"
>javap
Note: Every driver class implementation having one common property as static block
Approach1:
.......
JdbcOdbcDriver driver = new JdbcOdbcDriver();
DriverManager.registerDriver(driver); //type1
.........
OracleDriver driver=new OracleDriver();
DriverManager.registerDriver(driver); //oracle type4
............
class JdbcOdbcDriver .....{
........
static {
JdbcOdbcDriver driver = new JdbcOdbcDriver();
DriverManager.registerDriver(driver);
}
}
class OracleDriver ....{
........
static {
OracleDriver driver=new OracleDriver();
DriverManager.registerDriver(driver);
}
}
class Driver implements java.sql.Driver {
........
static {
Driver driver=new Driver();
DriverManager.registerDriver(driver);
}
}
> Every driver class will provides static block for registering current jdbc driver class to the driver manager service.
Limitation:
> In this process driver class instantiated twice and jdbc driver is also registered twice to the driver manager service which is useless.
DriverClass: sun.jdbc.JdbcOdbcDriver
Approach2:
JdbcOdbcDriver driver = new JdbcOdbcDriver();
> In thise case even though driver object is registered only once, but driver class instatiated twice, one extra driver object got created which not required.
Approach3:
> By extending jdbc driver class inside our jdbc application
> As we known java doesnt support multiple inheritance, if our jdbc application class developed by extending from JdbcOdbcDriver class than at same our application related class cant extends other necessary class
ie. Thread, Frame ...etc
Approach4: (Recommended)
> We can register jdbc driver class by using Class.forName(...) method.
How many way of establishing logical connection?
a. By using connect() method of Driver interface
Driver: public Connection connect(String url,Properties p);
b. By using getConnection() methods provided by DriverManager class
public static Connection getConnection(String url);
public static Connection getConnection(String url,Properties p);
public static Connection getConnection(String url,String un,String pass);
1. connect():
It is a method of Driver interface.
public Connection connect(String url,Properties p); p(user,password)
> Eventhough we can create jdbc connection using connect() method of Driver class directly but still it is not recommended to call connect() method directly. Because if JDBC driver class is registered with the help of static block (forName()) then this static block created driver object is not available to our application. In order to call connect() we should create Driver object explicitly.
class DriverManager ...{
private static Driver driver=null;
...........
public static void registerDriver(Driver d){
driver = d;
}
public Connection getConnection(...){
............
if(driver!=null)
return driver.connect(...);
else
return null;
}
}
Note: It is recommended to establish db connection using getConnection() method of DriverManager class.
2.
Case1: getConnection(String url):
public static Connection getConnection(String url);
Type1: any db
Connection con=DriverManager.getConnection("jdbc:odbc:dsn");
Type4: Oracle: oracle.jdbc.driver.OracleDriver
String url="jdbc:odbc:thin:scott/tiger@localhost:1521:orcl";
Connection con=DriverManager.getConnection(url);
case2:
public static Connection getConnection(String url,String un,String pwd);
String url="jdbc:odbc:oradsn";
String un="scott",pass="tiger";
Connection con=DriverManager.getConnection(url,un,pass);
> If database information(driver class,url,username,password) will not comming from a properties file then this method is recommended.
Case3: (recommended)
public static Connection getConnection(String url,Properties p);
How many way of gethering database details in our JDBC application?
a. using hard code details or statically.
b. Using Scanner class methods
c. Using command line argument
import java.sql.*;
import java.util.*;
class DBInfoType3 {
public static void main(String[] args)throws Exception {
//step1: collecting db details
String driver = args[0];
String url= args[1];
String user = args[2];
String pass = args[3];
//step2: registering jdbc driver
Class.forName(driver);
//step3: getting connection
Connection con = DriverManager.getConnection(url,user,pass);
if(con!=null)
System.out.println("Success");
else
System.out.println("Failed");
}
}
recommended:
d. using environment variable
e. using properties file
f. using xml file
Type4: OracleDriver
DB Info:
driver = oracle.jdbc.driver.OracleDriver (oracle: ojdbc6.jar)
url = jdbc:oracle:thin:@localhost:1521:orcl
user = scott
pass = tiger
Note: copy ojdbc6.jar file inside c:\temp folder and set the classpath of that jar file.
>set classpath=c:\temp\ojdbc6.jar;%classpath%;
Program1: supplying jdbc information statically (hard code logics)
import java.sql.*;
class DBInfoType1 {
public static void main(String[] args)throws Exception {
//step1: collecting db details
String driver = "oracle.jdbc.driver.OracleDriver";
String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "scott";
String pass = "tiger";
//step2: registering jdbc driver
Class.forName(driver);
//step3: getting connection
Connection con = DriverManager.getConnection(url,user,pass);
if(con!=null)
System.out.println("Success");
else
System.out.println("Failed");
}
}
//setting classpath of ojdbc6.jar
// set classpath=c:\temp\ojdbc6.jar;%classpath%
Note: eventhough we can supply database dynamically by using methods of scanner class or command line argument but still it is not recommended. Because in this we have to supply database details everytime for each executions.
import java.sql.*;
import java.util.*;
class DBInfoType2 {
public static void main(String[] args)throws Exception {
Scanner sc = new Scanner(System.in);
//step1: collecting db details
System.out.println("Enter driver");
String driver = sc.nextLine();
System.out.println("Enter db URL");
String url= sc.nextLine();
System.out.println("Enter db username");
String user = sc.nextLine();
System.out.println("Enter db password");
String pass = sc.nextLine();
//step2: registering jdbc driver
Class.forName(driver);
//step3: getting connection
Connection con = DriverManager.getConnection(url,user,pass);
if(con!=null)
System.out.println("Success");
else
System.out.println("Failed");
}
}
Approach4:
> By using environment variables.
Example: How to access value of environment variables inside java application.
System:
public static String getenv(String vname);
String x = System.getenv("var name")
Write a JDBC application that will takes database details from the environment variable list
Step1: create four new environment variables with given name and value
varname varvalue
-------------------------
driver = oracle.jdbc.driver.OracleDriver
url = jdbc:oracle:thin:@localhost:1521:orcl
dbuser = scott
dbpass = tiger
class EnvDemo1
{
public static void main(String[] args){
String x=System.getenv("path");
System.out.println(x);
}
}
Step2: create jdbc application and gethering database details from environment variables (ref: DBInfoType4.java)
Step3: Restart your pc (For shake of environment variable updation)
Step4: set classpath of oracle type4 jdbc driver (ojdbc6.jar file
set classpath=c:\temp\ojdbc6.jar;%classpath%
Step5: compile above jdbc appl
>javac DBInfoType4.java
Step6: execute it
>java DBInfoType4
import java.sql.*;
import java.util.*;
class DBInfoType4 {
public static void main(String[] args)throws Exception {
//step1: collecting db details: driver,url,dbuser,dbpass
String driver = System.getenv("driver");
String url= System.getenv("url");
String user = System.getenv("dbuser");
String pass = System.getenv("dbpass");
System.out.println(driver);
System.out.println(url);
System.out.println(user);
System.out.println(pass);
//step2: registering jdbc driver
Class.forName(driver);
//step3: getting connection
Connection con = DriverManager.getConnection(url,user,pass);
if(con!=null)
System.out.println("Success");
else
System.out.println("Failed");
}
}
Note: The process of creating environment variables may worry os to os
Note: To update/create the environment variable we need to contact machine physically.
Approach5:
We can supply database details dynamically using properties file (resource bundle).
notepad: *.prop, *.properties
java.util.Properties
load() :
store():
setProperty():
getProperty():
file =>(load)=> (object)
object =>(store)=>file
dbinfo.properties
------------------------------------------
key=value
x = 10
y = 20
#comments
> We can supply database details dynamically by using properties file.
> Properties is also known as resource bundle.
Steps for supplying db details to the jdbc application using properties file
Step1: create new properties file as named
dbinfo.properties
Step2: copy required database details inside properties file
driver=oracle.jdbc.driver.OracleDriver
url = jdbc:oracle:thin:@localhost:1521:orcl
user = scott
password = tiger
//save
Step3: create jdbc logic based java application
DBInfoType5.java
Things to be know:
class DriverManager ....{
private static Driver d=null;
public static void registerDriver(Driver d){
DriverManager.d = d;
}
...........
public static Connection getConnection(String url,Properties p){
Connection con=d.connect(url,p);
return con
}
public static Connection getConnection(String url,String un,String pass){
Properties p = new Properties();
p.setProperty("user",un);
p.setProperty("password",pass);
Connection con=d.connect(url,p);
return con;
}
}
class OracleDriver extetnds Object implements java.sql.Driver{
public Connection connect(String url,Properties p){
.............
String uname = p.getProperty("user");
String pass = p.getProperty("password");
..........
}
}
-------------------------------------------------------------------------------------------------------------------Properties file: DBinfoPropertires.prop
#This file contains oracle type4 driver details
driver=oracle.jdbc.driver.OracleDriver
url = jdbc:oracle:thin:@localhost:1521:orcl
user = scott
password = tiger
-------------------------------------------------------------------------------------------------------------------
What is the required database details for connecting jdbc appl to the MYSQL server using type4 driver?
Required jar file:
mysql-connector-java-5.1.48.jar
mysql.jar
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/test
username = root
password = test
Note: Download mysql.jar file and setting classpath
Link: https://jar-download.com/artifacts/mysql/mysql-connector-java
> copy downloaded mysql.jar file inside c:\temp\mysql.jar
> set classpath=c:\temp\mysql.jar;%classpath%
==========================vikashkarma===========================
More about jdbc Statement interface:
> Statement acts like vehicle between java application and database server
> statement is responsible for translating JAVA objects(query) into actual SQL format, and it will also sends java sql query from java appl to database server.
> it will also collects results from a database server and converts this result in the form of appropriate java objects and send back to the java application in the form of ResultSet.
Types of Statement:
> there are three types of statement interface available in jdbc api
a. Statement (i) => SQL
b. PreparedStatement (i) => SQL
c. CallableStatement (i) => SQL + PL/SQL
DB(SQL + PL)
About Statement interface:
> Statement is an api interface
How to retrieving/getting Statement object?
Ans: Working with Statement means, indirectly we are working with its implementated class object.
> We can create a statement object by using createStatement() method provided by Connection interface.
public Statement createStatement(); => Connection
Statement st=con.createStatement();
According to SQL specification sql queries are classified as given:
> DDL-CREATE,TRUNCATE,ALTER,RENAME...
> DML-INSERT,UPDATE,DELTE
> DCL-GRANT,REVOKE
> TCL-COMMIT,ROLLBACK,SAVEPOINT
> DRL-SELECT
According to JDBC specification there are two types of sql queries
a. select query (DRL)
b. non-select query (DDL,DML,TCL,DCL)
Important methods of Statement interface:
1sql> executeUpdate(): Non-select query (DML-INSERT,UPDATE,DELETE)
1sql> executeQuery() : SELECT query
1sql> execute() : Any type of SQL query (SELECT + NON-SELECT)
n-sql> executeBatch() : (DML)
Statment:
executeUpdate() method:
> public int executeUpdate(String sql);
Ex:
String qry = "INSERT INTO........";
st.executeUpdate(qry);
> By using this method we can execute any non-select sql statement/query.
Recommended to execute only DML operation.
> On success this method will returns number of affected row in the table.
> It will takes one String type of argument as a SQL query.
By Default database:Driver - Oracle: Type4
Program: create a Student(sno,sname,fee) table using JDBC application?
Query: CREATE TABLE Student
(sno NUMBER(4) PRIMARY KEY,sname VARCHAR2(10),fee NUMBER(5))
Step1: Gethering database details
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String uname="scott";
String pass="tiger";
Step2: registering jdbc driver
Class.forName(driver);
Step3: getting DB connection
Connection con = DriverManager.getConnection(url,uname,pass);
Step4: creating statement
Statement st = con.createStatement();
Step5: executing sql query
String query = "CREATE TABLE Student(sno NUMBER(4) PRIMARY KEY,sname VARCHAR2(10),fee NUMBER(5))";
int count = st.executeUpdate(query);
if(count==0)
System.out.println("Student registered successfully!");
else
System.out.println("Failed to register!");
Step6: closing resources (Connection,Statement,ResultSet)
> classing all resource in their reverse order
st.close();
con.close();
--------------------------------------------vikashkarma-------------------------------------------------------
> Download mysql-connector-java-5.1.48.jar file from given url
https://jar-download.com/artifacts/mysql/mysql-connector-java
> copy above jar file inside c:\temp and set classpath
set classpath=c:\temp\mysql-connector-java-5.1.48.jar;%classpath%
or copy this jar file inside c:\java\jdk6\lib\ext
> compile
> run
-------------------------------------------------------------------------------------------------------------------
Verify mysql database:
> Open MYSQL prompt
> Enter password: test
> use test
Program: Create table student inside mysql:
import java.sql.*;
class MysqlConCreateTest{
public static void main(String[] args)throws Exception{
//Step1: Gethering database details
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/test";
String uname="root";
String pass="test";
//Step2: registering jdbc driver
Class.forName(driver);
//Step3: getting DB connection
Connection con = DriverManager.getConnection(url,uname,pass);
//Step4: creating statement
Statement st = con.createStatement();
//Step5: executing sql query
String query = "CREATE TABLE Student(sno int PRIMARY KEY,sname VARCHAR(10),fee int)";
int count = st.executeUpdate(query);
if(count==0)
System.out.println("Student table created successfully!");
else
System.out.println("Failed to create table!");
/*Step6: closing resources (Connection,Statement,ResultSet)
> classing all resource in their reverse order */
st.close();
con.close();
}
}
//set classpath=c:\temp\ojdbc6.jar;%classpath%
------------------------------------------vikashkarma-------------------------------------------------------
Program: insert into student details inside mysql or oracle
Using Oracle dataBase:-
import java.sql.*;
class InsertStudentOracleDemo1{
public static void main(String[] args)throws Exception{
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String uname="scott";
String pass="tiger";
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uname,pass);
Statement st = con.createStatement();
String query;
query= "INSERT INTO Student VALUES(102,'NITIN',24000)";
int count = st.executeUpdate(query);
query= "INSERT INTO Student VALUES(101,'SHUBHAM',22000)";
count = count + st.executeUpdate(query);
System.out.println(count+" row's affected");
st.close();
con.close();
}
}
-------------------------------------------------------------------------------------------------------------------
B.using mysql Database:-
import java.sql.*;
class InsertStudentMysqlDemo1{
//102,NITIN,24000
public static void main(String[] args)throws Exception{
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/test";
String uname="root";
String pass="test";
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uname,pass);
Statement st = con.createStatement();
String query = "INSERT INTO Student VALUES(102,'NITIN',24000)";
int count = st.executeUpdate(query);
System.out.println(count+" row's affected");
st.close();
con.close();
}
}
-------------------------------------------------------------------------------------------------------------------
Inserting Dynamically In oracle DataBase:-
dbinfo.prop:-
driver=oracle.jdbc.Driver.OracleDriver
url=jdbc:oracle:thin:@localHost:1521:orcl
uname=system
pass=Tiger123
//java InsertStudentOracleDemo2 sno sname fee
import java.util.*;
import java.io.*;
import java.sql.*;
class InsertStudentOracleDemo2{
public static void main(String[] args)throws Exception{
Properties p = new Properties();
p.load(new FileInputStream("dbinfo.prop"));
String driver=p.getProperty("driver");
String url=p.getProperty("url");
String uname=p.getProperty("uname");
String pass=p.getProperty("pass");
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uname,pass);
Statement st = con.createStatement();
String query,sno=args[0],sname=args[1],fee=args[2];
query= "INSERT INTO Student VALUES("+sno+",'"+sname+"',"+fee+")";
int count = st.executeUpdate(query);
System.out.println(count+" row's affected");
st.close();
con.close();
}
}
-------------------------------------------------------------------------------------------------------------------
Inserting Dynamically In Mysql DataBase:-
dbinfo.prop
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
uname=root
pass=test
Program to insert data in mysql Table dynamically
//java InsertStudentOracleDemo2 sno sname fee
import java.util.*;
import java.io.*;
import java.sql.*;
class InsertStudentOracleDemo2{
public static void main(String[] args)throws Exception{
Properties p = new Properties();
p.load(new FileInputStream("dbinfo.prop"));
String driver=p.getProperty("driver");
String url=p.getProperty("url");
String uname=p.getProperty("uname");
String pass=p.getProperty("pass");
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uname,pass);
Statement st = con.createStatement();
String query,sno=args[0],sname=args[1],fee=args[2];
query= "INSERT INTO Student VALUES("+sno+",'"+sname+"',"+fee+")";
int count = st.executeUpdate(query);
System.out.println(count+" row's affected");
st.close();
con.close();
}
}
-------------------------------------------------------------------------------------------------------------------
CURD/CRUD
C => CREATE=> INSERT
U => UPDATE=> UPDATE
R => READ => SELECT
D => DELETE=> DELETE
How to execute SELECT statement?
> We can execute SELECT Statement using executeQuery() method provided by Statement interface
executeQuery():
> public ResultSet executeQuery(String sql);
> It will takes select statement as a string object.
> It will returns whatever results generated by the database server by storing inside ResultSet object.
Program: demo program for executing SELECT statement.
> Create a student table
sno,sname
1 AAA
2 BBB
3 CCC
CREATE TABLE Student(SNO NUMBER(5) PRIMARY KEY,SNAME VARCHAR2(10))
INSERT INTO Student VALUES(1,'AAA');
INSERT INTO Student VALUES(2,'BBB');
INSERT INTO Student VALUES(3,'CCC');
---------------------------------------------------
import java.sql.*;
class SelectDemo1 {
public static void main(String[] args)throws Exception{
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String uname = "scott";
String pass = "tiger";
Class.forName(driver);
Connection con;
con = DriverManager.getConnection(url,uname,pass);
Statement st = con.createStatement();
String sql = "SELECT *FROM Student";
ResultSet rs = st.executeQuery(sql);
String sno,sname;
while(rs.next()){
sno = rs.getString(1);
sname = rs.getString(2);
System.out.println(sno+","+sname);
}
}
}
-----------------------------------------------vikash karma --------------------------------------------------
What is IDE?
> IDE stands for Integrated Development Environment.
> It will provides s/w tools for developing any type of java project
a. core java
b. website
c. ejb
d. struts
e. sptring
f. hibernate
There three popular IDE of java
a. Netbeans - Open Source (recommended)
b. Eclipse - Open Source
c. MyEclipse - Commercial
How to install myecplipse 10?
Tips:
> make empty temp and prefetch directories
Double Click IDE given *.exe file: myeclipse-10.0-offline-installer-windows.exe
> next
> checked agreement process
> next
> change default location: c:\myeclipse10
> next
> next
> Finish
> Change default location of workspace: C:\Workspaces10
And select this workspace as default by click on checked button
> Ok
Workspace:
> A place where all myecplise projects are saved
What is views?
> views are small small windows in myeclipse IDE which will provides an environment related to current project.
What is perspective?
> Perspectives are collection of project specific related views.
Developing HelloWorld appl using myeclipse
> Create new project: File> New > Java Project
or
Right empty place of package explorer view>new>Java project
> Enter Project name: HelloWorldDemo
> next>finish
> Create new class
> select src folder inside HelloWorldDemo project
> right click > new > class > Name...
Short cut:
> ctrl+m: To maximize and minimize the selected view
> syso=> ctrl+space: To include sop();
> To open source code: Hold Ctrl key and click on required method/class name.
> Alt+ <= : To navigate in left/previous window
> Alt+ => : To navigate in right/next windown
> ctrl+w : To close current editor file
> ctrl+shift+w: To class all opened file
> ctrl + shift + o: To organize the required package.
> ctrl + D: To delete current statement/line
> ctrl + L: To reach the specified line.
> ctrl + f8: To navigate b/w multipal perspective
> ctrl + f7: To navigate b/w multipal view
> ctrl + f6: To navigate b/w multipal files opened in the editor
> ctrl + shift +f: To format the source code format(text alignments)
> ctrl+shift+/ : To comment the selected code
> ctrl+shift+\ : To uncomment the selected code
How to create jdbc application using myeclipse connecting oracle db by type4 driver?
Required Jar: ojdbc6.jar
How to using third party given jar files inside myeclipse ide?
step1: create new folder inside our project
name: lib (recommended)
step2: copy required jar files and past inside project given lib folder.
Step3: add to build path newly added jar file
right click on the jar file> Build Path > Add to build path
.......remaining steps are same as notepad
How to gethering db details from a properties file using IDE?
> create a properties file inside source folder, It is highly recommended to create seperate source folder for resource bundles
> create source folder Name: config
> inside config folder create oracleinfo.properties file, and copy oracle type4 driver details inside this properties.
right click on config folder> new > file
name: oracleinfo.properties
> To add new properties open this properties file in source view.
> create java source file....same as previous application
---------------------------------------------vikash karma-----------------------------------------------------
int executeUpdate(): non-select
ResultSet executeQuery(): select
# execute():
sign: public boolean execute(String sqlquery)
> By using this method we can execute any type(select+non select) of sql statement.
true=> select => result set => st.getResultSet()
false => non-select => affected row => st.getUpdateCount()
> If currently select statement being executed by this method then it will returns true. In case of non-select statement this method will returns false.
Ex:
boolean status = st.execute(query);
if(status==true){
//select
ResultSet rs = st.getResultSet(); //executeQuery()
.............
}
else{
//non-select
int count = st.getUpdateCount(); //executeUpdate()
System.out.println(count+" row affected");
}
getUpdateCount(): Statement
> int getUpdateCount():
> it will returns number affected row,
> By using this method we can the information about updated row at any time.
> It is highly recommended to call getUpdateCount() method only after executing non-select statement.
int count = st.getUpdateCount();
getResultSet(): Statement
> ResultSet getResultSet();
> By using this method we can get the ResultSet object which is generated by previous executeXxx() method at any time(on demand).
Project03: demo application for testing behaviour of execute() method.
package edu.jdbc;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
public class ExecuteDemo {
public static void main(String[] args) {
inputStream in =ExecuteDemo.class.getClassLoader().getResourceAsStream("oracleinfo.properties");
Properties p = new Properties();
String driver = "", url = "", query = "";
Connection con = null;
Statement st = null;
ResultSet rs = null;
Scanner sc = new Scanner(System.in);
try {
p.load(in);
System.out.println(p);
driver = p.getProperty("driver");
url = p.getProperty("url");
Class.forName(driver);
con = DriverManager.getConnection(url, p);
st = con.createStatement();
} catch (Exception e) {
}
while (true) {
try {
System.out.print("SQL> ");
query = sc.nextLine();
if("exit".equalsIgnoreCase(query))
break;
if (st.execute(query)) {
rs = st.getResultSet();
String c1, c2;
System.out.printf("%15s %15s\n", "Column1", "Column2");
System.out.println(".............................................");
while (rs.next()) {
c1 = rs.getString(1);
c2 = rs.getString(2);
System.out.printf("%15s %15s\n", c1, c2);
}
} else {
int count = st.getUpdateCount();
System.out.println(count + " row's affected");
}
} catch (Exception e) {
System.out.println("INVALID SQL Statement"+e);
}
}
}
}
==============================================================
Statement:
executeUpdate(): Non-select
executeQuery(): Select
execute(): [Select(true)-st.getRSet()] + [Non-select(false-st.getUpdCount())]
executeBatch():
1. insert
2. update
3. delete
What is the limitation of Statement?
Voter
-----------------
voterid name age
101 mohit 20
INSERT INTO Voter VALUES(id,name,age); 100Cr
-------------------------------------------------------------------------------------------------------------------
Query exicution prosess:-
-------------------------------------------------------------------------------------------------------------------
Diffrence between Statement and PreparedStatement:-
-------------------------------------------------------------------------------------------------------------------
> poor performance, because in case query parsing and query compilation process got repeated everytime eventhough we are executing same query multiple.
> It may leads sql injection attack problem
> By using statement we cant execute dynamic query.
We can overcome above problem by using PreparedStatement.
PreparedStatement:
> PreparedStatement st =con.prepareStatement(query);
>
Case: using Statement
Statement st = con.createStatement();
for(int i=0;i<100;i++){
...............
same query with different input data
...................
st.executeUpdate(query);//parsing(n)+compilation(n)+execution(n)
}
Case: using PreparedStatement
PreparedStatement ps = con.prepareStatement(query);//parsing(1)+compilation(1)
for(int i=0;i<100;i++){
...............
same query with different input data
...................
ps.executeUpdate(); //execution: everytime
}
What steps are followed by PreparedStatement to execute a sql query?
Consider: inserting student details inside student table(sno,sname,fee)
sno sname fee
101 PINTU 0
Step1: Preparing sql query using place holders
String query = "INSERT INTO Student VALUES(?,?,?)";
Step2: preparing object of PreparedStatement
PreparedStatement ps = con.prepareStatement(query);
//setXxx(int index,Xxx value); //Xxx means column type
Step3: setting data/value for the place holders by using their index(1 is begining)
ps.setInt(1,101);
ps.setString(2,"MINAL");
ps.setString(3,"22000");
//INSERT INTO Student VALUES(101,'MINAL','22000')
Step4: execute sql query by using suitable executeXxx() method
ps.execute();
-------------------------------------vikash karma------------------------------------------
What is the difference b/w Statement and PreparedStatement?
Statement PreparedStatement
-------------------------------------------------------
Low performance High Performance
non-repeated query repeated query
High network traffic Low network traffic
only static query static + dynamic query
sql injection attack not possible
is possible
static query:
SELECT *FROM Emp WHERE Ename='KING'
dynamic query:
SELECT *FROM Emp WHERE Ename=?
Place holder or Parameter:
SELECT *FROM Login WHERE uname = ? AND pass = ? //position: starts with 1
SELECT *FROM Login WHERE uname = :name AND pass = :pwd
SELECT ?,? FROM ? WHERE id = ?; invalid
> positional parameters can be used only in place of values, we cant replace keyword, column name, table name, operator by the positional parameters
? *FROM Emp //invalid
SELECT *FROM ? // invalid
SELECT *FROM Emp WHERE id ? '101'; //INVALID
Aggregate function/Group function/multi row function:
CREATE TABLE Student
(
name VARCHAR2(10),
fee NUMBER(5)
)
NAME FEE
---------- ----------
AAA 1000
BBB 2000
CCC 3000
DDD 4000
AAA 500
AAA 5500
BBB 2500
-----------------------------------
Demo program:
package edu.jdbc;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
public class ExecuteDemo {
public static void main(String[] args) {
InputStream in; in=ExecuteDemo.class.getClassLoader().getResourceAsStream("oracleinfo.properties");
Properties p = new Properties();
String driver = "", url = "", query = "";
Connection con = null;
Statement st = null;
ResultSet rs = null;
Scanner sc = new Scanner(System.in);
try {
p.load(in);
System.out.println(p);
driver = p.getProperty("driver");
url = p.getProperty("url");
Class.forName(driver);
con = DriverManager.getConnection(url, p);
st = con.createStatement();
} catch (Exception e) {
}
while (true) {
try {
System.out.print("SQL> ");
query = sc.nextLine();
if("exit".equalsIgnoreCase(query))
break;
if (st.execute(query)) {
rs = st.getResultSet();
String c1, c2;
System.out.printf("%15s %15s\n","Column1","Column2");
System.out.println(".............................................");
while (rs.next()) {
c1 = rs.getString(1);
c2 = rs.getString(2);
System.out.printf("%15s %15s\n", c1, c2);
}
} else {
int count = st.getUpdateCount();
System.out.println(count + " row's affected");
}
} catch (Exception e) {
System.out.println("INVALID SQL Statement"+e);
}
}
}
}
-------------------------------------------------------------------------------------------------------------
Statement Demo program:
package org.jit.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class STDemo {
public static void main(String[] args) throws Exception {
Scanner sc=new Scanner(System.in);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
Statement st = con.createStatement();
String sno,sname;
System.out.print("Enter Id: ");
sno = sc.nextLine();
System.out.print("Enter Pass: ");
sname = sc.nextLine();
String query = "SELECT *FROM Student WHERE sno = '"+sno+"' AND sname = '"+sname+"'";
System.out.println("SQL> "+query);
ResultSet rs = st.executeQuery(query);
if(rs.next()==true)
System.out.println("Login successfully, enjoy browsing your account");
else
System.out.println("Login failed due invalid credential!");
rs.close();
st.close();
con.close();
}
}
--------------------------------------------------------------------------------------------------------------------------------
What is metadata?(imp)
> In jdbc metadata is an information about
database
result set
parameters
Types of metadata (interface):
1. DatabaseMetaData
2. ResultSetMetaData
3. ParameterMetaData
What is DatabaseMetaData?
> DBMD is an api interface.
> this interface object will provides metadata information about connected database and jdbc driver
ex: name,version,tables,
DatabaseMetaData dbmd = con.getMetaData();
getDatabaseProductName();
getDatabaseProductVersion();
Connection con1 = DM.getConnection(oracle);//Connection + DatabaseMetaData(oracle);
Connection con2 = DM.getConnection(mysql);//Connection + DatabaseMetaData(mysql);
--------------------
PROGRAM FOR TEST:
package com.edu;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.util.Properties;
public class Test {
public static void main(String[] args)throws Exception {
Properties p = new Properties();
p.load(Test.class.getClassLoader().getResourceAsStream("mysqlinfo.properties"));
String driver = p.getProperty("driver");
String url = p.getProperty("url");
Class.forName(driver);
Connection con = DriverManager.getConnection(url,p);
DatabaseMetaData md = con.getMetaData();
String name = md.getDatabaseProductName();
String version = md.getDatabaseProductVersion();
System.out.println("Connected to "+name);
System.out.println("Current version: "+version);
}
}
Working with ResultSetMetaData (interface):
>ResultSet rs = st.executeQuery(select); //ResultSet + ResultSetMetaData
ResultSet: Row and Column values
RSMD: Metadata
>SELECT : => RS
>DESC Emp: => RSMD (total number of columns, name,size...)
> ResultSetMetaData is an API interface
> Along with ResultSet object executeXxx() methods also creates ResultSetMetaData object.
> ResultSet object will stores database table data. ResultSet object never stores size of the column, name of the column, number of selected columns.
> To store number of selected columns, columns names, column size information their is seperate object is created of ResultSetMetaData type.
How to get RSMD object?
ans: By using getMetaData() method of ResultSet
RSMD m = rs.getMetaData();
Methods of RSMD:
> getColumnCount(): It will returns number of selected columns.
> getColumnName(): It will returns name of the column
md.getColumnName(1);
How to insert/read binary data?
LOB: (4GB)
BLOB(Binary Large Object): image,video,audio,pdf...
CLOB(Character Large Object): doc(text)
NCLOB(National Character Large Object):
Table: Voter(vid: VARCHAR2(10),name: VARACHAR2(20),photo BLOB)
CREATE TABLE Voter
(
vid NUMBER(5) PRIMARY KEY,
vname VARCHAR2(10),
photo BLOB
)
> create a jdbc project
> We cant handle binary data by using Statement interface methods. To handle binary data we should use PreparedStatement.
Because binary can be processed only by using dynamic query.
c:\temp\pintu.jpeg (input stream)
String query = "INSERT INTO Voter VALUES(?,?,?)";
PreparedStatement ps = con.prepareStatement(query);
int id = sc.nextInt(); //sc.nextLine();
String name = sc.nextLine();
FileInputStream in = new FileInputStream("c:\\temp\\pintu.jpeg");
File f = new File("c:\\temp\\pintu.jpeg");
ps.setInt(1,id);
ps.setString(2,name);
ps.setBinaryStream(3,in,f.length());
-------------------------------------------------------------------------------------------------------------------
progrma for insert image in dataBase:-
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class InsertImageDemo {
public static void main(String[] args) throws Exception {
File f = new File("c:\\temp\\a.png");
FileInputStream in = new FileInputStream(f);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager
.getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:orcl");
String query = "INSERT INTO Voter VALUES(?,?,?)";
PreparedStatement ps = con.prepareStatement(query);
ps.setInt(1, 101);
ps.setString(2, "MOHIT");
ps.setBinaryStream(3, in, (int)f.length());
int count = ps.executeUpdate();
System.out.println(count+" row inserted");
con.close();
}
}
----------------------------------------------------------------------------------------------------------------------
program for Read image file in database:-
package edu.std;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class ReadImageDemo {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager .getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:orcl");
String query = "SELECT *FROM Voter";
PreparedStatement ps = con.prepareStatement(query);
ResultSet rs = ps.executeQuery();
rs.next();
System.out.println("Your id is "+rs.getInt(1));
System.out.println("Your name is "+rs.getString(2));
System.out.println("Photo saved successfully(c:\\temp\\downimage.png)");
InputStream in = rs.getBinaryStream(3);
FileOutputStream out=new FileOutputStream("c:\\temp\\downimage.png");
int ch;
while((ch=in.read())!=-1)
out.write(ch);
in.close();
out.close();
ps.close();
con.close();
con.close();
}
}
-------------------------------------------------------------------------------------------------------------
program ResultSetMetaData:
package com.edu;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Scanner;
public class RSMDDemo1 {
public static void main(String[] args) throws Exception {
Scanner sc = new Scanner(System.in);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager
.getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:orcl");
Statement st = con.createStatement();
while (true) {
System.out.print("SQL> ");
String sql = sc.nextLine();
if(sql.equalsIgnoreCase("exit"))
break;
ResultSet rs = st.executeQuery(sql); // RS+RSMD
ResultSetMetaData md = rs.getMetaData();
int cc = md.getColumnCount();
System.out.println("Total Selected Columns: " + cc);
for (int i = 1; i <= cc; i++)
System.out.printf("%12s",md.getColumnName(i));
System.out.println();
for(int i=0;i<cc;i++)
System.out.print("------------");
int row=0;
System.out.println();
while(rs.next()){
for (int i = 1; i <= cc; i++)
System.out.printf("%12s",rs.getString(i));
System.out.println();
row++;
}
System.out.println(row+" row's selected");
}
}
}
// url: "jdbc:mysql://localhost:3306/test?username=root&password=test"
-----------------------------------------------------------------------------------------------------------
GET DATA FROM ALL TABLE:
package com.edu;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;
public class GetAllDBTables {
public static void main(String[] args) throws Exception {
Properties p = new Properties();
p.load(Test.class.getClassLoader().getResourceAsStream(
"oracleinfo.properties"));
String driver = p.getProperty("driver");
String url = p.getProperty("url");
Class.forName(driver);
Connection con = DriverManager.getConnection(url, p);
DatabaseMetaData md = con.getMetaData();
//ResultSet rs = md.getTables(null,null,null,null);
//ResultSet rs = md.getTables(null,"SYSTEM",null,null);
ResultSet rs = md.getTables(null,null,null,new String[]{"TABLE","VIEW"});
int c=0;
while(rs.next()){
c++;
System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4));
}
System.out.println("Total: "+c);
}
}
--------------------------------------------------------------------------------------------------------------------------------
program for Aggregate function demo program:
package edu.std;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AggregateDemo {
public static void main(String[] args)throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con;
con = DriverManager.getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:orcl");
Statement st = con.createStatement();
String query = "SELECT MIN(Fee) Minimum,MAX(fee) Maximum,AVG(fee),COUNT(*) FROM student";
ResultSet rs = st.executeQuery(query);
rs.next();
int min=rs.getInt(1);
int max=rs.getInt(2);
double avg =rs.getDouble(3);
int total = rs.getInt(4);
System.out.println("Min fee: "+min);
System.out.println("AVg fee: "+avg);
System.out.println("Max fee: "+max);
System.out.println("Totat students: "+total);
}
}
--------------------------------------------------------------------------------------------------------------------------------
program: create SQL editor using advance java:
package com.edu;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Scanner;
public class SQLEditor {
public static void main(String[] args) throws Exception {
Scanner sc = new Scanner(System.in);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager
.getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:orcl");
Statement st = con.createStatement();
while (true) {
try {
System.out.print("SQL> ");
String sql = sc.nextLine();
if (sql.equalsIgnoreCase("exit"))
break;
ResultSet rs = null;
boolean flag = st.execute(sql);
if (flag == true) {
rs = st.getResultSet();
ResultSetMetaData md = rs.getMetaData();
int cc = md.getColumnCount();
System.out.println("Total Selected Columns: " + cc);
for (int i = 1; i <= cc; i++)
System.out.printf("%12s",md.getColumnName(i));
System.out.println();
for (int i = 0; i < cc; i++)
System.out.print("------------");
int row = 0;
System.out.println();
while (rs.next()) {
for (int i = 1; i <= cc; i++)
System.out.printf("%12s", rs.getString(i));
System.out.println();
row++;
}
System.out.println(row + " row's selected");
} else {
int count = st.getUpdateCount();
System.out.println(count + " row's affected"); }
} catch (Exception e) {
System.out.println("INVALID Statement");
}
}
}
}
// url: "jdbc:mysql://localhost:3306/test?username=root&password=test"
------------------------------------------------------------------------------------------------------------------------
Types of ResultSet:
1. Based on traversing result data:
a. ResultSet.TYPE_FORWARD_ONLY (1003)
b. ResultSet.TYPE_SCROLL_INSENSETIVE(1004)
c. ResultSet.TYPE_SCROLL_SENSETIVE(1005)
2. Based on concurancy:
a. ResultSet.CONCUR_READ_ONLY (1007)
b. ResultSet.CONCUR_UPDATABLE (1008)
Program to Check The Value of Concurancy:
package com.edu;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Scanner;
public class RSMDDemo1 {
public static void main(String[] args) throws Exception {
System.out.println(ResultSet.TYPE_FORWARD_ONLY);
System.out.println(ResultSet.TYPE_SCROLL_INSENSITIVE);
System.out.println(ResultSet.TYPE_SCROLL_SENSITIVE);
System.out.println(ResultSet.CONCUR_READ_ONLY);
System.out.println(ResultSet.CONCUR_UPDATABLE);
}
}
3. Based on Holdability:
a. ResultSet.CLOSE_CURSORS_AT_COMMIT
Whenever transaction is commited than resultset data no more available to process.
b. ResultSet.HOLD_CURSORS_OVER_COMMIT
Result set still available after commit operations
Methods:
a. read only or forward only result
getXxx(int columnindex)
getXxx(String columnname)
next()
getRow()
b. updatable or scrollable
getXxx(int columnindex)
getXxx(String columnname)
next()
getRow()
previous()
absolute()
relative()
first()
last()
beforeFirst()
afterLast()
deleteRow()
updatedRow()
moveToInsertRow()
insertRow()
updateXxx()
isFirst()
isLast()
isBeforeFirst()
isAfterLast()
> Resultset operations depends upon database software as well as JDBC Driver implementations. some jdbc drivers might not be support result set sensitive and updatable operations
Program: JDBC application for processing resultset in both direction.
package com.edu;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ScrollTest1 {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
String query = "SELECT dname,id,place FROM Department";
ResultSet rs = st.executeQuery(query); //BFR => LAST ROW
int did;
String dname, loc;
rs.last();
System.out.printf("%5d %12s %12s\n", rs.getInt(2), rs.getString(1),
rs.getString(3));
/*
* CODE2 while (rs.previous()) { did = rs.getInt(2);
* System.out.printf("%5d\n",did); } System.out.println("success");
*/
/*
* CODE1:
*
* System.out.println("Forward> "); while(rs.next()) { did =
* rs.getInt(2); dname = rs.getString(1); loc = rs.getString(3);
* System.out.printf("%5d %12s %12s\n",did,dname,loc); }
* System.out.println("Backward>"); while(rs.previous()) { did =
* rs.getInt(2); dname = rs.getString(1); loc = rs.getString(3);
* System.out.printf("%5d %12s %12s\n",did,dname,loc); }
*/
}
}
/*
* CREATE TABLE Department AS (SELECT Deptno Id,Dname,Loc Place FROM Dept)
*/
Program to Check Result set Support using SQL:-
package com.edu;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class CheckRSSupport {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
DatabaseMetaData md = con.getMetaData();
System.out.println(md
.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
System.out.println(md
.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));
System.out.println(md
.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE));
}
}
Program to check Support ResultSet Using MySQL:-
package com.edu;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class CheckRSSupportMysql {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test", "root","test");
DatabaseMetaData md = con.getMetaData();
System.out.println(md.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
System.out.println(md
.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));
System.out.println(md
.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY));
System.out.println(md.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE));
}
}
Program to Checking ResultSet MataData:-
package com.edu;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ResultSetMethodDemo {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
String query = "SELECT * FROM Department";
ResultSet rs = st.executeQuery(query);
rs.absolute(2);
System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
rs.relative(2);
System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
/* ResultSet rs = st.executeQuery(query);
//rs.absolute(2);
//rs.absolute(-3);
//rs.absolute(1); //rs.first()
//rs.absolute(0); //Exception
//rs.next(); //First
System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
*/
/*String query = "SELECT * FROM Department";
ResultSet rs = st.executeQuery(query);
rs.afterLast(); //ALR
while(rs.previous())
System.out.println(rs.getString(1));
*/
/* String query = "SELECT * FROM Department where ID = 10";
ResultSet rs = st.executeQuery(query);
System.out.println(rs.isAfterLast()); //false
System.out.println(rs.isBeforeFirst()); //true
rs.last();
System.out.println(rs.isLast()); //true
System.out.println(rs.isBeforeFirst()); //false
System.out.println("success");*/
/*
* String query = "SELECT * FROM Department"; ResultSet rs =
* st.executeQuery(query); //bfr: 0
*
* rs.last();
* System.out.println(rs.getString(1)+"\t"+rs.getString("dname"
* )+"\t"+rs.getString(3)); rs.first();
* System.out.println(rs.getString(1
* )+"\t"+rs.getString("dname")+"\t"+rs.getString(3));
*/
/*
* Statement st = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
* ResultSet.CONCUR_READ_ONLY); String query =
* "SELECT * FROM Department"; ResultSet rs = st.executeQuery(query);
* //bfr: 0 rs.last();
* System.out.println(rs.getString(1)+"\t"+rs.getString
* ("dname")+"\t"+rs.getString(3)); rs.first();
* System.out.println(rs.getString
* (1)+"\t"+rs.getString("dname")+"\t"+rs.getString(3));
*/
/*
* //case1: String query = "SELECT * FROM Department"; ResultSet rs =
* st.executeQuery(query); //bfr: 0
*
* System.out.println(rs.getRow()); rs.next();
* System.out.println(rs.getRow());
*/
// case2:
}
}
ResultSet cursor movements/access methods?
beforeFirst()
afterLast()
isFirst()
isLast()
isBeforeFirst()
isAfterLast()
absolute()
relative()
xxx getXxx():
> getInt(int index)
> getInt(String name)
> It will returns value of specified columns from a result set
> It can takes column name or column index as an input.
getString():
> By using this method we can get any type of column data/value in string representation.
Note: Index of first column starts from 1.
getRow():
> It will returns current position of the result set cursor
next():
> boolean next();
> It will moves result set cursor on the next row
> If next row is empty then it will returns false.
> if next row is non-empty then it will returns true.
previous():
> boolean previous()
> It will moves resultset cursor on the previous row
> If previous row is empty then returns false, if previous row is non-empty the returns true.
first():
> It will moves result set cursor on the first row directly.
last():
> It will moves result set cursor on the last row.
beforeFirst():
> It will moves result set cursor on before first row (BFR)
afterLast()
> It will moves result set cursor on the after last row(ALR
isFirst():
> it will checks result cursor is pointing to the first row or not.
> if cursor is available on the first then it will returns true.
isLast():
> if cursor is available at last row then returns true. otherwise false
isBeforeFirst():
isAfterLast();
absolute(int rowid):
> It will moves result set cursor directly on the specified rowid.
> It can takes forward rowid(+ve) as well as backward rowid(-ve)
rs.absolute(2)
> If rowid is positive then it will counts from first row onward in forward direction.
> If rowid is negative then it will counts from last row onward in backward direction.
relative(int rowid):
> It will counts row id according to current position of the cursor.
--------------------------------------------------------------------------------------------------------------------
Working with sensetive result set:
----------------------------------
> INSERT,DELETE,UPDATE: Oracle TYPE1, Oracle Type2,Oracle Type4,Mysql Type1,Type4
Working with updatable result set:
Program: update result set demo
Transaction Management:
Do Nothing
Do Everything
Transfer(acc1,with,acc2,dep)
{
con.autoCommit(false);
with(acc1,1000); UPDATE Account SET balance = balance - 1000 WHERE accno=acc1
catch(): con.rollback();
dep(acc2,1000); UPDATE Account SET balance = balance + 1000 WHERE accno=acc2
con.commit();
}
Saler:
avail: 1000pc=> 900pc
Consumer:
avail: 5pc+100pc =>100pc
order: 100pc
Transaction Manament:
> transaction management works based on property of do everything or do nothing.
> In jdbc by default auto commit property is true. In jdbc every db transaction automacally committed by the connection.
We can change this auto commit property by using Connection provided method
public void setAutoCommit(boolean status);
> We can commit any jdbc transaction explicitly by using
commit() method
> We can rollback any transaction by using
rollback() method
Transaction related methods:
> setAutoCommit()
> commit()
> rollback()
> savepoint()
> releaseSavepoint()
Mini Project(MVC):
Required Resource
-----------------
1. Required tables
Employee(eid,ename,salary,hiredate)
Customer(accountNo,cname,balance)
2. Required utility class
DBUtil.java
connect()
close()
3. Required DTO classes
EmployeeDTO.java
> id,ename,salary,hiredate
> getters and setters
CustomerDTO.java
>accountNo,cname,balance
> getters and setters
Required DAO class
EmployeeDao.java - SingleTon
insertEmployee();
deleteEmployee();
updateEmployee();
selectEmployee();
selectAllEmployee();
CustomerDao.java - SingleTon
insertCustomer();
deleteCustomer();
updateCustomer();
selectCustomer();
selectAllCustomer()
Table: Employee
CREATE TABLE Employee
(
eid VARCHAR2(5) PRIMARY KEY,
ename VARCHAR2(10),
salary NUMBER(7,2),
hiredate DATE
)
Table: Customer
CREATE TABLE Customer
(
accountNo VARCHAR2(5) PRIMARY KEY,
cname VARCHAR2(10),
balance NUMBER(7,2)
)
Create Myeclipse project(MiniProject)
-------------------------------------
> add lib folder with required jar files(ojdbc6.jar)
> add required jar file in build path
> create required DTO classes
No comments:
Post a Comment