A simple JDBC linker
Package com
Import java.sql.Connection
Import java.sql.ResultSet
Import java.sql.SQLException
Import java.sql.Statement
Import java.util.ArrayList
Public class NavalMgmtDAO {
Public static void main (String [] args) {
NavalMgmtDAO navalMgmtDAO=new NavalMgmtDAO ()
Double sal=navalMgmtDAO.getOfficersTotalSalOnBaseCamp (38)
System.out.println (sal)
}
Public int addNavalOfficer (NavalOfficer navalOfficer)
{
Int no=navalOfficer.getOfficerNo ()
String name=navalOfficer.getOfficerName ()
String rank=navalOfficer.getOfficerRank ()
Double sal=navalOfficer.getOfficerSal ()
Int campid=navalOfficer.getBaseCampId ()
Connection connection = null
Int status = 0
DBConnectionHelper helper = new DBConnectionHelper ()
Connection = helper.getOracleConnection ()
Statement statement = null
Try {
Connection.setAutoCommit (false)
Statement = connection.createStatement ()
String sql = "INSERT INTO TBL_Officer_1273752 values (" + no+ ",'" + name+ "','" + rank+ "'," + sal+ "," + campid+ ")"
Status = statement.executeUpdate (sql)
Connection.commit ()
} catch (SQLException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
}
Finally {
Try {
If (statement! = null)
Statement.close ()
/ / System.out.println ("STATEMENT SUCCESSFULLY CLOSED")
} catch (SQLException se) {
Se.printStackTrace ()
} / / do nothing
Try {
If (connection! = null)
Connection.close ()
/ / System.out.println ("CONNECTION SUCCESSFULLY CLOSED")
} catch (SQLException se) {
Se.printStackTrace ()
} / / end finally try
} / / end try
If (status = = 1) {
System.out.println ("Data inserted successfully")
} else {
System.out.println ("Connection error")
}
Return status
}
Public int addBaseCamp (BaseCamp baseCamp)
{
Int id=baseCamp.getBaseCampId ()
String name=baseCamp.getBaseCampName ()
Int loc=baseCamp.getBaseCampLoc ()
Connection connection = null
Int status = 0
DBConnectionHelper helper = new DBConnectionHelper ()
Connection = helper.getOracleConnection ()
Statement statement = null
Try {
Connection.setAutoCommit (false)
Statement = connection.createStatement ()
String sql = "INSERT INTO TBL_Base_Camp_1273752 values (" + id+ ",'" + name+ "','" + loc+ "')"
Status = statement.executeUpdate (sql)
Connection.commit ()
} catch (SQLException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
}
Finally {
Try {
If (statement! = null)
Statement.close ()
/ / System.out.println ("STATEMENT SUCCESSFULLY CLOSED")
} catch (SQLException se) {
Se.printStackTrace ()
} / / do nothing
Try {
If (connection! = null)
Connection.close ()
/ / System.out.println ("CONNECTION SUCCESSFULLY CLOSED")
} catch (SQLException se) {
Se.printStackTrace ()
} / / end finally try
} / / end try
If (status = = 1) {
System.out.println ("Data inserted successfully")
} else {
System.out.println ("Connection error")
}
Return status
}
Public ArrayList getOfficersNameSortedBySal ()
{
ArrayList arrayList=new ArrayList ()
Connection connection = null
DBConnectionHelper helper = new DBConnectionHelper ()
Connection = helper.getOracleConnection ()
Statement statement = null
ResultSet resultSet = null
Try {
Statement = connection.createStatement ()
String sql = "select officer_name from tbl_officer_1273752 order by officer_sal"
ResultSet = statement.executeQuery (sql)
While (resultSet.next ()) {
String name = resultSet.getString (1)
ArrayList.add (name)
}
} catch (SQLException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
} finally {
Try {
If (statement! = null)
Statement.close ()
} catch (SQLException se) {
} / / do nothing
Try {
If (connection! = null)
Connection.close ()
} catch (SQLException se) {
Se.printStackTrace ()
} / / end finally try
If (resultSet! = null) {
Try {
ResultSet.close ()
} catch (SQLException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
}
}
} / / end try
Return arrayList
}
Public ArrayList getOfficersNameForBaseCampLoc (String baseCampLoc)
{
ArrayList arrayList=new ArrayList ()
Connection connection = null
DBConnectionHelper helper = new DBConnectionHelper ()
Connection = helper.getOracleConnection ()
Statement statement = null
ResultSet resultSet = null
Try {
Statement = connection.createStatement ()
String sql = "select officer_name from tbl_officer_1273752 where base_camp_id in (select base_camp_id from tbl_base_camp_1273752 where base_camp_loc='" + baseCampLoc+ "')"
ResultSet = statement.executeQuery (sql)
While (resultSet.next ()) {
String name = resultSet.getString (1)
ArrayList.add (name)
}
} catch (SQLException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
} finally {
Try {
If (statement! = null)
Statement.close ()
} catch (SQLException se) {
} / / do nothing
Try {
If (connection! = null)
Connection.close ()
} catch (SQLException se) {
Se.printStackTrace ()
} / / end finally try
If (resultSet! = null) {
Try {
ResultSet.close ()
} catch (SQLException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
}
}
} / / end try
Return arrayList
}
Public double getOfficersTotalSalOnBaseCamp (int baseCampId)
{
Double sal=0
Connection connection = null
DBConnectionHelper helper = new DBConnectionHelper ()
Connection = helper.getOracleConnection ()
Statement statement = null
ResultSet resultSet = null
Try {
Statement = connection.createStatement ()
String sql = "select sum (officer_sal) from tbl_officer_1273752 where base_camp_id=" + baseCampId+ ""
ResultSet = statement.executeQuery (sql)
While (resultSet.next ()) {
Sal= resultSet.getDouble (1)
}
} catch (SQLException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
} finally {
Try {
If (statement! = null)
Statement.close ()
} catch (SQLException se) {
} / / do nothing
Try {
If (connection! = null)
Connection.close ()
} catch (SQLException se) {
Se.printStackTrace ()
} / / end finally try
If (resultSet! = null) {
Try {
ResultSet.close ()
} catch (SQLException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
}
}
} / / end try
Return sal
}
}
