DBClient: update, query and metadata
committer: Markus Bröker <mbroeker@largo.homelinux.org>
--- 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;
}