Blogs by Ganesh Babu N R: Java DB Derby Example

Blogs by Ganesh Babu N R: Java DB Derby Example

Java DB Derby Example

There are tons of information on Java DB or Derby. However I found that there was not a single place that gave a complete example to use Derby to store and retrieve XML Data Type - Derby in Server Mode of Operation.

So I thought I would post the code that got me going successfully - for the benefit of someone else, provided Google gets to this posting!

Installation - Note Derby and Java DB are synonymous - almost!

1. Install Java DB 10.5.3.0.
2. Create a Database -
   
   
3. In the java.policy file found in ${Java_Home}\jdk1.6.0\jre\lib\security add the below shown grant{} block
4. In the startNetworkServer.bat file - set the derby.system.home property.
5. Start the Derby Engine by double clicking startNetworkServer.bat [in Windows]
6. You should not see any java.security.access Exception for Derby.Log file - else revisit step-3
7. See if Derby.Log file is created in the derby.system.home. If you don't find, then check the startNetworkServer.bat folder [nothing but the /bin folder] - if a Derby.Log file is found here, then Revisit Step-3. If you don't clear this step, later on, when you run the program you will see "Database Jeevi Not Found" exception. Because Derby will search for "Jeevi" folder in /bin folder.
8. Create a New Java Project in NetBeans 6.5.1 - you may use the below package structure and copy paste the code as-is
9. Add Derby.jar, DerbyNet.jar and DerbyClient.jar to the NetBeans Project Library
10. Compile and Run
11. See the Output

@@@@@ Java.Policy file entries @@@@@

//all below lines are added for Derby to work
//NOTE: this throws open java permissions to every program
//better to use grant codeBase{derby.jar} and grant codeBase{derbynet} to restrict the access //only to derby
//this is an exhaustive list - see http://db.apache.org/derby/manuals/index.html#docs_10.5

grant{
permission java.util.PropertyPermission "user.home", "read";
permission java.util.PropertyPermission "java.home", "read";
permission java.lang.RuntimePermission "createClassLoader";
permission java.util.PropertyPermission "derby.*", "read";
permission java.io.FilePermission "C:\Program Files\Sun\JavaDB\*", "read,write,delete";
permission java.lang.RuntimePermission "createClassLoader";
permission java.util.PropertyPermission "derby.*", "read";
// The next two properties are used to determine if the VM is 32 or 64 bit.
permission java.util.PropertyPermission "sun.arch.data.model", "read";
permission java.util.PropertyPermission "os.arch", "read";
permission java.util.PropertyPermission "user.dir", "read";
permission java.util.PropertyPermission "derby.storage.jvmInstanceId", "write";
permission java.io.FilePermission "C:\Program Files\Sun\JavaDB","read";
permission java.io.FilePermission "C:\Program Files\Sun\JavaDB\*", "read,write,delete";
permission java.io.FilePermission "<>", "read,write,delete";
permission java.net.SocketPermission "*", "accept";
};

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Add this line just below ":runWithClasspath" line
set DERBY_OPTS=%DERBY_OPTS% -Dderby.system.home="C:\Program Files\Sun\JavaDB"

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

code:
package jdb.main;

import jdb.dao.ProcessSqlRequest;

public class Main {
public static void main(String[] args) {
ProcessSqlRequest p = new ProcessSqlRequest();
p.storeXML(); //stores an XML into Java DB as XML Datatype
p.fetchXML(); //retrieves XML from Java DB
}
}

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

package jdb.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class ProcessSqlRequest {
public void fetchXML() {
try {
Connection con = new DBCon1().getConnection();
Statement s = con.createStatement();
ResultSet rs = null;
if(con != null)
{
//rs = s.executeQuery("SELECT XMLSERIALIZE(DATA AS VARCHAR(3000)) FROM ARTICLE WHERE ID = 3");
rs = s.executeQuery("SELECT ID, XMLSERIALIZE(DATA AS VARCHAR(3000)) FROM ARTICLE");
System.out.println("Ahh.....done");
}
else
{
System.out.println("Huh ....Connection Object Null");
return;
}
while (rs.next()) {
int id = rs.getInt("ID");
String temp = rs.getString("2");
System.out.println("DATA is ::: " + id + " ::: " + temp);
}
s.close();
con.close();
} catch (Exception e) {
System.out.println("Exception in ProcessSqlRequest().fetchXML()" + e);
e.printStackTrace();
}
}

public void storeXML() {
try {
Connection con = new DBCon1().getConnection();
Statement s = con.createStatement();
if(con != null)
{
///int i = s.executeUpdate("insert into ARTICLE (ID,DATA) values (4, XMLPARSE(DOCUMENT CAST (‘Your XML' AS CLOB) PRESERVE WHITESPACE");
int i = s.executeUpdate("insert into ARTICLE (ID,DATA) values (8, XMLPARSE(DOCUMENT CAST ('Your XML' AS VARCHAR(3000)) PRESERVE WHITESPACE))");
System.out.println("Inserted " + i + "...Rows");
}
else
{
System.out.println("Huh ....Connection Object Null");
return;
}
s.close();
con.close();
} catch (Exception e) {
System.out.println("Exception in ProcessSqlRequest().storeXML()" + e);
e.printStackTrace();
}
}
}

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

package jdb.dao;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBCon1 {
//private static final String url = "jdbc:derby://localhost:1527/jeevi";
private static final String url = "jdbc:derby://127.0.0.1:1527/jeevi";

// Load Derby Network Client driver class.
// If you are running on JDK 6 or higher, you do not
// need to invoke Class.forName(). In that environment, the
// network client driver loads automatically.
public Connection getConnection() throws Exception {
Connection conn = null;
Class.forName("org.apache.derby.jdbc.ClientDriver");
conn = DriverManager.getConnection(url);
System.out.println("Sql Connection Successful");
return conn;
}
}
:code

@@@@@@@@@@@@@@@@@@@@@@@@@@@

The output in NetBeans Log Window should show this - if everything is working for you...

run:
Sql Connection Successful
Inserted 1...Rows
Sql Connection Successful
Ahh.....done
DATA is ::: 7 ::: Your XML
DATA is ::: 8 ::: Your XML

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

I am using NetBeans 6.5.1 and Java DB 10.5.3.0 and the above worked successfully.

Usage note from http://db.apache.org/derby/docs/10.2/ref/rreffuncxmlserialize.html
Derby requires that a JAXP parser, such as Apache Xerces, and that Apache Xalan are listed in the Java classpath for the XML functions to work. If either the JAXP parser or Xalan is missing from the classpath, attempts to use the XMLSERIALIZE operator will result in an error

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Thanks to all the tons of documentation on Java DB by Sun, Derby by Apache, Cloudscape by IBM. I referred all to arrive at the above successful program.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

I would be happy to see a simple comment - by you - if at all Google got you here.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Happy Derbyyyying !

Ganesh Babu N R

Java: DB Connection

How to Connect to DB?

import java.sql.Connection;

import java.sql.DriverManager;

/**

* This Class returns a Connection Object

*

*/

public class GetDBConnection 

{

   public GetDBConnection(){} //constructor

   

   public java.sql.Connection getConnection() 

   {

        Connection conn = null;

        try 

        {

            Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "ganesh", "ganesh");

            System.out.println("Successfully Connected to DB");

        } catch (Exception e) 

        {

            System.out.println("Exception Caught while Connecting to DB " + e.getMessage());

            e.printStackTrace();

        }

        return conn;

    }

}

############

import GetDBConnection;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

//Use the Connection object and make a connection to the db

//to check the credentials

public class ValidateUser 

{

    public boolean checkUser(String name, String pswd) 

    {

        Connection conn = null;

        PreparedStatement ps = null;

        ResultSet rs = null;

        try 

        {

            conn = new GetDBConnection().getConnection();

            ps = conn.prepareStatement("select password from PraanaUsers where username = ?");

            ps.setString(1, name);

            System.out.println("Executing SQLQUERY");

            rs = ps.executeQuery();

            System.out.println("SUCCESSFULLY EXECUTED RS");

            String temp = null;

            while (rs.next()) 

            {

                temp = rs.getString(1);

            }

            if (pswd.equals(temp)) 

            {

                System.out.println("User Exists...Validation Successful");

                return true;

            } else 

            {

                System.out.println("User Does Not Exist...Validation Failed");

                return false;

            }

        } catch (Exception e) 

        {

            e.printStackTrace();

        } finally 

        {

            try {

                if (rs != null) {

                    rs.close();

                }

                if (ps != null) {

                    ps.close();

                }

                if (conn != null) {

                    conn.close();

                }

            } catch (Exception e) {

                e.printStackTrace();

            }

        }

        return false;

    }

Ganesh Babu N R