JDBC

MapD Core Database supports JDBC connections.

Resources

Resource Description
JAR file $MAPD_PATH/bin/mapdjdbc-1.0-SNAPSHOT-jar-with-dependencies.jar
Code samples $MAPD_PATH/samples
JDBC driver com.mapd.jdbc.MapDDriver
URL jdbc:mapd:<host>:<port>:<dbName>

You can connect to a JDBC session using the HTTP protocol by appending http to a URL. For example, jdbc:mapd:localhost:9092:mapd:http.

Supported JDBC Methods

Method Description
Connection class
clearWarnings Clear all warnings reported for this connection object. After calling clearWarnings, calling getWarnings returns null until a new warning is reported for this connection object.
close Disconnect the JDBC client session and frees associated resources.
createStatement Get a new, empty Statement object.
getCatalog Get the Connection object current catalog name.
getMetaData Get a DatabaseMetaData object, which contains metadata about the database for which this Connection object represents a connection. The metadata includes information about the database tables, supported SQL grammar, stored procedures, and the capabilities of this connection.
getWarnings Get the first warning reported by calls on this Connection object.
isClosed

Get the closed status of this Connection object:

  • true = connection closed
  • false = connection valid
isValid

Get the connection status:

  • true = valid connection
  • false = connection closed
prepareStatement Create a PreparedStatement object. MapD Core makes no distinction between prepared and directly executed statements and queries.
Driver class
acceptsURL Get the driver determination of whether or not it can open a connection to the URL.
connect Make a database connection to the specified URL.
getMajorVersion Get the driver major version number.
getMinorVersion Get the driver minor version number.
getParentLogger Get the parent Logger of all Loggers used by this driver.
getPropertyInfo Get driver property information.
jdbcCompliant Test if this driver is a JDBC-compliant driver.
Statement
executeQuery Execute a SELECT query.
executeUpdate Execute an INSERT or DROP statement.
getMaxFieldSize Get the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object.
getMaxRows Get the maximum number of rows that a ResultSet object produced by this Statement object can contain.
getMetaData Get a new DatabaseMetaData object.
setMaxFieldSize Set the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object.
setMaxRows Set the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number. Default is 100,000 rows.
PreparedStatement
addBatch Add an INSERT statement to a batch.
execute Execute a prepared query. MapD Core makes no distinction between prepared and direct query execution.
executeBatch Execute a batch of queries.
executeUpdate Create a prepared statement object for batch updates.
set[obj] Set a dynamic parameter for batch statements. Dynamic parameters are supported with batch inserts only.
setMaxRows Set the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number. Default is 100,000 rows.

Unsupported Features

  • Transaction statements
  • Cursors
  • Table updates, alterations or deletions
  • Multiple result sets
  • Domains
  • Rules
  • Database procedures
  • Indexes
  • Query cancellation
  • Keys
  • Constraints
  • Permissions
  • Schemas (table ownership) - any client can see tables and views created by any user without restriction

Example

This example uses the JDBC interface to create a flight information database, then reads carrier information from the database.

Sequence

The key steps are:

  1. Set connection parameters and credentials:
static final String JDBC_DRIVER = "com.mapd.jdbc.MapDDriver";
static final String DB_URL = "jdbc:mapd:localhost:9091:mapd";
static final String USER = "myUserName";
static final String PASS = "myPassWord";
  1. Register the JDBC driver:
Class.forName(JDBC_DRIVER);
  1. Open a MapD server connection:
conn = DriverManager.getConnection(DB_URL, USER, PASS);
  1. Create a table, add data, and query the database:
stmt = conn.createStatement();

String sql = "CREATE table flights(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
stmt.executeUpdate(sql);

sql = "insert into flights values('2017-04-23 06:30:0’, '2017-04-23 07:45:00’, 'Southwest’)";
stmt.executeUpdate(sql);
sql = "insert into flights values('2017-04-23 06:50:0’, '2017-04-23 09:45:00’, 'American’)";
stmt.executeUpdate(sql);
sql = "insert into flights values('2017-04-23 09:30:0’,’ 2017-04-23 12:45:00’, 'United’)";
stmt.executeUpdate(sql);

sql = "SELECT uniquecarrier from flights";
ResultSet rs = stmt.executeQuery(sql);
  1. Extract data from the result set:
while (rs.next()) {
    String uniquecarrier = rs.getString("uniquecarrier");
    System.out.println("uniquecarrier: " + uniquecarrier);
}

Compile and Run

javac Flights.java
java -cp $MAPD_PATH/bin/mapdjdbc-1.0-SNAPSHOT-jar-with-dependencies.jar:./  Flights

Source Code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Flights {

    static final String JDBC_DRIVER = "com.mapd.jdbc.MapDDriver";
    static final String DB_URL = "jdbc:mapd:localhost:9091:mapd";
    static final String USER = "myUserName";
    static final String PASS = "myPassWord";

    public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
        Class.forName(JDBC_DRIVER);

        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        stmt = conn.createStatement();
        String sql = "CREATE table flights(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
        stmt.executeUpdate(sql);
        sql = "insert into flights values('2017-04-23 06:30:0’, '2017-04-23 07:45:00’, 'Southwest’)";
        stmt.executeUpdate(sql);
        sql = "insert into flights values('2017-04-23 06:50:0’, '2017-04-23 09:45:00’, 'American’)";
        stmt.executeUpdate(sql);
        sql = "insert into flights values('2017-04-23 09:30:0’,’ 2017-04-23 12:45:00’, 'United’)";
        stmt.executeUpdate(sql);

        sql = "SELECT uniquecarrier from flights";
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
          String uniquecarrier = rs.getString("uniquecarrier");
          System.out.println("uniquecarrier: " + uniquecarrier);
        }

        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException se) {
        //Handle errors for JDBC
        se.printStackTrace();
      } catch (Exception e) {
        e.printStackTrace();
        if (stmt != null)
            stmt.close();
        if (conn != null)
            conn.close();
       }
    }
}