If you are interested in a more detailed introduction, have a look at Sun's JDBC Tutorial.
import java.sql.*;The java.sql package provides means to execute queries or updates.
Global variables of the class are
class dbWrapper
{
Statement stmt;
Connection con;
String strUrl;
String strUserName;
String strPassword;
The constructor assigns user name and password.
It also constructs the URL string consisting of a protocol part,
here jdbc:odbc: which means that JDBC Java DataBase Connectivity
interfaces Microsoft's ODBC Open DataBase Connectivity which then
connects to the database server - whose Internet address is
denoted by the DSN Data Source Name strDSN.
public dbWrapper(String strDSN)
{
strUrl = "jdbc:odbc:" + strDSN;
strUserName = "guest";
strPassword = "guest";
}
A driver is needed which bridges between the Java DB connectivity and
Microsoft's ODBC. This driver is loaded from Sun's site.
Then the connection to the database, specified by URL, user name and
password, is established.
Finally, a statement object is created, which will allow us to
pass SQL statements to the database
public void Open()
{
...
// Load the jdbc-odbc bridge driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Attempt to connect to a driver.
con = DriverManager.getConnection( strUrl, strUserName,
strPassword );
// Create a Statement object so we can submit
// SQL statements to the driver
stmt = con.createStatement();
...
}
The Select method allows us to execute an SQL select (or update) statement.
strQuery is a string containing the query (or update).
strQuery is executed and a resultSet is returned.
This result set - in case of a SELECT statement a set of tuples (records) -
is then processed using the method printResultSet().
Then, the result set is closed (i.e. discarded).
public void Select( String strQuery )
{
...
// Submit a query, creating a ResultSet object
ResultSet rs = stmt.executeQuery( strQuery );
// Display all columns and rows from the result set
printResultSet (rs);
rs.close();
...
}
There is also a method called 'executeUpdate' for table definitions
and updates (insert,delete,update) - see bottom of page.
Close closes the connection to the database.
public void Close()
{
...
stmt.close();
con.close();
...
}
The method printResultSet prints the result set which has been returned by
the statement execution.
For each record in the result set (obtained by rs.next()), all attribute
values are printed in a for-loop.
private static void printResultSet(ResultSet rs) throws SQLException
{
int numCols = rs.getMetaData().getColumnCount();
while ( rs.next() )
{
for (int i=1; i<=numCols; i++)
{
System.out.print(rs.getString(i) + " | " );
}
System.out.println();
}
}
}
Suppose the DSN you have defined is "TestDB", then the following establishes the connection.
static dbWrapper myDB = new dbWrapper("TestDB");//an instance variable
This has to be outside of any method.
Use
myDB.Open();to open the connection.
There is a Book-example in the DB. A sample query which could be executed is "SELECT * FROM Authors":
String strSQLQuery = "SELECT * FROM Authors"; myDB.Select(strSQLQuery);
Close the connection
myDB.Close();at the end.
// dbWrapper Class
import java.sql.*;
class dbWrapper
{
Statement stmt;
Connection con;
String strUrl;
String strUserName;
String strPassword;
public dbWrapper(String strDSN)
{
// the DSN for the Db connection
strUrl = "jdbc:odbc:" + strDSN;
strUserName = "guest";
strPassword = "guest";
}
public void Open()
{
try
{
// Load the jdbc-odbc bridge driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Attempt to connect to a driver.
con = DriverManager.getConnection( strUrl, strUserName,
strPassword );
// Create a Statement object so we can submit
// SQL statements to the driver
stmt = con.createStatement();
}
catch (SQLException ex)
{
while (ex != null)
{
System.out.println("SQL Exception: " + ex.getMessage() );
ex = ex.getNextException();
}
}
catch (java.lang.Exception ex)
{
ex.printStackTrace();
}
}
public void Select( String strQuery )
{
try
{
// Submit a query, creating a ResultSet object
ResultSet rs = stmt.executeQuery( strQuery );
// Display all columns and rows from the result set
printResultSet(rs);
rs.close();
}
catch (SQLException ex)
{
while (ex != null)
{
System.out.println ("SQL Exception: " + ex.getMessage () );
ex = ex.getNextException ();
}
}
}
public void Close()
{
try
{
stmt.close();
con.close();
}
catch (SQLException ex)
{
while (ex != null)
{
System.out.println("SQL Exception: " + ex.getMessage () );
ex = ex.getNextException();
}
}
}
private static void printResultSet(ResultSet rs) throws SQLException
{
int numCols = rs.getMetaData().getColumnCount();
while ( rs.next() )
{
for (int i=1; i<=numCols; i++)
{
System.out.print(rs.getString(i) + " | " );
}
System.out.println();
}
}
}
If you want to use the dbWrapper class you can just include it
in another Java program. This one is called DBTest, so the name
of the file would be DBTest.java :
import java.sql.*;
class dbWrapper
{
... THE WHOLE CONTENT OF THE CLASS ...
}
public class DBTest
{
static dbWrapper myDB = new dbWrapper("TestDB");
public static void main(String[] args) {
String strSQLQuery;
// open the database connection
myDB.Open(); // open a connection to DB
strSQLQuery = "SELECT * FROM authors";
myDB.Select(strSQLQuery);
myDB.Close();
}
}
In general, you can create (and delete) tables and insert/update/delete table content using JDBC - there is a link below - and check the effect using the SQL Server query interface. By selecting all information from a table (execute "SELECT * FROM T" in the 'pubs' database using the query tool - if T is the table you have just created or updated) you can see whether your commands were successful: if the table hasn't been created then the system is going to tell you that the object doesn't exist; if it exists you will see its content (might be empty immediately after creation).
You can create tables in the database using JDBC:
stmt.executeUpdate("CREATE TABLE BOOKS " +
"(ISBN VARCHAR(15), AUTHOR VARCHAR(20), TITLE VARCHAR(20), PRICE DECIMAL(3.2), " +
" PRIMARY KEY(ISBN) )");
and you can insert data into this table:
stmt.executeUpdate("INSERT INTO BOOKS " +
" VALUES ('1-234-567', 'Gosling', 'Java', 25.99)");
The suggested attribute type DECIMAL might not be available in all DBMS.
If there is a problem, use FLOAT instead.
Here is a sample program which creates a table, inserts data into it, and retrieves its content.
Some attempts to create tables in the database (or to insert or update table content) might be successful. You can check that by either printing out the content using JDBC (as in the program) or to use the SQL Server Query tool to query the content.
Sometimes you might want to delete a table definition. Then use the drop operation as show in the sample program.
© 2000 Claus Pahl