Here is a bean that I use in my JSP pages. It uses the Oracle "thin" driver. (The driver came with our oracle installation).
note: the close() method is for closing the prepared statement and connection after using the resultset returned from the select() method.
<dbaccessbean>
import java.awt.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.driver.*;
/** the dbAccessBean class allows the developer to access
*the database without having to rewrite the connection strings mutiple times.
*@author matt goss
*/
public class dbAccessBean extends java.lang.Object
{
public dbAccessBean()
{
}
/** sets the current driver being used
*@param driver default=sun.jdbc.odbc.JdbcOdbcDriver
*/
public void setDriver(java.lang.String driver)
{
this.driver = driver;
}
/** gets the current driver being used
*@return the current driver
*/
public java.lang.String getDriver()
{
return this.driver;
}
/** sets the current username for the database
*@param username for the database
*/
public void setUsername(java.lang.String username)
{
this.username = username;
}
/** gets the current username for the database
*@return the current username
*/
public java.lang.String getUsername()
{
return this.username;
}
/** sets the current password for the database
*@param password for the database
*/
public void setPassword(java.lang.String password)
{
this.password = password;
}
/** gets the current username for the database
*@return the current password
*/
public java.lang.String getPassword()
{
return this.password;
}
/** sets the connect string for the database
*@param the connect string for the database
*/
public void setConnectString(java.lang.String connectString)
{
this.connectString = connectString;
}
/** gets the current connect string for the database
*@return the current connect string
*/
public java.lang.String getConnectString()
{
return this.connectString;
}
/** builds and executes an insert statement
*@param table the tablename to be accessed
*@param column[] an array containing the column names for the table
*@param value[] an array containing the data values to be inserted
*/
public void Insert( String table , String[] column, String[] value) throws java.sql.SQLException
{
String sqlString = "INSERT INTO " + table + " ( ";
String S1 = "";
String S2 = "";
for (int x = 0; x < column.length ; x++){
if (x == (column.length - 1)){
S1 += column[x];
S2 += "?";
}else {
S1 += column[x] + ", ";
S2 += "?, ";
}
}
sqlString += S1 + " ) values ( " + S2 + " )";
try {
Class.forName(this.driver).newInstance();
c = java.sql.DriverManager.getConnection(this.connectString, this.username, this.password);
ps = c.prepareStatement(sqlString);
for (int x = 0; x < value.length ; x++){
ps.setString( (x + 1), value[x] );
}
ps.execute();
ps.close();
c.close();
}catch (Exception E)
{
throw new java.sql.SQLException(E.toString());
}
}
/** executes an Update statement
*@param sqlString the SQL string to be executed
*/
public void Update( String sqlString) throws java.sql.SQLException
{
try {
Class.forName(this.driver).newInstance();
c = java.sql.DriverManager.getConnection(this.connectString, this.username, this.password);
ps = c.prepareStatement(sqlString);
ps.execute();
ps.close();
c.close();
}catch (Exception E)
{
throw new java.sql.SQLException(E.toString());
}
}
/** executes a Delete statement
*@param sqlString the SQL string to be executed
*/
public void Delete( String sqlString ) throws java.sql.SQLException
{
try {
Class.forName(this.driver).newInstance();
c = java.sql.DriverManager.getConnection(this.connectString, this.username, this.password);
ps = c.prepareStatement(sqlString);
ps.execute();
ps.close();
c.close();
}catch (Exception E)
{
throw new java.sql.SQLException(E.toString());
}
}
/** executes a Select statement
*note: use the close() method to ensure that the preperred statement and connection get closed properly
*@exception java.sql.SQLException if sql error incurred
*@param sqlString the SQL string to be executed
*@return resultset containing the table data
*/
public java.sql.ResultSet Select( String sqlString ) throws java.sql.SQLException
{
try {
Class.forName(this.driver).newInstance();
c = java.sql.DriverManager.getConnection(this.connectString, this.username, this.password);
ps = c.prepareStatement(sqlString);
java.sql.ResultSet rs = ps.executeQuery();
return rs;
}catch (Exception E)
{
throw new java.sql.SQLException(E.toString());
}
}
public void close()
{
try{
if(this.ps != null){
this.ps.close();
}
}catch(Exception E){System.err.println(E.toString());}
finally
{
try{
if(this.c != null){
this.c.close();
}
}catch(Exception E){System.err.println(E.toString());}
}
}
private java.sql.Connection c;
private java.sql.PreparedStatement ps;
protected java.lang.String username = "";
protected java.lang.String connectString = "";
protected java.lang.String driver = "";
protected java.lang.String password = "";
}
</dbaccessbean>
hope this helps,
Matt Goss
Jim Vu wrote:
I'm a newbie to Jrun.
Does anybody have a simple example of how to use jdbc (jdbc to odbc and jdbc
to oracle) in a bean?
any help is appreciated.
------------------------------------------------------------------------------
Archives: http://www.egroups.com/group/jrun-interest/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/jrun_talk
or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
begin:vcard n:Goss;Matt tel;fax:919-657-1501 tel;work:919-657-1432 x-mozilla-html:FALSE url:www.rtci.com org:RTCI;Custom Solutions adr:;;201 Shannon Oaks Circle;Cary;NC;27511;US version:2.1 email;internet:[EMAIL PROTECTED] title:Web Developer fn:Matt end:vcard
