001 package biz.wedoit4u.databeans;
002
003 import java.io.IOException;
004 import java.io.Writer;
005 import java.util.ArrayList;
006 import java.util.Collection;
007 import java.util.Date;
008 import java.sql.Connection;
009 import java.sql.PreparedStatement;
010 import java.sql.ResultSet;
011 import java.sql.SQLException;
012 import java.sql.Statement;
013 import java.sql.Timestamp;
014
015 import javax.naming.InitialContext;
016 import javax.naming.NamingException;
017 import javax.sql.DataSource;
018
019 import oracle.sql.CLOB;
020 import biz.wedoit4u.Logger;
021
022 /**
023 * A helper class that provides convenient methods to interact with
024 * a backend database. All methods are <code>static</code> and hence
025 * no <code>constructor</code> is provided to initialise this class.
026 *
027 * <p>Copyright 2003, Rakesh Vidyadharan and wedoit4u.biz</p>
028 *
029 * @author Rakesh Vidyadharan 10<sup><small>th</small></sup> September 2003
030 *
031 * @version $Id: DatabaseHelper.java,v 1.3 2004/05/26 11:42:32 rakesh Exp $
032 */
033 public final class DatabaseHelper extends Object
034 {
035 /**
036 * The <code>JNDI</code> location for the <code>DataSource</code>
037 * that represents the <code>wedoit4u</code> database schema.
038 */
039 private static final String WEDOIT4U_JNDI_LOCATION = "jdbc/oracle/wedoit4u";
040
041 /**
042 * A reference to the <code>DataSource</code> to be used to fetch
043 * connections from the application server's context.
044 */
045 private static DataSource dataSource = null;
046
047 /**
048 * Fetch a <code>Connection</code> from the {@link #dataSource}
049 * obtained from the {@link #WEDOIT4U_JNDI_LOCATION} in the
050 * application server context.
051 *
052 * @return Connection - A connection from the data source.
053 * @throws NamingException - If errors are encountered while fetching
054 * the JNDI location from the application server context.
055 * @throws SQLException - If errors are encountered while fetching
056 * a connection from the data source.
057 */
058 public static final Connection getConnection()
059 throws NamingException, SQLException
060 {
061 if ( dataSource == null )
062 {
063 InitialContext initialContext = new InitialContext( System.getProperties() );
064 dataSource = (DataSource) initialContext.lookup( DatabaseHelper.WEDOIT4U_JNDI_LOCATION );
065 }
066
067 return dataSource.getConnection();
068 }
069
070 /**
071 * Return the next value of the specified sequence.
072 *
073 * @param sequence - The name of the sequence whose next value
074 * is to be retrieved.
075 * @return int - The next value of the sequence.
076 * @throws SQLException - If any errors are encountered while fetching
077 * the sequence value.
078 * @throws NamingException - If any errors are encountered while
079 * fetching a reference to the database data source.
080 */
081 public static final int getNextSequenceValue( String sequence )
082 throws SQLException, NamingException
083 {
084 int result = 0;
085 Connection connection = null;
086 Statement statement = null;
087 ResultSet resultSet = null;
088
089 try
090 {
091 connection = DatabaseHelper.getConnection();
092 statement = connection.createStatement();
093 resultSet = statement.executeQuery( "select " + sequence + ".nextval from dual" );
094
095 // There should be one row in result
096 resultSet.next();
097 result = resultSet.getInt( 1 );
098 }
099 finally
100 {
101 try
102 {
103 if ( resultSet != null ) resultSet.close();
104 }
105 catch ( SQLException sex )
106 {
107 // Just log it.
108 Logger.error( "Error while closing ResultSet in WebservicesData.getNextSequenceValue() for sequence " + sequence + ". " + sex.toString() );
109 }
110
111 try
112 {
113 if ( statement != null ) statement.close();
114 }
115 catch ( SQLException sex )
116 {
117 // Just log it.
118 Logger.error( "Error while closing Statement in DatabaseHelper.getNextSequenceValue() for sequence " + sequence + ". " + sex.toString() );
119 }
120
121 try
122 {
123 if ( connection != null ) connection.close();
124 }
125 catch ( SQLException sex )
126 {
127 // Just log it.
128 Logger.error( "Error while closing Connection in DatabaseHelper.getNextSequenceValue() for sequence " + sequence + ". " + sex.toString() );
129 }
130 }
131
132 return result;
133 }
134
135 /**
136 * Update the specified <code>CLOB</code> object with the contents
137 * in the string passed in. This method uses the <code>Oracle</code>
138 * specific writing to clob fields process.
139 *
140 * @param clob - The <code>clob</code> column that is to be
141 * modified.
142 * @param content - The new content that is to be written to
143 * the CLOB field.
144 * @throws IOException - If exceptions are encountered while writing
145 * to the <code>clob</code> object.
146 * @throws SQLException - If exceptions are encountered while
147 * truncating the existing content in the clob field.
148 */
149 public static final void writeToClob( CLOB clob, String content )
150 throws IOException, SQLException
151 {
152 Writer writer = clob.getCharacterOutputStream();
153 writer.write( content.toCharArray() );
154 writer.flush();
155 writer.close();
156 }
157
158 /**
159 * Default constructor. Cannot be instantiated.
160 */
161 private DatabaseHelper() {}
162 }