Code example for SQLException

Methods: getErrorCode, getMessage

0
import java.sql.SQLException; 
import java.sql.Statement; 
import java.sql.Timestamp; 
import java.sql.Types; 
import java.text.ParseException; 
import java.text.SimpleDateFormat; 
import java.util.Iterator; 
import java.util.ResourceBundle; 
 
import javax.naming.Context; 
import javax.naming.InitialContext; 
import javax.naming.NamingException; 
import javax.sql.DataSource; 
import javax.sql.rowset.CachedRowSet; 
 
import oracle.jdbc.rowset.OracleCachedRowSet; 
 
import org.apache.log4j.Logger; 
 
import servershell.be.dto.PrepstmtDTO; 
import servershell.be.dto.PrepstmtDTOArray; 
 
import com.ycs.be.dao.C3P0Helper; 
 
/** 
 * This class is used to connect to the database and execute queries. 
 *  
 */ 
public class DBConnector { 
	private Logger logger = Logger.getLogger(DBConnector.class); 
	private static String DRIVERNAME = "oracle.jdbc.driver.OracleDriver";
	private static String DBURL = "jdbc:oracle:thin:@localhost:1521:XE";
	private static String DBUSER = "test";
	private static String DBPASSWORD = "test";
	private boolean isRuninServerContext;
 
	private final String SIMPLE_DATE_FORMAT = PrepstmtDTO.DATE_NS_FORMAT;//"yyyy-MM-dd HH:mm:ss.SSS";
	private final String DATE_TIME = PrepstmtDTO.DATE_TIME_MIN_FORMAT;
	 
	private static boolean poolinitialized = false; 
	 
	public DBConnector() { 
			final ResourceBundle prop = ResourceBundle.getBundle("config");
			DBURL = prop.getString("DBURL");
			DBUSER = prop.getString("DBUSER");
			DBPASSWORD = prop.getString("DBPASSWORD");
			DRIVERNAME = prop.getString("DRIVERNAME");
	} 
 
	private void debug(int priority, String s) {
		if (priority > 0) {
			System.out.println("DBConnecctor:" + s);
		} 
	} 
 
	private void debug(int priority, String s, Throwable e) {
		if (priority > 0) {
			System.out.println("DBConnecctor:" + s);
		} 
	} 
 
	/** 
	 * This function is used to return a connection with the database. 
	 *  
	 * @throws BackendException 
	 *  
	 */ 
	public Connection getConnection() throws BackendException {
 
		Connection conn = null;
		try { 
			String driverName = DRIVERNAME;// "oracle.jdbc.driver.OracleDriver";
			// String url = "jdbc:mysql://localhost:3306/ams"; 
			String url = DBURL;// "jdbc:oracle:thin:@127.0.0.1:1521:XE";
			String userName = DBUSER;
			String password = DBPASSWORD;// "test";
 
			Context initContext = null;
			Context envContext = null;
			try { 
				initContext = new InitialContext();
				envContext = (Context) initContext.lookup("java:/comp/env");
			} catch (NamingException e1) {
				//logger.error("Exception occured in contructing InitialContext"); 
			} 
 
			boolean fallaback = false;
			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){ 
//						MiniConnectionPoolHelper.setUp(); 
//						poolinitialized = true; 
//					} 
//					conn =  MiniConnectionPoolHelper.getConnection(); 
//				}catch(Exception e){ 
//					logger.error("MiniConnectionPoolHelper connection error",e); 
//				} 
				try{ 
					if(!poolinitialized){
						C3P0Helper.setUp(); 
						poolinitialized = true;
					} 
					conn =  C3P0Helper.getConnection();
				}catch(Exception e){
					logger.error("MiniConnectionPoolHelper 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);
			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); 
				} 
			} 
		} 
		return retval;