Code example for Connection

Methods: close, commit, createStatement, isClosed, rollback

0
			if (envContext != null) {
				try { 
					DataSource ds = (DataSource) envContext.lookup("jdbc/myoracle");
					conn = ds.getConnection();
 
					if (conn == null || conn.isClosed()) {
						logger.error("Some thing wrong with connecting with database!");
						throw new SQLException("Some thing wrong with connecting with database using connection pool!");
					} 
					debug(3, "Database connection Running in tomcat datasource pooled mode"); 
					isRuninServerContext = true;
				} catch (SQLException e) {
					fallaback = true;
					debug(3, "Datasource is not configured, Using fallback method of direct connection", e);
				} catch (NamingException e) {
					fallaback = true;
					debug(3, "Datasource is not configured, Using fallback method of direct connection", e);
				} 
			} 
 
			if (envContext == null || fallaback) {
				// /Running in standalone mode 
				//debug(3, "Database connection Running in standalone mode# " + DBURL); 
				// conn = DriverManager.getConnection (url, userName, password); 
//				 conn = JDBCUtils.getConnection(); 
//				 JDBCUtils.listCacheInfos(); 
				 
				try{ 
					if(!poolinitialized){
						C3P0Helper.setUp(); 
						poolinitialized = true;
					} 
					conn =  C3P0Helper.getConnection();
				}catch(Exception e){
					logger.error("c3p0 connection error"+e);
				} 
				 
				isRuninServerContext = false;
				if(conn == null){ 
					Class.forName(driverName);
					conn = DriverManager.getConnection(url, userName, password);
				} 
				 
				if (conn == null) {
					logger.error("Some thing wrong with connecting with database!");
					throw new SQLException("DBConnection is null");
				} 
			} 
			debug(0, "Database connection established"); 
			// CachedRowSet crs; 
 
		} catch (ClassNotFoundException e) {
			logger.error("error.driverclassnameNotfound", e);
			throw new BackendException("error.DBconnectionFail", e);
		} catch (SQLException e) {
			logger.error("error.getdbConnection", e);
			throw new BackendException("error.DBconnectionFail", e);
		} 
 
		return conn;
	} 
 
	/** 
	 * This functions executes a query 
	 *  
	 * @param qry 
	 * @return result 
	 * @throws BackendException 
	 * @throws SQLException 
	 */ 
	public CachedRowSet executeQuery(String qry) throws BackendException {
		CachedRowSet crs;
		Connection conn = null;
		try { 
 
			conn = getConnection();
			Statement stmt = conn.createStatement();
			debug(0, qry);
			ResultSet rs = stmt.executeQuery(qry);
 
			crs = new OracleCachedRowSet();
			crs.populate(rs);
			rs.close();
			stmt.close();
 
		} catch (SQLException e) {
			logger.error("error.sqlstatement:"+qry, e);
			throw new BackendException("error.sqlstatement", e);
		} finally { 
			if (conn != null) {
				try { 
					conn.close();
					conn = null;
					debug(0, "Database connection terminated"); 
				} catch (SQLException e) {
					logger.error("error.closeDBconnection", e);
					// throw new BackendException("error.closeDBconnection", e); 
				} 
			} 
		} 
 
		return crs;
	} 
 
	/** 
	 * This function is used to execute update query. 
	 *  
	 * @param qry 
	 * @return 
	 * @throws SQLException 
	 * @throws BackendException 
	 */ 
	public int executeUpdate(String qry) throws BackendException {
		Connection conn = null;
		int retval = 0;
		try { 
 
			conn = getConnection();
			Statement stmt = conn.createStatement();
			debug(0, qry);
			retval = stmt.executeUpdate(qry);
			logger.debug("Rows updated :"+retval +" for the query "+qry);
			conn.commit();
		} catch (SQLException e) {
			logger.error("error.sqlstatement:"+qry, e);
			try { 
				conn.rollback();
			} catch (SQLException e1) {
				logger.error("Rollback Exception", e);
			} 
			throw new BackendException("error.sqlstatement", e);
		} finally { 
			if (conn != null) {
				try { 
					conn.close();
					// log ("Database connection terminated"); 
				} catch (SQLException e) {
					logger.error("error.closeDBconnection", e);
					// throw new BackendException("error.closeDBconnection", e); 
				}