JDBC Connector

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