Shulou(Shulou.com)06/01 Report--

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

}

}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.