All Overview of Interview Questions about JAVA language
JDBC
• JDBC is a Java API for database connectivity
• It is not the same as ODBC but implements a
similar specification
• JDBC enables programmers to write java
applications that
• Connect to a database
• Send queries and update statements to the database
• Retrieve and process the results received from the
database in answer to the query
----------------------------------------------------------------------------------------------------
Difference of SQL and Other Languages
SQL Language
• Combining SQL and
another language
• Use SQL to run
queries on the
database
• Use another language
(Java, C, etc) to do
the rest of the work:
e.g. user interface, or
complicated
processing
• Need an interface
between the two
Other Language
• ODBC (Open DB
Connectivity) is a
common standard
• Provides an API which
is widely supported
• Allows you to pass
queries to a database,
and return the results
to a program
--------------------------------------------------------------------------------------------------------------
• JDBC consists of:
• The JDBC™ API proper: interfaces, classes
and methods for executing SQL
statements, retrieving results, and
propagating changes back to the database
• JDBC Driver Manager: a class that defines
objects which can connect Java
applications to a JDBC driver.
• JDBC Test Suite
• JDBC-ODBC Bridge
Using JDBC
• Basic steps when using JDBC
• Register a database driver
• Open a connection
• Pass some queries to the database
• Process the results as needed
• Close the connection
• Deal with any error
• Preamble: import java.sql.*;
-------------------------------------------------------------------------------------------------------
Register a Driver
• We need to register an appropriate
driver with the DriverManager
• There is a different driver for each DBMS
• We’ll need to use the driver for Oracle:
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver()
);
--------------------------------------------------------------------------------------------------------------
Open a Connection
• Next we open a connection to the database
from the DriverManager
• We give the address of the database, a
username and a password
Connection conn = DriverManager.getConnection (
"jdbc:oracle:thin:@oracle.cs.nott.ac.uk:1521:maindb",
“xxx06u", “somepassword");
Your
username
Your sqlplus
password
Passing Queries to the DB
• Now we can send
• Now we can send
queries to the DB
• We do this through a
Statement object
• Each Statement can
deal with one query at
a time
• A single Connection
can have several
statements open at
any time
------------------------------------------------------------------------------------------------------
• Statement objects
• Are created from a
Connection
• The executeUpdate()
method runs a query
that doesn’t return
any results (UPDATE,
CREATE TABLE, etc)
• executeQuery() is
used when a result is
expected
-----------------------------------------------------------------------------------------------------
Processing Query Results
• When a query
returns a result
• We use the Statement
object’s executeQuery
method
• The results are put in
a ResultSet object
• Each Statement can
deal with a single
ResultSet at any one
time
• The ResultSet object
• Is essentially a table
• Has a cursor that
points to the current
row of data
• Initially the cursor is
positioned before the
first row
• The next() method
moves to the next
row, and returns false
if there isn’t one
------------------------------------------------------------------------------------------------
Processing Query Results
Statement stresult = conn.createStatement();
ResultSet fruit = stresult.executeQuery(
"SELECT * FROM Fruit"
);
while(fruit.next()) {
System.out.println(
fruit.getString("Name")+ ", " +
fruit.getInt("Amount"));
}
fruit.close();
-----------------------------------------------------------------------------------------------------
Working with ResultSets
• We get values from the ResultSet with
• getInt()
• getString()
• getDouble()
• etc.
• Each takes either
• The name of the column as a String, or
• The index of the column as an integer
---------------------------------------------------------------------------------------------
Dealing with Errors
• Things can go wrong
with all of this
• Incorrect SQL
statements
• DBMS might not be
available
• DBMS might not
support some features
• If something goes
wrong then an
SQLException occurs
---•
If an exception
thrown:
• We need to deal with
it as best we can
• Make sure any
database objects are
closed
• If a connection is left
open it can consume
resources and might
interfere with later
use of the database
--------------------------------------------------------------------------------------------------------------------
Exception Handling
// Declaration of any database objects
try {
// Some database code
} catch (Exception e) {
// Error reporting etc.
} finally {
// Make sure all database objects are
// closed and cleaned up
}
Closing Objects
• To make sure the
object is closed
• See if the object
exists
• If it does, call its close
method
• This might throw an
exception itself, which
needs to be caught
• At some stage we
have to stop handling
the exceptions
Connection conn;
try {
...
} finally {
if (conn != null) {
try {
conn.close();
} catch (...) {
// what to do?
}
}
}
-------------------------------------------------------------------------------------------------------
Good...
ReplyDelete