Introduction
The JDBC™ API was designed to keep simple things simple. This means that the JDBC makes everyday database tasks easy. In this lab you will learn about how Java interacts with databases.
JDBC helps you to write java applications that manage these three programming activities:
• Connect to a data source, like a database;
• Send queries and update statements to the database;
• Retrieve and process the results received from the database in answer to your query.
In general, to process any SQL statement with JDBC, you follow these steps:
1. Establishing a connection.
2. Create a statement.
3. Execute the query.
4. Process the ResultSet object.
5. Close the connection Establishing Connections
First, establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. This
connection is represented by a Connection object.
Example:
private Connection connection = DriverManager.getConnection(
databaseUrl, databaseUsername, databasePassword );
Typically, a JDBC application connects to a target data source using two classes. One of them:
DriverManager: this fully implemented class connects an application to a data source, which is specified by a database URL. When this class first attempts to establish a connection, it
automatically loads any JDBC 4.0 drivers found within the class path.
The method DriverManager.getConnection establishes a database connection. This method requires a database URL, which varies depending on your DBMS. Example of database URL:
MySQL: jdbc:mysql://localhost:3306/, where localhost is the name of the server hosting your database, and 3306 is the port number.
This method specifies the user name and password required to access the DBMS.
Creating Statements
A Statement is an interface that represents a SQL statement. You
execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set. You need a Connection object to create
a Statement object.
Example:
private Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
Executing Queries
To execute a query, call an execute method from Statement such as the following:
• execute: returns true if the first object that the query returns is a ResultSet object.
Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly
calling Statement.getResutSet.
• executeQuery: returns one ResultSet object.
• executeUpdate: returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL
statements.
Example:
private ResultSet resultSet = statement.executeQuery( "SELECT * FROM smartphone WHERE manufacturer = '" + manufacturer + "'" );
Processing ResultSet Objects
You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the
ResultSet object. Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.
Example:
resultSet.next(); Moves the cursor forward by one row.
Closing Connections
When you are finished using a Statement, call the method Statement.close to immediately release the resources it is using. When you call this method,
its ResultSet objects are closed.
Example:
statement.close();
Your task
Read more about JDBC Basics:
http://download.oracle.com/javase/tutorial/jdbc/basics/index.html Create a new table in your database
Connect to your database with any SQL tool you like and execute the SQL script provided.
Normally your database name starts with a year plus your username, for example: 2014_xx. You should know the password. Contact CS helpdesk if you can’t connect.
Get MySQL Connector/J
1. Create lab8 folder in your home directory on Windows.
2. Go to http://dev.mysql.com/downloads/connector/j/
3. Download mysql‐connector‐java‐5.1.15.zip ZIP archive.
4. Unzip it in your lab8 folder and rename folder to jconnector.
Configure DrJava
Open DrJava. Choose: Edit – Preferences – Resource Locations.
Click “Add”. Select mysql‐connector‐java‐5.1.15‐bin.jar in your jconnector folder. Click “OK”.
Writing Java classes
1. Create Catalog.java and Smartphone.java in your Lab8 folder. Compile both classes and run Catalog.
2. Explain what method Catalog.getManufacturer does and how?
3. Research and explain 3 types of ResultSet.
4. Research and explain 2 ResultSet concurrency levels.
5. Write method Catalog.setManufacturer (Smartphone smartphone). It receives a single Smartphone object as an argument and inserts it into database. Use ResultSet.insertRow() method.
Submission
A pdf document should be created detailing the answers to the questions above. This document is due by email to [email protected], subject: CS2506 – lab8, at latest at the beginning of the next lab – 15th March.
The header of the document should include the name and student id:
UCC ID………..
Lab ……… results
Smartphone.java
public class Smartphone { private String manufacturer;
private String model;
private String os;
public Smartphone() { manufacturer = "None";
model = "None";
os = "None";
}
// Setters and Getters
public void setManufacturer( String manufacturer ) { this.manufacturer = manufacturer;
}
public String getManufacturer() { return manufacturer;
}
public void setModel( String model ) { this.model = model;
}
public String getModel() { return model;
}
public void setOs( String os ) { this.os = os;
}
public String getOs() { return os;
} }
Catalog.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
public class Catalog {
private String databaseUsername = "<your_db_username>";
private String databasePassword = "<your_db_password>";
// database driver
private String databaseDriver = "com.mysql.jdbc.Driver";
// database url
private String databaseUrl =
"jdbc:mysql://localhost:3306/<name_of_your_database>";
private Connection connection = null;
private Statement statement = null;
private ResultSet resultSet = null;
public Catalog() {
ArrayList< Smartphone > smartphoneList = new ArrayList< Smartphone >();
try {
smartphoneList = getManufacturer( "HTC" );
} catch ( Exception e ) { e.printStackTrace();
System.exit( 1 );
} // end catch
Smartphone smartphone;
//get an Iterator object for ArrayList using iterator() method.
Iterator itr = smartphoneList.iterator();
while( itr.hasNext() ) {
smartphone = (Smartphone)itr.next();
System.out.println( smartphone.getManufacturer() + " " + smartphone.getModel() + " " + smartphone.getOs() );
} // end while } // end catalog
public ArrayList< Smartphone > getManufacturer( String manufacturer ) throws Exception {
ArrayList< Smartphone > smartphoneList = new ArrayList< Smartphone >();
// connect to database and query database try {
// load database driver class Class.forName( databaseDriver );
connection = DriverManager.getConnection( databaseUrl, databaseUsername, databasePassword );
statement = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
resultSet = statement.executeQuery( "SELECT * FROM smartphone WHERE manufacturer = '" + manufacturer + "'" );
// get row data
for ( int i = 0; resultSet.next() && i < 10; i++ ) { Smartphone smartphone = new Smartphone();
smartphone.setManufacturer( resultSet.getString(
"manufacturer" ) );
smartphone.setModel( resultSet.getString( "model" ) );
smartphone.setOs( resultSet.getString( "os" ) );
// add to list
smartphoneList.add( smartphone );
} // end for
} // end try /*
if the DriverManager cannot connect to the database, method getConnection throws an exception
*/
catch( SQLException sqlException ) { sqlException.printStackTrace();
System.exit( 1 );
} // end catch
// if class loader cannot locate the driver class catch( ClassNotFoundException classNotFound ) { classNotFound.printStackTrace();
System.exit( 1 );
} // end catch finally {
statement.close();
connection.close();
return smartphoneList;
} // end finally } // end method
public static void main( String[] args ) { Catalog catalog = new Catalog();
} // end main }
SQL statement. Creates new table smartphone.
USE `<name_of_your_database>` ;
CREATE TABLE IF NOT EXISTS `<name_of_your_database>`.`smartphone` ( `id` INT NOT NULL AUTO_INCREMENT ,
`manufacturer` VARCHAR(500) NOT NULL , `model` VARCHAR(1000) NOT NULL , `os` VARCHAR(200) NOT NULL , PRIMARY KEY (`id`) )
ENGINE = InnoDB;
