// Connection URL.
System.out.println(classNotFoundException + "-----------Unable to load database driver classes");
dbConn = (Connection) DriverManager.getConnection(sourceURL, "username", "password");
------------------------------------------------------------------------------------------------------------
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;
}
}
------------------------------------------------------------------------------------------------------------
