Apache Derby Examples...
authorMarkus Bröker <mbroeker@largo.dyndns.tv>
Sun, 19 Jul 2009 19:43:46 +0200
changeset 100 a6d2add085d7
parent 99 d99503984cd5
child 101 176448af80fa
Apache Derby Examples... The DBClient class connects to any kind of derby db 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
javadb/sql/euroleague.sql
javadb/targets/Linux.properties
javadb/targets/Windows.properties
new file mode 100644
--- /dev/null
+++ b/javadb/build.xml
@@ -0,0 +1,54 @@
+<?xml version="1.0"?>
+<project name="App" basedir="." default="debug">
+	<property name="project" value="App" />
+	<property name="package.name" value="org.homelinux.largo" />
+	<property name="package.dir" value="org/homelinux/largo" />
+	<property name="jar.file" value="bin/app.jar" />
+	<property name="app.host" value="-Dorg.omg.CORBA.ORBInitialHost=largo.homelinux.org" />
+	<property file="targets/${os.name}.properties" />
+
+	<path id="classpath">
+		<pathelement location="${app.server}/lib/javaee.jar" />
+		<pathelement location="${app.server}/lib/appserv-rt.jar" />
+		<pathelement location="${app.server}/lib/appserv-admin.jar" />
+		<pathelement location="${app.server}/lib/install/applications/jmsra/imqjmsra.jar" />
+		<pathelement location="${app.server}/javadb/lib/derbyrun.jar" />
+		<pathelement location="." />
+	</path>
+
+	<!-- Targets -->
+	<target name="clean">
+		<echo message="deleting all class- and jar-files in ${package.dir} tree" />
+		<delete>
+			<fileset dir="${package.dir}" includes="**/*.class" />
+			<fileset dir="." includes="**/*.*~" defaultexcludes="no" />
+			<fileset dir="." includes="**/*.log" />
+		</delete>
+		<delete  dir="clubstatistik" />
+	</target>
+
+	<target name="init" >
+		<echo message="creating initial database" />
+		<exec executable="${native.shell}">
+			<env key="DERBY_HOME" value="${app.server}/javadb/" />
+			<arg line="${native.switch} ${app.server}/javadb/bin/ij sql/euroleague.sql" />
+		</exec>
+		<mkdir dir="clubstatistik" />
+	</target>
+
+	<target name="compile">
+		<echo message="compiling ${package.dir} tree." />
+		<javac source="1.5" target="1.5" srcdir="${package.dir}"
+			sourcepath="${basedir}" debug="on">
+			<classpath refid="classpath" />
+		</javac>
+	</target>
+
+	<target name="debug" depends="init, compile">
+		<java classname="${package.name}.app.App"
+			failonerror="true" fork="yes">
+			<classpath refid="classpath" />
+			<arg line="${app.host}" />
+		</java>
+	</target>
+</project>
new file mode 100644
--- /dev/null
+++ b/javadb/org/homelinux/largo/app/App.java
@@ -0,0 +1,25 @@
+package org.homelinux.largo.app;
+
+import org.homelinux.largo.sql.client.DBClient;
+
+public class App {
+	public static void main (String args[]) {
+		try {
+			DBClient db = new DBClient (
+					"org.apache.derby.jdbc.EmbeddedDriver",
+					"jdbc:derby:clubstatistik",
+					null, null
+			);
+
+			db.query ("select * from euroleague order by team");
+
+			while (db.hasNext ()) {
+				System.out.println (db.getColumn (1));
+			}
+
+			db.close ();
+		} catch (Exception e) {
+			e.printStackTrace ();
+		}
+	}
+}
new file mode 100644
--- /dev/null
+++ b/javadb/org/homelinux/largo/sql/client/DBClient.java
@@ -0,0 +1,120 @@
+package org.homelinux.largo.sql.client;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.Statement;
+
+import javax.naming.Context;
+import javax.naming.InitialContext;
+import javax.sql.DataSource;
+
+public class DBClient {
+	private Connection connection;
+	private Statement statement;
+	private ResultSet resultset;
+
+	private String driver, username, password;
+	private String dbURL;
+
+	/**
+	 * Default Konstruktor to connect to my database
+	 */
+	public DBClient () throws Exception {
+		this.driver = "org.apache.derby.jdbc.EmbeddedDriver";
+		this.dbURL = "jdbc:derby://localhost/clubstatistik";
+
+		this.username = null;
+		this.password = null;
+
+		Class.forName (driver);
+		connection = DriverManager.getConnection (dbURL, username, password);
+		statement = connection.createStatement ();
+	}
+
+	/**
+	 * Default Konstruktor to connect to my DataPool
+	 */ 
+	public DBClient (String jndiResource) throws Exception {
+		Context ctx = new InitialContext ();
+		DataSource ds = (DataSource) ctx.lookup (jndiResource);
+
+		connection = ds.getConnection ();
+		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;
+
+		this.username = username;
+		this.password = password;
+
+		Class.forName (driver);
+		connection = DriverManager.getConnection (dbURL, username, password);
+		statement = connection.createStatement ();
+	} 
+
+	public void close () throws Exception {
+		connection.close ();
+	} 
+
+	public String getColumn (int pos) throws Exception {
+		return resultset.getString (pos);
+	} 
+
+	public String getColumn (String name) throws Exception {
+		return resultset.getString (name);
+	} 
+
+	public Connection getConnection () {
+		return connection;
+	} 
+
+	public String getDbURL () {
+		return dbURL;
+	}
+
+	public String getDriver () {
+		return driver;
+	}
+
+	public String getPassword () {
+		return password;
+	}
+
+	public String getUsername () {
+		return username;
+	}
+
+	public boolean hasNext () throws Exception {
+		return resultset.next ();
+	}
+
+	public void query (String query) throws Exception {
+		resultset = statement.executeQuery (query);
+	} 
+
+	public void setConnection (Connection connection) {
+		this.connection = connection;
+	}
+
+	public void setDbURL (String dbURL) {
+		this.dbURL = dbURL;
+	}
+
+	public void setDriver (String driver) {
+		this.driver = driver;
+	}
+
+	public void setPassword (String password) {
+		this.password = password;
+	}
+
+	public void setUsername (String username) {
+		this.username = username;
+	}
+}
new file mode 100644
--- /dev/null
+++ b/javadb/sql/euroleague.sql
@@ -0,0 +1,98 @@
+connect 'jdbc:derby:clubstatistik;create=true';
+drop table uefa;
+drop table euroleague;
+create table euroleague (
+	team char(20),
+	lwin int, ldraw int, llost int,
+	cup double,
+	uwin int, udraw int,
+	bonus double,
+	country char(15));
+
+create unique index team_idx on euroleague(team);
+
+insert into euroleague values('FC Toulouse', 16, 16, 6, 0, 0, 0, 0, 'Frankreich');
+insert into euroleague values('Rubin Kazan', 6, 3, 2, 0.25, 0, 0, 0, 'Russland');
+insert into euroleague values('Paris St. Germain', 19, 7, 12, 0, 4, 4, 4, 'Frankreich');
+insert into euroleague values('Stade Rennes', 15, 16, 7, 0, 0, 0, 0, 'Frankreich');
+insert into euroleague values('Spartak Moskau', 6, 2, 4, 0, 1, 1, 4, 'Russland');
+insert into euroleague values('AJ Auxerre', 16, 7, 15, 0, 0, 0, 0, 'Frankreich');
+insert into euroleague values('AS Monaco', 11, 12, 15, 0, 0, 0, 0, 'Frankreich');
+insert into euroleague values('ZSKA Moskau', 6, 1, 4, 0.5, 8, 1, 0, 'Russland');
+insert into euroleague values('St. Petersburg', 5, 4, 3, 0, 4, 2, 3, 'Russland');
+insert into euroleague values('Ajax Amsterdam', 21, 5, 8, 0, 4, 2, 4, 'Niederlande');
+insert into euroleague values('PSV Eindhoven', 19, 8, 7, 0, 1, 0, 3, 'Niederlande');
+insert into euroleague values('FC Villarreal', 18, 11, 9, 0, 3, 5, 5, 'Spanien');
+insert into euroleague values('1899 Hoffenheim', 15, 10, 9, 0, 0, 0, 0, 'Deutschland');
+insert into euroleague values('Schalke 04', 14, 8, 12, 0, 1, 1, 4, 'Deutschland');
+insert into euroleague values('Bayer Leverkusen', 14, 7, 13, 0.25, 0, 0, 0, 'Deutschland');
+insert into euroleague values('FC Groningen', 17, 5, 12, 0, 0, 0, 0, 'Niederlande');
+insert into euroleague values('Werder Bremen', 12, 9, 13, 0.5, 4, 8, 6, 'Deutschland');
+insert into euroleague values('Feyenoord Rotterdam', 12, 9, 13, 0, 0, 0, 2, 'Niederlande');
+insert into euroleague values('Manchester United', 28, 6, 4, 0.125, 6, 6, 7, 'England');
+insert into euroleague values('FC Liverpool', 25, 11, 2, 0, 6, 3, 6.5, 'England');
+insert into euroleague values('FC Valencia', 18, 8, 12, 0, 1, 5, 4, 'Spanien');
+insert into euroleague values('Deportivo La Coruna', 16, 10, 12, 0, 2, 1, 3.5, 'Spanien');
+insert into euroleague values('VFL Wolfsburg', 21, 6, 7, 0, 3, 1, 3, 'Deutschland');
+insert into euroleague values('Inter Mailand', 25, 9, 4, 0.125, 2, 3, 4, 'Italien');
+insert into euroleague values('Juventus Turin', 21, 11, 6, 0.125, 3, 4, 5.5, 'Italien');
+insert into euroleague values('FC Chelsea', 25, 8, 5, 0.5, 5, 6, 6, 'England');
+insert into euroleague values('AC Mailand', 22, 8, 8, 0, 2, 4, 4, 'Italien');
+insert into euroleague values('FC Porto', 21, 7, 2, 0.5, 4, 3, 5, 'Portugal');
+insert into euroleague values('AC Florenz', 21, 5, 12, 0, 1, 4, 4.5, 'Italien');
+insert into euroleague values('Sporting Lissabon', 20, 6, 4, 0, 3, 0, 4, 'Portugal');
+insert into euroleague values('Benfica Lissabon', 17, 8, 5, 0, 0, 1, 0, 'Portugal');
+insert into euroleague values('FC Arsenal', 20, 12, 6, 0.125, 5, 3, 8, 'England');
+insert into euroleague values('NAC Breda', 13, 6, 15, 0, 0, 0, 0, 'Niederlande');
+insert into euroleague values('FC Everton', 17, 12, 9, 0.25, 0, 0, 1, 'England');
+insert into euroleague values('AS Rom', 18, 9, 11, 0, 5, 0, 4, 'Italien');
+insert into euroleague values('Udinese Calcio', 16, 10, 12, 0, 5, 2, 3, 'Italien');
+insert into euroleague values('Nacional Funchal', 15, 7, 8, 0, 0, 0, 0, 'Portugal');
+insert into euroleague values('Lazio Rom', 15, 5, 18, 0.5, 0, 0, 0, 'Italien');
+insert into euroleague values('SSC Neapel', 12, 10, 16, 0, 0, 0, 4, 'Italien');
+insert into euroleague values('SC Braga', 13, 11, 6, 0, 3, 2, 4, 'Portugal');
+insert into euroleague values('Bayern München', 20, 7, 7, 0, 6, 3, 5, 'Deutschland');
+insert into euroleague values('VFB Stuttgart', 19, 7, 8, 0, 2, 1, 5, 'Deutschland');
+insert into euroleague values('Hertha BSC', 19, 6, 9, 0, 0, 2, 6.5, 'Deutschland');
+insert into euroleague values('Aston Villa', 17, 11, 10, 0, 2, 1, 4.5, 'England');
+insert into euroleague values('Manchester City', 15, 5, 18, 0, 7, 2, 4, 'England');
+insert into euroleague values('FC Barcelona', 27, 6, 5, 0.5, 7, 5, 8, 'Spanien');
+insert into euroleague values('Real Madrid', 25, 3, 10, 0, 4, 0, 4, 'Spanien');
+insert into euroleague values('Glasgow Rangers', 26, 8, 4, 0.5, 0, 0, 0.5, 'Schottland');
+insert into euroleague values('Celtic Glasgow', 24, 10, 4, 0, 1, 2, 3, 'Schottland');
+insert into euroleague values('Heart of Midlothian', 16, 11, 11, 0, 0, 0, 0, 'Schottland');
+insert into euroleague values('Sampdoria Genua', 11, 13, 14, 0.25, 2, 1, 4, 'Italien');
+insert into euroleague values('Girondins Bordeaux', 24, 8, 6, 0, 0, 2, 3, 'Frankreich');
+insert into euroleague values('Hamburger SV', 19, 4, 11, 0.125, 8, 1, 5, 'Deutschland');
+insert into euroleague values('Olympique Marseille', 22, 11, 5, 0, 4, 1, 6, 'Frankreich');
+insert into euroleague values('Besiktas Istanbul', 21, 8, 5, 0.5, 0, 0, 0, 'Türkei');
+insert into euroleague values('FC Sevilla', 21, 7, 10, 0.125, 2, 0, 4, 'Spanien');
+insert into euroleague values('Fenerbahce Istanbul', 18, 7, 9, 0.25, 0, 2, 6.5, 'Türkei');
+insert into euroleague values('FC Aberdeen', 14, 11, 13, 0, 0, 0, 0, 'Schottland');
+insert into euroleague values('Olympique Lyon', 20, 11, 7, 0, 3, 3, 4, 'Frankreich');
+insert into euroleague values('Galatasaray Istanbul', 18, 7, 9, 0, 4, 2, 5, 'Türkei');
+insert into euroleague values('Borussia Dortmund', 15, 14, 5, 0, 0, 0, 2, 'Deutschland');
+insert into euroleague values('Atletico Madrid', 20, 7, 11, 0, 3, 5, 5, 'Spanien');
+insert into euroleague values('FK Moskau', 6, 5, 1, 0.125, 0, 0, 3.5, 'Russland');
+insert into euroleague values('Schachtjor Donezk', 19, 7, 4, 0.25, 8, 4, 8, 'Ukraine');
+
+select * from euroleague order by lwin desc, ldraw desc;
+create table uefa (
+	country char(20), 
+	coeff double);
+
+create unique index uefa_idx on uefa(country);
+
+insert into uefa values('Deutschland', 56.695);
+insert into uefa values('England', 79.498999999999995);
+insert into uefa values('Spanien', 74.266000000000005);
+insert into uefa values('Schottland', 27.875);
+insert into uefa values('Portugal', 36.462000000000003);
+insert into uefa values('Ukraine', 41.847999999999999);
+insert into uefa values('Italien', 62.784999999999997);
+insert into uefa values('Frankreich', 50.167999999999999);
+insert into uefa values('Russland', 47.125);
+insert into uefa values('Niederlande', 38.963000000000001);
+insert into uefa values('Türkei', 32.225000000000001);
+
+quit;
new file mode 100644
--- /dev/null
+++ b/javadb/targets/Linux.properties
@@ -0,0 +1,5 @@
+# Default Properties for Linux Machines
+
+app.server=/opt/SUNWappserver
+native.shell=/bin/sh
+native.switch=
new file mode 100644
--- /dev/null
+++ b/javadb/targets/Windows.properties
@@ -0,0 +1,4 @@
+app.server=C:/SUN/Appserver
+native.shell=cmd.exe
+native.switch=/c
+