HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

"Adding a Middle Tier To Your Java Code Using
 Jaguar CTS Part 2 Server side Coding"
Vol. 3, Issue 6, P.34

	

Listing 1: Variable initialization.

final String  DB_USER    = "pubs2_user";     // Database user name.
final String  DB_PWD     = "pubs2_user";     // Database user password.
final String  DATASERVER = "jdbc:sybase:Tds:89.86.200.1:5000/pubs2"; 
// URL to the dataserver. 

// other variables
String sql;
static Connection con = null;
static Statement stmt = null;
static ResultSet rs   = null;
JCMCache _cache = null;

Listing 2: Constructor.

public Pubs2CompImpl() throws JException 
{
    try 
    {   // Get a cache reference.
      _cache = JCM.getCache(DB_USER, DB_PWD, DATASERVER); 
    }
    catch (Exception e) 
    {
      Jaguar.writeLog(true, "Pubs2CompImpl constructor getCache(): " +
e.getMessage());
      _cache = null;
    }

    /*
    ** If we can't get a handle to the connection cache, log an error and throw 
    ** a Jaguar exception. The JException gets caught by the component stub class,
    ** which in this case is Pubs2Comp.class. 
    */
    if (_cache == null) 
   {
      Jaguar.writeLog(true, "Pubs2CompImpl(): Could not get connection cache reference.");
      throw new JException("Pubs2CompImpl constructor: Could not create connection 
cache.");
    }
  }

Listing 3: The qt function.

public String qt(String str) 
{
    return("\"" + str + "\""); // Enclose the string in quotes.
}

Listing 4: The doQuery function.

private ResultSet doQuery(String sql, Statement stmt, Connection con) throws JException 
{
    int rows;

    try {
      rows = stmt.executeUpdate("set quoted_identifier off");
      boolean results = stmt.execute(sql);
      rs = stmt.getResultSet();
    }
    catch (SQLException sqle) {
      Jaguar.writeLog(true, "doQuery(): " + sqle.getMessage());
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "doQuery(): " + e.getMessage());
    }
    return rs;
}

Listing 5: The selAuthors Function.

public void selAuthors() throws JException 
{
    sql =       "SELECT * "; 
    sql = sql + "FROM authors ";
    sql = sql + "ORDER by au_lname";

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); 
      ResultSet rs = doQuery(sql, stmt, con);      
      JContext.forwardResultSet(rs);       
      stmt.close();
      _cache.releaseConnection(con);       
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "selAuthors(): " + e.getMessage());
    }
 }

Listing 6: The selAuthorsByLname function.

public void selAuthorsByLname(String au_lname) throws JException {

    // Build the sql statement and put strings in double quotes.
    sql =       "SELECT * "; 
    sql = sql + "FROM authors ";
    sql = sql + "WHERE au_lname = " + qt(au_lname) + " ";
    sql = sql + "ORDER by au_lname";

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); // Create a connection statement.
      ResultSet rs = doQuery(sql, stmt, con); // This returns a result set so call doQuery().
      JContext.forwardResultSet(rs);          // Forward the result sets to the client.
      stmt.close();                           // Release all statement resources.
      _cache.releaseConnection(con);          // Release the connection back into the pool.
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "selAuthorsByLname(): " + e.getMessage());
    }
  }

Listing 7: The insAuthors function.

public void insAuthor(String au_id, String au_lname, String au_fname,
                        String phone, String address,  String city,
                        String state, String country,  String postal_code) throws JException {

    sql = "INSERT INTO authors VALUES(";
    sql = sql + qt(au_id) + "," + qt(au_lname) + "," + qt(au_fname) + ",";
    sql = sql + qt(phone) + "," + qt(address)  + "," + qt(city) + ",";
    sql = sql + qt(state) + "," + qt(country)  + "," + qt(postal_code) + ")";

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); 
      doActionQuery(sql, stmt, con);
      stmt.close();
      _cache.releaseConnection(con);
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "insAuthor(): " + e.getMessage());
    }
  }

Listing 8: The delAuthors Function.

public void delAuthor(String au_id) throws JException {

    sql =       "DELETE ";
    sql = sql + "FROM authors ";
    sql = sql + "WHERE au_id = " + qt(au_id);

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); 
      doActionQuery(sql, stmt, con);
      stmt.close();
      _cache.releaseConnection(con);
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "delAuthor(): " + e.getMessage());
    }
  }

Listing 9: The updAuthors function.

public void updAuthor(String au_id, String au_lname, String au_fname,
                        String phone, String address,  String city,
                        String state, String country,  String postal_code) throws JException {

    sql =       "UPDATE authors ";
    sql = sql + "SET au_lname    = " + qt(au_lname) + ",";
    sql = sql + "    au_fname    = " + qt(au_fname) + ","; 
    sql = sql + "    phone       = " + qt(phone)    + ",";
    sql = sql + "    address     = " + qt(address)  + ",";
    sql = sql + "    city        = " + qt(city)     + ",";
    sql = sql + "    state       = " + qt(state)    + ",";
    sql = sql + "    country     = " + qt(country)  + ",";
    sql = sql + "    postalcode  = " + qt(postal_code);
    sql = sql + "WHERE au_id = " + qt(au_id); 

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); 
      doActionQuery(sql, stmt, con);
      stmt.close();
      _cache.releaseConnection(con);
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "updAuthor(): " + e.getMessage());
    }
  }

Listing 10: The doActionQuery function.

private void doActionQuery(String sql, Statement stmt, Connection con) throws JException {

    int rows = 0;

    try {
      rows = stmt.executeUpdate("set quoted_identifier off");
      rows = stmt.executeUpdate(sql.toString());
      rows = stmt.executeUpdate("commit");
    }
    catch (SQLException sqle) {
      Jaguar.writeLog(true, "doActionQuery(): " + sqle.getMessage());
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "doActionQuery(): " + e.getMessage());
    }
  }
  
      
 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.