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();
}
}
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());
}
}
}
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();
}
}
}
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.");
}
}
}
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());
}
}
}
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());
}
}
}
Installing CUBRID and CUBRID Admin on Windows
