数据库应用开发

MySQL/MariaDB实例应用开发：
创建数据库及数据表：
登录MySQL：/opt/sequoiasql/mysql/bin/mysql -h 127.0.0.1 -P 3306 -u root
创建数据库：create database company; use company;
在 MySQL 实例 company 数据库中创建分区表 employee:

CREATE TABLE employee 
(
empno INT,
ename VARCHAR(128),
age INT,
PRIMARY KEY (empno)
) ENGINE = sequoiadb COMMENT = "雇员表, sequoiadb: { table_options: { ShardingKey: { 'empno': 1 }, ShardingType: 'hash', 'Compressed': true, 'CompressionType': 'lzw', 'AutoSplit': true, 'EnsureShardingIndex': false } } ";

MySQL 实例建表参数配置

在分区表 employee 中插入数据：

INSERT INTO employee VALUES (10001, 'Georgi', 48);
INSERT INTO employee VALUES (10002, 'Bezalel', 21);
INSERT INTO employee VALUES (10003, 'Parto', 33);
INSERT INTO employee VALUES (10004, 'Chirstian', 40);
INSERT INTO employee VALUES (10005, 'Kyoichi', 23);
INSERT INTO employee VALUES (10006, 'Anneke', 19);

查询分区表 employee 中 age 大于 20，小于 30 的数据: SELECT * FROM employee WHERE age > 20 AND age < 30;
更新分区表 employee 中的数据，将 empno 为 10001 的记录 age 更改为 34: UPDATE employee SET age=34 WHERE empno = 10001;
删除分区表 employee 中的数据，将 empno 为 10005 的记录删除: DELETE FROM employee WHERE empno = 10005;

索引的使用：
在分区表 employee 的 ename 字段上创建索引：ALTER TABLE employee ADD INDEX idx_ename(ename);
显示分区表 employee 查询语句执行计划：EXPLAIN SELECT * FROM employee WHERE ename = 'Georgi';

Java语言操作MySQL实例中的数据：
进入源码放置目录：cd /home/sdbadmin/source/mysql
首先对连接的 java 文件进行编译: javac -d . MySQLConnection.java
增加 empno 为 20001 、 20002 和 20003 这三条记录，Insert.java 源代码如下:

package com.sequoiadb.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Insert {
    private static String url = "jdbc:mysql://127.0.0.1:3306/company?useUnicode=true&charac
terEncoding=utf-8&useSSL=false";
    private static String username = "root";
    private static String password = "";

    public static void main(String[] args) throws SQLException {
        insert();
    }

    public static void insert() throws SQLException {
        MySQLConnection mysqlConnection = new MySQLConnection(url, username, password);

        Connection connection = mysqlConnection.getConnection();
        String sql = "INSERT INTO employee VALUES";
        PreparedStatement psmt = connection.prepareStatement("");
        StringBuffer sb = new StringBuffer();
        sb.append("(").append(20001).append(",").append("'Quincy'").append(",").append(30).
append("),");
        sb.append("(").append(20002).append(",").append("'Newton'").append(",").append(31).
append("),");
        sb.append("(").append(20003).append(",").append("'Dan'").append(",").append(32).app
end("),");

        sb.deleteCharAt(sb.length() - 1);
        sql = sql + sb.toString();
        System.out.println(sql);
        psmt.addBatch(sql);
        psmt.executeBatch();
        connection.close();
    }
}

进行编译：javac -d . Insert.java
运行 Insert 类的代码可以将数据插入: java -cp .:../mysql-connector-java-5.1.48.jar com.sequoiadb.mysql.Insert

查询表中 empno 、 ename 和 age 这三个字段，Select.java 源代码如下:

package com.sequoiadb.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Select {
    private static String url = "jdbc:mysql://127.0.0.1:3306/company?useUnicode=true&charac
terEncoding=utf-8&useSSL=false";
    private static String username = "root";
    private static String password = "";

    public static void main(String[] args) throws SQLException {
        select();
    }

    public static void select() throws SQLException {
        MySQLConnection mysqlConnection = new MySQLConnection(url, username, password);

        Connection connection = mysqlConnection.getConnection();
        String sql = "select * from employee";
        PreparedStatement psmt = connection.prepareStatement(sql);
        ResultSet rs = psmt.executeQuery();
        System.out.println("-------------------------------------------");
        System.out.println("empno \t ename \t age");
        System.out.println("-------------------------------------------");
        while(rs.next()){
            Integer empno = rs.getInt("empno");
            String ename = rs.getString("ename");
            String age = rs.getString("age");
            System.out.println(empno + "\t" + ename + "\t" + age);
        }
        connection.close();
    }
}

编译：javac -d . Select.java
运行：java -cp .:../mysql-connector-java-5.1.48.jar com.sequoiadb.mysql.Select

将 empno 为 10001 的 age 修改为 49 ，Update.java 文件源代码如下:

package com.sequoiadb.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Update {
    private static String url = "jdbc:mysql://127.0.0.1:3306/company?useUnicode=true&charac
terEncoding=utf-8&useSSL=false";
    private static String username = "root";
    private static String password = "";

    public static void main(String[] args) throws SQLException {
        update();
    }

    public static void update() throws SQLException {
        MySQLConnection mysqlConnection = new MySQLConnection(url, username, password);

        Connection connection = mysqlConnection.getConnection();
        String sql = "update employee set age = ? where empno = ?";
        PreparedStatement psmt = connection.prepareStatement(sql);
        psmt.setInt(1, 49);
        psmt.setInt(2, 10001);
        psmt.execute();

        connection.close();
    }
}

编译：javac -d . Update.java
运行：java -cp .:../mysql-connector-java-5.1.48.jar com.sequoiadb.mysql.Update

将 empno 值为 10006 的记录删除， Delete.java 源代码如下:

package com.sequoiadb.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Delete {
    private static String url = "jdbc:mysql://127.0.0.1:3306/company?useUnicode=true&charac
terEncoding=utf-8&useSSL=false";
    private static String username = "root";
    private static String password = "";

    public static void main(String[] args) throws SQLException {
        delete();
    }

    public static void delete() throws SQLException {
        MySQLConnection mysqlConnection = new MySQLConnection(url, username, password);

        Connection connection = mysqlConnection.getConnection();
        String sql = "delete from employee where empno = ?";
        PreparedStatement psmt = connection.prepareStatement(sql);
        psmt.setInt(1, 10006);
        psmt.execute();
        connection.close();
    }
}

编译：javac -d . Delete.java
运行：java -cp .:../mysql-connector-java-5.1.48.jar com.sequoiadb.mysql.Delete

PostgreSQL实例应用开发

以 sdbadmin 用户登录，在 PostgreSQL 实例创建数据库 company：/opt/sequoiasql/postgresql/bin/sdb_sql_ctl createdb company myinst
登录到 PostgreSQL 实例 Shell: /opt/sequoiasql/postgresql/bin/psql -p 5432 company
加载SequoiaDB连接驱动: CREATE EXTENSION sdb_fdw;
创建 sdb_server 这个连接器:

CREATE SERVER sdb_server FOREIGN DATA WRAPPER sdb_fdw OPTIONS 
(
address '127.0.0.1', 
service '11810', 
user '', 
password '', 
preferedinstance 'A', 
transaction 'on' 
);

创建 company_domains 逻辑域: db.createDomain("company_domains", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 company 集合空间: db.createCS("company", { Domain: "company_domains" } );
创建 employee 集合: db.company.createCL("employee", { "ShardingKey": { "_id": 1}, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false });

登录到 PostgreSQL 实例 Shell: /opt/sequoiasql/postgresql/bin/psql -p 5432 company
创建 PostgreSQL 实例中的外表并与 SequoiaDB 中的集合空间和集合关联:

CREATE FOREIGN TABLE employee 
(
empno INT,
ename VARCHAR(128),
age INT
) 
SERVER sdb_server OPTIONS ( collectionspace 'company', collection 'employee', decimal 'on' );

检查 PostgreSQL 中创建的表: \d

postgreSQL连接说明

插入数据：

INSERT INTO employee VALUES (10001, 'Georgi', 48);
INSERT INTO employee VALUES (10002, 'Bezalel', 21);
INSERT INTO employee VALUES (10003, 'Parto', 33);
INSERT INTO employee VALUES (10004, 'Chirstian', 40);
INSERT INTO employee VALUES (10005, 'Kyoichi', 23);
INSERT INTO employee VALUES (10006, 'Anneke', 19);

更新 PostgreSQL 实例外表 employee 中的数据，将 empno 为10001的记录 age 更改为34: UPDATE employee SET age=34 WHERE empno = 10001;
删除 PostgreSQL 实例外表 employee 中的数据，将 empno 为 10006 的记录删除：DELETE FROM employee WHERE empno = 10006;

进入 SequoiaDB Shell，在 SequoiaDB 数据库引擎集合 employee 的 ename 字段上创建索引: db.company.employee.createIndex("idx_ename", { ename: 1 }, false);
查看执行计划：db.company.employee.find( { "ename": { "$et": "Georgi" } } ).explain();

进入源码放置目录: cd /home/sdbadmin/source/postgresql
首先对连接的 java 文件进行编译: javac -d . PostgreSQLConnection.java
增加 empno 为 30001 、 30002 和 30003 这三条记录，Insert.java 源代码如下:

package com.sequoiadb.postgresql;

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

public class Insert {
    private static String url = "jdbc:postgresql://127.0.0.1:5432/company";
    private static String username = "sdbadmin";
    private static String password = "";
    public static void main(String[] args) throws SQLException {
        insert();
    }

    public static void insert() throws SQLException {
        PostgreSQLConnection pgConnection = new PostgreSQLConnection(url, username, passwor
d);
        Connection connection = pgConnection.getConnection();
        String sql = "INSERT INTO employee VALUES";
        Statement stmt = connection.createStatement();
        StringBuffer sb = new StringBuffer();
        sb.append("(").append(30001).append(",").append("'Mike'").append(",").append(20).ap
pend("),");
        sb.append("(").append(30002).append(",").append("'Donna'").append(",").append(21).a
ppend("),");
        sb.append("(").append(30003).append(",").append("'Jack'").append(",").append(22).ap
pend("),");
        sb.deleteCharAt(sb.length() - 1);
        sql = sql + sb.toString();
        System.out.println(sql);
        stmt.executeUpdate(sql);
        connection.close();
    }
}

编译：javac -d . Insert.java
运行：java -cp .:../postgresql-9.3-1104-jdbc41.jar com.sequoiadb.postgresql.Insert

查询返回 empno 、 ename 和 age 这三个字段 ，Select.java 源代码如下:

package com.sequoiadb.postgresql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Select {
    private static String url = "jdbc:postgresql://127.0.0.1:5432/company";
    private static String username = "sdbadmin";
    private static String password = "";

    public static void main(String[] args) throws SQLException {
        select();
    }

    public static void select() throws SQLException {
        PostgreSQLConnection pgConnection = new PostgreSQLConnection(url, username, passwor
d);
        Connection connection = pgConnection.getConnection();
        String sql = "select * from employee";
        PreparedStatement psmt = connection.prepareStatement(sql);
        ResultSet rs = psmt.executeQuery();
        System.out.println("---------------------------------------------------------------
-------");
        System.out.println("empno \t ename \t age");
        System.out.println("---------------------------------------------------------------
-------");
        while(rs.next()){
            Integer empno = rs.getInt("empno");
            String ename = rs.getString("ename");
            String age = rs.getString("age");

            System.out.println(empno + "\t" + ename + "\t" + age);
        }
        connection.close();
    }
}

编译：javac -d . Select.java
运行：java -cp .:../postgresql-9.3-1104-jdbc41.jar com.sequoiadb.postgresql.Select

将 empno 值为 10004 的 age 修改为 41 ， Update.java 源代码如下:

package com.sequoiadb.postgresql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Update {
    private static String url = "jdbc:postgresql://127.0.0.1:5432/company";
    private static String username = "sdbadmin";
    private static String password = "";

    public static void main(String[] args) throws SQLException {
        update();
    }

    public static void update() throws SQLException {
        PostgreSQLConnection pgConnection = new PostgreSQLConnection(url, username, passwor
d);
        Connection connection = pgConnection.getConnection();
        String sql = "update employee set age = ? where empno = ?";
        PreparedStatement psmt = connection.prepareStatement(sql);
        psmt.setInt(1, 41);
        psmt.setInt(2, 10004);
        psmt.execute();

        connection.close();
    }
}

编译：javac -d . Update.java
运行：java -cp .:../postgresql-9.3-1104-jdbc41.jar com.sequoiadb.postgresql.Update

将 empno 值为 10005 的记录删除 , Delete.java 源代码如下:

package com.sequoiadb.postgresql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Delete {
    private static String url = "jdbc:postgresql://127.0.0.1:5432/company";
    private static String username = "sdbadmin";
    private static String password = "";

    public static void main(String[] args) throws SQLException {
        delete();
    }

    public static void delete() throws SQLException {
        PostgreSQLConnection pgConnection = new PostgreSQLConnection(url, username, passwor
d);
        Connection connection = pgConnection.getConnection();
        String sql = "delete from employee where empno = ?";
        PreparedStatement psmt = connection.prepareStatement(sql);
        psmt.setInt(1, 10005);
        psmt.execute();
        connection.close();
    }
}

编译：javac -d . Delete.java
运行：java -cp .:../postgresql-9.3-1104-jdbc41.jar com.sequoiadb.postgresql.Delete

SparkSQL实例应用开发

创建 company_domains 逻辑域: db.createDomain("company_domains", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 company 集合空间: db.createCS("company", { Domain: "company_domains" } );
创建 employee 集合: db.company.createCL("employee", { "ShardingKey": { "_id": 1}, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false });

使用 Beeline 客户端连接 SparkSQL 实例服务：/opt/spark/bin/beeline -u 'jdbc:hive2://localhost:10000'
在 SparkSQL 实例中创建数据库 company，并切换至company库: create database company; use company;
在 SparkSQL 实例中创建表并与 SequoiaDB 数据库存储引擎中的集合空间和集合关联:

CREATE TABLE employee 
(
empno      INT,
ename STRING,
age INT
) USING com.sequoiadb.spark OPTIONS ( host 'localhost:11810', collectionspace 'company', collection 'employee' );

从 SparkSQL 实例中创建视图、表及数据类型对应关系的详细说明请参考:SparkSQL 实例访问SequoiaDB数据库存储引擎

在 SparkSQL 实例关联表 employee 中插入数据:

INSERT INTO employee VALUES (10001, 'Georgi', 48);
INSERT INTO employee VALUES (10002, 'Bezalel', 21);
INSERT INTO employee VALUES (10003, 'Parto', 33);
INSERT INTO employee VALUES (10004, 'Chirstian', 40);
INSERT INTO employee VALUES (10005, 'Kyoichi', 23);
INSERT INTO employee VALUES (10006, 'Anneke', 19);

创建Java工程目录：mkdir -p /home/sdbadmin/spark/lib
进入工程目录：cd /home/sdbadmin/spark
拷贝 JDBC 接口需要的 jar 包

cp /opt/spark/jars/commons-logging-1.1.3.jar ./lib
cp /opt/spark/jars/hadoop-common-2.7.3.jar ./lib
cp /opt/spark/jars/hive-exec-1.2.1.spark2.jar ./lib
cp /opt/spark/jars/hive-jdbc-1.2.1.spark2.jar ./lib
cp /opt/spark/jars/hive-metastore-1.2.1.spark2.jar ./lib
cp /opt/spark/jars/httpclient-4.5.6.jar ./lib
cp /opt/spark/jars/httpcore-4.4.10.jar ./lib
cp /opt/spark/jars/libthrift-0.9.3.jar ./lib
cp /opt/spark/jars/log4j-1.2.17.jar ./lib
cp /opt/spark/jars/slf4j-api-1.7.16.jar ./lib
cp /opt/spark/jars/slf4j-log4j12-1.7.16.jar ./lib
cp /opt/spark/jars/spark-network-common_2.11-2.4.4.jar ./lib
cp /opt/spark/jars/spark-hive-thriftserver_2.11-2.4.4.jar ./lib

复制以下代码到实验环境终端执行，生成通过 JDBC 接口操作 SparkSQL 数据的 Select.java 文件:

cat > /home/sdbadmin/spark/Select.java << EOF
package com.sequoiadb.spark;

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

public class Select {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    public static void main(String args[])
    {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        String url = "jdbc:hive2://localhost:10000/company";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, "sdbadmin", "");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("Connection success!");
        try {
            System.out.println("--------- Get Records ---------");
            Statement st = conn.createStatement();
            String sql = " select * from employee";
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getInt("empno") + "\t" + rs.getString("ename") + "\t" + rs.getInt("age"));
            }
            rs.close();
            st.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

EOF

编译：javac -d . Select.java
运行：java -cp .:./lib/* com.sequoiadb.spark.Select

JSON实例应用开发

创建 company_domains 逻辑域: db.createDomain("company_domains", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 company 集合空间: db.createCS("company", { Domain: "company_domains" } );
创建 employee 集合: db.company.createCL("employee", { "ShardingKey": { "_id": 1}, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false });
在 JSON 实例集合 company 中写入数据：

db.company.employee.insert( { "empno": 10001, "ename": "Georgi", "age": 48 } );
db.company.employee.insert( { "empno": 10002, "ename": "Bezalel", "age": 21 } );
db.company.employee.insert( { "empno": 10003, "ename": "Parto", "age": 33 } );
db.company.employee.insert( { "empno": 10004, "ename": "Chirstian", "age": 40 } );
db.company.employee.insert( { "empno": 10005, "ename": "Kyoichi", "age": 23 } );
db.company.employee.insert( { "empno": 10006, "ename": "Anneke", "age": 19 } );

查询集合 employees 中age 大于20，小于30的数据：db.company.employee.find( { "age": { "$gt": 20, "$lt": 30 } }
集合 employees 中的数据，将 empno 为10001的记录 age 更改为34: db.company.employee.update( { "$set": { "age": 34 } }, { "empno": 10001 } );
删除集合 employees 中的数据，将 empno 为10006的记录删除: db.company.employee.remove( { "empno": 10006 } );
在集合 employee 的 ename 字段上创建索引: db.company.employee.createIndex("idx_ename", { ename: 1 }, false);
查看集合 employee 上创建的索引：db.company.employee.listIndexes();
显示集合 employees 查询语句执行计划: db.company.employee.find( { "ename": "Georgi" } ).explain();

创建Java工程目录：mkdir -p /home/sdbadmin/json/lib
进入工程目录：cd /home/sdbadmin/json
拷贝 JSON 实例驱动包: cp /opt/sequoiadb/java/sequoiadb-driver-3.4.jar ./lib
建立连接：

cat > /home/sdbadmin/json/Datasource.java << EOF

package com.sequoiadb.samples;

import java.util.ArrayList;
import org.bson.BSONObject;
import org.bson.BasicBSONObject;
import com.sequoiadb.base.CollectionSpace;
import com.sequoiadb.base.ConfigOptions;
import com.sequoiadb.base.DBCollection;
import com.sequoiadb.base.DBCursor;
import com.sequoiadb.base.Sequoiadb;
import com.sequoiadb.datasource.ConnectStrategy;
import com.sequoiadb.datasource.DatasourceOptions;
import com.sequoiadb.datasource.SequoiadbDatasource;
import com.sequoiadb.exception.BaseException;

public class Datasource {

    public static void main(String[] args) throws InterruptedException {
        ArrayList<String> addrs = new ArrayList<String>();
        String user = "";
        String password = "";
        ConfigOptions nwOpt = new ConfigOptions();
        DatasourceOptions dsOpt = new DatasourceOptions();
        SequoiadbDatasource ds = null;

        // 提供coord节点地址
        addrs.add("sdbserver1:11810");

        // 设置网络参数
        nwOpt.setConnectTimeout(500); // 建连超时时间为500ms。
        nwOpt.setMaxAutoConnectRetryTime(0); // 建连失败后重试时间为0ms。

        // 设置连接池参数
        dsOpt.setMaxCount(500); // 连接池最多能提供500个连接。
        dsOpt.setDeltaIncCount(20); // 每次增加20个连接。
        dsOpt.setMaxIdleCount(20); // 连接池空闲时，保留20个连接。
        dsOpt.setKeepAliveTimeout(0); // 池中空闲连接存活时间。单位:毫秒。
                                        // 0表示不关心连接隔多长时间没有收发消息。
        dsOpt.setCheckInterval(60 * 1000); // 每隔60秒将连接池中多于
                                            // MaxIdleCount限定的空闲连接关闭，
                                            // 并将存活时间过长（连接已停止收发
                                            // 超过keepAliveTimeout时间）的连接关闭。
        dsOpt.setSyncCoordInterval(0); // 向catalog同步coord地址的周期。单位:毫秒。
                                        // 0表示不同步。
        dsOpt.setValidateConnection(false); // 连接出池时，是否检测连接的可用性，默认不检测。
        dsOpt.setConnectStrategy(ConnectStrategy.BALANCE); // 默认使用coord地址负载均衡的策略获取连接。

        // 建立连接池
        ds = new SequoiadbDatasource(addrs, user, password, nwOpt, dsOpt);

        // 使用连接池运行任务
        runTask(ds);

        // 任务结束后，关闭连接池
        ds.close();
    }

    static void runTask(SequoiadbDatasource ds) throws InterruptedException {
        String clFullName = "company.employee";
        // 准备任务

        Thread insertTask = new Thread(new InsertTask(ds, clFullName));
        Thread queryTask = new Thread(new QueryTask(ds, clFullName));

        // 往集合插记录
        insertTask.start();
        Thread.sleep(3000);

        // 从集合中查记录
        queryTask.start();

        // 等待任务结束
        insertTask.join();
        queryTask.join();
    }
}

class InsertTask implements Runnable {
    private SequoiadbDatasource ds;
    private String csName;
    private String clName;

    public InsertTask(SequoiadbDatasource ds, String clFullName) {
        this.ds = ds;
        this.csName = clFullName.split("\\\.")[0];
        this.clName = clFullName.split("\\\.")[1];
    }

    @Override
    public void run() {
        Sequoiadb db = null;
        CollectionSpace cs = null;
        DBCollection cl = null;
        BSONObject record = null;
        // 从连接池获取连接
        try {
            db = ds.getConnection();
        } catch (BaseException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (InterruptedException e) {
            e.printStackTrace();
            System.exit(1);
        }

        // 使用连接获取集合对象
        cs = db.getCollectionSpace(csName);
        cl = cs.getCollection(clName);
        // 使用集合对象插入记录
        record = genRecord();
        cl.insert(record);
        // 将连接归还连接池
        ds.releaseConnection(db);
        System.out.println("Suceess to insert record: " + record.toString());
    }

    private BSONObject genRecord() {
        BSONObject obj = new BasicBSONObject();
        obj.put("empno", 10007);
        obj.put("ename", "JACK");
        obj.put("age", 30);
        return obj;
    }
}

class QueryTask implements Runnable {
    private SequoiadbDatasource ds;
    private String csName;
    private String clName;

    public QueryTask(SequoiadbDatasource ds, String clFullName) {
        this.ds = ds;
        this.csName = clFullName.split("\\\.")[0];
        this.clName = clFullName.split("\\\.")[1];
    }

    @Override
    public void run() {
        Sequoiadb db = null;
        CollectionSpace cs = null;
        DBCollection cl = null;
        DBCursor cursor = null;
        // 从连接池获取连接
        try {
            db = ds.getConnection();
        } catch (BaseException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (InterruptedException e) {
            e.printStackTrace();
            System.exit(1);
        }
        // 使用连接获取集合对象
        cs = db.getCollectionSpace(csName);
        cl = cs.getCollection(clName);
        // 使用集合对象查询
        cursor = cl.query();
        try {
            while (cursor.hasNext()) {
                System.out.println("The inserted record is: " + cursor.getNext());
            }
        } finally {
            cursor.close();
        }
        // 将连接对象归还连接池
        ds.releaseConnection(db);
    }
}
EOF

编译：javac -cp .:./lib/sequoiadb-driver-3.4.jar -d . Datasource.java
运行：java -cp .:./lib/sequoiadb-driver-3.4.jar com.sequoiadb.samples.Datasource

S3 对象存储部署与应用开发
开启 RC 级别事务，且配置为等锁模式: db.updateConf( { transactionon: true, transisolation: 1, translockwait: true } );
创建存储元数据的集合空间所在的域：db.createDomain("metaDomain", [ "group1", "group2", "group3" ], { AutoSplit: true} );
创建存储对象数据的集合空间所在的域: db.createDomain("dataDomain", [ "group1", "group2", "group3" ], { AutoSplit: true} );

配置SequoiaS3系统：
进入 SequoiaS3 包目录：cd /opt/sequoiadb/tools/sequoias3
配置 SequoiaS3，配置对外监听端口：echo 'server.port=8002' >> config/application.properties
配置 coord 节点的 IP 和端口，可以配置多组并使用逗号分隔: echo 'sdbs3.sequoiadb.url=sequoiadb://localhost:11810' >> config/application.properties
配置 SequoiaS3 的存储创建了专属的域:

echo 'sdbs3.sequoiadb.meta.domain=metaDomain' >> config/application.properties
echo 'sdbs3.sequoiadb.data.domain=dataDomain' >> config/application.properties

SequoiaS3实例参数配置

配置修改完成后，通过 ./sequoias3.sh 可执行脚本启动 SequoiaS3：/opt/sequoiadb/tools/sequoias3/sequoias3.sh start
查看集合列表: db.list(SDB_SNAP_COLLECTIONS);

使用 curl 创建用户 s3user: curl -v -X POST "http://localhost:8002/users/?Action=CreateUser&UserName=s3user&role=admin" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"
使用 curl 获取 s3user 用户访问秘钥（需管理员用户权限）：curl -v -X POST "http://localhost:8002/users/?Action=GetAccessKey&UserName=s3user" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"
使用 curl 删除用户 s3user（需管理员用户权限）：curl -v -X POST "http://localhost:8002/users/?Action=DeleteUser&UserName=s3user" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"

使用 curl 创建桶 sdbbucket: curl -v -X PUT "http://localhost:8002/sdbbucket" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"
使用 curl 获取所有桶信息：curl -v -X GET "http://localhost:8002" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"
使用 curl 删除桶 sdbbucket: curl -v -X DELETE "http://localhost:8002/sdbbucket" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"
检查桶是否存在，确认结果中是否存在桶 sdbbucket: curl -v GET "http://localhost:8002" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"

文件对象上传：
检查 /opt/sequoiadb/tools/sequoias3/ 目录下是否存在 sequoia-s3-3.4.jar 这个文件，后面向 sdbbucket 桶上传此文件：ls /opt/sequoiadb/tools/sequoias3/sequoia-s3-3.4.jar
使用 curl 向 sdbbucket 中写入文件 “sequoia-s3-3.4.jar” ，在S3中的名称是 “sdbs3.jar”: curl -X PUT -T "/opt/sequoiadb/tools/sequoias3/sequoia-s3-3.4.jar" "http://localhost:8002/sdbbucket/sdbs3.jar" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD" -H "Content-Type: text/plain"

在元数据集合中查找文件名，确定是否上传成功: db.S3_SYS_Meta.S3_ObjectMeta.find( { "Key": "sdbs3.jar" } );
文件对象下载：
从桶 sdbbucket 中读取文件对象 “sdbs3.jar”，并存放到本地目录 sdbs3.jar 文件中: curl -o sdbs3.jar -X GET "http://localhost:8002/sdbbucket/sdbs3.jar" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD" -H "Content-Type: text/plain"

文件对象删除：
从桶 sdbbucket 中删除文件对象 “sdbs3.jar”: curl -X DELETE "http://localhost:8002/sdbbucket/sdbs3.jar" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"

创建 JAVA 工程目录: mkdir -p /home/sdbadmin/s3/lib
进入工程目录: cd /home/sdbadmin/s3
拷贝 S3 接口需要的 jar 包: cp /home/sdbadmin/package/s3/* ./lib
制以下代码到实验环境终端执行，生成创建桶和上传文件的 Init.java 文件:

cat > /home/sdbadmin/s3/Init.java << EOF
package com.sequoiadb.samples;

import com.amazonaws.auth.AWSCredentials;
import com.amazonaws.auth.AWSStaticCredentialsProvider;
import com.amazonaws.auth.BasicAWSCredentials;
import com.amazonaws.client.builder.AwsClientBuilder;
import com.amazonaws.services.s3.AmazonS3;
import com.amazonaws.services.s3.AmazonS3ClientBuilder;
import com.amazonaws.services.s3.model.PutObjectRequest;
import java.io.File;
import java.io.IOException;

public class Init {

    public static void main(String[] args) throws IOException {

        //初始化客户端, 生成一个与SequoiaS3的连接, 此处需要修改endPoint的地址和端口, 使之指向SequoiaS3的地址和端口.
        AWSCredentials credentials = new BasicAWSCredentials("ABCDEFGHIJKLMNOPQRST", "abcdefghijklmnopqrstuvwxyz0123456789ABCD");
        String endPoint = "http://127.0.0.1:8002";
        AwsClientBuilder.EndpointConfiguration endpointConfiguration = new AwsClientBuilder.EndpointConfiguration(endPoint, null);
        AmazonS3 s3 = AmazonS3ClientBuilder.standard()
                .withEndpointConfiguration(endpointConfiguration)
                .withCredentials(new AWSStaticCredentialsProvider(credentials))
                .build();
        //创建一个名为"javabucket"的桶
        s3.createBucket("javabucket");
        //从本地上传文件 sequoia-s3-3.4.jar 到"javabucket"的存储桶中，并命名为"objectname"
        PutObjectRequest request = new PutObjectRequest("javabucket", "objectname", new File("/opt/sequoiadb/tools/sequoias3/sequoia-s3-3.4.jar"));
        s3.putObject(request);
    }
}

EOF

复制以下代码到实验环境终端执行，生成查询桶内对象列表和下载对象的 GetObject.java 文件:

cat > /home/sdbadmin/s3/GetObject.java << EOF
package com.sequoiadb.samples;

import com.amazonaws.auth.AWSCredentials;
import com.amazonaws.auth.AWSStaticCredentialsProvider;
import com.amazonaws.auth.BasicAWSCredentials;
import com.amazonaws.client.builder.AwsClientBuilder;
import com.amazonaws.services.s3.AmazonS3;
import com.amazonaws.services.s3.AmazonS3ClientBuilder;
import com.amazonaws.services.s3.model.GetObjectRequest;
import com.amazonaws.services.s3.model.PutObjectRequest;
import com.amazonaws.services.s3.model.S3Object;
import com.amazonaws.services.s3.model.S3ObjectInputStream;
import com.amazonaws.services.s3.model.ListObjectsV2Result;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class GetObject {

    public static void main(String[] args) throws IOException {

        AWSCredentials credentials = new BasicAWSCredentials("ABCDEFGHIJKLMNOPQRST", "abcdefghijklmnopqrstuvwxyz0123456789ABCD");
        String endPoint = "http://127.0.0.1:8002";
        AwsClientBuilder.EndpointConfiguration endpointConfiguration = new AwsClientBuilder.EndpointConfiguration(endPoint, null);
        AmazonS3 s3 = AmazonS3ClientBuilder.standard()
                .withEndpointConfiguration(endpointConfiguration)
                .withCredentials(new AWSStaticCredentialsProvider(credentials))
                .build();
       //查询名为"bucketname"的桶中所有对象名称
       ListObjectsV2Result listObj = s3.listObjectsV2("javabucket");
        System.out.println("ListObjectsV2Result: "+listObj);
        //从"javabucket"获得"objectname"对象, 并将对象内容存储在本地文件中
        GetObjectRequest request = new GetObjectRequest("javabucket", "objectname");
        S3Object result = s3.getObject(request);
        S3ObjectInputStream s3is = result.getObjectContent();
        FileOutputStream fos = new FileOutputStream(new File("/home/sdbadmin/s3/java_test_s3.jar"));
        byte[] read_buf = new byte[1024];
        int read_len = 0;
        while ((read_len = s3is.read(read_buf)) > 0) {
            fos.write(read_buf, 0, read_len);
        }
        s3is.close();
        fos.close();
    }
}
EOF

复制以下代码到实验环境终端执行，生成删除对象和桶的 DeleteBucket.java 文件:

cat > /home/sdbadmin/s3/DeleteBucket.java << EOF
package com.sequoiadb.samples;

import com.amazonaws.auth.AWSCredentials;
import com.amazonaws.auth.AWSStaticCredentialsProvider;
import com.amazonaws.auth.BasicAWSCredentials;
import com.amazonaws.client.builder.AwsClientBuilder;
import com.amazonaws.services.s3.AmazonS3;
import com.amazonaws.services.s3.AmazonS3ClientBuilder;
import com.amazonaws.services.s3.model.PutObjectRequest;
import java.io.IOException;

public class DeleteBucket {

    public static void main(String[] args) throws IOException {


        AWSCredentials credentials = new BasicAWSCredentials("ABCDEFGHIJKLMNOPQRST", "abcdefghijklmnopqrstuvwxyz0123456789ABCD");
        String endPoint = "http://127.0.0.1:8002";
        AwsClientBuilder.EndpointConfiguration endpointConfiguration = new AwsClientBuilder.EndpointConfiguration(endPoint, null);
        AmazonS3 s3 = AmazonS3ClientBuilder.standard()
                .withEndpointConfiguration(endpointConfiguration)
                .withCredentials(new AWSStaticCredentialsProvider(credentials))
                .build();
        //删除"javabucket"桶中名为"objectname"的对象
        s3.deleteObject("javabucket", "objectname");
        //删除名为"javabucket"的存储桶
        s3.deleteBucket("javabucket");
    }
}

EOF

编译：javac -cp .:./lib/* -d . Init.java
运行：java -cp .:./lib/* com.sequoiadb.samples.Init
编译：javac -cp .:./lib/* -d . GetObject.java
运行：java -cp .:./lib/* com.sequoiadb.samples.GetObject
编译：javac -cp .:./lib/* -d . DeleteBucket.java
运行：java -cp .:./lib/* com.sequoiadb.samples.DeleteBucket

检查桶是否存在，确认结果中是否存在桶 javabucket: curl -v GET "http://localhost:8002" -H "Host: localhost:8002" -H "Authorization: AWS ABCDEFGHIJKLMNOPQRST:abcdefghijklmnopqrstuvwxyz0123456789ABCD"

NFS文件系统部署与应用开发
创建fs_domain逻辑域：db.createDomain("fs_domain", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 fscs 集合空间: db.createCS("fscs", { Domain: "fs_domain" } );
创建 fscl 集合: db.fscs.createCL("fscl", { "ShardingKey": { "_id": 1}, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );

创建挂载点及配置文件：
创建挂载点 mountpoint: mkdir -p /opt/sequoiadb/sequoiafs/mountpoint
sdbadmin 用户登录，创建sequoiafs的配置文件目录和日志目录:

mkdir -p /opt/sequoiadb/sequoiafs/conf/fscs_fscl/001/
mkdir -p /opt/sequoiadb/sequoiafs/log/fscs_fscl/001/

生成一个空的配置文件，SequoiaFS 服务在启动时会将指定的值写入该文件中，其他参数使用缺省值: touch /opt/sequoiadb/sequoiafs/conf/fscs_fscl/001/sequoiafs.conf

启动SequoiaFS服务：
启动服务之后 mountpoint 目录下的所有文件信息会存放在远程数据库节点上，可以在数据库的目录元数据集合及文件元数据集合中查看，文件内容会以 LOB对象 的形式存放在目标集合下: sequoiafs /opt/sequoiadb/sequoiafs/mountpoint -i localhost:11810 -l fscs.fscl --autocreate -c /opt/sequoiadb/sequoiafs/conf/fscs_fscl/001/ --diagpath /opt/sequoiadb/sequoiafs/log/fscs_fscl/001/ -o big_writes -o max_write=131072 -o max_read=131072

SequoiaFS 实例参数配置

查看挂载目录和底层存储信息：
本地 SequoiaFS 节点通过 mount 可以看到挂载信息：mount

SequoiaDB查看创建的集合信息：db.list(SDB_LIST_COLLECTIONS);
sequoiafs.maphistory 为映射挂载历史信息表，记录历史挂载的关键数据信息: db.sequoiafs.maphistory.find();

挂载目录下文件、目录操作：
进入挂载目录：cd /opt/sequoiadb/sequoiafs/mountpoint/
创建目录：mkdir fsdir
进入创建的 fsdir 目录： cd /opt/sequoiadb/sequoiafs/mountpoint/fsdir
使用 echo 重定向内容创建文件: echo 'hello, this is a fsfile!' >> fsfile.txt

查看挂载目录创建的文件及存储内容：
查看数据库中文件存储情况：db.sequoiafs.fscl_file142361856883863522.find();
查看数据库中目录存储情况: db.sequoiafs.fscl_dir142361856883863522.find();

查看集合中的 LOB 信息， 文件已经保存为一条 LOB 记录: db.fscs.fscl.listLobs();

创建 JAVA 工程目录: mkdir -p /home/sdbadmin/nfs
进入工程目录: cd /home/sdbadmin/nfs
复制以下代码到实验环境终端执行生成 CopyFile.java 文件，用于操作 NFS 文件系统:

cat > /home/sdbadmin/nfs/CopyFile.java << EOF
package com.sequoiadb.samples;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

public class CopyFile {

    public static void main(String[] args) throws IOException {

        // 创建目录
        File dir = new File("/opt/sequoiadb/sequoiafs/mountpoint/fsdir/cpdir");
        if (!dir.exists()) {
            dir.mkdirs();
            System.out.println("Directory created successfully");
        } else {
            System.out.println("Directory already exists");
        }

        // 复制文件到新目录
        File source = new File("/opt/sequoiadb/sequoiafs/mountpoint/fsdir/fsfile.txt");
        File dest = new File("/opt/sequoiadb/sequoiafs/mountpoint/fsdir/cpdir/fsfile.txt");
        InputStream input = null;
        OutputStream output = null;
        try {
            input = new FileInputStream(source);
            output = new FileOutputStream(dest);
            byte[] buf = new byte[1024];
            int bytesRead;
            while ((bytesRead = input.read(buf)) != -1) {
                output.write(buf, 0, bytesRead);
            }
            System.out.println("File copied successfully");
        } finally {
            input.close();
            output.close();
        }
    }

}
EOF

编译：javac -d . CopyFile.java
运行：java -cp .:./lib/* com.sequoiadb.samples.CopyFile