• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

jboss + database access

 
Ranch Hand
Posts: 174
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using Jboss 2.4.3 integrated with Tomcat 3.2.3
I have a simple stateless session bean. I am using my web browser to access the Servlet which in turn accesses the BEAN and returns the result back to the Browser.
I now want the SESSION BEAN to access a database directly and then return the extracted value to the browser.
Can anyone provide some simple code which can explain this basic interaction ?
 
Ranch Hand
Posts: 313
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Give the following a try. Note: I cut and pasted bits and pieces out of some code from a project of mine and modified it. I did not compile it again or run it to check it out, but it should help you get the idea even if it doesn't run the first time.
You'll need to update some of the variable (i.e. database connect string, userid, password, sql statement, etc...)
This gives you a generic routine to create a connection, a generic routine to dump a resultset out into a simplistic HTML table and some code executing a query.
Regards,
Byron Estes

{Code follows...}
String db_jdbcdriver = "sun.jdbc.odbc.JdbcOdbcDriver"; // or whatever driver you are using...
java.sql.Connection dbc = null;
java.sql.Statement stmt = null;
java.sql.ResultSet rs = null;
String db = "jdbc dbc bname"; // Put the connect String here that convforms to your driver. If you are using the JdbcOdbcDriver, the last "node" should be your database name.
String userid = "userid"; // Put the userid for the database here
String password = "password"; // Put the password for the database here
String sql = "Select * from tablename;"; // Put your sql statement here.
try {

dbc = getConnection(db_jdbcdriver,
db ,
userid,
password);

stmt = dbc.createStatement();
rs = stmt.executeQuery(sql);
dumpAnyResultSetToTable(rs, "Result Set Dump");

} catch (java.sql.SQLException sqle) {

System.out.println("Error querying database...");

} finally {

try {
// Clean up resources
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(dbc != null) dbc.close();
} catch (Exception e) {

System.out.println("Error cleaning up database connection resources...");
}

}

/**
* Utility method to handle creating a database connection.
*
* Creation date: (4/11/00 1:35:48 PM)
* @return java.sql.Connection
*/
public java.sql.Connection getConnection(String DBdriver, String url, String userid, String password) {
try{
Class.forName(DBdriver);
//DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
java.sql.Connection conn = java.sql.DriverManager.getConnection(url, userid, password);
return conn;
}catch(java.sql.SQLException se){
System.err.println(renderSQLE(se));
return null;
}catch(ClassNotFoundException ce){
System.err.println(ce.getMessage());
return null;
}
}
public String dumpAnyResultSetToTable(java.sql.ResultSet rs, String title) throws java.sql.SQLException {

StringBuffer sb = new StringBuffer();
sb.append("<TABLE border=1>");

java.sql.ResultSetMetaData rsmd = rs.getMetaData();
final int cols = rsmd.getColumnCount();
sb.append("<tr><td colspan=");
sb.append(cols + ">");
sb.append(title);
sb.append("</td></tr>");
// Load labels and column names...
// Element 0 will not be used to simplify code.
String[] labels = new String[cols + 1];
String[] names = new String[cols + 1];

for (int i = 1; i <= cols; i++) {
labels[i] = rsmd.getColumnLabel(i);
names[i] = rsmd.getColumnName(i);
}

// Build table header
sb.append("<tr>");
for (int i = 1; i <= cols; i++) {

sb.append("<th>");
sb.append(labels[i]);
sb.append("</th>");

}
sb.append("</tr>");

// Build the table data
while (rs.next()) {

sb.append("<tr>");

for (int i = 1; i <= cols; i++) {

sb.append("<td>");
sb.append(rs.getString(names[i]));
sb.append("</td>");
}

sb.append("</tr>");
}

sb.append("</table>");

return sb.toString();

}
 
Byron Estes
Ranch Hand
Posts: 313
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The emoticons made the database connect String look humorous in my previous post.
The following is how it should have looked.
I forgot to put it in
Later,
Byron Estes
 
Byron Estes
Ranch Hand
Posts: 313
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok....
That didn't work either.
The first emoticon replaced the characters colon and "o".
The second emoticon replaced the characters colon and "d".
Byron Estes
 
Arijit Ghosh
Ranch Hand
Posts: 174
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Byron.. I could get the thing up and successfully retrieve data from the database.
Now I am going to use Session Bean to Entity Bean to Database AND also try Session Bean to JDO to Database.
Any pointers for each of the above methods ?
 
Arijit Ghosh
Ranch Hand
Posts: 174
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Any example for integrating JDO with JBoss ?
 
Byron Estes
Ranch Hand
Posts: 313
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Not really, entity beans are pretty simple once you've done a couple and the tricky parts (if there are any) are more related to the exact use of the containers tools to generate the rmi stubs/skeletons and mapping the data to the database correctly in the descriptor. I use WSAD (WebSphere Application Developer). If you use that IDE and run into trouble, give me a shout and I'll try to help you through it. I'm not really that familiar with any other IDE's.
Sorry, but I've never used JDO. I consider myself to be pretty good with JDBC and SQL in general. I love finding creative ways to use the metadata from the database JDBC calls to automatically generate sql, stored procedures, etc.
Regards,
Byron Estes
Regards,
 
reply
    Bookmark Topic Watch Topic
  • New Topic