Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register

Accessing CUBRID with JDBC - Part II

February 2013

This is the 2nd part of the latest CUBRID JDBC tutorial we have published recently. In this part, we will go through some more advanced topics, to help you start quickly with CUBRID in a Java development environment.

The software prerequisites are the same we mentioned in the 1st part, so if all is set and done, we are ready to move on!


Database schema


JDBC comes up with everything you need to query and change a database schema. Remember – CUBRID Manager Client is using JDBC to provide all those powerful tools to manage a CUBRID database!

Have you wondered, for example, how does the CUBRID Manager get a table SQL schema definition…?

 schema.png

Well, there is no “magic” in JDBC which simply gives a SQL table definition back to you in one call – it’s just Java code which uses the Metadata API standard.

So, we challenge you, as an exercise, to write the JAVA JDBC code that does create a table DLL SQL definition! J

In the example below, for a start, we will show you how to create the DDL SQL for an index (for simplicity we will consider that there is just one index defined on the table – let’s use the event demodb table):

public static String getIndexDDL(String tableName) throws SQLException, ClassNotFoundException {
    String indexRetrieve = "SELECT * FROM db_index" + "WHERE class_name = '" + tableName + "'";
    String SQL = "CREATE ";
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(indexRetrieve);
    rs.next();
    SQL += (rs.getString("is_unique").equals("YES") == true ? "UNIQUE " : "") +
        (rs.getString("is_reverse").equals("YES") == true ? "REVERSE " : "") +
        "INDEX \"" + rs.getString("index_name") +
        "\" ON \"" + rs.getString("class_name") + "\"(\n";
    Statement stmt2 = conn.createStatement();
    ResultSet rs2 = stmt2.executeQuery("SELECT * FROM db_index_key " +
        "WHERE class_name = '" + tableName + "' AND " +
        "index_name = '" + rs.getString("index_name") + "'");
    if (rs2.next()) {
      SQL += "\"" + rs2.getString("key_attr_name") + "\" " + rs2.getString("asc_desc");
    }
    while (rs2.next()) {
      SQL += ",\n\"" + rs2.getString("key_attr_name") + "\" " + rs2.getString("asc_desc");
    }
    SQL += "\n);";
 
    stmt2.close();
    rs2.close();
    stmt.close();
    rs.close();
    conn.close();
     
    return SQL;
}

The output is:

CREATE UNIQUE INDEX "pk_event_code" ON "event"(
"code" ASC
);

 

Error handling

 

Proper error handling is a key element in developing a reliable application. Many things can go wrong when your application deals with a database - it can be your code causing an error or it can be the database fault (for example - the database is not up & running, or there are no correct credentials etc.).

For example, let’s consider a code example from the 1st part of the tutorial, with a minor change: 

// Create test table
stmt.execute("drop table if exists test_metadata");
stmt.execute("create table test_metadata(id int, seq set(int))");

// Query table metadata
ResultSet rs = stmt.executeQuery("select * from test_metadata");
System.out.println("Element type:" + ((CUBRIDResultSetMetaData) rs.getMetaData()).getElementType(1));
System.out.println("Element type name: " + ((CUBRIDResultSetMetaData) rs.getMetaData()).getElementTypeName(1));

When you run this code, the following error is displayed:

Exception in thread "main" cubrid.jdbc.driver.CUBRIDException: The type of the column should be a collection type.
                at cubrid.jdbc.driver.CUBRIDResultSetMetaData.getElementType(CUBRIDResultSetMetaData.java:658)
                atError.main(Error.java:20)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                atjava.lang.reflect.Method.invoke(Method.java:601)
                at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

So what do you do…?

In this particular case, the error is on the application side – the wrong column index was used (“1” instead of “2”). So the right thing to do is fix the code and re-run.

As an exception, if you are not familiar with CUBRID or the error message needs more clarification, what you can do is to look directly at the error messages in the JDBC driver (remember, it’s all open source!). You will find the CUBRID specific error messages and codes in the file CUBRIDJDBCErrorCode.java.

On the other hand, you should consider using try-catch for unexpected errors. Here is an error handling code example you could use in your own application:

Some code to simulate an unrecoverable error:

try {
    ResultSet rs = stmt.executeQuery("select * from non_existing_table, and get an error!");
    rs.next();
    rs.close();
} catch (SQLException ex) {
    for (Throwable e : ex) {
        if (e instanceofSQLException) {
            e.printStackTrace(System.err);
            System.err.println("SQLState: " + ((SQLException) e).getSQLState());
            System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
            System.err.println("Message: " + e.getMessage());
            Throwable t = ex.getCause();
            while (t != null) {
                System.out.println("Cause: " + t);
                t = t.getCause();
          }
        }
      }
}

The output result is:

SQLState: null
Error Code: -493
Message: jdbc:cubrid:localhost:30000:demodb:public:********:
Syntax: In line 1, column 35 before ' get an error!'
Syntax error: unexpected 'and'
 

As you can see, usually the error messages provided in the CUBRID JDBC driver can be very helpful to the end user to track exactly the root problem, all you have to do is to implement a proper error handling logic in your application.

You can find more useful information about error handling and JDBC here.

 

Using transactions in JDBC


In this section we will take a look at using database transactions with JDBC.

In particular we will show:

-          How to get information about the current database status: isolation level, auto-commit mode

-          An example of nested transactions with error handling

Some more information about transactions can be found here:

http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

 Getting information about the current database status:

// Get the database Transaction Isolation Level
System.out.print("Isolation level: ");
switch(connection.getTransactionIsolation()) {
    case 1:
        System.out.println("READ COMMITTED CLASS with READ UNCOMMITTED INSTANCES");
        break;
    case 2:
        System.out.println("READ COMMITTED CLASS with READ COMMITTED INSTANCES");
        break;
    case 3:
        System.out.println("REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES");
        break;
    case 4:
        System.out.println("REPEATABLE READ CLASS with READ COMMITTED INSTANCES (or CURSOR STABILITY)");
        break;
    case 5:
        System.out.println("REPEATABLE READ CLASS with REPEATABLE READ INSTANCES");
        break;
    case 6:
        System.out.println("SERIALIZABLE");
}
// Get the database AutoCommit Mode
System.out.println("AutoCommit mode: " + connection.getAutoCommit());

The output result is:

Isolation level: READ COMMITTED CLASS with READ UNCOMMITTED INSTANCES
AutoCommit mode: true
 

Some more information about CUBRID Isolation Level can be found here:

http://www.cubrid.org/manual/90/en/Transaction%20Isolation%20Level


Nested transactions with error handling:

We will create a table in an outer transaction and in the inner transaction we will create a view based on the table. If the view creation fails (we will force it to fail for the demo purpose), the table creation will be rollback.

As you probably know, you can’t really embed transactions in CUBRID.

To implement nested transactions, you need to use SAVEPOINTS.

Statement stmt = connection.createStatement();
try{
    connection.setAutoCommit(false);
    stmt.execute("SAVEPOINT SP1");
    stmt.execute("create table test_tran(id int)");
    stmt.execute("SAVEPOINT SP2");
    stmt.execute("create view test_view as select nothing from test_tran");
    connection.commit();
} catch(SQLException ex) {
    System.out.println("Rolling back the transaction!");
    stmt.execute("ROLLBACK WORK TO SP1");
}
 

CUBRID Java Stored procedures


If you are familiar with CUBRID, you know that one of the most powerful CUBRID features is the support for Java stored procedures. There is almost no limit to what you can achieve when you combine stored procedures with the CUBRID engine!

We have already published an introduction about Java stored procedures – please take a look here.

Another interesting example can be found on this blog.

Here, we will demo an example of using stored procedures and JDBC, to implement a “missing” SQL feature – create a copy of a VIEW. You supply an existing VIEW name, the desired duplicate VIEW name and the procedure will create the duplicated VIEW.

Looking from the technical perspective, the code will look at the VIEW definition in the system objects and will use it to create the new VIEW:

import java.sql.*;
import cubrid.jdbc.driver.*;

public class StoredProc {
    public static void duplicateView(String view_name, String new_name) {
        try {
            Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
            Connection connection = DriverManager.getConnection("jdbc:default:connection:");
            PreparedStatement pstmt = connection.prepareStatement("select vclass_def from db_vclass where vclass_name = ?");
            pstmt.setString(1, view_name);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()) {
                Statement stmt = connection.createStatement();
                stmt.execute("CREATE VIEW " + new_name + " AS " + rs.getString(1));
                stmt.close();
            }
            pstmt.close();
            connection.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
    }
}

Next we have to compile the java class and load it to the demodb database using the loadjava utility.

javac -cp "C:\CUBRID\jdbc\cubrid_jdbc.jar" StoredProc.java
C:\CUBRID\bin\loadjava.exe -y demodb StoredProc.class

After loading the java class to the database, we will use CUBRID Manager to create the stored procedure and call it. So login to the demodb database, right-click on the Stored procedure node and select Create Procedure. Fill in the requested fields:

create_procedure.png

To test the procedure let’s create a view to duplicate. In the Query Editor run the following SQL:

CREATE VIEW test_view AS SELECT * FROM code

Now we are ready to use the procedure we just created by running the following SQL script in the Query Editor.

CALL duplicate_view('test_view', 'copy_of_test_view')

As you can see, the copy of test_view was created and we can display the results:

call_procedure.png 

More references and links

We already introduced you in the first part of the tutorial to some useful references and links. Here, we will give some more, to help you find online other great code samples and documentation.

Java CUBRID Stored procedures

http://www.cubrid.org/cubrid_java_stored_procedures

CUBRID Hibernate

http://www.cubrid.org/cubrid_hibernate_tutorial

JDBC sample program

http://www.cubrid.org/manual/90/en/JDBC%20Sample%20Program

SourceForge CUBRID JDBC examples

http://sourceforge.net/projects/cubridsrccode/?source=directory

JDBC tutorial

http://www.tutorialspoint.com/jdbc/index.htm

If you would like to get the code we used in this tutorial, as well as the IntelliJ project – please contact us at: http://www.cubrid.org/contact.

We hope you enjoyed this new CUBRID JDBC tutorial!

As always, we kindly ask you to let us know your feedback and suggestions, so we can improve!

And remember to stay tuned for more tutorials to come…

 

Thank you!

The CUBRID API team




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: