/** Creates a Pooled connection and adds it to the connection pool. */
private void installConnection() throws EmanagerDatabaseException {
logger.debug("enter");
PooledConnection connection;
try {
connection = poolDataSource.getPooledConnection();
connection.addConnectionEventListener(this);
connection.getConnection().setAutoCommit(false);
synchronized (connectionPool) {
connectionPool.add(connection);
logger.debug("Database connection added.");
}
} catch (SQLException ex) {
logger.fatal("exception caught while obtaining database " + "connection: ex = " + ex);
SQLException ex1 = ex.getNextException();
while (ex1 != null) {
logger.fatal("chained sql exception ex1 = " + ex1);
SQLException nextEx = ex1.getNextException();
ex1 = nextEx;
}
String logString;
EmanagerDatabaseException ede;
logString =
EmanagerDatabaseStatusCode.DatabaseConnectionFailure.getStatusCodeDescription()
+ ex.getMessage();
logger.fatal(logString);
ede =
new EmanagerDatabaseException(
EmanagerDatabaseStatusCode.DatabaseConnectionFailure, logString);
throw ede;
}
}
/**
* Gets the ConnectionID attribute of the SybaseConnector class
*
* @param connection
* @return The ConnectionID value
*/
public static long getConnectionID(Connection connection) {
try {
Statement stmt = connection.createStatement();
ResultSet res = stmt.executeQuery("select connection_property('Number')");
res.next();
return res.getLong(1);
} catch (SQLException ex) {
logger.error("SQL exception retrieving connection ID:" + ex.getMessage());
}
return InvalidConnectionId;
}
/**
* Maps attributes of an SQL structured type that are not serialized to a serialized form, using
* the given type map for custom mapping when appropriate. The following types in the Java
* programming language are mapped to their serialized forms: <code>Struct</code>, <code>SQLData
* </code>, <code>Ref</code>, <code>Blob</code>, <code>Clob</code>, and <code>Array</code>.
*
* <p>This method is called internally and is not used by an application programmer.
*
* @param map a <code>java.util.Map</code> object in which each entry consists of 1) a <code>
* String</code> object giving the fully qualified name of a UDT and 2) the <code>Class</code>
* object for the <code>SQLData</code> implementation that defines how the UDT is to be mapped
* @throws SerialException if an error occurs
*/
private void mapToSerial(Map map) throws SerialException {
try {
for (int i = 0; i < attribs.length; i++) {
if (attribs[i] instanceof Struct) {
attribs[i] = new SerialStruct((Struct) attribs[i], map);
} else if (attribs[i] instanceof SQLData) {
attribs[i] = new SerialStruct((SQLData) attribs[i], map);
} else if (attribs[i] instanceof Blob) {
attribs[i] = new SerialBlob((Blob) attribs[i]);
} else if (attribs[i] instanceof Clob) {
attribs[i] = new SerialClob((Clob) attribs[i]);
} else if (attribs[i] instanceof Ref) {
attribs[i] = new SerialRef((Ref) attribs[i]);
} else if (attribs[i] instanceof java.sql.Array) {
attribs[i] = new SerialArray((java.sql.Array) attribs[i], map);
}
}
} catch (SQLException e) {
throw new SerialException(e.getMessage());
}
return;
}
/**
* Constructs a <code>SerialStruct</code> object from the given <code>SQLData</code> object, using
* the given type map to custom map it to a class in the Java programming language. The type map
* gives the SQL type and the class to which it is mapped. The <code>SQLData</code> object defines
* the class to which the SQL type will be mapped.
*
* @param in an instance of the <code>SQLData</code> class that defines the mapping of the SQL
* structured type to one or more objects in the Java programming language
* @param map a <code>java.util.Map</code> object in which each entry consists of 1) a <code>
* String</code> object giving the fully qualified name of a UDT and 2) the <code>Class</code>
* object for the <code>SQLData</code> implementation that defines how the UDT is to be mapped
* @throws SerialException if an error occurs
*/
public SerialStruct(SQLData in, Map<String, Class<?>> map) throws SerialException {
try {
// set the type name
SQLTypeName = new String(in.getSQLTypeName());
Vector tmp = new Vector();
in.writeSQL(new SQLOutputImpl(tmp, map));
attribs = tmp.toArray();
} catch (SQLException e) {
throw new SerialException(e.getMessage());
}
}
public static Connection getConnect() {
try {
/*if (!getConnect) {
if (!con.isValid(1)) {*/
String host = "jdbc:mysql://localhost:3306/2761DB";
String uname = "root";
String upassword = "******";
con = DriverManager.getConnection(host, uname, upassword);
getConnect = true;
/*}
}*/
} catch (SQLException err) {
System.out.println(err.getMessage());
}
return con;
}
/**
* Constructs a <code>SerialStruct</code> object from the given <code>Struct</code> object, using
* the given <code>java.util.Map</code> object for custom mapping the SQL structured type or any
* of its attributes that are SQL structured types.
*
* @param map a <code>java.util.Map</code> object in which each entry consists of 1) a <code>
* String</code> object giving the fully qualified name of a UDT and 2) the <code>Class</code>
* object for the <code>SQLData</code> implementation that defines how the UDT is to be mapped
* @throws SerialException if an error occurs
* @see java.sql.Struct
*/
public SerialStruct(Struct in, Map<String, Class<?>> map) throws SerialException {
try {
// get the type name
SQLTypeName = new String(in.getSQLTypeName());
System.out.println("SQLTypeName: " + SQLTypeName);
// get the attributes of the struct
attribs = in.getAttributes(map);
/*
* the array may contain further Structs
* and/or classes that have been mapped,
* other types that we have to serialize
*/
mapToSerial(map);
} catch (SQLException e) {
throw new SerialException(e.getMessage());
}
}
public static List<Movie> getAllMovies() {
String query = "SELECT * FROM movie";
List<Movie> movies = new LinkedList<>();
try (Connection connection = Config.getConnection(); // step 1
Statement statement = connection.createStatement(); // step 2
ResultSet result = statement.executeQuery(query); ) { // step 3 and 4
while (result.next()) { // step 5
Movie movie = new Movie();
// you should be validating the following,
// this is just an example to get you started
movie.setName(result.getString(1));
movie.setYear(result.getDate(2).getYear());
movie.setUrl(result.getString(3));
movies.add(movie);
}
} catch (SQLException e) {
System.err.println(e.getMessage());
System.err.println(e.getStackTrace());
}
return movies;
}
protected boolean executeOnlyIf(Connection con, String q) throws SQLException {
if (q == null) return true;
Statement stmt = null;
try {
stmt = con.createStatement();
q = q.replace("$PREFIX", getPrefix());
LOG.debug(" Executing query " + q);
ResultSet rs = stmt.executeQuery(q);
rs.next();
boolean res = rs.getBoolean(1);
LOG.debug("Result: " + res);
return res;
} catch (SQLException sqe) {
LOG.error(sqe.getMessage() + " from " + q);
throw sqe;
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (Exception g) {
}
}
}
/**
* @return Connection
* @roseuid 3F3A5FFD0338
*/
public Connection getConnection() throws EmanagerDatabaseException {
long connectionId;
Connection connection;
PooledConnection pooledConnection;
connection = null;
pooledConnection = null;
connectionId = InvalidConnectionId;
try {
synchronized (connectionPool) {
if (!connectionPool.isEmpty()) {
try {
boolean connectionClosed;
connectionClosed = false;
pooledConnection = (PooledConnection) connectionPool.remove(0);
connection = pooledConnection.getConnection();
connection.clearWarnings();
connectionId = getConnectionID(connection);
connectionClosed = connection.isClosed();
if (connectionId == InvalidConnectionId || connectionClosed == true) {
logger.debug("Pooled connection closed.");
connection = null;
}
} catch (SQLException sqe) {
logger.debug("Pooled connection closed.");
connection = null;
}
}
}
if (connection == null) {
logger.debug("Getting a new connection.");
pooledConnection = poolDataSource.getPooledConnection();
pooledConnection.addConnectionEventListener(this);
connection = pooledConnection.getConnection();
connection.clearWarnings();
connectionId = getConnectionID(connection);
}
} catch (SQLException sqe) {
String logString;
EmanagerDatabaseException ede;
logString =
EmanagerDatabaseStatusCode.UnableToGetPooledConnection.getStatusCodeDescription()
+ sqe.getMessage();
logger.error(logString);
ede =
new EmanagerDatabaseException(
EmanagerDatabaseStatusCode.UnableToGetPooledConnection, logString);
throw ede;
}
if (connectionId == InvalidConnectionId) {
EmanagerDatabaseException ede;
ede =
new EmanagerDatabaseException(
EmanagerDatabaseStatusCode.UnableToGetPooledConnection,
EmanagerDatabaseStatusCode.UnableToGetPooledConnection.getStatusCodeDescription());
throw ede;
}
logger.debug(
"\n*****************************"
+ "\nPooled Connection Init"
+ "\nCon ID:"
+ connectionId
+ "\nCon Object:"
+ pooledConnection
+ "\nPool Object:"
+ connection
+ "\n*****************************");
return connection;
}
/**
* This method searches the table STAGE_USAC_FORM for the distinctfilename provided for the usac
* refrence number given
*
* @param Stringusac refrence number
* @return Vector containing the Result Set
*/
public Vector selectFileName(String rfrnc_nmbr) {
// the buffer for the query
StringBuffer sbQuery = null;
Vector rsVector = new Vector();
PreparedStatement psStmt = null;
ResultSet rsResult = null;
try {
// init the buffer
sbQuery = new StringBuffer("");
// build the query
sbQuery.append(buildSelectFileName());
sbQuery.append(" WHERE ");
sbQuery.append(" rfrnc_nmbr = ?");
// sbQuery.append( rfrnc_nmbr );
// sbQuery.append("'");
USFEnv.getLog()
.writeDebug(
"Get FileName by USAC Ref Number is: " + sbQuery.toString() + "\n", this, null);
// execute the query
psStmt = cConn.prepareStatement(sbQuery.toString());
psStmt.setString(1, rfrnc_nmbr);
rsResult = psStmt.executeQuery();
if (rsResult != null) {
while (rsResult.next()) {
rsVector.addElement(rsResult.getString("FILENAME"));
USFEnv.getLog()
.writeDebug(" THE FILENAME IS : " + rsResult.getString("FILENAME"), this, null);
}
}
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (SQLException e) {
try {
// close the statment and the ResultSet
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (SQLException ex) {
USFEnv.getLog()
.writeCrit("selectSQLCall(): Fail to close result set or prepare statement", this, ex);
}
USFEnv.getLog()
.writeCrit(
" SQL Exception : SQL Error message "
+ e.getMessage()
+ " with Query: \n"
+ sbQuery.toString(),
this,
e);
} catch (Exception e) {
try {
// close the statment and the ResultSet
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (SQLException ex) {
USFEnv.getLog()
.writeCrit("selectSQLCall(): Fail to close result set or prepare statement", this, ex);
}
USFEnv.getLog()
.writeCrit(
"Error Executing the Query(): Error executing query " + e.getMessage(), this, e);
}
/* try
{
// close the statment and the ResultSet
if (rsResult != null)
rsResult.close();
if (psStmt != null)
psStmt.close();
}
catch (SQLException e)
{
USFEnv.getLog().writeCrit("selectSQLCall(): Fail to close result set or prepare statement",this, e);
}*/
return rsVector;
}
