Accessing CUBRID with
JDBC - Part I
February 2013
It’s been a while since we published our first JDBC tutorial, in a time when CUBRID was still using with the GLO data type… The time has come to take another look at the CUBRID JDBC driver!
We will be looking together in the first part of the tutorial at the driver basics, and the second part will be dedicated to specific CUBRID features.
Before we start, these are the software prerequisites you should have installed:
- CUBRID 9.0 (here)
- Java SDK/JRE (you can find here the installation instructions)
- Recommended –IntelliJ IDEA Community edition – you can find it here. For those of you who don’t know yet, this is one of the best cross platform Java IDE available for free. Of course, you can also use any other IDE you prefer – Eclipse, NetBeans etc.
|
As for the driver installation, the key thing to remember is that the driver itself is part of the CUBRID engine installation! This means that
once you have installed CUBRID, the driver is already installed on your
system. You will find in in the CUBRID installation folder, in the \jdbc subfolder – cubrid_jdbc.jar. |
General notes about the
CUBRID JDBC driver
The Java Database Connectivity API - JDBC - is the industry Java standard for database-independent connectivity between Java and SQL databases. Each major RDBMS implements JDBC support and CUBRID is no exception. Actually, both main CUBRID managers – CUBRID Manager Client and CUBRID Query Browser – are developed in Java, using the CUBRID JDBC driver! …and it is not the only example – see also the CUBRID Migration Toolkit.
You can read more about JDBC here:
http://www.dmoz.org/Computers/Programming/Languages/Java/Databases_and_Persistence/JDBC/
If you are already familiar with the JDBC standard, you know that, the same as for ODBC, there are different levels of standards compliance:
http://oracle.su/docs/11g/java.112/e10589/jdbcvers.htm. Currently, the CUBRID JDBC driver is compliant with the JDBC 2.0 standard – see http://www.cubrid.org/wiki_apis/entry/cubrid-jdbc-compatibility. As a side note, there is some JDBC API 3.0 functionality implemented as well.
On top of the standard JDBC functionality, the driver implements a set of extensions specific to CUBRID – see more in the next sections of the tutorial.
|
Tip: You can find the full JDBC driver API documentation online: |
A first JDBC program
The first small program we will develop using the CUBRID JDBC driver will only do the followings basic operations:
- Connect to the demodb database
- Query the JDBC driver version used
- Disconnect
But before we start, let’s create an IntelliJ Java project and add the CUBRID JDBC driver library to the project.
To add the CUBRID JDBC driver to the project go to File -> Project Structure, and select the Libraries tab. Then, click on the “+” button and select Java. Navigate to your CUBRID installation path (default is C:\CUBRID), and you will find cubrid_jdbc.jar in the \jdbc folder. Click OK and you are done.
And this is the code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
class Startup {
public static void main(String args[]) throws SQLException, ClassNotFoundException {
// Load CUBRID JDBC driver
Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
// Connect to the "demodb" database, with user "public"
Connection connection = DriverManager.getConnection("jdbc:cubrid:localhost:30000:demodb:public::");
// Get CUBRID JDBC driver info
System.out.println("CUBRID JDBC driver version is: " + connection.getMetaData().getDriverVersion());
// Cleanup
connection.close();
}
}
As you can see, there’s nothing different in the above code when compared to any other usual JDBC drivers – MySQL, PostgreSQL etc.
When you run the code using the current CUBRID 9 release as of this tutorial published date, you get this output:
CUBRID JDBC driver version is: 9.0.0.0478
|
You can find all the details about the CUBRID JDBC connection parameters here. Remember, you can always specify username and password separately: String userid
= "public"; String
password = ""; Connection connection = DriverManager.getConnection(url, userid, password); |
“Hello, World!” and the CUBRID extensions
Let’s go on further in the spirit of the Hello, World! (You can read some interesting things about the “Hello, World” programming fame on Wikipedia).
Look at the following two “Hello, World!” code snippets:
Code snippet 1
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// Load the driver
Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
// Connect to the "demodb" database, with user "public"
Connection connection = DriverManager.getConnection("jdbc:cubrid:localhost:30000:demodb:public::");
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select 'Hello, World!'");
rs.next();
System.out.println(rs.getString(1));
// Cleanup
rs.close();
stmt.close();
connection.close();
}
Code snippet 2
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// Load the driver
Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
// Connect to the "demodb" database, with user "public"
CUBRIDConnection connection = (CUBRIDConnection) DriverManager.getConnection("jdbc:cubrid:localhost:30000:demodb:public::");
CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();
CUBRIDResultSet rs = (CUBRIDResultSet) stmt.executeQuery("select 'Hello, World!'");
rs.next();
System.out.println(rs.getString(1));
// Cleanup
rs.close();
stmt.close();
connection.close();
}
The question is: what is the difference between them two, from the end user perspective?
When we execute them, we will get the same, identical result:
|
We recommend using the JDBC standard/generic objects declaration whenever possible, especially when your application will have to deal with other databases beside CUBRID! Do not write dozens of if-else, it’s much better to use one-only-running-everywhere “generic” code. |
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// Load the driver
Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
// Connect to the "demodb" database, with user "public"
CUBRIDConnection connection = (CUBRIDConnection) DriverManager.getConnection("jdbc:cubrid:localhost:30000:demodb:public::");
CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();
CUBRIDResultSet rs = (CUBRIDResultSet) stmt.executeQuery("select event from event");
rs.next();
CUBRIDOID oid = rs.getOID(1);
System.out.println(oid.getOidString() + " :: " + oid.getTableName());
// Cleanup
rs.close();
stmt.close();
connection.close();
}
The code output is:
Btw, detailed information about the rules of using OIDs in Java can be found here.
|
Please note that the JDBC driver has a place of its own in the CUBRID manual: http://www.cubrid.org/manual/90/en/JDBC%20Driver. You will find there documentation, code samples and many other interesting things which are not listed in the standard tutorials cubrid.org web site section. |
Of course, the question you are probably asking yourself now is: what are the other CUBRID extensions available, besides OIDs?
The quick answer is:
- Remember the specific CUBRID data types? For example, set, multiset, BLOB etc.? Obviously, there are CUBRID extensions to deal with them.
- There is a Java.sql.CUBRIDException implementation
- There are some other extensions as well - more information can be found here.
And we encourage you to take a look (and contribute!) at the driver code; as with everything in the CUBRID world, the JDBC driver is open source!
Querying metadata
Many times the user need to query metadata… …schema info, query information and so on. The JDBC API standard already provides a powerful interface to deal with this. Let’s do a quick test on CUBRID:
DatabaseMetaData meta = connection.getMetaData();
System.out.println();
System.out.println("Database Product name is: " + meta.getDatabaseProductName());
System.out.println("Database Product version is: " + meta.getDatabaseProductVersion());
System.out.println("CUBRID JDBC Driver name is: " + meta.getDriverName());
System.out.println("Database features:");
System.out.println(String.format(" - Stores mixed case identifiers: %s", meta.storesMixedCaseIdentifiers()));
System.out.println(String.format(" - Supports the ANSI92 entry level SQL grammar: %s", meta.supportsANSI92EntryLevelSQL()));
System.out.println(String.format(" - Supports the ANSI92 full level SQL grammar: %s", meta.supportsANSI92FullSQL()));
System.out.println(String.format(" - Supports transactions: %s", meta.supportsTransactions()));
Database Product version is: 9.0.0.0478
CUBRID JDBC Driver name is: CUBRID JDBC Driver
Database features:
- Stores mixed
case identifiers: false
- Supports the
ANSI92 entry level SQL grammar: true
- Supports the
ANSI92 full level SQL grammar: false
- Supports
transactions: true
But more interesting would be to check some CUBRID specifics, right...? As it turns out, there is actually a CUBRID extension in the driver for this – CUBRIDResultSetMetadata:
// 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(2));
System.out.println("Element type name: " + ((CUBRIDResultSetMetaData) rs.getMetaData()).getElementTypeName(2));
The result of the code above is:
Element type:4
Element type name: INTEGER
…So what happened to GLO…?
At the end of this first part of the tutorial, let’s clarify what happened to the GLO data type, which we featured in the previous tutorial. Well, just in case you missed this, the GLO data type has been discontinued since 8.4.0 CUBRID release and it was replaced with LOB data types (BLOB, CLOB). As for how to use the new data types in Java, there are already some examples published online – see http://www.cubrid.org/wiki_tutorials/entry/inserting-a-blob-file-to-a-cubrid-database.
Let’s extend those examples with one more – let’s insert and retrieve an image file into a BLOB field. Obviously, as we are dealing with CUBRID specific extensions, which are not part of the JDBC API standards, we will have to use non-generic JDBC objects.
Inserting the image:
// Read the file content
FileInputStream inputFile = new FileInputStream("CUBRID.jpg");
// Insert the file into the BLOB field
String sql = "insert into test_blob values(?)";
CUBRIDPreparedStatement insert_stmt = (CUBRIDPreparedStatement) connection.prepareStatement(sql);
insert_stmt.setBlob(1, inputFile);
insert_stmt.execute();
insert_stmt.close();
inputFile.close();
Retrieving the image:
String sql = "select image from test_blob";
Statement stmt = connection.createStatement();
CUBRIDResultSet rs = (CUBRIDResultSet)stmt.executeQuery(sql);
rs.next();
CUBRIDBlob bImage = (CUBRIDBlob) rs.getBlob(1);
byte[] bArray = bImage.getBytes(1, (int)bImage.length());
// Write the BLOB data to file
FileOutputStream outputFile = new FileOutputStream("CUBRID_out.jpg");
outputFile.write(bArray);
outputFile.close();
References and links
|
Driver downloads |
|
|
Driver home page |
http://www.cubrid.org/wiki_apis/entry/about-cubrid-jdbc-driver |
|
Tutorials and Guides |
|
|
CUBRID Q&A |
|
|
CUBRID JDBC API documentation |
|
|
JDBC 2.0 standard |
http://www.karunya.edu/cse/collaboration/sun/resources/jdbc20.stdext.pdf |
|
Source code |
http://sourceforge.net/projects/cubrid/files/CUBRID-Drivers/JDBC_Driver/ |
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 tutorial! The 2nd part will be available soon.
Please 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