Sample Codes

Connection String

---

Because the JackHare incorporates with two datastores, one is target noSQL DB, another is metadata store, so the connection string is a little bit of complicated. let's take look at it !!

jdbc:jackhare:[noSqlconnectionString]:metastore:[metaDataStoreConnectionString]

The variables described as follows...

noSqlconnectionString: the connection string link to the target noSQL DB

metaDataStoreConnectionString: the connection string link to the metadata store

For example, if the target noSQL DB is HBase, and the metadata store is Derby by default, the connection string would be...

jdbc:jackhare:hbase://192.168.1.50?SCHEMA=TEST_SCHEMA&SCHEMA_CREATE=true:metastore:jdbc:derby://localhost/derbyTestDb

(The properties SCHEMA and SCHEMA_CREATE of metadata store are both optional)

NOTICE

The connection strings for both target noSQL DB and metadata store, follow the specifications defined by it's corresponding data store. JackHare just simply redirects the connection string to the corresponding data store server, so you can put any extra-parameters needed for your data store for any purpose :)

Sample Codes

---

JackHare is JDBC API compliant, which means that your client code only depends on the java.sql.* and javax.sql.*, so the following operations are the identical JDBC API operations you are already familiar on other RDB products.

You can download sample code here.

CREATE CONNECTION

You can use two java.sql.DriverManager.getConnection methods to create java.sql.Connection.

By default, you can call getConnection method with three arguments, connectionString, userName and password as follows...

Connection conn = null;
conn = DriverManager.getConnection(CONNECTION_STRING, USER_NAME, PASSWORD);

If you want to set more parameters to the target noSQL DB, you can use another method as follows...(Using Derby for example)

Connection conn = null;
Properties props = new Properties();
props.setProperty("user", USER_NAME);
props.setProperty("password", PASSWORD);
props.setProperty("hbase.client.scanner.caching", "20000");
props.setProperty("jackhare.schema", SCHEMA_NAME);
props.setProperty("jackhare.metadata.repository.jdbc.derby", "net.jackhare.sql.metadata.derby.DerbyMetaDataRepositoryImpl");
props.setProperty("jackhare.metadata.driver.jdbc.derby", "org.apache.derby.jdbc.ClientDriver");
props.setProperty("jackhare.schema.create", "true");
conn = DriverManager.getConnection(CONNECTION_STRING, props);

CREATE TABLE

private static final String SQL_CREATE_TABLE =
    "CREATE TABLE EMPLOYEE ( " +
    "   ID INTEGER PRIMARY KEY NOT NULL, " +
    "   FNAME VARCHAR(50) NOT NULL, " +
    "   LNAME VARCHAR(50) NOT NULL " +
    ") ";
//...
PreparedStatement statement = null;
statement = conn.prepareStatement(SQL_CREATE_TABLE);
statement.executeUpdate();

INSERT TABLE

private static final String SQL_INSERT_TABLE =
    "INSERT INTO EMPLOYEE (ID, FNAME, LNAME) " +
    "VALUES(?, ?, ?) ";
//...
PreparedStatement statement = null;
statement = conn.prepareStatement(SQL_INSERT_TABLE);
statement.setLong(1, 1);
statement.setString(2, "Scott");
statement.setString(3, "Miao");
statement.executeUpdate();

SELECT TABLE

private static final String SQL_SELECT_TABLE_ALL =
    "SELECT ID, FNAME, LNAME FROM EMPLOYEE ";
//...
PreparedStatement statement = null;
ResultSet rs = null;
//get all records
statement = conn.prepareStatement(SQL_SELECT_TABLE_ALL);
rs = statement.executeQuery();
while(rs.next()) {
    System.out.println("ROW#" + rs.getLong(1) + ", " + rs.getString(2) + ", " + 
    rs.getString(3));
}

UPDATE TABLE

private static final String SQL_UPDATE_TABLE_BY_ID =
    "UPDATE EMPLOYEE " +
    "SET FNAME = ?, LNAME = ? " +
    "WHERE ID = ? ";
//...
PreparedStatement statement = null;
ResultSet rs = null;
final String CHANGED_FNAME = "AAAA";
final String CHANGED_LNAME = "BBBB";
final long ID = 1;

//execute update
statement = conn.prepareStatement(SQL_UPDATE_TABLE_BY_ID);
statement.setString(1, CHANGED_FNAME);
statement.setString(2, CHANGED_LNAME);
statement.setLong(3, 1);
statement.executeUpdate();

DELETE TABLE

private static final String SQL_DELETE_TABLE_BY_ID =
    "DELETE FROM EMPLOYEE WHERE ID = ? ";
//...
PreparedStatement statement = null;
ResultSet rs = null;
final long ID = 1;
//execute delete
statement = conn.prepareStatement(SQL_DELETE_TABLE_BY_ID);
statement.setLong(1, ID);
statement.executeUpdate();

ALTER TABLE

private static final String SQL_ALTER_TABLE =
    "ALTER TABLE EMPLOYEE " +
    "ADD COLUMN ADDRESS VARCHAR(50) ";
//...
PreparedStatement statement = null;
statement = conn.prepareStatement(SQL_ALTER_TABLE);
statement.executeUpdate();

DROP TABLE

private static final String SQL_DROP_TABLE =
    "DROP TABLE EMPLOYEE ";
//...
PreparedStatement statement = null;
statement = conn.prepareStatement(SQL_DROP_TABLE);
statement.executeUpdate();