001 /***************************************************************************************************
002 * MODULE DESCRIPTION
003 ****************************************************************************************************
004 *
005 * NAME: SimpleDb.java
006 * LANGUAGE: Java2
007 * DATE: 2.12.2002
008 * AUTHOR: Miika Nurminen, Jyväskylän yliopisto
009 *
010 ****************************************************************************************************
011 * COPYRIGHT (C) Kiuru-Group
012 * Limited rights granted. Please refer to license
013 ****************************************************************************************************
014
015 ****************************************************************************************************
016 * UPDATES
017 ****************************************************************************************************
018 *
019 * 2.12.2002 - Initial version
020 *
021 ****************************************************************************************************/
022 package kiurubeans;
023 import java.util.*;
024 import java.beans.*;
025 import kotkabeans.*;
026
027 /**
028 * Facilitates executing simple ad hoc queries using kotkabeans.DB.
029 * @author Miika Nurminen
030 */
031 public class SimpleDb extends Object {
032
033 /** Creates new SimpleDb */
034 public SimpleDb() {
035 }
036
037
038 /** Converts given column in RS2 to string array
039 * @return <CODE>String[]</CODE>-array containing all values from column
040 * <CODE>colname</CODE>.
041 * @param rs RS2 containing records
042 * @param colname Column name where we fetch values from
043 * @throws Exception Something went wrong when handling RS2
044 */
045 public static String[] RS2toArray(RS2 rs,String colname) throws java.lang.Exception {
046 if (rs==null) return null;
047 String[] s = new String[rs.count()];
048 rs.beforeFirst();
049 int i=0;
050 while (rs.next()) {
051 s[i]=rs.getString(colname);
052 i++;
053 }
054 return s;
055 }
056
057 /** Executes simple database query.
058 * @param logstring Information for logging purposes.
059 * @param querystring Actual query.
060 * @throws Exception Database query couldn't made successfully.
061 * @return RS2 typed ResultSet containing results for the query.
062 */
063 public static RS2 simpleQuery(String logstring,String querystring) throws Exception {
064 RS2 rs=null;
065 DB db=new DB(logstring);
066 db.connect();
067 try {
068 rs = new RS2(db.executeQuery(querystring));
069 }
070 finally {
071 db.disconnect();
072 }
073 return rs;
074 }
075
076 /** Executes simple database update.
077 * @param logstring Information for logging purposes.
078 * @param updatestring Actual update sql clause.
079 * @throws Exception Database query couldn't made successfully.
080 * @return Returns amount of updated rows.
081 */
082 public static int simpleUpdate(String logstring,String updatestring) throws Exception {
083 int updatedfields = 0;
084 DB db=new DB(logstring);
085 db.connect();
086 try {
087 updatedfields = db.executeUpdate(updatestring);
088 }
089 finally {
090 db.disconnect();
091 }
092 return updatedfields;
093 }
094
095 /** Checks if record exists of the given table.
096 *
097 * Returns true even if there are deleted records.
098 * Assumes that db is valid and open! Caller is responsible for closing db!
099 * @param db Open database connection
100 * @param table Name of the table we are looking at.
101 * @param idname Column name of the primary key.
102 * @param idvalue Value of the primary key.
103 * @throws Exception Something illegal happened during database query.
104 * @return True if record with given id exists in given table.
105 */
106 public static boolean recordExists(DB db,String table,String idname,int idvalue) throws Exception {
107 RS2 rs = new RS2(db.executeQuery("select "+idname+" from "+table+" where "+idname+"="+Integer.toString(idvalue)));
108 return (rs.count()>0);
109 }
110
111
112 /** Checks if record exists of the given table. (like recordExists(db,table,idname,value, but multiple id/value pairs)
113 *
114 * Assumes that db is valid and open! Caller is responsible for closing db!
115 * @param db Open database connection
116 * @param table Name of the table we are looking at.
117 * @param values array of StringPair containing column names and values
118 * @throws Exception Something illegal happened during database query.
119 * @return True if record with given id exists in given table.
120 */
121 public static boolean recordExists(DB db,String table, StringPair[] values) throws Exception {
122 StringBuffer s = new StringBuffer("select ");
123 for (int i=0; i<values.length; i++) {
124 if (i>0) s.append(",");
125 s.append(values[i].getKey());
126 }
127 s.append(" from "+table+" where ");
128 for (int i=0; i<values.length; i++) {
129 if (i>0) s.append(" and ");
130 s.append(values[i].getKey()+"="+values[i].getValue());
131 }
132 RS2 rs = new RS2(db.executeQuery(s.toString()));
133 return rs.next();
134 }
135
136 /** Like ExecuteTransaction(Statementcontainer[],String), but takes list
137 * instead of array. Assumes arraylist contains only StatementContainers
138 * @param a Arraylist containing StatementContainers.
139 * @param dbLog Log string
140 * @return Returns true, if transaction was successful.
141 */
142 public static boolean executeTransaction(ArrayList a,String dbLog) {
143 if (a.size()==0) return false;
144 StatementContainer[] sc = new StatementContainer[a.size()];
145 for (int i=0; i<a.size(); i++) {
146 sc[i]=(StatementContainer)a.get(i);
147 }
148 return executeTransaction(sc,dbLog);
149 }
150
151 /** Executes array of prepared statements in a transaction. Returns true on success
152 * @param sc StatementContainer containing database statements
153 * @param dbLog Log string
154 * @return Returns true, if transaction was successful.
155 */
156 public static boolean executeTransaction(StatementContainer[] sc,String dbLog) {
157 boolean result=false;
158 DB db = new DB(dbLog);
159 try {
160 db.connect();
161 result = executeTransaction(sc,dbLog,db);
162 }
163 catch (Exception e) {
164 Log.log("",e);
165 }
166 finally {
167 db.disconnect();
168 }
169 return result;
170 }
171
172 /** Executes array of prepared statements in a transaction. Returns true on success
173 * @param sc StatementContainer containing database statements
174 * @param dbLog Log string
175 * @param db Open database connection
176 * @return Returns true, if transaction was successful.
177 */
178 public static boolean executeTransaction(StatementContainer[] sc,String dbLog, DB db) {
179 boolean result=false;
180 try {
181 db.beginTransaction();
182 try {
183 for (int i=0; i<sc.length; i++) {
184 sc[i].prepare(db);
185 sc[i].getStatement().executeUpdate();
186 }
187
188 db.endTransaction();
189 result=true;
190 }
191 catch (Exception e) {
192 db.abortTransaction();
193 Log.log("",e);
194 }
195 finally {
196 for (int i=0; i<sc.length; i++)
197 if (sc[i].getStatement()!=null)
198 sc[i].getStatement().close();
199 }
200 }
201 catch (Exception DBe) {
202 Log.log("",DBe);
203 }
204 return result;
205 }
206
207 /** Checks if user can edit current record & moves RS cursor to first record
208 *
209 * This should be called after every query before properties are assigned
210 * to bean.
211 * @return true if properties can be assigned, false is RS2 is empty of record is deleted
212 * @param rs RS2
213 * @throws Exception Something illegal happened during database operations.
214 */
215 public static boolean checkIfEditable(RS2 rs) throws java.lang.Exception {
216 return ((rs.next()) && (!rs.getBoolean("deleted")));
217 }
218
219 /** Filters off 'null' result strings from RS2.getString
220 * @return Returns value of the <CODE>column</CODE> or '-' if it is null
221 * @param rs RS2 containing results
222 * @param column Which column we are interested in
223 * @throws Exception Something illegal happened during database operations
224 */
225 public static String getString(RS2 rs,String column) throws java.lang.Exception {
226 String result = rs.getString(column);
227 if (rs.wasNull()) return "-"; // wasnull must be called after getString
228 return result;
229 }
230
231 /** Returns value of a timestamp field in compact notation.
232 * @param rs current resultset
233 * @param column column in resultset (should be of type timestamp)
234 * @throws Exception Something illegal happened during database operations.
235 * @return field value
236 */
237 public static String getDateTimeString(RS2 rs,String column) throws Exception {
238 String result = rs.getString(column);
239 if (rs.wasNull()) return "";
240 return time.timestampToOutFormat(result);
241 }
242
243 /** Filters off 'null' result strings from RS2.getString and combines them with space as delimiter
244 * @param rs RS2 containing results
245 * @param column Column names of the columns we are interested in.
246 * @throws Exception Something illegal happened during database operations.
247 * @return Values of the columns separated with spaces. <CODE>Null</CODE>s are replaced
248 * with <CODE>'-'</CODE>s.
249 */
250 public static String getString(RS2 rs,String[] column) throws Exception {
251 StringBuffer result = new StringBuffer(column.length*2);
252 for (int i=0; i<column.length; i++) {
253 if (result.length()>0) result.append(" ");
254 String s = rs.getString(column[i]);
255 if (!rs.wasNull()) result.append(s);
256 }
257 if (result.length()==0) result.append("-");
258 return result.toString();
259 }
260
261 /** Formats SQL search string from user's input. Assumes searchStr is not null.
262 * Result can be appended to SQL clause.
263 * @param fieldName Field name the user is searching.
264 * @param searchStr Search string
265 * @return Returns string that can be inserted in sql query clause.
266 */
267 public static String formatSearchString(String fieldName,String searchStr) {
268 return formatSearchString(fieldName,searchStr,true);
269 }
270
271 /** Formats SQL search string from user's input. Assumes searchStr is not null.
272 * Result can be appended to SQL clause.
273 * @param fieldName Field name the user is searching.
274 * @param searchStr Search string
275 * @param beginAnd Do we begin our search string with 'and'
276 * @return Returns string that can be inserted in sql query clause.
277 */
278 public static String formatSearchString(String fieldName,String searchStr,boolean beginAnd) {
279 String c=null;
280 if (searchStr.trim().equals("")) c="%"; else c=searchStr;
281 // String s = Tools.parseSearch(c,fieldName);
282 String s = SqlSearchParser.search(c, fieldName); // new search logic! 16.4./mn
283 String t =" ("+s+") ";
284 if (beginAnd==true) t=" and"+t;
285 return t;
286 }
287
288 /** Formats SQL search string from array of id numbers. Result can be appended to SQL clause.
289 * @return " ( t1.field='2' or t1.field='7' or t1.field='10' ... ) "
290 * @param fieldname The name of the column we are searching
291 * @param ids ids of the records we are looking for.
292 */
293 public static String formatOrSet(String fieldname,String[] ids) {
294 if ((null==ids) || (0==ids.length))
295 return "";
296 StringBuffer sb = new StringBuffer(20);
297 sb.append(" ( ");
298 for (int i=0; i<ids.length; i++) {
299 if (i>0) sb.append(" or ");
300 sb.append(fieldname);
301 sb.append("=");
302 sb.append(KiuruString.sqlQuote(ids[i]));
303 }
304 sb.append(" ) ");
305 return sb.toString();
306 }
307
308 /**
309 * returns "from" part of SQL
310 * @param tables table names (and aliases) in stringpair array. assumes key is alias, content tablename
311 * @return "from table1 as t1, table2 as t2 ..."
312 */
313 public static String addSqlFrom(StringPair[] tables) {
314 if ((tables==null) || (tables.length==0))
315 return "";
316 StringBuffer result = new StringBuffer(50);
317 result.append(" from ");
318 for (int i=0; i<tables.length; i++) {
319 if (i>0) result.append(", ");
320 result.append(tables[i].getValue());
321 result.append(" as ");
322 result.append(tables[i].getKey());
323 }
324 result.append(" ");
325 return result.toString();
326 }
327
328 /**
329 * Returns "deleted" part in where clause. Assumes clause may start with and
330 * @param tables table names (and aliases) in stringpair array. key is used in clause
331 * @return "and t1.deleted=false and t2.deleted=false ..."
332 */
333 public static String addSqlDeleted(StringPair[] tables) {
334 if ((tables==null) || (tables.length==0))
335 return "";
336 StringBuffer result = new StringBuffer(50);
337 for (int i=0; i<tables.length; i++) {
338 result.append(" and ");
339 result.append(tables[i].getKey());
340 result.append(".deleted=false");
341 }
342 result.append(" ");
343 return result.toString();
344 }
345
346 /** Tests if rs2 is NULL or empty.
347 * @return true if rs2 is NULL or empty. (eg. returns false if rs2 has content)
348 * @param rs RS2 we are testing
349 */
350 public static boolean isEmpty(RS2 rs) {
351 return ((rs==null) || (rs.count()==0));
352 }
353 }