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
