Computer Science - Java Coding Assignment Homework #2
rakesh.raki
CST367467-Homework2Setup.zipCST367467-Homework2Setup/pom.xml
4.0.0 com.mycompany CST367467-Homework2Setup 1.0-SNAPSHOT jar UTF-8 21 21 com.mycompany.cst367467.homework2setup.CST367467Homework2Setup
CST367467-Homework2Setup/src/main/java/com/mycompany/cst367467/homework2setup/InitDB.java
CST367467-Homework2Setup/src/main/java/com/mycompany/cst367467/homework2setup/InitDB.java
/*
* Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
* Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Main.java to edit this template
*/
package
com
.
mycompany
.
cst367467
.
homework2setup
;
import
java
.
sql
.
Connection
;
import
java
.
sql
.
DriverManager
;
/**
* I actually just renamed SetupDB (MySQLTest) to InitDB
*
* and changed the table/column names...
*
@author
mruth
*/
public
class
InitDB
{
/**
*
@param
args the command line arguments
*/
public
static
void
main
(
String
[]
args
)
{
try
{
Connection
conn
=
DriverManager
.
getConnection
(
"jdbc:mysql://localhost:3306/database"
,
"user"
,
"user"
);
//jdbc:mysql://localhost/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
System
.
out
.
println
(
"If Table Exists... drop it like it's hot"
);
try
{
String
sql
=
"DROP TABLE PRODUCTS"
;
conn
.
createStatement
().
execute
(
sql
);
System
.
out
.
println
(
"Table Dropped!"
);
}
catch
(
Exception
e
)
{
System
.
out
.
println
(
"Table Didn't Exist"
);
}
try
{
String
sql
=
"DROP TABLE SELLERS"
;
conn
.
createStatement
().
execute
(
sql
);
System
.
out
.
println
(
"Table Dropped!"
);
}
catch
(
Exception
e
)
{
System
.
out
.
println
(
"Table Didn't Exist"
);
}
String
sql
=
"CREATE TABLE SELLERS ("
;
sql
=
sql
+
" SID INTEGER PRIMARY KEY,"
;
sql
=
sql
+
" NAME VARCHAR(50),"
;
sql
=
sql
+
" CITY VARCHAR(50),"
;
sql
=
sql
+
" STATE VARCHAR(2))"
;
conn
.
createStatement
().
execute
(
sql
);
System
.
out
.
println
(
"Table SELLERS created!"
);
sql
=
"CREATE TABLE PRODUCTS ("
;
sql
=
sql
+
" SKU INTEGER PRIMARY KEY,"
;
sql
=
sql
+
" NAME VARCHAR(50),"
;
sql
=
sql
+
" DESCRIPTION VARCHAR(200),"
;
sql
=
sql
+
" CATEGORY VARCHAR(50),"
;
sql
=
sql
+
" SID INTEGER,"
;
sql
=
sql
+
" PRICE DOUBLE ,"
;
sql
=
sql
+
" FOREIGN KEY (SID) REFERENCES SELLERS(SID))"
;
conn
.
createStatement
().
execute
(
sql
);
System
.
out
.
println
(
"Table PRODUCTS created!"
);
System
.
out
.
println
(
"DB Complete!"
);
conn
.
close
();
}
catch
(
Exception
e
)
{
System
.
out
.
println
(
e
);
}
}
}
CST367467-Homework2Setup/src/main/java/com/mycompany/cst367467/homework2setup/PopulateDB.java
CST367467-Homework2Setup/src/main/java/com/mycompany/cst367467/homework2setup/PopulateDB.java
/*
* Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
* Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Main.java to edit this template
*/
package
com
.
mycompany
.
cst367467
.
homework2setup
;
import
java
.
sql
.
Connection
;
import
java
.
sql
.
DriverManager
;
import
java
.
sql
.
ResultSet
;
import
java
.
sql
.
SQLException
;
import
java
.
util
.
ArrayList
;
import
java
.
util
.
Random
;
/**
* Renamed PopulateDBFixedBetter (MySQLTest) to PopulateDB
*
@author
mruth
*/
public
class
PopulateDB
{
public
static
Connection
conn
;
/**
*
@param
args the command line arguments
*/
public
static
void
main
(
String
[]
args
)
{
try
{
conn
=
DriverManager
.
getConnection
(
"jdbc:mysql://localhost:3306/database"
,
"user"
,
"user"
);
generateSellers
();
generateProducts
(
50
);
}
catch
(
Exception
e
)
{
System
.
out
.
println
(
e
);
}
}
public
static
void
generateSellers
()
{
String
[]
names
=
{
"Walmart"
,
"K-Mart"
,
"Best Buy"
,
"Circuit City"
,
"Target"
,
"Costco"
,
"CompUSA"
};
String
[]
cities
=
{
"Rogers"
,
"Garden City"
,
"Burnsville"
,
"Richmond"
,
"Roseville"
,
"Seattle"
,
"Addison"
};
String
[]
states
=
{
"AR"
,
"MI"
,
"MN"
,
"VA"
,
"MN"
,
"WA"
,
"TX"
};
Random
random
=
new
Random
();
for
(
int
i
=
0
;
i
<
names
.
length
;
i
++
)
{
boolean
done
=
false
;
while
(
!
done
)
{
try
{
int
sid
=
random
.
nextInt
(
8999999
)
+
1000000
;
String
sql
=
"INSERT INTO SELLERS VALUES ("
;
sql
=
sql
+
sid
+
","
;
sql
=
sql
+
"'"
+
names
[
i
]
+
"',"
;
sql
=
sql
+
"'"
+
cities
[
i
]
+
"',"
;
sql
=
sql
+
"'"
+
states
[
i
]
+
"')"
;
System
.
out
.
println
(
sql
);
conn
.
createStatement
().
execute
(
sql
);
done
=
true
;
}
catch
(
Exception
e
)
{
System
.
out
.
println
(
e
);
}
}
}
//need 7 digit number for reviewID
}
public
static
void
generateProducts
(
int
size
)
throws
SQLException
{
//need seller IDs
String
tempSQL
=
"SELECT DISTINCT(SID) FROM SELLERS"
;
ResultSet
rs
=
conn
.
createStatement
().
executeQuery
(
tempSQL
);
ArrayList
<
Integer
>
sellerIDS
=
new
ArrayList
();
while
(
rs
.
next
())
{
int
sid
=
rs
.
getInt
(
"SID"
);
sellerIDS
.
add
(
sid
);
}
for
(
int
id
:
sellerIDS
)
{
System
.
out
.
println
(
"ID:"
+
id
);
}
String
[]
names
=
{
"Final Fantasy"
,
"Dragon Quest"
};
String
[]
numbers
=
{
"I"
,
"II"
,
"III"
,
"IV"
,
"V"
,
"VI"
,
"VII"
,
"VIII"
,
"IX"
,
"X"
,
"XI"
,
"XII"
,
"XIII"
,
"XIV"
,
"XV"
,
"XVI"
};
String
description
=
"RPG Video game"
;
String
[]
categories
=
{
"Nintendo Switch"
,
"Nintendo DS"
,
"Nintendo 64"
,
"Nintendo Wii"
,
"Nintendo GameCube"
,
"Nintendo 3DS"
,
"Super NES"
,
"NES"
};
Random
random
=
new
Random
();
int
count
=
size
;
while
(
count
>
0
)
{
//need 7 digit number for reviewID
int
sku
=
random
.
nextInt
(
8999999
)
+
1000000
;
double
price
=
(
random
.
nextInt
(
5
)
*
10
)
+
9.99
;
String
name
=
names
[
random
.
nextInt
(
names
.
length
)]
+
" "
;
name
=
name
+
numbers
[
random
.
nextInt
(
numbers
.
length
)];
String
category
=
categories
[
random
.
nextInt
(
categories
.
length
)];
/*
sql = "CREATE TABLE PRODUCTS (";
sql = sql + " SKU INTEGER PRIMARY KEY,";
sql = sql + " NAME VARCHAR(50),";
sql = sql + " DESCRIPTION VARCHAR(200),";
sql = sql + " CATEGORY VARCHAR(50),";
sql = sql + " SID INTEGER,";
sql = sql + " PRICE DOUBLE ,";
sql = sql + " FOREIGN KEY (SID) REFERENCES SELLERS(SID))";
*/
String
sql
=
"INSERT INTO PRODUCTS VALUES ("
;
sql
=
sql
+
sku
+
","
;
sql
=
sql
+
"'"
+
name
+
"',"
;
sql
=
sql
+
"'"
+
description
+
"',"
;
sql
=
sql
+
"'"
+
category
+
"',"
;
sql
=
sql
+
+
sellerIDS
.
get
(
random
.
nextInt
(
sellerIDS
.
size
()))
+
","
;
sql
=
sql
+
price
+
")"
;
System
.
out
.
println
(
sql
);
try
{
conn
.
createStatement
().
execute
(
sql
);
count
--
;
}
catch
(
Exception
e
)
{
System
.
out
.
println
(
e
.
getMessage
());
}
}
}
}
