Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register

CUBRID JDBC Tutorial (for CUBRID 8.3.1 and below)

You may write CUBRID powered applications in a variety of languages. The languages that probably most people use to write CUBRID applications are PHP and Java. We will present you in this article some examples of using CUBRID from Java applications for those of you who are beginners in using JDBC and CUBRID or for those of you who are already familiar with the JDBC, but would like to see some CUBRID specifics.

CUBRID JDBC

When you install CUBRID it will automatically install the JDBC driver. On Windows you can find it at the "$CUBRID\jdbc\cubrid_jdbc.jar" location. If you need the JDBC driver for other applications you can download it separately at Sourceforge.net.

CUBRID JDBC connector works within the framework of the JAVA JDBC interface, an API that allows Java programs to use database servers in a portable way. JDBC is based on an approach similar to that used in the design of Perl and Ruby DBI modules, Python's DB-API module, and PHP's PEAR::DB class. This approach uses a two-tier architecture:

  • The top level is visible to application programs and presents an abstract interface for connecting to and using database engines. The application interface does not depend on details specific to particular engines.
  • The lower level consists of drivers for individual database engines. Each driver handles the details necessary to map the abstract application interface onto operations that a specific engine will understand.

The JDBC interface allows developers to write applications that can be used with different databases with a minimum of porting effort. Once a driver for a given server engine is installed, JDBC applications can communicate with any server of that type. By using CUBRID JDBC connector, your Java programs can access CUBRID databases.

Examples

Connect, Disconnect

We will start with the CubridStartup.java file, to see how we can perfrom some fundamental operations: connect and disconnect:

import java.sql.*;
import java.lang.*;

public class CubridStartup {
    public static void main(String[] args) {
        Connection connection = null;

        try {
            Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
            // Connect to the "demodb" database
            connection = DriverManager.getConnection("jdbc:cubrid:localhost:30000:demodb:public::");
            System.out.println("Connected to demodb.");
            connection.close();
            System.out.println("Disconnected from demodb.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Create, Insert, Count, Delete, Drop

Now that you know how to perform fundamental operations in CUBRID we can move to the next sample called CubridBasic.java which contains some basic operations that can be done in CUBRID from the Java code (create, insert, count, delete, drop):

import java.lang.*;

public class CubridBasic {
    public static void main(String[] args) {
        CubridDB cubridDB = new CubridDB();
        String test_table = "test_table";

        try {
            cubridDB.init();
            // Drop test table if exists
            if (cubridDB.tableExists(test_table)) {
                cubridDB.dropTable(test_table);
            }
            // Create test table
            cubridDB.createTestTable(test_table);
            // Insert test data
            cubridDB.insertTestData(test_table);
            // Insert test data and show last insert ids
            cubridDB.insertTestDataGetID(test_table);
            // Output record count
            System.out.println("          Records count: " + cubridDB.countRecordsTable(test_table));
            // Show metadata from the table
            cubridDB.outputMetaData(test_table);
            // Show data from the table
            cubridDB.outputResultSet(test_table);
            // Delete test data
            cubridDB.deleteTable(test_table);
            // Output record count
            System.out.println("          Records count: " + cubridDB.countRecordsTable(test_table));
            // Drop test table
            cubridDB.dropTable(test_table);
            // Close connection
            cubridDB.close();
            System.out.println("Disconnected from demodb.");
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Request for Metadata

The next sample file called CubridMetadata.java presents how you can obtain some metadata information about the database server or some information regarding the JDBC connector that is used along with metadata generated for a query:

import java.sql.*;
import java.lang.*;

import cubrid.jdbc.driver.*;

public class CubridMetadata {
	public static void main(String[] args) {
		CUBRIDConnection connection = null;

		try {
			Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
			// Connect to the "demodb" database
			connection = (CUBRIDConnection) DriverManager.getConnection("jdbc:cubrid:localhost:30000:demodb:dba::");
			System.out.println("Connected to demodb.");

			// Get metadata
			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("CUBRID JDBC Driver Version is .....: " + meta.getDriverVersion());
			System.out.println("CUBRID JDBC Driver Major Version is: " + meta.getDriverMajorVersion());
			System.out.println("CUBRID JDBC Driver Minor Version is: " + meta.getDriverMinorVersion());
			System.out.println("JDBC Driver Major Version is: " + meta.getJDBCMajorVersion());
			System.out.println("JDBC Driver Minor Version is: " + meta.getJDBCMinorVersion());
			System.out.println();
			System.out.println("Features:");
			System.out.println(String.format(" Stores mixed case identifiers      : %s", meta.storesMixedCaseIdentifiers() ? "[ X ]" : "[   ]"));
			System.out.println(String.format(" Supports the ANSI92 entry level SQL grammar    : %s", meta.supportsANSI92EntryLevelSQL() ? "[ X ]" : "[   ]"));
			System.out.println(String.format(" Supports the ANSI92 full level SQL grammar  : %s", meta.supportsANSI92FullSQL() ? "[ X ]" : "[   ]"));
			System.out.println(String.format(" Supports transactions        : %s", meta.supportsTransactions() ? "[ X ]" : "[   ]"));
			System.out.println(String.format(" SQL keywords           : %s", meta.getSQLKeywords()));
			System.out.println();

			// Retrieve metadata from query
			CubridDB cubridDB = new CubridDB();
			cubridDB.init();
			String table = "jdbctutorial";
	   
			if (cubridDB.tableExists(table)) {
				cubridDB.dropTable(table);
			}
			
			cubridDB.createTestTable(table);
			cubridDB.insertTestData(table);

			CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();
			CUBRIDResultSet resultSet = (CUBRIDResultSet) stmt.executeQuery("SELECT * FROM " + table);

			System.out.println("Getting metadata from: SELECT * FROM " + table);
			CUBRIDResultSetMetaData rsmeta = (CUBRIDResultSetMetaData)resultSet.getMetaData();
			System.out.println("Columns selected:" + rsmeta.getColumnCount());
		   
			for( int i = 0; i < rsmeta.getColumnCount(); i++ ) {
				int idx = i + 1;
				String col_name = rsmeta.getColumnName(idx);
				String type = rsmeta.getColumnTypeName(idx);
				String nullable = ( rsmeta.isNullable(idx) == ResultSetMetaData.columnNoNulls ) ? "NOT NULL" : "NULL";
				System.out.println(String.format(" - Column #%d name: %s, type: %s %s", idx, col_name, type, nullable ));
			}

			// Close connection
			connection.close();
			System.out.println("Disconnected from demodb.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Sets, Multisets, Lists

Now that you know some fundamental and basic stuff about how to use CUBRID from a Java application, we can move to some CUBRID specific aspects.

The next sample file called CubridSet.java is ment to show working with CUBRID specific data types (set, multiset, list):

import java.lang.*;
import cubrid.sql.*;
import cubrid.jdbc.driver.*;
public class CubridSet {
    public static void main(String args[]) {
        CubridDB cubridDB = new CubridDB();

        try {
            cubridDB.init();
            cubridDB.getConnection().setAutoCommit(false);
            // Drop test table if exists
            if (cubridDB.tableExists("collection_test")) {
                cubridDB.dropTable("collection_test");
            }
            /**
             * create class collection_test(
             * settest set(integer),
             * multisettest multiset(integer),
             * listtest list(integer)
             * );
             */
            // Create test table
            CUBRIDStatement stmt_table = (CUBRIDStatement) cubridDB.getConnection().createStatement();
            stmt_table.executeUpdate(
                    "CREATE TABLE collection_test( " +
                           "settest set(integer)," +
                           "multisettest multiset(integer)," +
                           "listtest list(integer)" +
                           ")");
            cubridDB.getConnection().commit();
            stmt_table.close();
            System.out.println("Table: collection_test created.");
            
			// Insert test data
            CUBRIDStatement stmt = (CUBRIDStatement) cubridDB.getConnection().createStatement();
            stmt.executeUpdate("INSERT INTO collection_test VALUES({1,2,3},{1,2,3},{1,2,3})");
            stmt.executeUpdate("INSERT INTO collection_test VALUES({2,3,4},{2,3,4},{2,3,4})");
            stmt.executeUpdate("INSERT INTO collection_test VALUES({3,4,5},{3,4,5},{3,4,5})");
            cubridDB.getConnection().commit();
            stmt.close();
            System.out.println("Test data created.");

            // Query data
            String sql = "select collection_test from collection_test";
            stmt = (CUBRIDStatement) cubridDB.getConnection().createStatement();
            CUBRIDResultSet rs = (CUBRIDResultSet) stmt.executeQuery(sql);
            // Update data in the first record
            rs.next();
            CUBRIDOID oid = rs.getOID(1);
            oid.addToSet("settest", new Integer(10));
            oid.addToSet("multisettest", new Integer(20));
            oid.addToSequence("listtest", 3, new Integer(3));
            oid.addToSequence("listtest", 1, new Integer(1));
            oid.putIntoSequence("listtest", 2, new Integer(2));
            oid.removeFromSet("settest", new Integer(1));
            oid.removeFromSet("multisettest", new Integer(2));
            oid.removeFromSequence("listtest", 3);
            oid.removeFromSequence("listtest", 1);
            // Save & Commit
            cubridDB.getConnection().commit();
            rs.close();
            stmt.close();

            // Query and Output data
            sql = "select settest, multisettest, listtest from collection_test";
            stmt = (CUBRIDStatement) cubridDB.getConnection().createStatement();
            rs = (CUBRIDResultSet) stmt.executeQuery(sql);
            CUBRIDResultSetMetaData rsmd = (CUBRIDResultSetMetaData) rs.getMetaData();
            int numbOfColumn = rsmd.getColumnCount();
            
			while (rs.next()) {
                for (int j = 1; j <= numbOfColumn; j++) {
                    Object[] reset = (Object[]) rs.getCollection(j); //a
                    for (int m = 0; m < reset.length; m++)
                        System.out.print(reset[m] + ",");
                    System.out.print(" | ");
                }
                System.out.print("\n");
            }
            rs.close();
            stmt.close();

            // Cleanup
            if (cubridDB.tableExists("collection_test")) {
                cubridDB.dropTable("collection_test");
            }
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
        } finally {
            cubridDB.close();
            System.out.println("Disconnected from demodb.");
        }
    }
}

GLO Objects

Another CUBRID specific data type is GLO type. It is the equivalent of the BLOB data type from other database engines. It helps you store files or other information as a byte array. You can see in the sample below, CubridGLO.java, how to use GLO types from Java:

import cubrid.jdbc.driver.CUBRIDPreparedStatement;
import cubrid.jdbc.driver.CUBRIDResultSet;
import cubrid.jdbc.driver.CUBRIDStatement;
import cubrid.sql.CUBRIDOID;
import java.io.FileInputStream;
import java.io.FileOutputStream;
public class CubridGLO {
    public static void main(String[] args) {
        CubridDB cubridDB = new CubridDB();
        FileInputStream inputFile;
        FileOutputStream outputFile;
        CUBRIDPreparedStatement stmt;
        String sql = "insert into person(photo) values(?)";

        try {
            cubridDB.init();
            cubridDB.getConnection().setAutoCommit(false);
            // Create test table
            if (cubridDB.tableExists("person")) {
                cubridDB.dropTable("person");
            }
            // Create test table
            CUBRIDStatement stmt_table = (CUBRIDStatement) cubridDB.getConnection().createStatement();
            stmt_table.executeUpdate(
                    "CREATE TABLE person( " +
                           "photo glo" +
                           ")");
            cubridDB.getConnection().commit();
            stmt_table.close();
            System.out.println("Table 'person' created.");
            // Prepare data
            inputFile = new FileInputStream("testin.bin");
            CUBRIDOID glo_oid = cubridDB.getConnection().getNewGLO("glo", inputFile);
            inputFile.close();
            // Insert record
            stmt = (CUBRIDPreparedStatement) cubridDB.getConnection().prepareStatement(sql);
            stmt.setOID(1, glo_oid);
            stmt.executeUpdate();
            // Close and Commit
            stmt.close();
            cubridDB.getConnection().commit();

            // Output record count
            System.out.println("Records count: " + cubridDB.countRecordsTable("person"));
            // Get record
            sql = "select photo from person";
            CUBRIDStatement stmt2 = (CUBRIDStatement) cubridDB.getConnection().createStatement();
            CUBRIDResultSet rs2 = (CUBRIDResultSet)stmt2.executeQuery(sql);
            rs2.next();
            glo_oid = (CUBRIDOID) rs2.getObject("photo");
            rs2.close();
            stmt2.close();
            // Write data to file
            outputFile = new FileOutputStream("testout.bin");
            glo_oid.loadGLO(outputFile);
            outputFile.close();
            // Cleanup
            if (cubridDB.tableExists("person")) {
                cubridDB.dropTable("person");
            }
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
        } finally {
            cubridDB.close();
            System.out.println("Disconnected from demodb.");
        }
    }
}

Complete Code Example

The next file called CubridDB.java is an example of how a common library used in other classes would look like:

import java.lang.*;
import java.sql.*;
import cubrid.jdbc.driver.*;
public class CubridDB {
    private CUBRIDConnection connection = null;

    public CUBRIDConnection getConnection() {
        return connection;
    }

    public void init() throws SQLException, ClassNotFoundException {
        // Load the CUBRID jdbc driver
        Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
        // Connect to the "demodb" database
        connection = (CUBRIDConnection) DriverManager.getConnection("jdbc:cubrid:localhost:30000:demodb:dba::");
        System.out.println("Connected to demodb.");
    }

    public void createTestTable(String tableName) throws SQLException {
        CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();

        stmt.executeUpdate(
                "CREATE TABLE" + tableName + "( " +
                        "`id` integer AUTO_INCREMENT PRIMARY KEY, " +
                        "`desc` character varying(64), " +
                        "`val` bigint AUTO_INCREMENT(999,1), " +
                        "`longdesc` string, " +
                        "`money` monetary default 19.99, " +
                        "`bin_data` bit varying(65534), " +
                        "`insert_time` timestamp DEFAULT systimestamp " +
                        ")");
        connection.commit();
        stmt.close();

        System.out.println("Table: " + tableName + " created.");
    }

    public void insertTestData(String tableName) throws SQLException {
        CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();

        stmt.executeUpdate("INSERT INTO " + tableName + "(`desc`, `longdesc`) VALUES('first','first record in the table')");
        stmt.executeUpdate("INSERT INTO " + tableName + "(`desc`, `longdesc`) VALUES('second','second record in the table')");
        stmt.executeUpdate("INSERT INTO " + tableName + "(`desc`, `longdesc`) VALUES('third','third record in the table')");
        connection.commit();
        stmt.close();

        System.out.println("Test data inserted.");
    }

    public void insertTestDataGetID(String tableName) throws SQLException {
        String sql = "INSERT INTO " + tableName + "(`desc`, `longdesc`) VALUES('last','last record in the table')";
        CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();

        stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);

        System.out.println("Retrieving generated keys...");
        CUBRIDResultSet rs = (CUBRIDResultSet) stmt.getGeneratedKeys();
        while (rs.next()) {
            System.out.println("Automatically generated key values: id=" + rs.getBigDecimal("id") + ", val= " + rs.getBigDecimal("val"));
        }
        connection.commit();
        rs.close();
        stmt.close();

        System.out.println("Test data inserted.");
    }

    public void deleteTable(String tableName) throws SQLException {
        CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();

        stmt.executeUpdate("DELETE FROM " + tableName);
        connection.commit();
        stmt.close();

        System.out.println("All data in the table: " + tableName + " was deleted.");
    }

    public void dropTable(String tableName) throws SQLException {
        CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();

        stmt.executeUpdate("DROP TABLE " + tableName);
        connection.commit();
        stmt.close();

        System.out.println("Table: " + tableName + " dropped.");
    }

    public long countRecordsTable(String tableName) throws SQLException {
        long resultCount = -1;
        CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();
        CUBRIDResultSet resultSet = (CUBRIDResultSet) stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);

        resultSet.next();
        resultCount = resultSet.getLong(1);

        resultSet.close();
        stmt.close();

        return resultCount;
    }

    public void outputMetaData(String tableName) throws SQLException {
        CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();
        CUBRIDResultSet resultSet = (CUBRIDResultSet) stmt.executeQuery("select * from " + tableName);

        System.out.println("The columns in the table: " + resultSet.getMetaData().getTableName(1) + " are: ");
        for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
            System.out.println("Column #" + i + " name: " + resultSet.getMetaData().getColumnName(i));
            System.out.println("          type: " + resultSet.getMetaData().getColumnTypeName(i));
            System.out.println("          length: " + resultSet.getMetaData().getPrecision(i));
        }
        resultSet.close();
        stmt.close();
    }

    public void outputResultSet(String tableName) throws SQLException {
        CUBRIDStatement stmt = (CUBRIDStatement) connection.createStatement();
        CUBRIDResultSet resultSet = (CUBRIDResultSet) stmt.executeQuery("select * from " + tableName);

        System.out.println("Table: " + resultSet.getMetaData().getTableName(1) + " contains: ");
        int i = 1;
        while (resultSet.next()) {
            System.out.println("Record #: " + i++);
            for (int j = 1; j <= resultSet.getMetaData().getColumnCount(); j++) {
                System.out.println("          " + resultSet.getMetaData().getColumnName(j) + ":" + resultSet.getString(j));
            }
        }
        resultSet.close();
        stmt.close();
    }

    public boolean tableExists(String tableName) throws SQLException {
        boolean found = false;
        DatabaseMetaData metaData = connection.getMetaData();
        String[] tablesList = tableName.split(" ");

        ResultSet rs = metaData.getTables(null, null, tableName, new String[]{"TABLE"});
        if (rs.next()) {
            found = true;
        }
        rs.close();

        return found;
    }

    public void close() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

See also

Tutorial :: Clj-DBCP Clojure Library for Connecting to CUBRID Database

Clj-DBCP is a Java-6/Clojure library to create a database connection pool, which is known to be a more efficient way to connect to multiple databases ...

Tutorial :: Store Java Logs to Database Using log4j

log4j is one of the most powerful and commonly used logging system used when programming in Java. There are many different ways how you can utilize i...

Tutorials :: Analyzing JDBC Logs with LOG4JDBC

First, let's clarify what exactly this log4jdbc is useful for. Assume you work on a project written in Java which communicates with CUBRID JDBC (or an...




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: