Short instruction to using the JDBC driver for PostgreSQL

offbeatlossData Management

Nov 22, 2012 (4 years and 4 months ago)


JDBC Notes
A more thorough presentation can be found here:
Get the driver
Download the JDBC driver from:
A link should be available at the course web page:)
Adding the driver to the classpath
Once you have compiled your program,lets assume it is called Main,you must tell the virtual
machine that it should be aware of the driver.If the driver jar le is placed in the same
directory,the command will look like:
 java -cp.:postgresql-8.4-702.jdbc4.jar Main
The parameter -cp is the CLASSPATH,which tells Java where to look for classes.If the
driver is not in the same directory,you must add the full path to it,i.e.
java -cp.:/home/user/dir/postgresql-8.4-702.jdbc4.jar Main
The Basics
Make sure we can use the methods
import java.sql.*;
Load the type of the driver.We will use the PostgreSQL driver.
Connecting to the database
To communicate with the database,you need to setup a connection,supplying it with the
URL for the database,the username and the password,i.e.something like
String url ="jdbc:postgresql://";
String username ="jaam";
String password ="MySecretPassword";
Connection con = DriverManager.getConnection(url,username,password);
Querying the database
We will consider two ways,Statement and PreparedStatement.They will both return a
ResultSet if it is a SELECT query,and an int if it is an INSERT,UPDATE,or DELETE query.
If the user do not have to decide something!
Statement s = con.createStatement();
ResultSet m = s.executeQuery(
"SELECT color,type FROM furniture WHERE amount = 4;");
If the user will have the possibility to input anything,use a PreparedStatement.
int amount = 4;//Or whatever the user would input
PreparedStatement s = con.prepareStatement(
"SELECT color,type FROM furniture WHERE amount =?;");
ResultSet rs = s.executeQuery();
You can use the next() method on your result set as an iterator,and the methods:
with the column number (indexed from 1) or the column name as argument.
getMetaData() returns data about the result,like column count,column names,column
types etc.Try to experiment with this yourself.
Both the Connection,the Statements and the ResultSet should be closed (close()) when
you are done with them.