aspose file tools*
The moose likes JDBC and the fly likes derby Databse connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "derby Databse connection" Watch "derby Databse connection" New topic
Author

derby Databse connection

Yatin Shah
Ranch Hand

Joined: Sep 11, 2004
Posts: 72
Hello Guys,

I have to create two java classes that use derby to : a) create a database and b) retrieve data. I have created first one but getting exception. I tried so many ways to rid of this problem. Can any one help me? Please take a look question and code as follow:

Question-1:

Create two java classes that use derby to : a) create a database and b) retrieve data
Book: bookId, title, publisherName
BookAuthors: bookId, authorName ( bookId is a FK referencing Book )
Insert at least 3 rows into Book and 5 rows into BookAuthors
Retrieval: list the title of each book and the number of authors it has.

CODE:



import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;



import java.util.Properties;



public class CreateDatabase

{

public static String framework = "embedded";

public static String driver = "org.apache.derby.jdbc.EmbeddedDriver";

public static String protocol = "jdbc:derby:";



public static void main(String[] args)

{

go(args);

}



public static void go(String[] args)

{



System.out.println("Starting in " + framework + " mode.");



try

{

/*

The driver is installed by loading its class.

In an embedded environment, this will start up Derby, since it is not already running.

*/

Class.forName(driver).newInstance();

System.out.println("Loaded the appropriate driver.");



Connection conn = null;

Properties props = new Properties();

props.put("user", "user1");

props.put("password", "user1");



/*

The connection specifies create=true to cause

the database to be created. To remove the database,

remove the directory DerbyDatabase1 and its contents.

The directory DerbyDatabase1 will be created under

the directory that the system property

derby.system.home points to, or the current

directory if derby.system.home is not set.

*/

conn = DriverManager.getConnection(protocol + "DerbyDatabase2;create=true", props);



System.out.println("Connected to and created database DerbyDatabase2");



conn.setAutoCommit(false);



/*

statement for issuing commands

*/

Statement s = conn.createStatement();



/*

create a table Book.

*/

s.execute("create table Book(bookID int not null primary key, title varchar(20), publisherName varchar(40))");

System.out.println("Table Book creation process successfully!");



/*

create a table BookAuthors.

*/

s.execute("create table BookAuthors(bookID int not null foreign key, authorName varchar(40))");

System.out.println("Table BookAuthors creation process successfully!");



/*

Insert value into table Book.

*/

s.execute("insert into Book values (1001,'HTML, XHTML And DHTML')");

s.execute("insert into Book values (1002,'Discrete Mathematics')");

s.execute("insert into Book values (1003,'Fundamental of Database')");

System.out.println("Finished inserts value to Book table.");



/*

Insert value into table BookAuthors.

*/

s.execute("insert into BookAuthors values (1001,'Patrick Carey')");

s.execute("insert into BookAuthors values (1002,'Kenneth H. Rosen')");

s.execute("insert into BookAuthors values (1003,'Brian Preed')");

s.execute("insert into BookAuthors values (1001,'C J Date')");

s.execute("insert into BookAuthors values (1910,'Shamkant B. Navathe.')");

System.out.println("Finished inserts value to BookAuthors table.");



/*

release the statement resources.

*/

s.close();

System.out.println("Closed statement");



/*

We end the transaction and the connection.

*/

conn.commit();

conn.close();

System.out.println("Committed transaction and closed connection");



/*

In embedded mode, an application should shut down Derby.

If the application fails to shut down Derby explicitly,

then Derby does not perform a checkpoint when the JVM shuts down, which means

that the next connection will be slower.

Explicitly shutting down Derby with the URL is preferred.

This style of shutdown will always throw an "exception".

*/

boolean gotSQLExc = false;



if (framework.equals("embedded"))

{

try

{

DriverManager.getConnection("jdbc:derby:;shutdown=true");

}

catch (SQLException se)

{

gotSQLExc = true;

}



if (!gotSQLExc)

{

System.out.println("Database did not shut down normally");

}

else

{

System.out.println("Database shut down normally");

}

}

}

catch (Throwable e)

{

System.out.println("exception thrown:");



if (e instanceof SQLException)

{

printSQLError((SQLException) e);

}

else

{

e.printStackTrace();

}

}



System.out.println(" finished");

}



public static void printSQLError(SQLException e)

{

while (e != null)

{

System.out.println(e.toString());

e = e.getNextException();

}

}

}



Please reply soon. Awaiting for your reply.

Regards,
Yatin
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Please, use code tags around your code, it makes your code easier to read:
http://faq.javaranch.com/java/UseCodeTags





What exceptions do you get ? Post them here.

When I run your code, I got errors at this instruction:

This SQL clause is wrong, there is no FOREIGN KEY inline column level constraint in create table statement ,
refer to the SQL reference here:
http://db.apache.org/derby/docs/10.6/ref/rrefsqlj24513.html

If you want to create foreign key constraint, you must use 'references' clause, in this way:

CREATE TABLE table_name
column_name column_type REFERENCES table-Name [ ( Simple-column-Name [ , Simple-column-Name ]* ) ]
........

Refer documentation for proper syntax:
http://db.apache.org/derby/docs/10.6/ref/rrefsqlj16357.html#rrefsqlj16357
Yatin Shah
Ranch Hand

Joined: Sep 11, 2004
Posts: 72
Hi,

Thanks for reply and sorry for inconvenience. I am getting following error:

[error]

C:\Yatin\University\Winnipeg\Fall Term\Assignments\ACS-3902\Assignment4\CreateDatabase>java CreateDatabase
Starting in embedded mode.
exception thrown:
java.lang.ClassNotFoundException: org.apache.derby.jdbc.EmbeddedDriver
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:164)
at CreateDatabase.go(CreateDatabase.java:32)
at CreateDatabase.main(CreateDatabase.java:18)
finished
[/error]


Regards,
Yatin
Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

You need to add your derby JDBC driver jar to the classpath, when running your code.

Like this:


Regards, Rene Larsen
Dropbox Invite
 
 
subject: derby Databse connection