Examples of org.apache.empire.db.DBReader

org.apache.empire.db.DBReader
This class is used to perform database queries from a DBCommand object and access the results.
In oder to perform a query call the open() function or - for single row queries - call getRecordData();
You can iterate through the rows using moveNext() or an iterator.

However take care: A reader must always be explicitly closed using the close() method!
Otherwise you may lock the JDBC connection and run out of resources.
Use
```
try { ... } finally { reader.close(); } 
```
to make sure the reader is closed.

To access and work with the query result you can do one of the following:
- access field values directly by using one of the get... functions (see {@link DBRecordData})
- get the rows as a list of Java Beans using by using {@link DBReader#getBeanList(Class,int)}
- get the rows as an XML-Document using {@link DBReader#getXmlDocument()}
- initialize a DBRecord with the current row data using {@link DBReader#initRecord(DBRowSet,DBRecord)}
  This will allow you to modify and update the data.

        cmd.where (T_EMP.C_GENDER.is(genderParam));
        cmd.where (db.V_EMPLOYEE_INFO.C_CURRENT_DEP_ID.is(curDepParam));


        System.out.println("Perfoming two queries using a the same command with different parameter values.");
        
        DBReader r = new DBReader();
        try {
            // Query all females currently working in the Production department
            System.out.println("1. Query all females currently working in the production department");
            // Set command parameter values
            genderParam.setValue('F'); // set gender to female
            curDepParam.setValue(idProdDep); // set department id to production department
            // Open reader using a prepared statement (due to command parameters!)
            r.open(cmd, conn);
            // print all results
            System.out.println("Females working in the production department are:");
            while (r.moveNext())
                System.out.println("    " + r.getString(T_EMP.C_FULLNAME));
            r.close();   


            // Second query
            // Now query all males currently working in the development department
            System.out.println("2. Query all males currently working in the development department");
            // Set command parameter values
            genderParam.setValue('M'); // set gender to female
            curDepParam.setValue(idDevDep); // set department id to production department
            // Open reader using a prepared statement (due to command parameters!)
            r.open(cmd, conn);
            // print all results
            System.out.println("Males currently working in the development department are:");
            while (r.moveNext())
                System.out.println("    " + r.getString(T_EMP.C_FULLNAME));


        } finally {
            r.close();
        }
        
    }

View Full Code Here

        cmd.select(T_EMP.getColumns());
        // Set Constraints
        cmd.where(T_EMP.C_RETIRED.is(false));


        // Query Records and print output
        DBReader reader = new DBReader();
        try
        {
            // Open Reader
            System.out.println("Running Query:");
            System.out.println(cmd.getSelect());
            reader.open(cmd, conn);
            // Print output
            DBRecord record = new DBRecord();
            while (reader.moveNext())
            {
                // Calculate sum
                int sum = 0;
                for (int i=0; i<reader.getFieldCount(); i++)
                    sum += calcCharSum(reader.getString(i));
                // Init updateable record
                reader.initRecord(EMP, record);
                // reader
                record.setValue(T_EMP.C_CHECKSUM, sum);
                record.update(conn);
            }
            // Done
            db.commit(conn);


        } finally
        {
            // always close Reader
            reader.close();
        }
    }

View Full Code Here

        cmd.select(T_EMP.getColumns());
        // Set Constraints
        cmd.where(T_EMP.C_RETIRED.is(false));


        // Query Records and print output
        DBReader reader = new DBReader();
        try
        {   // Open Reader
            System.out.println("Running Query:");
            System.out.println(cmd.getSelect());
            reader.open(cmd, conn);
            // Print output
            HashMap<Integer, DBRecord> employeeMap = new HashMap<Integer, DBRecord>();
            while (reader.moveNext())
            {
                DBRecord rec = new DBRecord();
                reader.initRecord(T_EMP, rec);
                employeeMap.put(reader.getInt(T_EMP.C_EMPLOYEE_ID), rec);
            }
            return employeeMap;


        } finally
        {   // always close Reader
            reader.close();
        }
    }

View Full Code Here

     * @param conn the connection
     */
    private static void printQueryResults(DBCommand cmd, Connection conn)
    {
        // Query Records and print output
        DBReader reader = new DBReader();
        try
        {   // Open Reader
            System.out.println("Running Query:");
            System.out.println(cmd.getSelect());
            reader.open(cmd, conn);
            // Print column titles 
            System.out.println("---------------------------------");
            int count = reader.getFieldCount();
            for (int i=0; i<count; i++)
            {   // Print all column names
                DBColumnExpr c = reader.getColumnExpr(i);
                if (i>0)
                    System.out.print("\t");
                System.out.print(c.getName());
            }
            // Print output
            System.out.println("");
            // Text-Output by iterating through all records.
            while (reader.moveNext())
            {
                for (int i=0; i<count; i++)
                {   // Print all field values
                    if (i>0)
                        System.out.print("\t");
                    // Check if conversion is necessary
                    DBColumnExpr c = reader.getColumnExpr(i);
                    Options opt = c.getOptions();
                    if (opt!=null)
                    {   // Option Lookup
                        System.out.print(opt.get(reader.getValue(i)));
                    }
                    else
                    {   // Print String
                        System.out.print(reader.getString(i));
                    }
                }
                System.out.println("");
            }


        } finally
        {   // always close Reader
            reader.close();
        }
    }

View Full Code Here

                cmd.skipRows(1);
        }
        */
        
    // Query Records and print output
    DBReader reader = new DBReader();
    try
        {
      // Open Reader
      System.out.println("Running Query:");
      System.out.println(cmd.getSelect());
      reader.open(cmd, conn);
      // Print output
      System.out.println("---------------------------------");
      switch(queryType)
      {
          case Reader:
              // Text-Output by iterating through all records.
                  while (reader.moveNext())
                    {
                      System.out.println(reader.getString(EMP.EMPLOYEE_ID)
                              + "\t" + reader.getString(EMPLOYEE_FULLNAME)
                              + "\t" + EMP.GENDER.getOptions().get(reader.getString(EMP.GENDER))
                                + "\t" + reader.getString(PHONE_EXT_NUMBER)
                              + "\t" + reader.getString(DEP.NAME));
                  }
              break;
                case BeanList:
                    // Text-Output using a list of Java Beans supplied by the DBReader
                    List<SampleBean> beanList = reader.getBeanList(SampleBean.class);
                    System.out.println(String.valueOf(beanList.size()) + " SampleBeans returned from Query.");
                    for (SampleBean b : beanList)
                    {
                        System.out.println(b.toString());
                    }
                    break;
                case XmlDocument:
                    // XML Output
                    Document doc = reader.getXmlDocument();
                    // Print XML Document to System.out
                    XMLWriter.debug(doc);
                    break;
      }


    } finally
        {
      // always close Reader
      reader.close();
    }
  }

View Full Code Here

                cmd.skipRows(1);
        }
        */
        
    // Query Records and print output
    DBReader reader = new DBReader();
    try
        {
      // Open Reader
      System.out.println("Running Query:");
      System.out.println(cmd.getSelect());
      reader.open(cmd, conn);
      // Print output
      System.out.println("---------------------------------");
      switch(queryType)
      {
          case Reader:
              // Text-Output by iterating through all records.
                  while (reader.moveNext())
                    {
                      System.out.println(reader.getString(EMP.EMPLOYEE_ID)
                              + "\t" + reader.getString(EMPLOYEE_FULLNAME)
                              + "\t" + EMP.GENDER.getOptions().get(reader.getString(EMP.GENDER))
                                + "\t" + reader.getString(PHONE_EXT_NUMBER)
                              + "\t" + reader.getString(DEP.NAME));
                  }
              break;
                case BeanList:
                    // Text-Output using a list of Java Beans supplied by the DBReader
                    List<SampleBean> beanList = reader.getBeanList(SampleBean.class);
                    System.out.println(String.valueOf(beanList.size()) + " SampleBeans returned from Query.");
                    for (SampleBean b : beanList)
                    {
                        System.out.println(b.toString());
                    }
                    break;
                case XmlDocument:
                    // XML Output
                    Document doc = reader.getXmlDocument();
                    // Print XML Document to System.out
                    XMLWriter.debug(doc);
                    break;
      }


    } finally
        {
      // always close Reader
      reader.close();
    }
  }

View Full Code Here

        DBCommand sysDBCommand = sysDB.createCommand();
        sysDBCommand.select(sysDB.CI.getColumns());
        sysDBCommand.where (sysDB.CI.C_OWNER.is(owner));
        
        OracleDataDictionnary dataDictionnary = new OracleDataDictionnary();
        DBReader rd = new DBReader();
        try
        {
            rd.open(sysDBCommand, conn);
            // read all
            log.info("---------------------------------------------------------------------------------");
            log.info("checkDatabase start: " + db.getClass().getName());
            String skipTable = "";
            while (rd.moveNext())
            {
                String tableName = rd.getString(sysDB.CI.C_TABLE_NAME);


                // if a table wasn't found before, skip it
                if (tableName.equals(skipTable))
                    continue;


                // check if the found table exists in the DBDatabase object
                String columnName = rd.getString(sysDB.CI.C_COLUMN_NAME);
                DBTable dbTable = db.getTable(tableName);
                DBView  dbView  = db.getView(tableName);
                
                String dataType = rd.getString(sysDB.CI.C_DATA_TYPE);
                int charLength = rd.getInt(sysDB.CI.C_CHAR_LENGTH);
                int dataLength = rd.getInt(sysDB.CI.C_DATA_LENGTH);
                int dataPrecision = rd.getInt(sysDB.CI.C_DATA_PRECISION);
                int dataScale = rd.getInt(sysDB.CI.C_DATA_SCALE);
                String nullable = rd.getString(sysDB.CI.C_NULLABLE);
                
                dataDictionnary.fillDataDictionnary(tableName, columnName, dataType, 
                                                    charLength, dataLength, dataPrecision, dataScale, nullable);
                
                if (dbTable != null)
                {
                    
                    // check if the found column exists in the found DBTable
                    DBColumn col = dbTable.getColumn(columnName);
                    if (col == null)
                    {
                        log.warn("COLUMN NOT FOUND IN " + db.getClass().getName() + "\t: [" + tableName + "]["
                                       + columnName + "][" + dataType + "][" + dataLength + "]");
                        continue;
                    }
                    /*
                    else
                    {   // check the DBTableColumn definition
                        int length = (charLength>0) ? charLength : dataLength;
                        dataDictionnary.checkColumnDefinition(col, dataType, length, dataPrecision, dataScale, nullable.equals("N"));
                    }
                    */
                } 
                else if (dbView!=null)
                {
                    log.debug("Column check for view " + tableName + " not yet implemented.");
                } 
                else
                {
                    log.debug("TABLE OR VIEW NOT FOUND IN " + db.getClass().getName() + "\t: [" + tableName + "]");
                    // skip this table
                    skipTable = tableName;
                    continue;
                }
            }
            // check Tables
            dataDictionnary.checkDBTableDefinition(db.getTables());
            // check Views
            dataDictionnary.checkDBViewDefinition (db.getViews());
            // done
            log.info("checkDatabase end: " + db.getClass().getName());
            log.info("---------------------------------------------------------------------------------");
        } finally {
            // close
            rd.close();
        }
    }

View Full Code Here

        DBCommand sysDBCommand = sysDB.createCommand();
        sysDBCommand.select(sysDB.CI.getColumns());
        sysDBCommand.where (sysDB.CI.C_OWNER.is(owner));
        
        OracleDataDictionnary dataDictionnary = new OracleDataDictionnary();
        DBReader rd = new DBReader();
        try
        {
            rd.open(sysDBCommand, conn);
            // read all
            log.info("---------------------------------------------------------------------------------");
            log.info("checkDatabase start: " + db.getClass().getName());
            String skipTable = "";
            while (rd.moveNext())
            {
                String tableName = rd.getString(sysDB.CI.C_TABLE_NAME);


                // if a table wasn't found before, skip it
                if (tableName.equals(skipTable))
                    continue;


                // check if the found table exists in the DBDatabase object
                String columnName = rd.getString(sysDB.CI.C_COLUMN_NAME);
                DBTable dbTable = db.getTable(tableName);
                DBView  dbView  = db.getView(tableName);
                
                String dataType = rd.getString(sysDB.CI.C_DATA_TYPE);
                int charLength = rd.getInt(sysDB.CI.C_CHAR_LENGTH);
                int dataLength = rd.getInt(sysDB.CI.C_DATA_LENGTH);
                int dataPrecision = rd.getInt(sysDB.CI.C_DATA_PRECISION);
                int dataScale = rd.getInt(sysDB.CI.C_DATA_SCALE);
                String nullable = rd.getString(sysDB.CI.C_NULLABLE);
                
                dataDictionnary.fillDataDictionnary(tableName, columnName, dataType, 
                                                    charLength, dataLength, dataPrecision, dataScale, nullable);
                
                if (dbTable != null)
                {
                    
                    // check if the found column exists in the found DBTable
                    DBColumn col = dbTable.getColumn(columnName);
                    if (col == null)
                    {
                        log.warn("COLUMN NOT FOUND IN " + db.getClass().getName() + "\t: [" + tableName + "]["
                                       + columnName + "][" + dataType + "][" + dataLength + "]");
                        continue;
                    }
                    /*
                    else
                    {   // check the DBTableColumn definition
                        int length = (charLength>0) ? charLength : dataLength;
                        dataDictionnary.checkColumnDefinition(col, dataType, length, dataPrecision, dataScale, nullable.equals("N"));
                    }
                    */
                } 
                else if (dbView!=null)
                {
                    log.debug("Column check for view " + tableName + " not yet implemented.");
                } 
                else
                {
                    log.debug("TABLE OR VIEW NOT FOUND IN " + db.getClass().getName() + "\t: [" + tableName + "]");
                    // skip this table
                    skipTable = tableName;
                    continue;
                }
            }
            // check Tables
            dataDictionnary.checkDBTableDefinition(db.getTables());
            // check Views
            dataDictionnary.checkDBViewDefinition (db.getViews());
            // done
            log.info("checkDatabase end: " + db.getClass().getName());
            log.info("---------------------------------------------------------------------------------");
        } finally {
            // close
            rd.close();
        }
    }

View Full Code Here


    cmd.orderBy(EMP.C_LAST_NAME);
    cmd.orderBy(EMP.C_FIRST_NAME);


    // set DataTable
    DBReader reader = new DBReader();
    try {
      reader.open(cmd, FacesUtils.getConnection());
      employeeList = reader.getBeanList(EmployeeListItem.class);
    } finally {
      reader.close();
    }
  }

View Full Code Here

        cmd.where (T_EMP.C_GENDER.is(genderParam));
        cmd.where (db.V_EMPLOYEE_INFO.C_CURRENT_DEP_ID.is(curDepParam));


        System.out.println("Perfoming two queries using a the same command with different parameter values.");
        
        DBReader r = new DBReader();
        try {
            // Query all females currently working in the Production department
            System.out.println("1. Query all females currently working in the production department");
            // Set command parameter values
            genderParam.setValue('F'); // set gender to female
            curDepParam.setValue(idProdDep); // set department id to production department
            // Open reader using a prepared statement (due to command parameters!)
            r.open(cmd, conn);
            // print all results
            System.out.println("Females working in the production department are:");
            while (r.moveNext())
                System.out.println("    " + r.getString(T_EMP.C_FULLNAME));
            r.close();   


            // Second query
            // Now query all males currently working in the development department
            System.out.println("2. Query all males currently working in the development department");
            // Set command parameter values
            genderParam.setValue('M'); // set gender to female
            curDepParam.setValue(idDevDep); // set department id to production department
            // Open reader using a prepared statement (due to command parameters!)
            r.open(cmd, conn);
            // print all results
            System.out.println("Males currently working in the development department are:");
            while (r.moveNext())
                System.out.println("    " + r.getString(T_EMP.C_FULLNAME));


        } finally {
            r.close();
        }
        
    }

View Full Code Here

0 1 2 3 4 5 6 7

TOP

Related Classes of org.apache.empire.db.DBReader

com.vsked.empire.samples.db.SampleApp

org.apache.empire.data.bean.BeanResult

org.apache.empire.data.DataType

org.apache.empire.db.examples.codegen.ValidatePluginRunTest

org.apache.empire.db.oracle.DBDatabaseDriverOracle

org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQLTest

org.apache.empire.jsf2.pageelements.BeanListPageElement

org.apache.empire.jsf2.websample.web.pages.EmployeeListPage

org.apache.empire.samples.cxf.wssample.server.EmployeeServiceImpl

org.apache.empire.samples.db.advanced.SampleAdvApp

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.