云实验

GaussDB 部署

在华为云部署GaussDB数据库实例，并通过gsql远程连接数据库实例

购买云服务（云数据库GaussDB）

计费模式：选择【按需计费】
区域：默认的【华北-北京四】
实例名称：可以设置为【gauss-hccda】
产品类型：基础版
数据库版本：选择最新版即可
实例类型：集中式
部署形态：1主2备
性能规格、存储类型、存储空间、磁盘加密保持默认
虚拟私有云：选择【vpc-hce】
内网安全组：选择【sg-hce】
管理员密码：设置符合安全要求的root用户密码

使用gsql连接数据库

1	ssh [email protected]

下载gsql客户端，并解压，使用gsql登录数据库

wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1642684986086/GaussDB_opengauss_client_tools.zip
unzip GaussDB_opengauss_client_tools.zip
cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64
cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt
cd /opt/
tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh

// 登录GaussDB（root）
gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r
// 创建数据库
CREATE DATABASE devdb ENCODING 'UTF8' template = template0;
\q
// 登录GaussDB（root）
gsql -h xxx.xx.xx.x -d devdb -p 8000 -U root -W yourpassword -r
// 创建用户
CREATE USER hccdp SYSADMIN IDENTIFIED BY "setyourpassword";
\q
// 登录GaussDB（hccdp）
gsql -h xxx.xx.xx.x -d devdb -p 8000 -U hccdp -W yourpassword -r
\conninfo
“You are connected to database "devdb" as user "hccdp" on host "xxx.xxx.x.xxx" at port "8000".”
\q

GaussDB SQL及常规操作

开始实验之前请先完成GaussDB 部署的实验内容

ssh [email protected]

wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1642684986086/GaussDB_opengauss_client_tools.zip
unzip GaussDB_opengauss_client_tools.zip
cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64
cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt
cd /opt/
tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r

数据类型

TINYINT

CREATE TABLE int_type_t1
(
IT_COL1 TINYINT
) ;

INSERT INTO int_type_t1 VALUES(10);

SELECT * FROM int_type_t1;

DECIMAL

CREATE TABLE decimal_type_t1
(
DT_COL1 DECIMAL(10,4)
);

INSERT INTO decimal_type_t1 VALUES(123456.122331);

SELECT * FROM decimal_type_t1;

SMALLSERIAL

CREATE TABLE smallserial_type_tab(a SMALLSERIAL);

INSERT INTO smallserial_type_tab VALUES(default);
INSERT INTO smallserial_type_tab VALUES(default);

SELECT * FROM smallserial_type_tab;

FLOAT

CREATE TABLE float_type_t2
(
FT_COL1 INTEGER,
FT_COL2 FLOAT4,
FT_COL3 FLOAT8,
FT_COL4 FLOAT(3),
FT_COL5 BINARY_DOUBLE,
FT_COL6 DECIMAL(10,4),
FT_COL7 INTEGER(6,3)
) ;

INSERT INTO float_type_t2 VALUES(10,10.365456,123456.1234,10.3214, 321.321, 123.123654,123.123654);

SELECT * FROM float_type_t2 ;

字符类型

CREATE TABLE char_type_t1
(
CT_COL1 CHARACTER(4)
) ;

INSERT INTO char_type_t1 VALUES ('ok');

SELECT ct_col1, char_length(ct_col1) FROM char_type_t1;

日期/时间类型

CREATE TABLE date_type_tab(coll date);

INSERT INTO date_type_tab VALUES (date '12-10-2010');

SELECT * FROM date_type_tab;

二进制类型

CREATE TABLE blob_type_t1
(
BT_COL1 INTEGER,
BT_COL2 BLOB,
BT_COL3 RAW,
BT_COL4 BYTEA
) ;

INSERT INTO blob_type_t1 VALUES(10,empty_blob(), HEXTORAW('DEADBEEF'), E'\\xDEADBEEF');

SELECT * FROM blob_type_t1;

数据库对象

表

-- 创建表
CREATE TABLE bank_card( b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20),b_c_id INT NOT NULL);
-- 创建临时表
CREATE TEMPORARY TABLE bank_card2(b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20),b_c_id INT NOT NULL);
-- 重命名
ALTER TABLE bank_card RENAME TO bank_card1;
-- 增加列
ALTER TABLE bank_card1 ADD full_masks INTEGER;
-- 添加约束
ALTER TABLE bank_card1 ADD CONSTRAINT ck_bank_card CHECK(b_c_id>0);
ALTER TABLE bank_card1 ADD CONSTRAINT uk_bank_card UNIQUE(full_masks);
-- 删除列
ALTER TABLE bank_card1 DROP full_masks;
-- 插入数据
INSERT INTO bank_card1(b_number, b_type, b_c_id) VALUES ('6222021302020000001','Credit Card', 0);
-- 删除表
DROP TABLE IF EXISTS bank_card1;

视图

-- 创建视图
CREATE TABLE bank_card( b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20),b_c_id INT NOT NULL);
CREATE VIEW v_bank_card as select b_number, b_c_id from bank_card;

SELECT * FROM v_bank_card;

ALTER VIEW v_bank_card RENAME TO v_bank_card_new;

DROP VIEW v_bank_card_new;

索引

CREATE INDEX idx_b_type ON bank_card (b_type);

\di idx_b_type

ALTER INDEX idx_b_type RENAME TO idx_b_type_new;

DROP INDEX idx_b_type_new;

函数与操作符

字符处理函数

SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 );

SELECT overlay('hello' placing 'world' FROM 2 for 3 );

SELECT position('ing' in 'string');

SELECT substring_inner('adcde', 2,3);

SELECT replace('abcdefabcdef', 'cd', 'XXX');

SELECT substring('Thomas' FROM 2 for 3);

数字操作函数和操作符

![](C:\Users\zhaoy\Documents\WeChat Files\wxid_5bbqic72kdb912\FileStorage\Temp\1746429723615.png)

SELECT 2+3,2*3, @ -5.0, 2.0^3.0, |/ 25.0, 91&15, 17#5,1<<4 AS RESULT;

SELECT abs(-10),cos(0),sin(0),acos(1),asin(0);

-- 计算两个数字与运算（&）的结果
SELECT bitand(29,15);

SELECT round(1234.5678,-2),round(1234.5678,2);

SELECT date '2021-5-28' + integer '7' AS RESULT;

SELECT date '2021-05-28' + interval '1 hour' AS RESULT;

SELECT date '2021-05-28' + time '03:00' AS RESULT;

SELECT interval '1 day' + interval '1 hour' AS RESULT;

SELECT date '2021-05-01' - date '2021-04-28' AS RESULT;

SELECT date '2021-05-01' - integer '7' AS RESULT;

SELECT date '2021-05-28' - interval '1 hour' AS RESULT;

SELECT time '05:00' - interval '2 hours' AS RESULT;

-- 两个参数相减，并以年月日为返回值
SELECT age(timestamp '2001-04-10', timestamp '1957-06-13');

SELECT current_time;

SELECT to_char(125,'999'),to_clob('hello111'::CHAR(15)),to_date('05 Dec 2000', 'DD Mon YYYY'), to_number('12,454.8-', '99G999D9S');

SELECT cast('22-oct-1997' as timestamp);

SELECT hextoraw('7D');

数据的增删改查

-- 删除表client
DROP TABLE IF EXISTS client;

-- 创建表client
CREATE TABLE client
(
        c_id INT PRIMARY KEY,
        c_name NVARCHAR2(100) NOT NULL,
        c_mail NCHAR(30) UNIQUE,
        c_id_card NCHAR(20) UNIQUE NOT NULL,
        c_phone NCHAR(20) UNIQUE NOT NULL,
        c_password NCHAR(20) NOT NULL
);

-- 删除表bank_card
DROP TABLE IF EXISTS bank_card;


-- 创建表bank_card
CREATE TABLE bank_card
(
        b_number NCHAR(30) PRIMARY KEY,
        b_type NCHAR(20),
        b_c_id INT NOT NULL
);

\q

vi client.sql

-- 按i进入INSERT模式
INSERT INTO client(c_id, c_name, c_mail, c_id_card, c_phone, c_password) VALUES(1,'Zhang Yi','[email protected]', '340211199301010001', '18815650001','gaussdb_001');
INSERT INTO client(c_id, c_name, c_mail, c_id_card, c_phone, c_password) VALUES(2,'Zhang Er','[email protected]', '340211199301010002', '18815650002','gaussdb_002');
INSERT INTO client(c_id, c_name, c_mail, c_id_card, c_phone, c_password) VALUES (3,'Zhang San', '[email protected]', '340211199301010003', '18815650003', 'gaussdb_003');
INSERT INTO client(c_id, c_name, c_mail, c_id_card, c_phone, c_password) VALUES (4,'Zhang Si', '[email protected]', '340211199301010004', '18815650004', 'gaussdb_004');

vi bank_card.sql

INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Health Insurance', 1, 2000,'Old People', 30,'Ping An Insurance');
INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Life Insurance', 2,3000, 'Seniors', 30,'Ping An Insurance');
INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Accident Insurance', 3,5000, 'All', 30,'Ping An Insurance');
INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Medical Insurance', 4, 2000,'All', 30,'Ping An Insurance');
INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Loss of Property Insurance', 5, 1500, 'Middle-aged', 30,'Ping An Insurance');


gsql -h xxx.xxx.xxx.xxx -U root -d postgres -p 8000 -r

-- 执行脚本
\i client.sql
\i bank_card.sql
\i insurance.sql

SELECT * FROM bank_card;

SELECT b_number,b_type FROM bank_card;

SELECT a.c_id,a.c_name, a.c_mail, b.b_number FROM client a, bank_card b where a.c_id= 1 and b.b_c_id = 1;

SELECT b_c_id AS CardID, b_type CardType FROM bank_card;

SELECT a.c_id CID ,a.c_name Name, a.c_mail Email, b.b_number CardNumber FROM client a, bank_card b where a.c_id= 1 and b.b_c_id = 1;

SELECT * FROM bank_card WHERE b_type= 'Credit Card';

SELECT * FROM bank_card where b_c_id= 1and b_type='Credit Card';

SELECT c.c_id, b.b_number, b.b_type FROM client c JOIN bank_card b ON (b.b_c_id = c.c_id);

SELECT c.c_id,c.c_name, b.b_number,b.b_type FROM client c left join bank_card b on c.c_id=b.b_c_id;

SELECT c.c_id,c.c_name, b.b_number,b.b_type FROM client c right join bank_card b on c.c_id =b.b_c_id;

SELECT c.c_id,c.c_name, b.b_number,b.b_type FROM client c FULL JOIN bank_card b ON (b.b_c_id = c.c_id);

SELECT i_name,i_amount,i_person FROM insurance WHERE i_id>2 ORDER BY i_amount DESC;

SELECT i_name, i_id, i_amount, i_person FROM insurance LIMIT 2 OFFSET 1;

INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000030','Savings Card', 30);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000031','Savings Card', 31);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000032','Savings Card', 32);

CREATE TABLE bank_card1(b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20), b_c_id INT NOT NULL);
INSERT INTO bank_card1 SELECT * FROM bank_card;

INSERT INTO bank_card VALUES ('6222021302020000001', 'Credit Card', 1) ON DUPLICATE KEY UPDATE b_type = 'Savings Card';

SELECT * FROM bank_card;

UPDATE bank_card SET bank_card.b_type = 'Credit Card' where b_c_id=1;

SELECT * FROM bank_card;

DELETE FROM bank_card WHERE b_type='Credit Card' AND b_c_id=1;

SELECT * FROM bank_card;

DELETE FROM bank_card;

SELECT * FROM bank_card;

Java对数据库基本操作

购买GaussDB实例，在DAS上完成创建用户及相应数据库和模式操作

安装JDK

ssh [email protected]

mkdir /root/db-dev-cert
cd /root/db-dev-cert
mkdir libs
mkdir -p src/expt/db/basic

cd /root/db-dev-cert/libs
wget https://repo.huaweicloud.com/repository/maven/com/huaweicloud/gaussdb/opengaussjdbc/503.2.T35/opengaussjdbc-503.2.T35.jar

cd /root
wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com:443/20220525/OpenJDK11U-jdk_x64_linux_openj9_linuxXL_11.0.10_9_openj9-0.24.0.tar.gz

tar -xzvf OpenJDK11U-jdk_x64_linux_openj9_linuxXL_11.0.10_9_openj9-0.24.0.tar.gz
mv jdk-11.0.10+9 /usr/lib/
ln -s /usr/lib/jdk-11.0.10+9/bin/java /usr/local/bin/java
ln -s /usr/lib/jdk-11.0.10+9/bin/javac /usr/local/bin/javac
java -version
javac -version

配置数据库配置文件

mkdir /root/db-dev-cert/config
cd /root/db-dev-cert/config
vim dbconfig.properties

```
jdbc_driver=com.huawei.opengauss.jdbc.Driver
db_url=jdbc:opengauss://192.168.0.71:8000/db_test
db_user=db_dev
db_password=XXXXXX
currentschema=schema_test
```

连接数据库

1
2
3

cd /root/db-dev-cert/src/expt/db/basic
touch dbUtils.java
vi dbUtils.java

package expt.db.basic;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;

public class dbUtils {
    public static Connection getConnect(String driver, String db_conn_url) {
        Connection conn = null;

        try {
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        // open connecting
        System.out.println("connecting database...");
        try {
            System.out.println("connection url is: " + db_conn_url);
            conn = DriverManager.getConnection(db_conn_url);
            System.out.println("connection successfully?");
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void closeConnect(Connection conn) {
        System.out.println("colsing connection...");
        try {
            conn.close();
            System.out.println("connection closed?");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static Properties loadProperties() {
		InputStream input = null;
		 Properties prop = new Properties();
		try {
			input = new FileInputStream("/root/db-dev-cert/config/dbconfig.properties");
			// ??????
			prop.load(input);
		} catch (IOException ex) {
			ex.printStackTrace();
		} finally {
			if (input != null) {
				try {
					input.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return prop;
	}
    public static void printAllRecords(ResultSet rs) {
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                System.out.print(metaData.getColumnName(i + 1) + "\t");
            }
            System.out.println();

            while (rs.next()) {
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    System.out.print(rs.getString(i + 1) + "\t");
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void printOneRecord(ResultSet rs) {
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                System.out.print(metaData.getColumnName(i + 1) + "\t");
            }
            System.out.println();

            for (int i = 0; i < metaData.getColumnCount(); i++) {
                System.out.print(rs.getString(i + 1) + "\t");
            }
            System.out.println("\ncurrent row number: " + rs.getRow() + "\n");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

1
2
3

cd /root/db-dev-cert/src/expt/db/basic
touch exptConnection.java
vi exptConnection.java

package expt.db.basic;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

public class exptConnection {
 
	
	public static void main(String[] args) {
		getConnect();
	}

	public static Connection getConnect() {

Properties prop = dbUtils.loadProperties();
		Connection conn = null;
		String jdbcdriver = prop.getProperty("jdbc_driver");
		String dburl = prop.getProperty("db_url");
		String user = prop.getProperty("db_user");
		String password = prop.getProperty("db_password");
         String connection_url = dburl + "?user=" + user + "&password=" + password; 
		System.out.println("connection_url: " + connection_url);
		try {
			Class.forName(jdbcdriver);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		System.out.println("connecting database...");
		try {
			conn = DriverManager.getConnection(connection_url);
			System.out.println("connection successfully?");
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}

javac -classpath ../../../ -d . exptConnection.java
yum -y install tree
tree
java -p /root/db-dev-cert/libs/opengaussjdbc-503.2.T35.jar expt.db.basic.exptConnection

创建数据库

1
2
3

cd /root/db-dev-cert/src/expt/db/basic
touch exptCreateTable.java
vi exptCreateTable.java

package expt.db.basic;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class exptCreateTable {
	public static void main(String[] args) throws SQLException {

		Properties prop = dbUtils.loadProperties();

		String jdbcdriver = prop.getProperty("jdbc_driver");
		String dburl = prop.getProperty("db_url");
		String user = prop.getProperty("db_user");
		String password = prop.getProperty("db_password");

		String conn_url = dburl + "?user=" + user + "&password=" + password + "&currentSchema=schema_test";
		Connection conn = dbUtils.getConnect(jdbcdriver, conn_url);

		Statement statement = conn.createStatement();
		statement.execute(
				"create table test_table (id int, name varchar(10), destination varchar(20), uuid varchar(36))");
		System.out.println("execute successfully!");
		dbUtils.closeConnect(conn);
	}
}

1 2	javac -classpath ../../../ -d . exptCreateTable.java java -p /root/db-dev-cert/libs/opengaussjdbc-503.2.T35.jar expt.db.basic.exptCreateTable

增删改查数据库

1
2
3

cd /root/db-dev-cert/src/expt/db/basic
touch ExpDataOperation.java
vi ExpDataOperation.java

package expt.db.basic;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ExpDataOperation {
 
		static Properties prop = new Properties();
		static void loadProperties() {
			InputStream input = null;
			try {
				input = new FileInputStream("/root/db-dev-cert/config/dbconfig.properties");

				prop.load(input);
			} catch (IOException ex) {
				ex.printStackTrace();
			} finally {
				if (input != null) {
					try {
						input.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
		}	    
	    public static void main(String[] args) throws SQLException {
	    	

			loadProperties();

			String jdbcdriver = prop.getProperty("jdbc_driver");
			String dburl = prop.getProperty("db_url");
			String user = prop.getProperty("db_user");
			String password = prop.getProperty("db_password");

			String conn_url = dburl + "?user=" + user + "&password=" + password + "&currentSchema=schema_test";
			System.out.println("the connection url:" + conn_url);
			Connection conn = dbUtils.getConnect(jdbcdriver, conn_url);

	        Statement statement = conn.createStatement();
	        statement.execute("INSERT INTO test_table(id, name, destination, uuid) "
	                + "values (2, 'zhangsan', 'hangzhou', 123456789)");
	        System.out.println(statement.getUpdateCount());
	        

	        ResultSet resultSet = null;
	        PreparedStatement queryStatement=conn.prepareStatement("select * from test_table where id=?;");
	        queryStatement.setObject(1,2);
	        resultSet = queryStatement.executeQuery();
	        dbUtils.printAllRecords(resultSet);


	        PreparedStatement updateStatement=conn.prepareStatement("update test_table set name=? where id=?");
	        updateStatement.setObject(1, "wangwu");
	        updateStatement.setObject(2, 2);
	        updateStatement.execute();
	        System.out.println(updateStatement.getUpdateCount());


	        PreparedStatement deleteStatement = conn.prepareStatement("delete from test_table where id = ?");
	        deleteStatement.setObject(1, 2);
	        deleteStatement.execute();
	        System.out.println(deleteStatement.getUpdateCount());

	        dbUtils.closeConnect(conn);
	    }

}

1 2	javac -classpath ../../../ -d . ExpDataOperation.java java -p /root/db-dev-cert/libs/opengaussjdbc-503.2.T35.jar expt.db.basic.ExpDataOperation

批量插入数据

1
2
3

cd /root/db-dev-cert/src/expt/db/basic
touch exptBatchInsert.java
vi exptBatchInsert.java

package expt.db.basic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.util.UUID;

public class exptBatchInsert {
    static String JDBC_DRIVER = "";
    static String conn_url = "";
    
    public static void main(String[] args) throws SQLException {
        int current;

        Properties prop = dbUtils.loadProperties();

		JDBC_DRIVER = prop.getProperty("jdbc_driver");
		String dburl = prop.getProperty("db_url");
		String user = prop.getProperty("db_user");
		String password = prop.getProperty("db_password");
		String currentSchema = prop.getProperty("currentschema", "schema_test") ;
		conn_url = dburl + "?user=" + user + "&password=" + password + "&currentSchema=" + currentSchema;
		System.out.println("the connection url:" + conn_url);
		 
        current = insertRecordOnceATime(1, 1000);
        insertRecordBatch(current, 1000);
    }

    public static int insertRecordOnceATime(int begin, int count) {
        PreparedStatement preparedStatement;
        int index = begin;

        try {
            Connection conn = dbUtils.getConnect(JDBC_DRIVER, conn_url);
            conn.setAutoCommit(true);

            String targetQuery = "INSERT INTO test_table(id, name, destination, uuid) VALUES(?, ?, ?, ?)";
            preparedStatement = conn.prepareStatement(targetQuery);
            long start = System.currentTimeMillis();

            for( ; index < begin+count; index++) {
                preparedStatement.setInt(1, index);
                preparedStatement.setString(2, "name-"+index);
                preparedStatement.setString(3, "destination-"+index);
                preparedStatement.setString(4, UUID.randomUUID().toString());
                long startInternal = System.currentTimeMillis();
                preparedStatement.executeUpdate();
                System.out.println("each transaction time taken = " + (System.currentTimeMillis() - startInternal) + " ms");
            }

            long end = System.currentTimeMillis();
            System.out.println("total time taken = " + (end - start) + " ms");
            System.out.println("avg total time taken = " + (end - start)/ count + " ms");

            preparedStatement.close();
            dbUtils.closeConnect(conn);

        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return index;
    }

    public static void insertRecordBatch(int begin, int count) {
        PreparedStatement preparedStatement;
        int index = begin;

        try {
            Connection conn = dbUtils.getConnect(JDBC_DRIVER, conn_url);
            conn.setAutoCommit(true);

            String targetQuery = "INSERT INTO test_table(id, name, destination, uuid) VALUES(?, ?, ?, ?)";
            preparedStatement = conn.prepareStatement(targetQuery);

            for( ; index < begin+count; index++) {
                preparedStatement.setInt(1, index);
                preparedStatement.setString(2, "name-"+index);
                preparedStatement.setString(3, "destination-"+index);
                preparedStatement.setString(4, UUID.randomUUID().toString());
                preparedStatement.addBatch();
            }

            long start = System.currentTimeMillis();
            int[] inserted = preparedStatement.executeBatch();
            long end = System.currentTimeMillis();

            System.out.println("total time taken to insert the batch = " + (end - start) + " ms");
            System.out.println("total time taken = " + (end - start)/count + " s");
            preparedStatement.close();
            dbUtils.closeConnect(conn);
            System.out.println("row influence number is: " + inserted.length);
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
            throw new RuntimeException("Error");
        }
    }
}

1 2	javac -classpath ../../../ -d . exptBatchInsert.java java -p /root/db-dev-cert/libs/opengaussjdbc-503.2.T35.jar expt.db.basic.exptBatchInsert

添加游标数据

1
2
3

cd /root/db-dev-cert/src/expt/db/basic
touch exptCursor.java
vi exptCursor.java

package expt.db.basic;

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

public class exptCursor {
 
    public static void main(String[] args) throws SQLException {
        PreparedStatement preparedStatement;

        try {

    		Properties prop = dbUtils.loadProperties();
   
    		String jdbcdriver = prop.getProperty("jdbc_driver");
    		String dburl = prop.getProperty("db_url");
    		String user = prop.getProperty("db_user");
    		String password = prop.getProperty("db_password");
    		String conn_url = dburl + "?user=" + user + "&password=" + password + "&currentSchema=schema_test";
    		System.out.println("the connection url:" + conn_url);        	
            Connection conn = dbUtils.getConnect(jdbcdriver, conn_url);

            String targetQuery = "select * from test_table";
            preparedStatement = conn.prepareStatement(targetQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet resultSet = preparedStatement.executeQuery();

            resultSet.next();
            dbUtils.printOneRecord(resultSet);

            resultSet.last();
            dbUtils.printOneRecord(resultSet);

            resultSet.previous();
            dbUtils.printOneRecord(resultSet);

            resultSet.first();
            dbUtils.printOneRecord(resultSet);

            System.out.println("is before first: " + resultSet.isBeforeFirst());
            resultSet.afterLast();
            System.out.println("is after last: " + resultSet.isAfterLast());
            resultSet.beforeFirst();
            System.out.println("is before first: " + resultSet.isBeforeFirst());
            resultSet.next();
            dbUtils.printOneRecord(resultSet);

            resultSet.absolute(4);
            dbUtils.printOneRecord(resultSet);
            resultSet.relative(1);
            dbUtils.printOneRecord(resultSet);

            resultSet.absolute(-2);
            dbUtils.printOneRecord(resultSet);
            resultSet.relative(-1);
            dbUtils.printOneRecord(resultSet);

            preparedStatement.close();
            dbUtils.closeConnect(conn);

        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
    }
}

1 2	javac -classpath ../../../ -d . exptCursor.java java -p /root/db-dev-cert/libs/opengaussjdbc-503.2.T35.jar expt.db.basic.exptCursor

分页查询语句

1
2
3

cd /root/db-dev-cert/src/expt/db/basic
touch exptPagination.java
vi exptPagination.java

package expt.db.basic;

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

public class exptPagination {
 
	public static void main(String[] args) throws SQLException {
		int pageNumber = 1; // from 1 to N
		int pageSize = 3;

		Properties prop = dbUtils.loadProperties();
	
		String jdbcdriver = prop.getProperty("jdbc_driver");
		String dburl = prop.getProperty("db_url");
		String user = prop.getProperty("db_user");
		String password = prop.getProperty("db_password");
		String conn_url = dburl + "?user=" + user + "&password=" + password + "&currentSchema=schema_test";
		System.out.println("the connection url:" + conn_url);   		
		Connection conn = dbUtils.getConnect(jdbcdriver, conn_url);
		if (args.length == 2) {
			pageNumber = Integer.parseInt(args[0]);
			pageSize = Integer.parseInt(args[1]);
		}

		pageQueryByCursor(conn, pageNumber, pageSize);
		dbUtils.closeConnect(conn);
	}

	public static void pageQueryByCursor(Connection conn, int pageNumber, int pageSize) {
		PreparedStatement preparedStatement;

		int startNo = (pageNumber - 1) * pageSize;
		int endNo = pageNumber * pageSize;

		String targetQuery = "select * from test_table";

		try {
			preparedStatement = conn.prepareStatement(targetQuery, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			preparedStatement.setMaxRows(endNo);
			ResultSet resultSet = preparedStatement.executeQuery();

			resultSet.beforeFirst();
			resultSet.relative(startNo);

			dbUtils.printAllRecords(resultSet);
			preparedStatement.close();
		} catch (SQLException ex) {
			System.err.println("SQLException information");
			while (ex != null) {
				System.err.println("Error msg: " + ex.getMessage());
				ex = ex.getNextException();
			}
		}
	}
}

javac -classpath ../../../ -d . exptPagination.java
java -p /root/db-dev-cert/libs/opengaussjdbc-503.2.T35.jar expt.db.basic.exptPagination

// 以每页10条记录，输出第100页的内容
java -p /root/db-dev-cert/libs/opengaussjdbc-503.2.T35.jar expt.db.basic.exptPagination 100 10

通过Mybatis连接数据库

准备数据

CREATE TABLE goods (
     userid INTEGER PRIMARY KEY,
	goodsid INTEGER,
	deliveryTime date,
 	title VARCHAR(100),
 	description VARCHAR(100),
 	category VARCHAR(20),
    originalPrice FLOAT(3),
	price FLOAT(3)
) ;

INSERT INTO goods(goodsid, userid, deliveryTime, title, description, category, originalPrice, price)
values (1,1,'2024-06-21','手机','华为手机','电子产品',5000,6000) ;
INSERT INTO goods(goodsid, userid, deliveryTime, title, description, category, originalPrice, price)
values (2,2,'2024-06-21','手机','小米手机','电子产品',1000,2000) ;
INSERT INTO goods(goodsid, userid, deliveryTime, title, description, category, originalPrice, price)
values (3,3,'2024-06-22','热水器','美的热水器','电器',3000,4000) ;
INSERT INTO goods(goodsid, userid, deliveryTime, title, description, category, originalPrice, price)
values (4,4,'2024-06-26','苹果','红苹果','水果',5,10) ;
INSERT INTO goods(goodsid, userid, deliveryTime, title, description, category, originalPrice, price)
values (5,5,'2024-06-28','桃子','水蜜桃','水果',10,15) ;
INSERT INTO goods(goodsid, userid, deliveryTime, title, description, category, originalPrice, price)
values (6,6,'2024-06-29','杯子','水杯','日用品',15,30);
INSERT INTO goods(goodsid, userid, deliveryTime, title, description, category, originalPrice, price)
values (7,7,'2024-06-30','空调','格力空调','电器',3000,5000);

安装Maven

mkdir -p /root/db-dev-cert/maven
cd /root/db-dev-cert/maven
wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com:443/hccdp/HCCDP/apache-maven-3.6.0-bin.tar.gz
tar -zxvf apache-maven-3.6.0-bin.tar.gz
ln -s /root/db-dev-cert/maven/apache-maven-3.6.0/bin/mvn /usr/local/bin/mvn

// 确保安装了JDK
mvn -version

// 修改仓库位置
cd /root/db-dev-cert/maven/apache-maven-3.6.0/conf
vim settings.xml

<localRepository>/root/db-dev-cert/libs</localRepository>

<mirror>
      <id>huaweicloud</id>
      <mirrorOf>*,!HuaweiCloudSDK</mirrorOf>
      <url>https://repo.huaweicloud.com/repository/maven/</url>
</mirror>

<profile>
      <id>MyProfile</id>
      <repositories>
        <repository>
          <id>HuaweiCloudSDK</id>
          <url>https://repo.huaweicloud.com/repository/maven/huaweicloudsdk/</url>
          <releases>
            <enabled>true</enabled>
          </releases>
          <snapshots>
            <enabled>false</enabled>
          </snapshots>
        </repository>
      </repositories>
</profile>

创建接口类GoodsMapper

1
2
3

mkdir -p /root/db-dev-cert/mybatisdemo/src/main/java/com/huawei/guassdb/mapper
cd /root/db-dev-cert/mybatisdemo/src/main/java/com/huawei/guassdb/mapper
vim GoodsMapper.java

package com.huawei.guassdb.mapper;
import java.util.List;
import com.huawei.guassdb.pojo.Goods;
public interface GoodsMapper {
	Goods selectGoodsByUserId(int goodsid);
	List<Goods> getAllGoods();
}

创建Goods_Mapper.xml文件

1 2	cd /root/db-dev-cert/mybatisdemo/src/main/java/com/huawei/guassdb/mapper vim Goods_Mapper.xml

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.huawei.guassdb.mapper.GoodsMapper">
    <select id="selectGoodsByUserId" resultType="com.huawei.guassdb.pojo.Goods" parameterType="int">
        select  * FROM goods WHERE userid = #{userid}
    </select>
    <select id="getAllGoods" resultType="com.huawei.guassdb.pojo.Goods" >
        select  * FROM goods 
    </select>
</mapper>

创建Goods类

1
2
3

mkdir /root/db-dev-cert/mybatisdemo/src/main/java/com/huawei/guassdb/pojo
cd /root/db-dev-cert/mybatisdemo/src/main/java/com/huawei/guassdb/pojo
vim Goods.java

package com.huawei.guassdb.pojo;
public class Goods {
	private long goodsid;
	private long userid;
	private String title;
	private String deliveryTime;
	private String description;
	private String category ;
	private float originalPrice;
	private float price;
	public long getGoodsid() {
		return goodsid;
	}
	public void setGoodsid(long goodsid) {
		this.goodsid = goodsid;
	}

	public long getUserid() {
		return userid;
	}
	public void setUserid(long userid) {
		this.userid = userid;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public float getOriginalPrice() {
		return originalPrice;
	}
	public void setOriginalPrice(float originalPrice) {
		this.originalPrice = originalPrice;
	}
	public float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	public long getGoodsId() {
		return goodsid;
	}
	public void setGoodsId(long goodsid) {
		this.goodsid = goodsid;
	}	
	public void setTitle(String title) {
		this.title = title;
	}
	public String getTitle() {
		return this.title;
	}
	public String getDeliveryTime() {
		return deliveryTime;
	}

	public void setDeliveryTime(String deliveryTime) {
		this.deliveryTime = deliveryTime;
	}
	@Override
	public String toString() {
		return "Goods{" + "????=" + userid + ", ??='" + title + '\'' +", ???='" + goodsid + '\'' +
		", ????='" + description + "\', ????='" + category +  '\'' +
		", ????='" + originalPrice + "\', ????='" + price +  '\'' +
		"}";
	}
}

创建配置文件mybatis\mybatis-config.xml

1
2
3

mkdir /root/db-dev-cert/mybatisdemo/src/main/java/mybatis
cd /root/db-dev-cert/mybatisdemo/src/main/java/mybatis
vim mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.huawei.opengauss.jdbc.Driver"/>
                <property name="url" value="jdbc:opengauss://192.168.0.168:8000/db_test?currentSchema=schema_test"/>
                <property name="username" value="db_dev"/>
                <property name="password" value="XXXXXX"/>
            </dataSource>
        </environment>
    </environments>	
    <mappers>
        <mapper resource="com/huawei/guassdb/mapper/Goods_Mapper.xml"/>
    </mappers>
</configuration>

创建MyBatisUtil.java文件

1
2
3

mkdir /root/db-dev-cert/mybatisdemo/src/main/java/com/huawei/guassdb/util
cd /root/db-dev-cert/mybatisdemo/src/main/java/com/huawei/guassdb/util
vim MyBatisUtil.java

package com.huawei.guassdb.util;
import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisUtil {

	private static SqlSessionFactory sqlSessionFactory;
	 
    static {
        try {
            String resource = "mybatis/mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

创建TestMyBatis类

1 2	cd /root/db-dev-cert/mybatisdemo/src/main/java/com/huawei/guassdb/util vim TestMyBatis.java

package com.huawei.guassdb.util;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.huawei.guassdb.mapper.GoodsMapper;
import com.huawei.guassdb.pojo.Goods;

public class TestMyBatis {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        try {
        	System.out.println("****************************");
        	GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
            Goods goods = mapper.selectGoodsByUserId(1);
            System.out.println(goods);
            System.out.println("********************************");
            List<Goods> allgoods = mapper.getAllGoods();
            for (int i = 0; i < allgoods.size(); i ++ ) {
            	System.out.println(allgoods.get(i));
            }
            System.out.println("************************************");
            System.out.println("----finished successfully!----");
            
        } finally {
            sqlSession.close();
        }
        System.exit(0);
    }
}

编译构建并运行程序

创建pom.xml

1 2	cd /root/db-dev-cert/mybatisdemo/ vim pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
 
    <groupId>com.huawei.gaussdb</groupId>
    <artifactId>gaussdb-project</artifactId>
    <version>1.0</version>
 
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
 
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
             <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>	
       <dependency>
             <groupId>com.huaweicloud.gaussdb</groupId> 
             <artifactId>opengaussjdbc</artifactId>
             <version>503.2.T35</version>
       </dependency>	
    </dependencies>
    
 <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build> 
</project>

mvn编译jar文件

cd /root/db-dev-cert/mybatisdemo/
mvn clean install

cd /root/db-dev-cert/
find . -type f -name gaussdb-project-1.0.jar

cd /root/db-dev-cert/
find . -type f -name mybatis-3.5.9.jar
find . -type f -name opengaussjdbc-503.2.T35.jar

cd /root/db-dev-cert/mybatisdemo
java -classpath .:/root/db-dev-cert/mybatisdemo/target/gaussdb-project-1.0.jar:/root/db-dev-cert/libs/org/mybatis/mybatis/3.5.9/mybatis-3.5.9.jar:/root/db-dev-cert/libs/com/huaweicloud/gaussdb/opengaussjdbc/503.2.T35/opengaussjdbc-503.2.T35.jar com.huawei.guassdb.util.TestMyBatis

数据库管理与运维实验

前置工作

ssh [email protected]

wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1642684986086/GaussDB_opengauss_client_tools.zip
unzip GaussDB_opengauss_client_tools.zip
cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64
cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt
cd /opt/
tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r
CREATE DATABASE devdb ENCODING 'UTF8' template = template0;
\q

gsql -h xxx.xx.xx.x -d devdb -p 8000 -U root -W yourpassword -r
CREATE USER hccda SYSADMIN IDENTIFIED BY "setyourpassword";
\q

gsql -h xxx.xx.xx.x -d devdb -p 8000 -U hccda -W yourpassword -r
\q

数据库对象访问权限设置

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r
CREATE USER joe PASSWORD 'yourpassword';
ALTER USER joe with sysadmin;

// 查看用户权限变化
\du

ALTER USER joe with nosysadmin;
CREATE SCHEMA tpcds;
 CREATE TABLE tpcds.reason
(
    r_reason_sk            INTEGER               NOT NULL,
    r_reason_id            CHAR(16)              NOT NULL,
    r_reason_desc                VARCHAR(20)                  
);

新起会话

ssh [email protected]
cd /opt/
source gsql_env.sh

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U joe -W yourpassword -r

// 验证joe用户是否拥有tpcds模式下reason表的权限
select * from tpcds.reason;

返回原来的会话

1 2	GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe; GRANT create,connect on database postgres TO joe WITH GRANT OPTION;

切换至新会话

1
2
3

-- 验证权限
SELECT r_reason_sk,r_reason_id,r_reason_desc FROM tpcds.reason;
UPDATE tpcds.reason SET r_reason_desc='test';

返回原来的会话

CREATE ROLE tpcds_manager PASSWORD 'yourpassword';
\du
GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;

CREATE ROLE manager PASSWORD 'yourpassword';
GRANT joe TO manager WITH ADMIN OPTION;

CREATE USER senior_manager PASSWORD 'yourpassword';
GRANT manager TO senior_manager;

数据导入导出

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

INSERT INTO test_table (name, age) VALUES
    ('Alice', 25),
    ('Bob', 30),
    ('Charlie', 28);

1 2	\COPY test_table TO '/tmp/test_table_export.csv' CSV HEADER; cat /tmp/test_table_export.csv

-- 清空测试表数据
TRUNCATE TABLE test_table;
\COPY test_table FROM '/tmp/test_table_export.csv' CSV HEADER;
SELECT * FROM test_table;

系统表、系统视图查询

1
2
3

cd /opt/
source gsql_env.sh
gsql -h xxx.xxx.xx.xxx -d postgres -p 8000 -U root -W password -r

SELECT * FROM pg_database;
SELECT * FROM pg_user;
\c - joe;
SELECT * FROM DB_ALL_TABLES;

金融场景下GaussDB Java编程综合实践

前置工作

ssh [email protected]

wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1642684986086/GaussDB_opengauss_client_tools.zip
unzip GaussDB_opengauss_client_tools.zip
cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64
cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt
cd /opt/
tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r
\conninfo
\q

实验数据模型介绍

E-R图

关系模式

物理模型

对象及字段属性为：

client(c_id，c_name，c_mail，c_id_card，c_phone)
bank_card(b_number，b_type，b_client_id)
financial_product(p_id，p_name，p_description，p_amount，p_year)
financial_asset(a_id, a_client_id，a_product_id，a_type，a_status，a_quantity，a_income，a_purchase_time)

JDBC实验操作

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r
create database finance with encoding ='utf8' ;

// 切换至finance数据库
\c finance

create user db_dev identified by 'yourpassword';
create schema finance authorization db_dev;
\dn finance
\q

// 创建代码根目录
mkdir /root/db-dev-cert
cd /root/db-dev-cert
mkdir libs

cd /root/GaussDB_driver/Centralized/Euler2.5_X86_64/
tar -zxvf GaussDB-Kernel_505.1.0_Euler_64bit_Jdbc.tar.gz
cp gaussdbjdbc.jar /root/db-dev-cert/libs/
ll /root/db-dev-cert/libs/

// 下载JDK
cd /root
wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com:443/20220525/OpenJDK11U-jdk_x64_linux_openj9_linuxXL_11.0.10_9_openj9-0.24.0.tar.gz
tar -xzvf OpenJDK11U-jdk_x64_linux_openj9_linuxXL_11.0.10_9_openj9-0.24.0.tar.gz
mv jdk-11.0.10+9 /usr/lib/
ln -s /usr/lib/jdk-11.0.10+9/bin/java /usr/local/bin/java
ln -s /usr/lib/jdk-11.0.10+9/bin/javac /usr/local/bin/javac
java -version
javac -version

// 创建实验代码结构目录
mkdir -p /root/db-dev-cert/src/expt/db
cd /root/db-dev-cert/src/expt/db
mkdir -p finance/dao
mkdir -p finance/resources
ll  /root/db-dev-cert/src/expt/db/finance/

// 数据库连接
cd /root/db-dev-cert/src/expt/db/finance/resources
vim config-db.properties

---
JDBC_DRIVER= com.huawei.gaussdb.jdbc.Driver
DB_URL=jdbc:gaussdb://xxx.xxx.xx.xx:8000/finance
SCHEMA=finance
USER=db_dev
PASSWORD=yourpassword
---

cd /root/db-dev-cert/src/expt/db/finance
vim Const.java

---
package expt.db.finance;

public class Const {
    public static final String PREFIX_LEVEL_FIR = " => ";
    public static final String PREFIX_LEVEL_SEC = " ==> ";

    public static final Boolean SUCCEED = true;
    public static final Boolean FAILED = false;

    public static final int INVALID = -1;
}
---

cd /root/db-dev-cert/src/expt/db/finance/dao
vim DBUtils.java

---
package expt.db.finance.dao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import expt.db.finance.Const;

public class DBUtils {

    public static Connection getConnect() {
        Connection conn = null;

        System.out.println("connecting database...");
        try {
            InputStream inputStream = DBUtils.class.getClassLoader().
                    getResourceAsStream("expt/db/finance/resources/config-db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            String url = properties.getProperty("DB_URL");
            String user = properties.getProperty("USER");
            String password = properties.getProperty("PASSWORD");
            String driverClass = properties.getProperty("JDBC_DRIVER");
            String schema = properties.getProperty("SCHEMA");

            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, user, password);
            conn.setSchema(schema);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        System.out.println("connection successfully?");
        return conn;
    }

    public static void closeConnect(Connection conn) {
        System.out.println("colsing connection...");
        try {
            conn.close();
            System.out.println("connection closed?");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void printAllRecords(ResultSet rs) {
        try {
            if (rs == null || rs.isBeforeFirst() == false) {
                System.out.println(Const.PREFIX_LEVEL_SEC + "未查询到相关数据！");
                return;
            }
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                System.out.print(metaData.getColumnName(i + 1) + "\t");
            }
            System.out.println();

            while (rs.next()) {
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    System.out.print(rs.getString(i + 1) + "\t");
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void printOneRecord(ResultSet rs) {
        try {
            if (rs == null || rs.isBeforeFirst() == false) {
                System.out.println(Const.PREFIX_LEVEL_SEC + "未查询到相关数据！");
                return;
            }

            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                System.out.print(metaData.getColumnName(i + 1) + "\t");
            }
            System.out.println();

            rs.next();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                System.out.print(rs.getString(i + 1) + "\t");
            }
            System.out.println();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
---

// 数据表初始化
cd /root/db-dev-cert/src/expt/db/finance
vim initTables.java

---
package expt.db.finance;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import expt.db.finance.dao.DBUtils;

public class initTables {

    public static void main(String[] args) throws SQLException {
        Connection conn = DBUtils.getConnect();
        createTableClient(conn);
        createTableBankCard(conn);
        createTableFinancialProduct(conn);
        createTableFinancialAsset(conn);

        System.out.println("数据表初始化创建完成!");
    }

    public static void executeSql(Connection conn, String targetSql) {
        Statement statement = null;

        try {
            statement = conn.createStatement();
            statement.execute(targetSql);

        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
    }
public static void createTableClient(Connection conn) {
        String execSql = "DROP Table If Exists finance.client;" +
                         "Create Table finance.client " +
                         "(c_id int Primary key," +
                         " c_name varchar(100) not null," +
                         " c_id_card char(20) unique not null," +
                         " c_phone char(20) unique not null," +
                         " c_mail char(30) unique);";

        executeSql(conn, execSql);
        System.out.println("table client created");
    }
    public static void createTableBankCard(Connection conn) {
        String execSql = "DROP Table If Exists finance.bank_card;" +
                         "Create Table finance.bank_card " +
                         "(b_number char(30) Primary key," +
                         " b_type char(20) not null," +
                         " b_client_id int not null);";

        executeSql(conn, execSql);
        System.out.println("table bank_card created");
    }

    public static void createTableFinancialProduct(Connection conn) {
        String execSql = "DROP Table If Exists finance.financial_product;" +
                         "Create Table finance.financial_product " +
                         "(p_id int Primary key," +
                         " p_name varchar(100) not null," +
                         " p_description varchar(1000)," +
                         " p_amount int," +
                         " p_year int);";

        executeSql(conn, execSql);
        System.out.println("table financial_product created");
    }

    public static void createTableFinancialAsset(Connection conn) {
        String execSql = "DROP Table If Exists finance.financial_asset;" +
                         "Create Table finance.financial_asset " +
                         "(a_id int Primary key," +
                         " a_client_id int not null," +
                         " a_product_id int not null," +
                         " a_type int not null," +
                         " a_status char(20)," +
                         " a_quantity int," +
                         " a_income int," +
                         " a_purchase_time Date);";

        executeSql(conn, execSql);
        System.out.println("table financial_asset created");
    }
}
---

// 编译
javac -classpath ../../../ -d . initTables.java

// 运行代码文件
mkdir -p expt/db/finance/resources
cd /root/db-dev-cert/src/expt/db/finance/
cp resources/config-db.properties expt/db/finance/resources/
java -p /root/db-dev-cert/libs/gaussdbjdbc.jar expt.db.finance.initTables

// 数据初始化
cd /root/db-dev-cert/src/expt/db/finance
vi initData.java

---
package expt.db.finance;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import expt.db.finance.dao.DBUtils;

public class initData {

    static String[] dataSqls = new String[]{
            "INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone) "
            + "VALUES (1,'小王', '[email protected]', '340211199301010001', '18815650001');",
             "INSERT INTO finance.bank_card(b_number,b_type,b_client_id) VALUES('6222021302020000001', '信用卡',1);",
            "INSERT INTO finance.financial_product(p_name,p_id,p_description,p_amount,p_year) "
            + "VALUES ('债券',1,' 以国债、金融央行票据企业为主要投资方向的银理财产品',50000,6);",

            
            "INSERT INTO financial_asset(a_id, a_client_id, a_product_id, a_type, a_status, a_quantity, a_income, a_purchase_time) "
            + "VALUES (1, 1, 1, 1, '可用', 4, 8001, '2018-07-01');",
    };

    public static void main(String[] args) throws SQLException {
        Connection conn = DBUtils.getConnect();
        Statement statement = conn.createStatement();

        for(String targetSql: dataSqls) {
            System.out.println(targetSql);
            statement.execute(targetSql);
        }
        System.out.println("complete!");
    }
}
---

javac -classpath ../../../ -d . initData.java
java -p /root/db-dev-cert/libs/gaussdbjdbc.jar expt.db.finance.initData

// DAO层实现
cd /root/db-dev-cert/src/expt/db/finance/dao
vim Client.java

---
package expt.db.finance.dao;

import java.sql.Connection;
import java.sql.SQLException;
import expt.db.finance.Const;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Client {
    private Connection conn;

    public Client(Connection connection) {
        conn = connection;
    }

    public ResultSet queryClientList() {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select c_id as 客户ID, c_name as 姓名, "
                + "c_id_card as , c_phone as 电话号码, c_mail as 电子邮箱地址 "
                + "from client");
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
         return rs;
    }

    public ResultSet queryClientById(int id) {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select c_id as 客户ID, c_name as 姓名, "
                + "c_id_card as , c_phone as 电话号码, c_mail as 电子邮箱地址 "
                + "from client where c_id = ?");
            pstat.setInt(1, id);
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return rs;
    }

    public boolean insertClient(int id, String name, String id_card, String phone_number, String email) {
        PreparedStatement pstat = null;

        try {
            pstat = conn.prepareStatement("INSERT INTO "
+ "client(c_id, c_name, c_id_card, c_phone, c_mail) "
                    + "VALUES (?,?,?,?,?);");
            pstat.setInt(1, id);
            pstat.setString(2, name);
            pstat.setString(3, id_card);
            pstat.setString(4, phone_number);
            pstat.setString(5, email);
            pstat.execute();

            return Const.SUCCEED;
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
            return Const.FAILED;
        }
    }

    public boolean updateClient(int id, String name, String phone_number, String email) {
        PreparedStatement pstat = null;

        try {
            pstat = conn.prepareStatement("UPDATE client set c_name=?, c_phone=?, c_mail=? where c_id=?");
            pstat.setString(1, name);
            pstat.setString(2, phone_number);
            pstat.setString(3, email);
            pstat.setInt(4, id);
            pstat.execute();

            return Const.SUCCEED;
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }

            return Const.FAILED;
        }
    }

    public boolean deleteClient(int id) {
        PreparedStatement pstat = null;

        try {
            pstat = conn.prepareStatement("DELETE from client where c_id = ?");
            pstat.setInt(1, id);
            pstat.execute();

            return true;
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
            return false;
        }
    }
}
---

vim BankCard.java

---
package expt.db.finance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import expt.db.finance.Const;

public class BankCard {
    private Connection conn;

    public BankCard(Connection connection) {
        this.conn = connection;
    }

    public ResultSet queryBankCardList() {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select b_number as ????, b_type as ????, "
                    + "b_client_id as ??ID "
                    + "from bank_card");
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }

        return rs;
    }
    public ResultSet queryBankCardByCardNumber(String bank_card_number) {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select b_number as 银行卡号, b_type as 卡片类型, "
                    + "b_client_id as 客户ID "
                    + "from bank_card where b_number = ?");
            pstat.setString(1, bank_card_number);
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return rs;
    }

    public ResultSet queryBankCardByClientId(int clientId) {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select b_number as 银行卡号, b_type as 卡片类型, "
                    + "b_client_id as 客户ID "
                    + "from bank_card where b_client_id = ?");
            pstat.setInt(1, clientId);
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return rs;
    }
    public int countOfBankCards(int clientId) {
        PreparedStatement pstat = null;
        ResultSet rs = null;
        int recordsCount = Const.INVALID;

        try {
            pstat = conn.prepareStatement("select count(*) as RECORDSCOUNT from bank_card where b_client_id = ?");
            pstat.setInt(1, clientId);
            rs = pstat.executeQuery();

            if (rs.next()) {
                recordsCount = rs.getInt("RECORDSCOUNT");
            }
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return recordsCount;
    }

    public boolean insertBankCard(String card_number, String card_type, int client_id) {
        PreparedStatement pstat = null;

        try {
            pstat = conn.prepareStatement("INSERT INTO bank_card(b_number, b_type, b_client_id) "
                    + "VALUES (?,?,?);");
            pstat.setString(1, card_number);
            pstat.setString(2, card_type);
            pstat.setInt(3, client_id);
            pstat.execute();

            return Const.SUCCEED;
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }

            return Const.FAILED;
        }
    }
    public boolean deleteBankCardByCardNumber(String bank_card_number) {
        PreparedStatement pstat = null;

        try {
            pstat = conn.prepareStatement("DELETE from bank_card where b_number = ?");
            pstat.setString(1, bank_card_number);
            pstat.execute();

            return true;
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
            return false;
        }
    }

}
---

vim FinancialProduct.java

---
package expt.db.finance.dao;

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

public class FinancialProduct {
    private Connection conn;

    public FinancialProduct(Connection connection) {
        conn = connection;
    }

    public ResultSet queryProductList() {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select p_id as 产品编号, p_name as 产品名称, "
                + "p_description as 产品描述, p_amount as 购买金额, p_year as 理财年限 "
                + "from financial_product");
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }

        return rs;
    }

    public ResultSet queryProductById(int product_id) {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select p_id as 产品编号, p_name as 产品名称, "
                + "p_description as 产品描述, p_amount as 购买金额, p_year as 理财年限 "
                + "from financial_product where p_id = ?");
            pstat.setInt(1, product_id);
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return rs;
    }
}
---

vim FinancialAsset.java

---
package expt.db.finance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import expt.db.finance.Const;

public class FinancialAsset {
    private Connection conn;

    public FinancialAsset(Connection connection) {
        conn = connection;
    }

    public ResultSet queryAssetList() {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select a_id as 资产编号, a_client_id as 客户ID, "
                    + "a_product_id as 产品编号, a_type as 产品类型, a_status as 状态, a_quantity as 金额, "
                    + "a_income as 收益金额, a_purchase_time as 申购时间 "
                    + "from financial_asset");
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return rs;
    }
    public ResultSet queryAssetById(int asset_id) {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select a_id as 资产编号, a_client_id as 客户ID, "
                    + "a_product_id as 产品编号, a_type as 产品类型, a_status as 状态, a_quantity as 金额, "
                    + "a_income as 收益金额, a_purchase_time as 申购时间 "
                    + "from financial_asset where a_id = ?");
            pstat.setInt(1, asset_id);
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
            System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return rs;
    }

    public ResultSet queryAssetByClientId(int client_id) {
        PreparedStatement pstat = null;
        ResultSet rs = null;

        try {
            pstat = conn.prepareStatement("select a_id as 资产编号, a_client_id as 客户ID, "
                    + "a_product_id as 产品编号, a_type as 产品类型, a_status as 状态, a_quantity as 金额, "
                    + "a_income as 收益金额, a_purchase_time as 申购时间 "
                    + "from financial_asset where a_client_id = ?");
            pstat.setInt(1, client_id);
            rs = pstat.executeQuery();
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return rs;
    }

    public boolean insertAssetRecord(int asset_id, int client_id, int product_id, int asset_type, int quantity) {
        PreparedStatement pstat = null;

        try {
            pstat = conn.prepareStatement("INSERT INTO financial_asset(a_id, a_client_id, a_product_id, "
                    + "a_type, a_status, a_quantity, a_income, a_purchase_time) "
                    + "VALUES (?,?,?,?,?,?,?,?);");
            pstat.setInt(1, asset_id);
            pstat.setInt(2, client_id);
            pstat.setInt(3, product_id);
            pstat.setInt(4, asset_type);
            pstat.setString(5, "??");
            pstat.setInt(6, quantity);
            pstat.setInt(7, 0);
            pstat.setDate(8, new java.sql.Date(new java.util.Date().getTime()));
            pstat.execute();

            return Const.SUCCEED;
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
            return Const.FAILED;
        }
    }

    public boolean deleteAssetRecordById(int asset_id) {
        PreparedStatement pstat = null;

        try {
            pstat = conn.prepareStatement("DELETE from financial_asset where a_id = ?");
            pstat.setInt(1, asset_id);
            pstat.execute();

            return Const.SUCCEED;
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
            return Const.FAILED;
        }
    }

    public int countOfAssetRecords(int clientId) {
        PreparedStatement pstat = null;
        ResultSet rs = null;
        int recordsCount = Const.INVALID;

        try {
            pstat = conn.prepareStatement("select count(*) as RECORDSCOUNT from financial_asset where a_client_id = ?");
            pstat.setInt(1, clientId);
            rs = pstat.executeQuery();

            if (rs.next()) {
            recordsCount = rs.getInt("RECORDSCOUNT");
            }
        } catch (SQLException ex) {
            System.err.println("SQLException information");
            while (ex != null) {
                System.err.println("Error msg: " + ex.getMessage());
                ex = ex.getNextException();
            }
        }
        return recordsCount;
    }
}

cd /root/db-dev-cert/src/expt/db/finance
vim testDAO.java

---
package expt.db.finance;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import expt.db.finance.dao.*;

public class testDAO {

    public static void main(String[] args) throws SQLException {
        Connection conn = DBUtils.getConnect();

        testClient(conn);
        testBankCard(conn);
        testFinancialProduct(conn);
        testFinancialAsset(conn);

        conn.close();
    }

    public static void testFinancialAsset(Connection conn) {
        FinancialAsset fa = new FinancialAsset(conn);
        ResultSet rs = null;
        rs = fa.queryAssetList();
        DBUtils.printAllRecords(rs);

        rs = fa.queryAssetById(1);
        DBUtils.printAllRecords(rs);

        fa.insertAssetRecord(5, 1, 2, 1, 666);
        rs = fa.queryAssetByClientId(1);
        DBUtils.printAllRecords(rs);

        fa.deleteAssetRecordById(5);
        rs = fa.queryAssetList();
        DBUtils.printAllRecords(rs);
    }

    public static void testFinancialProduct(Connection conn) {
        FinancialProduct fp = new FinancialProduct(conn);
        ResultSet rs = null;

        rs = fp.queryProductList();
        DBUtils.printAllRecords(rs);

        rs = fp.queryProductById(1);
        DBUtils.printAllRecords(rs);
    }

    public static void testBankCard(Connection conn) {
        BankCard bc = new BankCard(conn);
        ResultSet rs = null;

        rs = bc.queryBankCardList();
        DBUtils.printAllRecords(rs);

        bc.insertBankCard("card_number_01", "储蓄卡", 1);
        rs = bc.queryBankCardByCardNumber("card_number_01");
        DBUtils.printAllRecords(rs);
        rs = bc.queryBankCardByClientId(1);
        DBUtils.printAllRecords(rs);

        bc.deleteBankCardByCardNumber("card_number_01");
        rs = bc.queryBankCardByClientId(1);
        DBUtils.printAllRecords(rs);
    }
    public static void testClient(Connection conn) {
        Client client = new Client(conn);
        ResultSet rs = null;

        rs = client.queryClientList();
        DBUtils.printAllRecords(rs);

        client.insertClient(11, "name01", "idCard01", "phone01", "email01");
        rs = client.queryClientById(11);
        DBUtils.printAllRecords(rs);

        client.updateClient(11, "name11", "phone11", "email11");
        rs = client.queryClientById(11);
        DBUtils.printAllRecords(rs);

        client.deleteClient(11);
        rs = client.queryClientList();
        DBUtils.printAllRecords(rs);
    }
}
---

javac -classpath ../../../ -d . testDAO.java
java -p /root/db-dev-cert/libs/gaussdbjdbc.jar expt.db.finance.testDAO

// 业务层实现
cd /root/db-dev-cert/src/expt/db/finance
vim Utils.java

---
package expt.db.finance;
import java.util.Scanner;

public class Utils {
    public static int getInt(String hint) {
        int target;
        Scanner sc = new Scanner(System.in);

        while (true) {
            System.out.print(hint);

            try {
                target = sc.nextInt();
                break;
            } catch (Exception ex) {
                System.out.println("* * * * * *    输入有误，请重新输入！   * * * * * *");
                sc.nextLine();
            }
        }

        return target;
    }

    public static String getString(String hint) {
        String target;
        Scanner sc = new Scanner(System.in);

        while (true) {
            System.out.print(hint);

            try {
                target = sc.nextLine();
                break;
            } catch (Exception ex) {
                System.out.println("* * * * * *    输入有误，请重新输入！   * * * * * *");
                sc.nextLine();
            }
        }

        return target;
    }
}
---

vim Service.java

---
package expt.db.finance;

import java.sql.Connection;
import java.sql.SQLException;
import expt.db.finance.dao.*;

public class Service {
    private Connection conn;
    private Client client;
    private BankCard bankCard;
    private FinancialProduct fp;
    private FinancialAsset fa;

    public Service() {
        conn = openConnection();

        client = new Client(conn);
        bankCard = new BankCard(conn);
        fp = new FinancialProduct(conn);
        fa = new FinancialAsset(conn);
    }

    public Connection openConnection() {
        return DBUtils.getConnect();
    }

    public void closeConnection() throws SQLException {
        conn.close();
    }

    public void showClients() {
        System.out.println("* * * * * * * * *    客户列表     * * * * * * * * *");
        DBUtils.printAllRecords(client.queryClientList());
        System.out.println("* * * * * * * * * * * * * * * * * * * * * * * * * * *");
 }
 public void showClient() {
        int clientId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入需要查询信息的客户的ID: ");

        System.out.println("* * * * * * * * *    基础信息     * * * * * * * * *");
        DBUtils.printOneRecord(client.queryClientById(clientId));
        System.out.println("* * * * * * * * * * * * * * * * * * * * * * * * * * *");
    }

    public void showClientWithCardsAndAssets() {
        int clientId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入需要查询信息的客户的ID: ");

        System.out.println("* * * * * * * * * *   基础信息    * * * * * * * * *");
        DBUtils.printOneRecord(client.queryClientById(clientId));
        System.out.println("- - - - - - - - - -   开卡信息    - - - - - - - - -");
        DBUtils.printAllRecords(bankCard.queryBankCardByClientId(clientId));
        System.out.println("- - - - - - - - - -   理财资产信息    - - - - - - - - -");
        DBUtils.printAllRecords(fa.queryAssetByClientId(clientId));
        System.out.println("* * * * * * * * * * * * * * * * * * * * * * * * * * *");
    }

    public void setUpAnAccount() {
        System.out.println(Const.PREFIX_LEVEL_FIR + "已进入开户流程，请按提示要求输入客户相关信息，并按回车");

        String name = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入客户姓名: ");
        String idCard = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入客户身份证号: ");
        String phoneNumber = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入客户手机号码: ");
        String email = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入客户电子邮箱地址: ");
        int clientId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入客户ID: ");

        boolean ret = client.insertClient(clientId, name, idCard, phoneNumber, email);
        if (ret == Const.SUCCEED) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "客户信息已录入成功！");
            return;
        }

        System.out.println(Const.PREFIX_LEVEL_SEC + "客户信息已录入失败！");
    }
    public void modifyAnAccount() {
        System.out.println(Const.PREFIX_LEVEL_FIR + "已进入更新用户信息流程，请按提示要求输入相关信息，并按回车");

        int clientId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入需要修改的客户的ID: ");
        String name = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入客户更新后姓名: ");
        String phoneNumber = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入客户更新后手机号码: ");
        String email = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入客户更新后电子邮箱地址: ");

        boolean ret = client.updateClient(clientId, name, phoneNumber, email);
        if (ret == Const.SUCCEED) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "客户基础信息更新成功");
            return;
        }

        System.out.println(Const.PREFIX_LEVEL_SEC + "客户信息已录入失败！");
    }

    public void closeAnAccount() {
        System.out.println(Const.PREFIX_LEVEL_FIR + "已进入注销客户信息流程，请按提示要求输入相关信息，并按回车");

        int clientId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入需要注销的客户的ID: ");
        if (!isSafeToCloseAccount(clientId)) {
            return;
        }

        boolean ret = client.deleteClient(clientId);
        if (ret == Const.SUCCEED) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "销户成功!");
            return;
        }

        System.out.println(Const.PREFIX_LEVEL_SEC + "销户失败!");
    }

    public void setUpAnCard() {
        System.out.println(Const.PREFIX_LEVEL_FIR + "已进入开卡流程，请按提示要求输入相关信息，并按回车");
        int clientId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入待开卡的客户的ID: ");
        String cardNumber = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入卡号: ");

        System.out.println("开卡中，默认开卡类型为储蓄卡");
        String cardType = "储蓄卡";

        boolean ret = bankCard.insertBankCard(cardNumber, cardType, clientId);
        if (ret == Const.SUCCEED) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "开卡成功!");
            return;
        }

        System.out.println(Const.PREFIX_LEVEL_SEC + "开卡失败!");
    }

    public void closeAnCard() {
        System.out.println(Const.PREFIX_LEVEL_FIR + "已进入销卡流程，请按提示要求输入相关信息，并按回车");

        String cardNumber = Utils.getString(Const.PREFIX_LEVEL_SEC + "请输入卡号: ");
        boolean ret = bankCard.deleteBankCardByCardNumber(cardNumber);
        if (ret == Const.SUCCEED) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "销卡成功!");
            return;
        }

        System.out.println(Const.PREFIX_LEVEL_SEC + "销卡失败!");
    }

    public boolean isSafeToCloseAccount(int clientId) {
        int countCardsUnderCurrentClient = bankCard.countOfBankCards(clientId);
        int countAssetRecordsUnderCurrentClient = fa.countOfAssetRecords(clientId);

        if (countCardsUnderCurrentClient == Const.INVALID ||
                countAssetRecordsUnderCurrentClient == Const.INVALID) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "当前信息查询异常，暂不能进行销户操作");
            return false;
        }

        if (countCardsUnderCurrentClient > 0) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "当前客户存在" + countCardsUnderCurrentClient +
                    "张银行卡未注销，不能注销账户！");
            return false;
            }

        if (countAssetRecordsUnderCurrentClient > 0) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "当前客户存在" + countCardsUnderCurrentClient +
                    "笔金融产品未赎回，不能注销账户");
            return false;
        }
        return true;
    }

    public void showFinancialProducts() {
        System.out.println("* * * * * * * * *     理财产品    * * * * * * * * *");
        DBUtils.printAllRecords(fp.queryProductList());
        System.out.println("* * * * * * * * * * * * * * * * * * * * * * * * * * *");
    }

    public void buyAsset() {
        System.out.println(Const.PREFIX_LEVEL_FIR + "已进入金融产品购买流程，请按提示要求输入相关信息，并按回车");

        int clientId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入客户ID: ");
        int productId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入金融产品编号: ");
        int assetType = 1;
        int quantity = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入申购金额: ");
        int assetId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入资产编号: ");

        boolean ret = fa.insertAssetRecord(assetId, clientId, productId, assetType, quantity);
        if (ret == Const.SUCCEED) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "金融产品购买成功！");
            return;
        }

        System.out.println(Const.PREFIX_LEVEL_SEC + "金融产品购买失败!");
    }

    public void redeemAsset() {
        System.out.println(Const.PREFIX_LEVEL_FIR + "已进入金融产品赎回流程，请按提示要求输入相关信息，并按回车");

        int assetId = Utils.getInt(Const.PREFIX_LEVEL_SEC + "请输入资产编号: ");

        boolean ret = fa.deleteAssetRecordById(assetId);
        if (ret == Const.SUCCEED) {
            System.out.println(Const.PREFIX_LEVEL_SEC + "赎回成功!");
             return;
        }

        System.out.println(Const.PREFIX_LEVEL_SEC + "赎回失败!");
    }
}
---

vim FlowControl.java

---
package expt.db.finance;

import java.util.Scanner;

public class FlowControl {
    Service service;

    public FlowControl() {
        service = new Service();
    }

    public void startFlow() {
        int choices;
        do {
            System.out.print("\n"
                    + "* * * * * * * * *   操作列表  * * * * * * * * *\n"
                    + "     -------------------------------------\n"
                    + "  1) 客户列表查询 \n"
                    + "  2) 客户详细信息查询 \n"
                    + "  3) 客户基础信息修改 \n"
                    + "  4) 客户开户 \n"
                    + "  5) 客户开卡 \n"
                    + "  6) 客户销卡 \n"
                    + "  7) 客户销户 \n"
                    + "     -------------------------------------\n"
                    + "  8) 理财产品查询 \n"
                    + "  9) 理财产品购买  \n"
                    + " 10) 理财产品赎回 \n"
                    + "     -------------------------------------\n"
                    + "  0) 退出系统\n"
                    + "* * * * * * * * *  * * * * * * * * * * * * * *\n");

            choices = Utils.getInt(Const.PREFIX_LEVEL_FIR + "请输入需要进行的操作编号: ");
            switch (choices) {
            case 1:
                service.showClients();
                waitForContinue();
                break;
            case 2:
                service.showClientWithCardsAndAssets();
                waitForContinue();
                break;
            case 3:
                service.modifyAnAccount();
                waitForContinue();
                break;
            case 4:
                service.setUpAnAccount();
                waitForContinue();
                break;
            case 5:
                service.setUpAnCard();
                waitForContinue();
                break;
            case 6:
                service.closeAnCard();
                waitForContinue();
                break;
            case 7:
                service.closeAnAccount();
                waitForContinue();
                break;
            case 8:
                service.showFinancialProducts();
                waitForContinue();
                break;
            case 9:
                service.buyAsset();
                waitForContinue();
                break;
            case 10:
                service.redeemAsset();
                waitForContinue();
                break;
            case 0:
                System.out.println(Const.PREFIX_LEVEL_FIR + "已退出!");
                break;
                default:
                System.out.println(Const.PREFIX_LEVEL_FIR + "当前输入信息无效!");
                waitForContinue();
            }
        }
        while(choices!=0);
    }

    public void waitForContinue() {
        Scanner sc = new Scanner(System.in);
        System.out.print(Const.PREFIX_LEVEL_FIR + "请按回车继续选择...");
        sc.nextLine();
    }
}
---

vim launch.java

---
package expt.db.finance;

public class launch {

    public static void main(String[] args) {
        FlowControl flowControl = new FlowControl();
        flowControl.startFlow();
    }
}
---

javac -classpath ../../../ -d . launch.java
java -p /root/db-dev-cert/libs/gaussdbjdbc.jar expt.db.finance.launch

GaussDB公有云运维管理操作

DN主备切换
备份恢复
修改参数 autoanalyze 改为 on

1
2
3

gsql -d postgres -h xxx.xxx.xx.xx -p 8000 -U root -W yourpassword -r
show autoanalyze;
\q

系统表查看

gsql -d finance -h xxx.xxx.xx.xx -p 8000 -U db_dev -W yourpassword -r
set search_path=finance;

// 查看finanace schema下的表
\d

select a.relname,b.nspname from pg_class a join pg_namespace b on a.relnamespace = b.oid  where b.nspname = 'finance' and relkind ='r';
select a.relname,b.nspname from pg_class a join pg_namespace b on a.relnamespace = b.oid  where b.nspname = 'finance' and relkind ='i';
\q

权限管控

gsql -d finance -h xxx.xxx.xx.xx -p 8000 -U root -W yourpassword -r
create user test identified by 'yourpassword';
\c - test
select * from finance.client;
\c - root
grant usage on schema finance to test;
\c - test
select * from finance.client;
\c - root
grant select on finance.client to test;
\c - test
select * from finance.client limit 1;

实验考试知识点（2025.5）

创建用户、数据库，完成数据的导入（sql文件），使用gsql登录gaussdb
修改数据库配置文件（vi命令）
使用系统表关联查询指定模式下所有表的模式名、表名和表的行数，并对表的行数进行排序
增删改查简单sql语句编写

注：题目较为简单，毕竟是入门级考试；大部分命令及sql语句需要自己编写，像java编译运行的命令手册会提供；需要知道linux常用命令（防止因为自己的一些操作出现了考试外的结果，此时需要自行恢复）

附上本人的考试成绩

最后感谢学校数据库老师给予的考试机会（向Huawei申请的代金券）