|
"Database-neutral access" interface in java for connecting to database and executing SQL statements
|
|
It is designed after ODBC(Open Database connectivity) but with java-specific features
|
|
Dependent on database-driver availability
|
|
It is also an API for creating the low-level JDBC drivers, which do the actual connecting/transacting with data sources
|
|
Based on the X/Open SQL Call Level Interface(CLI) that defines how client/server interactions are implemented for database systems
|
|
Programmer writes only one database interface, using jdbc, the pogram can access any data source without recoding
|
|
The following are some of the commonly used JDBC API calls
|
|
public class Date
|
|
This class extends the java.util.Date object. But unlike the java.util.Date, which stores time, this class stores the day,year and month.
|
|
StringtoString()
|
|
Formats a Date object as YYYY-MM-DD
|
|
valueOf(String str)
|
|
Converts a String str to an sql.Date object
|
|
public class DriverManager
|
|
This class is used to load a JDBC Driver . The main methods are
|
|
getConnection(String URL,String username ,String password)
|
|
throws SQLException
|
|
After instantiating a driver using Class.forName(), a connection
|
|
object is obtained using getConnection() with a driver URL,
|
|
username and password.
|
|
getDriver(String URL) throws SQLException
|
|
Finds a driver from the registered JDBC drivers
|
|
public interface Callable Statement
|
|
This is a primary interface to access stored procedures on a database. The get methods in this interface are identical in functionality returning various datatypes like getInt, getDate etc.
|
|
public interface Connection
|
|
This is a high level class used to interact with the database. The main methods are
|
|
clearWarmings() throws SQLException - clears warnings for the connection
|
|
close() throws SQLException - close the connection
|
|
commit() throws SQLException - commits the transaction
|
|
createStatement() throws SQLException - create statements for queries
|
|
getMetaData() throws SQLEXception - returns DatabaseMetaData
|
|
isClosed() throws SQLException - Returns true if connection is closed
|
|
The DriverManager class is used to open a connection to a database via a JDBC Driver, which must be registered with the DriverManager
|
|
DriverManager chooses from a given listof available drivers to suit the explicit type of database connection
|
|
DriverManager maps URL's to drivers
|
|
URL syntax
|
|
jdbc:<subprotocol>:<subname>
|
|
e.g jdbc:msql:naos://naos.npac.syr.edu:2007//stuff
|
|
The above syntax specifies the transport to use (jdbc), the database type (msql), the server name (naos), the port (2007), and the database to connect to (stuff).
|
|
|
|
JDBC drivers fit into one of four categories:
|
|
1.The JDBC-ODBC bridge provides JDBC access via mostly ODBC drivers.
|
|
2.A native-API partly-Java driver converts JDBC calls into calls to the client API for Oracle, Sybase, Informix, DB2, or other DBMS.
|
|
( E.g. WebLogic Oracle driver)
|
|
3.A net-protocol all-Java driver translates JDBC calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all its Java clients to many different databases.
|
|
4.A native-protocol all-Java driver converts JDBC calls into the network protcol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. (also WebLogic)
|
|
Some of the leading database, connectivity and tools vendors who have already endorsed the JDBC API and the DBMS's they support are
|
|
Borland International Inc. - InterBase 4.0
|
|
IBM - DB2 Version 2
|
|
Imaginary Software Inc. - mSQL
|
|
Intersolv - Oracle and Sybase
|
|
Javasoft - Several dozen through ODBC drivers
|
|
Sybase Inc. - Sybase SQL server
|
|
Weblogic Inc., etc. - Oracle, Sybase, MS SQL server
|
|
The JDBC technologies that are currently being explored at NPAC are :
|
|
mSQL Database - JDBC driver from Imaginary
|
|
- a lightweight SQL engine that supports a subset of ANSI SQL
|
|
- shareware product that can be built on most Unix OS and Win32 OS
|
|
- one of the first database products to be supported by a Java API
|
|
- avialable from anonymous ftp ftp://bond.edu.au/pub/Minerva/msql
|
|
- JDBC driver for mSQL database can be downloaded from
|
|
http://www.imaginary.com/Java/
|
|
Oracle - JDBC driver from Weblogic
|
|
- Weblogic has got a set of proprietary solutions to access DB
|
|
- Works with Oracle, Sybase, MS SQL server
|
|
- Classes built on top of JDBC to handle result datasets etc.
|
|
- Access the DB either through JDBC application or applets
|
|
- Test versions of their products can be downloaded from
|
|
http://www.weblogic.com
|
|
import java.net.URL;
|
|
import java.sql.*;
|
|
import imaginary.sql.*;
|
|
class Select {
|
|
public static void main (String argv[] ) {
|
|
try {
|
|
//load the class imaginary/sql/iMsqlDriver
|
|
Class.forName (" imaginary.sql.iMsqlDriver");
|
|
String url = jdbc:msql://host.npac.syr.edu:4333/test";
|
|
//connect to the test database using the driver URL
|
|
Connection conn = DriverManager.getConnection( url, "scott", "tiger");
|
|
//create the query statement and get the result set
|
|
Statement stmt = conn.createStatement();
|
|
ResultSet rs = stmt.executeQuery (" select * from
|
|
test_table order by id");
|
|
System.out.println ("Got results :");
|
|
//print the results in a loop
|
|
while (rs.next() ) {
|
|
int a = rs.getInt(1);
|
|
String str = rs.getString(2);
|
|
System.out.println("key = " + a);
|
|
System.out.println("str = " + str); }
|
|
package tutorial.jdbc.oracle;
|
|
import java.sql.*;
|
|
public class myTest {
|
|
public static void main (String argv[] ) throws Exception
|
|
{ //load the driver class from the local CLASSPATH
|
|
Class.forname("weblogic.jdbc.oci.Driver");
|
|
//connect to the database using the driver url and
|
|
// other properties like username, password and server
|
|
Connection conn =
|
|
DriverManager.getConnection("jdbc:weblogic:oracle:kayak",
|
|
"scott", "tiger")
|
|
//statement class is instantiated using the connection
|
|
// class for executing queries
|
|
Statement stmt = conn.createStatement();
|
|
stmt.execute("select * from empdemo");
|
|
//result set or hit list is obtained after
|
|
// executing the query
|
|
//and using a cursor it is displayed
|
|
ResultSet rs = stmt.getResultSet ( );
|
|
while (rs.next () ) {
|
|
System.out.println( rs.getString(1) + "-" +
|
|
rs.getString(2) + "-" + rs.getString(3) );
|
|
}
|
|
//get the metadata using the result set
|
|
ResultSetMetaData rsmd = rs.getMetaData ();
|
|
System.out.println("Number of Columns:"
|
|
+ rsmd.getColumnCount() );
|
|
//display the metadata information like max size. column type name etc.
|
|
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
|
|
System.out.println("Column Name :" + rsmd.getColumnName(i));
|
|
System.out.println("Nullable :" + rsmd.isNullable(i));
|
|
System.out.println("Precision :" + rsmd.getPrecision(i));
|
|
System.out.println("Scale :" + rsmd.getScale(i));
|
|
System.out.println("Size :" + rsmd.getColumnDisplaySize(i));
|
|
System.out.println("Column Type :" + rsmd.getColumnType(i));
|
|
System.out.println("ColType Name :" + rsmd.getColumnTypeName(i));
|
|
System.out.println(" ");
|
|
}
|
|
//close the query connection states and then close the database connection
|
|
stmt.close();
|
|
conn.close();
|
|
} // end main
|
|
} // end myTest class
|
|
Connection con = DriverManager.getConnection (
|
|
url, "my-user", "my-passwd");
|
|
|
|
// If we were unable to connect, an exception
|
|
// would have been thrown. So, if we get here,
|
|
// we are successfully connected to the URL
|
|
|
|
// Get the DatabaseMetaData object and display
|
|
// some information about the connection
|
|
|
|
DatabaseMetaData dma = con.getMetaData ();
|
|
|
|
System.out.println("\nConnected to " + dma.getURL());
|
|
System.out.println("Driver " +
|
|
dma.getDriverName());
|
|
System.out.println("Version " +
|
|
dma.getDriverVersion());
|
|
System.out.println("");
|
|
// Create a Statement object so we can submit
|
|
// SQL statements to the driver
|
|
Statement stmt = con.createStatement ();
|
|
// Submit a query, creating a ResultSet object
|
|
ResultSet rs = stmt.executeQuery (query);
|
|
// Display all columns and rows from the result set
|
|
dispResultSet (rs); // see later
|
|
// Close the result set
|
|
rs.close();
|
|
// Close the statement and then the connection
|
|
stmt.close();
|
|
con.close();
|
|
}
|
|
catch (SQLException ex) {
|
|
// A SQLException was generated. Catch it and
|
|
// display the error information. Note that there
|
|
// could be multiple error objects chained
|
|
// together
|
|
System.out.println ("\n*** SQLException caught***\n");
|
|
while (ex != null) {
|
|
System.out.println ("SQLState: " +
|
|
ex.getSQLState ());
|
|
System.out.println ("Message: " +
|
|
ex.getMessage ());
|
|
System.out.println ("Vendor: " +
|
|
ex.getErrorCode ());
|
|
ex = ex.getNextException ();
|
|
System.out.println ("");
|
|
}
|
|
}
|
Abstract * Foil Index for this file
