JavaDB(Derby)数据库批量SQL

最新推荐文章于 2024-03-06 11:24:03 发布
最新推荐文章于 2024-03-06 11:24:03 发布
阅读量2.1k 收藏 1
点赞数
分类专栏： 数据库 文章标签： derby cloudspace DB2 SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gsls200808/article/details/46529387
版权

大二的时候有门课RAD(IBM的一个软件)，当时用的RAD6.0里面内置了一个数据库cloudspace，网上一搜，这个cloudspace就是JavaDB(Derby)数据库的前身，当时由于种种原因，cloudspace图形界面出不来，后来直接想到直接用命令导入，下面介绍方法

方法很简单，命令是

ij 脚本名

ij命令中每条命令都加分号;否则无法执行下一条命令

cloudscape批量SQL(内嵌模式)

切换工作目录.bat

cd C:\Program Files (x86)\IBM\Rational\ADTrial\6.0\runtimes\base_v6\cloudscape
::cd C:\Program Files\IBM\Rational\SDP\6.0\runtimes\base_v6\cloudscape
创建表结构.bat 

call 切换工作目录.bat
call bin\embedded\ij %~dp0\ejbbank.ddl
@pause
ejbbank.dll 

connect 'jdbc:db2j:EJBBANK;create=true';

DROP TABLE ITSO.CUSTOMER;
DROP TABLE ITSO.CUSTADDRESS;
DROP TABLE ITSO.CUSTACCT;
DROP TABLE ITSO.ACCOUNT;
DROP TABLE ITSO.CHECKING;
DROP TABLE ITSO.SAVINGS;
DROP TABLE ITSO.TRANSRECORD;
DROP TABLE ITSO.CUSTOMERINFO;

CREATE TABLE ITSO.CUSTOMER (                                
   customerid    INTEGER     NOT NULL,                      
   title         CHAR(3)     NOT NULL,                      
   firstName     VARCHAR(30) NOT NULL,                      
   lastName      VARCHAR(30) NOT NULL,                      
   userID        CHAR(8),                                   
   password      CHAR(8),                                   
   address       BLOB(2000),                                
                             PRIMARY KEY (CUSTOMERID)       
);

CREATE TABLE ITSO.CUSTADDRESS (                             
   customerid    INTEGER     NOT NULL,                      
   street        CHAR(20),                                  
   city          CHAR(12),                                  
   state         CHAR(12),                                  
   zipcode       CHAR(10),                                  
                             PRIMARY KEY (CUSTOMERID)       
);

CREATE TABLE ITSO.CUSTACCT (                                
   customerid    INTEGER     NOT NULL,                      
   accID         CHAR(8)     NOT NULL,                      
                             PRIMARY KEY (CUSTOMERID,ACCID) 
);

CREATE TABLE ITSO.ACCOUNT (                                 
   accid         CHAR(8)     NOT NULL,                      
   balance       DEC(8,2)    NOT NULL DEFAULT 0.00,         
   interest      INTEGER     NOT NULL DEFAULT 5,            
   acctype       VARCHAR(8)  NOT NULL DEFAULT 'ACCOUNT',    
   discriminator CHAR(1)     NOT NULL DEFAULT 'A',          
   overdraft     DEC(8,2)    NOT NULL DEFAULT 200.00,       
   minamount     DEC(8,2)    NOT NULL DEFAULT 100.00,       
                             PRIMARY KEY (ACCID)            
);

CREATE TABLE ITSO.CHECKING (                                
   accid         CHAR(8)     NOT NULL,                      
   overdraft     DEC(8,2)    NOT NULL DEFAULT 200.00,       
                             PRIMARY KEY (ACCID)            
);

CREATE TABLE ITSO.SAVINGS (                                 
   accid         CHAR(8)     NOT NULL,                      
   minamount     DEC(8,2)    NOT NULL DEFAULT 100.00,       
                             PRIMARY KEY (ACCID)            
);

CREATE TABLE ITSO.TRANSRECORD (                             
   transid       TIMESTAMP   NOT NULL,                      
   accid         CHAR(8)             ,                      
   transtype     CHAR(1)     NOT NULL,                      
   transamt      DEC(8,2)    NOT NULL,                      
                             PRIMARY KEY (TRANSID)          
);

CREATE TABLE ITSO.CUSTOMERINFO (                             
   customerid    INTEGER     NOT NULL,                      
   infoid        INTEGER     NOT NULL,                      
   description   VARCHAR(50),                               
   data          BLOB(10K),                                 
                           PRIMARY KEY (CUSTOMERID, INFOID) 
);
插入表.bat 
call 切换工作目录.bat
call bin\embedded\ij %~dp0\ejbbank.sql
@pause

ejbbank.sql

connect 'jdbc:db2j:EJBBANK';
                           
INSERT INTO ITSO.CUSTOMER                 
   (customerid, title, firstname, lastname, userid, password) VALUES  
   (101, 'Mr', 'Maik',    'Schumacher', 'cust101', 'MS'),             
   (102, 'Mr', 'Fabio',   'Ferraz',     'cust102', 'FF'),             
   (103, 'Mr', 'Henrik',  'Sjostrand',  'cust103', 'HS'),             
   (104, 'Mr', 'Ian',     'Brown',      'cust104', 'IB'),             
   (105, 'Ms', 'Unknown', 'Lady',        null,      null),            
   (106, 'Mr', 'Ueli',    'Wahli',      'cust106', 'UW') ;

INSERT INTO ITSO.CUSTADDRESS                           
   (customerid, street, city, state, zipcode) VALUES   
   (101, 'A St', 'A village',  'Germany',    '11111'), 
   (102, 'B Bl', 'B big City', 'Brazil',     '22222'), 
   (103, 'C Rd', 'C City',     'Sweden',     '33333'), 
   (104, 'D Ln', 'D Metro',    'Canada',     '44444'), 
   (105, 'E Ct', 'E Farm',     'Hawaii',     '55555'), 
   (106, 'F Av', 'F Town',     'California', '66666');


INSERT INTO ITSO.ACCOUNT                                     
   (accid, balance, interest, acctype, discriminator) VALUES 
   ('101-1001',   80.00, 4, 'CHECKING', 'C'),                
   ('101-1002',  375.26, 5, 'SAVINGS',  'S'),                
   ('102-2001', 9375.26, 5, 'SAVINGS',  'S'),                
   ('102-2002',   75.50, 3, 'CHECKING', 'C'),                
   ('103-3001',  100.00, 6, 'SAVINGS',  'S'),                
   ('104-4001',  888.88, 4, 'SAVINGS',  'S'),                
   ('104-4002',   88.88, 4, 'CHECKING', 'C'),                
   ('105-5001',    0.00, 2, 'CHECKING', 'C'),                
   ('106-6001', 1000.00, 3, 'CHECKING', 'C'),                
   ('106-6002', 2000.00, 4, 'SAVINGS',  'S'),                
   ('106-6003', 3000.00, 6, 'SAVINGS',  'S');

INSERT INTO ITSO.CHECKING    
   (accid, overdraft) VALUES 
   ('101-1001',200.00),      
   ('102-2002',200.00),      
   ('104-4002',250.00),      
   ('105-5001',200.00),      
   ('106-6001',300.00);

INSERT INTO ITSO.SAVINGS     
   (accid, minamount) VALUES 
   ('101-1002',100.00),      
   ('102-2001',100.00),      
   ('103-3001',150.00),      
   ('104-4001',200.00),      
   ('106-6002',100.00),      
   ('106-6003',250.00);


INSERT INTO ITSO.CUSTACCT                               
   (customerid, accid) VALUES                           
   (101,'101-1001'), (101,'101-1002'),                  
   (102,'102-2001'), (102,'102-2002'),                  
   (103,'103-3001'),                                    
   (104,'104-4001'), (104,'104-4002'),                  
   (105,'105-5001'),                                    
   (106,'106-6001'), (106,'106-6002'), (106,'106-6003'), (106,'105-5001');


INSERT INTO ITSO.TRANSRECORD                                    
   (transid,           accid,      transtype, transamt) VALUES  
   (CURRENT TIMESTAMP, '101-1001', 'C',         80.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '101-1002', 'D',        200.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '102-2001', 'C',       1000.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '102-2002', 'D',         70.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '103-3001', 'C',        100.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '104-4001', 'C',         88.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '104-4002', 'C',         88.88 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '106-6001', 'D',         66.66 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '106-6001', 'C',         10.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '106-6002', 'C',         66.66 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '106-6003', 'C',       3000.00 );


INSERT INTO ITSO.CUSTOMERINFO                     
   (customerid, infoid, description,data) VALUES  
   (101, 1, 'Picture', blob('xxxxxxxxxx')),       
   (101, 2, 'Scanned image', blob('yyyyyy')) ;
查询.bat 
call 切换工作目录.bat
call bin\embedded\ij %~dp0\ejbbank.list
@pause
ejbbank.list 

connect 'jdbc:db2j:EJBBANK';
SELECT customerid, title, char(firstname,10) as FIRSTNAME, char(lastname,10) as LASTNAME, userid, password from itso.customer;
SELECT * from itso.custaddress;
select customerid, infoid, char(description,15) as DESCRIPTION, length(data) as DATALENGTH, blob(data,10) as DATAEXTRACT from itso.customerinfo;
SELECT * from itso.custacct;
SELECT * from itso.account;
SELECT * from itso.checking;
SELECT * from itso.savings;
SELECT * from itso.transrecord;
exit;

Derby批量SQL(内嵌模式)

先开启Javadb服务
然后创建、插入、查询


开启Javadb服务.bat

::开启Javadb服务
java -jar %java_db%\lib\derbyrun.jar server start
@pause
关闭Javadb服务.bat 

::关闭javadb服务
java -jar %java_db%\lib\derbyrun.jar server shutdown
@pause

创建表结构.bat 

call ij -f ejbbank.dll
@pause

ejbbank.ddl

connect 'jdbc:derby:EJBBANK;create=true'; 

DROP TABLE ITSO.CUSTOMER;
DROP TABLE ITSO.CUSTADDRESS;
DROP TABLE ITSO.CUSTACCT;
DROP TABLE ITSO.ACCOUNT;
DROP TABLE ITSO.CHECKING;
DROP TABLE ITSO.SAVINGS;
DROP TABLE ITSO.TRANSRECORD;
DROP TABLE ITSO.CUSTOMERINFO;



CREATE TABLE ITSO.CUSTOMER (                                
   customerid    INTEGER     NOT NULL,                      
   title         CHAR(3)     NOT NULL,                      
   firstName     VARCHAR(30) NOT NULL,                      
   lastName      VARCHAR(30) NOT NULL,                      
   userID        CHAR(8),                                   
   password      CHAR(8),                                   
   address       BLOB(2000),                                
                             PRIMARY KEY (CUSTOMERID)       
);

CREATE TABLE ITSO.CUSTADDRESS (                             
   customerid    INTEGER     NOT NULL,                      
   street        CHAR(20),                                  
   city          CHAR(12),                                  
   state         CHAR(12),                                  
   zipcode       CHAR(10),                                  
                             PRIMARY KEY (CUSTOMERID)       
);

CREATE TABLE ITSO.CUSTACCT (                                
   customerid    INTEGER     NOT NULL,                      
   accID         CHAR(8)     NOT NULL,                      
                             PRIMARY KEY (CUSTOMERID,ACCID) 
);

CREATE TABLE ITSO.ACCOUNT (                                 
   accid         CHAR(8)     NOT NULL,                      
   balance       DEC(8,2)    NOT NULL DEFAULT 0.00,         
   interest      INTEGER     NOT NULL DEFAULT 5,            
   acctype       VARCHAR(8)  NOT NULL DEFAULT 'ACCOUNT',    
   discriminator CHAR(1)     NOT NULL DEFAULT 'A',          
   overdraft     DEC(8,2)    NOT NULL DEFAULT 200.00,       
   minamount     DEC(8,2)    NOT NULL DEFAULT 100.00,       
                             PRIMARY KEY (ACCID)            
);

CREATE TABLE ITSO.CHECKING (                                
   accid         CHAR(8)     NOT NULL,                      
   overdraft     DEC(8,2)    NOT NULL DEFAULT 200.00,       
                             PRIMARY KEY (ACCID)            
);

CREATE TABLE ITSO.SAVINGS (                                 
   accid         CHAR(8)     NOT NULL,                      
   minamount     DEC(8,2)    NOT NULL DEFAULT 100.00,       
                             PRIMARY KEY (ACCID)            
);

CREATE TABLE ITSO.TRANSRECORD (                             
   transid       TIMESTAMP   NOT NULL,                      
   accid         CHAR(8)             ,                      
   transtype     CHAR(1)     NOT NULL,                      
   transamt      DEC(8,2)    NOT NULL,                      
                             PRIMARY KEY (TRANSID)          
);

CREATE TABLE ITSO.CUSTOMERINFO (                             
   customerid    INTEGER     NOT NULL,                      
   infoid        INTEGER     NOT NULL,                      
   description   VARCHAR(50),                               
   data          BLOB(10K),                                 
                           PRIMARY KEY (CUSTOMERID, INFOID) 
);
exit;

插入表.bat 

call ij -f ejbbank.sql
@pause

ejbbank.sql 

connect 'jdbc:derby:EJBBANK';
                          
INSERT INTO ITSO.CUSTOMER                 
   (customerid, title, firstname, lastname, userid, password) VALUES  
   (101, 'Mr', 'Maik',    'Schumacher', 'cust101', 'MS'),             
   (102, 'Mr', 'Fabio',   'Ferraz',     'cust102', 'FF'),             
   (103, 'Mr', 'Henrik',  'Sjostrand',  'cust103', 'HS'),             
   (104, 'Mr', 'Ian',     'Brown',      'cust104', 'IB'),             
   (105, 'Ms', 'Unknown', 'Lady',        null,      null),            
   (106, 'Mr', 'Ueli',    'Wahli',      'cust106', 'UW') ;

INSERT INTO ITSO.CUSTADDRESS                           
   (customerid, street, city, state, zipcode) VALUES   
   (101, 'A St', 'A village',  'Germany',    '11111'), 
   (102, 'B Bl', 'B big City', 'Brazil',     '22222'), 
   (103, 'C Rd', 'C City',     'Sweden',     '33333'), 
   (104, 'D Ln', 'D Metro',    'Canada',     '44444'), 
   (105, 'E Ct', 'E Farm',     'Hawaii',     '55555'), 
   (106, 'F Av', 'F Town',     'California', '66666');


INSERT INTO ITSO.ACCOUNT                                     
   (accid, balance, interest, acctype, discriminator) VALUES 
   ('101-1001',   80.00, 4, 'CHECKING', 'C'),                
   ('101-1002',  375.26, 5, 'SAVINGS',  'S'),                
   ('102-2001', 9375.26, 5, 'SAVINGS',  'S'),                
   ('102-2002',   75.50, 3, 'CHECKING', 'C'),                
   ('103-3001',  100.00, 6, 'SAVINGS',  'S'),                
   ('104-4001',  888.88, 4, 'SAVINGS',  'S'),                
   ('104-4002',   88.88, 4, 'CHECKING', 'C'),                
   ('105-5001',    0.00, 2, 'CHECKING', 'C'),                
   ('106-6001', 1000.00, 3, 'CHECKING', 'C'),                
   ('106-6002', 2000.00, 4, 'SAVINGS',  'S'),                
   ('106-6003', 3000.00, 6, 'SAVINGS',  'S');

INSERT INTO ITSO.CHECKING    
   (accid, overdraft) VALUES 
   ('101-1001',200.00),      
   ('102-2002',200.00),      
   ('104-4002',250.00),      
   ('105-5001',200.00),      
   ('106-6001',300.00);

INSERT INTO ITSO.SAVINGS     
   (accid, minamount) VALUES 
   ('101-1002',100.00),      
   ('102-2001',100.00),      
   ('103-3001',150.00),      
   ('104-4001',200.00),      
   ('106-6002',100.00),      
   ('106-6003',250.00);


INSERT INTO ITSO.CUSTACCT                               
   (customerid, accid) VALUES                           
   (101,'101-1001'), (101,'101-1002'),                  
   (102,'102-2001'), (102,'102-2002'),                  
   (103,'103-3001'),                                    
   (104,'104-4001'), (104,'104-4002'),                  
   (105,'105-5001'),                                    
   (106,'106-6001'), (106,'106-6002'), (106,'106-6003'), (106,'105-5001');


INSERT INTO ITSO.TRANSRECORD                                    
   (transid,           accid,      transtype, transamt) VALUES  
   (CURRENT TIMESTAMP, '101-1001', 'C',         80.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '101-1002', 'D',        200.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '102-2001', 'C',       1000.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '102-2002', 'D',         70.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '103-3001', 'C',        100.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '104-4001', 'C',         88.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '104-4002', 'C',         88.88 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '106-6001', 'D',         66.66 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '106-6001', 'C',         10.00 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '106-6002', 'C',         66.66 );
INSERT INTO ITSO.TRANSRECORD                            VALUES  
   (CURRENT TIMESTAMP, '106-6003', 'C',       3000.00 );


INSERT INTO ITSO.CUSTOMERINFO                     
   (customerid, infoid, description,data) VALUES  
   (101, 1, 'Picture', blob('xxxxxxxxxx')),       
   (101, 2, 'Scanned image', blob('yyyyyy')) ;
查询.bat 

call ij -f ejbbank.list
@pause

ejbbank.list

connect 'jdbc:derby:EJBBANK';  
SELECT customerid, title, char(firstname,10) as FIRSTNAME, char(lastname,10) as LASTNAME, userid, password from itso.customer;
SELECT * from itso.custaddress;
select customerid, infoid, char(description,15) as DESCRIPTION, length(data) as DATALENGTH, blob(data,10) as DATAEXTRACT from itso.customerinfo;
SELECT * from itso.custacct;
SELECT * from itso.account;
SELECT * from itso.checking;
SELECT * from itso.savings;
SELECT * from itso.transrecord;
exit;

DB2数据库批量SQL

createbank.bat

db2cmd.exe db2setcp.bat db2 -f ejbbank.ddl
ejbbank.ddl 

echo --- create the EJBBANK database ---
CREATE DATABASE EJBBANK

echo --- connect to EJBBANK database ---
CONNECT TO EJBBANK

echo --- drop tables and triggers ---
DROP TABLE ITSO.TRANSRECORD
DROP TABLE ITSO.CUSTACCT
DROP TABLE ITSO.CUSTADDRESS
DROP TABLE ITSO.CHECKING
DROP TABLE ITSO.SAVINGS
DROP TABLE ITSO.ACCOUNT
DROP TABLE ITSO.CUSTOMER
DROP TABLE ITSO.CUSTOMERINFO
DROP TRIGGER ITSO.INSERTACCOUNT
DROP TRIGGER ITSO.UPDATEACCOUNT

echo --- create tables ---
CREATE TABLE ITSO.CUSTOMER (                                \
   customerid    INTEGER     NOT NULL,                      \
   title         CHAR(3)     NOT NULL,                      \
   firstName     VARCHAR(30) NOT NULL,                      \
   lastName      VARCHAR(30) NOT NULL,                      \
   userID        CHAR(8),                                   \
   password      CHAR(8),                                   \
   address       BLOB(2000),                                \
                             PRIMARY KEY (CUSTOMERID)       \
)

CREATE TABLE ITSO.CUSTADDRESS (                             \
   customerid    INTEGER     NOT NULL,                      \
   street        CHAR(20),                                  \
   city          CHAR(12),                                  \
   state         CHAR(12),                                  \
   zipcode       CHAR(10),                                  \
                             PRIMARY KEY (CUSTOMERID)       \
)

CREATE TABLE ITSO.CUSTACCT (                                \
   customerid    INTEGER     NOT NULL,                      \
   accID         CHAR(8)     NOT NULL,                      \
                             PRIMARY KEY (CUSTOMERID,ACCID) \
)

CREATE TABLE ITSO.ACCOUNT (                                 \
   accid         CHAR(8)     NOT NULL,                      \
   balance       DEC(8,2)    NOT NULL DEFAULT 0.00,         \
   interest      INTEGER     NOT NULL DEFAULT 5,            \
   acctype       VARCHAR(8)  NOT NULL DEFAULT 'ACCOUNT',    \
   discriminator CHAR(1)     NOT NULL DEFAULT 'A',          \
   overdraft     DEC(8,2)    NOT NULL DEFAULT 200.00,       \
   minamount     DEC(8,2)    NOT NULL DEFAULT 100.00,       \
                             PRIMARY KEY (ACCID)            \
)

CREATE TABLE ITSO.CHECKING (                                \
   accid         CHAR(8)     NOT NULL,                      \
   overdraft     DEC(8,2)    NOT NULL DEFAULT 200.00,       \
                             PRIMARY KEY (ACCID)            \
)

CREATE TABLE ITSO.SAVINGS (                                 \
   accid         CHAR(8)     NOT NULL,                      \
   minamount     DEC(8,2)    NOT NULL DEFAULT 100.00,       \
                             PRIMARY KEY (ACCID)            \
)

CREATE TABLE ITSO.TRANSRECORD (                             \
   transid       TIMESTAMP   NOT NULL,                      \
   accid         CHAR(8)             ,                      \
   transtype     CHAR(1)     NOT NULL,                      \
   transamt      DEC(8,2)    NOT NULL,                      \
                             PRIMARY KEY (TRANSID)          \
)

CREATE TABLE ITSO.CUSTOMERINFO (                            \ 
   customerid    INTEGER     NOT NULL,                      \
   infoid        INTEGER     NOT NULL,                      \
   description   VARCHAR(50),                               \
   data          BLOB(10K),                                 \
                           PRIMARY KEY (CUSTOMERID, INFOID) \
)
        
echo --- referential integrity ---
ALTER TABLE ITSO.TRANSRECORD                                \
   ADD CONSTRAINT "AccountTransrecord" FOREIGN KEY (ACCID)  \
   REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT

ALTER TABLE ITSO.CUSTACCT                                   \
   ADD CONSTRAINT "CAtoCustomer"  FOREIGN KEY (CUSTOMERID)  \
   REFERENCES ITSO.CUSTOMER ON DELETE RESTRICT

ALTER TABLE ITSO.CUSTACCT                                   \
   ADD CONSTRAINT "CAtoAccount"  FOREIGN KEY (ACCID)        \
   REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT

ALTER TABLE ITSO.CUSTADDRESS                                \
   ADD CONSTRAINT "CustAddr"  FOREIGN KEY (CUSTOMERID)      \
   REFERENCES ITSO.CUSTOMER ON DELETE RESTRICT

ALTER TABLE ITSO.CHECKING                                   \
   ADD CONSTRAINT "CheckingAccount"  FOREIGN KEY (ACCID)    \
   REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT

ALTER TABLE ITSO.SAVINGS                                    \
   ADD CONSTRAINT "SavingsAccount"  FOREIGN KEY (ACCID)     \
   REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT

echo --- triggers -------------------
CREATE TRIGGER ITSO.INSERTACCOUNT NO CASCADE BEFORE INSERT            \
    ON ITSO.ACCOUNT referencing new as ACCT for each row mode db2sql  \
    BEGIN ATOMIC                                                      \
      SET ACCT.acctype =                                              \
         CASE                                                         \
           WHEN ACCT.discriminator = 'C' THEN 'CHECKING'              \
           WHEN ACCT.discriminator = 'S' THEN 'SAVINGS'               \
           WHEN ACCT.discriminator = 'A' THEN 'ACCOUNT'               \
         END;                                                         \
    END

CREATE TRIGGER ITSO.UPDATEACCOUNT NO CASCADE BEFORE UPDATE            \
    ON ITSO.ACCOUNT referencing new as ACCT for each row mode db2sql  \
    BEGIN ATOMIC                                                      \
      SET ACCT.acctype =                                              \
         CASE                                                         \
           WHEN ACCT.discriminator = 'C' THEN 'CHECKING'              \
           WHEN ACCT.discriminator = 'S' THEN 'SAVINGS'               \
           WHEN ACCT.discriminator = 'A' THEN 'ACCOUNT'               \
         END;                                                         \
    END

echo --- execute GRANT statements ---
GRANT CONNECT ON DATABASE          TO PUBLIC
GRANT ALL     ON ITSO.CUSTOMER     TO PUBLIC
GRANT ALL     ON ITSO.ACCOUNT      TO PUBLIC
GRANT ALL     ON ITSO.CHECKING     TO PUBLIC
GRANT ALL     ON ITSO.SAVINGS      TO PUBLIC
GRANT ALL     ON ITSO.TRANSRECORD  TO PUBLIC
GRANT ALL     ON ITSO.CUSTACCT     TO PUBLIC
GRANT ALL     ON ITSO.CUSTADDRESS  TO PUBLIC
GRANT ALL     ON ITSO.CUSTOMERINFO TO PUBLIC

echo --- connect reset ---
CONNECT RESET

loadbank.bat 

db2cmd.exe db2setcp.bat db2 -f ejbbank.sql
ejbbank.sql 

echo --- load the EJBBANK database ---

echo --- connect to EJBBANK database ---
CONNECT TO EJBBANK

DELETE FROM ITSO.TRANSRECORD
DELETE FROM ITSO.SAVINGS
DELETE FROM ITSO.CHECKING
DELETE FROM ITSO.CUSTACCT
DELETE FROM ITSO.ACCOUNT
DELETE FROM ITSO.CUSTADDRESS
DELETE FROM ITSO.CUSTOMER
DELETE FROM ITSO.CUSTOMERINFO

echo --- insert into CUSTOMER tables ---
INSERT INTO ITSO.CUSTOMER                                             \
   (customerid, title, firstname, lastname, userid, password) VALUES  \
   (101, 'Mr', 'Maik',    'Schumacher', 'cust101', 'MS'),             \
   (102, 'Mr', 'Fabio',   'Ferraz',     'cust102', 'FF'),             \
   (103, 'Mr', 'Henrik',  'Sjostrand',  'cust103', 'HS'),             \
   (104, 'Mr', 'Ian',     'Brown',      'cust104', 'IB'),             \
   (105, 'Ms', 'Unknown', 'Lady',        null,      null),            \
   (106, 'Mr', 'Ueli',    'Wahli',      'cust106', 'UW') 

INSERT INTO ITSO.CUSTADDRESS                           \
   (customerid, street, city, state, zipcode) VALUES   \
   (101, 'A St', 'A village',  'Germany',    '11111'), \
   (102, 'B Bl', 'B big City', 'Brazil',     '22222'), \
   (103, 'C Rd', 'C City',     'Sweden',     '33333'), \
   (104, 'D Ln', 'D Metro',    'Canada',     '44444'), \
   (105, 'E Ct', 'E Farm',     'Hawaii',     '55555'), \
   (106, 'F Av', 'F Town',     'California', '66666')

echo --- insert into ACCOUNT tables ---
INSERT INTO ITSO.ACCOUNT                                     \
   (accid, balance, interest, acctype, discriminator) VALUES \
   ('101-1001',   80.00, 4, 'CHECKING', 'C'),                \
   ('101-1002',  375.26, 5, 'SAVINGS',  'S'),                \
   ('102-2001', 9375.26, 5, 'SAVINGS',  'S'),                \
   ('102-2002',   75.50, 3, 'CHECKING', 'C'),                \
   ('103-3001',  100.00, 6, 'SAVINGS',  'S'),                \
   ('104-4001',  888.88, 4, 'SAVINGS',  'S'),                \
   ('104-4002',   88.88, 4, 'CHECKING', 'C'),                \
   ('105-5001',    0.00, 2, 'CHECKING', 'C'),                \
   ('106-6001', 1000.00, 3, 'CHECKING', 'C'),                \
   ('106-6002', 2000.00, 4, 'SAVINGS',  'S'),                \
   ('106-6003', 3000.00, 6, 'SAVINGS',  'S')

INSERT INTO ITSO.CHECKING    \
   (accid, overdraft) VALUES \
   ('101-1001',200.00),      \
   ('102-2002',200.00),      \
   ('104-4002',250.00),      \
   ('105-5001',200.00),      \
   ('106-6001',300.00)

INSERT INTO ITSO.SAVINGS     \
   (accid, minamount) VALUES \
   ('101-1002',100.00),      \
   ('102-2001',100.00),      \
   ('103-3001',150.00),      \
   ('104-4001',200.00),      \
   ('106-6002',100.00),      \
   ('106-6003',250.00)

echo --- insert into CUSTACCT table ---
INSERT INTO ITSO.CUSTACCT                               \
   (customerid, accid) VALUES                           \
   (101,'101-1001'), (101,'101-1002'),                  \
   (102,'102-2001'), (102,'102-2002'),                  \
   (103,'103-3001'),                                    \
   (104,'104-4001'), (104,'104-4002'),                  \
   (105,'105-5001'),                                    \
   (106,'106-6001'), (106,'106-6002'), (106,'106-6003'), (106,'105-5001')

echo --- insert into TRANSRECORD table ---
INSERT INTO ITSO.TRANSRECORD                                    \
   (transid,           accid,      transtype, transamt) VALUES  \
   (CURRENT TIMESTAMP, '101-1001', 'C',         80.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '101-1002', 'D',        200.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '102-2001', 'C',       1000.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '102-2002', 'D',         70.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '103-3001', 'C',        100.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '104-4001', 'C',         88.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '104-4002', 'C',         88.88 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '106-6001', 'D',         66.66 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '106-6001', 'C',         10.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '106-6002', 'C',         66.66 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '106-6003', 'C',       3000.00 )

echo --- insert into CUSTOMERINFO table ---
INSERT INTO ITSO.CUSTOMERINFO                     \
   (customerid, infoid, description,data) VALUES  \
   (101, 1, 'Picture', blob('xxxxxxxxxx')),       \
   (101, 2, 'Scanned image', blob('yyyyyy'))    

echo --- connect reset ---
CONNECT RESET

listbank.bat 

db2cmd.exe db2setcp.bat db2 -fejbbank.list

ejbbank.list 

echo --- list the EJBBANK database ---

CONNECT TO EJBBANK

echo - Customer -
SELECT customerid, title, char(firstname,10) as FIRSTNAME, char(lastname,10) as LASTNAME, userid, password from itso.customer

echo - Address-
SELECT * from itso.custaddress

echo - CustomerInfo -
select customerid, infoid, char(description,15) as DESCRIPTION, length(data) as DATALENGTH, blob(data,10) as DATAEXTRACT from itso.customerinfo

echo - CustomerAccount -
SELECT * from itso.custacct

echo - Account-
SELECT * from itso.account

echo - Checking -
SELECT * from itso.checking

echo - Savings -
SELECT * from itso.savings

echo - TransRecord -
SELECT * from itso.transrecord


gsls200808
关注
专栏目录
【大数据之路6】分布式 NoSQL 数据库 HBase
程序员五哥
06-09 895
关系型数据库，比如 MySQL、Oracle、SqlServer、DB2、Derby（小型 SQL 数据库）实时查询、操作数据存储的数据都是一个行级别的记录获取数据：SQL 语句MySQL 底层存储是以文件的形式非关系型数据库，比如 HBase、Redis（kv 形式，可存字符、字典、集合、映射等）、MongoDB（文档形式，最大优势可存文件）满足大数据场景下实时查询的业务需求NoSQL 存储的数据都是 key-value 类型的数据get(key)得到对应的数据。
数据库批量 之 DB2
weixin_30361641的博客
04-24 165
<!-- 批量删除 --> <delete id="deleteAll" parameterType="String"> 　　delete from 　　<include refid="tableName"></include> 　　where 　　UUID in 　　<foreach item="item" index="ind...
java中跑批指的是什么_DB2几个问题的处理（三）
weixin_29556943的博客
03-02 752
想起来一个问题，先记下来。1. 同样的查询语句，从Java程序里面需要1分多钟，在CLP里面执行只用了不到一秒钟，这是怎么回事？这个问题我遇到过两次，一次是因为从Java过来的语句使用了参数标记，导致执行计划和CLP里面直接赋值的语句的执行计划不同；一次是因为使用V8的驱动连接V10的数据库，数据到了服务器端，服务器编译的时候需要隐式的对一个连接字段进行类型转换。但是这两次都是通过比较Java过来...
java SSM 批量插入数据，报错：SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, Pos
gjtao1130的专栏
10-08 721
SSM 批量插入数据， 报错：SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase] Unable to translate SQLException with Error code '933', will now try the fallback tra...
JavaDB（Derby）数据库使用
sinat_38368658的博客
09-04 5614
因为项目需要，做一个单机运行的小型管理系统。 本使用的是java+mysql实现，然后使用exe4j打包成exe运行。 但目标机器不但要配置jre环境，也要安装mysql数据库。 注：关于如何使用户不用配置数据库。目前的解决方案有： 让用户连接到远程mysql服务器 编写文档，教用户安装java和mysql（注意指定版本） 使用批处理文件，帮用户全部安装配置。 注：现在不是mysql+java...
Java DB（也被称为Derby）是一个开源的关系型数据库，它是为Java应用程序设计的
最新发布
BLOG域名:programb.blog.csdn.net
03-06 974
Java DB（也被称为Derby）是一个开源的关系型数据库，它是为Java应用程序设计的，并且可以作为嵌入式数据库使用。
SQLRunner der Java User Group Berlin:基于 Java 的 SQL 工具-开源
07-03
使用 SQLRunner，用户可以编写 SQL 脚本来批量执行数据库操作，比如创建表、插入记录、更新数据或者删除不必要的记录。同时，它也能帮助用户验证数据库模式，确保数据库结构的正确性和一致性。对于那些需要频繁处理 ...
Apache Derby 10.7版手册集
06-23
Apache Derby是一种由Apache软件基金会开发的开源关系数据库管理系统（RDBMS），其特点是完全用Java编写而成，因此也被称作Java DB。由于Derby是纯Java应用程序，这意味着它可以跨平台运行，只需要操作系统的Java...
老年人教程： 数据库版本管理工具 Flyway使用文档
波特多的博客
01-12 2985
一、什么是Flyway ? Flyway 是一款开源的数据库版本管理工具， 它更倾向于规约优于配置的方式。 Flyway 可以独立于应用实现管理并且跟踪数据库的变更。 支持数据库版本的自动升级， 自带有一套默认的规约，不需要复杂的配置。Migrations 可以写成SQL脚本，也可以集成到JAVA代码中，同时在分布式环境下能够安全可靠的升级数据库，支持失败恢复操作等。 而且flyway命令简单，...
Java学习笔记-JDBC
haozhzhzh的博客
09-17 278
11 JDBC 简介 JDBC(Java DataBase Connectivity，java数据库连接)是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序 ​ Java具有坚固、安全、易于使用、易于理解和可从网络上自动下载等特性，是编写数据库应用程序的杰出语言。所需要的知识Java应用程序与各种不同数据库之间进行对话的方法。 ​ JDB
Java DB的两种连接方式
dusin的专栏
04-05 3363
Derby有两种连接方式     第一种连接方式就是像MySQL、Oracle、SQL Server这样使用连接字符串进行连接的Network工作方式，其默认端口为1527。连接串写一写昂：             jdbc:derby://localhost:1527/[;属性1=属性值1[;...]]     第二种连接方式就可以用来替代Access这样“本地数据库”的Embedded形
derby教程使用总结及心得
winter13292的专栏
05-11 1万+
最近项目需要在使用Apache 的
Derby的导入与导出功能详解
梨木乔
05-21 9030
   最近为了解决大容量CSV文件的读取问题不得不转向Derby的学习，因为客户又提出解析CS文件的新需求，而CSVdrive那个鸟东西实在是接口少的可怜，最新的版本也是在04年出的，幸好现在捞到Derby这根救命稻草，按我的想法，是先将csv文件批量移植到Derby数据库中，然后利用Derby强大丰富的sql语句功能，然后解析数据，必然事半功倍。 由于本项目针对Derby某一功能的使用，由于进度
【转】什么是javaDB
luosng001的博客
01-17 6857
javaDB其实就是Derby，它并不是一个新的数据库产品,它是由IBM捐献给Apache的DB项目的一个纯Java数据库,JDK6.0里面带的这个Derby的版本是 10.2.1.7,支持存储过程和触发器;有两种运行模式,一种是作为嵌入式数据库,另一种是作为网络数据库,前者的数据库服务器和客户端都在同一个 JVM里面运行,后者允许数据库服务器端和客户端不在同一个JVM里面,而且允许这两者在不同的...
java db 使用_JavaDB的基本使用
weixin_36229423的博客
02-23 6296
Derby並不是一個新的數據庫產品,它是由IBM捐獻給Apache的DB項目的一個純Java數據庫,JDK6.0里面帶的這個Derby的版本是10.2.1.7,支持存儲過程和觸發器;有兩種運行模式,一種是作為嵌入式數據庫,另一種是作為網絡數據庫,前者的數據庫服務器和客戶端都在同一個JVM里面運行,后者允許數據庫服務器端和客戶端不在同一個JVM里面,而且允許這兩者在不同的物理機器上.值得注意的是JD...
derby数据库常见操作汇总
川在路上
07-14 3861
自增主键的语法： [ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY  [ ( START WITH IntegerConstant  [ ,INCREMENT BY IntegerConstant] ) ]  ]  ] 第一种： create table tb_test(id generated by default as identity,t
使用DBeaver一次性执行多条sql语句
热门推荐
二十一克阳光！的博客
10-08 1万+
前言 最新开始弃用Navicat，改用dbeaver，DBeaver需要Java语言支持，所以安装之前需要配置JDK环境; 问题 再使用DBeaver执行多个SQL语句时（语句已使用；分隔）报错： SQL 错误 [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
使用 Java DB (Derby) 数据库
diaojin6880的博客
02-23 2558
使用 Java DB (Derby) 数据库 https://netbeans.org/kb/docs/ide/java-db_zh_CN.html本文档说明了如何在 NetBeans IDE 中设置与 Java DB 数据库的连接。在建立连接之后，即可开始在 IDE 中使用该数据库，您可以执行的操作包括创建表、用数据填充表、运行 SQL ...
Derby开发者指南：构建Java DB应用
Apache Derby，也被称为Java DB，是一个轻量级、开源的关系型数据库管理系统，它完全由Java编写，且与Java平台高度集成，特别是与Java Database Connectivity (JDBC) API兼容。这本开发者指南旨在帮助构建使用Derby...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值