public Vector getUsacFiles(String invoice) {
Vector fileList = new Vector();
String query = "";
query =
query
+ " SELECT distinct filename, ' Date:'||to_char(usac_prcs_dat,'MM/DD/YYYY'),trunc(usac_prcs_dat) usac_prcs_dat ";
query = query + " FROM stage_usac_form ";
query = query + " WHERE rtrim(ltrim(sdc_inv_no)) = ?";
query = query + " ORDER BY usac_prcs_dat ";
USFEnv.getLog().writeDebug("getUsacFiles() Query :" + query, this, null);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = cConn.prepareStatement(query);
pstmt.setString(1, invoice);
rs = pstmt.executeQuery();
while (rs.next()) {
fileList.addElement(rs.getString(1));
fileList.addElement(rs.getString(2));
}
// rs.close();
// stmt.close();
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (Exception e) {
USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, e);
}
USFEnv.getLog().writeCrit("getUsacFiles() Failed Query:" + query, this, ex);
}
/* try
{
if( rs != null)
rs.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
}
try
{
if( pstmt != null)
pstmt.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
}*/
return fileList;
}
private String getMonth(String month) {
String query;
String i_month = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";
USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
rs = pstmt.executeQuery();
if (rs.next()) {
i_month = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return i_month;
}
public boolean checkChartUpdateRequired(String doi, Connection con) throws SQLException {
PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY);
getUpdateStmt.setString(1, doi);
ResultSet rs1 = getUpdateStmt.executeQuery();
int lastNcites = 0;
if (rs1.first()) {
lastNcites = rs1.getInt("lastNcites");
} else {
rs1.close();
getUpdateStmt.close();
return true;
}
rs1.close();
getUpdateStmt.close();
PreparedStatement getNcitesStmt = con.prepareStatement(DEF_GET_NCITES_QUERY);
getNcitesStmt.setString(1, doi);
int ncites = 0;
ResultSet rs2 = getNcitesStmt.executeQuery();
if (rs2.first()) {
ncites = rs2.getInt("ncites");
}
rs2.close();
getNcitesStmt.close();
if (ncites != lastNcites) {
return true;
} else {
return false;
}
} // - checkChartUpdateRequired
private void initFromDatabase() throws SQLException, BlockStoreException {
Statement s = conn.get().createStatement();
ResultSet rs;
rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'");
if (!rs.next()) {
throw new BlockStoreException("corrupt Postgres block store - no chain head pointer");
}
Sha256Hash hash = new Sha256Hash(rs.getBytes(1));
rs.close();
this.chainHeadBlock = get(hash);
this.chainHeadHash = hash;
if (this.chainHeadBlock == null) {
throw new BlockStoreException("corrupt Postgres block store - head block not found");
}
rs =
s.executeQuery(
"SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'");
if (!rs.next()) {
throw new BlockStoreException(
"corrupt Postgres block store - no verified chain head pointer");
}
hash = new Sha256Hash(rs.getBytes(1));
rs.close();
s.close();
this.verifiedChainHeadBlock = get(hash);
this.verifiedChainHeadHash = hash;
if (this.verifiedChainHeadBlock == null) {
throw new BlockStoreException("corrupt Postgres block store - verified head block not found");
}
}
/**
* This method queries the database to get the name of the Billing System.
*
* @exception SQLException, if query fails
* @author
*/
public String getBlgsysnm(String blgsys) {
String query;
String blgsysnm = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select bs_nm from blg_sys where bs_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
rs = pstmt.executeQuery();
if (rs.next()) {
blgsysnm = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
}
}
return blgsysnm;
}
/**
* This method queries the database to validate the passed in FRN for the selected Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public boolean validateFRN(String frn, String year) {
String query;
boolean validfrn = false;
Statement stmt = null;
ResultSet rs = null;
query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year;
USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
validfrn = true;
if (rs != null) rs.close();
if (stmt != null) stmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the Invoice Number associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public long getInvID(String inv_no) {
String query;
long invID = 0;
Statement stmt = null;
ResultSet rs = null;
query = "select inv_id from inv where inv_no=" + inv_no;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
invID = rs.getLong(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvview: The Invoice ID not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return (invID);
}
/**
* This method queries the database to get the Invoice Number associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public String getInvno(String invid) {
String query;
String invno = "";
Statement stmt = null;
ResultSet rs = null;
query = "select inv_no from rhcc_inv where rhcc_inv_id=" + invid;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
invno = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return invno;
}
/**
* This method queries the database to get the SPIN associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public String getSPINname(String invid) {
String query;
String spinnm = "";
Statement stmt = null;
ResultSet rs = null;
// query = "select slc_nm from usw_co,inv where uc_id=uc_id_fk and inv_id="+invid;
query =
"select srv_provr_nm from srv_provr,rhcc_inv where spin = rhcc_inv.spin_fk and rhcc_inv_id="
+ invid;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
spinnm = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: The SPIN Name not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return spinnm;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getYears(String year) {
String query;
Vector years = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
rs = pstmt.executeQuery();
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return years;
}
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public String getBpid() {
String query;
String bpid = "";
Statement stmt = null;
ResultSet rs = null;
query = "select bp_id_seq.nextval from dual ";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
bpid = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return bpid;
}
/**
* This method queries the database to get the details related to the bp_id passed.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBpdet(String bpid) {
String query;
Vector bpdet = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
rs = pstmt.executeQuery();
while (rs.next()) {
bpdet.addElement(rs.getString(1));
bpdet.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return bpdet;
}
@Test
public void testImport() throws Exception {
MemoryDatabase memDb = new MemoryDatabase("sample");
memDb.start();
final String jsonFileName = "/sample.json";
memDb.importJSON(getClass(), jsonFileName);
JSONParser jsonParser = new JSONParser();
JSONArray tables =
(JSONArray)
jsonParser.parse(new InputStreamReader(getClass().getResourceAsStream(jsonFileName)));
assertEquals(2, tables.size());
Connection conn = memDb.getConnection();
Statement stmt = conn.createStatement();
JSONObject employee = (JSONObject) tables.get(0);
ResultSet rs = stmt.executeQuery("SELECT * FROM \"employee\"");
verifyTableEquals(employee, rs);
rs.close();
JSONObject team = (JSONObject) tables.get(1);
rs = stmt.executeQuery("SELECT * FROM \"team\"");
verifyTableEquals(team, rs);
rs.close();
stmt.close();
conn.close();
memDb.stop();
}
/**
* Returns the normalized Authority value for an author based on the name passed in. If no
* authority exists, null will be returned.
*
* @param author the author to get the authority information for
* @return the normalized authority information or null if no authority exists.
*/
public static String getNormalizedAuthorAuthorityFromDatabase(String author) {
if (!connectToDatabase()) {
return null;
} else {
try {
getPreferredAuthorByOriginalNameStmt.setString(1, author);
// First check without normalization
ResultSet originalNameResults = getPreferredAuthorByOriginalNameStmt.executeQuery();
if (originalNameResults.next()) {
String authority = originalNameResults.getString("normalizedName");
// Found a match
originalNameResults.close();
return authority;
} else {
// No match, check alternate names for the author
String normalizedAuthor = AuthorNormalizer.getNormalizedName(author);
getPreferredAuthorByAlternateNameStmt.setString(1, normalizedAuthor);
ResultSet alternateNameResults = getPreferredAuthorByAlternateNameStmt.executeQuery();
if (alternateNameResults.next()) {
String authority = alternateNameResults.getString("normalizedName");
alternateNameResults.close();
return authority;
}
}
} catch (Exception e) {
logger.error("Error loading authority information from database", e);
}
}
return null;
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
String username = request.getParameter("username");
String password = request.getParameter("password");
Statement stmt;
ResultSet rs;
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://localhost/myflickr?" + "user=root&password=123456";
con = DriverManager.getConnection(connectionUrl);
if (con != null) {
System.out.println("connected to mysql");
}
} catch (SQLException e) {
System.out.println("SQL Exception: " + e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: " + cE.toString());
}
try {
stmt = con.createStatement();
System.out.println("SELECT * FROM flickrusers WHERE name='" + username + "'");
rs = stmt.executeQuery("SELECT * FROM flickrusers WHERE name='" + username + "'");
while (rs.next()) {
if (rs.getObject(1).toString().equals(username)) {
out.println("<h1>To username pou epileksate uparxei hdh</h1>");
out.println("<a href=\"project3.html\">parakalw dokimaste kapoio allo.</a>");
stmt.close();
rs.close();
return;
}
}
stmt.close();
rs.close();
stmt = con.createStatement();
if (!stmt.execute("INSERT INTO flickrusers VALUES('" + username + "', '" + password + "')")) {
out.println("<h1>Your registration is completed " + username + "</h1>");
out.println("<a href=\"index.jsp\">go to the login menu</a>");
registerListener.Register(username);
} else {
out.println("<h1>To username pou epileksate uparxei hdh</h1>");
out.println("<a href=\"project3.html\">Register</a>");
}
} catch (SQLException e) {
throw new ServletException("Servlet Could not display records.", e);
}
}
/**
* This method queries the database to check if the passed start date for the Journal Month starts
* exactly after the Previous end date.
*
* @exception SQLException, if query fails
* @author
*/
public boolean check(String strtdat, String blgsys, String year, String month, String rgn) {
if (month.length() > ((new Integer("2")).intValue())) {
month = getMonth(month);
}
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from (select bp_end_dat,bp_year from blg_prd ";
query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?";
if ((rgn != null) && !(rgn.equals(""))) {
query = query + " and bp_rgn = ?";
} else {
query = query + " and bp_rgn is null";
}
query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A ";
query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1";
USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
pstmt.setString(2, month);
pstmt.setString(3, blgsys);
int m = 4;
if ((rgn != null) && !(rgn.equals(""))) {
pstmt.setString(m, rgn);
m = m + 1;
}
pstmt.setString(m, month);
pstmt.setString(m + 1, year);
pstmt.setString(m + 2, year);
pstmt.setString(m + 3, strtdat);
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the Journal Dates for the passed in Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public Vector getJrnldts(String year) {
String query;
Vector jrnllst = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query =
"select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
query =
query
+ "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
query =
query
+ "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
query = query + "from blg_prd,blg_sys ";
query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
query = query + "(select strt_dat from fung_yr where yr = ?) and ";
query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
query = query + "order by ord,nm,bp_strt_dat ";
USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
pstmt.setString(2, year);
pstmt.setString(3, year);
rs = pstmt.executeQuery();
while (rs.next()) {
Dinvjrnl jrnldts = new Dinvjrnl(null);
jrnldts.strBpid = rs.getString(1);
jrnldts.strBlgsys = rs.getString(3);
jrnldts.strBlgmnth = rs.getString(5);
jrnldts.strBlgstrtdt = rs.getString(6);
jrnldts.strBlgenddt = rs.getString(7);
jrnllst.addElement(jrnldts);
jrnldts = null;
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
return jrnllst;
}
public ExptLocatorTree(Genome g) {
super();
try {
java.sql.Connection c = DatabaseFactory.getConnection(ExptLocator.dbRole);
int species = g.getSpeciesDBID();
int genome = g.getDBID();
Statement s = c.createStatement();
ResultSet rs = null;
rs =
s.executeQuery(
"select e.name, e.version from experiment e, exptToGenome eg where e.active=1 and "
+ "e.id=eg.experiment and eg.genome="
+ genome);
while (rs.next()) {
String name = rs.getString(1);
String version = rs.getString(2);
ChipChipLocator loc = new ChipChipLocator(g, name, version);
this.addElement(loc.getTreeAddr(), loc);
}
rs.close();
rs =
s.executeQuery(
"select ra.name, ra.version from rosettaanalysis ra, rosettaToGenome rg where "
+ "ra.id = rg.analysis and ra.active=1 and rg.genome="
+ genome);
while (rs.next()) {
String name = rs.getString(1);
String version = rs.getString(2);
MSPLocator msp = new MSPLocator(g, name, version);
this.addElement(msp.getTreeAddr(), msp);
}
rs.close();
rs =
s.executeQuery(
"select ra.name, ra.version from bayesanalysis ra, bayesToGenome rg where "
+ "ra.id = rg.analysis and ra.active=1 and rg.genome="
+ genome);
while (rs.next()) {
String name2 = rs.getString(1);
String version2 = rs.getString(2);
ExptLocator loc2 = new BayesLocator(g, name2, version2);
addElement(loc2.getTreeAddr(), loc2);
}
rs.close();
s.close();
DatabaseFactory.freeConnection(c);
} catch (SQLException se) {
se.printStackTrace(System.err);
throw new RuntimeException(se);
} catch (UnknownRoleException e) {
e.printStackTrace();
}
}
/**
* Determine whether or a not a User with the supplied researcherID exists
*
* @param username The researcherID to test
* @return true if the user exists, false if not
* @throws SQLException if a database error was encountered
*/
public static boolean userExists(int researcherID) throws SQLException {
boolean returnVal = false;
// Get our connection to the database.
Connection conn = DBConnectionManager.getConnection("yrc");
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement("SELECT researcherID FROM tblUsers WHERE researcherID = ?");
stmt.setInt(1, researcherID);
rs = stmt.executeQuery();
// No rows returned.
if (!rs.next()) {
returnVal = false;
} else {
returnVal = true;
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {;
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {;
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {;
}
conn = null;
}
}
return returnVal;
}
public int createRelease(String label, String releaseDesc) {
Connection dbConn = null;
PreparedStatement prepStmt = null;
Statement stmt = null;
ResultSet rset = null;
try {
dbConn = ds.getConnection();
dbConn.setAutoCommit(false);
stmt = dbConn.createStatement();
rset = stmt.executeQuery("select nextval('release_id_seq')");
rset.next();
int ret = rset.getInt(1);
rset.close();
prepStmt = dbConn.prepareStatement("insert into release values(?, ?, now(), ?)");
prepStmt.setInt(1, ret);
prepStmt.setString(2, label);
if (releaseDesc == null) {
prepStmt.setNull(3, Types.VARCHAR);
} else {
prepStmt.setString(3, releaseDesc);
}
prepStmt.execute();
dbConn.commit();
return ret;
} catch (SQLException e) {
try {
dbConn.rollback();
} catch (Exception ee) {
}
throw new RuntimeException(e);
} finally {
try {
rset.close();
} catch (Exception e) {
}
try {
stmt.close();
} catch (Exception e) {
}
try {
prepStmt.close();
} catch (Exception e) {
}
try {
dbConn.close();
} catch (Exception e) {
}
}
}
/**
* Description of the Method
*
* @param db Description of the Parameter
* @return Description of the Return Value
* @throws SQLException Description of the Exception
*/
public DependencyList processDependencies(Connection db) throws SQLException {
DependencyList dependencyList = new DependencyList();
// Check for orders associated with this customer product
try {
PreparedStatement pst =
db.prepareStatement(
"SELECT COUNT(DISTINCT(order_id)) as orderscount "
+ "FROM customer_product_history "
+ "WHERE customer_product_id = ? ");
pst.setInt(1, this.getId());
ResultSet rs = pst.executeQuery();
if (rs.next()) {
int orderscount = rs.getInt("orderscount");
if (orderscount != 0) {
Dependency thisDependency = new Dependency();
thisDependency.setName("orders");
thisDependency.setCount(orderscount);
thisDependency.setCanDelete(true);
dependencyList.add(thisDependency);
}
}
rs.close();
pst.close();
} catch (SQLException e) {
throw new SQLException(e.getMessage());
}
// check for quotes associated with this customer product
try {
PreparedStatement pst =
db.prepareStatement(
"SELECT COUNT(*) as quotescount FROM order_entry "
+ "WHERE quote_id > -1 AND order_id IN "
+ "(SELECT DISTINCT(order_id) "
+ " FROM customer_product_history "
+ " WHERE customer_product_id = ? )");
pst.setInt(1, this.getId());
ResultSet rs = pst.executeQuery();
if (rs.next()) {
int quotescount = rs.getInt("quotescount");
if (quotescount != 0) {
Dependency thisDependency = new Dependency();
thisDependency.setName("quotes");
thisDependency.setCount(quotescount);
thisDependency.setCanDelete(true);
dependencyList.add(thisDependency);
}
}
rs.close();
pst.close();
} catch (SQLException e) {
throw new SQLException(e.getMessage());
}
return dependencyList;
}
public void run() {
m_nTrans = 0;
m_nTrans1Sec = 0;
m_nTrans2Sec = 0;
m_nTimeSum = 0;
if (m_nNumRuns <= 0) return;
Statement stmt = null;
try {
stmt = m_conn.createStatement();
ResultSet set;
// get branch count
log("select max(branch) from " + m_Driver.getBranchName() + "\n", 2);
set = stmt.executeQuery("select max(branch) from " + m_Driver.getBranchName());
if (set != null && set.next()) {
m_nMaxBranch = set.getInt(1);
set.close();
}
// get teller count
log("select max(teller) from " + m_Driver.getTellerName() + "\n", 2);
set = stmt.executeQuery("select max(teller) from " + m_Driver.getTellerName());
if (set != null && set.next()) {
m_nMaxTeller = set.getInt(1);
set.close();
}
// get account count
log("select max(account) from " + m_Driver.getAccountName() + "\n", 2);
set = stmt.executeQuery("select max(account) from " + m_Driver.getAccountName());
if (set != null && set.next()) {
m_nMaxAccount = set.getInt(1);
set.close();
}
} catch (SQLException e) {
log("Error getting table limits : " + e.getMessage() + "\n", 0);
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException e) {
}
stmt = null;
}
// System.out.println("Thread : " + getName() + " Branch :" + m_nMaxBranch + " Teller : " +
// m_nMaxTeller + " Account : " + m_nMaxAccount);
if (m_bRunText) runTextTest();
if (m_bRunPrepared) runPrepareTest();
if (m_bRunSProc) runProcTest();
if (m_bCloseConnection) {
try {
m_conn.close();
log("Thread connection closed", 2);
} catch (SQLException e) {
}
}
if (m_log != null) m_log.taskDone();
}
@Test
public void testReadWithConnection() throws Exception {
PreparedStatement preparedStatement = EasyMock.createStrictMock(PreparedStatement.class);
ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
Connection connection = EasyMock.createStrictMock(Connection.class);
SavedReviewerSearch search = new SavedReviewerSearch();
int id = 34;
String name = "myName";
SavedReviewerSearchDAO dao =
EasyMock.createMockBuilder(SavedReviewerSearchDAO.class)
.withConstructor()
.addMockedMethod("resultFromResultSet")
.createStrictMock();
EasyMock.expect(connection.prepareStatement(SavedReviewerSearchDAO.READ_SQL))
.andReturn(preparedStatement);
preparedStatement.setInt(1, id);
EasyMock.expectLastCall();
preparedStatement.setString(2, name);
EasyMock.expectLastCall();
EasyMock.expect(preparedStatement.executeQuery()).andReturn(resultSet);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(dao.resultFromResultSet(resultSet)).andReturn(search);
resultSet.close();
EasyMock.expectLastCall();
preparedStatement.close();
EasyMock.replay(preparedStatement, resultSet, connection, dao);
SavedReviewerSearch result = dao.read(connection, id, name);
assertEquals("Wrong search returned", search, result);
EasyMock.verify(preparedStatement, resultSet, connection, dao);
// failure case
EasyMock.reset(preparedStatement, resultSet, connection, dao);
EasyMock.expect(connection.prepareStatement(SavedReviewerSearchDAO.READ_SQL))
.andReturn(preparedStatement);
preparedStatement.setInt(1, id);
EasyMock.expectLastCall();
preparedStatement.setString(2, name);
EasyMock.expectLastCall();
EasyMock.expect(preparedStatement.executeQuery()).andReturn(resultSet);
EasyMock.expect(resultSet.next()).andReturn(false);
resultSet.close();
EasyMock.expectLastCall();
preparedStatement.close();
EasyMock.replay(preparedStatement, resultSet, connection, dao);
try {
dao.read(connection, id, name);
fail("Should have thrown a sql exception.");
} catch (SQLException ex) {
// expected
}
EasyMock.verify(preparedStatement, resultSet, connection, dao);
}
@Test
public void utf() throws SQLException {
ResultSet rs =
stat.executeQuery(
"select '"
+ utf01
+ "','"
+ utf02
+ "','"
+ utf03
+ "','"
+ utf04
+ "','"
+ utf05
+ "','"
+ utf06
+ "','"
+ utf07
+ "','"
+ utf08
+ "';");
assertEquals(utf01, rs.getString(1));
assertEquals(utf02, rs.getString(2));
assertEquals(utf03, rs.getString(3));
assertEquals(utf04, rs.getString(4));
assertEquals(utf05, rs.getString(5));
assertEquals(utf06, rs.getString(6));
assertEquals(utf07, rs.getString(7));
assertEquals(utf08, rs.getString(8));
rs.close();
PreparedStatement prep = conn.prepareStatement("select ?,?,?,?,?,?,?,?;");
prep.setString(1, utf01);
prep.setString(2, utf02);
prep.setString(3, utf03);
prep.setString(4, utf04);
prep.setString(5, utf05);
prep.setString(6, utf06);
prep.setString(7, utf07);
prep.setString(8, utf08);
rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(utf01, rs.getString(1));
assertEquals(utf02, rs.getString(2));
assertEquals(utf03, rs.getString(3));
assertEquals(utf04, rs.getString(4));
assertEquals(utf05, rs.getString(5));
assertEquals(utf06, rs.getString(6));
assertEquals(utf07, rs.getString(7));
assertEquals(utf08, rs.getString(8));
rs.close();
}
public void readData() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con =
DriverManager.getConnection(
"jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + filep);
stmnt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rst = con.getMetaData().getTables(null, null, "%", null);
rst.next();
String SheetName = rst.getString(3);
String query = "Select distinct * from [" + SheetName + "]";
rs = stmnt.executeQuery(query);
if (rs != null) {
rs.last();
RowCount = rs.getRow();
rs.beforeFirst();
// stmnt.close();
rst.close();
if (RowCount > 0) {
registno = new String[RowCount];
name = new String[RowCount];
kdno = new int[RowCount];
kcno = new double[RowCount];
ccno = new double[RowCount];
seat = new double[RowCount];
i = 0;
while (rs.next() && (rs.getString(1) != null)) {
// rs.next();
i++;
try {
registno[i] = rs.getString(1);
name[i] = rs.getString(2);
kdno[i] = Integer.parseInt(rs.getString(3));
kcno[i] = Double.parseDouble(rs.getString(4));
ccno[i] = Double.parseDouble(rs.getString(5));
seat[i] = Double.parseDouble(rs.getString(6));
System.out.println(i);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
rs.close();
con.close();
} catch (Exception e) {
// System.out.println("fail to get student connection");
}
}
boolean validar(String busqueda, String Tabla, String llave_consulta, String llave_busqueda)
throws IOException, SQLException {
Connection con = null;
ResultSet resultadosConsulta = null;
Statement instruccionSQL = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost/rsu_inventario", "root", "inforsu");
// Preparamos la consulta
instruccionSQL = con.createStatement();
resultadosConsulta =
instruccionSQL.executeQuery(
"SELECT "
+ busqueda
+ " FROM "
+ Tabla
+ " WHERE "
+ llave_consulta
+ " = '"
+ llave_busqueda
+ "'");
if (resultadosConsulta
.next()) { // si es valido el primer reg. hay una fila, tons el usuario y su pw existen
con.close();
resultadosConsulta.close();
instruccionSQL.close();
;
return true;
} // usuario validado correctamente
else {
con.close();
resultadosConsulta.close();
instruccionSQL.close();
return false;
} // usuario validado incorrectamente
} catch (Exception e) {
e.printStackTrace();
con.close();
resultadosConsulta.close();
instruccionSQL.close();
return false;
}
}
@Test
public void testCreateWithConnection() throws Exception {
PreparedStatement preparedStatement = EasyMock.createStrictMock(PreparedStatement.class);
ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
Connection connection = EasyMock.createStrictMock(Connection.class);
SavedReviewerSearch search = new SavedReviewerSearch();
Date date = new Date();
SavedReviewerSearchDAO dao =
EasyMock.createMockBuilder(SavedReviewerSearchDAO.class)
.withConstructor()
.addMockedMethod("updatePreparedStatementForCreate")
.createStrictMock();
EasyMock.expect(connection.prepareStatement(SavedReviewerSearchDAO.CREATE_SQL))
.andReturn(preparedStatement);
dao.updatePreparedStatementForCreate(preparedStatement, search);
EasyMock.expectLastCall();
EasyMock.expect(preparedStatement.executeQuery()).andReturn(resultSet);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(resultSet.getTimestamp(1)).andReturn(new Timestamp(date.getTime()));
resultSet.close();
EasyMock.expectLastCall();
preparedStatement.close();
EasyMock.replay(preparedStatement, resultSet, connection, dao);
SavedReviewerSearch result = dao.create(connection, search);
EasyMock.verify(preparedStatement, resultSet, connection, dao);
assertEquals("Wrong created date.", date, result.getCreatedDate());
// failure case
EasyMock.reset(preparedStatement, resultSet, connection, dao);
EasyMock.expect(connection.prepareStatement(SavedReviewerSearchDAO.CREATE_SQL))
.andReturn(preparedStatement);
dao.updatePreparedStatementForCreate(preparedStatement, search);
EasyMock.expectLastCall();
EasyMock.expect(preparedStatement.executeQuery()).andReturn(resultSet);
EasyMock.expect(resultSet.next()).andReturn(false);
resultSet.close();
EasyMock.expectLastCall();
preparedStatement.close();
EasyMock.replay(preparedStatement, resultSet, connection, dao);
try {
dao.create(connection, search);
fail("Should have thrown a sql exception.");
} catch (SQLException ex) {
// expected
}
EasyMock.verify(preparedStatement, resultSet, connection, dao);
}
// metode updateType atjauno informāciju par tipiem.
// Nosaka tipiem norādīto procesu laikus un tos sasummējot ieraksta Process_Type tabulā
public static void updateType() {
try {
Statement stmt = connect.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM PROCESS_TYPE;");
int counter = 0;
// while ( rs.next() )
// {
counter = rs.getInt("COUNT(*)");
// }
rs.close();
rs =
stmt.executeQuery(
"SELECT Type_ID, SUM(COUNT_DAY), SUM(COUNT_ALL) FROM PROCESS GROUP BY Type_ID;");
int[] id = new int[counter];
int[] count_day = new int[counter];
int[] count_all = new int[counter];
int i = 0;
while (rs.next()) {
id[i] = rs.getInt("Type_ID");
count_day[i] = rs.getInt("SUM(COUNT_DAY)");
count_all[i] = rs.getInt("SUM(COUNT_ALL)");
i++;
}
rs.close();
stmt.executeUpdate("UPDATE PROCESS_TYPE SET Type_Count_Day=0, Type_Count_All=0;");
for (int k = 0; k < counter; k++) {
stmt.executeUpdate(
"UPDATE PROCESS_TYPE SET Type_Count_Day = '"
+ count_day[k]
+ "',Type_Count_All = '"
+ count_all[k]
+ "' WHERE PROCESS_TYPE.PT_ID = '"
+ id[k]
+ "';");
}
stmt.close();
// System.out.println("Type count updated");
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
// System.exit(0);
}
}
public void updateScore(List<Player> players) {
try {
if (connection == null || players.size() <= 0) {
return;
}
List<String> playersName = new ArrayList<String>();
for (Player player : players) {
playersName.add("'" + player.getName() + "'");
}
String arrs = join(playersName, ",");
String strSql = "SELECT * FROM player WHERE name IN (" + arrs + ")";
cacheScores.clear();
Map<String, Object> map;
Statement sql = connection.createStatement();
ResultSet result = sql.executeQuery(strSql);
while (result.next()) {
map = new HashMap<String, Object>();
map.put("kills", result.getInt("kills"));
map.put("deaths", result.getInt("deaths"));
map.put("mobs", result.getInt("mobs"));
map.put("prefix", result.getString("prefix"));
cacheScores.put(result.getString("name"), map);
}
result.close();
sql.close();
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public Emp get(Integer objectId) {
if (conn == null) {
System.err.println("No connect!!");
return null;
}
Emp emp = null;
try {
final String sql =
"SELECT " + "id,lname,fname,mname,d_hire,d_fire " + "FROM DDT_EMP" + " WHERE id = ?";
PreparedStatement st = conn.prepareStatement(sql);
st.setInt(1, objectId.intValue());
ResultSet rs = st.executeQuery();
while (rs.next()) {
emp = new Emp();
emp.setId(rs.getInt("id"));
emp.setLastName(rs.getString("lname"));
emp.setFirstName(rs.getString("fname"));
emp.setMiddleName(rs.getString("mname"));
emp.setHireDate(rs.getDate("d_hire"));
emp.setFireDate(rs.getDate("d_fire"));
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return emp;
}
