实验六:ODBC/JDBC数据库编程

许安
772 阅读6分钟

实验六:ODBC/JDBC数据库编程

1.ODBC数据源配置以及程序调试

(1) 配置一个ODBC数据源，要求数据源名称：student，其中包含s表（学生信息表）。

image-20211029211016521

(2) 理解ODBC编程，阅读并运行实验给出的例子程序（MFC或者CSharp代码），要求简单写出自己对这段程序的理解或者流程图，并且给出程序运行结果截图。

JDBC链接数据库并进行操作可分为以下六步：

  1. 注册数据库驱动
  2. 链接数据库
  3. 获取数据库操作对象
  4. 执行sql语句
  5. 处理查询结果集
  6. 释放数据库链接
package lyx.jdbc;
import java.sql.*;

public class conn_test {
    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        String url = "jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC"; //JDBC连接URL
        String user = "root"; //用户名
        String passwd = "12315"; //密码

        String sql = ""; // sql语句

        try {
            // 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 建立数据库链接
            connection = DriverManager.getConnection(url, user, passwd);

            // 获取数据库操作对象
            statement = connection.createStatement();

            // 执行SQL
            sql = "select * from s";

            // 处理查询结果集
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                for (int i = 1; i < 4; i++) {
                    System.out.print(resultSet.getString(i) + " ");
                }
                System.out.println();
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
}

以上代码运行截图：

image-20211029225513166

2.参考以上的ODBC编程例子程序，使用ODBC编程技术，编写一个简单的程序，包含对数据库SPJ_MNG的连接，查询，插入，修改和删除。

在1（2）基础上进行部分修改

数据库链接

String url = "jdbc:mysql://localhost:3306/spj_mng?useSSL=false&serverTimezone=UTC"; //JDBC连接URL

数据库查询

// 执行SQL
sql = "select * from s";

从数据库中查询S表

结果：

image-20211030135624734

与workbench相同：

image-20211030135715062

数据库插入

// 执行SQL
sql = "insert into s (sno, sname, status, city) values ('S6', '新建', 99, '上海')";

// 处理查询结果集
cnt = statement.executeUpdate(sql);
System.out.println(cnt);

代码含义：向表插入数据，并打印被影响的行数

结果：

image-20211030140928685

image-20211030140951556

数据库修改

PreparedStatement statement = null;

// 执行SQL
sql = "update s set sname=? where sno=?";
statement = connection.prepareStatement(sql);
statement.setString(1, "八建");
statement.setString(2, "S6");

// 处理查询结果集
cnt = statement.executeUpdate();
System.out.println(cnt);

使用PreparedStatement预置对象进行修改，返回影响行数

image-20211030142540069

image-20211030142556079

数据库删除

// 执行SQL
sql = "delete from s where sno=?";
statement = connection.prepareStatement(sql);
statement.setString(1, "S6");

删除S6

image-20211030142816918

image-20211030142831309

3.银行场景化综合应用实验。

(1)身份证号为“610103123456781234”的人在C银行注册了一个新客户，并且申请办理了一张新的储蓄卡。在客户表和银行卡表中插入该客户记录。

使用预置对象进行插入

// 插入customer
sql = "insert into customer (c_id, c_name, c_id_card, c_phone, c_password)" +
        "values (?, ?, ?, ?, ?)";
statement = connection.prepareStatement(sql);

statement.setInt(1, 31);
statement.setString(2, "赵一");
statement.setString(3, "610103123456781234");
statement.setString(4, "18815650031");
statement.setString(5, "gaussdb");
cnt += statement.executeUpdate();

// 插入bank_card
sql = "insert into bank_card (b_number, b_type, b_c_id, b_balance)" +
        "values (?, ?, ?, ?)";
statement = connection.prepareStatement(sql);

statement.setString(1, "6222021302020000021");
statement.setString(2, "储蓄卡");
statement.setInt(3, 31);
statement.setDouble(4, 0.00);

结果：

image-20211030150132117

image-20211030150142172

image-20211030150153933

(2) 根据业务需要添加表的约束，约束添加成功后进行验证。

① 在银行卡表，理财产品购买表，保险购买表，基金购买表中，添加正确的外键约束：客户编号设置为外键，参照客户表的客户编号；理财产品编号，保险编号，基金编号分别参照对应的表中的编号；支付银行卡号参考银行卡表的卡号。

新建addFK方法，传入Statement以及Connection对象，从控制台接受外键创建的各种参数，拼接sql语句进行数据库操作。

package lyx.jdbc;
import java.sql.*;
import java.util.Scanner;

public class conn_test {
    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        String url = "jdbc:mysql://localhost:3306/finance?useSSL=false&serverTimezone=UTC"; //JDBC连接URL
        String user = "root"; //用户名
        String passwd = "12315"; //密码

        String sql = ""; // sql语句
        int cnt = 0;    //affected rows cnt

        try {
            // 1 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2 建立数据库链接
            connection = DriverManager.getConnection(url, user, passwd);


            /*************** 数据库操作开始 ***********************************/
            // 3 获取数据库操作对象
            // 4 执行SQL

            // add fk
            cnt += addFK(statement, connection);
            cnt += addFK(statement, connection);
            cnt += addFK(statement, connection);

            // 5 处理查询结果集
            System.out.println(cnt);

            /*************** 数据库操作结束 ***********************************/


        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 6 释放资源
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }

    private static int addFK(Statement statement, Connection connection) {

        String tb_name;
        String fk_name;
        String tb_item;
        String fk_tb;
        String fk_item;

        int cnt = 0;

        Scanner input = new Scanner(System.in);

        System.out.println("tb_name:");
        tb_name = input.next();

        System.out.println("fk_name:");
        fk_name = input.next();

        System.out.println("tb_item:");
        tb_item = input.next();

        System.out.println("fk_tb:");
        fk_tb = input.next();

        System.out.println("fk_item:");
        fk_item = input.next();


        String sql = "alter table "+tb_name+" " +
                "add constraint "+fk_name+" " +
                "foreign key("+tb_item+") " +
                "REFERENCES "+fk_tb+"("+fk_item+")";
        try {
            statement = connection.createStatement();
            cnt += statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        System.out.println("FK "+fk_name+" add successfully!");
        return cnt;

    }
}

结果：

银行卡表

image-20211030160145658

image-20211030160201598

理财产品购买表
c_id

image-20211030161812725

image-20211030161832992

p_id

image-20211030162126360

image-20211030162150029

b_number

image-20211030162649471

保险购买表

image-20211030163246260

image-20211030163254596

image-20211030163302991

image-20211030163337107

基金购买表

image-20211030163650660

image-20211030163659971

image-20211030163708865

image-20211030163727749

② 在以上基本表中，存在金额或者价格相关的4个属性。在现实生活中，金额或者价格不会存在负数。因此针对这些属性，添加其值大于0的约束条件。注意，对于银行卡是“信用卡”的情况余额可以为负，所以不要设定余额大于0的约束。

理财产品、保险、基金约束
        String sql = "alter table "+tb_name+" " +
                "add constraint "+ck_name+" " +
                "check("+ck_item+">0) ";

image-20211030170522516

检验：

执行代码：

UPDATE `finance`.`insurance` SET `i_price` = '-100.00' WHERE (`i_id` = '5');

出错，禁止修改为负数：

image-20211030170728069

银行卡约束

关键代码：

String sql = "alter table bank_card " +
        "add constraint ck_b_balance " +
        "check((b_balance>=0 and b_type='储蓄卡') or b_type='信用卡')";

check((b_balance>=0 and b_type='储蓄卡') or b_type='信用卡')根据卡类型进行检查。

检验：

修改储蓄卡金额为-100，提示修改禁止：

image-20211030185914311

修改信用卡金额为-100，成功：

image-20211030190001320

(3) 模拟以下的业务写出SQL查询语句进行查询。

① 查询C银行所有银行卡的卡号和类型信息。

/*************** 数据库操作开始 ***********************************/
// 3 获取数据库操作对象
statement = connection.createStatement();

// 4 执行SQL
sql = "select b_number, b_type from bank_card";
resultSet = statement.executeQuery(sql);

// 5 处理查询结果集
while (resultSet.next()) {
    System.out.print(++cnt + " ");
    for (int i = 1; i <= 2; i++) {
        System.out.print(resultSet.getString(i) + " ");
    }
    System.out.println();
}

/*************** 数据库操作结束 ***********************************/

结果：

image-20211030213917854

② 查询C银行拥有的客户数量。

方法：getInt()方法：检索当前行中指定列的值；若该列的int值为null，则返回0；

/*************** 数据库操作开始 ***********************************/
// 3 获取数据库操作对象
statement = connection.createStatement();

// 4 执行SQL
sql = "select count(c_id) from customer";
resultSet = statement.executeQuery(sql);

// 5 处理查询结果集
if (resultSet.next()) {
    cnt = resultSet.getInt(1);
}
System.out.println(cnt);

/*************** 数据库操作结束 ***********************************/

image-20211030214811957

③ 查询拥银行卡的所有客户编号，姓名和身份证号。

/*************** 数据库操作开始 ***********************************/
// 3 获取数据库操作对象
statement = connection.createStatement();

// 4 执行SQL
sql = "SELECT distinct c_id, c_name, c_id_card FROM customer, bank_card " +
        "where c_id = b_c_id;";
resultSet = statement.executeQuery(sql);

// 5 处理查询结果集
while (resultSet.next()) {
    for (int i = 1; i <= 3; i++) {
        System.out.print(resultSet.getString(i) + " ");
    }
    System.out.println();
}

/*************** 数据库操作结束 ***********************************/

image-20211030215345299

④ 统计所有的银行卡中，储蓄卡和信用卡的各自数量。

/*************** 数据库操作开始 ***********************************/
// 3 获取数据库操作对象
statement = connection.createStatement();

// 4 执行SQL
sql = "SELECT count(b_type), b_type FROM bank_card group by b_type;";
resultSet = statement.executeQuery(sql);

// 5 处理查询结果集
while (resultSet.next()) {
    for (int i = 1; i <= 2; i++) {
        System.out.print(resultSet.getString(i) + " ");
    }
    System.out.println();
}

/*************** 数据库操作结束 ***********************************/

image-20211030215821840

⑤ 查询保险表中，保险价格的平均值。

// 4 执行SQL
sql = "SELECT avg(i_price) FROM insurance;";
resultSet = statement.executeQuery(sql);

image-20211030220032004

⑥ 查询保险表中保险价格的最大值和最小值所对应的险种和价格。

// 4 执行SQL
sql = "SELECT i_name as max_name, max(i_price) as max_price, " +
        "i_name as min_name, min(i_price) as min_price " +
        "FROM insurance ;";
resultSet = statement.executeQuery(sql);

image-20211030220633878

⑦ 某人捡到一张卡，希望查询该银行卡号是'6222021302020000006'的客户编号，姓名和联系电话。

// 4 执行SQL
sql = "SELECT c_id, c_name, c_phone FROM customer, bank_card " +
        "where c_id = b_c_id and b_number = '6222021302020000006';";
resultSet = statement.executeQuery(sql);

image-20211030221132054

⑧ 查询保险产品中保险价格大于平均值的保险名称和适用人群。

// 4 执行SQL
sql = "SELECT i_name, i_person FROM insurance \n" +
        "where i_price > (select avg(i_price) from insurance);";
resultSet = statement.executeQuery(sql);

image-20211030221830499

⑨ 查询C银行发布的理财产品总数，按照p_year分组。

// 4 执行SQL
sql = "SELECT count(p_year) as product_num, p_year \n" +
        "FROM finances_product group by p_year;";
resultSet = statement.executeQuery(sql);

image-20211030222243526

⑩ 查询适用于老人的保险编号，保险名称，保险年限。

// 4 执行SQL
sql = "SELECT i_id, i_name, i_year FROM insurance where i_person = '老人';";
resultSet = statement.executeQuery(sql);

image-20211030222508940

(4) 模拟以下的业务创建视图并基于视图进行查询。

① 创建一个视图，包含拥有银行卡的所有客户编号，姓名, 身份证号, 拥有的银行卡个数。

// 4 执行SQL
// create view
sql = "create view view01 as \n" +
        "select c_id, c_name, c_id_card, count(b_number) as card_num\n" +
        "from customer, bank_card\n" +
        "where c_id = b_c_id group by c_id ;";
statement.execute(sql);
// query
sql = "select * from view01;";
resultSet = statement.executeQuery(sql);

image-20211030224111534

② 修改视图：在原有视图的基础上，仅包含拥有信用卡的用户。

// 4 执行SQL
// alter view
sql = "ALTER VIEW view01 as \n" +
        "select c_id, c_name, c_id_card, count(b_number) as card_num\n" +
        "from customer, bank_card\n" +
        "where c_id = b_c_id and b_type = '信用卡'group by c_id ;";
statement.execute(sql);
// query
sql = "select * from view01;";
resultSet = statement.executeQuery(sql);

image-20211030224609204

(5) 模拟业务变化，人们对基金查询的需求大幅度增加。在基金购买表上创建复合索引：c_id ASC, f_id ASC, f_quantity DESC。（3分）

// 4 执行SQL
sql = "create index idx_fund\n" +
        "on c_fund(c0_id asc, f_id asc, f_quantity desc);";
statement.execute(sql

image-20211030225445797

(6) 模拟业务需求，增删改数据。（15分）

① 客户编号为2的客户，申请更新自己的手机号码为'13312345678'。

// 4 执行SQL
sql = "update customer set c_phone = '13312345678'\n" +
        "where c_id = 2;";
statement.execute(sql);

image-20211030230302028

② 模拟4号理财产品的发售，购买，结算，停止产品的简化过程。

第一步：银行发售新的理财产品。

2018/12/1日，银行发售1年期的理财产品，编号为4，开始封闭时间2019/1/6，价格为8.0元，状态为0(0表示正常）。

// 4 执行SQL
sql = "insert into finances_product\n" +
        "(p_id, p_name, p_description, p_sale_start_date, p_excu_start_date, p_price, p_year, p_status)\n" +
        "values\n" +
        "(4, '四号产品', null, '2018-12-1', '2019-1-6', 8.0, 1, 0);";
statement.execute(sql);

image-20211030231049206

第二步：客户购买4号理财产品（购买并从对应的银行卡扣钱）。

假设有三笔交易：

a. 2019/1/5 13:00:00： 客户3用银行卡'6222021302020000002'购买了1000份4号理财产品：

b. 2019/1/5 14:00:00： 客户5用银行卡'6222021302020000003'购买了1000份4号理财产品：

c. 2019/1/5 15:00:00： 客户5用银行卡'6222021302020000003'又购买了500份4号理财产品

首先创建存储过程 buy_finance ：

delimiter $$
create procedure buy_finance
( in c_id int, in p_id int, 
in p_time datetime, in p_quantity int, in b_number char(30))

begin

	declare purchase_money double default 0;
    declare income double default 0;
    declare total double default 0;
    
    -- get neccessary info
    declare product_price DECIMAL(10,2);
    declare balance_left DECIMAL(10,2);
    declare card_type char(20);
    set product_price = (select p_price from finances_product where finances_product.p_id = p_id);
    set balance_left = (select b_balance from bank_card where bank_card.b_number = b_number);
    set card_type = (select b_type from bank_card where bank_card.b_number = b_number);
    
    -- caculate
    set purchase_money = p_quantity * product_price;
    set income = 0;
    set total = purchase_money;
    
    -- judge
    if balance_left < purchase_money and card_type = '储蓄卡' then
		-- purcahse fail
        select 'Balance Not Enough';
    else
		-- purchase success
        -- --isnert
        insert into c_finances(c_id, p_id, p_time, p_quantity, p_purchase_money, p_income, p_total, b_number)
        values (c_id, p_id, p_time, p_quantity, purchase_money, income, total, b_number);
        -- --cut balance
        set balance_left = balance_left - purchase_money;
        update bank_card set b_balance = balance_left where bank_card.b_number = b_number;
    end if;
    
end $$
delimiter ;

调用存储过程：

call buy_finance (3, 4,	'2019/1/5 13:00:00', 1000, '6222021302020000002');

结果：

image-20211102224419272

扣除购买金：

image-20211102224445773

其余两项记录操作相同。

第三步：银行进行收益兑现。

假设2020/1/6日该理财产品到期，假设收益为5%。2020/1/6日，银行给所有购买4号理财产品的用户进行收益兑现：计算所有购买客户的收益，将其本金+收益的总金额累计增加到购买该产品的银行卡余额中。

创建存储过程：

输入：产品号、收益率、当前时间

首先判断当前时间是否达到产品的上线时间，若达到，则进行数据处理：计算并更新收益、计算并更新总资产、计算并更新银行卡余额。

DROP PROCEDURE if exists pay_back;

delimiter $$
create procedure pay_back
( in p_id int, in pay_rate double, in cur_time datetime )

begin

	declare excu_start_date datetime;
    declare product_c_id int;
    declare product_p_id int;
    declare product_p_time datetime;
    declare product_purchase_money DECIMAL(10,2);
    declare product_b_number char(30);
	-- cursor
	DECLARE done BOOLEAN DEFAULT 0;
	declare c_finances_cur cursor for
		select c_id, p_id, p_time, p_purchase_money, b_number from c_finances;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    
	-- set
    set excu_start_date = (select p_excu_start_date from finances_product where finances_product.p_id = p_id);
    
    -- judge
	if TimeStampDiff(year, excu_start_date, cur_time) < (select p_year from finances_product where finances_product.p_id = p_id) then
		-- Time Not Up
        select 'Time Not Up';
    else
		-- Time Up
		OPEN c_finances_cur;
		REPEAT
			FETCH c_finances_cur INTO product_c_id, product_p_id, product_p_time, product_purchase_money, product_b_number;
			IF done!=1 THEN        
            
				-- business logic
				if  product_p_id = p_id then
					-- income
					update c_finances set p_income = product_purchase_money * (pay_rate / 100)
						where c_finances.c_id = product_c_id and c_finances.p_id = product_p_id and c_finances.p_time = product_p_time; 
					-- total
					update c_finances set p_total = product_purchase_money * (1 + pay_rate / 100)
						where c_finances.c_id = product_c_id and c_finances.p_id = product_p_id and c_finances.p_time = product_p_time; 
					-- pay
					update bank_card set b_balance = bank_card.b_balance + product_purchase_money * (1 + pay_rate / 100)
						where bank_card.b_number = product_b_number;
				end if;
                
			END IF;
		UNTIL DONE END REPEAT;
		CLOSE c_finances_cur;

    end if;
        
end $$
delimiter ;

运行：call pay_back(4, 5, '2020/1/6');

数据更新：

image-20211104154859142

image-20211104154929450

image-20211104154945923

第四步：银行停止4号理财产品

假设银行已经处理完了4号理财产品的收益兑现。现在银行要下架该理财产品。注意删除该理财产品记录时，需考虑外键约束。

DROP PROCEDURE if exists drop_product;

delimiter $$
create procedure drop_product(in p_id int)

begin
	delete from c_finances where c_finances.p_id = p_id;
    delete from finances_product where finances_product.p_id = p_id;
end $$
delimiter ;

运行call drop_product(4);

image-20211104161155804

image-20211104161225202

(7) 用JDBC、ODBC或者其他第三方库等编程实现finance数据库的连接，并且实现以上第（6）小题中的第二步或者第三步。

编写JDBC程序，实现第三步。

主要代码：

// 计算数据
product_income = product_purchase_money * (rate / 100);
product_total = product_income + product_purchase_money;
b_balance += product_total;

// 更新数据
sql = "update c_finances set p_income=?, p_total=?" +
        "where c_id = "+ product_cid +" and p_time = '"+ product_time + "'";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setDouble(1, product_income);
preparedStatement.setDouble(2, product_total);
preparedStatement.executeUpdate();

sql_card = "update bank_card set b_balance=?" +
        "where b_number = '"+ product_b_number + "'";
preparedStatement = connection.prepareStatement(sql_card);
preparedStatement.setDouble(1, b_balance);
preparedStatement.executeUpdate();

从控制台接受到期产品号、收益率：

image-20211104205012081

数据库更新：

image-20211104205035094

image-20211104205050651

遇到的问题及解方案

1

在编写存储过程时，我将过程内的变量名定义为与表中字段名相同，导致查询过程中怎么也拿不到对应的值。

image-20211102223910515

image-20211102224000206

结果为null：

image-20211102223944432

后来经过查阅资料发现问题所在，修改之后恢复正常

image-20211102224141560

image-20211102224158013

2

进行第三步收益兑现时需要从c_finances中逐条选取信息进行计算，但我忽略了相同用户、相同产品、相同卡号而仅仅时间不同的情况，导致查询结果出现多值而报错的情况。

image-20211103000124937

改进后使用游标逐条读取数据判断，符合条件的更新

declare c_finances_cur cursor for
	select c_id, p_id, p_time, p_purchase_money, b_number from c_finances;
avatar
文章
阅读
粉丝