I don't know if it's a GOOD example, but here's some code from a class that I'm working on to do this. See below. -----Original Message----- From: Jonas Devries [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 16, 2001 3:00 AM To: [EMAIL PROTECTED] Subject: Hi, I'm trying to make a JSP file that sends a SQL instruction to a bean! In that bean I want to acces the DB! The bean has to send back a result (ea. a select instruction)! What is the best way to do this? Someone may have a good example? Thx Jonas ------------------------------------------------------------------ I'm using this class in another bean, but it was designed with a no-argument constructor so it can be used as a bean also. You would want to change the "query()" method to a getATTRIB method for even more bean-like behavior: Db dbHandle = new Db(); dbHandle.setDbNickname("Guinness"); ResultSet rs = dbHandle.query("SELECT * from MYTABLE WHERE This = 'that'"); Note -- this code should be surrounded with the usual try/catch for exceptions. And here's the class Db ------------------------------------------------------------------ // Db Class // Written JE VanOver <[EMAIL PROTECTED]>, UI Dev // Copyright 2001 package util; import java.util.Properties; import java.sql.ResultSet; import java.sql.Connection; import java.sql.Statement; import java.sql.SQLException; import java.sql.DriverManager; import java.io.InputStream; import java.io.IOException; /** General purpose class to shorten and simplify database access for simple JSP projects * */ public class Db { /** dbname@sqlservername * */ String dsn; /** weblogic.jdbc.mssqlserver4.Driver; * */ String dbDriverName; /** jdbc:weblogic:mssqlserver4 * */ String dbServer; /** server+dsn * */ String dbConnectionURL ; /** Database username. Read-only access OK. * */ String username; /** Database password * */ String password; /** Nickname for a data source. This is used to identify fields in ".properties" file related to a particular datasource. * */ String nickname = null; /** All String fields initialized to null. * When user calls setNickname(), the rest of the fields are read from the .properties file. * * @exception java.lang.Exception */ public Db() throws java.lang.Exception { init(); } public String toString() { StringBuffer tmpStr = new StringBuffer(); tmpStr.append("Nickname: "+nickname+", "); tmpStr.append("username: "+username+", "); tmpStr.append("password: "+password+", "); tmpStr.append("dsn: "+dsn+", "); tmpStr.append("dbDriverName: "+dbDriverName+", "); tmpStr.append("dbServer: "+dbServer+", "); tmpStr.append("dbConnectionURL: "+dbConnectionURL+"\n"); return tmpStr.toString(); } /** If nickname is null, initialize all member variables to null. * If nickname is NOT null, read all fields related to this nickname from properties file * * @exception java.lang.Exception */ public void init() throws java.lang.Exception { if (nickname != null) { Properties props = readProperties(); if (props == null) { System.out.println("Error reading properties "); return; } dsn = props.getProperty("dsn"+nickname); username = props.getProperty("username"+nickname); password = props.getProperty("password"+nickname); dbDriverName = props.getProperty("dbDriverName"+nickname); dbServer = props.getProperty("dbServer"+nickname); dbConnectionURL = dbServer+dsn; } else { // nickname == null dsn = null; username = null; password = null; dbDriverName = null; dbServer = null; } } /** Set nickname to given string, and then call init() to read all fields related to this nickname from properties file * * @param tNickname A short text description of a database. This must match * as part of the field names in the db.properties file for all data related * to this database. * @exception java.lang.Exception */ public void setDbNickname(String tNickname) throws java.lang.Exception { nickname = tNickname; init(); } /** If all fields required to connect to a database are in place, then it "isReady" * * @return Returns true if all fields needed to connect to a database are * available (fields are not null) */ public boolean isReady() { return (dsn != null && username != null && password != null && dbDriverName != null && dbServer != null ) ; } public ResultSet query(StringBuffer sqlStatement) throws Exception { System.out.println("Begin Db.query() with StringBuffer"); return query(sqlStatement.toString()); } /** Send a SQL Query to a database and return a ResultSet. All database connection fields must be in place before this can be called * * @param sqlStatement String containing a SQL SELECT statement expected to * return one or more results. * @return Returns null if database connection can't be made or if SQL * statement provoked an error. */ public ResultSet query(String sqlStatement) throws Exception { System.out.println("Begin Db.query() with String"); if (! isReady()) { throw new Exception("Database property values not available: "); } // \n"+this.toString()); System.out.println("In Db-executeSQLcheck: DSN = \""+dsn+"\", Username = \""+username+"\""); Statement dbStatement = null; Connection dbConnection = null; ResultSet dbResultSet = null; System.out.println("sqlStatement = \""+sqlStatement+"\""); try { dbConnection = connect(); if (dbConnection == null) { System.out.println("DB Connection not established to "+dsn); return null; } System.out.println("DB Connection established to "+dsn); dbStatement = dbConnection.createStatement(); // System.out.println("Statement Created in "+dsn); dbResultSet = dbStatement.executeQuery(sqlStatement); // System.out.println("Statement Executed in "+dsn); return dbResultSet; } // end try catch (SQLException sqlex) { System.out.println("SQL exception in createResultSet"+sqlex.toString()); System.out.println(sqlex); sqlex.printStackTrace(); return null; } catch (Throwable e){ System.out.println("Catch throwable in createResultSet"); System.out.println(e); e.printStackTrace(); return null; } // end catch } /** Perform database connection with current attributes and return Connection object. * * @return return a jdbc connection or null */ private Connection connect() { System.out.println("RealTotalsBean - CONNECT DSN = \""+dbConnectionURL+"\", username = \""+username+"\""); try { Class.forName(dbDriverName); return DriverManager.getConnection(dbConnectionURL,username,password); } catch (Exception e){ System.out.println("ERROR -- Exception in main try block of connect"); System.out.println(e); e.printStackTrace(); return null; } // end catch } /** Open db.properties file. This file must be in the top directory of the application. * For example, ...\WebApps\myApp\db.properties * * @return props object with all data read from db.properties */ private Properties readProperties() { System.out.println("START METHOD readProperties -- Db"); Properties props = new Properties(); try { InputStream in = this.getClass().getResourceAsStream("../../../db.properties"); props.load(in); } catch (IOException e) { System.out.println("Cannot open db.properties file"); System.out.println(e); e.printStackTrace(); System.out.println("END METHOD readProperties -- Db"); return null; } // System.out.println("END METHOD readProperties -- Db"); return props; } } /* end Db */ =========================================================================== To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST". For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST". Some relevant FAQs on JSP/Servlets can be found at: http://java.sun.com/products/jsp/faq.html http://www.esperanto.org.nz/jsp/jspfaq.html http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
