public boolean checkUpdate(ArrayList<Object> where) {
DBConnect db = DBConnect.getDBConnect();
String sql = "select count(*) from product where image=?"; // image 값은 URL이라 타 상품과 중복 없음..아마도..
ArrayList<Object> checkWhere = new ArrayList<Object>();
checkWhere.add(where.get(1));
ResultSet rs = db.selectDB(sql, checkWhere);
System.out.println("checkWhere : " + where.get(1));
try {
rs.next();
int cnt;
if ((cnt = rs.getInt(1)) > 0) {
System.out.println(where.get(3) + " : duplication " + cnt);
return false;
} else {
System.out.println(cnt);
return true;
}
} catch (SQLException e) {
System.out.println(sData.getBRAND_NAME() + " checkUpdate Error : " + e.getMessage());
return false;
} finally {
DBClose.dbClose(null, null, rs);
}
}
public boolean insertDataGift(ArrayList<Object> where) {
// brand 0,name 1 , giftname 2 ,image 3 ,price 4 ,lastupdate 5
DBConnect db = DBConnect.getDBConnect();
String sql =
"insert into gift values(?,?,?,?,?,?) on duplicate key update giftname=?,image=?,price=?,lastupdate=?";
ArrayList<Object> insertWhere = new ArrayList<Object>();
insertWhere.addAll(where);
insertWhere.add(where.get(2));
insertWhere.add(where.get(3));
insertWhere.add(where.get(4));
insertWhere.add(where.get(5));
if (db.modifyDB(sql, insertWhere)) {
System.out.println(
where.get(0) + " : " + where.get(1) + " + " + where.get(2) + " Data Insert OR Update OK");
return true;
} else {
System.out.println(
where.get(0)
+ " : "
+ where.get(1)
+ " + "
+ where.get(2)
+ " Data Insert OR Update Fail");
return false;
}
}
public static UserID createNewAccount(Scanner scan) {
String username = checkString(Func.USERNAME, scan, true, null);
String password = checkString(Func.PASSWORD, scan, true, null);
String name = checkString(Func.NAME, scan, true, null);
// Attributes
Attributes.Gender gender =
(Attributes.Gender) setUpEnumAttribute(scan, Attributes.AttributeEnum.GENDER);
int age = makeSureValInRange(scan, 18, 200, "age", ValType.INTEGER, false, true).intValue();
int weight =
makeSureValInRange(scan, 70, 2000, "weight", ValType.INTEGER, false, true).intValue();
double height =
makeSureValInRange(scan, 3, 10, "height", ValType.DOUBLE, false, true).doubleValue();
Attributes.Sexuality sexuality =
(Attributes.Sexuality) setUpEnumAttribute(scan, Attributes.AttributeEnum.SEXUALITY);
Attributes.EyeColor eyeColor =
(Attributes.EyeColor) setUpEnumAttribute(scan, Attributes.AttributeEnum.EYE_COLOR);
Attributes.HairColor hairColor =
(Attributes.HairColor) setUpEnumAttribute(scan, Attributes.AttributeEnum.HAIR_COLOR);
Attributes.Ethnicity ethnicity =
(Attributes.Ethnicity) setUpEnumAttribute(scan, Attributes.AttributeEnum.ETHNICITY);
Attributes.Education education =
(Attributes.Education) setUpEnumAttribute(scan, Attributes.AttributeEnum.EDUCATION);
Attributes.Zodiac zodiac =
(Attributes.Zodiac) setUpEnumAttribute(scan, Attributes.AttributeEnum.ZODIAC);
// preparing the fields to be packaged and updated in database
String[] strFields = new String[Attributes.NUM_ENUM_FIELDS + UserID.NUM_UID_ENTRIES];
int i = 0;
strFields[i] = username;
strFields[++i] = password;
strFields[++i] = name;
strFields[++i] = gender.toString();
strFields[++i] = sexuality.toString();
strFields[++i] = ethnicity.toString();
strFields[++i] = eyeColor.toString();
strFields[++i] = hairColor.toString();
strFields[++i] = education.toString();
strFields[++i] = zodiac.toString();
DBConnect connection = new DBConnect();
connection.insertRow(strFields, age, weight, height);
// Update registered users hash table
Attributes attr =
new Attributes(
gender, sexuality, eyeColor, hairColor, ethnicity, education, zodiac, age, weight,
height);
UserID user = new UserID(username, password, name, attr);
registeredUsers.put(username, user);
return user;
}
private void createBlood_12Table() {
try {
sta.setText("Creating Blood Test 12 table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE IF NOT EXISTS `blood_test_12` (\n"
+ " `id` varchar(10) NOT NULL,\n"
+ " `alt_sgpt` varchar(20) NOT NULL,\n"
+ " `gamma` varchar(20) NOT NULL,\n"
+ " `mcv` varchar(20) NOT NULL\n"
+ ")";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
// JOptionPane.showMessageDialog(null, "Blood 12 table has been created successfully", "System
// Message", JOptionPane.INFORMATION_MESSAGE);
sta.setText("Blood 12 table has been created successfully");
} catch (Exception e) {
sta.setText(e.getMessage());
// JOptionPane.showMessageDialog(null, e.getMessage());
}
}
private void createAuditTable() {
try {
sta.setText("Creating AUDIT table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE IF NOT EXISTS `audit` (\n"
+ " `id` varchar(10) NOT NULL,\n"
+ " `audit_1` int(11) NOT NULL,\n"
+ " `audit_2` int(11) NOT NULL,\n"
+ " `audit_3` int(11) NOT NULL,\n"
+ " `audit_4` int(11) NOT NULL,\n"
+ " `audit_5` int(11) NOT NULL,\n"
+ " `audit_6` int(11) NOT NULL,\n"
+ " `audit_7` int(11) NOT NULL,\n"
+ " `audit_8` int(11) NOT NULL,\n"
+ " `audit_9` int(11) NOT NULL,\n"
+ " `audit_10` int(11) NOT NULL\n"
+ ")";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
// JOptionPane.showMessageDialog(null, "Audit table has been created successfully", "System
// Message", JOptionPane.INFORMATION_MESSAGE);
sta.setText("Audit table has been created successfully");
} catch (SQLException | HeadlessException e) {
sta.setText(e.getMessage());
// JOptionPane.showMessageDialog(null, e.getMessage());
}
}
private void createMiniTable() {
try {
sta.setText("Creating MINI table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE IF NOT EXISTS `mini` (\n"
+ " `alcohol_dependence_current` varchar(5) NOT NULL,\n"
+ " `alcohol_abuse_current` varchar(5) NOT NULL,\n"
+ " `psychotic_disoeder_current` varchar(5) NOT NULL,\n"
+ " `psychotic_disoeder_lifetime` varchar(5) NOT NULL,\n"
+ " `id` varchar(10) NOT NULL\n"
+ ")";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
// JOptionPane.showMessageDialog(null, "MINI table has been created successfully", "System
// Message", JOptionPane.INFORMATION_MESSAGE);
sta.setText("MINI table has been created successfully");
} catch (Exception e) {
sta.setText(e.getMessage());
// JOptionPane.showMessageDialog(null, e.getMessage());
}
}
public boolean endingEventProductDelete(String lastUpdate, String table) {
DBConnect db = DBConnect.getDBConnect();
String sql =
"delete from "
+ table
+ " where lastupdate!="
+ lastUpdate
+ " brand="
+ sData.getBRAND_NAME();
if (db.modifyDB(sql, null)) {
System.out.println("Old Data Delete");
return true;
} else {
System.out.println("Old Data Delete Fail");
return false;
}
}
public String login(User user) throws SQLException {
this.user = dbcon.login(user);
kundvagn.setKundID(this.user.getUserID());
if (this.user.isLoggedin()) {
return "admin.xhtml";
} else {
return null;
}
}
private void createTlfbTable() {
try {
sta.setText("Creating TLFB table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE IF NOT EXISTS `tlfb` (\n"
+ " `id` varchar(10) NOT NULL,\n"
+ " `tlfb_1` int(11) NOT NULL,\n"
+ " `tlfb_2` int(11) NOT NULL,\n"
+ " `tlfb_3` int(11) NOT NULL,\n"
+ " `tlfb_4` int(11) NOT NULL,\n"
+ " `tlfb_5` int(11) NOT NULL,\n"
+ " `tlfb_6` int(11) NOT NULL,\n"
+ " `tlfb_7` int(11) NOT NULL,\n"
+ " `tlfb_8` int(11) NOT NULL,\n"
+ " `tlfb_9` int(11) NOT NULL,\n"
+ " `tlfb_10` int(11) NOT NULL,\n"
+ " `tlfb_11` int(11) NOT NULL,\n"
+ " `tlfb_12` int(11) NOT NULL,\n"
+ " `tlfb_13` int(11) NOT NULL,\n"
+ " `tlfb_14` int(11) NOT NULL,\n"
+ " `tlfb_15` int(11) NOT NULL,\n"
+ " `tlfb_16` int(11) NOT NULL,\n"
+ " `tlfb_17` int(11) NOT NULL,\n"
+ " `tlfb_18` int(11) NOT NULL,\n"
+ " `tlfb_19` int(11) NOT NULL,\n"
+ " `tlfb_20` int(11) NOT NULL,\n"
+ " `tlfb_21` int(11) NOT NULL,\n"
+ " `tlfb_22` int(11) NOT NULL,\n"
+ " `tlfb_23` int(11) NOT NULL,\n"
+ " `tlfb_24` int(11) NOT NULL,\n"
+ " `tlfb_25` int(11) NOT NULL,\n"
+ " `tlfb_26` int(11) NOT NULL,\n"
+ " `tlfb_27` int(11) NOT NULL,\n"
+ " `tlfb_28` int(11) NOT NULL,\n"
+ " `tlfb_29` int(11) NOT NULL,\n"
+ " `tlfb_30` int(11) NOT NULL,\n"
+ " `tlfb_31` int(11) NOT NULL\n"
+ ")";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
JOptionPane.showMessageDialog(
null,
"TLFB table has been created successfully",
"System Message",
JOptionPane.INFORMATION_MESSAGE);
sta.setText("TLFB table has been created successfully");
} catch (Exception e) {
// JOptionPane.showMessageDialog(null, e.getMessage());
sta.setText(e.getMessage());
}
}
// brand,image,type,name,price,lastupdate
// product 의 키값=brand+name
// 중복되는 키값이 있으면 해당 열의 값을 업데이트(image,type,price,lastupdate)
// 키값이 없으면 추가
public boolean insertData(ArrayList<Object> where) {
String sql =
"insert into product values(?,?,?,?,?,?) on duplicate key update image=?, type=?,price=?,lastupdate=?";
DBConnect db = DBConnect.getDBConnect();
ArrayList<Object> insertWhere = new ArrayList<Object>();
insertWhere.addAll(where);
insertWhere.add(where.get(1)); // image
insertWhere.add(where.get(2)); // type
insertWhere.add(where.get(4)); // price
insertWhere.add(where.get(5)); // lastupdate
if (db.modifyDB(sql, insertWhere)) {
System.out.println(
sData.getBRAND_NAME() + " : " + where.get(3) + " Data Insert OR Update OK");
return true;
} else {
System.out.println(sData.getBRAND_NAME() + " Data Insert Error");
return false;
}
}
private void createMMQb_12Table() {
try {
sta.setText("Creating MMQb 12 table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE IF NOT EXISTS `mmqb_12` (\n"
+ " `id` varchar(10) NOT NULL,\n"
+ " `mmqb_1` varchar(2) NOT NULL,\n"
+ " `mmqb_2` varchar(2) NOT NULL,\n"
+ " `mmqb_3` varchar(2) NOT NULL,\n"
+ " `mmqb_4` varchar(2) NOT NULL,\n"
+ " `mmqb_5` varchar(2) NOT NULL,\n"
+ " `mmqb_6` varchar(2) NOT NULL,\n"
+ " `mmqb_7` varchar(2) NOT NULL,\n"
+ " `mmqb_8` varchar(2) NOT NULL,\n"
+ " `mmqb_9` varchar(2) NOT NULL,\n"
+ " `mmqb_10` varchar(2) NOT NULL,\n"
+ " `mmqb_11` varchar(2) NOT NULL,\n"
+ " `mmqb_12` varchar(2) NOT NULL,\n"
+ " `mmqb_13` varchar(2) NOT NULL,\n"
+ " `mmqb_14` varchar(2) NOT NULL,\n"
+ " `mmqb_15` varchar(2) NOT NULL,\n"
+ " `mmqb_16` varchar(2) NOT NULL,\n"
+ " `mmqb_17` varchar(2) NOT NULL,\n"
+ " `mmqb_18` varchar(2) NOT NULL,\n"
+ " `mmqb_19` varchar(2) NOT NULL,\n"
+ " `mmqb_20` varchar(2) NOT NULL,\n"
+ " `mmqb_21` varchar(2) NOT NULL,\n"
+ " `mmqb_22` varchar(2) NOT NULL,\n"
+ " `mmqb_23` varchar(2) NOT NULL,\n"
+ " `mmqb_24` varchar(2) NOT NULL,\n"
+ " `mmqb_25` varchar(2) NOT NULL,\n"
+ " `mmqb_26` varchar(2) NOT NULL,\n"
+ " `mmqb_27` varchar(2) NOT NULL,\n"
+ " `mmqb_28` varchar(2) NOT NULL,\n"
+ " `mmqb_29` varchar(2) NOT NULL,\n"
+ " `mmqb_30` varchar(2) NOT NULL\n"
+ ")";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
// JOptionPane.showMessageDialog(null, "MMQb 12 table has been created successfully", "System
// Message", JOptionPane.INFORMATION_MESSAGE);
sta.setText("MMQb 12 table has been created successfully");
} catch (SQLException | HeadlessException e) {
// JOptionPane.showMessageDialog(null, e.getMessage());
sta.setText(e.getMessage());
}
}
public UserManager() {
registeredUsers = new Hashtable<String, UserID>(HTABLE_INIT_CAP, HTABLE_LOAD_FACT);
DBConnect connection = new DBConnect();
connection.setResultSet();
ResultSet data = connection.getData();
try {
while (data.next()) {
String username = data.getString("Username");
String password = data.getString("Password");
String name = data.getString("Name");
String[] enumFieldString = new String[Attributes.NUM_ENUM_FIELDS];
int start = UserID.NUM_UID_ENTRIES + 1;
int i = start;
int j = 0;
while (i < start + Attributes.NUM_ENUM_FIELDS) {
enumFieldString[j] = data.getString(i);
i++;
j++;
}
int age = data.getInt("Age");
int weight = data.getInt("Weight");
double height = data.getDouble("Height");
Attributes attributes = new Attributes(enumFieldString, age, weight, height);
UserID user = new UserID(username, password, name, attributes);
registeredUsers.put(username, user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
connection.closeConnection();
}
public String sendKundvagn() throws SQLException {
dbcon.createOrder(kundvagn);
String to = user.geteMail();
String password = "******";
String from = "Javaiskolan";
String host = "smtp.gmail.com";
Properties props = System.getProperties();
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.starttls.enable", "true");
props.put("mail.smtp.host", "smtp.gmail.com");
props.put("mail.smtp.port", "587");
Session session =
Session.getInstance(
props,
new javax.mail.Authenticator() {
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(to, password);
}
});
try {
// skapar upp en medelande och sätter vart det kommer ifrån
MimeMessage message = new MimeMessage(session);
message.setFrom(new InternetAddress(from, "h12augde"));
message.addRecipient(Message.RecipientType.TO, new InternetAddress(to, "h12augde"));
message.setSubject("Thanks for your order");
StringBuilder sb = new StringBuilder();
for (Vara v : kundvagn.getVara()) {
sb.append(v.getNamn() + "\t");
sb.append(v.getAntal() + "\t");
sb.append(v.getPris() + "kr \n");
}
sb.append("Sum: " + getSum() + "kr");
message.setText(sb.toString());
// skrickar medeleandet
Transport.send(message);
} catch (MessagingException ex) {
ex.printStackTrace();
;
} catch (UnsupportedEncodingException ex) {
Logger.getLogger(VarorDB.class.getName()).log(Level.SEVERE, null, ex);
}
return "paypal.xhtml";
}
private void insertToTable() {
try {
// Statement stmt;
Connection conn1 = DBConnect.Connect();
// ResultSet rs=null;
PreparedStatement pst1 = null;
String sql = "INSERT INTO user (user_name, password) VALUES(?, ?);";
pst1 = conn1.prepareStatement(sql);
pst1.setString(1, USER.getText());
pst1.setString(2, PASS.getText());
pst1.execute();
JOptionPane.showMessageDialog(null, "Saved");
} catch (SQLException | HeadlessException ex) {
sta.setText(ex.getMessage());
// JOptionPane.showMessageDialog(null, ex.getMessage());
}
}
private void createUserTable() {
try {
sta.setText("Creating User table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE `user` (`user_name` varchar(10) NOT NULL,`password` varchar(10) NOT NULL) ";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
// JOptionPane.showMessageDialog(null, "User table has been created successfully", "System
// Message", JOptionPane.INFORMATION_MESSAGE);
sta.setText("User table has been created successfully");
} catch (SQLException | HeadlessException e) {
sta.setText(e.getMessage());
// JOptionPane.showMessageDialog(null, e.getMessage());
}
}
private void createPatientTable() {
try {
sta.setText("Creating Patient table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE IF NOT EXISTS `patient` (\n"
+ " `id` varchar(10) NOT NULL,\n"
+ " `name` varchar(30) NOT NULL,\n"
+ " `gender` varchar(10) NOT NULL,\n"
+ " `religion` varchar(20) NOT NULL,\n"
+ " `race` varchar(20) NOT NULL,\n"
+ " `town` varchar(20) NOT NULL,\n"
+ " `employeement` varchar(30) NOT NULL,\n"
+ " `date_birth` date NOT NULL,\n"
+ " `register_date` date NOT NULL,\n"
+ " `date_t6` date DEFAULT NULL,\n"
+ " `date_t12` date DEFAULT NULL,\n"
+ " `email` varchar(30) NOT NULL,\n"
+ " `telephone` varchar(15) NOT NULL,\n"
+ " `contact_person` varchar(100) NOT NULL,\n"
+ " `adress` varchar(70) NOT NULL,\n"
+ " `refered_by` varchar(20) NOT NULL,\n"
+ " `age` varchar(15) NOT NULL,\n"
+ " `diognosis` varchar(50) NOT NULL,\n"
+ " `level_motivation` varchar(30) NOT NULL,\n"
+ " PRIMARY KEY (`id`)\n"
+ ") ";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
// JOptionPane.showMessageDialog(null, "Patient table has been created successfully", "System
// Message", JOptionPane.INFORMATION_MESSAGE);
sta.setText("Patient table has been created successfully");
} catch (SQLException | HeadlessException e) {
// JOptionPane.showMessageDialog(null, e.getMessage());
sta.setText(e.getMessage());
}
}
private void createStateTable() {
try {
sta.setText("Creating State table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE IF NOT EXISTS `state` (\n"
+ " `T0_audit` varchar(1) NOT NULL,\n"
+ " `T0_tlfb` varchar(1) NOT NULL,\n"
+ " `T0_mmqa` varchar(1) NOT NULL,\n"
+ " `T0_mmqb` varchar(1) NOT NULL,\n"
+ " `T0_qles_qsf` varchar(1) NOT NULL,\n"
+ " `T6_tlfb` varchar(1) NOT NULL,\n"
+ " `T6_mmqa` varchar(1) NOT NULL,\n"
+ " `T6_mmqb` varchar(1) NOT NULL,\n"
+ " `T6_qles_qsf` varchar(1) NOT NULL,\n"
+ " `T6_blood` varchar(1) NOT NULL,\n"
+ " `T12_audit` varchar(1) NOT NULL,\n"
+ " `T12_tlfb` varchar(1) NOT NULL,\n"
+ " `T12_mmqa` varchar(1) NOT NULL,\n"
+ " `T12_mmqb` varchar(1) NOT NULL,\n"
+ " `T12_qles_qsf` varchar(1) NOT NULL,\n"
+ " `T12_blood` varchar(1) NOT NULL,\n"
+ " `id` varchar(10) NOT NULL,\n"
+ " `T0_blood` varchar(1) NOT NULL,\n"
+ " PRIMARY KEY (`id`)\n"
+ ") ";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
// JOptionPane.showMessageDialog(null, "State table has been created successfully", "System
// Message", JOptionPane.INFORMATION_MESSAGE);
sta.setText("State table has been created successfully");
} catch (Exception e) {
// JOptionPane.showMessageDialog(null, e.getMessage());
sta.setText(e.getMessage());
}
}
private void createQlefQsf_12Table() {
try {
sta.setText("Creating Q LEF Q SF 12 table");
Connection connection = DBConnect.Connect();
String sql =
"CREATE TABLE IF NOT EXISTS `q_lef_q_sf_12` (\n"
+ " `id` varchar(10) NOT NULL,\n"
+ " `q_lef_q_sf_1` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_2` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_3` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_4` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_5` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_6` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_7` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_8` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_9` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_10` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_11` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_12` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_13` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_14` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_15` int(11) NOT NULL,\n"
+ " `q_lef_q_sf_16` int(11) NOT NULL\n"
+ ")";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
// JOptionPane.showMessageDialog(null, "Q LEF QSF 12 table has been created successfully",
// "System Message", JOptionPane.INFORMATION_MESSAGE);
sta.setText("Q LEF QSF 12 table has been created successfully");
} catch (Exception e) {
// JOptionPane.showMessageDialog(null, e.getMessage());
sta.setText(e.getMessage());
}
}
/** Creates a new instance of VarorDB */
public VarorDB() throws SQLException {
dbcon = new DBConnect();
al = dbcon.getVaror();
}
public ArrayList<Kategorier> getKategorier() throws SQLException {
return dbcon.getKategorier();
}
public void kategorierLista(ValueChangeEvent e) throws SQLException {
// assign new value to country
kategoriID = Integer.parseInt(e.getNewValue().toString());
al = dbcon.getVarorByKategori(kategoriID);
}
// http://www.tutorialspoint.com/jsf/jsf_valuechangelistener_tag.htm
public void orderHistory(ValueChangeEvent e) throws SQLException {
// assign new value to country
orderID = Integer.parseInt(e.getNewValue().toString());
orderHistorik = dbcon.getOrderHistorik(orderID);
}
public ArrayList<Orders> getOrders() throws SQLException {
return dbcon.getOrders(user);
}
public String newUser(User user) throws SQLException {
dbcon.newUser(user);
return "index.xhtml";
}
public int getOrderName() throws SQLException {
ArrayList<Orders> ord = dbcon.getOrders(user);
int size = ord.size();
return ord.get(size - 1).getOrderID();
}
public User retrieveFromDB(Long id) {
User user = new User();
UserDetail userdetails = new UserDetail();
Address addr = new Address();
List<EducationDetails> education = new ArrayList<EducationDetails>();
List<CarrerDetail> career = new ArrayList<CarrerDetail>();
OtherDetails otherDetail = new OtherDetails();
user.setId(id);
try {
Connection con = DBConnect.connect();
PreparedStatement stmt = con.prepareStatement(DbQueries.SELECT_USER_BY_ID);
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
user.setEmail(rs.getString(2));
user.setPassword(rs.getString(3));
}
stmt = con.prepareStatement(DbQueries.SELECT_USERDETAILS);
stmt.setLong(1, id);
ResultSet rs1 = stmt.executeQuery();
while (rs1.next()) {
userdetails.setFirstName(rs1.getString(3));
userdetails.setLastName(rs1.getString(4));
userdetails.setDob(rs1.getDate(5));
userdetails.setMobile(rs1.getString(6));
userdetails.setFatherName(rs1.getString(7));
userdetails.setCarrerObjective(rs1.getString(8));
user.setFlag(true);
break;
}
user.setFlag(false);
stmt = con.prepareStatement(DbQueries.SELECT_ADDRESS);
stmt.setLong(1, id);
ResultSet rs2 = stmt.executeQuery();
while (rs2.next()) {
addr.setCity(rs2.getString(3));
addr.setState(rs2.getString(4));
addr.setLine(rs2.getString(5));
userdetails.setAddress(addr);
user.setFlag(true);
break;
}
user.setFlag(false);
stmt = con.prepareStatement(DbQueries.SELECT_EDUCATION);
stmt.setLong(1, id);
ResultSet rs3 = stmt.executeQuery();
while (rs3.next()) {
EducationDetails edu = new EducationDetails();
edu.setCollegeName(rs3.getString(3));
edu.setStartYear(rs3.getDate(4));
edu.setEndYear(rs3.getDate(5));
edu.setPercentage(rs3.getString(6));
education.add(edu);
user.setFlag(true);
}
user.setFlag(false);
userdetails.setEducation(education);
stmt = con.prepareStatement(DbQueries.SELECT_CAREER);
stmt.setLong(1, id);
ResultSet rs4 = stmt.executeQuery();
while (rs4.next()) {
CarrerDetail car = new CarrerDetail();
car.setCompanyName(rs4.getString(3));
car.setDesignation(rs4.getString(4));
car.setFrom(rs4.getDate(5));
car.setTo(rs4.getDate(6));
career.add(car);
user.setFlag(true);
}
user.setFlag(false);
userdetails.setExperience(career);
stmt = con.prepareStatement(DbQueries.SELECT_OTHERDETAIL);
stmt.setLong(1, id);
ResultSet rs5 = stmt.executeQuery();
while (rs5.next()) {
otherDetail.setLanguage(rs5.getString(3));
otherDetail.setExtraCurrAct(rs5.getString(4));
userdetails.setOtherDetail(otherDetail);
user.setFlag(true);
break;
}
user.setUserDetail(userdetails);
} catch (Exception e) {
e.printStackTrace();
}
return user;
}
/**
* Generates a QuestionSetWrapper containing valid question, answer pairs Skip elements introduces
* randomness to the result query but at the cost of perhaps not having enough elements in the
* result query if the ResultSet was small to begin with
*
* @param difficulty
* @return
* @throws SQLException
*/
@SuppressWarnings("unchecked")
private static Question generateQuestion(int difficulty, int choiceSize, boolean skipElements)
throws SQLException {
// Question parameters
int answer = 0, indexSelected = 0;
String question = null;
List<String> choices = new ArrayList<>(choiceLimit);
ArrayList<SQLGenreSet> genres = null;
ArrayList<String> movies = null;
ArrayList<Integer> years = null;
ArrayList<Integer> aids = null;
ArrayList<Integer> mids = null;
boolean dbConnect = false;
switch ((int) (Math.random() * questionTypeN)) {
case 0:
// what year is this movie released?
if (!questionCache.containsKey(getCacheKey(0, difficulty))
|| Math.random() <= refreshThreshold) {
dbConnect = true;
loadIndex(0, difficulty, choiceSize, skipElements);
}
movies =
(ArrayList<String>) questionCache.get(getCacheKey(0, difficulty)).getPropertyList();
years = (ArrayList<Integer>) questionCache.get(getCacheKey(0, difficulty)).getAnswerList();
//
if (movies.size() < resultThreshold) {
System.out.println(
"Insufficient amount of elements for question type: (" + movies.size() + ", 0)");
System.out.println("Re-generating question without skipping");
return generateQuestion(difficulty, choiceSize, false);
} else {
System.out.println("Valid result set, proceeding with computations");
if (!questionCache.containsKey(getCacheKey(0, difficulty)) || dbConnect) {
questionCache.put(
getCacheKey(0, difficulty), new QuestionSetWrapper<String, Integer>(movies, years));
}
}
indexSelected = (int) (Math.random() * movies.size());
question = "What year was " + movies.get(indexSelected) + " first released?";
int yearSelected = years.get(indexSelected);
String yearStr = Integer.toString(yearSelected);
// populate choices with (choiceLimit - 1) elements
for (int i = 0; i < choiceLimit - 1; ++i) {
if (Math.random() > 0.70) {
int randomCloseYear = yearSelected;
if (yearSelected < 2000 && Math.random() > 0.50) {
randomCloseYear += 1 + ((int) (10 * Math.random()));
} else {
randomCloseYear -= 1 + ((int) (10 * Math.random()));
}
String randomCloseYearString = Integer.toString(randomCloseYear);
if (!choices.contains(randomCloseYearString)) {
choices.add(randomCloseYearString);
} else {
--i;
}
} else {
// random number from 1900 to 2015
int randomYear = 1900 + ((int) (Math.random() * 116));
String randomYearString = Integer.toString(randomYear);
if (randomYear != yearSelected && !choices.contains(randomYearString)) {
choices.add(randomYearString);
} else {
// just redo calculation
--i;
}
}
}
answer = (int) (Math.random() * (choices.size() + 1));
choices.add(answer, yearStr);
return new Question(question, choices, answer);
case 1:
// what set of genres best matches this movie?
if (!questionCache.containsKey(getCacheKey(1, difficulty))
|| Math.random() <= refreshThreshold) {
dbConnect = true;
loadIndex(1, difficulty, choiceSize, skipElements);
}
movies =
(ArrayList<String>) questionCache.get(getCacheKey(1, difficulty)).getPropertyList();
genres =
(ArrayList<SQLGenreSet>) questionCache.get(getCacheKey(1, difficulty)).getAnswerList();
if (movies.size() < resultThreshold) {
System.out.println(
"Insufficient amount of elements for question type: (" + movies.size() + ", 1)");
System.out.println("Re-generating question without skipping");
return generateQuestion(difficulty, choiceSize, false);
} else {
System.out.println("Valid result set, proceeding with computations");
}
indexSelected = (int) (Math.random() * movies.size());
String movieSelected = movies.get(indexSelected);
question = "What set of genres best matches the movie " + movieSelected + "?";
SQLGenreSet genreSetSelected = genres.get(indexSelected);
HashSet<SQLGenreSet> genresChosen = new HashSet<>();
for (int i = 0; i < choiceLimit - 1; ++i) {
int index = (int) (Math.random() * movies.size());
SQLGenreSet genreSet = genres.get(index);
if (index != indexSelected
&& !genresChosen.contains(genreSet)
&& !genreSet.equals(genreSetSelected)) {
choices.add(genreSet.toString());
genresChosen.add(genreSet);
} else if (Math.random() > 0.10) {
--i;
}
}
answer = (int) (Math.random() * (choices.size() + 1));
choices.add(answer, genreSetSelected.toString());
return new Question(question, choices, answer);
case 2:
// which of these movies is the oldest?
movies = null;
years = null;
if (!questionCache.containsKey(getCacheKey(0, difficulty))
|| Math.random() <= refreshThreshold) {
dbConnect = true;
loadIndex(0, difficulty, choiceSize, skipElements);
}
movies =
(ArrayList<String>) questionCache.get(getCacheKey(0, difficulty)).getPropertyList();
years = (ArrayList<Integer>) questionCache.get(getCacheKey(0, difficulty)).getAnswerList();
if (movies.size() < resultThreshold) {
System.out.println(
"Insufficient amount of elements for question type: (" + movies.size() + ", 3)");
System.out.println("Re-generating question without skipping");
return generateQuestion(difficulty, choiceSize, false);
} else {
System.out.println("Valid result set, proceeding with computations");
}
// randomly generate 5 movies that were made in different years
TreeMap<Integer, String> movieYearPair = new TreeMap<>();
for (int i = 0; i < choiceLimit; ++i) {
indexSelected = (int) (Math.random() * movies.size());
if (movieYearPair.containsKey(years.get(indexSelected))) {
--i;
continue;
}
movieYearPair.put(years.get(indexSelected), movies.get(indexSelected));
}
movies = new ArrayList<String>(movieYearPair.values());
years = new ArrayList<Integer>(movieYearPair.keySet());
years.remove(0);
String lowestYearString = movies.remove(0);
int iter_size = movies.size();
for (int i = 0; i < iter_size; ++i) {
int random_index = (int) (movies.size() * Math.random());
choices.add(movies.remove(random_index));
years.remove(random_index);
}
answer = (int) (Math.random() * (choices.size() + 1));
choices.add(answer, lowestYearString);
question = "Which of these movies (";
for (int i = 0; i < choices.size(); ++i) {
question = question + choices.get(i) + ((i == choices.size() - 1) ? "" : ", ");
}
question = question + ") was made the earliest?";
return new Question(question, choices, answer);
case 3:
/*
// which movie has actor X acted in
if (!cacheContainsKey(3, difficulty) || Math.random() <= refreshThreshold) {
loadIndex(3, difficulty, choiceSize, skipElements);
}
// Instead of movies and genres, they are actors and movies but
// that's good enough
movies = (ArrayList<String>) questionCache.get(getCacheKey(3, difficulty)).getPropertyList();
genres = (ArrayList<SQLGenreSet>) questionCache.get(getCacheKey(3, difficulty)).getAnswerList();
if (movies.size() < resultThreshold) {
System.out.println("Insufficient amount of elements for question type: (" + movies.size() + ", 1)");
System.out.println("Re-generating question without skipping");
return generateQuestion(difficulty, choiceSize, false);
} else {
System.out.println("Valid result set, proceeding with computations");
}
indexSelected = (int) (Math.random() * movies.size());
movieSelected = movies.get(indexSelected);
question = "What movie has " + movieSelected + " acted in?";
genreSetSelected = genres.get(indexSelected);
HashSet<String> chosen3 = new HashSet<>();
String answer3 = genreSetSelected.getList().remove(0);
genreSetSelected.addGenre(answer3);
chosen3.add(answer3);
for (int i = 0; i < choiceLimit - 1; ++i) {
int index = (int) (Math.random() * movies.size());
SQLGenreSet genreSet = genres.get(index);
if (index != indexSelected) {
if (genreSet.getList().size() == 0) {
--i;
continue;
}
String movanswer = genreSet.getList().remove(0);
if (!genreSetSelected.contains(movanswer) && !chosen3.contains(movanswer)) {
choices.add(movanswer);
chosen3.add(movanswer);
} else {
--i;
}
} else if (Math.random() > 0.10) {
--i;
}
}
answer = (int) (Math.random() * (choices.size() + 1));
choices.add(answer, answer3);
return new Question(question, choices, answer);*/
case 4:
/*// which movie has actor X not acted in
if (!cacheContainsKey(3, difficulty) || Math.random() <= refreshThreshold) {
loadIndex(3, difficulty, choiceSize, skipElements);
}
// Instead of movies and genres, they are actors and movies but
// that's good enough
movies = (ArrayList<String>) questionCache.get(getCacheKey(3, difficulty)).getPropertyList();
genres = (ArrayList<SQLGenreSet>) questionCache.get(getCacheKey(3, difficulty)).getAnswerList();
if (movies.size() < resultThreshold) {
System.out.println("Insufficient amount of elements for question type: (" + movies.size() + ", 1)");
System.out.println("Re-generating question without skipping");
return generateQuestion(difficulty, choiceSize, false);
} else {
System.out.println("Valid result set, proceeding with computations");
}
indexSelected = (int) (Math.random() * movies.size());
movieSelected = movies.get(indexSelected);
question = "What movie has " + movieSelected + " NOT acted in?";
genreSetSelected = genres.get(indexSelected);
HashSet<String> chosen4 = new HashSet<>();
for (int i = 0; i < choiceLimit - 1 && i < genreSetSelected.getList().size(); ++i) {
chosen4.add(genreSetSelected.getList().get(i));
}
String answer4 = null;
while (answer4 == null) {
int index = (int) (Math.random() * movies.size());
SQLGenreSet genreSet = genres.get(index);
if (index != indexSelected) {
if (genreSet.getList().size() == 0) {
continue;
}
String movanswer = genreSet.getList().remove(0);
if (!genreSetSelected.contains(movanswer) && !chosen4.contains(movanswer)) {
choices.add(movanswer);
answer4 = movanswer;
}
}
}
answer = (int) (Math.random() * (choices.size() + 1));
choices.add(answer, answer4);
return new Question(question, choices, answer);
*/
case 5:
case 6:
// just re-generate question since incomplete code
return generateQuestion(difficulty, choiceSize, skipElements);
case 7:
// has actor X acted in same movie as actor Y?
if (!cacheContainsKey(7, difficulty) || Math.random() <= refreshThreshold) {
loadIndex(7, difficulty, choiceSize, skipElements);
}
mids = (ArrayList<Integer>) questionCache.get(getCacheKey(7, difficulty)).getPropertyList();
aids = (ArrayList<Integer>) questionCache.get(getCacheKey(7, difficulty)).getAnswerList();
int actor1 = -1, actor2 = -1;
for (int i = 0; i < choiceSize * 2 && (actor1 == -1 || actor2 == -1); i++) {
int randomVal = aids.get((int) (Math.random() * mids.size()));
if (actor1 == -1) {
actor1 = randomVal;
} else if (randomVal == actor1) {
continue;
} else {
actor2 = randomVal;
break;
}
}
Connection conn = DBConnect.getConnection();
HashSet<Integer> actor1MovieSet = new HashSet<>();
HashSet<Integer> actor2MovieSet = new HashSet<>();
String a1Name = "", a2Name = "";
try {
Statement statement = conn.createStatement();
ResultSet rs =
statement.executeQuery("select mid as mid from movie.acts where aid = " + actor1);
while (rs.next()) {
int movieID = rs.getInt("mid");
actor1MovieSet.add(movieID);
}
statement = conn.createStatement();
rs = statement.executeQuery("select mid as mid from movie.acts where aid = " + actor1);
while (rs.next()) {
int movieID = rs.getInt("mid");
actor2MovieSet.add(movieID);
}
statement = conn.createStatement();
rs =
statement.executeQuery("select name as name from movie.actors where aid = " + actor1);
if (rs.next()) {
a1Name = rs.getString("name");
} else {
System.out.println("actor id " + actor1 + " does not have a name");
}
statement = conn.createStatement();
rs =
statement.executeQuery("select name as name from movie.actors where aid = " + actor2);
if (rs.next()) {
a2Name = rs.getString("name");
} else {
System.out.println("actor id " + actor2 + " does not have a name");
}
} finally {
if (conn != null) {
conn.close();
}
}
answer = 1;
for (int mid : actor1MovieSet) {
if (actor2MovieSet.contains(mid)) {
answer = 0;
break;
}
}
question = "Have " + a1Name + " and " + a2Name + " acted in the same movie?";
choices.add("True");
choices.add("False");
return new Question(question, choices, answer);
case 8:
// which set of genres is movie X NOT about?
if (!questionCache.containsKey(getCacheKey(1, difficulty))
|| Math.random() <= refreshThreshold) {
dbConnect = true;
loadIndex(1, difficulty, choiceSize, skipElements);
}
movies =
(ArrayList<String>) questionCache.get(getCacheKey(1, difficulty)).getPropertyList();
genres =
(ArrayList<SQLGenreSet>) questionCache.get(getCacheKey(1, difficulty)).getAnswerList();
if (movies.size() < resultThreshold) {
System.out.println(
"Insufficient amount of elements for question type: (" + movies.size() + ", 8)");
System.out.println("Re-generating question without skipping");
return generateQuestion(difficulty, choiceSize, false);
} else {
System.out.println("Valid result set, proceeding with computations");
}
indexSelected = (int) (Math.random() * movies.size());
movieSelected = movies.get(indexSelected);
question = "Which of these genres does not match the genre of " + movieSelected + "?";
genreSetSelected = genres.get(indexSelected);
if (genreSetSelected.size() == 0) {
return generateQuestion(difficulty, choiceSize, skipElements);
// just redo, we selected bad data on random
}
ArrayList<String> choiceCandidates = genreSetSelected.getScrambledList();
for (int i = 0; i < choiceLimit - 1 && i < choiceCandidates.size(); ++i) {
choices.add(choiceCandidates.get(i));
}
answer = (int) (Math.random() * (choices.size() + 1));
ArrayList<String> genresSetComplement = new ArrayList<>();
for (String s : ALL_GENRES) {
if (!genreSetSelected.contains(s)) {
genresSetComplement.add(s);
}
}
if (genresSetComplement.isEmpty()) {
System.out.println("Genres set complement is empty, re-generating question.");
System.out.println("Genres set was " + genreSetSelected.toString());
return generateQuestion(difficulty, choiceSize, skipElements);
}
choices.add(
answer, genresSetComplement.get((int) (Math.random() * genresSetComplement.size())));
return new Question(question, choices, answer);
case 9:
case 10:
// which actor is associated with date of birth x?
ArrayList<String> aName = null;
ArrayList<String> aDOB = null;
if (!questionCache.containsKey(getCacheKey(10, difficulty))
|| Math.random() <= refreshThreshold) {
dbConnect = true;
loadIndex(10, difficulty, choiceSize, skipElements);
}
aName =
(ArrayList<String>) questionCache.get(getCacheKey(10, difficulty)).getPropertyList();
aDOB = (ArrayList<String>) questionCache.get(getCacheKey(10, difficulty)).getAnswerList();
if (aName.size() < resultThreshold) {
System.out.println(
"Insufficient amount of elements for question type: (" + aName.size() + ", 10)");
System.out.println("Re-generating question without skipping");
return generateQuestion(difficulty, choiceSize, false);
} else {
System.out.println("Valid result set, proceeding with computations");
}
ArrayList<Integer> randomSelects = new ArrayList<>();
for (int i = 0; i < choiceLimit; ++i) {
int randomIndex = (int) (Math.random() * aName.size());
if (!randomSelects.contains(randomIndex) && !choices.contains(aDOB.get(randomIndex))) {
randomSelects.add(randomIndex);
choices.add(aDOB.get(randomIndex));
} else {
--i;
}
}
// randomSelects now contains 5 random indices to (actor, DOB) pair
answer = (int) (Math.random() * randomSelects.size());
question =
"On which day was actor "
+ aName.get(randomSelects.get(answer))
+ " born? (YYYY-MM-DD)";
return new Question(question, choices, answer);
default:
}
return null;
}
public String addAction(Vara vara) throws SQLException {
dbcon.addVara(vara);
setAll();
return null;
}
public void setAll() throws SQLException {
al = dbcon.getVaror();
}
private static void loadIndex(int qType, int difficulty, int choiceSize, boolean skipElements)
throws SQLException {
ArrayList<String> actorNames = new ArrayList<>();
ArrayList<String> actorDOBs = new ArrayList<>();
ArrayList<String> movies = new ArrayList<>();
ArrayList<Integer> years = new ArrayList<>();
ArrayList<Integer> idList = new ArrayList<>();
int skip = maxSkipNumber(choiceSize, difficulty) / 2;
if (!skipElements) {
skip = 2;
} else {
skip += (int) (Math.random() * skip);
skip = skip * 3 / 4;
}
switch (qType) {
case 0:
Connection connection = DBConnect.getConnection();
ResultSet rs = null;
try {
String qry =
"SELECT * from (SELECT @row := @row +1 AS rownum, name, "
+ "rating FROM (SELECT @row := 0) r, movie.movies offset "
+ ") ranked WHERE rownum % "
+ skip
+ " = 1 AND rating > "
+ getRating(difficulty);
qry =
"select M.name as name from movie.movies as M where M.mid % "
+ skip
+ " = 1 and M.rating > "
+ getRating(difficulty);
Statement s = connection.createStatement();
System.out.println("Executing: " + qry);
rs = s.executeQuery(qry);
// rs.next() is actually very slow so we would
// like to minimize the time by caching the results
while (rs.next() && movies.size() <= choiceSize) {
String name = "";
int year = 0;
String nameYear = rs.getString("name");
System.out.println("Processing: " + nameYear);
String tokens[] = nameYear.split("\\s");
Pattern p = Pattern.compile("\\(\\d\\d\\d\\d\\)");
for (String token : tokens) {
Matcher m = p.matcher(token);
if (m.matches()) {
token = token.replaceAll("[\\(\\)]", "");
try {
year = Integer.parseInt(token);
} catch (NumberFormatException e) {
e.printStackTrace();
}
} else {
name = name + " " + token;
}
}
if (year > 0 && !name.matches("(\\s)*")) {
// from this, we guarantee that the indexes will
// match
// for (movie, year)
movies.add(name);
years.add(year);
}
}
questionCache.put(
getCacheKey(0, difficulty), new QuestionSetWrapper<String, Integer>(movies, years));
} finally {
if (connection != null) {
connection.close();
}
}
break;
case 1:
LinkedHashMap<String, SQLGenreSet> genres = new LinkedHashMap<>();
connection = DBConnect.getConnection();
try {
skip = maxSkipNumber(choiceSize, difficulty) / 2;
skip += (int) (Math.random() * skip);
skip = skip * 3 / 4;
String qry = null;
// preQuery selects 'choiceSize' number of rows from movies to
// join into movie_genre
String preQuery =
"(SELECT * from (SELECT @row := @row +1 as rownum, name as name, mid as mid, rating as rating FROM (SELECT @row := 0) r, movie.movies) ranked WHERE rownum % "
+ skip
+ " = 1 AND rating > "
+ getRating(difficulty)
+ ")";
preQuery =
"(select M.name as name, M.mid as mid from movie.movies as M where M.mid % "
+ skip
+ " = 1 and M.rating > "
+ getRating(difficulty)
+ ")";
qry =
"select movie.movie_genre.genre as m_genre, T.name as m_name from movie.movie_genre inner join "
+ preQuery
+ "as T on T.mid = movie.movie_genre.mid";
Statement s = connection.createStatement();
System.out.println("Executing: " + qry);
rs = s.executeQuery(qry);
while (rs.next() && genres.size() <= choiceSize) {
String nameString = rs.getString("m_name");
String genreString = rs.getString("m_genre");
if (!genres.containsKey(nameString)) {
genres.put(nameString, new SQLGenreSet());
}
SQLGenreSet sqlGenre = genres.get(nameString);
sqlGenre.addGenre(genreString);
}
questionCache.put(
getCacheKey(1, difficulty),
new QuestionSetWrapper<String, SQLGenreSet>(
new ArrayList<String>(genres.keySet()),
new ArrayList<SQLGenreSet>(genres.values())));
} finally {
if (connection != null) {
connection.close();
}
}
break;
case 2:
case 3:
skip = 10 + (int) (Math.random() * 180000);
connection = DBConnect.getConnection();
LinkedHashMap<String, SQLGenreSet> map3 = new LinkedHashMap<>();
try {
String qry =
"SELECT a.name as actor, m.name as movie FROM movie.actors a, movies.movie m "
+ "INNER JOIN movies.acts act ON m.mid = acts.mid and acts.aid = a.aid "
+ "WHERE m.mid IN (SELECT mid from (SELECT @row := @row +1 AS rownum, mid as mid"
+ "rating FROM (SELECT @row := 0) r, movie.movies offset "
+ ") ranked WHERE rownum % "
+ skip
+ " = 1 AND rating > "
+ getRating(difficulty)
+ ")";
qry =
"select A.name as actor, M.name as movie from movie.actors as A inner join movie.acts as AM on A.aid = AM.aid inner join movie.movies M on AM.mid = M.mid where M.mid % "
+ skip
+ " = 1 and M.rating > "
+ getRating(difficulty);
Statement s3 = connection.createStatement();
System.out.println("Executing: " + qry);
rs = s3.executeQuery(qry);
while (rs.next() && map3.size() <= choiceSize) {
String actor = rs.getString("actor");
String movie = rs.getString("movie");
if (!map3.containsKey(actor)) {
map3.put(actor, new SQLGenreSet());
}
SQLGenreSet set = map3.get(actor);
set.addGenre(movie);
}
questionCache.put(
getCacheKey(3, difficulty),
new QuestionSetWrapper<String, SQLGenreSet>(
new ArrayList<String>(map3.keySet()), new ArrayList<SQLGenreSet>(map3.values())));
} finally {
if (connection != null) {
connection.close();
}
}
break;
case 4:
case 5:
case 6:
case 7:
if (!cacheContainsKey(9, difficulty)) {
loadIndex(9, difficulty, choiceSize, skipElements);
}
skip = 10 + (int) (Math.random() * 180000);
connection = DBConnect.getConnection();
ArrayList<Integer> idList2 = new ArrayList<>(choiceSize * 2);
try {
String qry =
"(SELECT * from (SELECT @row := @row +1 as rownum, aid as aid, "
+ "mid as mid FROM (SELECT @row := 0) r, movie.acts) "
+ "ranked WHERE rownum % 1 = 0 order by mid desc limit "
+ skip
+ ", 200000)";
qry = "select A.aid as aid, A.mid as mid from movie.acts as A limit " + skip + ", 200000";
Statement s = connection.createStatement();
System.out.println("Executing: " + qry);
rs = s.executeQuery(qry);
// rs.next() is actually very slow so we would
// like to minimize the time by caching the results
while (rs.next() && actorNames.size() <= choiceSize * 2) {
int mid = rs.getInt("mid");
int aid = rs.getInt("aid");
idList.add(mid);
idList2.add(aid);
}
questionCache.put(
getCacheKey(7, difficulty),
new QuestionSetWrapper<Integer, Integer>(idList, idList2));
} finally {
if (connection != null) {
connection.close();
}
}
break;
case 8:
// we'll use 8 as a way to load random actors
skip = Question.maxSkipNumberActorOverall(choiceSize) / 2;
skip += (int) (Math.random() * skip);
skip = skip * 3 / 4;
connection = DBConnect.getConnection();
try {
String qry =
"(SELECT * from (SELECT @row := @row +1 as rownum, name as a_name, aid as a_aid FROM (SELECT @row := 0) r, movie.actors) ranked WHERE rownum % "
+ skip
+ " = 0)";
Statement s = connection.createStatement();
System.out.println("Executing: " + qry);
rs = s.executeQuery(qry);
// rs.next() is actually very slow so we would
// like to minimize the time by caching the results
while (rs.next() && actorNames.size() <= choiceSize) {
String name = rs.getString("a_name");
int aid = rs.getInt("a_aid");
if (!name.matches("(\\s)*")) {
actorNames.add(name);
idList.add(aid);
}
}
questionCache.put(
getCacheKey(8, difficulty),
new QuestionSetWrapper<Integer, String>(idList, actorNames));
} finally {
if (connection != null) {
connection.close();
}
}
break;
case 9:
// we'll use 9 as a way to load random movies
skip = maxSkipNumber(choiceSize, difficulty) / 2;
skip += (int) (Math.random() * skip);
skip = skip * 3 / 4;
connection = DBConnect.getConnection();
try {
String qry =
"(SELECT * from (SELECT @row := @row +1 as rownum, name as m_name, mid as mid FROM (SELECT @row := 0) r, movie.movies) ranked WHERE rownum % "
+ skip
+ " = 0)";
Statement s = connection.createStatement();
System.out.println("Executing: " + qry);
rs = s.executeQuery(qry);
// rs.next() is actually very slow so we would
// like to minimize the time by caching the results
while (rs.next() && actorNames.size() <= choiceSize) {
String name = rs.getString("m_name");
int mid = rs.getInt("mid");
if (!name.matches("(\\s)*")) {
movies.add(name);
idList.add(mid);
}
}
questionCache.put(
getCacheKey(9, difficulty), new QuestionSetWrapper<Integer, String>(idList, movies));
} finally {
if (connection != null) {
connection.close();
}
}
break;
case 10:
skip = maxSkipNumberActorWithBirthday(choiceSize) / 2;
skip += (int) (Math.random() * skip);
skip = skip * 3 / 4;
connection = DBConnect.getConnection();
rs = null;
try {
String qry =
"select * from (select @row := @row +1 as rownum, A.name as a_name, A.Date_of_birth as a_DOB from (SELECT @row := 0) r, movie.actors as A where A.Date_of_birth like '%%%%-%%-%%') ranked where rownum % "
+ skip
+ " = 1";
qry =
"select A.name as a_name, A.Date_of_birth as a_DOB from movie.actors as A where A.Date_of_birth like '%%%%-%%-%%' and aid % "
+ skip
+ " = 1";
Statement s = connection.createStatement();
System.out.println("Executing: " + qry);
rs = s.executeQuery(qry);
// rs.next() is actually very slow so we would
// like to minimize the time by caching the results
while (rs.next() && actorNames.size() <= choiceSize) {
String name = rs.getString("a_name");
String dob = rs.getString("a_DOB");
if (!dob.matches("(\\s)*") && !name.matches("(\\s)*")) {
actorNames.add(name);
actorDOBs.add(dob);
}
}
questionCache.put(
getCacheKey(10, difficulty),
new QuestionSetWrapper<String, String>(actorNames, actorDOBs));
} finally {
if (connection != null) {
connection.close();
}
}
break;
default:
}
}
