Instructions
Requirements and Specifications
Source Code
QUESTION 1
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import javax.swing.table.*;
import javax.swing.*;
import java.awt.event.*;
import java.sql.*;
public class Q1_ID {
public static void main(String[] args) {
// Helper variable
int EVENT_TYPE = -1;
JFrame f=new JFrame();//creating instance of JFrame
// add label
JLabel label1 = new JLabel("Select Event Type");
label1.setBounds(50, 20, 100, 40);
JComboBox cbx = new JComboBox();
cbx.setBounds(180, 20, 100, 40);
cbx.addItem("Journal");
cbx.addItem("Book");
cbx.addItem("Conference");
// fields
JLabel label2 = new JLabel("Event Name:");
JLabel label3 = new JLabel("Weblink:");
JLabel label4 = new JLabel("Description:");
JLabel label5 = new JLabel("Journal Name:");
JLabel label6 = new JLabel("Publisher:");
JLabel label7 = new JLabel("City:");
JLabel label8 = new JLabel("Country:");
JLabel label9 = new JLabel("Date:");
/*
// JTable
String[]columns = {"Type", "Date", "Count"};
String[][]data = {{"", "", "", ""}};
final DefaultTableModel model = new DefaultTableModel(null, columns);
JTable table = new JTable(model);
table.setBounds(5, 5, 300, 100);
JScrollPane sp = new JScrollPane(table);
sp.setBounds(10, 520, 360, 100);*/
label2.setBounds(50, 80, 100, 30);
label3.setBounds(50, 120, 100, 30);
label4.setBounds(50, 160, 100, 30);
label5.setBounds(50, 200, 100, 30);
label6.setBounds(50, 240, 100, 30);
label7.setBounds(50, 280, 100, 30);
label8.setBounds(50, 320, 100, 30);
label9.setBounds(50, 360, 100, 30);
// textfields
JTextField text1 = new JTextField();
JTextField text2 = new JTextField();
JTextField text3 = new JTextField();
JTextField text4 = new JTextField();
JTextField text5 = new JTextField();
JTextField text6 = new JTextField();
JTextField text7 = new JTextField();
JTextField text8 = new JTextField();
//add listener
cbx.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e)
{
if(cbx.getSelectedIndex() == 0) // journal
{
text4.setEnabled(true);
text5.setEnabled(true);
text6.setEnabled(false);
text7.setEnabled(false);
text8.setEnabled(false);
}
else if(cbx.getSelectedIndex() == 1) // book
{
text4.setEnabled(false);
text5.setEnabled(true);
text6.setEnabled(false);
text7.setEnabled(false);
text8.setEnabled(false);
}
else if(cbx.getSelectedIndex() == 2) // conference
{
text4.setEnabled(false);
text5.setEnabled(false);
text6.setEnabled(true);
text7.setEnabled(true);
text8.setEnabled(true);
}
}
});
cbx.setSelectedIndex(0);
// Button
JButton addButton = new JButton("Create Event");
// add listener
addButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e)
{
int EVENT_TYPE = cbx.getSelectedIndex();
String field1 = text1.getText();
String field2 = text2.getText();
String field3 = text3.getText();
String field4 = text4.getText();
String field5 = text5.getText();
String field6 = text6.getText();
String field7 = text7.getText();
String field8 = text8.getText();
if(EVENT_TYPE == 0) // journal
{
// check that none of the fields are empty
if(!field1.isEmpty() && !field2.isEmpty() && !field3.isEmpty() && !field4.isEmpty() && !field5.isEmpty())
{
// Create new Q3 object that is the one that will update the DB
Q3_ID query = new Q3_ID();
query.setEvent(0);
query.setEventName(field1);
query.setWebLink(field2);
query.setDescription(field3);
query.setJournalName(field4);
query.setPublisher(field5);
try {
query.push();
} catch (SQLException e2) {
// TODO Auto-generated catch block
System.out.println("Could not push query: " + query.toString());
}
}
}
else if(EVENT_TYPE == 1) // book
{
// check that none of the fields are empty
if(!field1.isEmpty() && !field2.isEmpty() && !field3.isEmpty() && !field5.isEmpty())
{
// Create new Q3 object that is the one that will update the DB
Q3_ID query = new Q3_ID();
query.setEvent(1);
query.setEventName(field1);
query.setWebLink(field2);
query.setDescription(field3);
query.setPublisher(field5);
try {
query.push();
} catch (SQLException e2) {
// TODO Auto-generated catch block
System.out.println("Could not push query: " + query.toString());
}
}
}
else if(EVENT_TYPE == 2) // conference
{
// check that none of the fields are empty
if(!field1.isEmpty() && !field2.isEmpty() && !field3.isEmpty() && !field6.isEmpty() && !field7.isEmpty() && !field8.isEmpty())
{
// Create new Q3 object that is the one that will update the DB
Q3_ID query = new Q3_ID();
query.setEvent(2);
query.setEventName(field1);
query.setWebLink(field2);
query.setDescription(field3);
query.setCity(field6);
query.setCountry(field7);
try {
query.setDate(new java.sql.Date(new SimpleDateFormat("dd/MM/yyyy").parse(field8).getTime()));
} catch (ParseException e1) {
// TODO Auto-generated catch block
System.out.println("Invalid date format");
}
try {
query.push();
} catch (SQLException e2) {
// TODO Auto-generated catch block
System.out.println("Could not push query: " + query.toString());
e2.printStackTrace();
}
}
}
}
});
text1.setBounds(180, 80, 100, 30);
text2.setBounds(180, 120, 100, 30);
text3.setBounds(180, 160, 100, 30);
text4.setBounds(180, 200, 100, 30);
text5.setBounds(180, 240, 100, 30);
text6.setBounds(180, 280, 100, 30);
text7.setBounds(180, 320, 100, 30);
text8.setBounds(180, 360, 100, 30);
addButton.setBounds(70, 420, 200, 40);
// add components
f.add(label1);
f.add(label2);
f.add(label3);
f.add(label4);
f.add(label5);
f.add(label6);
f.add(label7);
f.add(label8);
f.add(label9);
f.add(cbx);
f.add(text1);
f.add(text2);
f.add(text3);
f.add(text4);
f.add(text5);
f.add(text6);
f.add(text7);
f.add(text8);
f.add(addButton);
f.setSize(400,600);//400 width and 500 height
f.setLayout(null);//using no layout managers
f.setVisible(true);//making the frame visible
}
}
QUESTION 2
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import javax.swing.table.*;
import javax.swing.*;
import java.awt.event.*;
import java.sql.*;
public class Q2_ID {
public static void main(String[] args) {
JFrame f = new JFrame();
// JTable
String[]columns = {"Type", "Date", "Count"};
String[][]data = {{"", "", "", ""}};
final DefaultTableModel model = new DefaultTableModel(null, columns);
JTable table = new JTable(model);
table.setBounds(5, 5, 300, 100);
JScrollPane sp = new JScrollPane(table);
sp.setBounds(10, 10, 360, 260);
// Button
JButton queryButton = new JButton("Query Events");
// add listene
queryButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e)
{
String query = "SELECT \"EventConference\" as type, Concat(monthname(Date),\", \",Year(Date)) as Month, count(*) as Count FROM EventConference GROUP BY Year(Date), Month(Date) UNION Select \"EventJournal\" as type, Concat(monthname(Temp.ADate),\", \",Year(Temp.ADate)) as Month, Count(*) as Count From (Select EventName, Min(ActivityDate) as ADate from ActivityHappens WHERE EventName IN (Select EventName FROM EventJournal) Group by EventName) as Temp Group by Year(Temp.ADate), Month(Temp.ADate)";
List rows = Q3_ID.getResultQuery(query);
if(rows.size() > 0)
{
for(Object[] row: rows)
model.addRow(row);
}
}
});
queryButton.setBounds(70, 300, 200, 40);
// add components
f.add(queryButton);
f.add(sp);
f.setSize(400,400);//400 width and 500 height
f.setLayout(null);//using no layout managers
f.setVisible(true);//making the frame visible
}
}
QUESTION 3
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
public class Q3_ID {
private String EventName;
private String WebLink;
private String Description;
private String City;
private String Country;
private Date EventDate;
private String JournalName;
private String Publisher;
private int event; // 0 for journal, 1 for book and 2 for conference
private static final int JOURNAL = 0;
private static final int BOOK = 1;
private static final int CONFERENCE = 2;
public static final String DB_USER = "root";
public static final String DB_PASS = "root";
public static final String DB_NAME = "3421a03";
public static final String DB_PORT = "3306";
public static final String DB_HOST = "localhost";
public Q3_ID()
{
event = -1;
}
public String getWebLink() {
return this.WebLink;
}
public void setWebLink(String WebLink) {
this.WebLink = WebLink;
}
public String getDescription() {
return this.Description;
}
public void setDescription(String Description) {
this.Description = Description;
}
public String getEventName() {
return EventName;
}
public void setEventName(String eventName) {
EventName = eventName;
}
public String getCity() {
return City;
}
public void setCity(String city) {
City = city;
}
public String getCountry() {
return Country;
}
public void setCountry(String country) {
Country = country;
}
public Date getDate() {
return EventDate;
}
public void setDate(Date date) {
EventDate = date;
}
public String getJournalName() {
return JournalName;
}
public void setJournalName(String journalName) {
JournalName = journalName;
}
public String getPublisher() {
return Publisher;
}
public void setPublisher(String publisher) {
Publisher = publisher;
}
public int getEvent() {
return event;
}
public void setEvent(int event) {
this.event = event;
}
// method
public void createEvent() throws SQLException
{
// this method addes to databse the given event
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://" + DB_HOST + ":" + DB_PORT + "/" + DB_NAME + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", DB_USER, DB_PASS);
//Statement stmt = conn.createStatement();
// Specify query
String query = "";
query = "INSERT INTO Event (Name, EventWebLink, CFPText) VALUES (?,?,?);";
PreparedStatement prepared = conn.prepareStatement(query);
prepared.setString(1, getEventName());
prepared.setString(2, getWebLink());
prepared.setString(3, getDescription());
prepared.execute();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println(e.getMessage());
}
catch(SQLException ex2)
{
ex2.printStackTrace();
}
}
public void push() throws SQLException
{
// this method addes to databse the given event
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/3421a03?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", "root", "root");
// create the event
createEvent();
// Specify query
String query = "";
if(getEvent() == BOOK)
{
query = "INSERT INTO EventBook (EventName, Publisher) VALUES (?,?);";
PreparedStatement prepared = conn.prepareStatement(query);
prepared.setString(1, getEventName());
prepared.setString(2, getPublisher());
prepared.execute();
}
else if(getEvent() == CONFERENCE)
{
query = "INSERT INTO EventConference (EventName, City, Country, Date) VALUES (?, ?, ?,?);";
PreparedStatement prepared = conn.prepareStatement(query);
prepared.setString(1, getEventName());
prepared.setString(2, getCity());
prepared.setString(3, getCountry());
prepared.setDate(4, getDate());
prepared.execute();
}
else if(getEvent() == JOURNAL)
{
query = "INSERT INTO EventJournal (EventName, JournalName, Publisher) VALUES (?, ?, ?);";
PreparedStatement prepared = conn.prepareStatement(query);
prepared.setString(1, getEventName());
prepared.setString(2, getJournalName());
prepared.setString(3, getPublisher());
prepared.execute();
}
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public String toString()
{
if(getEvent() == -1)
return "Undefined Query";
String result = "";
switch(getEvent())
{
case JOURNAL:
result = String.format("Journal - Event Name: %s, Journal Name: %s, Publisher: %s", getEventName(), getJournalName(), getPublisher());
break;
case BOOK:
result = String.format("Book - Event Name: %s, Publisher: %s", getEventName(), getPublisher());
break;
case CONFERENCE:
DateFormat format = new SimpleDateFormat("dd/MM/yyyy");
result = String.format("Conference - Event Name: %s, City: %s, Country: %s, Date: %s", getEventName(), getCity(), getCountry(), format.format(getDate()));
break;
}
return result;
}
public static List getResultQuery(String query)
{
List rows = new ArrayList();
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://" + Q3_ID.DB_HOST + ":" + Q3_ID.DB_PORT + "/" + Q3_ID.DB_NAME + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", Q3_ID.DB_USER, Q3_ID.DB_PASS);
Statement stmt = conn.createStatement();
ResultSet result = stmt.executeQuery("SELECT \"EventConference\" as type, Concat(monthname(Date),\", \",Year(Date)) as Month, count(*) as Count FROM EventConference GROUP BY Year(Date), Month(Date) UNION Select \"EventJournal\" as type, Concat(monthname(Temp.ADate),\", \",Year(Temp.ADate)) as Month, Count(*) as Count From (Select EventName, Min(ActivityDate) as ADate from ActivityHappens WHERE EventName IN (Select EventName FROM EventJournal) Group by EventName) as Temp Group by Year(Temp.ADate), Month(Temp.ADate)");
ResultSetMetaData rsmd = result.getMetaData();
int nCols = rsmd.getColumnCount();
while(result.next())
{
Object[] row = new Object[nCols];
for(int i = 0; i < nCols; i++)
{
row[i] = result.getObject(i+1);
}
rows.add(row);
//model.addRow(row);
}
conn.close();
} catch (SQLException e2) {
//TODO: handle exception
e2.printStackTrace();
}
catch (ClassNotFoundException e3)
{
e3.printStackTrace();
}
return rows;
}
}