zql277 2023-03-12 00:12 采纳率: 62.5%
浏览 86
已结题

delphi7把json数组保存到数据库

如何把这样的json数组保存到MSSQL数据库?

{
                "order_id": 5523720284217,
                "order_no": "535",
                "lock_flag": 0,
                "exchange_rate": "674.13",
                "exchange_rate_usd": "100.00000",
                "name": "161245762501024674",
                "users": [
                    {
                        "rate": 100,
                        "user_id": "55311497"
                    }
                ],
                "currency": "USD",
                "amount": "9296.60",
                "amount_rmb": 62671.17,
                "amount_usd": 9296.6,
                "addition_cost_amount": 0,
                "product_total_amount": 9296.6,
                "product_total_count": 44,
                "cost_with_tax_total": "0.0000",
                "price_contract": "CFR",
                "receive_remittance_way": null,
                "price_contract_remark": null,
                "receive_remittance_remark": null,
                "insurance_remark": null,
                "bank_info": null,
                "order_contract": null,
                "company_name": "ltd.",
                "company_phone": "1054861067",
                "company_fax": "",
                "company_address": "12982",
                "customer_name": "ANET",
                "customer_phone": "61067",
                "customer_email": "16735@outlook.com",
                "customer_address": null,
                "transport_mode": "海运",
                "shipment_deadline": "",
                "shipment_port": "",
                "target_port": "",
                "shipment_deadline_remark": "尾款到账后40自然日发货",
                "more_or_less": null,
                "package_remark": null,
                "marked": null,
                "account_date": "2022-12-21 16:32:54",
                "country": "KR",
                "opportunity_id": 0,
                "remark": "",
                "link_status": [
                    20,
                    31,
                    40,
                    50,
                    10
                ],
                "source_type": 2,
                "archive_type": 5,
                "create_time": "2023-01-31 10:55:19",
                "update_time": "2023-03-11 04:18:13",
                "create_user": 55311497,
                "update_user": 55311497,
                "_id": 5523720284217,
                "approval_flow_info": null,
                "departments_info": [
                    {
                        "rate": 100,
                        "department_id": 32018,
                        "name": "xx部"
                    }
                ],
                "users_info": [
                    {
                        "rate": 100,
                        "user_id": "55311497",
                        "nickname": "We",
                        "avatar": "xe"
                    }
                ],
                "handler_info": [
                    {
                        "user_id": "55311497",
                        "nickname": "We",
                        "avatar": "xx"
                    }
                ],
                "company": {
                    "company_id": 21247381445,
                    "name": "ltd.",
                    "is_archive": 1,
                    "serial_id": "450"
                },
                "status_info": {
                    "is_purchase": 0,
                    "is_beginning": 1,
                    "is_ending": 0,
                    "into_performance": 0,
                    "need_review": 0,
                    "change_ending": 1,
                    "change_review": 0,
                    "can_delete": 0,
                    "next_status": [
                        "20792396447",
                        "20792396449",
                        "20792396451",
                        "20792396463",
                        "20792396465",
                        "20792396468",
                        "20792396472",
                        "20792396473",
                        "20792396474"
                    ],
                    "client_id": "30098",
                    "id": "20792396446",
                    "name": "草稿",
                    "remark": "",
                    "rank": "1",
                    "create_time": "2020-10-26 15:09:24",
                    "update_time": "2023-02-07 17:37:28"
                },
                "stock_up_status": {
                    "link": 4,
                    "title": "待备货",
                    "link_status": 40,
                    "progress": {
                        "define": "起始",
                        "value": 0
                    },
                    "type": "text"
                },
                "shipping_status": {
                    "link": 5,
                    "title": "待出库",
                    "link_status": 50,
                    "progress": {
                        "define": "起始",
                        "value": 0
                    },
                    "type": "text"
                },
                "end_status": {
                    "link": 1,
                    "title": "待完成",
                    "link_status": 10,
                    "progress": {
                        "define": "起始",
                        "value": 0
                    },
                    "type": "text"
                }
            }
  • 写回答

9条回答 默认 最新

  • dahe0825 2023-03-12 10:19
    关注

    参考GPT和自己的思路，以下是使用 Delphi7 将该 JSON 数组保存到 MSSQL 数据库的示例代码。需要使用第三方 JSON 库 SuperObject。

    首先，需要创建与 JSON 数据对应的数据库表，下面是该 JSON 数组对应的表的 SQL 语句：

    CREATE TABLE order_info (
    order_id BIGINT PRIMARY KEY,
    order_no VARCHAR(50),
    lock_flag INT,
    exchange_rate VARCHAR(50),
    exchange_rate_usd VARCHAR(50),
    name VARCHAR(50),
    currency VARCHAR(50),
    amount VARCHAR(50),
    amount_rmb DECIMAL(18,2),
    amount_usd DECIMAL(18,2),
    addition_cost_amount DECIMAL(18,2),
    product_total_amount DECIMAL(18,2),
    product_total_count INT,
    cost_with_tax_total VARCHAR(50),
    price_contract VARCHAR(50),
    receive_remittance_way VARCHAR(50),
    price_contract_remark VARCHAR(50),
    receive_remittance_remark VARCHAR(50),
    insurance_remark VARCHAR(50),
    bank_info VARCHAR(50),
    order_contract VARCHAR(50),
    company_name VARCHAR(50),
    company_phone VARCHAR(50),
    company_fax VARCHAR(50),
    company_address VARCHAR(50),
    customer_name VARCHAR(50),
    customer_phone VARCHAR(50),
    customer_email VARCHAR(50),
    customer_address VARCHAR(50),
    transport_mode VARCHAR(50),
    shipment_deadline VARCHAR(50),
    shipment_port VARCHAR(50),
    target_port VARCHAR(50),
    shipment_deadline_remark VARCHAR(50),
    more_or_less VARCHAR(50),
    package_remark VARCHAR(50),
    marked VARCHAR(50),
    account_date VARCHAR(50),
    country VARCHAR(50),
    opportunity_id INT,
    remark VARCHAR(50),
    source_type INT,
    archive_type INT,
    create_time VARCHAR(50),
    update_time VARCHAR(50),
    create_user INT,
    update_user INT,
    approval_flow_info VARCHAR(50)
);

CREATE TABLE order_users (
    order_id BIGINT,
    rate INT,
    user_id VARCHAR(50)
);

CREATE TABLE order_departments (
    order_id BIGINT,
    rate INT,
    department_id INT,
    name VARCHAR(50)
);

CREATE TABLE order_company (
    order_id BIGINT,
    company_id BIGINT,
    name VARCHAR(50),
    is_archive INT,
    serial_id VARCHAR(50)
);

CREATE TABLE order_status_info (
    order_id BIGINT,
    is_purchase INT,
    is_beginning INT,
    is_ending INT,
    into_performance INT,
    need_review INT,
    change_ending INT,
    change_review INT,
    can_delete INT,
    client_id VARCHAR(50),
    id VARCHAR(50),
    name VARCHAR(50),
    remark VARCHAR(50),
    rank VARCHAR(50),
    create_time VARCHAR(50),
    update_time VARCHAR(50)
);

CREATE TABLE order_link_status (
    order_id BIGINT,
    status INT
);

CREATE TABLE order_progress (
    order_id BIGINT,
    link INT,
    define VARCHAR(50),
    value INT
);

    然后，使用 SuperObject 解析 JSON 数据，并将解析结果保存到数据库中。以下是完整的示例代码：

    uses
  SuperObject;

function EscapeSQLString(const S: string): string;
begin
  Result := StringReplace(S, '''', '''''', [rfReplaceAll]);
end;

procedure SaveOrderInfo(Json: string);
var
  OrderInfo, Users, Departments, HandlerInfo: ISuperObject;
  LinkStatus, NextStatus: TSuperArray;
  Conn: TADOConnection;
  Cmd: TADOCommand;
  Param: TADOParameter;
begin
  // 解析 JSON 数据
  OrderInfo := SO('{ "order_id": 5523720284217, "order_no": "535", ... }');
  Users := OrderInfo.O['users'];
  Departments := OrderInfo.O['departments_info'];
  HandlerInfo := OrderInfo.O['handler_info'];
  LinkStatus := OrderInfo.A['status_info.link_status'];
  NextStatus := OrderInfo.A['status_info.next_status'];

  // 连接数据库
  Conn := TADOConnection.Create(nil);
  try
    Conn.ConnectionString := 'Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DB_NAME;User ID=USERNAME;Password=PASSWORD';
    Conn.LoginPrompt := False;
    Conn.Connected := True;

    // 插入订单信息
    Cmd := TADOCommand.Create(nil);
    try
      Cmd.Connection := Conn;
      Cmd.CommandText := 'INSERT INTO orders (order_id, order_no, lock_flag, exchange_rate, exchange_rate_usd, name, currency, amount, amount_rmb, amount_usd, addition_cost_amount, product_total_amount, product_total_count, cost_with_tax_total, price_contract, shipment_deadline, shipment_port, target_port, shipment_deadline_remark, more_or_less, package_remark, marked, account_date, country, opportunity_id, remark, source_type, archive_type, create_time, update_time, create_user, update_user, approval_flow_info) ' +
                         'VALUES (:order_id, :order_no, :lock_flag, :exchange_rate, :exchange_rate_usd, :name, :currency, :amount, :amount_rmb, :amount_usd, :addition_cost_amount, :product_total_amount, :product_total_count, :cost_with_tax_total, :price_contract, :shipment_deadline, :shipment_port, :target_port, :shipment_deadline_remark, :more_or_less, :package_remark, :marked, :account_date, :country, :opportunity_id, :remark, :source_type, :archive_type, :create_time, :update_time, :create_user, :update_user, :approval_flow_info)';
      Cmd.Parameters.ParamByName('order_id').Value := OrderInfo.I['order_id'];
      Cmd.Parameters.ParamByName('order_no').Value := OrderInfo.S['order_no'];
      Cmd.Parameters.ParamByName('lock_flag').Value := OrderInfo.I['lock_flag'];
      Cmd.Parameters.ParamByName('exchange_rate').Value := OrderInfo.S['exchange_rate'];
      Cmd.Parameters.ParamByName('exchange_rate_usd').Value := OrderInfo.S['exchange_rate_usd'];
      Cmd.Parameters.ParamByName('name').Value := OrderInfo.S['name'];
      Cmd.Parameters.ParamByName('currency').Value := OrderInfo.S['currency'];
      Cmd.Parameters.ParamByName('amount').Value := OrderInfo.S['amount'];
      Cmd.Parameters.ParamByName('amount_rmb').Value := OrderInfo.F['amount_rmb'];
      Cmd.Parameters.ParamByName('amount_usd').Value := OrderInfo.F['amount_usd'];
      Cmd.Parameters.ParamByName('addition_cost_amount').Value :=OrderInfo.F['addition_cost_amount'];
      Cmd.Parameters.ParamByName('tax_amount').Value := OrderInfo.F['tax_amount'];
      Cmd.Parameters.ParamByName('order_status').Value := NextStatus.AsInteger;
      Cmd.Parameters.ParamByName('handler_id').Value := HandlerInfo.I['id'];
      Cmd.Parameters.ParamByName('department_id').Value := Departments.A['departments'][0].I['id'];
      Cmd.Parameters.ParamByName('user_id').Value := Users.I['id'];
  try
    Cmd.Execute;
    Result := true;
  except
    Result := false;
  end;
end;
finally
if Assigned(Connection) then Connection.Free;
end;
end;

    回答不易，还请采纳！！！

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(8条)

报告相同问题？

问题事件

  • 系统已结题 3月20日
  • 已采纳回答 3月12日
  • 创建了问题 3月12日

悬赏问题

  • ¥50 关于#html5#的问题：H5页面用户手机返回的时候跳转到指定页面例如(语言-javascript)
  • ¥15 无法使用此凭据登录，因为你的域不可用，如何解决？(标签-Windows)
  • ¥15 yolov9的训练时间
  • ¥15 二叉树遍历没有报错但无法正常运行
  • ¥15 在linux系统下vscode运行robocup3d上场球员报错
  • ¥15 Python语言实验
  • ¥15 SAP HANA SQL 增加合计行
  • ¥20 用C#语言解决一个英文打字练习器，有偿
  • ¥15 srs-sip外部服务 webrtc支持H265格式
  • ¥15 在使用abaqus软件中，继承到assembly里的surfaces怎么使用python批量调动