处理 Oracle 数据库中的大量列
4.60/5 (3投票s)
在本文中，
介绍
如今，数据库是应用程序的一个重要因素。 数据库日复一日变得更加复杂。 在本文中，我想提供一个解决方案来处理具有大量列的 Oracle 数据库。
根据 Oracle 限制文档，我们应该记住，在 Oracle 数据库中，一个表最多不能超过 1000 列。
我们的解决方案是使用我们当前项目中的一个真实示例，它将 Iso8583 格式存储到 Oracle 数据库中。 在金融领域工作的人会知道 Iso8583 格式及其用途。 但仅将 Iso8583 格式视为一个由 128 个 String 数组值组成的数组，我们将把 iso 字段的 128 个值存储到包含 128 列的表中
设计与实现
数据库设计
首先，我们将创建一个具有这种类型的 Oracle 数据库
create or replace
type ISOMESSAGE_ARRAY
as table of varchar2(1000);
因为 Oracle 只能支持包含不超过 1000 列的表，所以我们只创建一个包含 1000 个 varchar2 元素的数组类型
第二件事，我们将创建一个包含 128 列的 TBLISO 来存储 ISO8583 数据。 通过运行此代码
CREATE TABLE "TBLISO"
( "MSGIN_ID" NUMBER(10,0),
"MSGHEADER" VARCHAR2(100 BYTE),
"BITMAP" VARCHAR2(32 BYTE),
"F2_PAN" VARCHAR2(19 BYTE),
"F3_PROCESSING_CODE" VARCHAR2(6 BYTE),
"F4_AMOUNT_TRANS" VARCHAR2(12 BYTE),
"F5_AMOUNT_SETT" VARCHAR2(12 BYTE),
"F6_AMOUNT_CARDHOLDER_BILL" VARCHAR2(12 BYTE),
"F7_TRANSMISSION_DATETIME" VARCHAR2(10 BYTE),
"F8_AMOUNT_CARDHOLDER_BILL_FEE" VARCHAR2(8 BYTE),
"F9_CONVERSION_RATE_SETT" VARCHAR2(8 BYTE),
"F10_CONVERSION_RATE_CARDBILL" VARCHAR2(8 BYTE),
"F11_SYSTEM_TRACE" VARCHAR2(6 BYTE),
"F12_LOCAL_TIME" VARCHAR2(6 BYTE),
"F13_LOCAL_DATE" VARCHAR2(4 BYTE),
"F14_DATE_EXPIRATION" VARCHAR2(4 BYTE),
"F15_DATE_SETT" VARCHAR2(4 BYTE),
"F16_DATE_CONVERSION" VARCHAR2(4 BYTE),
"F17_DATE_CAPTURE" VARCHAR2(4 BYTE),
"F18_MERCHANT_TYPE" VARCHAR2(4 BYTE),
"F19_ACQ_INST_COUNTRY_CODE" VARCHAR2(3 BYTE),
"F20_PRIMARY_ACC_NUM_EXT" VARCHAR2(3 BYTE),
"F21_FORWD_INST_COUNTRY_CODE" VARCHAR2(3 BYTE),
"F22_POS_ENTRY_MODE" VARCHAR2(3 BYTE),
"F23_CARD_SEQ_NUM" VARCHAR2(3 BYTE),
"F24_NETW_INTER_IDENTIFY" VARCHAR2(3 BYTE),
"F25_POS_CONDITION_CODE" VARCHAR2(2 BYTE),
"F26_POS_PIN_CAPTURE_CODE" VARCHAR2(2 BYTE),
"F27_AUTHO_IDEN_RESP_LENGTH" VARCHAR2(1 BYTE),
"F28_AMOUNT_TRANS_FEE" VARCHAR2(9 BYTE),
"F29_AMOUNT_SETT_FEE" VARCHAR2(9 BYTE),
"F30_AMOUNT_TRANS_PROCESS_FEE" VARCHAR2(9 BYTE),
"F31_AMOUNT_SETT_PROCESS_FEE" VARCHAR2(9 BYTE),
"F32_ACQ_INST_IDEN_CODE" VARCHAR2(11 BYTE),
"F33_FORW_INST_IDEN_CODE" VARCHAR2(11 BYTE),
"F34_PAN_EXTEND" VARCHAR2(28 BYTE),
"F35_TRACK2" VARCHAR2(37 BYTE),
"F36_TRACK3" VARCHAR2(104 BYTE),
"F37_RETRIEVAL_REF_NUM" VARCHAR2(12 BYTE),
"F38_AUTHO_IDEN_RESP" VARCHAR2(20 BYTE),
"F39_RESP_CODE" VARCHAR2(2 BYTE),
"F40_SERV_RESTRICT_CODE" VARCHAR2(3 BYTE),
"F41_CARD_ACCEPT_TERM_IDEN" VARCHAR2(20 BYTE),
"F42_CARD_ACCEPT_IDEN_CODE" VARCHAR2(15 BYTE),
"F43_CARD_ACCCEPT_NAME" VARCHAR2(40 BYTE),
"F44_ADD_RESP_DATA" VARCHAR2(25 BYTE),
"F45_TRACK1" VARCHAR2(79 BYTE),
"F46_ADD_DATA_ISO" VARCHAR2(999 BYTE),
"F47_ADD_DATA_NATIONAL" VARCHAR2(999 BYTE),
"F48_ADD_DATA_PRIVATE" VARCHAR2(999 BYTE),
"F49_CURRENCY_CODE_TRANS" VARCHAR2(3 BYTE),
"F50_CURRENCY_CODE_SETT" VARCHAR2(3 BYTE),
"F51_CURRENCY_CODE_CARDHOLD" VARCHAR2(3 BYTE),
"F52_PIN_DATA" VARCHAR2(16 BYTE),
"F53_SECUR_RELATE_CONTROL_INFO" VARCHAR2(16 BYTE),
"F54_ADD_AMOUNT" VARCHAR2(120 BYTE),
"F55_ICC_SYS_RELATE_DATA" VARCHAR2(255 BYTE),
"F56_RESERVED_ISO" VARCHAR2(999 BYTE),
"F57_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F58_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F59_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F60_ADVICE_REASON_CODE" VARCHAR2(60 BYTE),
"F61_POS_DATA" VARCHAR2(26 BYTE),
"F62_INF_DATA" VARCHAR2(50 BYTE),
"F63_NETWORK_DATA" VARCHAR2(44 BYTE),
"F64_MAC" VARCHAR2(16 BYTE),
"F65_BITMAP_EXTEND" VARCHAR2(16 BYTE),
"F66_SETT_CODE" VARCHAR2(1 BYTE),
"F67_EXTEND_PAYMENT_CODE" VARCHAR2(2 BYTE),
"F68_RECEIV_INST_COUNTRY_CODE" VARCHAR2(3 BYTE),
"F69_SETT_INST_COUNTRY_CODE" VARCHAR2(3 BYTE),
"F70_NETW_MANG_INFO_CODE" VARCHAR2(3 BYTE),
"F71_MESSAGE_NUM" VARCHAR2(4 BYTE),
"F72_MESSAGE_NUM_LAST" VARCHAR2(4 BYTE),
"F73_DATE_ACTION" VARCHAR2(6 BYTE),
"F74_CREDIT_NUM" VARCHAR2(10 BYTE),
"F75_CREDIT_REVRS_NUM" VARCHAR2(10 BYTE),
"F76_DEBIT_NUM" VARCHAR2(10 BYTE),
"F77_DEBIT_REVRS_NUM" VARCHAR2(10 BYTE),
"F78_TRANSFER_NUM" VARCHAR2(10 BYTE),
"F79_TRANSFER_REVRS_NUM" VARCHAR2(10 BYTE),
"F80_INQUIRIES_NUM" VARCHAR2(10 BYTE),
"F81_AUTHO_NUM" VARCHAR2(10 BYTE),
"F82_CREDIT_PROCESS_FEE_AMOUNT" VARCHAR2(12 BYTE),
"F83_CREDIT_TRANS_FEE_AMOUNT" VARCHAR2(12 BYTE),
"F84_DEBIT_PROCESS_FEE_AMOUNT" VARCHAR2(12 BYTE),
"F85_DEBIT_TRANS_FEE_AMOUNT" VARCHAR2(12 BYTE),
"F86_CREDIT_AMOUNT" VARCHAR2(16 BYTE),
"F87_CREDIT_REVRS_AMOUNT" VARCHAR2(16 BYTE),
"F88_DEBIT_AMOUNT" VARCHAR2(16 BYTE),
"F89_DEBIT_REVRS_AMOUNT" VARCHAR2(16 BYTE),
"F90_ORIGI_DATA_ELEMNT" VARCHAR2(42 BYTE),
"F91_FILE_UPDATE_CODE" VARCHAR2(1 BYTE),
"F92_FILE_SECUR_CODE" VARCHAR2(2 BYTE),
"F93_RESP_INDICATOR" VARCHAR2(5 BYTE),
"F94_SERVICE_INDICATOR" VARCHAR2(7 BYTE),
"F95_REPLACEMENT_AMOUNT" VARCHAR2(42 BYTE),
"F96_MESAGE_SECUR_CODE" VARCHAR2(16 BYTE),
"F97_AMOUNT_NET_SETT" VARCHAR2(17 BYTE),
"F98_PAYEE" VARCHAR2(25 BYTE),
"F99_SETT_INST_IDEN_CODE" VARCHAR2(11 BYTE),
"F100_RECEIVE_INST_IDEN_CODE" VARCHAR2(11 BYTE),
"F101_FILE_NAME" VARCHAR2(17 BYTE),
"F102_ACCOUNT_IDEN_1" VARCHAR2(28 BYTE),
"F103_ACCOUNT_IDEN_2" VARCHAR2(28 BYTE),
"F104_TRANS_DESCP" VARCHAR2(100 BYTE),
"F105_RESERVED_ISO" VARCHAR2(999 BYTE),
"F106_RESERVED_ISO" VARCHAR2(999 BYTE),
"F107_RESERVED_ISO" VARCHAR2(999 BYTE),
"F108_RESERVED_ISO" VARCHAR2(999 BYTE),
"F109_RESERVED_ISO" VARCHAR2(999 BYTE),
"F110_RESERVED_ISO" VARCHAR2(999 BYTE),
"F111_RESERVED_ISO" VARCHAR2(999 BYTE),
"F112_PARCELAS_DATA" VARCHAR2(248 BYTE),
"F113_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F114_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F115_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F116_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F117_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F118_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F119_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F120_RECORD_DATA" VARCHAR2(999 BYTE),
"F121_AUTHO_AGENT_IDEN_CODE" VARCHAR2(11 BYTE),
"F122_ADD_RECORD_DATA" VARCHAR2(100 BYTE),
"F123_RESERVED_FUTURE" VARCHAR2(999 BYTE),
"F124_RESERVED_FUTURE" VARCHAR2(999 BYTE),
"F125_RESERVED_FUTURE" VARCHAR2(999 BYTE),
"F126_RESERVED_PRIVATE" VARCHAR2(999 BYTE),
"F127_PRIVATE_DATA" VARCHAR2(50 BYTE),
"F128_MAC" VARCHAR2(16 BYTE)
)
我们添加了 MSGIN_ID 列作为表的主键，将使用一个序列在每次需要插入新的 iso8583 数据时生成主键
不要忘记创建一个序列来生成主键
CREATE SEQUENCE "SEQ_ISO" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 101 CACHE 100 NOORDER NOCYCLE ;
最后，我们需要的是一个存储过程来将数据插入到表中。 有几种方法可以创建这个存储过程。 正常的方法是我们创建一个具有 128 个输入参数的存储过程，并在 insert 语句中使用它。 但是，如果出现错误，这种方法将花费开发人员大量的时间来编写和调试。 所以，我将使用更简单的方法将参数传递给存储过程：数组输入参数解决方案
让我们运行此代码以在 Oracle 数据库中创建包头
create or replace
package isopkg as
procedure insert_iso(in_isomsg IN isomessage_array);
end isopkg;
让我们运行此代码以在 Oracle 数据库中创建包体
create or replace
package body isopkg as
procedure insert_iso(in_isomsg IN isomessage_array) AS
new_id_in_msg number(10,0);
begin
select seq_iso.nextval into new_id_in_msg from dual;
INSERT INTO "TBLiso"
VALUES
(
new_id_in_msg,
'0000', --as Header
in_isomsg(2),--Bitmap
in_isomsg(3),--PAN
in_isomsg(4),
in_isomsg(5),
in_isomsg(6),
in_isomsg(7),
in_isomsg(8),
in_isomsg(9),
in_isomsg(10),
in_isomsg(11),
in_isomsg(12),
in_isomsg(13),
in_isomsg(14),
in_isomsg(15),
in_isomsg(16),
in_isomsg(17),
in_isomsg(18),
in_isomsg(19),
in_isomsg(20),
in_isomsg(21),
in_isomsg(22),
in_isomsg(23),
in_isomsg(24),
in_isomsg(25),
in_isomsg(26),
in_isomsg(27),
in_isomsg(28),
in_isomsg(29),
in_isomsg(30),
in_isomsg(31),
in_isomsg(32),
in_isomsg(33),
in_isomsg(34),
in_isomsg(35),
in_isomsg(36),
in_isomsg(37),
in_isomsg(38),
in_isomsg(39),
in_isomsg(40),
in_isomsg(41),
in_isomsg(42),
in_isomsg(43),
in_isomsg(44),
in_isomsg(45),
in_isomsg(46),
in_isomsg(47),
in_isomsg(48),
in_isomsg(49),
in_isomsg(50),
in_isomsg(51),
in_isomsg(52),
in_isomsg(53),
in_isomsg(54),
in_isomsg(55),
in_isomsg(56),
in_isomsg(57),
in_isomsg(58),
in_isomsg(59),
in_isomsg(60),
in_isomsg(61),
in_isomsg(62),
in_isomsg(63),
in_isomsg(64),
in_isomsg(65),
in_isomsg(66),
in_isomsg(67),
in_isomsg(68),
in_isomsg(69),
in_isomsg(70),
in_isomsg(71),
in_isomsg(72),
in_isomsg(73),
in_isomsg(74),
in_isomsg(75),
in_isomsg(76),
in_isomsg(77),
in_isomsg(78),
in_isomsg(79),
in_isomsg(80),
in_isomsg(81),
in_isomsg(82),
in_isomsg(83),
in_isomsg(84),
in_isomsg(85),
in_isomsg(86),
in_isomsg(87),
in_isomsg(88),
in_isomsg(89),
in_isomsg(90),
in_isomsg(91),
in_isomsg(92),
in_isomsg(93),
in_isomsg(94),
in_isomsg(95),
in_isomsg(96),
in_isomsg(97),
in_isomsg(98),
in_isomsg(99),
in_isomsg(100),
in_isomsg(101),
in_isomsg(102),
in_isomsg(103),
in_isomsg(104),
in_isomsg(105),
in_isomsg(106),
in_isomsg(107),
in_isomsg(108),
in_isomsg(109),
in_isomsg(110),
in_isomsg(111),
in_isomsg(112),
in_isomsg(113),
in_isomsg(114),
in_isomsg(115),
in_isomsg(116),
in_isomsg(117),
in_isomsg(118),
in_isomsg(119),
in_isomsg(120),
in_isomsg(121),
in_isomsg(122),
in_isomsg(123),
in_isomsg(124),
in_isomsg(125),
in_isomsg(126),
in_isomsg(127),
in_isomsg(128),
in_isomsg(129)
);
end insert_iso;
end isopkg;
实现
以上，我们在表 TBLISO 中创建了具有大量列的数据库。 在本节中，我们将创建一个 Java 应用程序来处理这个数据库
我们将使用 Oracle Thin Driver 来处理 Oracle 数据库。 示例应用程序将包含在本节中，或者您可以在上面下载它。
我们将在 cfg 文件夹中有 2 个配置文件
Dbcdg.txt：包含数据库连接信息
jdbc:oracle:thin:@[iphost]:[port]:[sid]
user
pass
iso8583data.txt 存储 Iso 8583 数据。 其结构将如下所示
0: 420
1: F2B900018E80C4000000000004000000
2: 9876543210123456
3: 351000
4: 000000000000
7: 0418055231
9: 20000000
11: 300422
12: 130527
13: 0418
16: 0418
32: 998866
33: 704001
37: 000000224003
38: 012345
39: 05
41: 00001100
49: 704
50: 840
54: 0000000000000000000000000000000000000000
102: 00000000000000000000
如上所述，Iso8583 格式有 128 个数据字段。 每一行将为一个字段设置一个值。 上面的“:”字符串是字段索引，其后是 Iso 字段值。 您可以从该文件中添加/删除字段，但始终参考 ISO 格式文档以确保数据能够正确存储到表中对应的列中
参考 http://en.wikipedia.org/wiki/ISO_8583 以便为 Iso 字段创建良好的数据范围
现在我们开始“编码”。
使用代码
在 Java 中创建类 FileReader
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package iso8583db;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author minhdbh
*/
public class FileReader {
private String filePath;
private String[] lineData;
public FileReader() {
filePath = "";
lineData = new String[0];
}
public void setFilePath(String filePath) {
this.filePath = filePath;
loadData();
}
public String[] getLineData() {
return lineData;
}
/**
* I find the samples code to read text file on the internet :)
*
*/
private void loadData() {
try {
FileInputStream fstream = new FileInputStream(filePath);
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
lineData=new String[getLines(new File(filePath))];
int iLine=0;
String iData="";
while ((iData = br.readLine()) != null) {
System.out.println(String.format("Line %s: %s", iLine,iData));
lineData[iLine]= iData;
iLine++;
}
in.close();
} catch (Exception e) {
System.err.println("Error: " + e.getMessage());
}
}
/**
* Use only when file is iso8583data, it will return an array of 128 elements correspondence with Iso format
* @return
*/
public String[] toIsoArray() {
String[] isoValueArray = new String[129];
for (int i = 0; i < 129; i++) {
isoValueArray[i] = "";
}
if (lineData.length>0)
{
for(int iField=0;iField<lineData.length;iField++)
{
String isoField[]=lineData[iField].split(": ");
isoValueArray[Integer.parseInt(isoField[0])]=isoField[1];
}
}
return isoValueArray;
}
/**
* Get total lines in a text file
* This function I find on internet
* @param aFile: pointer to text file
* @return
*/
private int getLines(File aFile) {
LineNumberReader reader = null;
try {
reader = new LineNumberReader(new java.io.FileReader(aFile));
while ((reader.readLine()) != null);
return reader.getLineNumber();
} catch (Exception ex) {
return -1;
} finally {
if (reader != null) {
try {
reader.close();
} catch (IOException ex) {
Logger.getLogger(FileReader.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
}创建类 dbLib
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package iso8583db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
/**
*
* @author minhdbh
*/
public class dbLib {
public static boolean insertIsoMsg(Connection cnn, String[] datainput)
{
CallableStatement proc = null;
boolean flag = true;
try{
String temp = "{ call isopkg.insert_iso(?)}";
proc = cnn.prepareCall(temp);
ArrayDescriptor para2Des = new ArrayDescriptor("ISOMESSAGE_ARRAY", cnn);
ARRAY para2 = new ARRAY(para2Des,cnn,datainput);
proc.setArray(1,para2);
proc.execute();
System.out.println("Insert to DB successfully");
}
catch(Exception ex)
{
flag=false;
System.out.println("Cannot insert to DB");
}
finally
{
if (proc != null){
try {
proc.close();
} catch (SQLException se) {
}
}
}
return flag;
}
}
并且在项目的主函数中，我们将添加此代码
public static void main(String[] args) {
// TODO code application logic here
FileReader dbCfg = new FileReader();
dbCfg.setFilePath("cfg/dbcdg.txt");
Connection conn=null;
String driver = "oracle.jdbc.driver.OracleDriver";
try {
Class.forName(driver); // load Oracle driver
conn = DriverManager.getConnection(dbCfg.getLineData()[0], dbCfg.getLineData()[1], dbCfg.getLineData()[2]);
} catch (Exception ex) {
Logger.getLogger(Iso8583DB.class.getName()).log(Level.SEVERE, null, ex);
}
while (true) {
FileReader isoData = new FileReader();
isoData.setFilePath("cfg/iso8583data.txt");
dbLib.insertIsoMsg(conn, isoData.toIsoArray());
try {
Thread.sleep(1000);
} catch (InterruptedException ex) {
Logger.getLogger(Iso8583DB.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
我使用了一个循环和 Sleep 方法，当应用程序运行时，我们可以编辑文件 iso8583data 中的数据，以便将新更改应用到数据库！
结论
这是一个处理 Oracle 中大量列的简单方法。 它将使代码更清晰，更易于调试和开发。 希望它对您有意义！