Download JDBC Connection: Understanding Two-tier Architecture, Setup, and Usage and more Slides Java Programming in PDF only on Docsity!
JDBC ConnectionCalin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc- tutorial.com/
JDBC Connection
Calin Curescu, following http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/
12 pages JDBC ConnectionCalin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc- tutorial.com/
2 of 12
Overview
JDBC Connection Calin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/ 3 of 12
Two-tier Architecture
- The top level
- visible to application programs
- presents an abstract interface for connecting to and using database engines
- does not depend on particular engines
- The lower level
- consists of drivers for individual database engines
- Each driver handles the details necessary to map the abstract application interface onto operations that a specific engine will understand
JDBC Connection Calin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/ 4 of 12
Setup
- Download and add the JDBC driver to the CLASSPATH
- E.g. : mysql-connector-java-5.0.4-bin.jar
- Don’t forget to import the java.sql package
JDBC Connection Calin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/ 5 of 12
Loading a JDBC driver
try { Class.forName(jdbc.DriverXYZ); }
catch(Exception x){ System.out.println( "Unable to load the driver class!" ); }
- For example, for MySQL the driver is
JDBC Connection Calin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/ 6 of 12
Connecting to a database
try{ Connection dbConnection = DriverManager.getConnection(url, "loginName", "Password"); } catch( SQLException x ){ System.out.println( "Couldn't get connection!" ); }
- JDBC URL syntax
- jdbc: :
- Each driver has its own subprotocol
- Each subprotocol has its own syntax for the source
- E.g. : jdbc:mysql://localhost/test
- NOTE - The database itself has to be created beforehand
- With vendor-specific tools
JDBC ConnectionCalin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc- tutorial.com/
7 of 12
Database connection object
- Represents a session/connection with a specific database
- Within the context of a Connection, SQL statements are executed and results are returned.
- An application can have
- one or more connections with a single database, or
- many connections with different databases.
- A Connection object
- Provides metadata (i.e. information) about the database, tables, and fields.
- Contains methods to deal with transactions.
JDBC ConnectionCalin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc- tutorial.com/
8 of 12
Creating a jdbc Statement object
Statement statement = dbConnection.createStatement();
- Statement interface defines methods that are used to interact with database via the execution of SQL statements
- Three types of Statements
- Statement
- Prepared Statement
- Pre-compiled query, with or without parameters
- Callable Statement
- Execute a call to a database stored procedure
JDBC Connection Calin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/ 9 of 12
Executing a SQL statement
- Statement class has three methods for executing
- executeQuery()
- Retreives data from database
- Returns a ResultSet
- For a SELECT statement
- executeUpdate()
- Creates and modifies tables
- Does not return a ResultSet
- execute()
- If you don’t know which one of the above the the String representsing the SQL statement is
- Returns true if the statement was a SELECT
JDBC Connection Calin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/ 10 of 12
Update Queries
Statement s = conn.createStatement (); int count; s.executeUpdate ("DROP TABLE IF EXISTS animal"); s.executeUpdate ( "CREATE TABLE animal (" + "id INT UNSIGNED NOT NULL AUTO_INCREMENT," + "PRIMARY KEY (id)," + "name CHAR(40), category CHAR(40))"); count = s.executeUpdate ( "INSERT INTO animal (name, category)" + " VALUES" + "('snake', 'reptile')," + "('frog', 'amphibian')," + "('tuna', 'fish')"); s.close (); System.out.println (count + " rows were inserted");
JDBC Connection Calin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/ 11 of 12
ResultSet
- ResultSet
- Provides access to a table of data generated by executing a Statement.
- The table rows are retrieved in sequence.
- The next() method is used to successively step through the rows of the tabular results.
- ResultSetMetaData
- Holds information on the types and properties of the columns in a ResultSet.
- Constructed from the Connection object.
JDBC Connection Calin Curescu, parts from http://www.kitebird.com/articles/jdbc.html and http://www.jdbc-tutorial.com/ 12 of 12
Execute Queries
Statement s = conn.createStatement (); s.executeQuery ("SELECT id, name, category FROM animal"); ResultSet rs = s.getResultSet (); int count = 0; while (rs.next ()) { int idVal = rs.getInt ("id"); String nameVal = rs.getString ("name"); String catVal = rs.getString ("category"); System.out.println ( "id = " + idVal + ", name = " + nameVal + ", category = " + catVal); ++count; } rs.close (); s.close (); System.out.println (count + " rows were retrieved");
