Data
DBConnManager.java
package data1;
import com.mysql.jdbc.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnManager {
String sourceURL;
public DBConnManager() {
try {
// Load JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// Connection URL.
sourceURL = new String("");
} catch (ClassNotFoundException classNotFoundException) {
System.out.println(classNotFoundException + "-----------Unable to load database driver classes");
}
}
public Connection connect() throws SQLException{
Connection dbConn = null;
try {
dbConn = (Connection) DriverManager.getConnection(sourceURL, "username", "password");
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------DB connection failure");
}
return dbConn;
}
public void con_close(Connection dbConn) {
try {
dbConn.close();
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------DB connection closing failure");
}
}
}
------------------------------------------------------------------------------------------------------------
DBConnManager.java
package data1;
import com.mysql.jdbc.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnManager {
String sourceURL;
public DBConnManager() {
try {
// Load JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// Connection URL.
sourceURL = new String("");
} catch (ClassNotFoundException classNotFoundException) {
System.out.println(classNotFoundException + "-----------Unable to load database driver classes");
}
}
public Connection connect() throws SQLException{
Connection dbConn = null;
try {
dbConn = (Connection) DriverManager.getConnection(sourceURL, "username", "password");
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------DB connection failure");
}
return dbConn;
}
public void con_close(Connection dbConn) {
try {
dbConn.close();
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------DB connection closing failure");
}
}
}
------------------------------------------------------------------------------------------------------------
JobDetailsDAO
package data1;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
public class JobDetailsDAO {
private DBConnManager dbConnManager = null;
public JobDetailsDAO() {
dbConnManager = new DBConnManager();
}
public ArrayList getJobCategories() throws SQLException{
ArrayList jobCatList = null;
Connection dbConn = null;
try{
//Connect to th DB
dbConn = dbConnManager.connect();
Statement stmt = (Statement) dbConn.createStatement();
//Select the JobCatNames
String query = "SELECT DISTINCT jobCatName FROM JobCategory";
System.out.println(query);
ResultSet rs = stmt.executeQuery(query);
jobCatList = new ArrayList();
while (rs.next()) {
String catName = rs.getString(1);
System.out.println(catName);
jobCatList.add(catName);
}
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Select query failed at JobCatNames");
}finally{
//Close the db connection
dbConnManager.con_close(dbConn);
}
return jobCatList;
}
//sub_catogary
public int getJobCatId(String jobCatName){
int catId = 0;
Connection dbConn = null;
try {
dbConn = dbConnManager.connect();
Statement stmt = (Statement) dbConn.createStatement();
String query = "SELECT jobCatId FROM JobCategory WHERE jobCatName = '"+jobCatName+"'";
System.out.println(query);
ResultSet rs = stmt.executeQuery(query);
if(rs.next()){
catId = Integer.parseInt(rs.getString(1));
System.out.println(catId);
}
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Select query Failed for JobCatId");
}finally{
dbConnManager.con_close(dbConn);
}
return catId;
}
public ArrayList getSubCategories(String jobCat){
ArrayList jobSubCatList = null;
Connection dbConn = null;
try{
//Connect to th DB
dbConn = dbConnManager.connect();
Statement stmt = (Statement) dbConn.createStatement();
int jobCatId = getJobCatId(jobCat);
//Select the JobCatNames
String query = "SELECT DISTINCT subCategoryName FROM JobSubCategory WHERE jobCatId = "+jobCatId;
System.out.println(query);
ResultSet rs = stmt.executeQuery(query);
jobSubCatList = new ArrayList();
while (rs.next()) {
String subCatName = rs.getString(1);
System.out.println(subCatName);
jobSubCatList.add(subCatName);
}
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Select query failed at JobSubCatNames");
}finally{
//Close the db connection
dbConnManager.con_close(dbConn);
}
return jobSubCatList;
}
public int getSubCatId(String subCatName){
int subCatId = 0;
Connection dbConn = null;
try {
dbConn = dbConnManager.connect();
Statement stmt = (Statement) dbConn.createStatement();
String query = "SELECT jobSubCatId FROM JobSubCategory WHERE subCategoryName = '"+subCatName+"'";
System.out.println(query);
ResultSet rs = stmt.executeQuery(query);
if(rs.next()){
subCatId = Integer.parseInt(rs.getString(1));
System.out.println(subCatId);
}
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Select query Failed for JobCatId");
}finally{
dbConnManager.con_close(dbConn);
}
return subCatId;
}
public boolean addJob(jobDetails d) {
boolean result = false;
Connection dbConn = null;
try {
dbConn = dbConnManager.connect();
Statement stmt = (Statement) dbConn.createStatement();
int catId = getJobCatId(d.getJobCategoryName());
int subCatId = getSubCatId(d.getSubCategoryName());
String query = "INSERT INTO Jobs(jobName,postedDate,expirationDate,jobCategory,jobSubCategory) " +
"VALUES( '" + d.getJobName() + "','" + d.getPostedDate() + "','" + d.getExpirationDate() + "',"+catId+","+subCatId+")";
System.out.println(query);
int val = stmt.executeUpdate(query);
if (val == 1) {
result = true;
}
else {
result = false;
}
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Insert query failed");
result = false;
}finally{
dbConnManager.con_close(dbConn);
}
return result;
}
public Vector getJobDetails() {
Vector<Vector<String>> jobDetailsVector = null;
Connection dbConn = null;
try {
dbConn = dbConnManager.connect();
Statement stmt = (Statement) dbConn.createStatement();
String query = "SELECT jobID, jobName, postedDate, expirationDate, jobCatName, subCategoryName "+
"FROM Jobs J, JobCategory C, JobSubCategory S " +
"WHERE J.jobCategory=C.jobCatId AND J.jobSubCategory=S.jobSubCatid ORDER BY jobID DESC";
ResultSet rs = stmt.executeQuery(query);
jobDetailsVector = new Vector<Vector<String>>();
while (rs.next()) {
Vector<String> jobDetails = new Vector<String>();
jobDetails.add(rs.getString(1)); //jobID
jobDetails.add(rs.getString(2)); //jobName
jobDetails.add(rs.getString(3)); //postedDate
jobDetails.add(rs.getString(4)); //expirationDate
jobDetails.add(rs.getString(5)); //jobCatName
jobDetails.add(rs.getString(6)); //subCategoryName
jobDetailsVector.add(jobDetails);
}
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Select query failed");
} finally {
dbConnManager.con_close(dbConn);
}
return jobDetailsVector;
}
public boolean updateJob(jobDetails d) {
boolean result = false;
Connection dbConn = null;
try {
dbConn = dbConnManager.connect();
Statement stmt = (Statement) dbConn.createStatement();
//Select the JobCatId and the JobSubCatId
int catId = getJobCatId(d.getJobCategoryName());
int subCatId = getSubCatId(d.getSubCategoryName());
String query = "UPDATE Jobs SET jobName = '"
+ d.getJobName() + "' ,expirationDate = '" + d.getExpirationDate()
+ "' ,jobCategory = " + catId + " ,jobSubCategory = " + subCatId +
" WHERE jobId = " +d.getJobId();
System.out.println(query);
int val = stmt.executeUpdate(query);
if (val == 1) {
result = true;
} else {
result = false;
}
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Update query failed");
result = false;
} finally {
dbConnManager.con_close(dbConn);
}
return result;
}
public jobDetails getJobDetails(int jobId) {
jobDetails jobDetails = null;
Connection dbConn = null;
try {
//Connect to th DB
dbConn = dbConnManager.connect();
Statement stmt = (Statement) dbConn.createStatement();
String query = "SELECT j.jobId,j.jobName,j.postedDate, j.expirationDate, jc.jobCatName, js.subCategoryName " +
"FROM Jobs j,JobCategory jc,JobSubCategory js " +
"WHERE j.jobId = "+jobId+" AND jc.jobCatId = j.jobCategory AND js.jobSubCatId = j.jobSubCategory;";
System.out.println(query);
ResultSet rs = stmt.executeQuery(query);
jobDetails = new jobDetails();
if (rs.next()) {
jobDetails.setJobId(Integer.parseInt(rs.getString(1))); //jobID
jobDetails.setJobName(rs.getString(2)); //jobName
jobDetails.setPostedDate(rs.getString(3)); //postedDate
jobDetails.setExpirationDate(rs.getString(4)); //expirationDate
jobDetails.setJobCategoryName(rs.getString(5)); //jobCatName
jobDetails.setSubCategoryName(rs.getString(6)); //subCategoryName
}
} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Select query failed for JobID");
} finally {
//Close the db connection
dbConnManager.con_close(dbConn);
}
return jobDetails;
}
}
------------------------------------------------------------------------------------------------------------
jobDetails
jobDetails
package data1;
public class jobDetails {
private int jobId;
String jobName;
String postedDate;
String expirationDate;
String jobCategoryName;
String subCategoryName;
public jobDetails(String jobName, String postedDate, String expirationDate, String jobCategoryName, String subCategoryName) {
this.jobName = jobName;
this.postedDate = postedDate;
this.expirationDate = expirationDate;
this.jobCategoryName = jobCategoryName;
this.subCategoryName = subCategoryName;
}
public jobDetails(int jobID,String jobName, String expirationDate, String jobCategoryName, String subCategoryName) {
this.jobId = jobId;
this.jobName = jobName;
this.expirationDate = expirationDate;
this.jobCategoryName = jobCategoryName;
this.subCategoryName = subCategoryName;
}
public jobDetails() {
}
public String getExpirationDate() {
return expirationDate;
}
public String getJobCategoryName() {
return jobCategoryName;
}
public int getJobId() {
return jobId;
}
public String getJobName() {
return jobName;
}
public String getPostedDate() {
return postedDate;
}
public String getSubCategoryName() {
return subCategoryName;
}
public void setExpirationDate(String expirationDate) {
this.expirationDate = expirationDate;
}
public void setJobCategoryName(String jobCategoryName) {
this.jobCategoryName = jobCategoryName;
}
public void setJobId(int jobId) {
this.jobId = jobId;
}
public void setJobName(String jobName) {
this.jobName = jobName;
}
public void setPostedDate(String postedDate) {
this.postedDate = postedDate;
}
public void setSubCategoryName(String subCategoryName) {
this.subCategoryName = subCategoryName;
}
}
------------------------------------------------------------------------------------------------------------
GUI
JFrame_JobDescription
package gui;
import data1.JobDetailsDAO;
import data1.jobDetails;
public class JFrame_JobDescription extends javax.swing.JFrame {
public JFrame_JobDescription(){
}
/** Creates new form JFrame_JobDescription */
public JFrame_JobDescription(int jobId) {
initComponents();
showJobDetails(jobId);
}
public void showJobDetails(int jobId){
JobDetailsDAO dao= new JobDetailsDAO();
jobDetails d = dao.getJobDetails(jobId);
jTextField6.setText(String.valueOf(d.getJobId()));
jTextField1.setText(d.getJobName());
jTextField2.setText(d.getExpirationDate());
jTextField3.setText(d.getPostedDate());
jTextField4.setText(d.getJobCategoryName());
jTextField5.setText(d.getSubCategoryName());
}
------------------------------------------------------------------------------------------------------------
jobDetails1
package gui;
import data1.JobDetailsDAO;
import data1.jobDetails;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
public class jobDetails1 extends javax.swing.JFrame {
private Vector<Vector<String>> data; //Used for data from database
private Vector<String> header; //Used to store data header
int selectedJobId;
/** Creates new form jobdetails */
public jobDetails1() throws SQLException {
initComponents();
fillJobCatNames();
//Create column header for the table
header = new Vector<String>();
header.add("Job ID");
header.add("Name");
header.add("Posted Date");
header.add("Expiry Date");
header.add("Job Category");
header.add("Job Sub Category");
loadTable();
}
private void fillJobCatNames() throws SQLException {
JobDetailsDAO dao = new JobDetailsDAO();
ArrayList jobCatList = dao.getJobCategories();
Iterator i = jobCatList.iterator();
while (i.hasNext()) {
jComboBox1.addItem(i.next());
}
String jobCatName = jComboBox1.getSelectedItem().toString();
fillSubCatNames(jobCatName);
}
private void fillSubCatNames(String jobCat) {
JobDetailsDAO dao = new JobDetailsDAO();
ArrayList jobSubCatList = dao.getSubCategories(jobCat);
Iterator i = jobSubCatList.iterator();
jComboBox2.removeAllItems();
while (i.hasNext()) {
jComboBox2.addItem(i.next());
}
jComboBox2.setSelectedIndex(0);
}
private void selectRow() {
//retrieving the selected row index
int row = jTable_JobDetails.getSelectedRow();
//if a single row is selected from the table, take each cell values into the controls
if (jTable_JobDetails.getRowSelectionAllowed()) {
selectedJobId = Integer.parseInt(jTable_JobDetails.getValueAt(row, 0).toString());
jTextField1.setText(jTable_JobDetails.getValueAt(row, 1).toString());
jTextField_ExpDate.setText(jTable_JobDetails.getValueAt(row, 3).toString());
jComboBox1.setSelectedItem(jTable_JobDetails.getValueAt(row, 4).toString());
jComboBox2.setSelectedItem(jTable_JobDetails.getValueAt(row, 5).toString());
}
}
/** This method is called from within the constructor to
* initialize the form.
* WARNING: Do NOT modify this code. The content of this method is
* always regenerated by the Form Editor.
*/
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">
private void initComponents() {
jLabel1 = new javax.swing.JLabel();
jLabel2 = new javax.swing.JLabel();
jLabel3 = new javax.swing.JLabel();
jLabel4 = new javax.swing.JLabel();
add = new javax.swing.JButton();
updata = new javax.swing.JButton();
delete = new javax.swing.JButton();
jButton4 = new javax.swing.JButton();
jTextField1 = new javax.swing.JTextField();
jTextField_ExpDate = new javax.swing.JTextField();
jComboBox1 = new javax.swing.JComboBox();
jComboBox2 = new javax.swing.JComboBox();
jScrollPane1 = new javax.swing.JScrollPane();
jTable_JobDetails = new javax.swing.JTable();
select = new javax.swing.JButton();
view = new javax.swing.JButton();
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
setTitle("Job Details Management");
jLabel1.setText("Job Name");
jLabel2.setText("Expiring Date");
jLabel3.setText("Job catogary");
jLabel4.setText("Sub catogary");
add.setText("Add");
add.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
addActionPerformed(evt);
}
});
updata.setText("Update");
updata.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
updataActionPerformed(evt);
}
});
delete.setText("Delete");
delete.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
deleteActionPerformed(evt);
}
});
jButton4.setText("Clear");
jTextField1.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jTextField1ActionPerformed(evt);
}
});
jTextField_ExpDate.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jTextField_ExpDateActionPerformed(evt);
}
});
jComboBox1.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jComboBox1ActionPerformed(evt);
}
});
jComboBox2.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jComboBox2ActionPerformed(evt);
}
});
jTable_JobDetails.setModel(new javax.swing.table.DefaultTableModel(
new Object [][] {
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null}
},
new String [] {
"Job ID", "Job Name ", "Ex Date", "Posted Date", "Job Cat", "Job Sub Cat"
}
));
jTable_JobDetails.addMouseListener(new java.awt.event.MouseAdapter() {
public void mouseClicked(java.awt.event.MouseEvent evt) {
jTable_JobDetailsMouseClicked(evt);
}
});
jScrollPane1.setViewportView(jTable_JobDetails);
select.setText("Select");
select.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
selectActionPerformed(evt);
}
});
view.setText("View");
view.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
viewActionPerformed(evt);
}
});
private void deleteActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void jComboBox2ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void jComboBox1ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
String jobCat = jComboBox1.getSelectedItem().toString();
fillSubCatNames(jobCat);
}
private void selectActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
DatePicker datePicker = new DatePicker(this);
jTextField_ExpDate.setText(datePicker.setPickedDate());
}
private void jTextField_ExpDateActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void addActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
//Read the job details
String jobName = jTextField1.getText();
SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
String postedDate = sdf.format(new Date());
String expDate = jTextField_ExpDate.getText();
String jobCatName = jComboBox1.getSelectedItem().toString();
String subCatName = jComboBox2.getSelectedItem().toString();
//Create a JobDetails Object
jobDetails d = new jobDetails(jobName, postedDate, expDate, jobCatName, subCatName);
//Save to the database
JobDetailsDAO dao = new JobDetailsDAO();
dao.addJob(d);
}
private void jTextField1ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void jTable_JobDetailsMouseClicked(java.awt.event.MouseEvent evt) {
// TODO add your handling code here:
selectRow();
}
private void updataActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
JobDetailsDAO dao = new JobDetailsDAO();
//Read the job details
String jobName = jTextField1.getText();
String expDate = jTextField_ExpDate.getText();
String jobCatName = jComboBox1.getSelectedItem().toString();
String subCatName = jComboBox2.getSelectedItem().toString();
//Create a JobDetails Object
jobDetails d = new jobDetails(selectedJobId, jobName, expDate, jobCatName, subCatName);
//Save to the database
dao.updateJob(d);
//Refresh the table
loadTable();
}
private void viewActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
JFrame_JobDescription frm = new JFrame_JobDescription(selectedJobId);
frm.showJobDetails(selectedJobId);
frm.setVisible(true);
frm.setDefaultCloseOperation(frm.DISPOSE_ON_CLOSE);
}
No comments:
Post a Comment