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);
   }
 }
Example #3
0
 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;
 }
Example #4
0
 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;
 }
Example #5
0
 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;
 }
Example #6
0
  @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);
    }
  }
Example #7
0
 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);
   }
 }
Example #8
0
  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();
      }
  }