mybatis学习当中遇到的问题
- 建立mybatis的配置文件时头文件报错
- mapper.xml中sql语句的写法需要注意
- sql映射文件中的增删改查
- mapper接口开发dao
学习步骤
第一步：
-
1.在maven工程里面引入mybatis的依赖
-
https://mvnrepository.com/ (到maven仓库找到你想要使用的mybatis版本)
-
http://www.mybatis.org/mybatis-3/zh/getting-started.html (mybatis学习官网)
第二步：解决mybatis配置文件引入后头文件：http://mybatis.org/dtd/mybatis-3-config.dtd 报错的方法
- 打开File-Languages&Frameworks-Schemas and DTDs
- 点击最下面的“+”，将“http://mybatis.org/dtd/mybatis-3-config.dtd”添加进去即可
第三步：编写Junit单元测试
- 1.首先在idea中下载Junit插件。并重启
- 2.使用ALT+INSERT快捷键插入单元测试类
- 3.编写测试方法
第四步：创建数据库(数据库选用Oracle)
- 建表SQL
rem
rem $Header: summit2.sql 27-jun-2000.12:30:22 slari Exp $
rem
rem Copyright (c) 1991, 2000 Oracle Corporation. All rights reserved.
rem
rem NAME
rem summit2.sql - <one-line expansion of the name>
rem DESCRIPTION
rem <short description of component this file declares/defines>
rem RETURNS
rem
rem FUNCTION
rem Create and populate tables and sequences to support the Summit
rem Sporting Goods business scenario. These objects and data are used
rem in several Oracle classes and demonstration files.
rem
rem MODIFIED (MM/DD/YY)
rem slari 06/27/00 - b1138912: remove duplicate contents
rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines
rem GDURHAM Mar 15, 1993 -- Created
set feedback off
prompt Creating and populating tables and sequences. Please wait.
rem Create sequences.
rem Starting values for sequences begin at the existing maxima for
rem existing primary key values, plus increments.
CREATE SEQUENCE s_customer_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 216
NOCACHE
NOORDER
NOCYCLE;
CREATE SEQUENCE s_dept_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 51
NOCACHE
NOORDER
NOCYCLE;
CREATE SEQUENCE s_emp_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 26
NOCACHE
NOORDER
NOCYCLE;
CREATE SEQUENCE s_image_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 1981
NOCACHE
NOORDER
NOCYCLE;
CREATE SEQUENCE s_longtext_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 1369
NOCACHE
NOORDER
NOCYCLE;
CREATE SEQUENCE s_ord_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 113
NOCACHE
NOORDER
NOCYCLE;
CREATE SEQUENCE s_product_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 50537
NOCACHE
NOORDER
NOCYCLE;
CREATE SEQUENCE s_region_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 6
NOCACHE
NOORDER
NOCYCLE;
CREATE SEQUENCE s_warehouse_id
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 10502
NOCACHE
NOORDER
NOCYCLE;
rem Create and populate tables.
CREATE TABLE s_customer
(id NUMBER(7)
CONSTRAINT s_customer_id_nn NOT NULL,
name VARCHAR2(50)
CONSTRAINT s_customer_name_nn NOT NULL,
phone VARCHAR2(25),
address VARCHAR2(400),
city VARCHAR2(30),
state VARCHAR2(20),
country VARCHAR2(30),
zip_code VARCHAR2(75),
credit_rating VARCHAR2(9),
sales_rep_id NUMBER(7),
region_id NUMBER(7),
comments VARCHAR2(255),
CONSTRAINT s_customer_id_pk PRIMARY KEY (id),
CONSTRAINT s_customer_credit_rating_ck
CHECK (credit_rating IN ('EXCELLENT', 'GOOD', 'POOR')));
INSERT INTO s_customer VALUES (
201, 'Unisports', '55-2066101',
'72 Via Bahia', 'Sao Paolo', NULL, 'Brazil', NULL,
'EXCELLENT', 12, 2, NULL);
INSERT INTO s_customer VALUES (
202, 'OJ Atheletics', '81-20101',
'6741 Takashi Blvd.', 'Osaka', NULL, 'Japan', NULL,
'POOR', 14, 4, NULL);
INSERT INTO s_customer VALUES (
203, 'Delhi Sports', '91-10351',
'11368 Chanakya', 'New Delhi', NULL, 'India', NULL,
'GOOD', 14, 4, NULL);
INSERT INTO s_customer VALUES (
204, 'Womansport', '1-206-104-0103',
'281 King Street', 'Seattle', 'Washington', 'USA', NULL,
'EXCELLENT', 11, 1, NULL);
INSERT INTO s_customer VALUES (
205, 'Kam''s Sporting Goods', '852-3692888',
'15 Henessey Road', 'Hong Kong', NULL, NULL, NULL,
'EXCELLENT', 15, 4, NULL);
INSERT INTO s_customer VALUES (
206, 'Sportique', '33-2257201',
'172 Rue de Rivoli', 'Cannes', NULL, 'France', NULL,
'EXCELLENT', 15, 5, NULL);
INSERT INTO s_customer VALUES (
207, 'Sweet Rock Sports', '234-6036201',
'6 Saint Antoine', 'Lagos', NULL, 'Nigeria', NULL,
'GOOD', NULL, 3, NULL);
INSERT INTO s_customer VALUES (
208, 'Muench Sports', '49-527454',
'435 Gruenestrasse', 'Stuttgart', NULL, 'Germany', NULL,
'GOOD', 15, 5, NULL);
INSERT INTO s_customer VALUES (
209, 'Beisbol Si!', '809-352689',
'792 Playa Del Mar', 'San Pedro de Macon''s', NULL, 'Dominican Republic',
NULL, 'EXCELLENT', 11, 1, NULL);
INSERT INTO s_customer VALUES (
210, 'Futbol Sonora', '52-404562',
'3 Via Saguaro', 'Nogales', NULL, 'Mexico', NULL,
'EXCELLENT', 12, 2, NULL);
INSERT INTO s_customer VALUES (
211, 'Kuhn''s Sports', '42-111292',
'7 Modrany', 'Prague', NULL, 'Czechoslovakia', NULL,
'EXCELLENT', 15, 5, NULL);
INSERT INTO s_customer VALUES (
212, 'Hamada Sport', '20-1209211',
'57A Corniche', 'Alexandria', NULL, 'Egypt', NULL,
'EXCELLENT', 13, 3, NULL);
INSERT INTO s_customer VALUES (
213, 'Big John''s Sports Emporium', '1-415-555-6281',
'4783 18th Street', 'San Francisco', 'CA', 'USA', NULL,
'EXCELLENT', 11, 1, NULL);
INSERT INTO s_customer VALUES (
214, 'Ojibway Retail', '1-716-555-7171',
'415 Main Street', 'Buffalo', 'NY', 'USA', NULL,
'POOR', 11, 1, NULL);
INSERT INTO s_customer VALUES (
215, 'Sporta Russia', '7-3892456',
'6000 Yekatamina', 'Saint Petersburg', NULL, 'Russia', NULL,
'POOR', 15, 5, NULL);
COMMIT;
CREATE TABLE s_dept
(id NUMBER(7)
CONSTRAINT s_dept_id_nn NOT NULL,
name VARCHAR2(25)
CONSTRAINT s_dept_name_nn NOT NULL,
region_id NUMBER(7),
CONSTRAINT s_dept_id_pk PRIMARY KEY (id),
CONSTRAINT s_dept_name_region_id_uk UNIQUE (name, region_id));
INSERT INTO s_dept VALUES (
10, 'Finance', 1);
INSERT INTO s_dept VALUES (
31, 'Sales', 1);
INSERT INTO s_dept VALUES (
32, 'Sales', 2);
INSERT INTO s_dept VALUES (
33, 'Sales', 3);
INSERT INTO s_dept VALUES (
34, 'Sales', 4);
INSERT INTO s_dept VALUES (
35, 'Sales', 5);
INSERT INTO s_dept VALUES (
41, 'Operations', 1);
INSERT INTO s_dept VALUES (
42, 'Operations', 2);
INSERT INTO s_dept VALUES (
43, 'Operations', 3);
INSERT INTO s_dept VALUES (
44, 'Operations', 4);
INSERT INTO s_dept VALUES (
45, 'Operations', 5);
INSERT INTO s_dept VALUES (
50, 'Administration', 1);
COMMIT;
CREATE TABLE s_emp
(id NUMBER(7)
CONSTRAINT s_emp_id_nn NOT NULL,
last_name VARCHAR2(25)
CONSTRAINT s_emp_last_name_nn NOT NULL,
first_name VARCHAR2(25),
userid VARCHAR2(8),
start_date DATE,
comments VARCHAR2(255),
manager_id NUMBER(7),
title VARCHAR2(25),
dept_id NUMBER(7),
salary NUMBER(11, 2),
commission_pct NUMBER(4, 2),
CONSTRAINT s_emp_id_pk PRIMARY KEY (id),
CONSTRAINT s_emp_userid_uk UNIQUE (userid),
CONSTRAINT s_emp_commission_pct_ck
CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)));
-- to_date
INSERT INTO s_emp VALUES (
1, 'Velasquez', 'Carmen', 'cvelasqu',
to_date('03-03-90 8:30', 'dd-mm-yy hh24:mi'), NULL, NULL, 'President',
50, 2500, NULL);
INSERT INTO s_emp VALUES (
2, 'Ngao', 'LaDoris', 'lngao',
to_date('08-03-90', 'DD-MM-YYYY'), NULL, 1, 'VP, Operations',
41, 1450, NULL);
INSERT INTO s_emp VALUES (
3, 'Nagayama', 'Midori', 'mnagayam',
to_date('17-06-91', 'DD-MM-YYYY'), NULL, 1, 'VP, Sales',
31, 1400, NULL);
INSERT INTO s_emp VALUES (
4, 'Quick-To-See', 'Mark', 'mquickto',
to_date('07-4-90', 'DD-MM-YYYY'), NULL, 1, 'VP, Finance',
10, 1450, NULL);
INSERT INTO s_emp VALUES (
5, 'Ropeburn', 'Audry', 'aropebur',
to_date('04-3-90', 'DD-MM-YYYY'), NULL, 1, 'VP, Administration',
50, 1550, NULL);
INSERT INTO s_emp VALUES (
6, 'Urguhart', 'Molly', 'murguhar',
to_date('18-6-91', 'DD-MM-YYYY'), NULL, 2, 'Warehouse Manager',
41, 1200, NULL);
INSERT INTO s_emp VALUES (
7, 'Menchu', 'Roberta', 'rmenchu',
to_date('14-5-90', 'DD-MM-YYYY'), NULL, 2, 'Warehouse Manager',
42, 1250, NULL);
INSERT INTO s_emp VALUES (
8, 'Biri', 'Ben', 'bbiri',
to_date('07-4-90', 'DD-MM-YYYY'), NULL, 2, 'Warehouse Manager',
43, 1100, NULL);
INSERT INTO s_emp VALUES (
9, 'Catchpole', 'Antoinette', 'acatchpo',
to_date('09-2-92', 'DD-MM-YYYY'), NULL, 2, 'Warehouse Manager',
44, 1300, NULL);
INSERT INTO s_emp VALUES (
10, 'Havel', 'Marta', 'mhavel',
to_date('27-2-91', 'DD-MM-YYYY'), NULL, 2, 'Warehouse Manager',
45, 1307, NULL);
INSERT INTO s_emp VALUES (
11, 'Magee', 'Colin', 'cmagee',
to_date('14-5-90', 'DD-MM-YYYY'), NULL, 3, 'Sales Representative',
31, 1400, 10);
INSERT INTO s_emp VALUES (
12, 'Giljum', 'Henry', 'hgiljum',
to_date('18-1-92', 'DD-MM-YYYY'), NULL, 3, 'Sales Representative',
32, 1490, 12.5);
INSERT INTO s_emp VALUES (
13, 'Sedeghi', 'Yasmin', 'ysedeghi',
to_date('18-2-91', 'DD-MM-YYYY'), NULL, 3, 'Sales Representative',
33, 1515, 10);
INSERT INTO s_emp VALUES (
14, 'Nguyen', 'Mai', 'mnguyen',
to_date('22-1-92', 'DD-MM-YYYY'), NULL, 3, 'Sales Representative',
34, 1525, 15);
INSERT INTO s_emp VALUES (
15, 'Dumas', 'Andre', 'adumas',
to_date('09-10-91', 'DD-MM-YYYY'), NULL, 3, 'Sales Representative',
35, 1450, 17.5);
INSERT INTO s_emp VALUES (
16, 'Maduro', 'Elena', 'emaduro',
to_date('07-2-92', 'DD-MM-YYYY'), NULL, 6, 'Stock Clerk',
41, 1400, NULL);
INSERT INTO s_emp VALUES (
17, 'Smith', 'George', 'gsmith',
to_date('08-3-90', 'DD-MM-YYYY'), NULL, 6, 'Stock Clerk',
41, 940, NULL);
INSERT INTO s_emp VALUES (
18, 'Nozaki', 'Akira', 'anozaki',
to_date('09-2-91', 'DD-MM-YYYY'), NULL, 7, 'Stock Clerk',
42, 1200, NULL);
INSERT INTO s_emp VALUES (
19, 'Patel', 'Vikram', 'vpatel',
to_date('06-8-91', 'DD-MM-YYYY'), NULL, 7, 'Stock Clerk',
42, 795, NULL);
INSERT INTO s_emp VALUES (
20, 'Newman', 'Chad', 'cnewman',
to_date('21-7-91', 'DD-MM-YYYY'), NULL, 8, 'Stock Clerk',
43, 750, NULL);
INSERT INTO s_emp VALUES (
21, '3karian', 'Alexander', 'a3kari',
to_date('26-5-91', 'DD-MM-YYYY'), NULL, 8, 'Stock Clerk',
43, 850, NULL);
INSERT INTO s_emp VALUES (
22, 'Chang', 'Eddie', 'echang',
to_date('30-11-90', 'DD-MM-YYYY'), NULL, 9, 'Stock Clerk',
44, 800, NULL);
INSERT INTO s_emp VALUES (
23, 'Patel', 'Radha', 'rpatel',
to_date('17-10-90', 'DD-MM-YYYY'), NULL, 9, 'Stock Clerk',
34, 795, NULL);
INSERT INTO s_emp VALUES (
24, 'Dancs', 'Bela', 'bdancs',
to_date('17-3-91', 'DD-MM-YYYY'), NULL, 10, 'Stock Clerk',
45, 860, NULL);
INSERT INTO s_emp VALUES (
25, 'Schwartz', 'Sylvie', 'sschwart',
to_date('09-5-91', 'DD-MM-YYYY'), NULL, 10, 'Stock Clerk',
45, 1100, NULL);
COMMIT;
CREATE TABLE s_image
(id NUMBER(7)
CONSTRAINT s_image_id_nn NOT NULL,
format VARCHAR2(25),
use_filename VARCHAR2(1),
filename VARCHAR2(255),
image LONG RAW,
CONSTRAINT s_image_id_pk
PRIMARY KEY (id),
CONSTRAINT s_image_format_ck
CHECK (format in ('JFIFF', 'JTIFF')),
CONSTRAINT s_image_use_filename_ck
CHECK (use_filename in ('Y', 'N')));
INSERT INTO s_image VALUES (
1001, 'JTIFF', 'Y', 'bunboot.tif', NULL);
INSERT INTO s_image VALUES (
1002, 'JTIFF', 'Y', 'aceboot.tif', NULL);
INSERT INTO s_image VALUES (
1003, 'JTIFF', 'Y', 'proboot.tif', NULL);
INSERT INTO s_image VALUES (
1011, 'JTIFF', 'Y', 'bunpole.tif', NULL);
INSERT INTO s_image VALUES (
1012, 'JTIFF', 'Y', 'acepole.tif', NULL);
INSERT INTO s_image VALUES (
1013, 'JTIFF', 'Y', 'propole.tif', NULL);
INSERT INTO s_image VALUES (
1291, 'JTIFF', 'Y', 'gpbike.tif', NULL);
INSERT INTO s_image VALUES (
1296, 'JTIFF', 'Y', 'himbike.tif', NULL);
INSERT INTO s_image VALUES (
1829, 'JTIFF', 'Y', 'safthelm.tif', NULL);
INSERT INTO s_image VALUES (
1381, 'JTIFF', 'Y', 'probar.tif', NULL);
INSERT INTO s_image VALUES (
1382, 'JTIFF', 'Y', 'curlbar.tif', NULL);
INSERT INTO s_image VALUES (
1119, 'JTIFF', 'Y', 'baseball.tif', NULL);
INSERT INTO s_image VALUES (
1223, 'JTIFF', 'Y', 'chaphelm.tif', NULL);
INSERT INTO s_image VALUES (
1367, 'JTIFF', 'Y', 'grglove.tif', NULL);
INSERT INTO s_image VALUES (
1368, 'JTIFF', 'Y', 'alglove.tif', NULL);
INSERT INTO s_image VALUES (
1369, 'JTIFF', 'Y', 'stglove.tif', NULL);
INSERT INTO s_image VALUES (
1480, 'JTIFF', 'Y', 'cabbat.tif', NULL);
INSERT INTO s_image VALUES (
1482, 'JTIFF', 'Y', 'pucbat.tif', NULL);
INSERT INTO s_image VALUES (
1486, 'JTIFF', 'Y', 'winbat.tif', NULL);
COMMIT;
CREATE TABLE s_inventory
(product_id NUMBER(7)
CONSTRAINT s_inventory_product_id_nn NOT NULL,
warehouse_id NUMBER(7)
CONSTRAINT s_inventory_warehouse_id_nn NOT NULL,
amount_in_stock NUMBER(9),
reorder_point NUMBER(9),
max_in_stock NUMBER(9),
out_of_stock_explanation VARCHAR2(255),
restock_date DATE,
CONSTRAINT s_inventory_prodid_warid_pk
PRIMARY KEY (product_id, warehouse_id));
INSERT INTO s_inventory VALUES (
10011, 101, 650, 625, 1100, NULL, NULL);
INSERT INTO s_inventory VALUES (
10012, 101, 600, 560, 1000, NULL, NULL);
INSERT INTO s_inventory VALUES (
10013, 101, 400, 400, 700, NULL, NULL);
INSERT INTO s_inventory VALUES (
10021, 101, 500, 425, 740, NULL, NULL);
INSERT INTO s_inventory VALUES (
10022, 101, 300, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
10023, 101, 400, 300, 525, NULL, NULL);
INSERT INTO s_inventory VALUES (
20106, 101, 993, 625, 1000, NULL, NULL);
INSERT INTO s_inventory VALUES (
20108, 101, 700, 700, 1225, NULL, NULL);
INSERT INTO s_inventory VALUES (
20201, 101, 802, 800, 1400, NULL, NULL);
INSERT INTO s_inventory VALUES (
20510, 101, 1389, 850, 1400, NULL, NULL);
INSERT INTO s_inventory VALUES (
20512, 101, 850, 850, 1450, NULL, NULL);
INSERT INTO s_inventory VALUES (
30321, 101, 2000, 1500, 2500, NULL, NULL);
INSERT INTO s_inventory VALUES (
30326, 101, 2100, 2000, 3500, NULL, NULL);
INSERT INTO s_inventory VALUES (
30421, 101, 1822, 1800, 3150, NULL, NULL);
INSERT INTO s_inventory VALUES (
30426, 101, 2250, 2000, 3500, NULL, NULL);
INSERT INTO s_inventory VALUES (
30433, 101, 650, 600, 1050, NULL, NULL);
INSERT INTO s_inventory VALUES (
32779, 101, 2120, 1250, 2200, NULL, NULL);
INSERT INTO s_inventory VALUES (
32861, 101, 505, 500, 875, NULL, NULL);
INSERT INTO s_inventory VALUES (
40421, 101, 578, 350, 600, NULL, NULL);
INSERT INTO s_inventory VALUES (
40422, 101, 0, 350, 600, 'Phenomenal sales...', to_date('08-2-93','DD-MM-YYYY'));
INSERT INTO s_inventory VALUES (
41010, 101, 250, 250, 437, NULL, NULL);
INSERT INTO s_inventory VALUES (
41020, 101, 471, 450, 750, NULL, NULL);
INSERT INTO s_inventory VALUES (
41050, 101, 501, 450, 750, NULL, NULL);
INSERT INTO s_inventory VALUES (
41080, 101, 400, 400, 700, NULL, NULL);
INSERT INTO s_inventory VALUES (
41100, 101, 350, 350, 600, NULL, NULL);
INSERT INTO s_inventory VALUES (
50169, 101, 2530, 1500, 2600, NULL, NULL);
INSERT INTO s_inventory VALUES (
50273, 101, 233, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
50417, 101, 518, 500, 875, NULL, NULL);
INSERT INTO s_inventory VALUES (
50418, 101, 244, 100, 275, NULL, NULL);
INSERT INTO s_inventory VALUES (
50419, 101, 230, 120, 310, NULL, NULL);
INSERT INTO s_inventory VALUES (
50530, 101, 669, 400, 700, NULL, NULL);
INSERT INTO s_inventory VALUES (
50532, 101, 0, 100, 175, 'Wait for Spring.', to_date('12-4-93','DD-MM-YYYY'));
INSERT INTO s_inventory VALUES (
50536, 101, 173, 100, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
20106, 201, 220, 150, 260, NULL, NULL);
INSERT INTO s_inventory VALUES (
20108, 201, 166, 150, 260, NULL, NULL);
INSERT INTO s_inventory VALUES (
20201, 201, 320, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
20510, 201, 175, 100, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
20512, 201, 162, 100, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
30321, 201, 96, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30326, 201, 147, 120, 210, NULL, NULL);
INSERT INTO s_inventory VALUES (
30421, 201, 102, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30426, 201, 200, 120, 210, NULL, NULL);
INSERT INTO s_inventory VALUES (
30433, 201, 130, 130, 230, NULL, NULL);
INSERT INTO s_inventory VALUES (
32779, 201, 180, 150, 260, NULL, NULL);
INSERT INTO s_inventory VALUES (
32861, 201, 132, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
50169, 201, 225, 220, 385, NULL, NULL);
INSERT INTO s_inventory VALUES (
50273, 201, 75, 60, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
50417, 201, 82, 60, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
50418, 201, 98, 60, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
50419, 201, 77, 60, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
50530, 201, 62, 60, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
50532, 201, 67, 60, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
50536, 201, 97, 60, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
20510, 301, 69, 40, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
20512, 301, 28, 20, 50, NULL, NULL);
INSERT INTO s_inventory VALUES (
30321, 301, 85, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30421, 301, 102, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30433, 301, 35, 20, 35, NULL, NULL);
INSERT INTO s_inventory VALUES (
32779, 301, 102, 95, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
32861, 301, 57, 50, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
40421, 301, 70, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
40422, 301, 65, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
41010, 301, 59, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
41020, 301, 61, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
41050, 301, 49, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
41080, 301, 50, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
41100, 301, 42, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
20510, 401, 88, 50, 100, NULL, NULL);
INSERT INTO s_inventory VALUES (
20512, 401, 75, 75, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30321, 401, 102, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30326, 401, 113, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30421, 401, 85, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30426, 401, 135, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
30433, 401, 0, 100, 175, 'A defective shipment was sent to Hong Kong ' ||
'and needed to be returned. The soonest ACME can turn this around is ' ||
'early February.', to_date('07-9-92','DD-MM-YYYY'));
INSERT INTO s_inventory VALUES (
32779, 401, 135, 100, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
32861, 401, 250, 150, 250, NULL, NULL);
INSERT INTO s_inventory VALUES (
40421, 401, 47, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
40422, 401, 50, 40, 70, NULL, NULL);
INSERT INTO s_inventory VALUES (
41010, 401, 80, 70, 220, NULL, NULL);
INSERT INTO s_inventory VALUES (
41020, 401, 91, 70, 220, NULL, NULL);
INSERT INTO s_inventory VALUES (
41050, 401, 169, 70, 220, NULL, NULL);
INSERT INTO s_inventory VALUES (
41080, 401, 100, 70, 220, NULL, NULL);
INSERT INTO s_inventory VALUES (
41100, 401, 75, 70, 220, NULL, NULL);
INSERT INTO s_inventory VALUES (
50169, 401, 240, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
50273, 401, 224, 150, 280, NULL, NULL);
INSERT INTO s_inventory VALUES (
50417, 401, 130, 120, 210, NULL, NULL);
INSERT INTO s_inventory VALUES (
50418, 401, 156, 100, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
50419, 401, 151, 150, 280, NULL, NULL);
INSERT INTO s_inventory VALUES (
50530, 401, 119, 100, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
50532, 401, 233, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
50536, 401, 138, 100, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
10012, 10501, 300, 300, 525, NULL, NULL);
INSERT INTO s_inventory VALUES (
10013, 10501, 314, 300, 525, NULL, NULL);
INSERT INTO s_inventory VALUES (
10022, 10501, 502, 300, 525, NULL, NULL);
INSERT INTO s_inventory VALUES (
10023, 10501, 500, 300, 525, NULL, NULL);
INSERT INTO s_inventory VALUES (
20106, 10501, 150, 100, 175, NULL, NULL);
INSERT INTO s_inventory VALUES (
20108, 10501, 222, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
20201, 10501, 275, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
20510, 10501, 57, 50, 87, NULL, NULL);
INSERT INTO s_inventory VALUES (
20512, 10501, 62, 50, 87, NULL, NULL);
INSERT INTO s_inventory VALUES (
30321, 10501, 194, 150, 275, NULL, NULL);
INSERT INTO s_inventory VALUES (
30326, 10501, 277, 250, 440, NULL, NULL);
INSERT INTO s_inventory VALUES (
30421, 10501, 190, 150, 275, NULL, NULL);
INSERT INTO s_inventory VALUES (
30426, 10501, 423, 250, 450, NULL, NULL);
INSERT INTO s_inventory VALUES (
30433, 10501, 273, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
32779, 10501, 280, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
32861, 10501, 288, 200, 350, NULL, NULL);
INSERT INTO s_inventory VALUES (
40421, 10501, 97, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
40422, 10501, 90, 80, 140, NULL, NULL);
INSERT INTO s_inventory VALUES (
41010, 10501, 151, 140, 245, NULL, NULL);
INSERT INTO s_inventory VALUES (
41020, 10501, 224, 140, 245, NULL, NULL);
INSERT INTO s_inventory VALUES (
41050, 10501, 157, 140, 245, NULL, NULL);
INSERT INTO s_inventory VALUES (
41080, 10501, 159, 140, 245, NULL, NULL);
INSERT INTO s_inventory VALUES (
41100, 10501, 141, 140, 245, NULL, NULL);
COMMIT;
CREATE TABLE s_item
(ord_id NUMBER(7)
CONSTRAINT s_item_ord_id_nn NOT NULL,
item_id NUMBER(7)
CONSTRAINT s_item_item_id_nn NOT NULL,
product_id NUMBER(7)
CONSTRAINT s_item_product_id_nn NOT NULL,
price NUMBER(11, 2),
quantity NUMBER(9),
quantity_shipped NUMBER(9),
CONSTRAINT s_item_ordid_itemid_pk PRIMARY KEY (ord_id, item_id),
CONSTRAINT s_item_ordid_prodid_uk UNIQUE (ord_id, product_id));
INSERT INTO s_item VALUES (
100, 1, 10011, 135, 500, 500);
INSERT INTO s_item VALUES (
100, 2, 10013, 380, 400, 400);
INSERT INTO s_item VALUES (
100, 3, 10021, 14, 500, 500);
INSERT INTO s_item VALUES (
100, 5, 30326, 582, 600, 600);
INSERT INTO s_item VALUES (
100, 7, 41010, 8, 250, 250);
INSERT INTO s_item VALUES (
100, 6, 30433, 20, 450, 450);
INSERT INTO s_item VALUES (
100, 4, 10023, 36, 400, 400);
INSERT INTO s_item VALUES (
101, 1, 30421, 16, 15, 15);
INSERT INTO s_item VALUES (
101, 3, 41010, 8, 20, 20);
INSERT INTO s_item VALUES (
101, 5, 50169, 4.29, 40, 40);
INSERT INTO s_item VALUES (
101, 6, 50417, 80, 27, 27);
INSERT INTO s_item VALUES (
101, 7, 50530, 45, 50, 50);
INSERT INTO s_item VALUES (
101, 4, 41100, 45, 35, 35);
INSERT INTO s_item VALUES (
101, 2, 40422, 50, 30, 30);
INSERT INTO s_item VALUES (
102, 1, 20108, 28, 100, 100);
INSERT INTO s_item VALUES (
102, 2, 20201, 123, 45, 45);
INSERT INTO s_item VALUES (
103, 1, 30433, 20, 15, 15);
INSERT INTO s_item VALUES (
103, 2, 32779, 7, 11, 11);
INSERT INTO s_item VALUES (
104, 1, 20510, 9, 7, 7);
INSERT INTO s_item VALUES (
104, 4, 30421, 16, 35, 35);
INSERT INTO s_item VALUES (
104, 2, 20512, 8, 12, 12);
INSERT INTO s_item VALUES (
104, 3, 30321, 1669, 19, 19);
INSERT INTO s_item VALUES (
105, 1, 50273, 22.89, 16, 16);
INSERT INTO s_item VALUES (
105, 3, 50532, 47, 28, 28);
INSERT INTO s_item VALUES (
105, 2, 50419, 80, 13, 13);
INSERT INTO s_item VALUES (
106, 1, 20108, 28, 46, 46);
INSERT INTO s_item VALUES (
106, 4, 50273, 22.89, 75, 75);
INSERT INTO s_item VALUES (
106, 5, 50418, 75, 98, 98);
INSERT INTO s_item VALUES (
106, 6, 50419, 80, 27, 27);
INSERT INTO s_item VALUES (
106, 2, 20201, 123, 21, 21);
INSERT INTO s_item VALUES (
106, 3, 50169, 4.29, 125, 125);
INSERT INTO s_item VALUES (
107, 1, 20106, 11, 50, 50);
INSERT INTO s_item VALUES (
107, 3, 20201, 115, 130, 130);
INSERT INTO s_item VALUES (
107, 5, 30421, 16, 55, 55);
INSERT INTO s_item VALUES (
107, 4, 30321, 1669, 75, 75);
INSERT INTO s_item VALUES (
107, 2, 20108, 28, 22, 22);
INSERT INTO s_item VALUES (
108, 1, 20510, 9, 9, 9);
INSERT INTO s_item VALUES (
108, 6, 41080, 35, 50, 50);
INSERT INTO s_item VALUES (
108, 7, 41100, 45, 42, 42);
INSERT INTO s_item VALUES (
108, 5, 32861, 60, 57, 57);
INSERT INTO s_item VALUES (
108, 2, 20512, 8, 18, 18);
INSERT INTO s_item VALUES (
108, 4, 32779, 7, 60, 60);
INSERT INTO s_item VALUES (
108, 3, 30321, 1669, 85, 85);
INSERT INTO s_item VALUES (
109, 1, 10011, 140, 150, 150);
INSERT INTO s_item VALUES (
109, 5, 30426, 18.25, 500, 500);
INSERT INTO s_item VALUES (
109, 7, 50418, 75, 43, 43);
INSERT INTO s_item VALUES (
109, 6, 32861, 60, 50, 50);
INSERT INTO s_item VALUES (
109, 4, 30326, 582, 1500, 1500);
INSERT INTO s_item VALUES (
109, 2, 10012, 175, 600, 600);
INSERT INTO s_item VALUES (
109, 3, 10022, 21.95, 300, 300);
INSERT INTO s_item VALUES (
110, 1, 50273, 22.89, 17, 17);
INSERT INTO s_item VALUES (
110, 2, 50536, 50, 23, 23);
INSERT INTO s_item VALUES (
111, 1, 40421, 65, 27, 27);
INSERT INTO s_item VALUES (
111, 2, 41080, 35, 29, 29);
INSERT INTO s_item VALUES (
97, 1, 20106, 9, 1000, 1000);
INSERT INTO s_item VALUES (
97, 2, 30321, 1500, 50, 50);
INSERT INTO s_item VALUES (
98, 1, 40421, 85, 7, 7);
INSERT INTO s_item VALUES (
99, 1, 20510, 9, 18, 18);
INSERT INTO s_item VALUES (
99, 2, 20512, 8, 25, 25);
INSERT INTO s_item VALUES (
99, 3, 50417, 80, 53, 53);
INSERT INTO s_item VALUES (
99, 4, 50530, 45, 69, 69);
INSERT INTO s_item VALUES (
112, 1, 20106, 11, 50, 50);
COMMIT;
CREATE TABLE s_longtext
(id NUMBER(7)
CONSTRAINT s_longtext_id_nn NOT NULL,
use_filename VARCHAR2(1),
filename VARCHAR2(255),
text VARCHAR2(2000),
CONSTRAINT s_longtext_id_pk PRIMARY KEY (id),
CONSTRAINT s_longtext_use_filename_ck
CHECK (use_filename in ('Y', 'N')));
INSERT INTO s_longtext VALUES (
1017, 'N', NULL,
'Protective knee pads for any number of physical activities including ' ||
'bicycling and skating (4-wheel, in-line, and ice). Also provide ' ||
'support for stress activities such as weight-lifting. Velcro belts ' ||
'allow easy adjustment for any size and snugness of fit. Hardened ' ||
'plastic shell comes in a variety of colors, so you can buy a pair to ' ||
'match every outfit. Can also be worn at the beach to cover ' ||
'particularly ugly knees.');
INSERT INTO s_longtext VALUES (
1019, 'N', NULL,
'Protective elbow pads for any number of physical activities including ' ||
'bicycling and skating (4-wheel, in-line, and ice). Also provide ' ||
'support for stress activities such as weight-lifting. Velcro belts ' ||
'allow easy adjustment for any size and snugness of fit. Hardened ' ||
'plastic shell comes in a variety of colors, so you can buy a pair to ' ||
'match every outfit.');
INSERT INTO s_longtext VALUES (
1037, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
1039, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
1043, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
1286, 'N', NULL,
'Don''t slack off--try the Slaker Water Bottle. With its 1 quart ' ||
'capacity, this is the only water bottle you''ll need. It''s ' ||
'lightweight, durable, and guaranteed for life to be leak proof. It ' ||
'comes with a convenient velcro strap so it ' ||
'can be conveniently attached to your bike or other sports equipment.');
INSERT INTO s_longtext VALUES (
1368, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
517, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
518, 'N', NULL,
'Perfect for the beginner. Rear entry (easy to put on with only one ' ||
'buckle), weight control adjustment on side of boot for easy access, ' ||
'comes in a wide variety of colors to match every outfit.');
INSERT INTO s_longtext VALUES (
519, 'N', NULL,
'If you have mastered the basic techniques you are ready for the Ace Ski ' ||
'Boot. This intermediate boot comes as a package with self adjustable ' ||
'bindings that will adapt to your skill and speed. The boot is designed ' ||
'for extra grip on slopes and jumps.');
INSERT INTO s_longtext VALUES (
520, 'N', NULL,
'The Pro ski boot is an advanced boot that combines high tech and ' ||
'comfort. It''s made of fibre that will mould to your foot with body ' ||
'heat. If you''re after perfection, don''t look any further: this is it!');
INSERT INTO s_longtext VALUES (
527, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
528, 'N', NULL,
'Lightweight aluminum pole, comes in a variety of sizes and neon ' ||
'colors. Comfortable adjustable straps.');
INSERT INTO s_longtext VALUES (
529, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
530, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
557, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
587, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
607, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
613, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
615, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
676, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
708, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
780, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
828, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
833, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
924, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
925, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
926, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
927, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
928, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
929, NULL, NULL, NULL);
INSERT INTO s_longtext VALUES (
933, 'N', NULL,
'The widest, strongest, and knobbiest tires for mountain bike ' ||
'enthusiasts. Guaranteed to withstand pummelling that will reduce most ' ||
'bicycles (except for the Himalayan) to scrap iron. These tires can ' ||
'carry you to places where nobody would want to bicycle. Sizes to ' ||
'fit all makes of mountain bike including wide and super wide rims. ' ||
'Steel-banded radial models are also available by direct factory order.');
INSERT INTO s_longtext VALUES (
940, NULL, NULL, NULL);
COMMIT;
CREATE TABLE s_ord
(id NUMBER(7)
CONSTRAINT s_ord_id_nn NOT NULL,
customer_id NUMBER(7)
CONSTRAINT s_ord_customer_id_nn NOT NULL,
date_ordered DATE,
date_shipped DATE,
sales_rep_id NUMBER(7),
total NUMBER(11, 2),
payment_type VARCHAR2(6),
order_filled VARCHAR2(1),
CONSTRAINT s_ord_id_pk PRIMARY KEY (id),
CONSTRAINT s_ord_payment_type_ck
CHECK (payment_type in ('CASH', 'CREDIT')),
CONSTRAINT s_ord_order_filled_ck
CHECK (order_filled in ('Y', 'N')));
-- to_date
INSERT INTO s_ord VALUES (
100, 204, to_date('31-8-92', 'DD-MM-YYYY'), to_date('10-9-92', 'DD-MM-YYYY'),
11, 601100, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
101, 205, to_date('31-8-92', 'DD-MM-YYYY'), to_date('15-9-92', 'DD-MM-YYYY'),
14, 8056.6, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
102, 206, to_date('01-9-92', 'DD-MM-YYYY'),to_date( '08-9-92', 'DD-MM-YYYY'),
15, 8335, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
103, 208, to_date('02-9-92', 'DD-MM-YYYY'), to_date('22-9-92', 'DD-MM-YYYY'),
15, 377, 'CASH', 'Y');
INSERT INTO s_ord VALUES (
104, 208, to_date('03-9-92', 'DD-MM-YYYY'),to_date( '23-9-92', 'DD-MM-YYYY'),
15, 32430, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
105, 209, to_date('04-9-92', 'DD-MM-YYYY'), to_date('18-9-92', 'DD-MM-YYYY'),
11, 2722.24, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
106, 210, to_date('07-9-92', 'DD-MM-YYYY'), to_date('15-9-92', 'DD-MM-YYYY'),
12, 15634, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
107, 211, to_date('07-9-92', 'DD-MM-YYYY'), to_date('21-9-92', 'DD-MM-YYYY'),
15, 142171, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
108, 212, to_date('07-9-92', 'DD-MM-YYYY'), to_date('10-9-92', 'DD-MM-YYYY'),
13, 149570, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
109, 213, to_date('08-9-92', 'DD-MM-YYYY'), to_date('28-9-92', 'DD-MM-YYYY'),
11, 1020935, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
110, 214, to_date('09-9-92', 'DD-MM-YYYY'), to_date('21-9-92', 'DD-MM-YYYY'),
11, 1539.13, 'CASH', 'Y');
INSERT INTO s_ord VALUES (
111, 204, to_date('09-9-92', 'DD-MM-YYYY'), to_date('21-9-92', 'DD-MM-YYYY'),
11, 2770, 'CASH', 'Y');
INSERT INTO s_ord VALUES (
97, 201, to_date('28-8-92', 'DD-MM-YYYY'), to_date('17-9-92', 'DD-MM-YYYY'),
12, 84000, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
98, 202, to_date('31-8-92', 'DD-MM-YYYY'), to_date('10-9-92', 'DD-MM-YYYY'),
14, 595, 'CASH', 'Y');
INSERT INTO s_ord VALUES (
99, 203, to_date('31-8-92', 'DD-MM-YYYY'), to_date('18-9-92', 'DD-MM-YYYY'),
14, 7707, 'CREDIT', 'Y');
INSERT INTO s_ord VALUES (
112, 210, to_date('31-8-92', 'DD-MM-YYYY'), to_date('10-9-92', 'DD-MM-YYYY'),
12, 550, 'CREDIT', 'Y');
COMMIT;
-- to_date
CREATE TABLE s_product
(id NUMBER(7)
CONSTRAINT s_product_id_nn NOT NULL,
name VARCHAR2(50)
CONSTRAINT s_product_name_nn NOT NULL,
short_desc VARCHAR2(255),
longtext_id NUMBER(7),
image_id NUMBER(7),
suggested_whlsl_price NUMBER(11, 2),
whlsl_units VARCHAR2(25),
CONSTRAINT s_product_id_pk PRIMARY KEY (id),
CONSTRAINT s_product_name_uk UNIQUE (name));
INSERT INTO s_product VALUES (
10011, 'Bunny Boot',
'Beginner''s ski boot',
518, 1001,
150, NULL);
INSERT INTO s_product VALUES (
10012, 'Ace Ski Boot',
'Intermediate ski boot',
519, 1002,
200, NULL);
INSERT INTO s_product VALUES (
10013, 'Pro Ski Boot',
'Advanced ski boot',
520, 1003,
410, NULL);
INSERT INTO s_product VALUES (
10021, 'Bunny Ski Pole',
'Beginner''s ski pole',
528, 1011,
16.25, NULL);
INSERT INTO s_product VALUES (
10022, 'Ace Ski Pole',
'Intermediate ski pole',
529, 1012,
21.95, NULL);
INSERT INTO s_product VALUES (
10023, 'Pro Ski Pole',
'Advanced ski pole',
530, 1013,
40.95, NULL);
INSERT INTO s_product VALUES (
20106, 'Junior Soccer Ball',
'Junior soccer ball',
613, NULL,
11, NULL);
INSERT INTO s_product VALUES (
20108, 'World Cup Soccer Ball',
'World cup soccer ball',
615, NULL,
28, NULL);
INSERT INTO s_product VALUES (
20201, 'World Cup Net',
'World cup net',
708, NULL,
123, NULL);
INSERT INTO s_product VALUES (
20510, 'Black Hawk Knee Pads',
'Knee pads, pair',
1017, NULL,
9, NULL);
INSERT INTO s_product VALUES (
20512, 'Black Hawk Elbow Pads',
'Elbow pads, pair',
1019, NULL,
8, NULL);
INSERT INTO s_product VALUES (
30321, 'Grand Prix Bicycle',
'Road bicycle',
828, 1291,
1669, NULL);
INSERT INTO s_product VALUES (
30326, 'Himalaya Bicycle',
'Mountain bicycle',
833, 1296,
582, NULL);
INSERT INTO s_product VALUES (
30421, 'Grand Prix Bicycle Tires',
'Road bicycle tires',
927, NULL,
16, NULL);
INSERT INTO s_product VALUES (
30426, 'Himalaya Tires',
'Mountain bicycle tires',
933, NULL,
18.25, NULL);
INSERT INTO s_product VALUES (
30433, 'New Air Pump',
'Tire pump',
940, NULL,
20, NULL);
INSERT INTO s_product VALUES (
32779, 'Slaker Water Bottle',
'Water bottle',
1286, NULL,
7, NULL);
INSERT INTO s_product VALUES (
32861, 'Safe-T Helmet',
'Bicycle helmet',
1368, 1829,
60, NULL);
INSERT INTO s_product VALUES (
40421, 'Alexeyer Pro Lifting Bar',
'Straight bar',
928, 1381,
65, NULL);
INSERT INTO s_product VALUES (
40422, 'Pro Curling Bar',
'Curling bar',
929, 1382,
50, NULL);
INSERT INTO s_product VALUES (
41010, 'Prostar 10 Pound Weight',
'Ten pound weight',
517, NULL,
8, NULL);
INSERT INTO s_product VALUES (
41020, 'Prostar 20 Pound Weight',
'Twenty pound weight',
527, NULL,
12, NULL);
INSERT INTO s_product VALUES (
41050, 'Prostar 50 Pound Weight',
'Fifty pound weight',
557, NULL,
25, NULL);
INSERT INTO s_product VALUES (
41080, 'Prostar 80 Pound Weight',
'Eighty pound weight',
587, NULL,
35, NULL);
INSERT INTO s_product VALUES (
41100, 'Prostar 100 Pound Weight',
'One hundred pound weight',
607, NULL,
45, NULL);
INSERT INTO s_product VALUES (
50169, 'Major League Baseball',
'Baseball',
676, 1119,
4.29, NULL);
INSERT INTO s_product VALUES (
50273, 'Chapman Helmet',
'Batting helmet',
780, 1223,
22.89, NULL);
INSERT INTO s_product VALUES (
50417, 'Griffey Glove',
'Outfielder''s glove',
924, 1367,
80, NULL);
INSERT INTO s_product VALUES (
50418, 'Alomar Glove',
'Infielder''s glove',
925, 1368,
75, NULL);
INSERT INTO s_product VALUES (
50419, 'Steinbach Glove',
'Catcher''s glove',
926, 1369,
80, NULL);
INSERT INTO s_product VALUES (
50530, 'Cabrera Bat',
'Thirty inch bat',
1037, 1480,
45, NULL);
INSERT INTO s_product VALUES (
50532, 'Puckett Bat',
'Thirty-two inch bat',
1039, 1482,
47, NULL);
INSERT INTO s_product VALUES (
50536, 'Winfield Bat',
'Thirty-six inch bat',
1043, 1486,
50, NULL);
COMMIT;
CREATE TABLE s_region
(id NUMBER(7)
CONSTRAINT s_region_id_nn NOT NULL,
name VARCHAR2(50)
CONSTRAINT s_region_name_nn NOT NULL,
CONSTRAINT s_region_id_pk PRIMARY KEY (id),
CONSTRAINT s_region_name_uk UNIQUE (name));
INSERT INTO s_region VALUES (
1, 'North America');
INSERT INTO s_region VALUES (
2, 'South America');
INSERT INTO s_region VALUES (
3, 'Africa / Middle East');
INSERT INTO s_region VALUES (
4, 'Asia');
INSERT INTO s_region VALUES (
5, 'Europe');
COMMIT;
CREATE TABLE s_title
(title VARCHAR2(25)
CONSTRAINT s_title_title_nn NOT NULL,
CONSTRAINT s_title_title_pk PRIMARY KEY (title));
INSERT INTO s_title VALUES ('President');
INSERT INTO s_title VALUES ('Sales Representative');
INSERT INTO s_title VALUES ('Stock Clerk');
INSERT INTO s_title VALUES ('VP, Administration');
INSERT INTO s_title VALUES ('VP, Finance');
INSERT INTO s_title VALUES ('VP, Operations');
INSERT INTO s_title VALUES ('VP, Sales');
INSERT INTO s_title VALUES ('Warehouse Manager');
COMMIT;
CREATE TABLE s_warehouse
(id NUMBER(7)
CONSTRAINT s_warehouse_id_nn NOT NULL,
region_id NUMBER(7)
CONSTRAINT s_warehouse_region_id_nn NOT NULL,
address LONG,
city VARCHAR2(30),
state VARCHAR2(20),
country VARCHAR2(30),
zip_code VARCHAR2(75),
phone VARCHAR2(25),
manager_id NUMBER(7),
CONSTRAINT s_warehouse_id_pk PRIMARY KEY (id));
INSERT INTO s_warehouse VALUES (
101, 1,
'283 King Street',
'Seattle', 'WA', 'USA',
NULL,
NULL, 6);
INSERT INTO s_warehouse VALUES (
10501, 5,
'5 Modrany',
'Bratislava', NULL, 'Czechozlovakia',
NULL,
NULL, 10);
INSERT INTO s_warehouse VALUES (
201, 2,
'68 Via Centrale',
'Sao Paolo', NULL, 'Brazil',
NULL,
NULL, 7);
INSERT INTO s_warehouse VALUES (
301, 3,
'6921 King Way',
'Lagos', NULL, 'Nigeria',
NULL,
NULL, 8);
INSERT INTO s_warehouse VALUES (
401, 4,
'86 Chu Street',
'Hong Kong', NULL, NULL,
NULL,
NULL, 9);
COMMIT;
rem Add foreign key constraints.
ALTER TABLE s_dept
ADD CONSTRAINT s_dept_region_id_fk
FOREIGN KEY (region_id) REFERENCES s_region (id);
ALTER TABLE s_emp
ADD CONSTRAINT s_emp_manager_id_fk
FOREIGN KEY (manager_id) REFERENCES s_emp (id);
ALTER TABLE s_emp
ADD CONSTRAINT s_emp_dept_id_fk
FOREIGN KEY (dept_id) REFERENCES s_dept (id);
ALTER TABLE s_emp
ADD CONSTRAINT s_emp_title_fk
FOREIGN KEY (title) REFERENCES s_title (title);
ALTER TABLE s_customer
ADD CONSTRAINT s_sales_rep_id_fk
FOREIGN KEY (sales_rep_id) REFERENCES s_emp (id);
ALTER TABLE s_customer
ADD CONSTRAINT s_customer_region_id_fk
FOREIGN KEY (region_id) REFERENCES s_region (id);
ALTER TABLE s_ord
ADD CONSTRAINT s_ord_customer_id_fk
FOREIGN KEY (customer_id) REFERENCES s_customer (id);
ALTER TABLE s_ord
ADD CONSTRAINT s_ord_sales_rep_id_fk
FOREIGN KEY (sales_rep_id) REFERENCES s_emp (id);
ALTER TABLE s_product
ADD CONSTRAINT s_product_image_id_fk
FOREIGN KEY (image_id) REFERENCES s_image (id);
ALTER TABLE s_product
ADD CONSTRAINT s_product_longtext_id_fk
FOREIGN KEY (longtext_id) REFERENCES s_longtext (id);
ALTER TABLE s_item
ADD CONSTRAINT s_item_ord_id_fk
FOREIGN KEY (ord_id) REFERENCES s_ord (id);
ALTER TABLE s_item
ADD CONSTRAINT s_item_product_id_fk
FOREIGN KEY (product_id) REFERENCES s_product (id);
ALTER TABLE s_warehouse
ADD CONSTRAINT s_warehouse_manager_id_fk
FOREIGN KEY (manager_id) REFERENCES s_emp (id);
ALTER TABLE s_warehouse
ADD CONSTRAINT s_warehouse_region_id_fk
FOREIGN KEY (region_id) REFERENCES s_region (id);
ALTER TABLE s_inventory
ADD CONSTRAINT s_inventory_product_id_fk
FOREIGN KEY (product_id) REFERENCES s_product (id);
ALTER TABLE s_inventory
ADD CONSTRAINT s_inventory_warehouse_id_fk
FOREIGN KEY (warehouse_id) REFERENCES s_warehouse (id);
prompt Tables and sequences created and populated.
set feedback on
-插入数据
--
-- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved.
--
-- NAME
-- demobld.sql
--
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables in the
-- current schema. It should be STARTed by each user wishing to
-- access the tables. To remove the tables use the demodrop.sql
-- script.
--
-- USAGE
-- From within SQL*Plus, enter:
-- START demobld.sql
SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF
DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-12-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-2-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-2-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-4-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-9-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-5-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-6-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-12-1982', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-9-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-1-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-12-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-1-1982', 'DD-MM-YYYY'), 1300, NULL, 10);
INSERT INTO EMP VALUES
(9257, 'HUA''AN', 'CLERK', 7839,
TO_DATE('23-1-1985', 'DD-MM-YYYY'), 1300, NULL, NULL );
INSERT INTO EMP VALUES
(8526, 'QIU''XIANG', 'ANALYST', 7839,
TO_DATE('23-1-1985', 'DD-MM-YYYY'), 1400, NULL, NULL );
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);
CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);
INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
CREATE TABLE DUMMY
(DUMMY NUMBER);
INSERT INTO DUMMY VALUES (0);
COMMIT;
SET TERMOUT ON
PROMPT Demonstration table build is complete.
第五步：选择EMP表作为实体类的映射
- 创建Emp实体类，编写get和set方法，重写toString方法。
第六步：创建测试类
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import org.junit.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.List;
import java.util.Date;
public class TestUser {
private SqlSessionFactory sqlSessionFactory;
private Logger logger = Logger.getLogger(TestUser.class);
@Before
public void setUp() throws IOException {
//1.加载配置文件
String resouce = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resouce);
//2.创建回话工厂，传入mybatis的配置文件信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserInfoById() {
//3.通过回话工厂得到sqlsession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.通过sqlsession操作数据库，sqlsession
Emp emp= sqlSession.selectOne("BlogMapper.selectOne",1);
//关闭sqlsession对象
sqlSession.close();
logger.info(emp);
}
/**
* 查询所有用户
*/
@Test
public void findAll() {
//3.通过回话工厂得到sqlsession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.通过sqlsession操作数据库，sqlsession
List<Emp> lists= sqlSession.selectList("EmpMapper.findAllUser");
for(Emp emp:lists){
System.out.println(emp);
}
//关闭sqlsession对象
sqlSession.close();
}
/**
* 根据用户名查询用户信息
*/
@Test
public void userinfoFindByName() {
//3.通过回话工厂得到sqlsession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.通过sqlsession操作数据库，sqlsession
List<Emp> list= sqlSession.selectList("EmpMapper.findUserinfoByName","A");
if(list.size()>0){
for(Emp emp:list){
System.out.println(emp);
}
}else{
System.out.println("用户不存在");
}
//关闭sqlsession对象
sqlSession.close();
}
/**
* 根据用户名查询用户信息
*/
@Test
public void addUserinfo() {
Calendar calendar = Calendar.getInstance();
Date date = calendar.getTime();
//3.通过回话工厂得到sqlsession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
Emp emp = new Emp();
emp.setEmpno(7905);
emp.setEname("闫有泉");
emp.setJob("java工程");
emp.setMgr(7839);
emp.setSal(10000);
emp.setComm(300);
emp.setDeptno(10);
//4.通过sqlsession操作数据库，sqlsession
int count=0;
count= sqlSession.insert("EmpMapper.addUserinfo",emp);
if(count!=0){
System.out.println("插入成功");
}
//关闭sqlsession对象
sqlSession.close();
}
/**
* 删除用户
*/
@Test
public void deleteById() {
//3.通过回话工厂得到sqlsession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.通过sqlsession操作数据库，sqlsession
sqlSession.delete("EmpMapper.deleteOne",8526);
//关闭sqlsession对象
sqlSession.close();
}
}
mybatis配置的SQL语句
- 普通SQL语句
- select * from emp where username =#{} <==> select * from emp where username = ? ;
- 所以#{}可以防止SQL注入
-
select * from emp where username =${} <==> select * from emp where username = username ;
- 所以${}无法防止SQL注入
-
配置的SQL语句后面不要加分号，否则会报错：无效字符
- 动态SQL语句
<sql id="Update_By_Example_Where_Clause" >
<where >
<foreach collection="example.oredCriteria" item="criteria" separator="or" >
<if test="criteria.valid" >
<trim prefix="(" suffix=")" prefixOverrides="and" >
<foreach collection="criteria.criteria" item="criterion" >
<choose >
<when test="criterion.noValue" >
and ${criterion.condition}
</when>
<when test="criterion.singleValue" >
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue" >
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue" >
and ${criterion.condition}
<foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
#####什么是SQL注入？
2311
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
