Background Image

DOCUMENTATION

Votes 0
?

Shortcut

PrevPrev Article

NextNext Article

Larger Font Smaller Font Up Down Go comment Print

Java Programming with CUBRID

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 to 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 the CUBRID JDBC connector, your Java programs can access CUBRID databases.

Examples

Connect, Disconnect

We will start with the CubridStartup.java file, see how we can perform 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);
            System.out.println("Features:");
            // Insert test data
            cubridDB.insertTestData(test_table);
            System.out.println("Features:");
            // Insert test data and show last insert ids
            cubridDB.insertTestDataGetID(test_table);
            System.out.println("Features:");
            // 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 meant 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.");
        }
    }
}

 

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:172.31.201.15:33010: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());
        }
    }
}

  1. CUBRID HA (High Availability) Starting Guide

    Date2020.05.22 CategoryServer
    Read More
  2. Analyzing JDBC Logs with LOG4JDBC

    Date2020.05.21 CategoryInterfaces
    Read More
  3. How to Configure to Log4J to Log Messages to CUBRID Database

    Date2020.05.12 CategoryInterfaces
    Read More
  4. CUBRID ODBC Drvier Testing on Windows

    Date2020.05.12 CategoryInterfaces
    Read More
  5. CSQL - Command Line SQL interpreter

    Date2020.05.12 CategoryTools
    Read More
  6. CUBRID JDBC Tutorial

    Date2020.05.12 CategoryInterfaces
    Read More
  7. Installing CUBRID and CUBRID Admin on Windows

    Date2020.05.11 CategoryInstall
    Read More
  8. Our Experience of Creating Large Scale Log Search System Using ElasticSearch

    Date2020.05.11 CategoryEtc
    Read More
  9. A Node.js speed dilemma: AJAX or Socket.IO?

    Date2020.05.11 CategoryEtc
    Read More
  10. Become a Jave GC Expert Series 5 : The Principles of Java Application Performance Tuning

    Date2020.05.11 CategoryEtc
    Read More
Board Pagination Prev 1 2 Next
/ 2

Join the CUBRID Project on