Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 4 of 4

Thread: Java (Derby) Help

  1. #1
    Junior Member
    Join Date
    Aug 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Java (Derby) Help

    I'm trying to create a simple database, a library. I've found that for my purposes JavaDB (Apache Derby) will do what I'm looking for.

    I've downloaded the bin file from the Apache Derby site, and run through their SimpleApp example. I've tried to figure out the code but I can't seem to make heads or tails of it.

    I've posted a slightly edited version of the code. ATM, I'm just trying to figure out how to connect to a DB and create a table.

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
     
    import java.util.ArrayList;
    import java.util.Properties;
     
    public class Simple
    {
        /* the default framework is embedded*/
        private String framework = "embedded";
        private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
        private String protocol = "jdbc:derby:";
     
        public static void main(String[] args)
        {
            new Simple().go(args);
            System.out.println("Simple finished");
        }
     
        void go(String[] args)
        {
            System.out.println("Simple starting in " + framework + " mode");
     
            /* load the desired JDBC driver */
            loadDriver();
     
            Connection conn = null;
     
            PreparedStatement psInsert = null;
            PreparedStatement psUpdate = null;
            Statement s = null;
            ResultSet rs = null;
            try
            {
                Properties props = new Properties(); // connection properties
                // providing a user name and password is optional in the embedded
                // and derbyclient frameworks
                props.put("user", "user1");
                props.put("password", "user1");
     
                String dbName = "derbyDB"; // the name of the database
     
                conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
     
                System.out.println("Connected to and created database " + dbName);
     
                // We want to control transactions manually. Autocommit is on by
                // default in JDBC.
                conn.setAutoCommit(false);
     
                /* Creating a statement object that we can use for running various
                 * SQL statements commands against the database.*/
                s = conn.createStatement();
                statements.add(s);
     
                // We create a table...
                s.execute("create table location(num int, addr varchar(40))");
                System.out.println("Created table location");
     
                // and add a few rows...
     
                // parameter 1 is num (int), parameter 2 is addr (varchar)
                psInsert = conn.prepareStatement(
                            "insert into location values (?, ?)");
                statements.add(psInsert);
     
                psInsert.setInt(1, 1956);
                psInsert.setString(2, "Webster St.");
                psInsert.executeUpdate();
                System.out.println("Inserted 1956 Webster");
     
                psInsert.setInt(1, 1910);
                psInsert.setString(2, "Union St.");
                psInsert.executeUpdate();
                System.out.println("Inserted 1910 Union");
     
                // Let's update some rows as well...
     
                // parameter 1 and 3 are num (int), parameter 2 is addr (varchar)
                psUpdate = conn.prepareStatement(
                            "update location set num=?, addr=? where num=?");
                statements.add(psUpdate);
     
                psUpdate.setInt(1, 180);
                psUpdate.setString(2, "Grand Ave.");
                psUpdate.setInt(3, 1956);
                psUpdate.executeUpdate();
                System.out.println("Updated 1956 Webster to 180 Grand");
     
                psUpdate.setInt(1, 300);
                psUpdate.setString(2, "Lakeshore Ave.");
                psUpdate.setInt(3, 180);
                psUpdate.executeUpdate();
                System.out.println("Updated 180 Grand to 300 Lakeshore");
     
                //We select the rows and verify the results.
                rs = s.executeQuery(
                        "SELECT num, addr FROM location ORDER BY num");
     
                int number; // street number retrieved from the database
                boolean failure = false;
                if (!rs.next())
                {
                    failure = true;
                    reportFailure("No rows in ResultSet");
                }
     
                if ((number = rs.getInt(1)) != 300)
                {
                    failure = true;
                    reportFailure(
                            "Wrong row returned, expected num=300, got " + number);
                }
     
                if (!rs.next())
                {
                    failure = true;
                    reportFailure("Too few rows");
                }
     
                if ((number = rs.getInt(1)) != 1910)
                {
                    failure = true;
                    reportFailure(
                            "Wrong row returned, expected num=1910, got " + number);
                }
     
                if (rs.next())
                {
                    failure = true;
                    reportFailure("Too many rows");
                }
     
                if (!failure) {
                    System.out.println("Verified the rows");
                }
     
                // delete the table
                s.execute("drop table location");
                System.out.println("Dropped table location");
     
                /*
                   We commit the transaction. Any changes will be persisted to
                   the database now.
                 */
                conn.commit();
                System.out.println("Committed the transaction");
     
                if (framework.equals("embedded"))
                {
                    try
                    {
                        // the shutdown=true attribute shuts down Derby
                        DriverManager.getConnection("jdbc:derby:;shutdown=true");
                    }
                    catch (SQLException se)
                    {
                        if (( (se.getErrorCode() == 50000)
                                && ("XJ015".equals(se.getSQLState()) ))) {
                            // we got the expected exception
                            System.out.println("Derby shut down normally");
                            // Note that for single database shutdown, the expected
                            // SQL state is "08006", and the error code is 45000.
                        } else {
                            // if the error code or SQLState is different, we have
                            // an unexpected exception (shutdown failed)
                            System.err.println("Derby did not shut down normally");
                            printSQLException(se);
                        }
                    }
                }
            }
            catch (SQLException sqle)
            {
                printSQLException(sqle);
            } finally {
                // release all open resources to avoid unnecessary memory usage
     
                // ResultSet
                try {
                    if (rs != null) {
                        rs.close();
                        rs = null;
                    }
                } catch (SQLException sqle) {
                    printSQLException(sqle);
                }
     
                // Statements and PreparedStatements
                int i = 0;
                while (!statements.isEmpty()) {
                    // PreparedStatement extend Statement
                    Statement st = (Statement)statements.remove(i);
                    try {
                        if (st != null) {
                            st.close();
                            st = null;
                        }
                    } catch (SQLException sqle) {
                        printSQLException(sqle);
                    }
                }
     
                //Connection
                try {
                    if (conn != null) {
                        conn.close();
                        conn = null;
                    }
                } catch (SQLException sqle) {
                    printSQLException(sqle);
                }
            }
        }
     
        private void loadDriver() {
     
            try {
                Class.forName(driver).newInstance();
                System.out.println("Loaded the appropriate driver");
            } catch (ClassNotFoundException cnfe) {
                System.err.println("\nUnable to load the JDBC driver " + driver);
                System.err.println("Please check your CLASSPATH.");
                cnfe.printStackTrace(System.err);
            } catch (InstantiationException ie) {
                System.err.println(
                            "\nUnable to instantiate the JDBC driver " + driver);
                ie.printStackTrace(System.err);
            } catch (IllegalAccessException iae) {
                System.err.println(
                            "\nNot allowed to access the JDBC driver " + driver);
                iae.printStackTrace(System.err);
            }
        }
     
        private void reportFailure(String message) {
            System.err.println("\nData verification failed:");
            System.err.println('\t' + message);
        }
     
        public static void printSQLException(SQLException e)
        {
            // Unwraps the entire exception chain to unveil the real cause of the
            // Exception.
            while (e != null)
            {
                System.err.println("\n----- SQLException -----");
                System.err.println("  SQL State:  " + e.getSQLState());
                System.err.println("  Error Code: " + e.getErrorCode());
                System.err.println("  Message:    " + e.getMessage());
                // for stack traces, refer to derby.log or uncomment this:
                //e.printStackTrace(System.err);
                e = e.getNextException();
            }
        }
    }

    Thanks to anyone who can offer some insight.


  2. #2
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,358
    Thanks
    182
    Thanked 834 Times in 777 Posts
    Blog Entries
    5

    Default Re: Java (Derby) Help

    Can you explain the problem a bit more? Does your code compile? Are your receiving any exceptions when you run the code?

  3. #3
    Junior Member
    Join Date
    Aug 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Java (Derby) Help

    There isn't a problem, it complies and does what its supposed to do. Its just I don't know how it does it. Or what it does.

  4. #4
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,358
    Thanks
    182
    Thanked 834 Times in 777 Posts
    Blog Entries
    5

    Default Re: Java (Derby) Help

    Thoroughly explaining that code could take quite some time. The comments in there should explain quite a bit. But to add to them:
    1) you load the driver
    2) establish the connection to the database
    3) Make your SQL calls using Statement and PreparedStatement objects. These are grouped into a transaction (allows you to group database updates): the transaction is then committed (usually, you catch exceptions during the transation and, if an error occurs, rollback the changes to maintain database integrity)
    Hope this helps. It'd be much easier to answer more specific questions you may have.

Similar Threads

  1. org.apache.derby does not exist?
    By disclaimer in forum What's Wrong With My Code?
    Replies: 0
    Last Post: March 30th, 2010, 05:58 PM