Creating a database in Sun Java System Application Server 9

[astolpho]

I am using a slightly outdated reference book on J2EE programming. It gives 2 methods of creating a database used in its casestudies. The first is an ANT script that gives the following output:

D:\original\CaseStudy-2-5\CaseStudy\Day02\exercise>asant database
Buildfile: build.xml

env-user:

prop-user:

set-user:

env-password:

prop-password:

read-password:

set-password:

set-j2ee:

create-jdbc:

set-j2ee:

asadmin:
[echo] asadmin.bat create-jdbc-resource --user admin --password password --
connectionpoolid PointBasePool --enabled=true jdbc/Agency
[exec] Usage: create-jdbc-resource [--terse=false] [--echo=false] [--intera
ctive=true] [--host localhost] [--port 4848|4849] [--secure | -s] [--user admin_
user] [--passwordfile file_name] --connectionpoolid id [--enabled=true] [--descr
iption text] [--target target(Default server)] jndi_name
[exec] CLI193 Password option "password" is not allowed on the command line
. Please use --passwordfile option or asadmin login command.

set-j2ee:

asadmin:
[echo] asadmin.bat list-jdbc-resources --user admin --password password
[exec] Usage: list-jdbc-resources [--terse=false] [--echo=false] [--interac
tive=true] [--host localhost] [--port 4848|4849] [--secure | -s] [--user admin_u
ser] [--passwordfile file_name] [target (Default server)]
[exec] CLI193 Password option "password" is not allowed on the command line
. Please use --passwordfile option or asadmin login command.

set-dbpath:

BUILD FAILED
D:\original\CaseStudy-2-5\CaseStudy\common\targets.xml:87: D:\Sun\SDK\pointbase\
lib not found.

Total time: 2 seconds
D:\original\CaseStudy-2-5\CaseStudy\Day02\exercise>

I've checked and there is no Sun\SDK\pointbase folder. The book was written for SDK 1.4 and I believe another application server. There is also a Java program that attempts to create it with jdbc. Its output looks like this :


D:\original\CaseStudy-2-5\CaseStudy\Day02\exercise\classes>java CreateAgency
java.lang.ClassNotFoundException: com.pointbase.jdbc.jdbcUniversalDriver
java.lang.ClassNotFoundException: com.pointbase.jdbc.jdbcUniversalDriver
at java.net.URLClassLoader$1.run(URLClassLoader.java: 200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.j ava:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:3 06)
at sun.misc.Launcher$AppClassLoader.loadClass(Launche r.java:276)
at java.lang.ClassLoader.loadClass(ClassLoader.java:2 51)
at java.lang.ClassLoader.loadClassInternal(ClassLoade r.java:319)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:169)
at CreateAgency.main(Unknown Source)

D:\original\CaseStudy-2-5\CaseStudy\Day02\exercise\classes>

I am not conversant enough to discuss the corrections for the ANT script but I'm reasonably certain the Java program could work if I had the right strings in these lines.

// PointBase
private static final String driver = "com.pointbase.jdbc.jdbcUniversalDriver";
private static final String protocol = "jdbc:pointbase:server://localhost/sun-appserv-samples,new";
private static final String user = "pbPublic";
private static final String password = "pbPublic";

Can anyone help me with the correct driver and protocol?
I am using the latest download version SDK JavaEE5
the entire java program looks like this:

import java.sql.*;


public class CreateAgency {

// Cloudscape
//private static final String driver = "COM.cloudscape.core.RmiJdbcDriver";
//private static final String protocol = "jdbc:cloudscape:rmi:Agency;create=true";
//private static final String user = "";
//private static final String password = "";

// PointBase
private static final String driver = "com.pointbase.jdbc.jdbcUniversalDriver";
private static final String protocol = "jdbc:pointbase:server://localhost/sun-appserv-samples,new";
private static final String user = "pbPublic";
private static final String password = "pbPublic";

public static void main(String[] args) {

Connection conn=null;
Statement s=null;

try {
Class.forName(driver);
System.out.println("Loaded driver: "+driver);

conn = DriverManager.getConnection(protocol,user,password );
System.out.println("Connected to: "+protocol);
conn.setAutoCommit(false);

s = conn.createStatement();

System.out.println("Dropping exisiting BMP tables...");
try {s.execute("drop table ApplicantSkill");} catch (SQLException ex){}
try {s.execute("drop table Applicant");} catch (SQLException ex){}
try {s.execute("drop table JobSkill");} catch (SQLException ex){}
try {s.execute("drop table Job");} catch (SQLException ex){}
try {s.execute("drop table Matched");} catch (SQLException ex){}
try {s.execute("drop table Customer");} catch (SQLException ex){}
try {s.execute("drop table Location");} catch (SQLException ex){}
try {s.execute("drop table Skill");} catch (SQLException ex){}

System.out.println("Dropped tables");

System.out.println("Creating new tables...");
s.execute("create table Skill(name varchar(16) CONSTRAINT pk_skill PRIMARY KEY (name), description varchar(64))");
s.execute("create table Location(name varchar(16)CONSTRAINT pk_location PRIMARY KEY (name), description varchar(64))");
s.execute("create table Applicant(login varchar(16) CONSTRAINT pk_applicant PRIMARY KEY (login), name varchar(64), email varchar(64), summary varchar(512), location varchar(16), CONSTRAINT fk_location FOREIGN KEY (location) REFERENCES Location(name))");
s.execute("create table ApplicantSkill(applicant varchar(16), skill varchar(16), CONSTRAINT fk_applicant FOREIGN KEY (applicant) REFERENCES Applicant(login), CONSTRAINT fk_skill FOREIGN KEY (skill) REFERENCES Skill(name))");
s.execute("create table Customer(login varchar(16) CONSTRAINT pk_customer PRIMARY KEY (login), name varchar(64), email varchar(64), address1 varchar(64), address2 varchar(64))");
s.execute("create table Job(ref varchar(16), customer varchar(16), description varchar(512), location varchar(16), CONSTRAINT pk_job PRIMARY KEY (ref,customer), CONSTRAINT fk_customer FOREIGN KEY (customer) REFERENCES Customer(login), CONSTRAINT fk_location FOREIGN KEY (location) REFERENCES Location(name))");
s.execute("create table JobSkill(job varchar(16), customer varchar(16), skill varchar(16), CONSTRAINT fk_job FOREIGN KEY (job,customer) REFERENCES Job(ref,customer), CONSTRAINT fk_skill FOREIGN KEY (skill) REFERENCES Skill(name))");
s.execute("create table Matched(applicant varchar(16), job varchar(16), customer varchar(16), exact boolean, CONSTRAINT fk_job FOREIGN KEY (job,customer) REFERENCES Job(ref,customer), CONSTRAINT fk_applicant FOREIGN KEY (applicant) REFERENCES Applicant(login))");
System.out.println("Created tables");

System.out.println("Inserting table records...");
s.execute("insert into Location values ('London','London UK')");
s.execute("insert into Location values ('Washington','Washington DC, USA')");
s.execute("insert into Location values ('Verona','Verona, Renaissance Italy')");
s.execute("insert into Location values ('Wessex','Wessex, Kingdom of England')");

s.execute("insert into Skill values ('Tree Surgeon','Tree Surgeon')");
s.execute("insert into Skill values ('Cigar Maker','Cigar Maker')");
s.execute("insert into Skill values ('Bodyguard','Bodyguard')");
s.execute("insert into Skill values ('Cook','Cook')");
s.execute("insert into Skill values ('Lawyer','Lawyer')");
s.execute("insert into Skill values ('Critic','Critic')");

s.execute("insert into Applicant values ('juliet','Juliet Capulet', 'juliet@localhost' , 'Dutiful daughter', 'London' )");
s.execute("insert into Applicant values ('romeo','Romeo Montague', 'romeo@localhost' , 'Dutiful son', 'Wessex' )");
s.execute("insert into Applicant values ('julius','Julius Caesar', 'julias@localhost' , 'Roman Emperor', 'Washington' )");
s.execute("insert into Applicant values ('brutus','Marcus Brutus', 'marcus@localhost' , 'Roman Senator', 'Washington' )");
s.execute("insert into Applicant values ('proteus','Proteus', 'proteus@localhost' , 'Gentleman', 'Verona' )");
s.execute("insert into Applicant values ('valentine','Valentine', 'valentine@localhost' , 'Gentleman', 'Verona' )");

s.execute("insert into ApplicantSkill values ('juliet', 'Cook')");
s.execute("insert into ApplicantSkill values ('romeo', 'Cook')");
s.execute("insert into ApplicantSkill values ('romeo', 'Bodyguard')");
s.execute("insert into ApplicantSkill values ('julius', 'Tree Surgeon' )");
s.execute("insert into ApplicantSkill values ('julius', 'Tree Surgeon' )");
s.execute("insert into ApplicantSkill values ('brutus', 'Critic' )");
s.execute("insert into ApplicantSkill values ('brutus', 'Lawyer' )");
s.execute("insert into ApplicantSkill values ('proteus', 'Lawyer' )");
s.execute("insert into ApplicantSkill values ('proteus', 'Critic' )");
s.execute("insert into ApplicantSkill values ('valentine', 'Critic' )");
s.execute("insert into ApplicantSkill values ('valentine', 'Cigar Maker' )");

s.execute("insert into Customer values ('george','George Washington', 'george@localhost', 'White House', 'Washington')");
s.execute("insert into Customer values ('winston','Winston S Churchill', 'winston@localhost', '10 Downing Street', 'London')");
s.execute("insert into Customer values ('abraham','Abraham Lincoln', 'abe@localhost', 'Springfield', 'Illinois')");
s.execute("insert into Customer values ('alfred','Alfred the Great', 'alf@localhost', 'Wessex', 'England')");

s.execute("insert into Job values ('Tree pruner', 'george', 'Must be honest', 'Washington')");
s.execute("insert into Job values ('Cigar trimmer', 'winston', 'Must like to talk and smoke', 'London')");
s.execute("insert into Job values ('Theatre goer', 'abraham', 'Should be intelligent and articulate', 'Washington')");
s.execute("insert into Job values ('Cake maker', 'alfred', 'Should have a good sense of smell', 'Wessex')");

s.execute("insert into JobSkill values ('Tree pruner','george','Tree Surgeon')");
s.execute("insert into JobSkill values ('Cigar trimmer', 'winston', 'Cigar Maker')");
s.execute("insert into JobSkill values ('Cigar trimmer', 'winston', 'Critic')");
s.execute("insert into JobSkill values ('Theatre goer', 'abraham', 'Bodyguard')");
s.execute("insert into JobSkill values ('Theatre goer', 'abraham', 'Lawyer')");
s.execute("insert into JobSkill values ('Theatre goer', 'abraham', 'Critic')");
s.execute("insert into JobSkill values ('Cake maker', 'alfred', 'Cook')");

System.out.println("Inserted records");

conn.commit();
System.out.println("Committed transactions");
}
catch (SQLException ex) {
System.out.println("SQL Exception thrown: "+ex);
ex.printStackTrace();
try { conn.rollback(); } catch (Exception e) {}
}
catch (ClassNotFoundException ex) {
System.out.println(ex);
ex.printStackTrace();
}
finally {
try { s.close(); } catch (Exception ex) {}
try { conn.close(); } catch (Exception ex) {}
}
}

}

[emekadavid]

am not into J2EE yet but if you're using an outdated book , no problem about that, just get the java tutorials on what you want, for J2EE i have "my first cup"?? i sure hope i got the title right. read through and read the outdated book, you'll get the concepts along the way. concepts never change, only the ways of implementing them do.

[astolpho]

I created the Database thru the netbeans gui in the Derby Pool and connected to it. I used the gui to create tables which worked fine except I couldn't figure out how to designate foreign keys. I made all the tables anyway but when I tried to insert data into the tables I got an error saying the tables didn't exist. I deleted the tables and tried making them with an sql script : (see below) This succeeded in creating the Skill, Location, and Customer tables but no data was inserted and no tables with Foreign keys were made because acording to the sql errors the tables do not exist, including the ones that were successfully created. One other problem is one of my tables has a Boolean column and that type is apparently unsupported? The main problem is though that the tables don't exist even though I can see them in the netbeans gui, at least the ones that were created successfully. The order of the table creation is such that no foreign key references are made to a table that has not been attempted to be created first.Note table "Location" is successfully created before I attempt to create table "Applicant" unsuccessfully because according the the sql error table "Location" does not exist. Anyone got any ideas what is happening here?

DROP TABLE ApplicantSkill;
DROP TABLE Applicant;
DROP TABLE JobSkill;
DROP TABLE Job;
DROP TABLE Matched;
DROP TABLE Customer;
DROP TABLE Location;
DROP TABLE Skill;

create table "Skill"
(
"name" varchar(16) NOT NULL PRIMARY KEY,
"description" varchar(64)
);

create table "Location"
(
"name" varchar(16) NOT NULL PRIMARY KEY,
"description" varchar(64)
);

create table "Applicant"
(
"login" varchar(16) NOT NULL PRIMARY KEY,
"name" varchar(64),
"email" varchar(64),
"summary" varchar(512),
"location" varchar(16),
CONSTRAINT FK_Applicant_location FOREIGN KEY (location) REFERENCES Location(name)
);

create table "ApplicantSkill"
(
"applicant" varchar(16),
"skill" varchar(16),
CONSTRAINT FK_ApplicantSkill_applicant FOREIGN KEY (applicant) REFERENCES Applicant(login),
CONSTRAINT FK_ApplicantSkill_skill FOREIGN KEY (skill) REFERENCES Skill(name)
);

create table "Customer"
(
"login" varchar(16) NOT NULL PRIMARY KEY,
"name" varchar(64),
"email" varchar(64),
"address1" varchar(64),
"address2" varchar(64)
);

create table "Job"
(
"ref" varchar(16),
"customer" varchar(16),
"description" varchar(512),
"location" varchar(16),
CONSTRAINT PK_job PRIMARY KEY (ref,customer),
CONSTRAINT FK_Job_customer FOREIGN KEY (customer) REFERENCES Customer(login),
CONSTRAINT FK_Job_location FOREIGN KEY (location) REFERENCES Location(name)
);

create table "JobSkill"
(
"job" varchar(16),
"customer" varchar(16),
"skill" varchar(16),
CONSTRAINT FK_JobSkill_job FOREIGN KEY (job,customer) REFERENCES Job(ref,customer),
CONSTRAINT FK_JobSkill_skill FOREIGN KEY (skill) REFERENCES Skill(name)
);

create table "Matched"
(
"applicant" varchar(16),
"job" varchar(16),
"customer" varchar(16),
"exact" boolean,
CONSTRAINT FK_Matched_job FOREIGN KEY (job,customer) REFERENCES Job(ref,customer),
CONSTRAINT FK_Matched_applicant FOREIGN KEY (applicant) REFERENCES Applicant(login)
);


INSERT INTO Location VALUES ('London','London UK');
INSERT INTO Location VALUES ('Washington','Washington DC, USA');
INSERT INTO Location VALUES ('Verona','Verona, Renaissance Italy');
INSERT INTO Location VALUES ('Wessex','Wessex, Kingdom of England');

INSERT INTO Skill VALUES ('Tree Surgeon','Tree Surgeon');
INSERT INTO Skill VALUES ('Cigar Maker','Cigar Maker');
INSERT INTO Skill VALUES ('Bodyguard','Bodyguard');
INSERT INTO Skill VALUES ('Cook','Cook');
INSERT INTO Skill VALUES ('Lawyer','Lawyer');
INSERT INTO Skill VALUES ('Critic','Critic');

INSERT INTO Applicant VALUES ('juliet','Juliet Capulet', 'juliet@localhost' , 'Dutiful daughter', 'London');
INSERT INTO Applicant VALUES ('romeo','Romeo Montague', 'romeo@localhost' , 'Dutiful son', 'Wessex');
INSERT INTO Applicant VALUES ('julius','Julius Caesar', 'julias@localhost' , 'Roman Emperor', 'Washington');
INSERT INTO Applicant VALUES ('brutus','Marcus Brutus', 'marcus@localhost' , 'Roman Senator', 'Washington');
INSERT INTO Applicant VALUES ('proteus','Proteus', 'proteus@localhost' , 'Gentleman', 'Verona');
INSERT INTO Applicant VALUES ('valentine','Valentine', 'valentine@localhost' , 'Gentleman', 'Verona');

INSERT INTO ApplicantSkill VALUES ('juliet', 'Cook');
INSERT INTO ApplicantSkill VALUES ('romeo', 'Cook');
INSERT INTO ApplicantSkill VALUES ('romeo', 'Bodyguard');
INSERT INTO ApplicantSkill VALUES ('julius', 'Tree Surgeon');
INSERT INTO ApplicantSkill VALUES ('julius', 'Tree Surgeon');
INSERT INTO ApplicantSkill VALUES ('brutus', 'Critic');
INSERT INTO ApplicantSkill VALUES ('brutus', 'Lawyer');
INSERT INTO ApplicantSkill VALUES ('proteus', 'Lawyer');
INSERT INTO ApplicantSkill VALUES ('proteus', 'Critic');
INSERT INTO ApplicantSkill VALUES ('valentine', 'Critic');
INSERT INTO ApplicantSkill VALUES ('valentine', 'Cigar Maker' );

INSERT INTO Customer VALUES ('george','George Washington', 'george@localhost', 'White House', 'Washington');
INSERT INTO Customer VALUES ('winston','Winston S Churchill', 'winston@localhost', '10 Downing Street', 'London');
INSERT INTO Customer VALUES ('abraham','Abraham Lincoln', 'abe@localhost', 'Springfield', 'Illinois');
INSERT INTO Customer VALUES ('alfred','Alfred the Great', 'alf@localhost', 'Wessex', 'England');

INSERT INTO Job VALUES ('Tree pruner', 'george', 'Must be honest', 'Washington');
INSERT INTO Job VALUES ('Cigar trimmer', 'winston', 'Must like to talk and smoke', 'London');
INSERT INTO Job VALUES ('Theatre goer', 'abraham', 'Should be intelligent and articulate', 'Washington');
INSERT INTO Job VALUES ('Cake maker', 'alfred', 'Should have a good sense of smell', 'Wessex');

INSERT INTO JobSkill VALUES ('Tree pruner','george','Tree Surgeon');
INSERT INTO JobSkill VALUES ('Cigar trimmer', 'winston', 'Cigar Maker');
INSERT INTO JobSkill VALUES ('Cigar trimmer', 'winston', 'Critic');
INSERT INTO JobSkill VALUES ('Theatre goer', 'abraham', 'Bodyguard');
INSERT INTO JobSkill VALUES ('Theatre goer', 'abraham', 'Lawyer');
INSERT INTO JobSkill VALUES ('Theatre goer', 'abraham', 'Critic');
INSERT INTO JobSkill VALUES ('Cake maker', 'alfred', 'Cook');