DBClient: update, query and metadata
authorMarkus Bröker <mbroeker@largo.dyndns.tv>
Mon, 21 Jun 2010 01:09:18 +0200
changeset 135 f837cf975e95
parent 134 8325a0fc22cd
child 136 d82f65e902d0
DBClient: update, query and metadata committer: Markus Bröker <mbroeker@largo.homelinux.org>
javadb/build.xml
javadb/org/homelinux/largo/app/App.java
javadb/org/homelinux/largo/sql/client/DBClient.java
--- a/javadb/build.xml
+++ b/javadb/build.xml
@@ -44,10 +44,12 @@
 		</javac>
 	</target>
 
-	<target name="debug" depends="init, compile">
+	<target name="debug" depends="compile">
+		<echo message="Use ant init to create an initial database." />
 		<java classname="${package.name}.app.App"
 			failonerror="true" fork="yes">
 			<classpath refid="classpath" />
+			<jvmarg line="-ea" />
 			<arg line="${app.host}" />
 		</java>
 	</target>
--- 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<String> 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();
+			}
 		}
 	}
 }
--- 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<String> getTables() throws SQLException {
+		List<String> list = new ArrayList<String>();
+		resultset = metadata.getTables(null, null, "%", null);
+
+		while (resultset.next()) {
+			list.add(resultset.getString("TABLE_NAME"));
+		}
+
+		return list;
+	}
+
+	public List<String> getColumns(String table) throws SQLException {
+		List<String> list = new ArrayList<String>();
+		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;
 	}