Relational Databases

Concepts

SQL

SELECT field1, field2, field3 ...
FROM table1, table2, table3 ...
WHERE condition

INSERT INTO table (field, field, ...)
VALUES (value, value, ...)

UPDATE table
SET field = value, ...
WHERE condition

DELETE FROM table WHERE condition

JDBC

Drivers

Browser

public class SQLBrowser extends Console {
protected String driverName;
protected String dbaseName;
protected Connection connection;
protected Statement statement;
protected ResultSet result;
public SQLBrowser(String db)
throws SQLException, ClassNotFoundException {...}
public void finalize() throws SQLException {...}
private String toString (ResultSet rs)
throws SQLException {...}
public String execute(String sql) throws AppError {...}
}

Connecting

public SQLBrowser(String db)
throws SQLException, ClassNotFoundException {
Class.forName(driverName);
dbaseName = "jdbc:cloudscape:rmi:" + db;
connection =
DriverManager.getConnection(dbaseName);
statement = connection.createStatement();
meta = connection.getMetaData();
}

Executing a Query

public String execute(String sql) throws AppError {
String answer = "???";
try {
result = statement.executeQuery(sql);
answer = toString(result);
} catch (SQLException e) { }
return answer;
}

Processing a Result Set

private String toString (ResultSet rs) throws SQLException {
StringBuffer results = new StringBuffer();
ResultSetMetaData metaData = rs.getMetaData();
int numCols = metaData.getColumnCount();
for(int i = 1; i <= numCols; i++) { // get column names
results.append(metaData.getColumnName(i) + "\t");
}
results.append("\n");
while(rs.next()) { // get next row
for(int i = 1; i <= numCols; i++) {
results.append(rs.getObject(i) + "\t");
}
results.append("\n");
}
return results.toString();
}

Closing a Connection

public void finalize() throws SQLException {
statement.close();
connection.close();
}