JDBC Connector

Starting from version 3.00.2.0, DolphinDB JDBC Connector supports reconnect property for auto reconnection. Note that when enableHighAvailability = true, reconnect is always enabled.

Starting from version 1.30.21.1, DolphinDB JDBC Connector supports enableHighAvailability property for connection strings, and the original highAvailability can be used as an alias. Configuration conflicts are reported if inconsistencies occur.

As of version 1.30.22.2, load balancing is automatically enabled for HA mode. Since 2.00.11.1, a new property enableLoadBalance is introduced to enable/disable load balancing in HA mode. Load balancing is only supported in HA mode.

If load balancing is disabled:

• For versions before 1.30.22.2, JDBC establishes connection to a random node.
• For versions since 2.00.11.1, JDBC establishes connection to the node formed by the HOST and PORT in the connection URL or the nodes formed by hostName, localhost, and port in the Properties.

If load balancing is enabled, the API establishes connection to a low-load node. A low-load node is selected based on: memory usage<80%, connections<90%, and node load<80%.

The following example disables load balancing in HA mode:

public void test_enableHighAvailability_and_enableLoadBalance() throws SQLException {
	String SITES = "192.168.0.69:18921 192.168.1.167:18922 192.168.0.69:18923 192.168.0.69:18924";
	String url = "jdbc:dolphindb://" + HOST + ":" + PORT + "?user=admin&password=123456&enableHighAvailability=true&highAvailabilitySites=" + SITES + "&enableLoadBalance=false";
	Connection connection = DriverManager.getConnection(url);
}

Since 1.30.22.2, JDBC connector supports tableAlias property to specify table alias(es).

For DFS Tables:

1. Use Default Alias:

   tableAlias=dfs://valuedb/pt

2. Use Custom Alias:

   tableAlias=ttt:dfs://valuedb/pt

3. Use Multiple Aliases (separated by comma):

   tableAlias=t1:dfs://valuedb/pt,dfs://valuedb/dt,dfs://valuedb/Order_tmp,dfs://testValue/nt

For MVCC Tables:

1. Use Default Alias:

   tableAlias=mvcc://work_dir/mvcc13

2. Use Custom Alias:

   tableAlias=mvcc3:mvcc://work_dir/mvcc13

For Shared In-Memory Tables:

tableAlias=tb8:memTb2

Syntax

getCatalogs()

Parameters

None

Details

Get all catalogs in the database.

Examples

Connection conn = DriverManager.getConnection(url);
DatabaseMetaData metaData = conn.getMetaData();
ResultSet rs = metaData.getCatalogs();

Syntax

getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)

Parameters

• catalog (optional): a String specifying the catalog.
• schemaPattern (optional): a String specifying the matching pattern of schema. Specifying "%" means to match all patterns.
• tableNamePattern: a String indicating the pattern of table name. Specifying "%" means to match all patterns.
• types (optional): a String array indicating the table type. Currently only "null" or "TABLE" is supported, and only "TABLE" type will be returned.

Details

Get tables in the specified catalog/schema.

Currently, the following usages are supported:

1. Set a specific catalog, and set schemaPattern and tableNamePattern to "%": Get all tables in the catalog.
2. Set a specific catalog and schemaPattern, and set tableNamePattern to "%": Get all tables in the schema.
3. Set catalog and schemaPattern to "null" and tableNamePattern to "%": Get all in-memory tables.

Return Value

A table with the following columns:

• TABLE_CAT: The catalog to which the table belongs. It is empty for an in-memory table.
• TABLE_NAME: The table name.
• TABLE_SCHEMA: The schema to which the table belongs.
• TABLE_TYPE: The table type.
• REMARKS: The remarks for the table.

Examples

1. Get all tables in catalog1:

   ResultSet rs = metaData.getTables("catalog1","%","%", null);

2. Get all tables in catalog1.schema_test:

   ResultSet rs = metaData.getTables("catalog1","schema_test","%", null);

3. Get all in-memory tables:

   ResultSet rs = metaData.getTables(null,null,"%", null);

Syntax

getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)

Parameters

• catalog (optional): a String specifying the catalog.
• schemaPattern (optional): a String specifying the pattern of schema.
• tableNamePattern: a String indicating the pattern of table name.
• columnNamePattern (optional): a String specifying the pattern of column name. Specifying "%" means to match all patterns.

Details

Get columns in the specified catalog, schema, and/or table.

Currently, the following usages are supported:

1. Set a specific catalog, schemaPattern, and tableNamePattern, and set columnNamePattern to "%": Get all columns of the table.
2. Set a specific tableNamePattern, and set columnNamePattern to "%": Get all columns of the table.

Return Value

A table with the following columns:

• COLUMN_NAME: The column name.
• TYPE_NAME: The column type.
• DATA_TYPE: java.sql.Types.
• EXTRA: Not yet supported. Returns null.
• REMARKS: The column comment. Returns null.
• ORDINAL_POSITION: The original position of the column, which starts from 1.
• IS_NULLABLE: Whether a column can contain null using ISO rules. It only returns 'NO' for the partitioning column.
• SQL_DATA_TYPES: An INT indicating the type value of java.sql.Types. For exmaple, STRING in DolphinDB is 12 (VARCHAR), DATE is 91, DECIMAL32/DECIMAL64/DECIMAL128 is 3.

Examples:

1. Get columns from DFS table "catalog1.schema_test.dt":

   ResultSet rs = metaData.getColumns("catalog1","schema_test","dt", "%");

2. Get columns from in-memory table "table1":

   ResultSet rs = metaData.getColumns(null, null, "table1", "%");

Syntax 1

getSchemas()

Details

Get schemas in the current catalog.

Syntax 2

getSchemas(String catalog, String schemaPattern)

Parameters

• catalog: a String specifying the catalog.
• schemaPattern: a String specifying the pattern of schema. Currently only "%" is supported, meaning to match all patterns.

Details

Get all schemas in the specified catalog.

Return Value

TABLE_SCHEMA: The schema.

TABLE_CATALOG: The catalog.

Examples

1. Get schemas in the current catalog:

   ResultSet rs = metaData.getSchemas();

2. Get schemas in the "catalog1":

   ResultSet rs = metaData.getSchemas("catalog1", "%");

Syntax

getCatalog() 

Details

Get the catalog set in current connection.

Example

Connection conn = DriverManager.getConnection(url);
String catalog = conn.getCatalog();

setCatalog(String catalog) 

Parameters

catalog: a String indicating the catalog.

Examples

Set the catalog for the current connection as "catalog1"

Connection conn = DriverManager.getConnection(url);
String catalog = conn.setCatalog("catalog1");

Use the following code to create a template table and save it to disk through DolphinDB Java API. You can use loadTable later to create an in-memory table quickly. Note that the variable names cannot be the same as DolphinDB keywords.

public static boolean CreateTable(String database, String tableName, String host, int port) {
    boolean success = false;
    DBConnection db = null;
    try {
        String sb = "bool = [1b, 0b];\n" +
            "char = [97c, 'A'];\n" +
            "short = [122h, 123h];\n" +
            "int = [21, 22];\n" +
            "long = [22l, 23l];\n" +
            "float  = [2.1f, 2.2f];\n" +
            "double = [2.1, 2.2];\n" +
            "string= [`Hello, `world];\n" +
            "date = [2013.06.13, 2013.06.14];\n" +
            "month = [2016.06M, 2016.07M];\n" +
            "time = [13:30:10.008, 13:30:10.009];\n" +
            "minute = [13:30m, 13:31m];\n" +
            "second = [13:30:10, 13:30:11];\n" +
            "datetime = [2012.06.13 13:30:10, 2012.06.13 13:30:10];\n" +
            "timestamp = [2012.06.13 13:30:10.008, 2012.06.13 13:30:10.009];\n" +
            "nanotime = [13:30:10.008007006, 13:30:10.008007007];\n" +
            "nanotimestamp = [2012.06.13 13:30:10.008007006, 2012.06.13 13:30:10.008007007];\n" +
            "tb1= table(bool,char,short,int,long,float,double,string,date,month,time,minute,second,datetime,timestamp,nanotime,nanotimestamp);\n" +
            "db=database(\"" + database + "\");\n" +
            "saveTable(db, tb1, `" + tableName + ");\n";
        db = new DBConnection();
        db.connect(host, port);
        db.run(sb);
        success = true;
    } catch (Exception e) {
        e.printStackTrace();
        success = false;
    } finally {
        if (db != null)
            db.close();
        return success;
    }
}

The append operation on in-memory tables through the JDBC interface is to first preset the SQL template through JDBCPrepareStatement, then write the parameters through the set method, and finally specify the parameters and execute the statement through the executeUpdate function.

public static void InMemmoryAddTest(String database, String tableName) {
    try {
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(DB_URL_WITHLOGIN);

        JDBCStatement stm = (JDBCStatement) conn.createStatement();
        stm.execute("memTable = loadTable('" + database + "',\"" + tableName + "\")");
        // SQL insert statement
        stmt = conn.prepareStatement("insert into memTable values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        stmt.setBoolean(1, true);
        stmt.setByte(2, (byte) 98);
        stmt.setShort(3, (short) 112);
        stmt.setInt(4, 21);
        stmt.setLong(5, 22l);
        stmt.setFloat(6, 2.1f);
        stmt.setDouble(7, 2.1);
        stmt.setString(8, "hello");
        stmt.setDate(9, Date.valueOf(LocalDate.of(2013, 06, 13)));
        stmt.setObject(10, new BasicMonth(YearMonth.of(2016, 06)));
        stmt.setObject(11, Time.valueOf("13:30:10"));
        stmt.setObject(12, LocalTime.of(13, 30));
        stmt.setObject(13, LocalTime.of(13, 30, 10));
        stmt.setObject(14, LocalDateTime.of(2012, 06, 13, 13, 30, 10));
        stmt.setObject(15, LocalDateTime.of(2012, 06, 13, 13, 30, 10, 8000000));
        stmt.setObject(16, LocalTime.of(13, 30, 10, 8007006));
        stmt.setObject(17, LocalDateTime.of(2012, 06, 13, 13, 30, 10, 8007006));
        stmt.executeUpdate();

        // load table
        ResultSet rs = stmt.executeQuery("select * from memTable");
        printData(rs);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException se2) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
}

To delete data from an in-memory table, fill in the corresponding conditions at "?".

public static void InMemoryDeleteTest(String database, String tableName){
    try {
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(DB_URL_WITHLOGIN);
        JDBCStatement stm = (JDBCStatement)conn.createStatement();
        stm.execute("memTable = loadTable('" + database + "',\"" + tableName + "\")");
        //SQL delete statement
        stmt = conn.prepareStatement("delete from memTable where char = ?");
        stmt.setByte(1, (byte)'A');
        stmt.executeUpdate();
        // Check if the records have been deleted
        ResultSet rs = stmt.executeQuery("select * from memTable");
        System.out.println("==========InMemoryDeleteTest======================");
        printData(rs);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException se2) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }    
}

public static void InMemoryUpdateTest(String database, String tableName){
    try {
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(DB_URL_WITHLOGIN);
        JDBCStatement stm = (JDBCStatement)conn.createStatement();
        stm.execute("memTable = loadTable('" + database + "',\"" + tableName + "\")");
        // SQL update statement
        stmt = conn.prepareStatement("update memTable set bool = 0b where char = 97c");
        stmt.executeUpdate();
        // check if records have been updated
        ResultSet rs = stmt.executeQuery("select * from memTable where char=97c");
        printData(rs);
    } catch (Exception e) {
        e.printStackTrace();
    } finally
    {
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException se2) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
}

Use the following code to create a DFS database with VALUE-based partitions through DolphinDB Java API.

public static boolean CreateValueTable(String database, String tableName, String host, String port)
{
    boolean success=false;
    DBConnection db = null;
    StringBuilder sb = new StringBuilder();
    sb.append("login(\"admin\",\"123456\")\n");
    sb.append("n=3000\n");
    sb.append("month=take(2000.01M..2019.05M, n)\n");
    sb.append("x=take(1..1000, n)\n");
    sb.append("t=table(month, x)\n");
    sb.append("if(existsDatabase(\""+database+"\"))\n" +
              "			dropDatabase(\""+database+"\")\n");
    sb.append("db=database(\""+database+"\", VALUE, 2000.01M..2019.05M)\n");
    sb.append("pt = db.createPartitionedTable(t, `"+tableName+", `month)\n");
    sb.append("pt.append!(t)\n");
    db = new DBConnection();
    try {
        db.connect(host, Integer.parseInt(port));
        db.run(sb.toString());
        success=true;
    } catch (NumberFormatException | IOException e) {
        e.printStackTrace();
        success=false;
    }finally {
        if (db != null)
            db.close();
        return success;
    }
}

public static void DFSAddTest(Properties info, String database, String tableName)
{
    try {
        Class.forName(JDBC_DRIVER);
        
        // load the partitioned table
        conn = DriverManager.getConnection(url2,info);
        JDBCStatement stm = (JDBCStatement)conn.createStatement();
        stm.execute("dfsTable = loadTable('" + database + "',\"" + tableName + "\")");
        // SQL insert statement
        stmt = conn.prepareStatement("insert into dfsTable values(?,?)");
        stmt.setObject(1, new BasicMonth(YearMonth.of(2016,06)));
        stmt.setInt(2,3);
        stmt.executeUpdate();
        // query the table ot see if the records have been inserted.
        ResultSet rs = stmt.executeQuery("select count(*) from loadTable(\""+database+"\", `"+tableName+")");
        printData(rs);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException se2) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {6
            se.printStackTrace();
        }
    }
}	

Starting from version 3.00.2.0, JDBC supports setting SQL query timeout, i.e., the maximum amount of time a database will wait for a query result to return. If the query result is not returned within this interval, JDBC will throw an SQLTimeoutException. Setting a query timeout helps avoid long, unresponsive waits, thereby preventing thread blocking and resource waste.

Query timeout can be set for a JDBCStatement or JDBCPreparedStatement object. Use the setQueryTimeout(int seconds) method to set the query timeout (a positive integer, in seconds). For example:

Statement stmt = conn.createStatement();
stmt.setQueryTimeout(5);
ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");

Note: JDBC will apply the runtime limits set in JDBCStatement or JDBCPreparedStatement to their corresponding execute, executeQuery, executeUpdate, and executeBatch methods. This means that once a JDBCStatement or JDBCPreparedStatement object has its query timeout set via the setQueryTimeout method, all calls to execute, executeQuery, executeUpdate, and executeBatch on that object will use the same timeout setting.