001 package hep.aida.ref.sql;
002
003 // SQL
004 import java.sql.Statement;
005 import java.sql.PreparedStatement;
006 import java.sql.ResultSet;
007 import java.sql.SQLException;
008
009 // Log4J
010 import org.apache.log4j.Logger;
011
012 // Java
013 import java.util.Properties;
014 import java.util.Enumeration;
015
016 /** <code>StmtStr</code> prepares SQL commands for
017 * {@link SQLTuple} and {@link SQLTupleFactory} in a form
018 * of simple Strings.
019 * It tries to get <code>SmtSrc.properties</code> Resource
020 * from {@link Accessor}.
021 * <p><font color="#880088">
022 * <pre>
023 * $Log: StmtSrc.java,v $
024 * Revision 1.48 2008/07/25 13:25:03 hrivnac
025 * oracle schema for tags supported improved
026 *
027 * Revision 1.47 2007/05/23 16:38:44 hrivnac
028 * logical connections for Plotter; better UML
029 *
030 * Revision 1.46 2006/12/15 00:16:13 hrivnac
031 * FORWARD_ONLY allows to plot big ntuples
032 *
033 * Revision 1.45 2006/11/20 16:56:08 hrivnac
034 * fixing input params for Plotter command line
035 *
036 * Revision 1.44 2005/09/28 18:22:42 hrivnac
037 * javadoc fixed
038 *
039 * Revision 1.43 2005/09/28 16:21:06 hrivnac
040 * code cleaning
041 *
042 * Revision 1.42 2005/03/09 15:25:52 hrivnac
043 * ITuple.rows() improved
044 *
045 * Revision 1.41 2005/03/08 17:05:51 hrivnac
046 * ...
047 *
048 * Revision 1.40 2005/02/10 20:07:52 hrivnac
049 * bug fixes to support new ColMan
050 *
051 * Revision 1.39 2005/01/25 16:45:03 hrivnac
052 * Adding rows uses PreparedStatement
053 *
054 * Revision 1.38 2004/12/08 18:04:26 hrivnac
055 * uf
056 *
057 * Revision 1.37 2004/12/08 17:10:37 hrivnac
058 * more flexible SQL mapping
059 *
060 * Revision 1.36 2004/12/01 16:07:15 hrivnac
061 * better support for ColMan JAS3 plugin
062 *
063 * Revision 1.35 2004/11/16 22:41:37 hrivnac
064 * bux fixes in handling linktables
065 *
066 * Revision 1.34 2004/10/22 15:33:00 hrivnac
067 * cleaned
068 *
069 * Revision 1.33 2004/10/12 19:26:59 hrivnac
070 * MySQL Connector 3.0.14
071 *
072 * Revision 1.32 2004/07/06 14:24:50 hrivnac
073 * support for Oracle
074 *
075 * Revision 1.31 2004/06/18 09:17:31 hrivnac
076 * StmtSrc can be accessed directkly
077 *
078 * Revision 1.30 2004/05/22 15:12:59 hrivnac
079 * class id reformated
080 *
081 * Revision 1.29 2004/05/18 15:34:28 hrivnac
082 * compatible with Java 1.5.0-beta2
083 *
084 * Revision 1.28 2004/04/22 08:11:27 hrivnac
085 * StmtSrc updated
086 *
087 * Revision 1.27 2004/04/21 08:27:05 hrivnac
088 * some explicit MySQL dependencies removed
089 *
090 * Revision 1.26 2004/04/21 08:24:51 hrivnac
091 * some explicit MySQL dependencies removed
092 *
093 * Revision 1.25 2004/04/16 15:32:57 hrivnac
094 * Type.properties to setup Type.class
095 *
096 * Revision 1.24 2004/04/14 13:54:42 hrivnac
097 * potential bugs fixed
098 *
099 * Revision 1.23 2004/04/14 13:39:47 hrivnac
100 * 1.5 warnings fixed
101 *
102 * Revision 1.22 2004/04/13 15:45:54 hrivnac
103 * AIDA URL introduced
104 *
105 * Revision 1.21 2004/02/12 16:39:40 hrivnac
106 * niceing
107 *
108 * Revision 1.20 2004/02/10 14:50:58 hrivnac
109 * JavaDoc tags completed
110 *
111 * Revision 1.19 2004/02/04 13:30:39 hrivnac
112 * - improvement of Enums internal mapping
113 * - general cleaning
114 *
115 * Revision 1.18 2004/01/30 18:10:30 hrivnac
116 * - StmtSrc.properties specified a Property
117 * - customised FreeHEP AIDA services included in jar file
118 *
119 * Revision 1.17 2004/01/29 16:39:09 hrivnac
120 * better support for Pol
121 *
122 * Revision 1.16 2004/01/27 14:09:56 hrivnac
123 * StmtSrc.properties loading fixed
124 *
125 * Revision 1.15 2003/11/26 16:09:46 hrivnac
126 * Functional EventSelector WebService
127 *
128 * Revision 1.14 2003/11/24 17:42:26 hrivnac
129 * Better handling of Pool Collections.
130 *
131 * Revision 1.13 2003/11/20 17:36:08 hrivnac
132 * JavaDoc links fixed
133 *
134 * Revision 1.12 2003/11/20 17:21:58 hrivnac
135 * Java 1.5 natively supported, Log4J reporting improved.
136 *
137 * Revision 1.11 2003/11/13 15:43:09 hrivnac
138 * BenchMarking introduced.
139 *
140 * Revision 1.10 2003/11/13 11:07:31 hrivnac
141 * EventSelector prototype added.
142 *
143 * Revision 1.9 2003/11/12 14:13:48 hrivnac
144 * Access to Pool Collections is provided.
145 *
146 * Revision 1.8 2003/11/05 19:46:22 hrivnac
147 * - FreeHEP 1.2.1
148 * - JAIDA 3.2.1
149 *
150 * </pre>
151 * </font></p>
152 * @opt attributes
153 * @opt operations
154 * @opt types
155 * @opt visibility
156 * @version $Id: StmtSrc.java,v 1.48 2008/07/25 13:25:03 hrivnac Exp $
157 * @author <a href="mailto:Julius.Hrivnac@cern.ch">J.Hrivnac</a> */
158 public class StmtSrc {
159
160 /** Prepare all commands.
161 * @param accessor The Accessor to be associated with this StmtSrc.
162 * @param name The name of the NTuple/table (to be used in SQL commands).
163 * @throws SQLTupleException if the StmtSrc can't be created. */
164 public StmtSrc(Accessor accessor, String name) throws SQLTupleException {
165 _accessor = accessor;
166 _name = name;
167 if (accessor.schema() != null && !_name.contains(".")) {
168 _name = "ATLAS_TAGS_CSC_FDR." + _name;
169 }
170 _properties = new LoadedProperties(accessor.src());
171 String key;
172 String value;
173 for (Enumeration en = _properties.propertyNames(); en.hasMoreElements();) {
174 key = (String)(en.nextElement());
175 value = (String)(_properties.get(key));
176 _properties.setProperty(key, value.replaceAll("NAME", _name));
177 }
178 }
179
180 /** Create Statement String. Replace <bold>NAME</bold> with
181 * table name.
182 * @param stmtString The Stmt String with formal NAME.
183 * @return The Stmt String with actual NAME. */
184 private String prepareStatementString(String stmtString) {
185 return stmtString.replaceAll("NAME", _name);
186 }
187
188 /** Perform update operation defined by the
189 * <code>statementString</code>. Use <code>args</code> as parameters.
190 * Multiple SQL comands are allowed.
191 * @param statementString The name of Stmt String (hep.aida.ref.sql.StmtStr.<statementString>[.<implemetation>]).
192 * @param args The arguments to be used in place of #* variables.
193 * @return The number of added rows (of the last SQL command in case
194 * of multiple commands).
195 * @throws SQLTupleException if the SQL command can't be created or performed. */
196 public int update(String statementString, String[] args) throws SQLTupleException {
197 String[] sss = parseStmtString(statementString, args);
198 int n = 0;
199 for (String s : sss) {
200 if (!s.equals("")) {
201 log.debug("update(" + statementString + ": " + s + ";) in " + _accessor);
202 Statement statement;
203 try {
204 statement = _accessor.connection().createStatement();
205 n = statement.executeUpdate(s);
206 statement.close();
207 }
208 catch (SQLException e) {
209 throw new SQLTupleException("Can't perform Update: " + s + ";" , e);
210 }
211 }
212 }
213 return n;
214 }
215
216 /** Perform update operation defined by the
217 * {@link PreparedStatement}.
218 * @param statement The {@link PreparedStatement}.
219 * @return The number of added rows.
220 * @throws SQLTupleException if the SQL command can't be created or performed. */
221 public int updatePrepared(PreparedStatement statement) throws SQLTupleException {
222 log.debug("updatePrepared(" + statement + ") in " + _accessor);
223 try {
224 return statement.executeUpdate();
225 }
226 catch (SQLException e) {
227 throw new SQLTupleException("Can't perform prepared Update" , e);
228 }
229 }
230
231 /** Prepare update statement defined by the
232 * <code>statementString</code>. Use <code>args</code> as parameters.
233 * Multiple SQL comands are allowed, but only the last one is delivered as
234 * {@link PreparedStatement}.
235 * @param statementString The name of Stmt String (hep.aida.ref.sql.StmtStr.<statementString>[.<implemetation>]).
236 * @param args The arguments to be used in place of #* variables.
237 * @throws SQLTupleException if the SQL command can't be created or performed. */
238 public PreparedStatement prepare(String statementString, String[] args) throws SQLTupleException {
239 String[] sss = parseStmtString(statementString, args);
240 if (sss.length > 1) {
241 log.warn("Only the last of " + sss.length + " statements will be prepared");
242 log.warn(" all statements: " + statementString);
243 }
244 PreparedStatement statement = null;
245 String s = sss[sss.length - 1];
246 if (!s.equals("")) {
247 log.debug("prepare(" + statementString + ": " + s + ";) in " + _accessor);
248 try {
249 statement = _accessor.connection().prepareStatement(s);
250 }
251 catch (SQLException e) {
252 throw new SQLTupleException("Can't perform Update: " + s + ";" , e);
253 }
254 }
255 return statement;
256 }
257
258 /** Perform query operation defined by the
259 * <code>statementString</code>. Uses <code>args</code> as parameters.
260 * Multiple SQL actions aren't allowed.
261 * @param statementString The name of Stmt String (hep.aida.ref.sql.StmtStr.<statementString>[.<implemetation>]).
262 * @param args The arguments to be used in place of #* variables.
263 * @throws SQLupleException if the SQL command can't be created or performed. */
264 public ResultSet query(String statementString, String[] args) throws SQLTupleException {
265 String[] sss = parseStmtString(statementString, args);
266 if (sss.length > 1) {
267 log.warn("Only the last of " + sss.length + " statements will be executes");
268 log.warn(" all statements: " + statementString);
269 }
270 String s = sss[sss.length - 1];
271 ResultSet rs = null;
272 if (!s.equals("")) {
273 log.debug("query(" + statementString + ": " + s + ") in " + _accessor);
274 try {
275 rs = _accessor.connection().createStatement(_accessor.forwardOnly() ? ResultSet.TYPE_FORWARD_ONLY : ResultSet.TYPE_SCROLL_INSENSITIVE,
276 ResultSet.CONCUR_READ_ONLY ).executeQuery(sss[0]);
277 }
278 catch (SQLException e) {
279 throw new SQLTupleException("Can't perform Query: " + sss[0] , e);
280 }
281 }
282 return rs;
283 }
284
285 /** Parse <code>statementString</code>. Uses <code>args</code>
286 * as parameters.
287 * @param statementString The name of Stmt String (hep.aida.ref.sql.StmtStr.<statementString>[.<implemetation>]).
288 * @param args The arguments to be used in place of #* variables.
289 * @return The array of statements with replaced arguments.
290 * @throws SQLupleException if the Stmt String can't be parsed. */
291 private String[] parseStmtString(String statementString, String[] args) throws SQLTupleException {
292 int n = 0;
293 String ss = (String)(_properties.get(_place + "." + statementString + "." + _accessor.protocol()));
294 if (ss == null || ss.equals("")) {
295 ss = (String)(_properties.get(_place + "." + statementString));
296 }
297 if (ss == null || ss.equals("")) {
298 throw new SQLTupleException("Can't find Statement String " + statementString);
299 }
300 if (args != null) {
301 int i = 1;
302 for (String arg : args) {
303 ss = ss.replaceAll("#" + i++, arg);
304 }
305 }
306 return ss.split(";");
307 }
308
309 private Properties _properties;
310
311 private Accessor _accessor;
312
313 private String _name;
314
315 private static String _place = StmtSrc.class.getName();
316
317 /** Logging . */
318 private static Logger log = Logger.getLogger(StmtSrc.class);
319
320 }