JDBC
The Java Database Connectivity interface enables a Java application to connect to a relational
database in a storage independent manner. That is, JDBC presents a uniform interface to Java
applications that allows changing the database with just a few value changes in the Java code.
These are the steps necessary to establish a connection from a Java application to a MySQL
database using Eclipse.
1. Create and Load Project Folder in Eclipse:
Be sure to import the java.sql package in any class using JDBC.
Place MySQL driver on application’s CLASSPATH using Eclipse.
2. Create URL to database:
String URL = “jdbc:mysql://hostname:3306/db_name”;
Hostname may be specified as localhost (your laptop) or frodo.bentley.edu
(a DNS name) or wherever your database is hosted.
3306 refers to server port on which MySQL listens for remote Queries.
3. Load DB connector/driver into memory: (omit this section if your J-connector is rev 8 or
higher. This connector is used with MySQL 8.0 and higher.)
Class.forName(“com.mysql.jdbc.Driver”);
forName() is a static method in Class that will load the driver into a table of
available drivers.
Method may throw ClassNotFoundException, a checked exception, so needs
try/catch block.
4. Connect application to DB:
Connection con = DriverManager.getConnection(URL, username, password);
May throw SQLException, a checked exception.
5. Write SQL statement to be executed against DB:
1
J. Pepe
String query = “select * from members;”;
6. Create Statement object:
Statement stmt = con.createStatement();
May throw SQLException.
7. Execute query and get resulting set of records:
ResultSet rs = stmt.executeQuery(query);
A ResultSet is a collection containing the records returned by the query.
There is a cursor that allows you to refer to each record.
When ResultSet object created, the cursor is set to point before the first record.
Methods of ResultSet class:
.next() boolean, and moves cursor to next record.
.getString(“field name”) String
.getInt(“field name”) int
.getDouble(“field name”) double
E.g., while (rs.next()) {
String city = rs.getString(“city”);
String state = rs.setString(“state”);
System.out.printf(“%s %s%n”, city, state);
}
.isBeforeFirst() boolean , and if true, result set not empty
8. Execute a SQL command other than SELECT:
stmt.executeUpdate(SQL statement);
There may be a return value depending on the statement. E.g., insert or delete will
return the integer number of rows effected.
2
J. Pepe
9. Close connection
con.close();
Be sure to close connections when done with them. Some DBs will have a
maximum number of connections limit. May throw checked exception.
3
J. Pepe
The four SQL statements that we will work with are Select, Delete, Insert, and Update. See the
reference posted on Bb to review these statements.
Executing a Select Query returns a ResultSet which may be empty.
Executing a Delete, Update, or Insert returns an int containing the number of records modified.
To execute a Delete, Update, or Insert requires a stmt.executeUpdate() method.
E.g.,
String sql = “delete from customers where city=’Boston’;”;
int count = stmt.executeUpdate(sql);
System.out.println(“Number of records deleted = “ + count);
Run examples:
FirstMySQLExample
CreateMySQLData
MySQLSelect
MySQLReport
The MySQL J-connector jar file has to be downloaded for these examples to run. This file must
be placed on the program’s Classpath in Eclipse. You can do this with the actions right click over
program, run as, run configurations, click Classpath tab, select User Entries, add external jar,
locate the J-connector file.
For an Android application the jar file must be placed in the application /res/libs/ folder and
placed on the build path in Eclipse.
4
J. Pepe
Java Application
JDBC API
MySQL Oracle SQLServer
Connector/Driver Connector/Driver Connector/Driver
MySQL DB Oracle DB MS SQLServer
Vendors provide drivers
Applications portable across DBs
Requires java.sql package
Some applications also require javax.sql package
5
J. Pepe
Prepared Statements
Objects of the Statement class are used to execute static SQL statements containing no
parameters. I.e., all search criteria values of the SQL statement are known at compile time.
Prepared Statements are used to execute SQL statements where some values are not known
until runtime. This is like a Parameter Query in MS Access.
E.g., String sql = “select * from members where city=? and state=?;”;
? is used to specify values to be supplied at runtime. May have one or more such values in a
SQL statement.
Java contains a class, PreparedStatement, that allows you to execute a SQL statement with one
or more values supplied at runtime. The SQL statement is precompiled and stored in a
PreparedStatement object. The object can be used to execute the SQL multiple times with
parameter substitution. It begs to be used in a loop.
PreparedStatement methods to set the values have two arguments, an integer to specify which
parameter is being set, and a value for that parameter.
.setString(1, “Chicago”)
.setInt(3, 123)
To execute the SQL:
.executeQuery() - returns a ResultSet, used for a select query
.executeUpdate() - returns the int number of records modified, used to execute delete,
update, or insert statements
For example, after a connection is created to the database:
String sql = “select * from programs where city=? and fee>=?;”;
PreparedStatement pstm = con.PrepareStatement(sql);
pstm.setString(1, “Cambridge”);
pstm.setDouble(2, 50.00);
int count = pstm.executeQuery();
Run examples ReportByCity and UpdateMySQLData
6
J. Pepe
Prepared Statement Benefits:
1. Allows writing a parameterized query where parameter values are determined at
runtime.
2. Executes faster than a Statement because it’s precompiled so less work has to be done
at runtime.
3. Prevents SQL injection attacks in Java from malicious users. All parameter values are
escaped automatically by the JDBC driver. I.e., if a single or double quote is in the
parameter value, the driver makes it \’ or \” to prevent SQL from interpreting it as
ending a value and use it as an ordinary character in the value. In other words,
4. The SQL used in a Prepared Statement is precomiled on the driver. From that point on
the parameters are sent to the driver as literal values and not as executable portions of
SQL so no SQL can be injected using a parameter.
Try with Resources Block
Java 7 introduced try with resources blocks. They allow connections and streams to be open in
the resource part and then automatically closed when exiting the try block.
try (open connections or streams) {
...
} catch(SomeException e) { . . . }
//connections and streams have been automatically closed
7
J. Pepe
Metadata
In a database, metadata is information about the data and database itself, and can include:
Table field data type,
Database version number,
Relationships,
Table field names,
Number of fields in a result set,
The Data Dictionary component of the database is a centralized catalog of such information.
JDBC enables you to get metadata using the method .getMetaData().
For information about the database metadata, use
DatabaseMetaData meta = con.getMetaData(); //getMetaData() in Connection class
meta.getDatabaseProductName() à String
meta.getDatabaseProductVersion() à String
meta.getDriverName() à String
meta.getDriverVersion() à String
meta.getURL() à String
Search on DatabaseMetaData class to see all database metadata available.
For information about a ResultSet metadata, use
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData meta = rs.getMetaData(); //getMetaData() in ResultSet class
meta.getColumnCount() à int
meta.getColumnLabel(int) à String
meta.getColumnTypeName(int) à String
Search on ResultSetMetaData class to see all result set metadata available.
Run example MySQLMetaData
8
J. Pepe
MySQL Data Types
Access MySQL
Text, length attribute VARCHAR(length)
Number, integer attribute INTEGER
Number, double attribute DOUBLE
Yes/No BOOLEAN
Date/Time, medium date DATE “yyyy-mm-dd”
9
J. Pepe
