/*
* @author : Pushkala
* @version : 1.0
*
* Development Environment : Oracle9i JDeveloper
* Name of the File : JDBCQueryBean.java
*
* Creation / Modification History
* Pushkala 25-Jan-2003 Created
*
*/
package oracle.otnsamples.cmsxdb.useraction;
import java.util.Hashtable;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Types;
import java.io.Writer;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import oracle.xdb.XMLType;
import oracle.otnsamples.cmsxdb.ConnParams;
import oracle.otnsamples.cmsxdb.admin.CMSAdminBean;
import oracle.otnsamples.cmsxdb.exception.CMSAccessException;
/**
* This class handles the JDBC operations of the XML Content Management
* application. This class handles the following tasks,
*
* -- Create database connection,
* -- New User creation and Validation of User
* -- Enqueue Externalization Requests to the Queue
* -- Retrieve Personalization Information of the User
* -- Handle create resource and update resource
*
*/
public class JDBCQueryBean {
// Datasource object
private static DataSource datasource = null;
/**
* Empty Default Constructor.
*/
public JDBCQueryBean() {
}
/**
* This method initializes the datasource and retrieves connection to
* the datasource using input username and password values and returns
* the database connection object obtained.
*
* @param username username
* @param password password
*
* @return Connection Connection to the datasource
*
* @exception CMSAccessException if lookup or connecting to datasource fails
*
*/
public static Connection getConnection(String username, String password)
throws CMSAccessException {
// Database Connection object
Connection connection = null;
try {
if ( datasource == null ) {
// Initialize datasource
datasource = ( DataSource ) new InitialContext( )
.lookup( ConnParams.datasourceName );
}
// Retrieve a connection from datasource
connection = datasource.getConnection( username, password );
} catch ( NamingException nameEx ) {
throw new CMSAccessException( "Error during DataSource lookup : " +
nameEx.toString( ) );
} catch ( SQLException sqlEx ) {
throw new CMSAccessException( "Error Connecting to Datasource : " +
sqlEx.toString( ) );
}
return connection;
}
/**
* This method checks for the validity of the logged in user and
* returns the corresponding success message or error message.
*
* @param username username
* @param password password
*
* @return String indicating whether the user is valid or not
*
* @exception CMSAccessException if lookup or connecting to datasource fails
*
*/
public static String validUser(String username, String password)
throws CMSAccessException {
String retMessage = "Connection failed";
Connection connection = null;
try {
connection = getConnection( username, password );
if ( connection != null ) {
// Getting a valid connection indicates that the username and password
// are valid, hence return the success message and close the connection
retMessage = "SUCCESS";
connection.close();
}
} catch( CMSAccessException accEx ) {
String exMsg = accEx.toString();
if ( exMsg.indexOf("ORA-01017: invalid username/password") > -1 ) {
retMessage = "Incorrect username/password specified";
} else {
throw accEx;
}
} catch( SQLException sqlEx ) {
throw new CMSAccessException("SQLException in validUser method " +
"of JDBCQueryBean " + sqlEx.toString());
}
return retMessage;
}
/**
* This method checks if the user already exists. If the user does not exist,
* creates a New User and returns the success message, Else returns the
* message that the User already exists.
*
* @param username username
* @param password password
*
* @return String indicating new user creation success message
* or corresponding error message
*
* @exception CMSAccessException if connection fails or
* new user creation fails
*
*/
public static String createNewUser(String username, String password)
throws CMSAccessException {
Statement stmt = null;
String retMessage = "FAILURE";
Connection connection = null;
try {
connection = getConnection( ConnParams.dbUsername,
ConnParams.dbPassword );
// Check if the user already exists or not
int userStatus = checkUser(connection, username);
if (userStatus == 0) {
// If the user does not already exist, create the new user and grant
// connect and resource privileges and return the success message
stmt = connection.createStatement();
stmt.execute(" CREATE USER " + username +
" identified by " + password);
stmt.execute("grant connect, resource to " + username);
retMessage = "SUCCESS";
} else if (userStatus == -1) {
// If the user already exists, return the message
retMessage = "User already exists";
}
} catch(SQLException ex) {
throw new CMSAccessException("SQLException in createNewUser method " +
"of JDBCQueryBean " + ex.toString());
} finally {
try {
if ( stmt != null ) stmt.close(); // close the statement
if ( connection != null ) connection.close(); // close the connection
} catch(SQLException ex) {
throw new CMSAccessException("SQLException in createNewUser " +
"method of JDBCQueryBean while " +
"closing connections " + ex.toString());
}
}
return retMessage;
}
/**
* This method calls the PL/SQL package function which checks
* if the user already exists and returns 0 or -1 accordingly
*
* @param Connection Database Connection object
* @param username username
*
* @return int -1 : If the user already exists
* 0 : If the user does not exist
*
* @exception CMSAccessException if executing the CallableStatement fails
*
*/
private static int checkUser(Connection connection, String username)
throws CMSAccessException {
CallableStatement cstmt = null;
int status = 0;
try {
cstmt = connection.prepareCall(
"begin ? := CMSADMIN.otncms_xmlcontent.checkuser(?); end;");
// 1st parameter is the return value
cstmt.registerOutParameter(1, OracleTypes.INTEGER);
// Bind 2nd parameter
cstmt.setString(2, username);
// Execute the callable statement
cstmt.execute();
// Get the return status
status = cstmt.getInt(1);
} catch(SQLException sqlex) { // Trap SQL Errors
throw new CMSAccessException("SQLException in checkUser method " +
"of JDBCQueryBean " + sqlex.toString());
} finally {
try {
if ( cstmt != null ) cstmt.close(); // close the statement
} catch(SQLException ex) {
throw new CMSAccessException("SQLException in checkUser " +
"method of JDBCQueryBean while " +
"closing statement " + ex.toString());
}
}
return status;
}
/**
* This method prepares the XMLType message using the input values
* and calls the methods in the CMSAdminBean to enqueue the message.
*
* @param resourceLoc Resource Location
* @param xslLoc XSL Location
* @param contentType Content Type
*
* @see oracle.otnsamples.cmsxdb.admin.CMSAdminBean
*
* @exception CMSAccessException if getting connection or enqueuing fails
*
*/
private static void enqueue( String resourceLoc, String xslLoc,
String contentType )
throws CMSAccessException {
Connection connection = null;
try {
connection = getConnection( ConnParams.dbUsername,
ConnParams.dbPassword );
// Create the XML Type message
XMLType externreq =
XMLType.createXML(connection,
"<Externalize>"+
"<ResourceURL>"+resourceLoc+"</ResourceURL>"+
"<XSLLocation>"+xslLoc+"</XSLLocation>"+
"<ContentType>"+contentType+"</ContentType>"+
"</Externalize>");
// Instantiate the CMSAdminBean and call the enqueue method
CMSAdminBean admin = new CMSAdminBean();
admin.enqueue("EXTERN_QUEUE", externreq, connection );
} catch(SQLException sqlex) {
throw new CMSAccessException("SQLException in enqueue method " +
"of JDBCQueryBean " + sqlex.toString());
} catch(Exception ex) {
throw new CMSAccessException("Exception in enqueue method " +
"of JDBCQueryBean " + ex.toString());
} finally {
try {
// close the connection
if (connection != null) connection.close();
} catch(SQLException ex) {
throw new CMSAccessException("SQLException in enqueue " +
"method of JDBCQueryBean while " +
"closing connections " + ex.toString());
}
}
}
/**
* This method is called from the JSP to externalize content.
*
* @param username username
* @param password password
* @param abspath resource path
* @param viewtype View Type
*
* @exception CMSAccessException if connection fails or enqueue fails
*
*/
public static void externContent( String username, String password,
String abspath, String viewtype )
throws CMSAccessException {
String xslLoc = "";
String contentType = viewtype;
if ( abspath.toLowerCase().endsWith(".xml") ) {
// If the resource is an XML file, get the User's personalization details
// ie,. the XSL Location and the View Type set by the User
Hashtable hash = getPers(username, password, abspath);
if (hash != null) {
xslLoc = (String) hash.get("XslLoc");
contentType = (String) hash.get("ViewType");
}
}
// Enqueue this information for sending externalization request
enqueue( abspath, xslLoc, contentType) ;
}
/**
* This method queries the personalization details of the User.
*
* @param username username
* @param password password
* @param abspath resource path
*
* @return Hashtable Hashtable containing the personalization details
*
* @exception CMSAccessException if connection fails or query fails
*
*/
public static Hashtable getPers(String username, String password,
String abspath)
throws CMSAccessException {
Hashtable hash = null;
ResultSet rset = null;
PreparedStatement pstmt = null;
String query = " SELECT viewtype, "+
" op.xslloc.getexternalurl() "+
" FROM CMSADMIN.otncms_pers_view op "+
" WHERE UPPER(op.username) = UPPER(?) "+
" AND op.resourceloc.geturl() = ? ";
Connection connection = null;
try {
connection = getConnection( username, password );
// Prepare the statement object with the query
pstmt = connection.prepareStatement(query);
// Bind the parameters
pstmt.setString(1, username);
pstmt.setString(2, abspath);
// Execute the query
rset = pstmt.executeQuery();
if (rset.next()) {
// If the query returns results,
// Get the results and populate the hashtable
hash = new Hashtable();
hash.put("ViewType", rset.getString(1));
String xslloc = rset.getString(2);
// XSL Location can have null values
if (xslloc != null) {
hash.put("XslLoc", xslloc);
}
}
} catch(SQLException sqlex) {
throw new CMSAccessException("SQLException in getPers method " +
"of JDBCQueryBean " + sqlex.toString());
} finally {
try {
// Close the resultset, statement and connection objects
if ( rset != null ) rset.close();
if ( pstmt != null ) pstmt.close();
if ( connection != null ) connection.close();
} catch(SQLException ex) {
throw new CMSAccessException("SQLException in getPers " +
"method of JDBCQueryBean while " +
"closing connections " + ex.toString());
}
}
return hash;
}
/**
* This method is common to createresource and updateresource methods.
* This method takes in the query string of the callablestatement and
* the input parameters to the PL/SQL procedure. The statement is executed
* and the results are returned.
*
* @param conn Database Connection
* @param query Statement Query
* @param param1 first parameter to the query
* @param param2 second parameter to the query
* @param param3 third parameter to the query
*
* @return String Result of the execution of the statement
*
* @exception SQLException if connection fails or processing query fails
*
*/
private static String processQuery( Connection conn,
String query, Object param1,
Object param2, Object param3 )
throws SQLException {
CallableStatement cstmt = null;
String status = "Failed to process the input query";
try {
// Prepare callable Statement to call PL/SQL Stored Procedure
cstmt = conn.prepareCall(query);
// Binds the return value types
cstmt.registerOutParameter(1, Types.VARCHAR);
if ( param3 != null && param3 instanceof BLOB ) {
cstmt.registerOutParameter(4, OracleTypes.BLOB);
} else if ( param3 != null && param3 instanceof CLOB ) {
cstmt.registerOutParameter(4, OracleTypes.CLOB);
}
if (param1 != null) {
cstmt.setObject(2, param1); // Bind parameter
}
if (param2 != null) {
cstmt.setObject(3, param2); // Bind parameter
}
if (param3 != null) {
cstmt.setObject(4, param3); // Bind parameter
}
// Execute the callable statement
cstmt.execute();
// Get return status
status = cstmt.getString(1);
} catch(SQLException ex) { // Trap SQL Errors
throw new SQLException("SQLException in processQuery method " +
"of JDBCQueryBean " + ex.toString());
} finally {
try {
// Close the statement and connection objects
if ( cstmt != null ) cstmt.close();
if ( conn != null ) conn.close();
} catch(SQLException ex) {
throw new SQLException("SQLException in processQuery method " +
"of JDBCQueryBean while closing connections " +
ex.toString());
}
}
return status;
}
/**
* This method is called by DataUtils to create a new resource.
* This method creates the callablestatement query calling the
* PL/SQL package function to create a new resource and then
* calls the processQuery method to execute the query.
*
* @param conn Database Connection
* @param abspath Resource path
* @param resdir Resource Container
* @param data Content of the new resource
*
* @return String Result of the execution of the statement
*
* @exception SQLException if query fails to execute
*
*/
public static String createresource( Connection conn, String abspath,
String resdir, Object data )
throws SQLException {
String query = "begin ? := CMSADMIN.otncms_xmlcontent.createresource(?, ?, ?); end;";
String status = null;
if ( data instanceof String ) {
status = processQuery(conn, query, abspath, resdir,
getCLOB((String)data, conn));
} else if ( data instanceof BLOB ) {
query = " begin ? := CMSADMIN.otncms_xmlcontent.createblobresource( ?, ?, ?); end;";
status = processQuery(conn, query, abspath, resdir, data);
} else {
status = processQuery(conn, query, abspath, resdir, data);
}
return status;
}
/**
* This method is called by DataUtils to update a resource.
* This method creates the callablestatement query calling the
* PL/SQL package function to update a resource and then
* calls the processQuery method to execute the query.
*
* @param conn Database Connection
* @param abspath Resource path
* @param data Content of the resource
*
* @return String Result of the execution of the statement
*
* @exception SQLException if query fails to execute
*
*/
public static String updateresource( Connection conn, String abspath,
String data )
throws SQLException {
String query =
"begin ? := otncms_xmlcontent.updateresource(?, ?); end;";
String status = processQuery(conn, query, abspath,
getCLOB(data, conn), null);
return status;
}
/**
* This method returns a CLOB Object with the specified Data. This CLOB can
* now be used to insert in the database table which has a CLOB column.
* In this sample, this Object is used to create/update XML String resource.
*
* @param clobData Data to be inserted in the CLOB Object
* @param conn Database Connection Object
*
* @return CLOB Object
*
* @exception SQLException if writing to CLOB fails
*/
private static CLOB getCLOB(String clobData, Connection conn)
throws SQLException {
CLOB tempClob = null;
try {
// If the temporary CLOB has not yet been created, create new
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
// Open the temporary CLOB in readwrite mode to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Write the data into the temporary CLOB
tempClobWriter.write(clobData);
// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();
} catch( SQLException sqlexp ) {
throw new SQLException( "SQLException in getCLOB : " +
sqlexp.toString( ) );
} catch( Exception exp ) {
throw new SQLException( "Generic Exception in getCLOB : " +
exp.toString( ) );
}
return tempClob;
}
}
Bookmark
