📄 db.java
字号:
/**
* 实现数据库的操作，维护
*/
import java.sql.*;
import java.math.*;
/**
* @author Jimmy
* 对KFC至少随机生成800个连锁店信息；
* 对KFC至少随机生成10万个顾客信息；
* 对KFC至少随机生成200万条订单记录，每张订单随机生成多条订单项记
*
*/
public class DB {
// all cities info
public static String china_city[][]={
{"BEIJING","北京市","北京市","华北"},{"SHANGHAI","上海市","上海市","华东"},
{"TIANJIN","天津市","天津市","华北"},{"HEFEI","合肥市","安徽省","华东"},
{"CHONGQIN","重庆市","重庆市","西南"},{"FUZHOU","福州市","福建省","华东"},
{"XIAMEN","厦门市","福建省","华东"},{"DONGWAN","东莞市","广东省","中南"},
{"GUANGZHOU","广州市","广东省","中南"},{"SHENZHEN","深圳市","广东省","中南"},
{"ZHUHAI","珠海市","广东省","中南"},{"SHIJIAZHUANG","石家庄市","河北省","华北"},
{"HAERBIN","哈尔滨市","黑龙江省","东北"},{"ZHENGZHOU","郑州市","河南省","华北"},
{"WUHAN","武汉市","湖北省","中南"},{"CHANGSHA","长沙市","湖南省","中南"},
{"NANJING","南京市","江苏省","华东"},{"NANCHANG","南昌市","江西省","中南"},
{"CHANGCHUN","长春市","吉林省","东北"},{"SHENYANG","沈阳市","辽宁省","东北"},
{"DALIAN","大连市","辽宁省","东北"},{"JINAN","济南市","山东省","华北"},
{"QINGDAO","青岛市","山东省","华北"},{"TAIYUAN","太原市","山西省","华北"},
{"XIAN","西安市","陕西省","西北"},{"CHENGDU","成都市","四川省","西南"},
{"KUNMING","昆明市","云南省","西南"},{"HANGZHOU","杭州市","浙江省","华东"},
{"NINGBO","宁波市","浙江省","华东"},{"CHANGZHOU","常州市","江苏省","华东"},
{"SUZHOU","苏州市","江苏省","华东"},{"WUXI","无锡市","江苏省","华东"}
};
// char a-z A-Z
public static String str[]={
"A","B","C","D","E","F","G","H","I","J","K","L",
"M","N","O","P","Q","R","S","T","U","V","W","X",
"Y","Z","a","b","c","d","e","f","g","h","i","j",
"k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"};
// number 0-9
public static char num[]={'0','1','2','3','4','5','6','7','8','9'};
// occupation
public static String occupation[]={"军人","教师","学生","教授","工人","农民","经理","董事长","顾问","组长","社长","音乐人","记者","公务员"};
// food list
public static String food[]={
"K0001","K0002","K0003","K0004","K0005","K0006","K0007","K0008","K0009","K0010",
"K0011","K0012","K0013","K0014","K0015","K0016","K0017","K0018","K0019","K0020",
"K0021","K0022","K0023","K0024","K0025"};
/*
* 插入城市信息 KFC_CHINACITY
* CITYNO CHAR(20) PRIMARY KEY, 城市编号
* CITYNAME CHAR(20) NOT NULL, 城市名称
* PROVINCE CHAR(10) NOT NULL, 所在省份
* CHINAAREA CHAR(10) NOT NULL 所属区域
*/
public void insert_city_info()
{
try
{
// 1.get connection
Connection conn = get_conn("oradb","ZYP","ZYP");
//2.generate information
String sql = "INSERT INTO KFC_CHINACITY (CITYNO,CITYNAME,PROVINCE,CHINAAREA) VALUES(?,?,?,?)";
PreparedStatement stmt = conn.prepareStatement(sql);
for(int i=0;i<32;i++)
{
stmt.setString(1,china_city[i][0]);
stmt.setString(2,china_city[i][1]);
stmt.setString(3,china_city[i][2]);
stmt.setString(4,china_city[i][3]);
stmt.addBatch();
}
//3.execute insert
stmt.executeBatch();
//4. close connection
conn.close();
}
catch(Exception e)
{
System.out.printf("insert fail e : "+e);
}
}
/*
* 对KFC至少随机生成800个连锁店信息
* KFC_CHAINSTORELIST 连锁店列表
* STORENO NUMBER(5) PRIMARY KEY, 连锁店编号
* STORENAME CHAR(20) NOT NULL, 连锁店名称
* LOCATEDCITY CHAR(20) NOT NULL
* CONSTRAINT LOCATEDCITY_FK
* REFERENCES KFC_CHINACITY(CITYNO) 所在城市，外键引用
*/
public void insert_store_info()
{
try
{
//1.get connection
Connection conn = get_conn("oradb","ZYP","ZYP");
//2.generate random information
String sql = "INSERT INTO KFC_CHAINSTORELIST (STORENO,STORENAME,LOCATEDCITY) VALUES(?,?,?)";
PreparedStatement stmt = conn.prepareStatement(sql);
for(int i=0;i<800;i++)
{
stmt.setInt(1,i);
stmt.setString(2,get_random_str(20));
stmt.setString(3,china_city[(int)(Math.random()*32)][0]);
stmt.addBatch();
}
//3.execute insert
stmt.executeBatch();
//4.close connection
conn.close();
}
catch(Exception e)
{
System.out.printf("insert fail e : "+e);
}
return;
}
/*
* 对KFC至少随机生成10万个顾客信息
* KFC_CUSTOMERLIST 顾客列表
*
* CUSTOMERNO NUMBER(8) PRIMARY KEY, 顾客编号
* CUSTOMERNAME VARCHAR2(20) NOT NULL, 顾客姓名
* IDCARDNO CHAR(20), 身份证号
* SEX CHAR(2), 性别
* AGE SMALLINT, 年龄
* OCCUPATION VARCHAR2(40) 职位
*/
public void insert_customer_info()
{
String sql = "INSERT INTO KFC_CUSTOMERLIST (CUSTOMERNO,CUSTOMERNAME,IDCARDNO,SEX,AGE,OCCUPATION) VALUES(?,?,?,?,?,?)";
try
{
//1.get connection
Connection conn = get_conn("oradb","ZYP","ZYP");
//2.generate random information
PreparedStatement stmt = conn.prepareStatement(sql);
for(int i=0;i<100000;i++)
{
stmt.setInt(1,i);
stmt.setString(2,get_random_str(20));
stmt.setString(3,(i+100000000)+"");
stmt.setString(4,Math.random()>0.5?"F":"M");
stmt.setInt(5,(int)(Math.random()*90)+10);
stmt.setString(6,occupation[(int)(Math.random()*14)]);
stmt.addBatch();
}
//3.execute insert
stmt.executeBatch();
//4.close connection
conn.close();
}
catch(Exception e)
{
System.out.printf(sql);
System.out.printf("insert fail e : "+e);
}
return;
}
/*
* 对KFC至少随机生成200万条订单记录，每张订单随机生成多条订单项记
*
* TABLE KFC_ORDER 订单
*
* ORDERNO NUMBER(10) PRIMARY KEY, 订单编号
* ORDERDATE DATE NOT NULL, 开单日期
* STORENO NUMBER(5) NOT NULL CONSTRAINT STORENO_FK
* REFERENCES KFC_CHAINSTORELIST(STORENO), 连锁店编号
* CUSTOMERNO NUMBER(8) NOT NULL CONSTRAINT CUSTOMERNO_FK
* REFERENCES KFC_CUSTOMERLIST(CUSTOMERNO), 顾客编号
* ORDERTYPE CHAR(10) 订单类型
*
*/
public void insert_order_info()
{
String sql = "INSERT INTO KFC_ORDER (ORDERNO,ORDERDATE,STORENO,CUSTOMERNO,ORDERTYPE) VALUES(?,?,?,?,?)";
try
{
//1.get connection
Connection conn = get_conn("oradb","ZYP","ZYP");
//2.generate random information
PreparedStatement stmt = conn.prepareStatement(sql);
for(int i=0;i<2000000;i++)
{
stmt.setInt(1,i);
java.util.Date current_time = new java.util.Date();
Timestamp ts = new Timestamp(current_time.getTime());
ts.setNanos(0);
stmt.setTimestamp(2,ts);
stmt.setInt(3,(int)(Math.random()*800));
stmt.setInt(4,(int)(Math.random()*100000));
stmt.setString(5,Math.random()>0.5?"堂吃":"外卖");
stmt.addBatch();
if(i%10000==0)
{
//3.execute insert
stmt.executeBatch();
stmt.clearBatch();
}
}
stmt.executeBatch();
stmt.clearBatch();
//4.close connection
conn.close();
}
catch(Exception e)
{
System.out.printf(sql);
System.out.printf("insert fail e : "+e);
}
return;
}
/*
* KFC_ORDERITEMS 订单项表
*
* ITEMNO NUMBER(12) PRIMARY KEY, 订单项编号
* ORDERNO NUMBER(10) NOT NULL CONSTRAINT ORDERNO_FK
* REFERENCES KFC_ORDER(ORDERNO), 订单编号
* FOODNO CHAR(8) NOT NULL CONSTRAINT FOODNO_FK
* REFERENCES KFC_FOODLIST(FOODNO), 食品编号
* ORDEREDNUMBER NUMBER(6) DEFAULT(1) 购买数量
*
*/
public void insert_orderItem_info()
{
String sql = "INSERT INTO KFC_ORDERITEMS (ITEMNO,ORDERNO,FOODNO,ORDEREDNUMBER) VALUES(?,?,?,?)";
try
{
//1.get connection
Connection conn = get_conn("oradb","ZYP","ZYP");
//2.generate random information
PreparedStatement stmt = conn.prepareStatement(sql);
int j = 0;
for(int i=0;i<1900000;i++)
{
int count = (int)(Math.random()*5);
for(int k=0;k<count;k++)
{
stmt.setInt(1,j);
stmt.setInt(2,i);
stmt.setString(3,food[(int)(Math.random()*25)]);
stmt.setInt(4,count);
stmt.addBatch();
j++;
}
if(i%10000==0)
{
//3.execute insert
stmt.executeBatch();
stmt.clearBatch();
}
}
stmt.executeBatch();
stmt.clearBatch();
//4.close connection
conn.close();
}
catch(Exception e)
{
System.out.printf(sql);
System.out.printf("insert fail e : "+e);
}
return;
}
/*
* help function get the connection to oracle
*
*/
public Connection get_conn(String SID,String userName,String password)
{
Connection conn = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url="jdbc:oracle:thin:@localhost:1521:"+SID;
conn= DriverManager.getConnection(url,userName,password);
System.out.printf(" CONNECT TO ORACLE SUCCESS!");
}
catch(Exception e)
{
System.out.printf("connect fail e : "+e);
}
return conn;
}
/*
* get data from access file
*/
public void convert_city()
{
String china_city [][]=new String [32][4];
String temp="";
int i=0;
Connection con;
Statement stmt;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=F:\\DB\\DB_Helper\\DB_HELPER\\MCD.mdb";
con = DriverManager.getConnection(url, "", "");
stmt = con.createStatement();
String query = "select * from MCD_CHINACITY";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()&&i<32) {
china_city[i][0]=rs.getString("CITYNO").trim();
china_city[i][1]=rs.getString("CITYNAME").trim();
china_city[i][2]=rs.getString("PROVINCE").trim();
china_city[i][3]=rs.getString("CHINAAREA").trim();
temp="{\""+china_city[i][0]+"\",\""+china_city[i][1]+"\",\""+china_city[i][2]+"\",\""+china_city[i][3]+"\"},";
System.out.print(temp);
i++;
}
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* get food data from access file
* MCD_FOODLIST 食品列表
*
* FOODNO CHAR(8) PRIMARY KEY, 食品编号
* FOODNAME VARCHAR2(40) NOT NULL, 食品名称
* FOODDESCRIPTION VARCHAR2(400), 食品描述
* DELIVERYDATE DATE, 推出时间
* SPEC CHAR(4) NOT NULL, 食品规格
* PRICE NUMBER(6,2) NOT NULL 食品单价
*/
public void convert_food()
{
//
Connection con = null;
ResultSet rs = null;
Statement stmt;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=F:\\DB\\DB_Helper\\DB_HELPER\\MCD.mdb";
con = DriverManager.getConnection(url, "", "");
stmt = con.createStatement();
String query = "select * from MCD_FOODLIST";
rs = stmt.executeQuery(query);
//1.get connection
Connection conn = get_conn("oradb","ZYP","ZYP");
String sql = "INSERT INTO KFC_FOODLIST (FOODNO,FOODNAME,FOODDESCRIPTION,DELIVERYDATE,SPEC,PRICE) VALUES(?,?,?,?,?,?)";
//2.generate random information
PreparedStatement mystmt = conn.prepareStatement(sql);
while(rs.next())
{
mystmt.setString(1,rs.getString("FOODNO"));
mystmt.setString(2,rs.getString("FOODNAME"));
mystmt.setString(3,rs.getString("FOODDESCRIPTION"));
mystmt.setDate(4,rs.getDate("DELIVERYDATE"));
mystmt.setString(5,rs.getString("SPEC"));
mystmt.setFloat(6,rs.getFloat("PRICE"));
mystmt.addBatch();
}
mystmt.executeBatch();
con.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* get a random str which constructed with a-z A-Z
* @strlen the max length of the string
* @return return the random str
*/
public String get_random_str(int strlen)
{
String result="";
strlen = strlen - 5;
int count =(int)(Math.random()*strlen) + 5;
for(int i=0;i<count;i++)
{
int pos = (int)(Math.random()* 52);
result += str[pos];
}
return result;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -