KEMBAR78
Java database connectivity | PPTX
Atul Saurabh, profile picture
Uploaded byAtul Saurabh
PPTX, PDF927 views

Java database connectivity

This document provides an overview of Java Database Connectivity (JDBC) including: - JDBC provides a vendor-neutral way to connect Java applications to databases. - It describes the basic components and architecture of JDBC including drivers, connections, statements, and result sets. - It explains how to establish a connection to a database using JDBC including installing drivers, getting connections, executing queries, and processing result sets.

Downloaded 32 times
JAVA DATABASE CONNECTIVITY A vendor neutral way to talk to database By Atul Saurabh Assist. Professor Babaria Institute of Technology
THE DATABASE A database is an organized collection of data. -Wikipedia a) A file contains data in sequential form. Whenever we need to retrieve data from a file, we need to traverse the file character by character. b) There is no natural way to establish relation between file even though we can distribute data in different files. c) There is no effective way to check whether there is any redundancy in the file. d) There is no effective way to check the integrity of the data in a file.
FORMS OF DATABASES On the basis of organization of data, database appears in different forms a) Relational Database : The data is organized in form of relations or tables. E.g. Oracle, MySQL, SyBase, Postgres etc. b) Hierarchal Database : The data is organized in some tree form. E.g. XML, LDAP ect. c) Object Oriented Database : The data is encapsulated in form of object. Each object represent a row from some table in other kind of database. E.g. Java Persistence API, Hibernate etc.
TALKING TO THE DATABASE Each database vendor follows their own protocols for managing data inside database. Though the same syntax i.e. SQL is followed by every database vendor, their internal representation of data, i.e. datatype, is different. As for example MySQL uses float for floating type number but Oracle uses number(m,n) for the same. Programming language like Java provides its own syntax and datatypes. These diversities simply put a requirement of a layer which translate programming language representation into vendor specific representation. This layer is commonly known as database driver.
PREREQUISITES FOR DATABASE CONNECTION Whenever a database is connected through some programming language following information is required: 1) The driver class 2) Authentication credentials for database engine 3) The URL to locate the database.
STEPS TO CONNECT DATABASE THROUGH JAVA 1) Installation of vendor specific driver : Normally drivers comes in form of JAR file. Here are the names of some drivers a) ORACLE : oracle.jdbc.driver.OracleDriver b) MySQL : com.mysql.jdbc.Driver c) Postgres : org.postgresql.Driver d) ODBC : sun.jdbc.odbc.JdbcOdbcDriver There are three ways to install JDBC driver. i) Create a lib folder inside the project folder and place the JDBC driver JAR file inside lib and set classpath using manifest file.
ii) CLASSPATH : set class path. Follow the steps : Create any directory inside the system say in D: drive create a directory with name library. Put the JAR file inside this folder. Let the name of jar is mysqlconnector-java-5.0.8-bin.jar. Now Go to My Computer |--Properties |---Advanced System Setting |------Environmental Variable |---- User Variable |-----New The following windows appears :
In variable name write CLASSPATH In variable value write D:library mysql-connector-java-5.0.8-bin.jar; . (Mind the last . (dot) after semi-colon) iii) Place your JAR %JAVA_HOME%/jre/lib/ext directory. file If ODBC driver is used then no need to install the driver. Skip this step inside
2) Instantiate the driver : To instantiate the driver following code can be used class DatabaseConnection { public void makeConnection() { try { String driverClass=“sun.jdbc.odbc.JdbcOdbcDriver” Class.forName(driverClass); } catch(ClassNotFoundException e) { Logger.getLogger(DatabaseConnection.class.getName()).l og(Logger.SEVER,”The Driver Class Is Not Located”,e); } }
3) Establish the connection : Use DriverManager to establish the connection: public class DatabaseConnection { public void makeConnection() { try { String driverClass=“sun.jdbc.odbc.JdbcOdbcDriver” String username=“”; String passwd=“”; String url=“jdbc:odbc:dsn”; Class.forName(driverClass); Connection c=DriverManager.getConnection(url,username,passwd); if(!c.isClosed()) System.out.println(“Connection established”); } catch(ClassNotFoundException e) { Logger.getLogger(DatabaseConnection.class.getName()).log(Logger.SEVER, ”The Driver Class Is Not Located”,e); } catch(SQLException sqle){ Logger.getLogger(DatabaseConnection.class.getName()).log(Logger.SEVER, ”Unable To Connect”,sqle); } } }
CREATING A DSN DSN stands for Data Source Name. It is required only if we are using ODBC driver. To create a DSN use the following steps : Go To Control Panel |----Administrative Tool |--- Data Source And create a System DSN by supplying the DSN name and selecting the database.
ARCHITECTURE OF JDBC
TYPES OF JDBC DRIVERS Type 1: JDBC-ODBC Bridge Driver: In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on each client machine. Using ODBC requires configuring on your system a Data Source Name (DSN) that represents the target database.
Type 2: JDBC-Native API: In a Type 2 driver, JDBC API calls are converted into native C/C++ API calls which are unique to the database. These drivers typically provided by the database vendors and used in the same manner as the JDBC-ODBC Bridge, the vendor-specific driver must be installed on each client machine.
Type 3: JDBC-Net pure Java : In a Type 3 driver, a three-tier approach is used to accessing databases. The JDBC clients use standard network sockets to communicate with an middleware application server. The socket information is then translated by the middleware application server into the call format required by the DBMS, and forwarded to the database server.
Type 4: 100% pure Java: In a Type 4 driver, a pure Java-based driver that communicates directly with vendor's database through socket connection. This is the highest performance driver available for the database and is usually provided by the vendor itself.
SQL DATA TYPE AND CORRESPONDING JAVA TYPES Sl. No. SQL Data Type Java Data Type 1 INTEGER, INT int 2 SMALLINT short 3 NUMERIC(m,n), DECIMAL(m,n), DEC(m,n) java.math.BigDecimal 4 FLOAT(n) double 5 REAL float 6 CHARACTER(n), CHAR(n) String 7 VARCHAR(n) String
Sl. No. SQL Data Type Java Data Type 8 BOOLEAN Boolean 9 DATE java.sql.Date 10 TIME java.sql.Time 11 TIMESTAMP java.sql.Timestamp 12 BLOB java.sql.Blob 13 CLOB java.sql.Clob 14 ARRAY java.sql.Array
EXECUTING SQL QUERY In native SQL there are two types of queries a) The query which changes the values in one or more rows possibly by creating or deleting rows. E.g. INSERT, UPDATE, DELETE and ALTER. b) The query which simply returns the projection of one or more tables. E.g. SELECT. Whenever we execute query (a) then the query always returns us how many rows get affected. Whenever we execute query (b) we get a snapshot of subset original table(s). This snapshot is commonly known as ResultSet.
The java.sql.Connection The Connection object represents a connection to the database. i) Statement createStatement() : This method is used to get the Statement object The java.sql.Statement The Statement object is used to execute a native SQL queries. i) ResultSet executeQuery(String query) : This method is used to execute query (b). ii) int executeUpdate(String query) : Used to execute query (a). iii) Int getUpdateCount() : Returns the number of records affected by previous update query.
The java.sql.ResultSet The ResultSet is a snapshot of subset of original tables(s) . A ResultSet is always provided by a cursor. Initially cursor is pointed outside the table. We can visualize ResultSet as follows Col1 Col2 …. Coln R01 R02 …. R0n R11 R12 .... R1n R21 R22 …. R2n
i) boolean next() : This method moves the cursor one row downward and returns true if there is any row available below the cursor. ii) Xxx getXxx(int colNumber) : This method returns the values inside the column represented by colNumber.
AN EXAMPLE public class InsertQueryExample { public static void main(String [] args) { try { String driverClass=“sun.jdbc.odbc.JdbcOdbcDriver”; String url=“jdbc:odbc:dsn”; String uname=“”; String upass=“”; Class.forName(driverClass); Connection c=DriverManager.getConnection(url,uname,upass); String query=“insert into db values(null,’atul’,’assist. Prof’); Statement s=c.createStatement(); int res=s.executeUpdate(s); if(res > 0) System.out.println(“Values inserted successfully”); } catch(Exception e) { } } }
SECOND EXAMPLE import java.sql.*; public class SelectQueryExample { public static void main(String [] args) { try { Connection c= getConnection(); Statement stmt=c.createStatement(); String query=“select * from student”; ResultSet rs=stmt.executeQuery(query); while(rs.next()) { System.out.println(“Student Id=“+rs.getInt(1)); System.out.println(“Student Name=“+rs.getString(2)); } } catch(Exception e) { e.printStackTrace(); } } }
EFFECT OF NEXT() OPERATION The next() method of ResultSet makes the cursor forward. If the cursor is at the last row this method returns false. Suppose the initial condition of ResultSet is as follows Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N R20 R21 … R2N
Now if we fire next() then the cursor moves forward and the method returns true as the cursor pointing to the first row. Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N R20 R21 … R2N
Now if the cursor points to the last row then the next() method returns false and cursor will not move forward. Col1 Col2 … C olN R00 R01 … R0N R10 R11 … R1N R20 R21 … R2N
IS THERE ANY METHOD PREVIOUS()? The next() method of ResultSet moves the cursor one row forward. The ResultSet interface defines the following method to manipulate data retrieval from the ResultSet. 1) boolean previous() : Move the cursor one row backward. If the cursor is pointing above the first row, the method returns false. 2) boolean first() : Move the cursor to the first row. 3) boolean last() : Move the cursor to the last row. 4) boolean relative(int n) : Moves the cursor to the location n from the current position.
PROBLEMS WITH STATEMENT Commonly we use java.sql.Statement interface to execute sql queries. Here is a typical example of SQL insert query String query=“insert values(null,‟Atul‟,‟Vadodara‟)”; into student Statement s=con.createStatement(); s.executeUpdate(query); There are two major problems with this statement: 1) If we need to change the values then query will be reconstructed and placed at different memory location. Here is a typical memory layout for this scheme:
insert into student values(null,’Atul’,’Vadodara’) 0x3356FF9 The first query insert into student values(null,’Prakash’,’Delhi’) 0x3356A80 The second query only value changed 2) We need to care about the quotes („). That means values must be wrapped by quotes if the SQL syntax requires that. These two limitations makes Statement a less preferable choice for programmers.
THE PREPAREDSTATEMENT To avoid the complexity of Statement, Java provides another interface called PreparedStatement Here is a typical way to execute a insert query using PreparedStatement. String query=“insert into student values(null,?,?)”; PreparedStatement p=con.prepareStatement(query); p.setString(1,”Atul”); p.setString(2,”Vadodara”); p.executeUpdate(); Look at the query: instead of placing values a ? Is placed. The ? is called positional parameter. The first ? is numbered 1, the second ? is numbered 2 and so on.
The values of each ? can be set using setXxx(int pos, Xxx value); Note: Xxx will be replaced by String, Int, Double, Float ect. depending upon what datatype is used for the position. Second point : When we are setting the value of each ? we are not taking care of quotes(„). The quotes will be automatically wrapped by PreparedStatement. When we supply any query to PreparedStatement then instead of wasting memory, it just leave the blank space for each ?. Now if we require to change the value only blank space if filled with new values.
insert into student values(null, ________________, ________________ ) 0x3356A79 Memory allocation for query by PreparedStatement insert into student values(null, ‘Atul’,’Vadodara’) 0x3356A79 Query after setting the value
insert into student values(null, ‘Prakash’,’Delhi’) 0x3356A79 Query after changing the value. Note that the memory location of the query remains same and quotes are added.
SCROLLABLE RESULTSET PreparedStatement gives us a very efficient way to create precompiled (in terms of java only) queries. Memory wastage and notorious quote problem are solved by it. The PreparedStatement was introduced in Java 1.1. At that time there is no way by which we can move backward or jump at any location in ResultSet. After the introduction of JDBC 2 the ResultSet is now scrollable. That means the cursor can move both backward and forward or jump to specific location in the ResultSet. By default, the ResultSet in not scrollable. That means Connection c=…..; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query); ResultSet rs=p.executeQuery(); // oops rs is not scrollable
HOW TO MAKE RESULTSET SCROLLABLE To make a ResultSet scrollable we need to use the following method to get PreparedStatement object. PreparedStatement prepareStatement(String query, int type, int concurrency); This method is defined in java.sql.Connection interface. The type parameter may have following values: Allowed values for type purpose ResultSet.TYPE_FORWARD_ONLY The resultset is not scrollable ResultSet.TYPE_SCROLL_INSENSITIVE The resultset is scrollable but not sensitive to database change ResultSet.TYPE_SCROLL_SENSITIVE The resultset scrollable and sensitive to database change
The concurrency parameter may have following values: Allowed values for concurrency purpose ResultSet.CONCUR_READ _ONLY The resultset can not be used to update database ResultSet.CONCUR_UPDA TABLE The resultset can be used to update database. Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL_IN SENSITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rs=p.executeQuery(); // OK rs is scrollable but not updatable
METHOD FOR SCROLLING RESULTSET To traverse back and forth in ResultSet JDBC API provides the following methods : 1) boolean previous() : Move the cursor one row backward. If the cursor is pointing above the first row, the method returns false. 2) boolean first() : Move the cursor to the first row. 3) boolean last() : Move the cursor to the last row. 4) boolean relative(int n) : Moves the cursor to the location n from the current position. 5) void beforeFirst() : move the cursor before the first row. 6) void afterLast() : move the cursor after the last row. 7) isBeforeFirst() 8) isAfterLast(): Test whether the cursor is before the first row or the last row.
UPDATABLE RESULTSET ResultSet is normally a memory snapshot of subset of tables present inside the database and is a result of Select query. Generally it is used only for data retrieval. But sometimes while traversing the ResultSet we need to make changes in our database. By default a ResultSet is neither scrollable nor updatable. That simply imply that we can not use a ResultSet to make change in database. After the introduction of JDBC 2, ResultSet can be made scrollable as well as updatable. To make a ResultSet updatable we need to obtain ResultSet as follows: Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL_INS ENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=p.executeQuery(); // OK rs is scrollable and updatable
MOVING FORWARD AND BACKWARD Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL _INSENSITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rs=p.executeQuery(); rs.next(); // Move the cursor to first row rs.next(); // Move the cursor to second row rs.previous(); // Move the cursor to first row again rs.last(); // place the cursor to last row
Col1 … ColN R00 R01 … R0N R10 R11 … R1N . . … . RM0  Col2 RM1 … RMN Effect of 2 next() operation Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0 RM1 … RMN
 Effect of one previous() operation Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0 RM1 … RMN
UPDATING CURRENT ROW VALUES Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL _INSENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=p.executeQuery(); rs.next(); // Move the cursor to first row rs.next(); // Move the cursor to second row rs.updateString(2,”Prakash”); // Changing the value of 2nd column of 2nd row rs.updateRow(); // Making effect permanent.
 Initial Value Col1 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0  Col2 RM1 … RMN Effect of updateXxx and updateRow(see prev example) Col1 Col2 … ColN R00 R01 … R0N R10 Prakash … R1N . . . . RM0 RM1 … RMN
INSERTING NEW VALUES Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITI VE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=p.executeQuery(); rs.next(); // Move the cursor to first row rs.next(); // Move the cursor to second row String id=0; String name=“Dinesh”; int age=25; String gender-”Male”; rs.moveToInsertRow(); // Move To the Row where insertion is done rs.updateInt(1,id); // update first column rs.updateString(2,name); // update second column rs.updateInt(3,age); // update third column rs.updateString(4,gender); // update fourth column rs.insertRow(); // make the effect permanent rs.moveToCurrentRow(); // move to second row
Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0 RM1 … R(M=1 R(M+1 … )) )1 RMN Insert Row Cursor moved to insert row Cursor moved back Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0 RM1 … RMN R(M+1)0 R(M+1)1 … R(M+1)N Merged R(M+1 )N
UPDATABLE METHODS SUMMARY All the update related methods are defined in java.sql.ResultSet interface. 1) void moveToInsertRow() : moves the cursor to insert row. The insert row is a special row for inserting new data with updateXXX and insertRow methods. 2) void moveToCurrentRow(): move the cursor back from the insert row to the row that is occupied when moveToInsertRow method was called. 3) void insertRow(): insert the content of the insert row into the database and resultset. 4) void updateXxx(int colnumber,Xxx data) : update the field in the current row of the resultset. 5) void updateRow(): sends the current row updates to the database.
METADATA In SQL, data that describe the database or one of its parts are called metadata. In general, metadata gives the following information 1) 2) 3) 4) 5) What is the structure of a database? How many maximum connection that can be established concurrently to the database? How many rows are there in a table? How many columns a table has? Is ResultSet Scrollable or updatable ? There are some other advanced information we can collect from the metadata. These information, in general, are not required for application programmers. But these information are highly required for those who want to design advanced tools for database.
TYPES OF METADATA On the basic of source of metadata, it is categorized into three types 1) Database Metadata database itself. : The information about 2) ResultSet Metadata : The information about the ResultSet. 3) PreparedStatement parameter: The information about the parameters of prepared statement.

More Related Content

PPTX
jsp MySQL database connectivity
bybaabtra.com - No. 1 supplier of quality freshers
 
PPTX
DataBase Connectivity
byAkankshaji
 
PPT
Java database connectivity with MYSQL
byAdil Mehmoood
 
PPT
java jdbc connection
byWaheed Warraich
 
PDF
JSP
bycorneliuskoo
 
PPTX
JDBC ppt
byRohit Jain
 
PDF
Jdbc
byJussi Pohjolainen
 
PPT
JDBC Java Database Connectivity
byRanjan Kumar
 
jsp MySQL database connectivity
bybaabtra.com - No. 1 supplier of quality freshers
 
DataBase Connectivity
byAkankshaji
 
Java database connectivity with MYSQL
byAdil Mehmoood
 
java jdbc connection
byWaheed Warraich
 
JSP
bycorneliuskoo
 
JDBC ppt
byRohit Jain
 
Jdbc
byJussi Pohjolainen
 
JDBC Java Database Connectivity
byRanjan Kumar
 

What's hot

PPTX
Jdbc
byNitesh Kumar Pandey
 
PPS
Jdbc api
bykamal kotecha
 
PPT
Jdbc ppt
byVikas Jagtap
 
PPT
JDBC Tutorial
byInformation Technology
 
PPTX
Database Access With JDBC
byDharani Kumar Madduri
 
PPT
3 database-jdbc(1)
byhameedkhan2017
 
PPT
JDBC – Java Database Connectivity
byInformation Technology
 
PPTX
1. java database connectivity (jdbc)
byFad Zulkifli
 
PPTX
Java database connectivity with MySql
byDhyey Dattani
 
PPTX
Java Database Connectivity (JDBC)
byPooja Talreja
 
KEY
JDBC Basics (In 20 Minutes Flat)
byCraig Dickson
 
PPS
Jdbc example program with access and MySql
bykamal kotecha
 
PPTX
Lecture 1. java database connectivity
byWaheedullah Suliman Khail
 
PDF
Overview Of JDBC
byMindfire Solutions
 
PPTX
Jdbc in servlets
byNuha Noor
 
PPT
Java database connectivity
byVaishali Modi
 
PPTX
JDBC
byBalwinder Kumar
 
PPTX
Jdbc
byYamuna Devi
 
PPT
Java Database Connectivity
bybackdoor
 
PPT
Jdbc complete
bySandeep Rawat
 
Jdbc
byNitesh Kumar Pandey
 
Jdbc api
bykamal kotecha
 
Jdbc ppt
byVikas Jagtap
 
JDBC Tutorial
byInformation Technology
 
Database Access With JDBC
byDharani Kumar Madduri
 
3 database-jdbc(1)
byhameedkhan2017
 
JDBC – Java Database Connectivity
byInformation Technology
 
1. java database connectivity (jdbc)
byFad Zulkifli
 
Java database connectivity with MySql
byDhyey Dattani
 
Java Database Connectivity (JDBC)
byPooja Talreja
 
JDBC Basics (In 20 Minutes Flat)
byCraig Dickson
 
Jdbc example program with access and MySql
bykamal kotecha
 
Lecture 1. java database connectivity
byWaheedullah Suliman Khail
 
Overview Of JDBC
byMindfire Solutions
 
Jdbc in servlets
byNuha Noor
 
Java database connectivity
byVaishali Modi
 
JDBC
byBalwinder Kumar
 
Jdbc
byYamuna Devi
 
Java Database Connectivity
bybackdoor
 
Jdbc complete
bySandeep Rawat
 

Viewers also liked

PDF
Java Server Pages
byEleonora Ciceri
 
PPTX
Java/Servlet/JSP/JDBC
byFAKHRUN NISHA
 
PPSX
Java server pages
byTanmoy Barman
 
PPTX
The java server pages
byAtul Saurabh
 
PPTX
The java enterprise edition (Servlet Basic)
byAtul Saurabh
 
PPT
Losseless
byanithabalaprabhu
 
PPT
Video Streaming - 4.ppt
byVideoguy
 
PPS
Jsp chapter 1
bykamal kotecha
 
PPT
Jsp ppt
byVikas Jagtap
 
PPT
Compression
byAshish Kumar
 
Java Server Pages
byEleonora Ciceri
 
Java/Servlet/JSP/JDBC
byFAKHRUN NISHA
 
Java server pages
byTanmoy Barman
 
The java server pages
byAtul Saurabh
 
The java enterprise edition (Servlet Basic)
byAtul Saurabh
 
Losseless
byanithabalaprabhu
 
Video Streaming - 4.ppt
byVideoguy
 
Jsp chapter 1
bykamal kotecha
 
Jsp ppt
byVikas Jagtap
 
Compression
byAshish Kumar
 

Similar to Java database connectivity

PPT
Basic Java Database Connectivity(JDBC)
bysuraj pandey
 
PPT
Java database connectivity
byVaishali Modi
 
PPTX
Core jdbc basics
bySourabrata Mukherjee
 
PPTX
Jdbc
byDeepikaT13
 
PDF
Jdbc[1]
byFulvio Corno
 
PDF
JDBC programming
byFulvio Corno
 
PPT
Chap3 3 12
byHemo Chella
 
PPTX
Jdbc introduction
byRakesh Kumar Ray
 
PDF
Jdbc 1
byTuan Ngo
 
PPTX
JAVA DATABASE CONNECTIVITY(JDBC CONNECTIVITY).pptx
byJGEETHAPRIYA
 
PPT
JDBC.ppt
byChagantiSahith
 
PPT
Jdbc
byMallikarjuna G D
 
PDF
Jdbc
bymishaRani1
 
PPT
Jdbc connectivity
byarikazukito
 
PDF
Introduction to JDBC and JDBC Drivers
byKumar
 
PDF
Introduction to JDBC and database access in web applications
byFulvio Corno
 
PDF
Unit 5.pdf
bysaturo3011
 
PPT
Final Database Connectivity in JAVA.ppt
byTabassumMaktum
 
PPT
JDBC java for learning java for learn.ppt
bykingkolju
 
PDF
Chapter 5 JDBC.pdf for stufent of computer andtudent It s
byanuwaradisu19
 
Basic Java Database Connectivity(JDBC)
bysuraj pandey
 
Java database connectivity
byVaishali Modi
 
Core jdbc basics
bySourabrata Mukherjee
 
Jdbc
byDeepikaT13
 
Jdbc[1]
byFulvio Corno
 
JDBC programming
byFulvio Corno
 
Chap3 3 12
byHemo Chella
 
Jdbc introduction
byRakesh Kumar Ray
 
Jdbc 1
byTuan Ngo
 
JAVA DATABASE CONNECTIVITY(JDBC CONNECTIVITY).pptx
byJGEETHAPRIYA
 
JDBC.ppt
byChagantiSahith
 
Jdbc
byMallikarjuna G D
 
Jdbc
bymishaRani1
 
Jdbc connectivity
byarikazukito
 
Introduction to JDBC and JDBC Drivers
byKumar
 
Introduction to JDBC and database access in web applications
byFulvio Corno
 
Unit 5.pdf
bysaturo3011
 
Final Database Connectivity in JAVA.ppt
byTabassumMaktum
 
JDBC java for learning java for learn.ppt
bykingkolju
 
Chapter 5 JDBC.pdf for stufent of computer andtudent It s
byanuwaradisu19
 

Recently uploaded

PPTX
Single entry System Vs Double entry System.pptx
bylavanyafranklin0804
 
PPTX
Common problems or challenges faced by Indian adolescents
byDr. Harpal Kaur
 
PDF
The Children’s Support Fund, set up to support the welfare and education of c...
bynservice241
 
PPTX
🧪“Blood Chemistry Tests in Pharmacy Practice: Clinical Laboratory Guide for P...
byBanny S.V
 
PPTX
DO 34 s 2025 - ammendment of DO 24 s 2025.pptx
byJeanalynEstrellado3
 
PPTX
GAS chromatography ppt (Presentation) by Jatin Chauhan
byjatinchauhan1618
 
PDF
Who Owns the Narrative? Data, Disinformation, and the Missing Voice of Academia
byIsmail Fahmi
 
PPTX
Basics for Conducting Bibliometric Analysis - Dr Ahsan Riaz
bySystematic Reviews Network (SRN)
 
PDF
Total Quality Management : A presentation by a third year student.
byMbalenhle Ndlovu
 
DOCX
PRESS STATEMENT - Response to Minority_ Press Ready.docx
bynservice241
 
PPTX
English Home Language & First Additional Language P2 Preparation.pptx
byMasingita Maswanganye
 
PPTX
Hudson Vitale "AI Essentials: From Tools to Strategies: A 2025 NISO Training ...
byNational Information Standards Organization (NISO)
 
PPTX
DPSM-BITDA Introduction Presentation Slides
byGreat Files
 
PPTX
Safety Needs and Prevention of environmental hazards.pptx
byAneetaSharma15
 
PDF
Nernst Distribution Law and factors affecting distribution constant
byMithil Fal Desai
 
PPTX
PECTORAL REGION.pptx Human anatomy,Bmls,
byKISMAT ALI
 
PPTX
psychoanalytic Theory.pptx, MSc. Nursing
byKomalJaiswal46
 
PPTX
Project Dashboard in Odoo 18 Project
byCeline George
 
PDF
BUSINESS ETHICS – UNIT V: Ethical Insights from Thirukkural and Modern Thought
byD NANEE
 
PDF
Yaksha Prashna | General Quiz at RLAC | Amlan Sarkar | Full Set
byAmlan Sarkar
 
Single entry System Vs Double entry System.pptx
bylavanyafranklin0804
 
Common problems or challenges faced by Indian adolescents
byDr. Harpal Kaur
 
The Children’s Support Fund, set up to support the welfare and education of c...
bynservice241
 
🧪“Blood Chemistry Tests in Pharmacy Practice: Clinical Laboratory Guide for P...
byBanny S.V
 
DO 34 s 2025 - ammendment of DO 24 s 2025.pptx
byJeanalynEstrellado3
 
GAS chromatography ppt (Presentation) by Jatin Chauhan
byjatinchauhan1618
 
Who Owns the Narrative? Data, Disinformation, and the Missing Voice of Academia
byIsmail Fahmi
 
Basics for Conducting Bibliometric Analysis - Dr Ahsan Riaz
bySystematic Reviews Network (SRN)
 
Total Quality Management : A presentation by a third year student.
byMbalenhle Ndlovu
 
PRESS STATEMENT - Response to Minority_ Press Ready.docx
bynservice241
 
English Home Language & First Additional Language P2 Preparation.pptx
byMasingita Maswanganye
 
Hudson Vitale "AI Essentials: From Tools to Strategies: A 2025 NISO Training ...
byNational Information Standards Organization (NISO)
 
DPSM-BITDA Introduction Presentation Slides
byGreat Files
 
Safety Needs and Prevention of environmental hazards.pptx
byAneetaSharma15
 
Nernst Distribution Law and factors affecting distribution constant
byMithil Fal Desai
 
PECTORAL REGION.pptx Human anatomy,Bmls,
byKISMAT ALI
 
psychoanalytic Theory.pptx, MSc. Nursing
byKomalJaiswal46
 
Project Dashboard in Odoo 18 Project
byCeline George
 
BUSINESS ETHICS – UNIT V: Ethical Insights from Thirukkural and Modern Thought
byD NANEE
 
Yaksha Prashna | General Quiz at RLAC | Amlan Sarkar | Full Set
byAmlan Sarkar
 

Java database connectivity

  • 1.
    JAVA DATABASE CONNECTIVITY Avendor neutral way to talk to database By Atul Saurabh Assist. Professor Babaria Institute of Technology
  • 2.
    THE DATABASE A databaseis an organized collection of data. -Wikipedia a) A file contains data in sequential form. Whenever we need to retrieve data from a file, we need to traverse the file character by character. b) There is no natural way to establish relation between file even though we can distribute data in different files. c) There is no effective way to check whether there is any redundancy in the file. d) There is no effective way to check the integrity of the data in a file.
  • 3.
    FORMS OF DATABASES Onthe basis of organization of data, database appears in different forms a) Relational Database : The data is organized in form of relations or tables. E.g. Oracle, MySQL, SyBase, Postgres etc. b) Hierarchal Database : The data is organized in some tree form. E.g. XML, LDAP ect. c) Object Oriented Database : The data is encapsulated in form of object. Each object represent a row from some table in other kind of database. E.g. Java Persistence API, Hibernate etc.
  • 4.
    TALKING TO THEDATABASE Each database vendor follows their own protocols for managing data inside database. Though the same syntax i.e. SQL is followed by every database vendor, their internal representation of data, i.e. datatype, is different. As for example MySQL uses float for floating type number but Oracle uses number(m,n) for the same. Programming language like Java provides its own syntax and datatypes. These diversities simply put a requirement of a layer which translate programming language representation into vendor specific representation. This layer is commonly known as database driver.
  • 5.
    PREREQUISITES FOR DATABASECONNECTION Whenever a database is connected through some programming language following information is required: 1) The driver class 2) Authentication credentials for database engine 3) The URL to locate the database.
  • 6.
    STEPS TO CONNECTDATABASE THROUGH JAVA 1) Installation of vendor specific driver : Normally drivers comes in form of JAR file. Here are the names of some drivers a) ORACLE : oracle.jdbc.driver.OracleDriver b) MySQL : com.mysql.jdbc.Driver c) Postgres : org.postgresql.Driver d) ODBC : sun.jdbc.odbc.JdbcOdbcDriver There are three ways to install JDBC driver. i) Create a lib folder inside the project folder and place the JDBC driver JAR file inside lib and set classpath using manifest file.
  • 7.
    ii) CLASSPATH :set class path. Follow the steps : Create any directory inside the system say in D: drive create a directory with name library. Put the JAR file inside this folder. Let the name of jar is mysqlconnector-java-5.0.8-bin.jar. Now Go to My Computer |--Properties |---Advanced System Setting |------Environmental Variable |---- User Variable |-----New The following windows appears :
  • 8.
    In variable namewrite CLASSPATH In variable value write D:library mysql-connector-java-5.0.8-bin.jar; . (Mind the last . (dot) after semi-colon) iii) Place your JAR %JAVA_HOME%/jre/lib/ext directory. file If ODBC driver is used then no need to install the driver. Skip this step inside
  • 9.
    2) Instantiate thedriver : To instantiate the driver following code can be used class DatabaseConnection { public void makeConnection() { try { String driverClass=“sun.jdbc.odbc.JdbcOdbcDriver” Class.forName(driverClass); } catch(ClassNotFoundException e) { Logger.getLogger(DatabaseConnection.class.getName()).l og(Logger.SEVER,”The Driver Class Is Not Located”,e); } }
  • 10.
    3) Establish theconnection : Use DriverManager to establish the connection: public class DatabaseConnection { public void makeConnection() { try { String driverClass=“sun.jdbc.odbc.JdbcOdbcDriver” String username=“”; String passwd=“”; String url=“jdbc:odbc:dsn”; Class.forName(driverClass); Connection c=DriverManager.getConnection(url,username,passwd); if(!c.isClosed()) System.out.println(“Connection established”); } catch(ClassNotFoundException e) { Logger.getLogger(DatabaseConnection.class.getName()).log(Logger.SEVER, ”The Driver Class Is Not Located”,e); } catch(SQLException sqle){ Logger.getLogger(DatabaseConnection.class.getName()).log(Logger.SEVER, ”Unable To Connect”,sqle); } } }
  • 11.
    CREATING A DSN DSNstands for Data Source Name. It is required only if we are using ODBC driver. To create a DSN use the following steps : Go To Control Panel |----Administrative Tool |--- Data Source And create a System DSN by supplying the DSN name and selecting the database.
  • 12.
  • 13.
    TYPES OF JDBCDRIVERS Type 1: JDBC-ODBC Bridge Driver: In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on each client machine. Using ODBC requires configuring on your system a Data Source Name (DSN) that represents the target database.
  • 14.
    Type 2: JDBC-NativeAPI: In a Type 2 driver, JDBC API calls are converted into native C/C++ API calls which are unique to the database. These drivers typically provided by the database vendors and used in the same manner as the JDBC-ODBC Bridge, the vendor-specific driver must be installed on each client machine.
  • 15.
    Type 3: JDBC-Netpure Java : In a Type 3 driver, a three-tier approach is used to accessing databases. The JDBC clients use standard network sockets to communicate with an middleware application server. The socket information is then translated by the middleware application server into the call format required by the DBMS, and forwarded to the database server.
  • 16.
    Type 4: 100%pure Java: In a Type 4 driver, a pure Java-based driver that communicates directly with vendor's database through socket connection. This is the highest performance driver available for the database and is usually provided by the vendor itself.
  • 17.
    SQL DATA TYPEAND CORRESPONDING JAVA TYPES Sl. No. SQL Data Type Java Data Type 1 INTEGER, INT int 2 SMALLINT short 3 NUMERIC(m,n), DECIMAL(m,n), DEC(m,n) java.math.BigDecimal 4 FLOAT(n) double 5 REAL float 6 CHARACTER(n), CHAR(n) String 7 VARCHAR(n) String
  • 18.
    Sl. No. SQL DataType Java Data Type 8 BOOLEAN Boolean 9 DATE java.sql.Date 10 TIME java.sql.Time 11 TIMESTAMP java.sql.Timestamp 12 BLOB java.sql.Blob 13 CLOB java.sql.Clob 14 ARRAY java.sql.Array
  • 19.
    EXECUTING SQL QUERY Innative SQL there are two types of queries a) The query which changes the values in one or more rows possibly by creating or deleting rows. E.g. INSERT, UPDATE, DELETE and ALTER. b) The query which simply returns the projection of one or more tables. E.g. SELECT. Whenever we execute query (a) then the query always returns us how many rows get affected. Whenever we execute query (b) we get a snapshot of subset original table(s). This snapshot is commonly known as ResultSet.
  • 20.
    The java.sql.Connection The Connectionobject represents a connection to the database. i) Statement createStatement() : This method is used to get the Statement object The java.sql.Statement The Statement object is used to execute a native SQL queries. i) ResultSet executeQuery(String query) : This method is used to execute query (b). ii) int executeUpdate(String query) : Used to execute query (a). iii) Int getUpdateCount() : Returns the number of records affected by previous update query.
  • 21.
    The java.sql.ResultSet The ResultSetis a snapshot of subset of original tables(s) . A ResultSet is always provided by a cursor. Initially cursor is pointed outside the table. We can visualize ResultSet as follows Col1 Col2 …. Coln R01 R02 …. R0n R11 R12 .... R1n R21 R22 …. R2n
  • 22.
    i) boolean next() :This method moves the cursor one row downward and returns true if there is any row available below the cursor. ii) Xxx getXxx(int colNumber) : This method returns the values inside the column represented by colNumber.
  • 23.
    AN EXAMPLE public classInsertQueryExample { public static void main(String [] args) { try { String driverClass=“sun.jdbc.odbc.JdbcOdbcDriver”; String url=“jdbc:odbc:dsn”; String uname=“”; String upass=“”; Class.forName(driverClass); Connection c=DriverManager.getConnection(url,uname,upass); String query=“insert into db values(null,’atul’,’assist. Prof’); Statement s=c.createStatement(); int res=s.executeUpdate(s); if(res > 0) System.out.println(“Values inserted successfully”); } catch(Exception e) { } } }
  • 24.
    SECOND EXAMPLE import java.sql.*; publicclass SelectQueryExample { public static void main(String [] args) { try { Connection c= getConnection(); Statement stmt=c.createStatement(); String query=“select * from student”; ResultSet rs=stmt.executeQuery(query); while(rs.next()) { System.out.println(“Student Id=“+rs.getInt(1)); System.out.println(“Student Name=“+rs.getString(2)); } } catch(Exception e) { e.printStackTrace(); } } }
  • 25.
    EFFECT OF NEXT()OPERATION The next() method of ResultSet makes the cursor forward. If the cursor is at the last row this method returns false. Suppose the initial condition of ResultSet is as follows Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N R20 R21 … R2N
  • 26.
    Now if wefire next() then the cursor moves forward and the method returns true as the cursor pointing to the first row. Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N R20 R21 … R2N
  • 27.
    Now if thecursor points to the last row then the next() method returns false and cursor will not move forward. Col1 Col2 … C olN R00 R01 … R0N R10 R11 … R1N R20 R21 … R2N
  • 28.
    IS THERE ANYMETHOD PREVIOUS()? The next() method of ResultSet moves the cursor one row forward. The ResultSet interface defines the following method to manipulate data retrieval from the ResultSet. 1) boolean previous() : Move the cursor one row backward. If the cursor is pointing above the first row, the method returns false. 2) boolean first() : Move the cursor to the first row. 3) boolean last() : Move the cursor to the last row. 4) boolean relative(int n) : Moves the cursor to the location n from the current position.
  • 29.
    PROBLEMS WITH STATEMENT Commonlywe use java.sql.Statement interface to execute sql queries. Here is a typical example of SQL insert query String query=“insert values(null,‟Atul‟,‟Vadodara‟)”; into student Statement s=con.createStatement(); s.executeUpdate(query); There are two major problems with this statement: 1) If we need to change the values then query will be reconstructed and placed at different memory location. Here is a typical memory layout for this scheme:
  • 30.
    insert into studentvalues(null,’Atul’,’Vadodara’) 0x3356FF9 The first query insert into student values(null,’Prakash’,’Delhi’) 0x3356A80 The second query only value changed 2) We need to care about the quotes („). That means values must be wrapped by quotes if the SQL syntax requires that. These two limitations makes Statement a less preferable choice for programmers.
  • 31.
    THE PREPAREDSTATEMENT To avoidthe complexity of Statement, Java provides another interface called PreparedStatement Here is a typical way to execute a insert query using PreparedStatement. String query=“insert into student values(null,?,?)”; PreparedStatement p=con.prepareStatement(query); p.setString(1,”Atul”); p.setString(2,”Vadodara”); p.executeUpdate(); Look at the query: instead of placing values a ? Is placed. The ? is called positional parameter. The first ? is numbered 1, the second ? is numbered 2 and so on.
  • 32.
    The values ofeach ? can be set using setXxx(int pos, Xxx value); Note: Xxx will be replaced by String, Int, Double, Float ect. depending upon what datatype is used for the position. Second point : When we are setting the value of each ? we are not taking care of quotes(„). The quotes will be automatically wrapped by PreparedStatement. When we supply any query to PreparedStatement then instead of wasting memory, it just leave the blank space for each ?. Now if we require to change the value only blank space if filled with new values.
  • 33.
    insert into studentvalues(null, ________________, ________________ ) 0x3356A79 Memory allocation for query by PreparedStatement insert into student values(null, ‘Atul’,’Vadodara’) 0x3356A79 Query after setting the value
  • 34.
    insert into studentvalues(null, ‘Prakash’,’Delhi’) 0x3356A79 Query after changing the value. Note that the memory location of the query remains same and quotes are added.
  • 35.
    SCROLLABLE RESULTSET PreparedStatement givesus a very efficient way to create precompiled (in terms of java only) queries. Memory wastage and notorious quote problem are solved by it. The PreparedStatement was introduced in Java 1.1. At that time there is no way by which we can move backward or jump at any location in ResultSet. After the introduction of JDBC 2 the ResultSet is now scrollable. That means the cursor can move both backward and forward or jump to specific location in the ResultSet. By default, the ResultSet in not scrollable. That means Connection c=…..; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query); ResultSet rs=p.executeQuery(); // oops rs is not scrollable
  • 36.
    HOW TO MAKERESULTSET SCROLLABLE To make a ResultSet scrollable we need to use the following method to get PreparedStatement object. PreparedStatement prepareStatement(String query, int type, int concurrency); This method is defined in java.sql.Connection interface. The type parameter may have following values: Allowed values for type purpose ResultSet.TYPE_FORWARD_ONLY The resultset is not scrollable ResultSet.TYPE_SCROLL_INSENSITIVE The resultset is scrollable but not sensitive to database change ResultSet.TYPE_SCROLL_SENSITIVE The resultset scrollable and sensitive to database change
  • 37.
    The concurrency parametermay have following values: Allowed values for concurrency purpose ResultSet.CONCUR_READ _ONLY The resultset can not be used to update database ResultSet.CONCUR_UPDA TABLE The resultset can be used to update database. Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL_IN SENSITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rs=p.executeQuery(); // OK rs is scrollable but not updatable
  • 38.
    METHOD FOR SCROLLINGRESULTSET To traverse back and forth in ResultSet JDBC API provides the following methods : 1) boolean previous() : Move the cursor one row backward. If the cursor is pointing above the first row, the method returns false. 2) boolean first() : Move the cursor to the first row. 3) boolean last() : Move the cursor to the last row. 4) boolean relative(int n) : Moves the cursor to the location n from the current position. 5) void beforeFirst() : move the cursor before the first row. 6) void afterLast() : move the cursor after the last row. 7) isBeforeFirst() 8) isAfterLast(): Test whether the cursor is before the first row or the last row.
  • 39.
    UPDATABLE RESULTSET ResultSet isnormally a memory snapshot of subset of tables present inside the database and is a result of Select query. Generally it is used only for data retrieval. But sometimes while traversing the ResultSet we need to make changes in our database. By default a ResultSet is neither scrollable nor updatable. That simply imply that we can not use a ResultSet to make change in database. After the introduction of JDBC 2, ResultSet can be made scrollable as well as updatable. To make a ResultSet updatable we need to obtain ResultSet as follows: Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL_INS ENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=p.executeQuery(); // OK rs is scrollable and updatable
  • 40.
    MOVING FORWARD ANDBACKWARD Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL _INSENSITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rs=p.executeQuery(); rs.next(); // Move the cursor to first row rs.next(); // Move the cursor to second row rs.previous(); // Move the cursor to first row again rs.last(); // place the cursor to last row
  • 41.
    Col1 … ColN R00 R01 … R0N R10 R11 … R1N . . … . RM0  Col2 RM1 … RMN Effect of 2next() operation Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0 RM1 … RMN
  • 42.
     Effect of oneprevious() operation Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0 RM1 … RMN
  • 43.
    UPDATING CURRENT ROWVALUES Connection c=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL _INSENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=p.executeQuery(); rs.next(); // Move the cursor to first row rs.next(); // Move the cursor to second row rs.updateString(2,”Prakash”); // Changing the value of 2nd column of 2nd row rs.updateRow(); // Making effect permanent.
  • 44.
     Initial Value Col1 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0  Col2 RM1 … RMN Effect ofupdateXxx and updateRow(see prev example) Col1 Col2 … ColN R00 R01 … R0N R10 Prakash … R1N . . . . RM0 RM1 … RMN
  • 45.
    INSERTING NEW VALUES Connectionc=……; String query=“select * from table”; PreparedStatement p=c.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITI VE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=p.executeQuery(); rs.next(); // Move the cursor to first row rs.next(); // Move the cursor to second row String id=0; String name=“Dinesh”; int age=25; String gender-”Male”; rs.moveToInsertRow(); // Move To the Row where insertion is done rs.updateInt(1,id); // update first column rs.updateString(2,name); // update second column rs.updateInt(3,age); // update third column rs.updateString(4,gender); // update fourth column rs.insertRow(); // make the effect permanent rs.moveToCurrentRow(); // move to second row
  • 46.
    Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0 RM1 … R(M=1 R(M+1 … )) )1 RMN InsertRow Cursor moved to insert row Cursor moved back Col1 Col2 … ColN R00 R01 … R0N R10 R11 … R1N . . . . RM0 RM1 … RMN R(M+1)0 R(M+1)1 … R(M+1)N Merged R(M+1 )N
  • 47.
    UPDATABLE METHODS SUMMARY Allthe update related methods are defined in java.sql.ResultSet interface. 1) void moveToInsertRow() : moves the cursor to insert row. The insert row is a special row for inserting new data with updateXXX and insertRow methods. 2) void moveToCurrentRow(): move the cursor back from the insert row to the row that is occupied when moveToInsertRow method was called. 3) void insertRow(): insert the content of the insert row into the database and resultset. 4) void updateXxx(int colnumber,Xxx data) : update the field in the current row of the resultset. 5) void updateRow(): sends the current row updates to the database.
  • 48.
    METADATA In SQL, datathat describe the database or one of its parts are called metadata. In general, metadata gives the following information 1) 2) 3) 4) 5) What is the structure of a database? How many maximum connection that can be established concurrently to the database? How many rows are there in a table? How many columns a table has? Is ResultSet Scrollable or updatable ? There are some other advanced information we can collect from the metadata. These information, in general, are not required for application programmers. But these information are highly required for those who want to design advanced tools for database.
  • 49.
    TYPES OF METADATA Onthe basic of source of metadata, it is categorized into three types 1) Database Metadata database itself. : The information about 2) ResultSet Metadata : The information about the ResultSet. 3) PreparedStatement parameter: The information about the parameters of prepared statement.