Writing a Sample

Check that the CUBRID SHARD operates normally by using a simple Java program.

Writing a Sample Table

Write a temporary table for the example in all shard DBs.

sh> csql -C -u shard -p 'shard123' shard1@localhost -c "create table student (s_no int, s_name varchar, s_age int, primary key(s_no))"

Writing Code

The following example program is to enter student information from 0 to 1023 to the shard DB. Check the shard.conf modified in the previous procedure and then set the address/port information and the user information in the connection url.

$ cat TestInsert.java

import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.PreparedStatement;

import java.sql.Date;

import java.sql.*;

import cubrid.jdbc.driver.*;

 

public class TestInsert {

 

        static  {

                try {

                        Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

                } catch (ClassNotFoundException e) {

                        throw new RuntimeException(e);

                }

        }

 

        public static void DoTest(int thread_id) throws SQLException {

                Connection connection = null;

 

                try {

                        connection = DriverManager.getConnection("jdbc:cubrid:localhost:45511:shard1:::?charset=utf8", "shard", "shard123");

                        connection.setAutoCommit(false);

 

                        for (int i=0; i < 1024; i++) {

                                String query = "INSERT INTO student VALUES (/*+ shard_key */ ?, ?, ?)";

                                PreparedStatement query_stmt = connection.prepareStatement(query);

 

                                String name="name_" + i;

                                query_stmt.setInt(1, i);

                                query_stmt.setString(2, name);

                                query_stmt.setInt(3, (i%64)+10);

 

                                query_stmt.executeUpdate();

                                System.out.print(".");

 

                                query_stmt.close();

                                connection.commit();

                        }

 

                        connection.close();

                } catch(SQLException e) {

                        System.out.print("exception occurs : " + e.getErrorCode() + " - " + e.getMessage());

                        System.out.println();

                        connection.close();

                }

        }

 

 

        /**

         * @param args

         */

        public static void main(String[] args) {

                // TODO Auto-generated method stub

 

                try {

                        DoTest(1);

                } catch(Exception e){

                        e.printStackTrace();

                }

        }

}

Executing a Sample

Execute the sample program as follows:

sh> javac -cp ".:$CUBRID/jdbc/cubrid_jdbc.jar" *.java

sh> java -cp ".:$CUBRID/jdbc/cubrid_jdbc.jar" TestInsert

................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................

Checking the result

Execute the query in each shard DB and check whether or not the partitioned information has been correctly entered.

• shard #0

• sh> csql -C -u shard -p 'shard123' shard1@localhost -c "select * from student order by s_no"

   

  === <Result of SELECT Command in Line 1> ===

   

           s_no  s_name                      s_age

  ================================================

              0  'name_0'                       10

              1  'name_1'                       11

              2  'name_2'                       12

              3  'name_3'                       13

              ………………

• shard #1

• sh> $ csql -C -u shard -p 'shard123' shard1@localhost -c "select * from student order by s_no"

   

  === <Result of SELECT Command in Line 1> ===

   

           s_no  s_name                      s_age

  ================================================

             64  'name_64'                      10

             65  'name_65'                      11

             66  'name_66'                      12

             67  'name_67'                      13  

  ………………

• shard #2

• sh> $ csql -C -u shard -p 'shard123' shard1@localhost -c "select * from student order by s_no"

   

  === <Result of SELECT Command in Line 1> ===

   

           s_no  s_name                      s_age

  ================================================

  128  'name_128'                     10

  129  'name_129'                     11

  130  'name_130'                     12

  131  'name_131'                     13

  ………………

• shard #3

• sh> $ csql -C -u shard -p 'shard123' shard1@localhost -c "select * from student order by s_no"

   

  === <Result of SELECT Command in Line 1> ===

   

           s_no  s_name                      s_age

  ================================================

  192  'name_192'                     10

  193  'name_193'                     11

  194  'name_194'                     12

  195  'name_195'                     13

  ………………