1 package nmp.dbms.JDBC;
2
3 import java.io.*;
4 import java.sql.*;
5
6 /***
7 * REpresents database connection.<BR>
8 * The following nativeSQL modifications are supported:
9 * <UL>
10 * <LI>Oracle
11 * <OL>
12 * <LI>; removed from statements (if present)
13 * <LI>COUNTER datatype changed to INTEGER and trigger inserted in create table statements (in executeUpdate)
14 * <LI>converts LONGBINARY in LONG RAW in create table statements (in executeUpdate)
15 * </OL>
16 * <LI>MSAccess
17 * <OL>
18 * <LI>; inserted in statements (if not present)
19 * </OL>
20 * <LI>HypersonicSQL
21 * <OL>
22 * <LI>; removed from statements (if present)
23 * <LI>removes constraint in create table statements (in executeUpdate)
24 * <LI>COUNTER datatype changed to INTEGER IDENTITY in create table statements (in executeUpdate)
25 * </OL>
26 * </UL>
27 */
28 public class DBConnection
29 {
30 Connection con;
31 Statement stmt;
32 boolean debug;
33 boolean toNative;
34 PrintWriter logWriter;
35 String dbproduct; // lower case database product name
36
37 public DBConnection( String driver, String url, String user, String passwd,
38 boolean debug, PrintWriter logWriter)
39 throws SQLException {
40 this( driver, url, user, passwd, debug, logWriter, true);
41 }
42
43 public DBConnection( String driver, String url, String user, String passwd,
44 boolean debug, PrintWriter logWriter, boolean toNative)
45 throws SQLException {
46 this.toNative = toNative;
47 dbproduct = "unknown";
48 this.debug = debug;
49 try {
50 con = null;
51 this.logWriter = logWriter;
52 Class.forName( driver);
53 if( logWriter != null)
54 try {
55 DriverManager.setLogWriter( logWriter);
56 }catch( java.lang.NoSuchMethodError e) {
57 // do nothing - using personal java
58 }
59 openConnection( url, user, passwd);
60 } catch( ClassNotFoundException th) {
61 throw new SQLException( "Driver not found");
62 }
63 }
64
65 protected void finalize() {
66 try {
67 closeConnection();
68 } catch( Exception ex) {
69 /* do nothing */
70 }
71 }
72
73 public void reset()
74 throws SQLException {
75 if( stmt != null)
76 stmt.close();
77 stmt = con.createStatement();
78 }
79
80 public void openConnection( String url, String user, String passwd)
81 throws SQLException {
82 if( con != null)
83 closeConnection();
84 con = DriverManager.getConnection( url, user, passwd);
85 if( debug)
86 checkForWarning( con.getWarnings());
87 con.setAutoCommit( true);
88
89 stmt = con.createStatement();
90
91 DatabaseMetaData dma = con.getMetaData ();
92 dbproduct = dma.getDatabaseProductName().toLowerCase();
93 if( dbproduct == null)
94 dbproduct = "unknown";
95
96 if( debug) {
97 PrintWriter writer = DriverManager.getLogWriter();
98
99 writer.println("\nConnected to :" + dma.getURL());
100 writer.println("Driver :" + dma.getDriverName());
101 writer.println("Version :" + dma.getDriverVersion());
102 writer.println("Database :" + dma.getDatabaseProductName());
103 writer.println("Version db :" + dma.getDatabaseProductVersion());
104 writer.println("Max con :" + Integer.toString( dma.getMaxConnections()));
105 writer.print("Trans level :"); writer.println( dma.supportsTransactions());
106 writer.print("SQL1 level :"); writer.println( dma.supportsANSI92EntryLevelSQL());
107 writer.print("SQL2 level :"); writer.println( dma.supportsANSI92IntermediateSQL());
108 writer.print("SQL3 level :"); writer.println( dma.supportsANSI92FullSQL());
109 writer.println("");
110 }
111 }
112
113 public void closeConnection()
114 throws SQLException {
115 if( stmt != null)
116 stmt.close();
117 if( con != null)
118 con.close();
119 }
120
121 /***
122 * Returns nativeSQL statement
123 */
124 public String nativeSQL( String command) throws SQLException {
125 return nativeSQL0( command)[0];
126 }
127
128 /***
129 * Returns a sequence of equivalente nativeSQL statements.
130 */
131 protected String []nativeSQL0( String command)
132 throws SQLException {
133 String []arr = null;
134 command = command.trim();
135 if( toNative) {
136 String cmdlower = command.toLowerCase();
137 if( dbproduct.startsWith( "oracle")) {
138 if( command.endsWith( ";")) {
139 command = command.substring( 0, command.length() - 1);
140 cmdlower = command.toLowerCase();
141 }
142 if( cmdlower.startsWith( "create table")) {
143 int pos = cmdlower.indexOf( "longbinary");
144 while( pos != -1) {
145 command = command.substring( 0, pos) + "LONG RAW" + command.substring( pos + 10, command.length());
146 cmdlower = command.toLowerCase();
147 pos = cmdlower.indexOf( "longbinary");
148 }
149 pos = cmdlower.indexOf( "counter");
150 if( pos != -1) {
151 int nxtpos = cmdlower.lastIndexOf( "counter");
152 if( nxtpos == pos) {
153 int i = pos - 1;
154 while( i >= 0 && Character.isWhitespace( command.charAt( i)))
155 i--;
156 int endpos = i;
157 while( i >= 0 && Character.isJavaIdentifierPart( command.charAt( i)))
158 i--;
159 int stpos = i + 1;
160 String fldname = command.substring( stpos, endpos + 1);
161 i = cmdlower.indexOf( "table");
162 i = i + 5;
163 while( i < command.length() && Character.isWhitespace( command.charAt( i)))
164 i++;
165 stpos = i;
166 while( i < command.length() && Character.isJavaIdentifierPart( command.charAt( i)))
167 i++;
168 endpos = i - 1;
169 String tblname = command.substring( stpos, endpos + 1);
170 arr = new String[3];
171 arr[1] = "create sequence seq_" + tblname + "_" + fldname;
172 arr[2] = "create or replace trigger trg_" + tblname + "_" + fldname +
173 " BEFORE insert ON " + tblname + " FOR EACH ROW " +
174 " when (new." + fldname + " IS NULL)" +
175 " declare val integer;" +
176 " begin " +
177 " select seq_" + tblname + "_" + fldname + ".nextval into val from dual; "+
178 " :new." + fldname + " := val; " +
179 " end; ";
180 command = command.substring( 0, pos) + "integer" + command.substring( pos + 7);
181 }
182 }
183 }
184 } else if( dbproduct.startsWith( "access")) {
185 if( ! command.endsWith( ";")) {
186 command = command + ";";
187 cmdlower = command.toLowerCase();
188 }
189 } else if( dbproduct.startsWith( "hypersonicsql") || dbproduct.startsWith( "hsql")) {
190 if( command.endsWith( ";")) {
191 command = command.substring( 0, command.length() - 1);
192 cmdlower = command.toLowerCase();
193 }
194 if( cmdlower.startsWith( "create table")) {
195 int pos = cmdlower.indexOf( "constraint");
196 while( pos >= 0) {
197 int i = pos + 10;
198 while( i < command.length() && Character.isWhitespace( command.charAt( i)))
199 i++;
200 while( i < command.length() && Character.isJavaIdentifierPart( command.charAt( i)))
201 i++;
202 command = command.substring( 0, pos) + command.substring( i);
203 cmdlower = command.toLowerCase();
204 pos = cmdlower.indexOf( "constraint");
205 }
206 pos = cmdlower.indexOf( "counter");
207 while( pos != -1) {
208 command = command.substring( 0, pos) + "INTEGER IDENTITY" + command.substring( pos + 7, command.length());
209 cmdlower = command.toLowerCase();
210 pos = cmdlower.indexOf( "counter");
211 }
212 pos = cmdlower.indexOf( "longbinary");
213 while( pos != -1) {
214 command = command.substring( 0, pos) + "LONGVARBINARY" + command.substring( pos + 10, command.length());
215 cmdlower = command.toLowerCase();
216 pos = cmdlower.indexOf( "longbinary");
217 }
218 }
219 }
220 }
221 if( arr == null)
222 arr = new String[1];
223 arr[0] = con.nativeSQL( command);
224 return arr;
225 }
226
227 public int executeUpdate( String command, java.util.Vector values) throws SQLException{
228 PreparedStatement prepStmt = null;
229 try {
230 command = nativeSQL( command);
231 prepStmt = con.prepareStatement(command);
232 if( debug)
233 DriverManager.getLogWriter().println( command);
234 for ( int i = 0; i < values.size(); i++)
235 prepStmt.setObject( i+1, nmp.io.Utilities.serialize( values.elementAt( i)), java.sql.Types.LONGVARBINARY);
236 // prepStmt.setBytes( i+1, nmp.io.Utilities.serialize( values.elementAt( i)));
237 return prepStmt.executeUpdate();
238 } catch( IOException e) {
239 throw new SQLException( "Error serializing object in executeUpdate");
240 } catch( SQLException ex) {
241 if( debug)
242 ex.printStackTrace( logWriter);
243 throw ex;
244 } finally {
245 if( prepStmt != null)
246 prepStmt.close();
247 }
248 }
249
250 public int executeUpdate( String command)
251 throws SQLException{
252 String []cmds = nativeSQL0( command);
253 int res = executeUpdate0( cmds[0]);
254 for( int i = 1 ; i < cmds.length; i++)
255 executeUpdate0( cmds[i]);
256 return res;
257 }
258
259 protected int executeUpdate0( String command)
260 throws SQLException{
261 try {
262 if( debug)
263 DriverManager.getLogWriter().println( command);
264 return stmt.executeUpdate( command);
265 } catch( SQLException ex) {
266 if( debug)
267 ex.printStackTrace( logWriter);
268 throw ex;
269 }
270 }
271
272 public ResultSet executeQuery( String command)
273 throws SQLException{
274 try {
275 command = nativeSQL( command);
276 if( debug)
277 DriverManager.getLogWriter().println( command);
278 return stmt.executeQuery( command);
279 } catch( SQLException ex) {
280 if( debug)
281 ex.printStackTrace( logWriter);
282 throw ex;
283 }
284 }
285
286 /***
287 * Sets the autocommit mode
288 */
289 public void setAutoCommit( boolean flag) throws SQLException {
290 if( con != null)
291 con.setAutoCommit( flag);
292 }
293
294 /***
295 * Commit transaction
296 */
297 public void commit() throws SQLException {
298 if( con != null)
299 con.commit();
300 }
301
302 /***
303 * Rollback transaction
304 */
305 public void rollback() throws SQLException {
306 if( con != null)
307 con.rollback();
308 }
309
310
311 private void checkForWarning( SQLWarning warn)
312 throws SQLException {
313 PrintWriter writer = DriverManager.getLogWriter();
314
315 if( warn != null) {
316 writer.println( "\n *** Warning ***\n");
317 while (warn != null) {
318 writer.println( "SQLState: " + warn.getSQLState ());
319 writer.println( "Message: " + warn.getMessage ());
320 writer.println( "Vendor: " + warn.getErrorCode ());
321 writer.println( "");
322 warn = warn.getNextWarning();
323 }
324 }
325 }
326
327 public boolean isValid() {
328 try {
329 return con != null && ! con.isClosed();
330 } catch( SQLException ex) {
331 return false;
332 }
333 }
334 }
This page was automatically generated by Maven