JDBC Enterprise Systems Programming JDBC Java Database Connectivity

JDBC Enterprise Systems Programming

JDBC Java Database Connectivity p Database Access Interface p n n p provides access to a relational database (by allowing SQL statements to be sent and executed through a Java program) JDBC package: set of Java classes that facilitate this access (java. sql. *) Comes with JDK (since 1. 1)

JDBC Driver Need a driver, specific to the DB product, to mediate between JDBC and the database n the driver is a Java class that needs to be loaded first Java Program - load driver - establish connection - send SQL statements Relational DBMS

JDBC-ODBC Bridge Driver that interfaces with ODBC (Object Database Connectivity--also an access interface) p Easiest way to access databases created by Microsoft products p n n register database as an ODBC data source use JDBC-ODBC bridge as the JDBC driver (included in JDK distribution)

Key Classes in JDBC p Connection n p Statement n p need to create an instance of this class when establishing a connection to the database for issuing SQL statements Result. Set (interface) n a Result. Set object represents the table returned by an SQL select statement

Establishing a Connection Use the get. Connection() method n n n under the Driver. Manager class String argument: "jdbc: driver: name” returns a Connection object Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Driver”); // above line loads the jdbc-odbc driver String dbname = “jdbc: odbc: My. DB”; Connection c = Driver. Manager. get. Connection(dbname);

Creating a Statement Object Execute the create. Statement() method on the Connection object n n returns a Statement object afterwards, run methods on the Statement object to execute an SQL statement Statement s = c. create. Statement();

Methods of the Statement Class p execute. Query() n n p requires a String argument (a select statement) returns a Result. Set object execute. Update() n n requires a String argument (an insert, update, or delete statement) returns an int (row count, in most cases)

The Result. Set Interface A Result. Set object represents the table returned by the select statement sent p Navigation/retrieval methods p n n next(): moves to the next row (first row if called for the first time), returns false if no rows remain get. XXX() methods return the value of a field for the current row

get Method Example: get. Int() Result. Set rs; rs = s. execute. Query(“SELECT * FROM ORDER”); rs. next(); // gets the first row (use in a loop for multiple rows) // suppose the ORDER table has an integer field // called quantity int myvar = rs. get. Int(“quantity”); // if you knew that quantity is the 2 nd field in the table myvar = rs. get. Int(2); Actually, this example will produce an SQL syntax error because ORDER is a reserved word in SQL. To fix, use this string instead: “SELECT * FROM [ORDER]”

Exercise p Create a Microsoft Access table n p Add an ODBC data source n n p insert sample rows use the Microsoft Access driver associate with the created database Create a Java program n n use JDBC-ODBC bridge create a loop that lists all rows of the table

Summary JDBC allows you to write Java programs that manipulate a database p A driver (often a separate product) is required that facilitates access p Key classes: Connection, Statement, and Result. Set p Other features: metadata, parameterized statements, and stored-proc invocation p