Exemple #1
0
 SqlDialect get(DataSource dataSource) {
   Connection connection = null;
   try {
     connection = dataSource.getConnection();
     DatabaseMetaData metaData = connection.getMetaData();
     String productName = metaData.getDatabaseProductName();
     String productVersion = metaData.getDatabaseProductVersion();
     List key = Arrays.asList(productName, productVersion);
     SqlDialect dialect = map.get(key);
     if (dialect == null) {
       final SqlDialect.DatabaseProduct product =
           SqlDialect.getProduct(productName, productVersion);
       dialect = new SqlDialect(product, productName, metaData.getIdentifierQuoteString());
       map.put(key, dialect);
     }
     connection.close();
     connection = null;
     return dialect;
   } catch (SQLException e) {
     throw new RuntimeException(e);
   } finally {
     if (connection != null) {
       try {
         connection.close();
       } catch (SQLException e) {
         // ignore
       }
     }
   }
 }
  private void loadDatabaseMetadata() {
    if (!databaseMetadataLoaded) {
      String componentName = "UNKNOWN";
      Connection con = null;
      try {
        componentName = getMetaComponent().getName();

        con = DataSourceConnectionProvider.getByComponent(componentName).getConnection();
        DatabaseMetaData metaData = con.getMetaData();
        supportsSchemasInDataManipulation = metaData.supportsSchemasInDataManipulation();
        Collection timeDateFunctions =
            Strings.toCollection(metaData.getTimeDateFunctions().toUpperCase());

        //
        // another solution instead of the use of 'if' would be to use a xml with
        //	the information of the functions from each BBDD
        if ("DB2 UDB for AS/400".equals(metaData.getDatabaseProductName())
            || "Oracle".equals(metaData.getDatabaseProductName())
            || "PostgresSQL".equals(metaData.getDatabaseProductName())) {
          supportsTranslateFunction = true;
        }
        if ("Oracle".equals(metaData.getDatabaseProductName())
            || "PostgreSQL".equals(metaData.getDatabaseProductName())) {
          supportsYearFunction = supportsMonthFunction = false;
        } else {
          supportsYearFunction = timeDateFunctions.contains("YEAR");
          supportsMonthFunction = timeDateFunctions.contains("MONTH");
        }
        databaseMetadataLoaded = true;
      } catch (Exception ex) {
        log.warn(XavaResources.getString("load_database_metadata_warning"));
      } finally {
        try {
          if (con != null) {
            con.close();
          }
        } catch (SQLException e) {
          log.warn(XavaResources.getString("close_connection_warning"));
        }
      }
    }
  }
 private static Map<PwmAboutProperty, String> getConnectionDebugProperties(
     final Connection connection) {
   if (connection != null) {
     try {
       final Map<PwmAboutProperty, String> returnObj = new LinkedHashMap<>();
       final DatabaseMetaData databaseMetaData = connection.getMetaData();
       returnObj.put(PwmAboutProperty.database_driverName, databaseMetaData.getDriverName());
       returnObj.put(PwmAboutProperty.database_driverVersion, databaseMetaData.getDriverVersion());
       returnObj.put(
           PwmAboutProperty.database_databaseProductName,
           databaseMetaData.getDatabaseProductName());
       returnObj.put(
           PwmAboutProperty.database_databaseProductVersion,
           databaseMetaData.getDatabaseProductVersion());
       return Collections.unmodifiableMap(returnObj);
     } catch (SQLException e) {
       LOGGER.error("error rading jdbc meta data: " + e.getMessage());
     }
   }
   return Collections.emptyMap();
 }
Exemple #4
0
  static void truncateTable(String strTable) {

    String DBMS = "";
    try {
      DatabaseMetaData metaData = conn.getMetaData();
      DBMS = metaData.getDatabaseProductName().toLowerCase();
    } catch (SQLException e) {
      System.out.println("Problem determining database product name: " + e);
    }
    System.out.println("Truncating '" + strTable + "' ...");
    try {
      if (DBMS.startsWith("db2")) {
        stmt.execute("TRUNCATE TABLE " + strTable + " IMMEDIATE");
      } else {
        stmt.execute("TRUNCATE TABLE " + strTable);
      }
      transCommit();
    } catch (SQLException se) {
      System.out.println(se.getMessage());
      transRollback();
    }
  }
Exemple #5
0
  /**
   * 初始化连接池
   *
   * @param props
   * @param show_sql
   */
  private static final void initDataSource(Properties dbProperties) {
    try {
      if (dbProperties == null) {
        dbProperties = new Properties();
        dbProperties.load(DBManager.class.getResourceAsStream(CONFIG_PATH));
      }
      // Class.forName(dbProperties.getProperty("jdbc.driverClass"));
      for (Object key : dbProperties.keySet()) {
        String skey = (String) key;
        if (skey.startsWith("jdbc.")) {
          String name = skey.substring(5);
          cp_props.put(name, dbProperties.getProperty(skey));
          if ("show_sql".equalsIgnoreCase(name)) {
            show_sql = "true".equalsIgnoreCase(dbProperties.getProperty(skey));
          }
        }
      }
      dataSource = (DataSource) Class.forName(cp_props.getProperty("datasource")).newInstance();
      if (dataSource.getClass().getName().indexOf("c3p0") > 0) {
        // Disable JMX in C3P0
        System.setProperty(
            "com.mchange.v2.c3p0.management.ManagementCoordinator",
            "com.mchange.v2.c3p0.management.NullManagementCoordinator");
      }
      log.info("Using DataSource : " + dataSource.getClass().getName());
      BeanUtils.populate(dataSource, cp_props);

      Connection conn = getConnection();
      DatabaseMetaData mdm = conn.getMetaData();
      log.info(
          "Connected to " + mdm.getDatabaseProductName() + " " + mdm.getDatabaseProductVersion());
      closeConnection();
    } catch (Exception e) {
      e.printStackTrace();
      throw new DBException(e);
    }
  }
  /** Method declaration */
  private void refreshTree() {

    tTree.removeAll();

    try {
      int color_table = Color.yellow.getRGB();
      int color_column = Color.orange.getRGB();
      int color_index = Color.red.getRGB();

      tTree.addRow("", dMeta.getURL(), "-", 0);

      String usertables[] = {"TABLE"};
      ResultSet result = dMeta.getTables(null, null, null, usertables);
      Vector tables = new Vector();

      // sqlbob@users Added remarks.
      Vector remarks = new Vector();

      while (result.next()) {
        tables.addElement(result.getString(3));
        remarks.addElement(result.getString(5));
      }

      result.close();

      for (int i = 0; i < tables.size(); i++) {
        String name = (String) tables.elementAt(i);
        String key = "tab-" + name + "-";

        tTree.addRow(key, name, "+", color_table);

        // sqlbob@users Added remarks.
        String remark = (String) remarks.elementAt(i);

        if ((remark != null) && !remark.trim().equals("")) {
          tTree.addRow(key + "r", " " + remark);
        }

        ResultSet col = dMeta.getColumns(null, null, name, null);

        while (col.next()) {
          String c = col.getString(4);
          String k1 = key + "col-" + c + "-";

          tTree.addRow(k1, c, "+", color_column);

          String type = col.getString(6);

          tTree.addRow(k1 + "t", "Type: " + type);

          boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls;

          tTree.addRow(k1 + "n", "Nullable: " + nullable);
        }

        col.close();
        tTree.addRow(key + "ind", "Indices", "+", 0);

        ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false);
        String oldiname = null;

        while (ind.next()) {
          boolean nonunique = ind.getBoolean(4);
          String iname = ind.getString(6);
          String k2 = key + "ind-" + iname + "-";

          if ((oldiname == null || !oldiname.equals(iname))) {
            tTree.addRow(k2, iname, "+", color_index);
            tTree.addRow(k2 + "u", "Unique: " + !nonunique);

            oldiname = iname;
          }

          String c = ind.getString(9);

          tTree.addRow(k2 + "c-" + c + "-", c);
        }

        ind.close();
      }

      tTree.addRow("p", "Properties", "+", 0);
      tTree.addRow("pu", "User: "******"pr", "ReadOnly: " + cConn.isReadOnly());
      tTree.addRow("pa", "AutoCommit: " + cConn.getAutoCommit());
      tTree.addRow("pd", "Driver: " + dMeta.getDriverName());
      tTree.addRow("pp", "Product: " + dMeta.getDatabaseProductName());
      tTree.addRow("pv", "Version: " + dMeta.getDatabaseProductVersion());
    } catch (SQLException e) {
      tTree.addRow("", "Error getting metadata:", "-", 0);
      tTree.addRow("-", e.getMessage());
      tTree.addRow("-", e.getSQLState());
    }

    tTree.update();
  }
  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();
      }
  }
  /* Clear all existing nodes from the tree model and rebuild from scratch.
   */
  protected void refreshTree() {

    DefaultMutableTreeNode propertiesNode;
    DefaultMutableTreeNode leaf;

    // First clear the existing tree by simply enumerating
    // over the root node's children and removing them one by one.
    while (treeModel.getChildCount(rootNode) > 0) {
      DefaultMutableTreeNode child = (DefaultMutableTreeNode) treeModel.getChild(rootNode, 0);

      treeModel.removeNodeFromParent(child);
      child.removeAllChildren();
      child.removeFromParent();
    }

    treeModel.nodeStructureChanged(rootNode);
    treeModel.reload();
    tScrollPane.repaint();

    // Now rebuild the tree below its root
    try {

      // Start by naming the root node from its URL:
      rootNode.setUserObject(dMeta.getURL());

      // get metadata about user tables by building a vector of table names
      String usertables[] = {"TABLE", "GLOBAL TEMPORARY", "VIEW"};
      ResultSet result = dMeta.getTables(null, null, null, usertables);
      Vector tables = new Vector();

      // sqlbob@users Added remarks.
      Vector remarks = new Vector();

      while (result.next()) {
        tables.addElement(result.getString(3));
        remarks.addElement(result.getString(5));
      }

      result.close();

      // For each table, build a tree node with interesting info
      for (int i = 0; i < tables.size(); i++) {
        String name = (String) tables.elementAt(i);
        DefaultMutableTreeNode tableNode = makeNode(name, rootNode);
        ResultSet col = dMeta.getColumns(null, null, name, null);

        // sqlbob@users Added remarks.
        String remark = (String) remarks.elementAt(i);

        if ((remark != null) && !remark.trim().equals("")) {
          makeNode(remark, tableNode);
        }

        // With a child for each column containing pertinent attributes
        while (col.next()) {
          String c = col.getString(4);
          DefaultMutableTreeNode columnNode = makeNode(c, tableNode);
          String type = col.getString(6);

          makeNode("Type: " + type, columnNode);

          boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls;

          makeNode("Nullable: " + nullable, columnNode);
        }

        col.close();

        DefaultMutableTreeNode indexesNode = makeNode("Indices", tableNode);
        ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false);
        String oldiname = null;

        // A child node to contain each index - and its attributes
        while (ind.next()) {
          DefaultMutableTreeNode indexNode = null;
          boolean nonunique = ind.getBoolean(4);
          String iname = ind.getString(6);

          if ((oldiname == null || !oldiname.equals(iname))) {
            indexNode = makeNode(iname, indexesNode);

            makeNode("Unique: " + !nonunique, indexNode);

            oldiname = iname;
          }

          // And the ordered column list for index components
          makeNode(ind.getString(9), indexNode);
        }

        ind.close();
      }

      // Finally - a little additional metadata on this connection
      propertiesNode = makeNode("Properties", rootNode);

      makeNode("User: "******"ReadOnly: " + cConn.isReadOnly(), propertiesNode);
      makeNode("AutoCommit: " + cConn.getAutoCommit(), propertiesNode);
      makeNode("Driver: " + dMeta.getDriverName(), propertiesNode);
      makeNode("Product: " + dMeta.getDatabaseProductName(), propertiesNode);
      makeNode("Version: " + dMeta.getDatabaseProductVersion(), propertiesNode);
    } catch (SQLException se) {
      propertiesNode = makeNode("Error getting metadata:", rootNode);

      makeNode(se.getMessage(), propertiesNode);
      makeNode(se.getSQLState(), propertiesNode);
    }

    treeModel.nodeStructureChanged(rootNode);
    treeModel.reload();
    tScrollPane.repaint();
  }