This post discusses the basics of using JDBC (Java Database Connectivity) to interface Java with a JDBC-enabled database. Here, PostrgreSQL will be said database. To follow along with this tutorial, you’ll need to download PostgreSQL and its JDBC driver, create a new Java project in your IDE, and add the JDBC jar to its library (NetBeans: right-click libraries in the project and select “Add JAR/Folder…”, in Eclipse: right-click the project and go to build path and select libraries). Generally, I create a directory named “lib” inside each project and keep relevant jars there. This allows for local references and ensures we don’t “lose” the jar if it’s moved, deleted, or updated (reverse-compatibility issues) from a global location.
PostgreSQL Setup
After PostgreSQL is install, execute the following statements. They will create a new schema in public named test_schema, add the user test_user, and create and populate the tables.
-- create test user and change to that role
SET ROLE postgres;
CREATE ROLE test_user WITH LOGIN PASSWORD 'test_pass' SUPERUSER;
SET ROLE test_user;
-- create new schema and set search path
CREATE SCHEMA test_schema;
SET SEARCH_PATH TO test_schema;
-- create tables and add data
CREATE TABLE patients (
patient_id SERIAL,
gender CHAR(1) NOT NULL,
race CHAR(1),
birth_year INTEGER,
CONSTRAINT patients_pk PRIMARY KEY (patient_id),
CONSTRAINT patients_gender_ck CHECK (gender IN ('M','F','T','U'))
);
INSERT INTO patients (gender, race, birth_year) VALUES ('M', 'W', '1980');
INSERT INTO patients (gender, race, birth_year) VALUES ('M', 'B', '1985');
INSERT INTO patients (gender, race, birth_year) VALUES ('F', 'A', '1976');
CREATE TABLE encounters(
encounter_id SERIAL,
patient_id INTEGER NOT NULL,
admit_date DATE NOT NULL,
department_name VARCHAR(15),
age INTEGER,
CONSTRAINT encounters_pk PRIMARY KEY (encounter_id),
CONSTRAINT encounters_patients_fk FOREIGN KEY (patient_id) REFERENCES patients (patient_id),
CONSTRAINT encounter_age_ck CHECK(age BETWEEN 0 AND 120)
);
INSERT INTO encounters (patient_id, admit_date, department_name, age) VALUES (1, '2012-02-11', 'clinic_1', 32);
INSERT INTO encounters (patient_id, admit_date, department_name, age) VALUES (2, '2012-03-09', 'emergency', 27);
INSERT INTO encounters (patient_id, admit_date, department_name, age) VALUES (1, '2013-09-16', 'oncology', 33);
INSERT INTO encounters (patient_id, admit_date, department_name, age) VALUES (2, '2013-09-16', 'clinic_2', 28);
INSERT INTO encounters (patient_id, admit_date, department_name, age) VALUES (1, '2013-09-21', 'radiology', 33);
INSERT INTO encounters (patient_id, admit_date, department_name, age) VALUES (3, '2013-09-26', 'clinic_1', 37);
INSERT INTO encounters (patient_id, admit_date, department_name, age) VALUES (3, '2013-09-27', 'emergency', 37);
CREATE TABLE disease_types (
disease_id SERIAL,
name VARCHAR(15),
icd9 VARCHAR(8),
start_date DATE,
end_date DATE,
parent_disease_id INTEGER,
CONSTRAINT disease_types_pk PRIMARY KEY(disease_id),
CONSTRAINT parent_disease_id_fk FOREIGN KEY(parent_disease_id) REFERENCES disease_types(disease_id)
);
INSERT INTO disease_types (name, icd9, start_date, end_date, parent_disease_id)
VALUES ('name1', 'icd9_1', '2013-09-01', '9999-12-31', null);
INSERT INTO disease_types (name, icd9, start_date, end_date, parent_disease_id)
VALUES ('name2', 'icd9_2', '2013-09-02', '9999-12-31', null);
INSERT INTO disease_types (name, icd9, start_date, end_date, parent_disease_id)
VALUES ('name3', 'icd9_3', '2013-09-03', '9999-12-31', 1);
INSERT INTO disease_types (name, icd9, start_date, end_date, parent_disease_id)
VALUES ('name4', 'icd9_4', '2013-09-04', '9999-12-31', 2);
INSERT INTO disease_types (name, icd9, start_date, end_date, parent_disease_id)
VALUES ('name5', 'icd9_5', '2013-09-05', '9999-12-31', 2);
INSERT INTO disease_types (name, icd9, start_date, end_date, parent_disease_id)
VALUES ('name6', 'icd9_6', '2013-09-06', '9999-12-31', 3);
CREATE TABLE diseases (
encounter_id INTEGER,
disease_id INTEGER,
CONSTRAINT diseases_pk PRIMARY KEY(encounter_id, disease_id),
CONSTRAINT diseases_encounters_fk FOREIGN KEY(encounter_id) REFERENCES encounters(encounter_id),
CONSTRAINT diseases_disease_types_fk FOREIGN KEY(disease_id) REFERENCES disease_types(disease_id)
);
-- encounter 3 is not assigned any diseases and disease_type 4 is not assigned to any encounters
INSERT INTO diseases VALUES (1,1);
INSERT INTO diseases VALUES (1,2);
INSERT INTO diseases VALUES (1,3);
INSERT INTO diseases VALUES (2,5);
INSERT INTO diseases VALUES (4,1);
INSERT INTO diseases VALUES (4,2);
INSERT INTO diseases VALUES (4,3);
INSERT INTO diseases VALUES (4,5);
INSERT INTO diseases VALUES (4,6);
INSERT INTO diseases VALUES (5,3);
INSERT INTO diseases VALUES (5,5);
INSERT INTO diseases VALUES (6,2);
INSERT INTO diseases VALUES (7,2);
INSERT INTO diseases VALUES (7,3);To remove everything created above, execute the following.
-- cleanup
DROP SCHEMA test_schema CASCADE;
SET ROLE postgres;
DROP ROLE test_user;Java Database Class
The workhorse of this tutorial is the database class. There are a few key elements to discuss before getting into the code.
First, you must know the JDBC URL, which includes the protocol, host, port number, and database name. Second you must have the JDBC driver path. Third, JDBC requires a valid username and password. In this example these elements can be found in lines 21-29 for simplicity. However, for safety reasons, you would be best suited using a configuration file via the Properties class. Either way, you should hash your credentials (post on message digests) in case the file ever reaches the open.
Now onto the code.
The variable on line 26 is the fixed schema name (tied to the database scripts previously discussed). It is labeled public so it can be read from the test class (to follow), however, we ensure integrity and security of the database by declaring it final.
PreparedStatements are a way to create an easily to modify SQL template. Line 33 is the name of the PreparedStatement with the query specified on line 34. Note the “?”s in the query; these are the variable to replace in 1-indexed order (JDBC is 1-indexed instead of 0-indexed, so the first “?” is 1 and so on).
The constructor (lines 46-53) attempts to connect to the database and prepares the statement (assigns the query to the PreparedStatement). The connect() method on lines 48-67 is quite simple. First, it locates the correct class in the JDBC jar (line 60), then it connects to the database using the full URL, username, and password (line 61). Line 62 disables auto commit for statements modifying the data. By disabling this feature, the user has explicit control over commits and rollbacks. As commits are off, the commit() method becomes necessary. The close() method (lines 83-90) first commits any uncommitted transactions and closes the connection.
The PreparedStatement is set and executed using insertDiseases(...) on lines 97-105. The information is set using the database data type (e.g., setInt(...) in this example) and order specified by the “?”s in the query string. Once set, the statement can be batched for later execution (allows for bulk processing) or performed directly – here the query is executed immediately via execute().
User-specified queries are processed by execute(sql) (lines 111-117) and executeQuery(sql) (lines 124-131). The former executes pretty much any type of query, but requires additional steps to get the results. The latter is simply a shortcut to the results.
Finally, the last three methods print the results in row/column form.
import static java.lang.System.out;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
* This is an example JDBC database class. It connects to a PostgreSQL
* database (that's easily changed by setting the connection details)
* consisting of four tables: patients, encounters, diseases, and
* disease_types (a dictionary of ICD-9-CM v1, v2 codes).
* @author Ray Hylock
*/
public class Database {
// connection details
private final String JDBC = "jdbc:postgresql";
private final String HOST = "localhost";
private final String PORT = "5432";
private final String DATABASE = "postgres";
private final String URL = JDBC+"://"+HOST+":"+PORT+"/"+DATABASE;
public final String SCHEMA = "test_schema";
private final String DRIVER = "org.postgresql.Driver";
private final String USERNAME = "test_user";
private final String PASSWORD = "test_pass";
private Connection conn;
// prepared statements
private PreparedStatement insertDiseases;
private final String insertDiseasesQuery = "INSERT INTO " + SCHEMA
+ ".diseases VALUES (?,?)";
/**
* This value replaces PostgreSQL {@code TEXT} data type's width of
* 2,147,483,647 with a more reasonable default.
*/
private final int pgTextWidthReplace = 35;
/**
* Creates a new {@link Database} instance and attempts to connect.
*/
public Database(){
try {
connect();
insertDiseases = conn.prepareStatement(insertDiseasesQuery);
} catch (SQLException ex) {
Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Attempts to connect to the database.
*/
private void connect(){
try {
Class.forName(DRIVER).newInstance();
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
conn.setAutoCommit(false);
} catch (ClassNotFoundException | InstantiationException
| IllegalAccessException | SQLException ex) {
Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Commit to the database.
*/
public void commit(){
try {
conn.commit();
} catch (SQLException ex) {
Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Close the database connection.
*/
public void close(){
try {
commit();
conn.close();
} catch (SQLException ex) {
Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Inserts a DISEASES record for encounter and disease id's.
* @param encounterID encounter id
* @param diseaseID disease id
*/
public void insertDiseases(int encounterID, int diseaseID){
try {
insertDiseases.setInt(1, encounterID);
insertDiseases.setInt(2, diseaseID);
insertDiseases.execute();
} catch (SQLException ex) {
Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Executes a query.
* @param sql query to execute
*/
public void execute(String sql){
try {
conn.createStatement().execute(sql);
} catch (SQLException ex) {
Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Executes the query and returns the {@link ResultSet} or {@code null}.
* @param sql query to execute
* @return {@link ResultSet} or {@code null}
*/
public ResultSet executeQuery(String sql){
try {
return conn.createStatement().executeQuery(sql);
} catch (SQLException ex) {
Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
/**
* Prints the contents of a {@link ResultSet}. All data will be left aligned
* and PostgreSQL {@code TEXT} data types will be set to a width of
* {@link #pgTextWidthReplace}. This is not comprehensive as it does how
* handle LOB output.
* @param rs the {@link ResultSet} to print
* @throws SQLException
*/
public void printResults(ResultSet rs) throws SQLException{
printResults(rs, false, pgTextWidthReplace);
}
/**
* Prints the contents of a {@link ResultSet}. If {@code autoAlign} is
* {@code true}, then it will try to align the text based on its data type
* (note: the header will always be left aligned). PostgreSQL {@code TEXT}
* data types will be set to a width of {@link #pgTextWidthReplace}. This is not
* comprehensive as it does how handle LOB output.
* @param rs the {@link ResultSet} to print
* @param autoAlign if {@code true} it will try to auto align the
* output by data type
* @throws SQLException
*/
public void printResults(ResultSet rs, boolean autoAlign) throws SQLException {
printResults(rs, autoAlign, pgTextWidthReplace);
}
/**
* Prints the contents of a {@link ResultSet}. If {@code autoAlign} is
* {@code true}, then it will try to align the text based on its data type
* (note: the header will always be left aligned). PostgreSQL {@code TEXT}
* data types will be set to a width of {@code pgTextWidthReplace}. This is
* not comprehensive as it does how handle LOB output.
* @param rs the {@link ResultSet} to print
* @param autoAlign if {@code true} it will try to auto align
* the output by data type
* @param pgTextWidthReplace the PostgreSQL {@code TEXT} data type has
* width 2147483647; this value replaces it
* with a reasonable width
* @throws SQLException
*/
public void printResults(ResultSet rs, boolean autoAlign,
int pgTextWidthReplace) throws SQLException {
// get metadata
ResultSetMetaData rsmd = rs.getMetaData();
// right align PostgreSQL data types
HashSet<String> right = new HashSet<String>();
right.add("float"); right.add("float4"); right.add("int");
right.add("int2"); right.add("int4"); right.add("int8");
right.add("money"); right.add("numeric"); right.add("serial");
right.add("serial2"); right.add("serial4");
// column padding
int padding = 2;
// determine column widths and alignment by data type
int columns = rsmd.getColumnCount();
String names[] = new String[columns];
int widths[] = new int[columns];
int alignments[] = new int[columns];
int totalWidth = 0;
// iterate over metadata: JDBC is 1-indexed
for (int c = 1; c <= columns; c++) {
names[c-1] = rsmd.getColumnName(c);
String dt = rsmd.getColumnTypeName(c);
if(dt.equals("text")){
widths[c-1] = pgTextWidthReplace;
alignments[c-1] = -1;
} else {
int nameLen = names[c-1].length();
int len = rsmd.getPrecision(c) + rsmd.getScale(c);
widths[c-1] = ((nameLen > len) ? nameLen : len) + padding;
alignments[c-1] = (autoAlign && right.contains(dt)) ? 1 : -1;
}
totalWidth += widths[c-1];
}
// print header
for(int i=0; i<names.length; i++)
out.format("%-"+widths[i]+"s", names[i]);
// print carriage return and line separator
out.println("\n"+(new String(new char[totalWidth])).replace("\0", "-"));
// print data
while(rs.next()){
// print data: JDBC is 1-indexed
for (int r = 1; r <= rsmd.getColumnCount(); r++)
out.format("%"+(alignments[r-1]*widths[r-1])+"s", rs.getString(r));
out.println(); // print carriage return
}
}
}Java Database Test Class
The following tests the features discussed in the prior section. The database object is created and a connection established on line 6. Line 10 adds a Diseases entry for encounter_id 7 and disease_id 5 using a SQL INSERT statement. Line 13 also adds data to Diseases, but does so via the PreparedStatement. At this point, neither record has been “saved” to the database, thus we call commit() on line 16. The remainder of the example executes SELECT statements and processes the results.
The first two (lines 23-24 and 27-34), simply select data from the database using standard SQL, calling the printResults(...) method to display the results. The first retrieves all data from the PATIENTS table, while the second collects all data from the database. The third examines post processing of the ResultSet. Yes, the desired output could have easily been captured using a single SQL expression, but the point is to highlight manual processing of the results. Lines 40-41 retrieves all patient data (query though execution). Lines 42-45 process the results. The if statement on line 43 grabs the results for the column named “gender” (PostgreSQL’s metadata is lowercase). If the value is M (which is how the database records “male”), then the patient_id is output via line 44.
Lastly, line 48 closes the connection to the database. This might not seem all that important, but a database allows only a limited number of connections. If you fail to close a connections while continually opening more, you will exhaust the allotted pool and the process will fail. So remember to close all connections when finished.
import java.sql.ResultSet;
import java.sql.SQLException;
public class DatabaseTest {
public static void main(String[] args) throws SQLException {
// create a new database instance (attempts to connect)
Database db = new Database();
/* two options to add data to DISEASES */
// 1: SQL statement
db.execute("INSERT INTO " + db.SCHEMA + ".diseases VALUES (7,5)");
// 2: prepared statement
db.insertDiseases(7, 6);
// commit the previous inserts
db.commit();
/* print variables */
boolean autoAlign = false; // false for left, true for auto
int pgTextWidthReplace = 20; // replace PostgreSQL default TEXT width
/* print patients */
String query = "SELECT * FROM " + db.SCHEMA + ".patients";
db.printResults(db.executeQuery(query), autoAlign, pgTextWidthReplace);
/* print all data */
System.out.println(); // simply a spacer
query = "SELECT *\n" +
"FROM "+db.SCHEMA+".patients p, "+db.SCHEMA+".encounters e, " +
db.SCHEMA + ".diseases d, "+db.SCHEMA+".disease_types t\n" +
"WHERE p.patient_id = e.patient_id\n" +
"AND e.encounter_id = d.encounter_id\n" +
"AND d.disease_id = t.disease_id";
db.printResults(db.executeQuery(query), autoAlign, pgTextWidthReplace);
/* print patient ids for males only - manually */
String h = "Patient ids for males";
System.out.println("\n" + h);
System.out.println((new String(new char[h.length()])).replace("\0", "-"));
query = "SELECT * FROM " + db.SCHEMA + ".patients";
ResultSet rs = db.executeQuery(query);
while(rs.next()){
if(rs.getString("gender").equals("M"))
System.out.println(rs.getString("patient_id"));
}
/* close connection */
db.close();
}
}Output from the test class:
patient_id gender race birth_year
--------------------------------------
1 M W 1980
2 M B 1985
3 F A 1976
patient_id gender race birth_year encounter_id patient_id admit_date department_name age encounter_id disease_id disease_id name icd9 start_date end_date parent_disease_id
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 M W 1980 1 1 2012-02-11 clinic_1 32 1 1 1 name1 icd9_1 2013-09-01 9999-12-31 null
1 M W 1980 1 1 2012-02-11 clinic_1 32 1 2 2 name2 icd9_2 2013-09-02 9999-12-31 null
1 M W 1980 1 1 2012-02-11 clinic_1 32 1 3 3 name3 icd9_3 2013-09-03 9999-12-31 1
2 M B 1985 2 2 2012-03-09 emergency 27 2 5 5 name5 icd9_5 2013-09-05 9999-12-31 2
2 M B 1985 4 2 2013-09-16 clinic_2 28 4 1 1 name1 icd9_1 2013-09-01 9999-12-31 null
2 M B 1985 4 2 2013-09-16 clinic_2 28 4 2 2 name2 icd9_2 2013-09-02 9999-12-31 null
2 M B 1985 4 2 2013-09-16 clinic_2 28 4 3 3 name3 icd9_3 2013-09-03 9999-12-31 1
2 M B 1985 4 2 2013-09-16 clinic_2 28 4 5 5 name5 icd9_5 2013-09-05 9999-12-31 2
2 M B 1985 4 2 2013-09-16 clinic_2 28 4 6 6 name6 icd9_6 2013-09-06 9999-12-31 3
1 M W 1980 5 1 2013-09-21 radiology 33 5 3 3 name3 icd9_3 2013-09-03 9999-12-31 1
1 M W 1980 5 1 2013-09-21 radiology 33 5 5 5 name5 icd9_5 2013-09-05 9999-12-31 2
3 F A 1976 6 3 2013-09-26 clinic_1 37 6 2 2 name2 icd9_2 2013-09-02 9999-12-31 null
3 F A 1976 7 3 2013-09-27 emergency 37 7 2 2 name2 icd9_2 2013-09-02 9999-12-31 null
3 F A 1976 7 3 2013-09-27 emergency 37 7 3 3 name3 icd9_3 2013-09-03 9999-12-31 1
3 F A 1976 7 3 2013-09-27 emergency 37 7 5 5 name5 icd9_5 2013-09-05 9999-12-31 2
3 F A 1976 7 3 2013-09-27 emergency 37 7 6 6 name6 icd9_6 2013-09-06 9999-12-31 3
Patient ids for males
---------------------
1
2