# HG changeset patch # User Markus Bröker # Date 1277075358 -7200 # Node ID f837cf975e95c656b3b3ee459965aa8713044fa4 # Parent 8325a0fc22cd26d7036eb6f73abe1488c80b64c3 DBClient: update, query and metadata committer: Markus Bröker diff --git a/javadb/build.xml b/javadb/build.xml --- a/javadb/build.xml +++ b/javadb/build.xml @@ -44,10 +44,12 @@ - + + + diff --git a/javadb/org/homelinux/largo/app/App.java b/javadb/org/homelinux/largo/app/App.java --- a/javadb/org/homelinux/largo/app/App.java +++ b/javadb/org/homelinux/largo/app/App.java @@ -1,20 +1,51 @@ package org.homelinux.largo.app; +import java.util.List; +import java.sql.SQLException; import org.homelinux.largo.sql.client.DBClient; public class App { public static void main (String args[]) { + DBClient db = null; + int i = 0; + try { - DBClient db = new DBClient (); + db = new DBClient (); + List list = db.getTables (); + + System.out.println ("TABLES IN THE CURRENT DATABASE"); + for (String s : list) { + System.out.printf ("[%04d] %s%n", i++, s); + } + System.out.println (); + + list = db.getColumns("EUROLEAGUE"); + System.out.println ("COLUMN NAMES IN THIS TABLE"); + for (String s : list) { + System.out.printf ("%s ", s); + } + System.out.printf ("%n%n"); + + i = db.update ("delete from euroleague where bonus>0"); + System.out.printf ("Deleted: %3d lines%n", i); db.query ("select * from euroleague order by team"); while (db.hasNext ()) { - System.out.println (db.getColumn (1)); + System.out.println (db.getColumn ("team")); } - - db.close (); + } catch (SQLException sqle) { + System.out.println ("SQLException: " + sqle.getMessage()); } catch (Exception e) { e.printStackTrace (); + } finally { + try { + if (db != null) { + System.out.println ("Disconnecting from database..."); + db.disconnect(); + } + } catch (Exception e) { + e.printStackTrace(); + } } } } diff --git a/javadb/org/homelinux/largo/sql/client/DBClient.java b/javadb/org/homelinux/largo/sql/client/DBClient.java --- a/javadb/org/homelinux/largo/sql/client/DBClient.java +++ b/javadb/org/homelinux/largo/sql/client/DBClient.java @@ -1,16 +1,28 @@ +/** + * Simple Database Client for Java + * + */ + package org.homelinux.largo.sql.client; +import java.util.List; +import java.util.ArrayList; + import java.sql.Connection; import java.sql.DriverManager; +import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.Statement; +import java.sql.SQLException; import javax.naming.Context; +import javax.naming.NamingException; import javax.naming.InitialContext; import javax.sql.DataSource; public class DBClient { private Connection connection; + private DatabaseMetaData metadata; private Statement statement; private ResultSet resultset; @@ -18,24 +30,36 @@ private String dbURL; /** - * Default Konstruktor to connect to my database + * Constructor to connect to a database with a specific driver + * and connection string */ - public DBClient () throws Exception { - this.driver = "org.apache.derby.jdbc.EmbeddedDriver"; - this.dbURL = "jdbc:derby:clubstatistik"; - - this.username = null; - this.password = null; + public DBClient (String driver, String dbURL, String username, String password) throws ClassNotFoundException, SQLException { + this.driver = driver; + this.dbURL = dbURL; Class.forName (driver); - connection = DriverManager.getConnection (dbURL, username, password); - statement = connection.createStatement (); + connect (username, password); } /** - * Default Konstruktor to connect to my DataPool + * Constructor to connect to a database with a specific driver + * and connection string */ - public DBClient (String jndiResource) throws Exception { + public DBClient (String driver, String dbURL) throws ClassNotFoundException, SQLException { + this (driver, dbURL, null, null); + } + + /** + * Default Constructor to connect to my database + */ + public DBClient () throws ClassNotFoundException, SQLException { + this ("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:clubstatistik", null, null); + } + + /** + * Constructor to connect to a DataSource on an AppServer + */ + public DBClient (String jndiResource) throws NamingException, SQLException { Context ctx = new InitialContext (); DataSource ds = (DataSource) ctx.lookup (jndiResource); @@ -43,33 +67,54 @@ statement = connection.createStatement (); } - /** - * Default Konstruktor to connect to my database with auth-data - */ - public DBClient (String driver, String dbURL, String username, String password) throws Exception { - this.driver = driver; - this.dbURL = dbURL; + protected void connect(String user, String pass) throws SQLException { + assert (connection == null) : "CONNECT: Already connected"; - this.username = username; - this.password = password; + this.username = user; + this.password = pass; - Class.forName (driver); connection = DriverManager.getConnection (dbURL, username, password); + metadata = connection.getMetaData(); statement = connection.createStatement (); } - public void close () throws Exception { - connection.close (); + public void disconnect () throws SQLException { + if (connection == null) + throw new SQLException ("DISCONNECT: Not connected"); + else + connection.close (); } - public String getColumn (int pos) throws Exception { + public String getColumn (int pos) throws SQLException { return resultset.getString (pos); } - public String getColumn (String name) throws Exception { + public String getColumn (String name) throws SQLException { return resultset.getString (name); } + public List getTables() throws SQLException { + List list = new ArrayList(); + resultset = metadata.getTables(null, null, "%", null); + + while (resultset.next()) { + list.add(resultset.getString("TABLE_NAME")); + } + + return list; + } + + public List getColumns(String table) throws SQLException { + List list = new ArrayList(); + resultset = metadata.getColumns(null, null, table, null); + + while (resultset.next()) { + list.add(resultset.getString("COLUMN_NAME")); + } + + return list; + } + public Connection getConnection () { return connection; } @@ -90,14 +135,21 @@ return username; } - public boolean hasNext () throws Exception { + public boolean hasNext () throws SQLException { return resultset.next (); } - public void query (String query) throws Exception { + public void query (String query) throws SQLException { resultset = statement.executeQuery (query); } + /** + * returns the number of updated rows + */ + public int update (String update) throws SQLException { + return statement.executeUpdate (update); + } + public void setConnection (Connection connection) { this.connection = connection; }