Code example for Connection

Methods: prepareStatement

0
    PreparedStatement stmt = null;
 
    try 
    { 
      String fullTableName = tableOwner + "." + tableName;
      stmt = con.prepareStatement("SELECT * FROM " + fullTableName + " WHERE 0=1");
      stmt.executeQuery();
      return true; 
    } 
    catch(SQLException ex)
    { 
      return false; 
    } 
    finally 
    { 
      SchemaUtils.close(stmt);
    } 
  } 
 
  public TableTypeInfo buildTableType(Connection con,
                                      String tableOwner,
                                      String tableName,
                                      String pk,
                                      ConsoleReader reader,
                                      HashMap<String, String> dbFieldToAvroDataType)
      throws SQLException, IOException, DatabusException 
  { 
    PreparedStatement stmt = null;
    ResultSet rs = null;
 
    try 
    { 
      String fullTableName = tableOwner + "." + tableName;
      stmt = con.prepareStatement("SELECT * FROM " + fullTableName + " WHERE 0=1");
      rs = stmt.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
 
      List<FieldInfo> fields = new ArrayList<FieldInfo>();
 
      int numColumns = rsmd.getColumnCount();
      for(int column=1; column <= numColumns; column++)
      { 
        String columnName = rsmd.getColumnName(column);
 
        System.out.println("Processing column " + tableName + "." + columnName + ":" + rsmd.getColumnTypeName(column));
 
        int columnPrecision = rsmd.getPrecision(column);
        int columnScale = rsmd.getScale(column);
 
 
 
        String columnTypeName;
        String columnTypeOwner;
 
        String[] columnTypeParts = rsmd.getColumnTypeName(column).split("\\.");
        if(columnTypeParts.length == 1)
        { 
          columnTypeOwner = null;
          columnTypeName = columnTypeParts[0];
        } 
        else 
        { 
          columnTypeOwner = columnTypeParts[0];
          columnTypeName = columnTypeParts[1];
        } 
 
 
        if(columnTypeName.equals("NUMBER"))
        { 
          System.out.println("If you are not sure about the following question, please talk with your DBA or the database owner");
          System.out.println("The following datatypes will be used by the avro generator: ");
          System.out.println("If scale <= 6                                     ===> FLOAT (Irrespective of the precision)");
          System.out.println("If scale <= 17                                    ===> DOUBLE (Irrespective of the precision)");
          System.out.println("If (precision > 9 or precision = 0) and scale = 0 ===> LONG ");
          System.out.println("If precision <= 9 and scale = 0                   ===> INTEGER");
          SimpleTypeInfo typeInfoValidate = new SimpleTypeInfo(columnTypeName,columnPrecision,columnScale);
          if(columnPrecision == 0 && columnScale == 0)
            System.out.println("Unable to determine the scale and precision for this column, please manually verify the the scale/precision in the oracle table ALL_TAB_COLUMNS");
 
          System.out.println("The precision ["+ columnPrecision +"] and scale ["+ columnScale + "] will be used for the field " + columnName  + " which has oracle datatype " + columnTypeName + " and the avro datatype " +
                                 typeInfoValidate.getPrimitiveType() + " will be used. (yes - to use the printed values, no - to override the datatype with user input): ");
 
          //If the hashmap is present, this indicates that it's cli driven, we don't ask user input, we except it to passed through cli. 
          if(dbFieldToAvroDataType == null)
          { 
            String line = checkAndRead(reader);
            while(true) 
            { 
 
              if(line.equals("yes"))
              { 
                System.out.println("Using the precision ["+ columnPrecision +"] and scale ["+ columnScale + "]");
                break; 
              } 
              else if(line.equals("no"))
              { 
                System.out.println("Overriding the avro datatype..");
                System.out.println("Please enter the avro datatype you would like to use [FLOAT,DOUBLE,LONG,INTEGER]: ");
                String datatype = checkAndRead(reader);
                try 
                { 
                  ScalePrecision scalePrecision = getScaleAndPrecision(datatype);
                  columnPrecision = scalePrecision.getPrecision();
                  columnScale = scalePrecision.getScale();
                } 
                catch (DatabusException e)
                { 
                  continue; //Invalid input, retry. 
                } 
                typeInfoValidate = new SimpleTypeInfo(columnTypeName, columnPrecision,columnScale);
                System.out.println("Based on your input, the avro datatype " + typeInfoValidate.getPrimitiveType() + " will be used for the field " + columnName);
                break; 
              } 
              else 
              { 
                System.out.println("Invalid input, say 'yes' or 'no'");
                line = checkAndRead(reader);
 
              } 
            } 
          } 
          else 
          { 
              if(dbFieldToAvroDataType.containsKey(columnName.trim()))
              { 
                  String avroDataType = dbFieldToAvroDataType.get(columnName.trim());
                  ScalePrecision scalePrecision = getScaleAndPrecision(dbFieldToAvroDataType.get(columnName.trim()));
                  System.out.println("Using avro data type [" + avroDataType +"] for the column [" + columnName + "]");
                  columnPrecision = scalePrecision.getPrecision();
                  columnScale = scalePrecision.getScale();
              } 
              else 
              { 
                System.out.println("The override for the column [" + columnName + "] is not present, this is expected from the user input in cli");
                throw new DatabusException("Number override not present"); 
              } 
          } 
 
        } 
 
        TypeInfo typeInfo = getTypeInfo(con, columnTypeOwner, columnTypeName, columnPrecision, columnScale,"", reader,
                                        dbFieldToAvroDataType);
        FieldInfo field = new FieldInfo(columnName, typeInfo, column - 1);
        fields.add(field);
      } 
      return new TableTypeInfo(tableOwner, tableName, fields,pk);
    } 
    catch (IOException e)
    { 
      System.out.println("Unable to process user input, please try again.");
      e.printStackTrace();
      throw e;
    } 
    finally 
    { 
      SchemaUtils.close(rs);
      SchemaUtils.close(stmt);
    } 
  } 
 
  private static class ScalePrecision 
  { 
 
    private int getScale() 
    { 
      return scale;
    } 
 
    private int getPrecision() 
    { 
      return precision;
    } 
 
    private int scale;
    private int precision;
 
    private ScalePrecision(int scale, int precision)
    { 
      this.scale = scale;
      this.precision = precision;
    } 
  } 
 
  private ScalePrecision getScaleAndPrecision(String dataType)
      throws DatabusException 
  { 
    dataType = dataType.trim();
    if(dataType.equalsIgnoreCase("FLOAT"))
    { 
      return new ScalePrecision(5,0); 
    } 
    else if(dataType.equalsIgnoreCase("DOUBLE"))
    { 
      return new ScalePrecision(16,0); 
    } 
    else if(dataType.equalsIgnoreCase("LONG"))
    { 
      return new ScalePrecision(0,10); 
    } 
    else if(dataType.equalsIgnoreCase("INTEGER"))
    { 
      return new ScalePrecision(0,8); 
    } 
    else 
    { 
      throw new DatabusException("Unknown datatype, valid datatypes/input are FLOAT/DOUBLE/LONG/INTEGER, please retry."); 
    } 
  } 
 
  private String checkAndRead(ConsoleReader reader)
      throws IOException 
  { 
    String line;
    if((line = reader.readLine()) == null)
    { 
      System.out.println("Please enter a valid input");
      return null; 
    } 
 
    return line.trim();
  } 
 
  // User Types 
  public boolean isUserType(Connection con, String ownerName, String typeName)
      throws SQLException 
  { 
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try 
    { 
      // Query to see if  user attribute information exists for this user type 
      stmt = con.prepareStatement("SELECT 1 FROM ALL_TYPE_ATTRS " +
                                      "WHERE OWNER=? AND TYPE_NAME=? AND ROWNUM < 2"); 
      stmt.setString(1, ownerName);
      stmt.setString(2, typeName);
      rs = stmt.executeQuery();
 
      // If a row exists then this is a user type 
      return rs.next();
    } 
    finally 
    { 
      SchemaUtils.close(rs);
      SchemaUtils.close(stmt);
    } 
  } 
 
  public UserTypeInfo buildUserTypeInfo(Connection con,
                                        String typeOwner,
                                        String typeName,
                                        ConsoleReader reader,
                                        HashMap<String, String> dbFieldToAvroDataType)
      throws SQLException, IOException, DatabusException 
  { 
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try 
    { 
      // Query to select the user type information 
      stmt = con.prepareStatement("SELECT ATTR_NAME, ATTR_TYPE_OWNER, ATTR_TYPE_NAME, PRECISION, SCALE FROM ALL_TYPE_ATTRS " +
                                      "WHERE OWNER=? AND TYPE_NAME=?" + 
                                      "ORDER BY ATTR_NO"); 
      stmt.setString(1, typeOwner);
      stmt.setString(2, typeName);
      rs = stmt.executeQuery();
 
      // If there was no row then this is not a user type 
      if(!rs.next())
      { 
        throw new SQLException("Not a user type. (" + typeName + ")");
      } 
 
      // Build up the list of attributes (fields) in this user type 
      List<FieldInfo> fields = new ArrayList<FieldInfo>();
      do 
      { 
        String attrName = rs.getString(1);
        String attrTypeOwner = rs.getString(2);
        String attrTypeName = rs.getString(3);
        int attrPrecision = rs.getInt(4);
        int attrScale = rs.getInt(5);
 
        TypeInfo typeInfo = getTypeInfo(con, attrTypeOwner, attrTypeName, attrPrecision, attrScale,"", reader,
                                        dbFieldToAvroDataType);
        fields.add(new FieldInfo(attrName, typeInfo, fields.size()));
      } while(rs.next());
 
      return new UserTypeInfo(typeOwner, typeName, fields);
    } 
    finally 
    { 
      SchemaUtils.close(rs);
      SchemaUtils.close(stmt);
    } 
  } 
 
  // Built in types 
  public boolean isSimpleType(Connection con, String typeOwner, String typeName)
      throws SQLException 
  { 
    //For whatever reason, the JDBC driver does not return this as primitive type 
    if (typeName.equalsIgnoreCase("NVARCHAR")
        || typeName.equalsIgnoreCase("NVARCHAR2")
        || typeName.contains("XML"))
      return true; 
    ResultSet rs = null;
    try 
    { 
      // This returns a ResultSet with all the built in types like NUMBER, VARCHAR2, CLOB, etc. 
      rs = con.getMetaData().getTypeInfo();
      while(rs.next())
      { 
        //System.out.print(" " + rs.getString("TYPE_NAME") + " "); 
        if(rs.getString("TYPE_NAME").equalsIgnoreCase(typeName))
          return true; 
      } 
      return false; 
    } 
    finally 
    { 
      SchemaUtils.close(rs);
    } 
  } 
 
  public SimpleTypeInfo buildSimpleTypeInfo(Connection con, String typeOwner, String typeName, int precision, int scale)
      throws SQLException 
  { 
    if(!isSimpleType(con, typeOwner, typeName))
    { 
      throw new SQLException("Not a simple type. (" + typeName + ")");
    } 
    return new SimpleTypeInfo(typeName, precision, scale);
  } 
 
  // Collections 
  public boolean isCollectionType(Connection con, String ownerName, String typeName)
      throws SQLException 
  { 
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try 
    { 
      // Query to see if  collection type information exists for this collection type 
      stmt = con.prepareStatement("SELECT 1 FROM ALL_COLL_TYPES " +
                                      "WHERE OWNER=? AND TYPE_NAME=? AND ROWNUM < 2"); 
      stmt.setString(1, ownerName);
      stmt.setString(2, typeName);
      rs = stmt.executeQuery();