private void closeConnection() { try { if (statement != null) statement.close(); if (connection != null) { JDBCExceptionReporter.logWarnings(connection.getWarnings()); connection.clearWarnings(); connectionProvider.closeConnection(connection); connectionProvider.close(); } } catch (Exception e) { System.err.println("Could not close connection"); e.printStackTrace(); } }
private void createConnection() throws SQLException { connectionProvider = ConnectionProviderFactory.newConnectionProvider(properties); connection = connectionProvider.getConnection(); if (!connection.getAutoCommit()) { connection.commit(); connection.setAutoCommit(true); } }
public static ResultSet SelectData() { ResultSet rs = null; try { Connection con = ConnectionProvider.getcon(); Statement stmt = con.createStatement(); rs = stmt.executeQuery("select * from feedback"); } catch (Exception e) { } return rs; }
public static ResultSet getSelect1(bookprop p) { ResultSet rs = null; try { Connection con = ConnectionProvider.getcon(); Statement stmt = con.createStatement(); rs = stmt.executeQuery("select * from appoint where did='" + p.getabc() + "'"); } catch (Exception e) { } return rs; }
public static ResultSet getSelect() { ResultSet rs = null; try { Connection con = ConnectionProvider.getcon(); Statement stmt = con.createStatement(); rs = stmt.executeQuery( "select appointdate,appointtime,appointid from appoint where appointid in (select max(appointid) from appoint)"); } catch (Exception e) { } return rs; }
@SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE") public static void execute(Configuration jooqConf, CollectionSchema colSchema) { ConnectionProvider provider = jooqConf.connectionProvider(); Connection connection = provider.acquire(); Statement st = null; try { st = connection.createStatement(); st.executeUpdate("DROP SCHEMA " + colSchema.getName() + " CASCADE"); DSLContext dsl = DSL.using(jooqConf); int deleted = dsl.deleteFrom(CollectionsTable.COLLECTIONS) .where(CollectionsTable.COLLECTIONS.NAME.eq(colSchema.getCollection())) .execute(); assert deleted == 1; } catch (SQLException ex) { throw new ToroImplementationException(ex); } finally { AutoCloser.close(st); } }
public static void InsertData(feedbackprop p) { try { Connection con = ConnectionProvider.getcon(); PreparedStatement stmt = con.prepareStatement("insert into feedback values(?,?,?,?,?)"); stmt.setString(1, p.getFname()); stmt.setString(2, p.getLname()); stmt.setString(3, p.getEmail()); stmt.setString(4, p.getMob()); stmt.setString(5, p.getEnq()); stmt.executeUpdate(); } catch (Exception e) { System.out.println("" + e); } }
public static void insertappoint2(bookprop p) { try { Connection con = ConnectionProvider.getcon(); PreparedStatement stmt = con.prepareStatement( "insert into appoint(appointdate,appointtime,did,pid,symptoms) values(?,?,?,?,?)"); stmt.setString(1, p.getdate1()); stmt.setString(2, p.gettime1()); stmt.setInt(3, Integer.parseInt(p.getdid())); stmt.setInt(4, Integer.parseInt(p.getpid())); stmt.setString(5, p.getSymp()); stmt.executeUpdate(); } catch (Exception e) { System.out.println("" + e); } }
private void initPK(ConnectionProvider cp, String shortTableName) throws SQLException, DBException { ResultSet rs; IndexElement[] iearr = getIndexes(); if (iearr != null) { for (int i = 0; i < iearr.length; i++) if (iearr[i].isUnique()) { UniqueKeyElementImpl ukei = new UniqueKeyElementImpl( iearr[i].getName().getName(), false); // false = not primary key (primary flag is setted later) UniqueKeyElement uke = new UniqueKeyElement(ukei, (TableElement) element, iearr[i]); uke.setColumns(iearr[i].getColumns()); changeKeys(new UniqueKeyElement[] {uke}, DBElement.Impl.ADD); } UniqueKeyElement[] ukes = ((TableElement) element).getUniqueKeys(); rs = cp.getDatabaseMetaData() .getPrimaryKeys(cp.getConnection().getCatalog(), cp.getSchema(), shortTableName); TreeMap cols = new TreeMap(); Object keySeq; String colName; if (rs != null) { HashMap rset = new HashMap(); while (rs.next()) { keySeq = rs.getObject("KEY_SEQ"); // NOI18N colName = rs.getString("COLUMN_NAME").trim(); // NOI18N cols.put(keySeq, colName); // NOI18N } rs.close(); } boolean primary = false; if (cols != null && cols.size() > 0) primary = true; if (primary) { if (ukes == null || ukes.length == 0) { // issue 56492: no index defined for the primary key // generate a UniqueKeyElement and an IndexElement for it String indexName = "primary_key_index"; // NOI18N int i = 1; while (((TableElement) element).getIndex(DBIdentifier.create(indexName)) != null) { indexName = indexName + i; i++; } LinkedList idxs = new LinkedList(); for (Iterator it = cols.values().iterator(); it.hasNext(); ) { // non-unique = false, thus the index is unique -- see initIndexes() idxs.add(indexName + "." + it.next() + ".false"); // NOI18N } IndexElementImpl iei = new IndexElementImpl(this, indexName, true); IndexElement ie = new IndexElement(iei, (TableElement) element); iei.initColumns(idxs); changeIndexes(new IndexElement[] {ie}, DBElement.Impl.ADD); UniqueKeyElementImpl ukei = new UniqueKeyElementImpl(ie.getName().getName(), true); UniqueKeyElement uke = new UniqueKeyElement(ukei, (TableElement) element, ie); uke.setColumns(ie.getColumns()); changeKeys(new UniqueKeyElement[] {uke}, DBElement.Impl.ADD); } else if (ukes.length == 1) ukes[0].setPrimaryKey(primary); else { ColumnElement[] ces; Object[] o = cols.values().toArray(); boolean equals; for (int i = 0; i < ukes.length; i++) { ces = ukes[i].getColumns(); if (ces.length != o.length) continue; else { equals = true; for (int j = 0; j < ces.length; j++) if (!o[j].toString().equals(ces[j].getName().getName())) { equals = false; break; } if (equals) { ukes[i].setPrimaryKey(primary); break; } } } } } } }
/** * @param expectRelatedTables specifies whether all related tables are expected to be provided. */ private void initFKs(ConnectionProvider cp, String shortTableName, boolean expectRelatedTables) throws SQLException, DBException { ResultSet rs; rs = cp.getDatabaseMetaData() .getImportedKeys(cp.getConnection().getCatalog(), cp.getSchema(), shortTableName); String name, fkColName, pkTableName, pkColName, c1, c2, s1, s2; if (rs != null) { HashMap rset = new HashMap(); while (rs.next()) { // test references between two schemas c1 = rs.getString("PKTABLE_CAT"); // NOI18N s1 = rs.getString("PKTABLE_SCHEM"); // NOI18N c2 = rs.getString("FKTABLE_CAT"); // NOI18N s2 = rs.getString("FKTABLE_SCHEM"); // NOI18N name = rs.getString("FK_NAME"); // NOI18N fkColName = rs.getString("FKCOLUMN_NAME").trim(); // NOI18N pkTableName = rs.getString("PKTABLE_NAME").trim(); // NOI18N pkColName = rs.getString("PKCOLUMN_NAME").trim(); // NOI18N if (comp(c1, c2)) { if (!comp(s1, s2)) continue; } else continue; ColumnPairElement cpe; if (name == null || name.trim().equals("")) name = "GENERATED_FK_" + pkTableName; else name = name.trim(); ColumnElement lce = getColumn(DBIdentifier.create(fkColName)); // NOI18N if (lce == null) // should be null only in same cases when FK is computed for view continue; SchemaElement se = ((TableElement) element).getDeclaringSchema(); TableElement fte = se.getTable(DBIdentifier.create(pkTableName)); // table could not be found since all related tables were not necessarily provided if (fte == null && !expectRelatedTables) { continue; } ColumnElement fce = fte.getColumn(DBIdentifier.create(pkColName)); ColumnPairElementImpl cpei = new ColumnPairElementImpl( lce.getName().getFullName() + ";" + fce.getName().getFullName()); // NOI18N cpe = new ColumnPairElement(cpei, lce, fce, (TableElement) element); changeColumnPairs(new ColumnPairElement[] {cpe}, DBElement.Impl.ADD); ForeignKeyElement fk = (ForeignKeyElement) keys.find(DBIdentifier.create(name)); if (fk != null) fk.addColumnPair(cpe); // add pair else { ForeignKeyElementImpl fkei = new ForeignKeyElementImpl(this, name); ForeignKeyElement fke = new ForeignKeyElement(fkei, (TableElement) element); fke.addColumnPair(cpe); changeKeys(new ForeignKeyElement[] {fke}, DBElement.Impl.ADD); } } rs.close(); } }
protected void initIndexes(ConnectionProvider cp, String tbl) { if (cp != null) try { boolean unique; DatabaseMetaData dmd = cp.getDatabaseMetaData(); String shortTableName; if (tbl == null) shortTableName = getName().getName(); else shortTableName = tbl; ResultSet rs; // rs = dmd.getIndexInfo(cp.getConnection().getCatalog(), // dmd.getUserName().trim(), shortTableName, false, true); rs = dmd.getIndexInfo( cp.getConnection().getCatalog(), cp.getSchema(), shortTableName, false, true); String name, columnName; boolean unq; LinkedList idxs = new LinkedList(); if (rs != null) { HashMap rset = new HashMap(); String uniqueStr; while (rs.next()) { name = rs.getString("INDEX_NAME"); // NOI18N columnName = rs.getString("COLUMN_NAME"); // NOI18N if (columnName != null) columnName = columnName.trim(); unq = rs.getBoolean("NON_UNIQUE"); // NOI18N // hack for PostgreSQL bug 3480: the driver returns quotes around quoted column names if (columnName != null && columnName.length() >= 2 && columnName.startsWith("\"") && columnName.endsWith("\"")) { // NOI18N columnName = columnName.substring(1, columnName.length() - 1); } if (name == null) continue; else name = name.trim(); if (unq) idxs.add(name + "." + columnName + ".false"); // NOI18N else idxs.add(name + "." + columnName + ".true"); // NOI18N } rs.close(); } String info; int start, end; for (int i = 0; i < idxs.size(); i++) { info = idxs.get(i).toString(); start = info.indexOf('.'); // NOI18N end = info.lastIndexOf('.'); // NOI18N name = info.substring(0, start); if ((info.substring(end + 1)).equals("true")) // NOI18N unique = true; else unique = false; if (indexes.find(DBIdentifier.create(name)) != null) continue; IndexElementImpl iei = new IndexElementImpl(this, name, unique); IndexElement[] ie = {new IndexElement(iei, (TableElement) element)}; iei.initColumns(idxs); changeIndexes(ie, DBElement.Impl.ADD); } } catch (Exception exc) { if (Boolean.getBoolean("netbeans.debug.exceptions")) // NOI18N exc.printStackTrace(); } }
protected void initColumns(ConnectionProvider cp) { if (cp != null) try { DatabaseMetaData dmd = cp.getDatabaseMetaData(); String shortTableName = getName().getName(); ResultSet rs; // rs = dmd.getColumns(cp.getConnection().getCatalog(), // dmd.getUserName().trim(), shortTableName, "%"); rs = dmd.getColumns(cp.getConnection().getCatalog(), cp.getSchema(), shortTableName, "%"); int sqlType; String sqlTypeName; String colName, colNull, colSize, colDec; if (rs != null) { HashMap rset = new HashMap(); while (rs.next()) { sqlType = rs.getInt("DATA_TYPE"); // NOI18N sqlTypeName = rs.getString("TYPE_NAME").trim(); // NOI18N colName = rs.getString("COLUMN_NAME").trim(); // NOI18N colNull = Integer.toString(rs.getInt("NULLABLE")); // NOI18N colSize = rs.getString("COLUMN_SIZE"); // NOI18N colDec = rs.getString("DECIMAL_DIGITS"); // NOI18N String dbProductName = dmd.getDatabaseProductName().trim(); // Oracle driver hacks if (dbProductName.indexOf("Oracle") != -1) { // NOI18N if (sqlType == 11 || ((sqlType == 1111) && sqlTypeName.startsWith("TIMESTAMP"))) sqlType = Types.TIMESTAMP; if ((sqlType == 1111) && sqlTypeName.equals("FLOAT")) // NOI18N sqlType = Types.DOUBLE; if ((sqlType == 1111) && sqlTypeName.equals("BLOB")) // NOI18N sqlType = Types.BLOB; if ((sqlType == 1111) && sqlTypeName.equals("CLOB")) // NOI18N sqlType = Types.CLOB; if ((sqlType == 1111) && sqlTypeName.equals("NVARCHAR2")) // NOI18N sqlType = Types.CHAR; } // MySQL driver hacks if (dbProductName.indexOf("MySQL") != -1) { // NOI18N if ((sqlType == 1111) && sqlTypeName.equalsIgnoreCase("BIT")) // NOI18N sqlType = Types.BIT; } // workaround for i-net Oranxo driver // value in int range is expected by JDBC API but 4294967296 is returned try { new Integer(colSize); } catch (NumberFormatException exc) { colSize = Integer.toString(Integer.MAX_VALUE); } ColumnElementImpl cei = new ColumnElementImpl(colName, Integer.toString(sqlType), colNull, colSize, colDec); ColumnElement ce = new ColumnElement(cei, (TableElement) element); ColumnElement[] c = {ce}; changeColumns(c, DBElement.Impl.ADD); } rs.close(); } } catch (Exception exc) { if (Boolean.getBoolean("netbeans.debug.exceptions")) // NOI18N exc.printStackTrace(); } }