Java to Develop GaussDB

ssh root@EIP

cd /usr/local

wget https://download.oracle.com/java/17/latest/jdk-17_linux-x64_bin.tar.gz

tar -zxvf jdk-17_linux-x64_bin.tar.gz

vim /etc/profile


export JAVA_HOME=/usr/local/ jdk-17.0.7 #JDK_installation_directory
export JRE_HOME=${JAVA_HOME}/jre
export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib:${JAVA_HOME}/test:${JAVA_HOME}/lib/gsjdbc4.jar:${JAVA_HOME}/lib/dt.jar:${JAVA_HOME}/lib/tools.jar:$CLASSPATH 
export JAVA_PATH=${JAVA_HOME}/bin:${JRE_HOME}/bin
export PATH=$PATH:${JAVA_PATH}


source /etc/profile

java -version

create or replace procedure testproc 
(
psv_in1 in integer,
psv_in2 in integer,
psv_inout in out integer
)
as
begin
psv_inout := psv_in1 + psv_in2 + psv_inout;
end;
/

scp /home/user/Downloads/dws_8.1.x_jdbc_driver.zip  root@EIP:/opt

cd /opt

unzip dws_8.1.x_jdbc_driver.zip

cp /opt/jdbc/gsjdbc4.jar /usr/local/jdk-17.0.7/lib

Autocmd FileType * setlocal formatoptions-=c formatoptions-=r formatoptions-=o

cd /usr/local/jdk-17.0.7

mkdir -p test

cd test

vi DBTest.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Types;

public class DBTest {
// Create a database connection.
public static Connection GetConnection(String username, String passwd) {
    String driver = "org.postgresql.Driver";
    String sourceURL = "jdbc:postgresql://100.93.12.41:8000/gaussdb";
    Connection conn = null;
    try {
// Load the database driver.
      Class.forName(driver);
    } catch (Exception e) {
      e.printStackTrace();
      return null;
    }

    try {
// Create a database connection.
      conn = DriverManager.getConnection(sourceURL, username, passwd);
      System.out.println("Connection succeed!");
    } catch (Exception e) {
      e.printStackTrace();
      return null;
    }

    return conn;
  };

// Run a common SQL statement to create table customer_t1.
 public static void CreateTable(Connection conn) {
    Statement stmt = null;
    try {
      stmt = conn.createStatement();

      int rc = stmt
          .executeUpdate("CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));");

      stmt.close();
    } catch (SQLException e) {
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }

// Run a prepared statement to insert data in batches.
 public static void BatchInsertData(Connection conn) {
    PreparedStatement pst = null;

    try {
// Generate a prepared statement.
      pst = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?,?)");
      for (int i = 0; i < 3; i++) {
   // Add parameters.
        pst.setInt(1, i);
        pst.setString(2, "data " + i);
        pst.addBatch();
      }
// Perform batch processing.
      pst.executeBatch();
      pst.close();
    } catch (SQLException e) {
      if (pst != null) {
        try {
          pst.close();
        } catch (SQLException e1) {
        e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }

// Run a prepared statement to update data.
public static void ExecPreparedSQL(Connection conn) {
    PreparedStatement pstmt = null;
    try {
      pstmt = conn
          .prepareStatement("UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1");
      pstmt.setString(1, "new Data");
      int rowcount = pstmt.executeUpdate();
      pstmt.close();
    } catch (SQLException e) {
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }

// Run the stored procedure.
public static void ExecCallableSQL(Connection conn) {
    CallableStatement cstmt = null;
    try {

      cstmt=conn.prepareCall("{? = CALL TESTPROC(?,?,?)}");
      cstmt.setInt(2, 50); 
      cstmt.setInt(1, 20);
      cstmt.setInt(3, 90);
cstmt.registerOutParameter(4, Types.INTEGER);  // Register an OUT parameter of the integer type.
      cstmt.execute();
int out = cstmt.getInt(4);  // Obtain the OUT parameter.
      System.out.println("The CallableStatment TESTPROC returns:"+out);
      cstmt.close();
    } catch (SQLException e) {
      if (cstmt != null) {
        try {
          cstmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }

public static void main(String[] args) {
// Create a database connection.
   Connection conn = GetConnection("dbadmin", "49bVzlaMBKYPf3DL*G6j");

    // Create a table.
    CreateTable(conn);
// Insert data in batches.
    BatchInsertData(conn);
  //Run the precompilation statement to update data.
    ExecPreparedSQL(conn);
// Run the stored procedure.
    ExecCallableSQL(conn);
// Close the connection to the database.
    try {
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
      }   
    }  
}


cd /usr/local/jdk-17.0.7/test

javac DBTest.java

Java DBTest

select * from customer_t1;

CREATE USER JIM PASSWORD 'JIM1234@abcd';

ALTER USER JIM IDENTIFIED BY 'abcd@123' REPLACE 'JIM1234@abcd';


ALTER USER JIM ACCOUNT LOCK;

ALTER USER JIM ACCOUNT UNLOCK;

CREATE USER joe WITH PASSWORD 'Gauss@123' VALID BEGIN '2021-10-10 08:00:00' VALID UNTIL '2022-10-10 08:00:00';

select rolname,rolvalidbegin,rolvaliduntil from pg_authid where rolname='joe';

ALTER USER joe WITH VALID BEGIN '2021-11-10 08:00:00' VALID UNTIL '2022-11-10 08:00:00';


CREATE USER u1 PASSWORD 'Gauss_234';
CREATE USER u2 PASSWORD 'Gauss_234';

select usename from pg_catalog.pg_user where upper(usename) in ('U1','U2');

GRANT create on SCHEMA u1 to u2;

CREATE TABLE u1.t1 (c1 int);

CREATE TABLE u1.t1 (c1 int);

CREATE VIEW u2.v1 AS SELECT * FROM u1.t1;

SELECT r.rolname,c.relname,c.relkind FROM pg_class c,pg_roles r WHERE c.relowner=r.oid AND c.relname IN('t1','v1');

SELECT * FROM u2.v1;

GRANT USAGE ON SCHEMA u1 TO u2; 
GRANT SELECT ON u1.t1 TO u2;

SELECT * FROM u2.v1;

CREATE SCHEMA s1;
CREATE TABLE s1.t1 (c1 int);

SELECT * FROM s1.t1;

CREATE TABLE s1.t2 (c1 int);

GRANT USAGE ON SCHEMA s1 TO u1;
GRANT SELECT ON ALL TABLES IN SCHEMA s1 TO u1;

SELECT * FROM s1.t1;
SELECT * FROM s1.t2;

CREATE TABLE s1.t3 (c1 int);

SELECT * FROM s1.t3;

REVOKE SELECT ON ALL TABLES IN SCHEMA s1 FROM u1;

ALTER DEFAULT PRIVILEGES FOR ROLE dbadmin IN SCHEMA s1 GRANT SELECT ON TABLES TO u1;

CREATE TABLE s1.t4 (c1 int);

SELECT * FROM s1.t4;
SELECT * FROM s1.t3;

SELECT a.rolname, n.nspname FROM pg_authid AS a, pg_namespace AS n WHERE has_schema_privilege(a.oid, n.oid, 'CREATE');

SELECT * FROM INFORMATION_SCHEMA.role_table_grants where table_name = 't1';

DROP SCHEMA s1 CASCADE;
DROP USER u1 CASCADE;
DROP USER u2 CASCADE;

CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE USER u1 PASSWORD 'Gauss_234';
CREATE USER u2 PASSWORD 'Gauss_234';
CREATE USER u3 PASSWORD 'Gauss_234';
CREATE USER u4 PASSWORD 'Gauss_234';

select schema_name from information_schema.schemata where schema_name in ('s1','s2');
select usename from pg_catalog.pg_user where upper(usename) in ('U1','U2','U3','U4');

CREATE TABLE s1.t1 (c1 int, c2 int);
CREATE TABLE s2.t1 (c1 int, c2 int);

INSERT INTO s1.t1 VALUES (1,2);
INSERT INTO s2.t1 VALUES (1,2);

CREATE ROLE rs1_select PASSWORD disable;  -- Permission to query s1；
CREATE ROLE rs1_update PASSWORD disable; -- Permission to update s1；
CREATE ROLE rs2_select PASSWORD disable; -- Permission to query s2；
CREATE ROLE rs2_update PASSWORD disable; -- Permission to update s2；

GRANT USAGE ON SCHEMA s1, s2 TO rs1_select, rs1_update,
rs2_select, rs2_update;

GRANT SELECT ON ALL TABLES IN SCHEMA s1 TO rs1_select; -- Grant the query permission on all the tables in s1 to the rs1_select role；
GRANT SELECT,UPDATE ON ALL TABLES IN SCHEMA s1 TO rs1_update;  -- Grant the query and update permissions on all the tables in s1 to the rs1_update role；
GRANT SELECT ON ALL TABLES IN SCHEMA s2 TO rs2_select;  -- Grant the query permission on all the tables in s2 to the rs2_select role；
GRANT SELECT,UPDATE ON ALL TABLES IN SCHEMA s2 TO rs2_update;  -- Grant the query and update permissions on all the tables in s2 to the rs2_update role；


GRANT rs1_select, rs2_update TO u1, u2;  -- Users u1 and u2 have the permissions to query s1 and update s2；
GRANT rs2_select, rs1_update TO u3, u4;  -- Users u3 and u4 have the permissions to query s2 and update s1；

SELECT * FROM s1.t1;
UPDATE s1.t1 SET c2 = 3 WHERE c1 = 1;

SELECT * FROM s2.t1;
UPDATE s2.t1 SET c2 = 3 WHERE c1 = 1;