import java.io.IOException;
import java.sql.*;
import java.text.MessageFormat;
import java.time.*;
import java.util.Properties;
import com.dolphindb.jdbc.JDBCStatement;
import com.xxdb.DBConnection;
import com.xxdb.data.BasicMonth;
public class doctest
{
private static final String JDBC_DRIVER = "com.dolphindb.jdbc.Driver";
// Connect to DolphinDB server
private static final String url1 = "jdbc:dolphindb://localhost:8081";
// Connect to DolphinDB Database
private static final String url2 = "jdbc:dolphindb://localhost:8081?databasePath=dfs://valuedb&partitionType=VALUE&partitionScheme=2000.01M..2019.05M";
public static Connection conn = null;
public static PreparedStatement stmt = null;
//Output in-memory table
public static void printData(ResultSet rs) throws SQLException {
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int len = resultSetMetaData.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= len; ++i) {
System.out.print(
MessageFormat.format("{0}: {1}, ", resultSetMetaData.getColumnName(i), rs.getObject(i)));
}
System.out.print("\n");
}
}
/*-------------------------------------------Query, add, delete and update the in-memory table-------------------------------------------*/
//1锛巖eate a memory table and save it locally: Put all the statements into a String, and finally connect to the port by DBconnection and run
@SuppressWarnings("finally")
public static boolean CreateTable(String database,String tableName,String host, String 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, Integer.parseInt(port));
db.run(sb);
success=true;
} catch (Exception e) {
e.printStackTrace();
success=false;
} finally {
if (db != null)
db.close();
return success;
}
}
//2锛嶪nsert records into an in-memory table
public static void InMemmoryAddTest(Properties info, String database, String tableName)
{
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(url1,info);
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, 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();
//Query the in-memory table
ResultSet rs = stmt.executeQuery("select * from memTable");
printData(rs);
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
// Release
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}
try {
// Release
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
//3锛嶥elete records from the in-memory table
public static void InMemoryDeleteTest(Properties info, String database, String tableName)
{
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(url1);
JDBCStatement stm = (JDBCStatement)conn.createStatement();
stm.execute("memTable = loadTable('" + database + "','" + tableName + "')");
//SQL delete璇彞
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();
}
}
}
//4锛嶶pdate an in-memory table
public static void InMemoryUpdateTest(Properties info, String database, String tableName)
{
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(url1);
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 the updates have been made
ResultSet rs = stmt.executeQuery("select * from memTable where char=97c");
System.out.println("==========InMemoryUpdateTest======================");
printData(rs);
} catch (Exception e) {
e.printStackTrace();
} finally {
// Release
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}
// Rlease
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
/*-------------------------------------------Query and insert on a partitioned table-------------------------------------------*/
//1. Create a partitioned table
@SuppressWarnings("finally")
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;
}
}
//锛掞紟Add records
public static void DFSAddTest(Properties info, String database, String tableName)
{
try {
Class.forName(JDBC_DRIVER);
// load 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 to check if the record have been added.
ResultSet rs = stmt.executeQuery("select count(*) from loadTable(\""+database+"\", `"+tableName+")");
printData(rs);
} catch (Exception e) {
e.printStackTrace();
} finally {
// Release
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}
// Release
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
public static void main(String[] args)
{
String HOST="localhost";// Server address that has been configured and successfully started
String PORT="8081";// Port number
Properties info = new Properties(); // Login info
info.put("user", "admin");// Username
info.put("password", "123456");// Password(default admin password)
/*-------------------------------------------Operations on in-memory table-------------------------------------------*/
String in_memory_database="H://DolphinDBStreamWin/jdbc";// Local database path
String in_memory_tableName="t1";// Table name
//锛戯紟Create
boolean success=CreateTable(in_memory_database, "`"+in_memory_tableName, HOST, PORT);
if(success==true)
System.out.println("\n---------------Successfully create table "+in_memory_tableName+"!---------------\n");
//锛掞紟Insert
InMemmoryAddTest(info, in_memory_database, in_memory_tableName);
//锛�. Delete
InMemoryDeleteTest(info, in_memory_database, in_memory_tableName);
// //4锛嶶pdate
InMemoryUpdateTest(info, in_memory_database, in_memory_tableName);
// /*-------------------------------------------Operations on partitioned table-------------------------------------------*/
String dfs_database="dfs://valuedb";//dfs database path
String dfs_tableName="pt";// Table name
//锛戯紟Create
success=CreateValueTable(dfs_database, dfs_tableName, HOST, PORT);
if(success==true)
System.out.println("\n---------------Successfully create table "+dfs_tableName+"!---------------\n");
//锛掞紟Insert
DFSAddTest(info, dfs_database,dfs_tableName);
}
}