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