Examples of org.apache.empire.db.DBCommand

org.apache.empire.db.DBCommand
This abstract class handles the creation of the SQL-Commands. There are methods to create SQL-Commands, like update, insert, delete and select.

     * @param conn a connection to the database
     */
    private static void bulkProcessRecords(Connection conn)
    {
        // Define the query
        DBCommand cmd = db.createCommand();
        // Define shortcuts for tables used - not necessary but convenient
        SampleAdvDB.Employees EMP = T_EMP;
        // Select required columns
        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())
            {

View Full Code Here

    }
  
    private static HashMap<Integer, DBRecord> bulkReadRecords(Connection conn)
    {
        // Define the query
        DBCommand cmd = db.createCommand();
        // Select required columns
        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())
            {

View Full Code Here

     * @param employeeId
     */
    private static void querySample(Connection conn, int employeeId)
    {
        // Define the sub query
        DBCommand subCmd = db.createCommand();
        DBColumnExpr MAX_DATE_FROM = T_EDH.C_DATE_FROM.max().as(T_EDH.C_DATE_FROM);
        subCmd.select(T_EDH.C_EMPLOYEE_ID, MAX_DATE_FROM);
        subCmd.groupBy(T_EDH.C_EMPLOYEE_ID);
        DBQuery Q_MAX_DATE = new DBQuery(subCmd);


        // Define the query
        DBCommand cmd = db.createCommand();
        // Select required columns
        cmd.select(T_EMP.C_EMPLOYEE_ID, T_EMP.C_FULLNAME);
        cmd.select(T_EMP.C_GENDER, T_EMP.C_PHONE_NUMBER);
        cmd.select(T_DEP.C_DEPARTMENT_ID, T_DEP.C_NAME, T_DEP.C_BUSINESS_UNIT);
        cmd.select(T_EMP.C_UPDATE_TIMESTAMP, T_DEP.C_UPDATE_TIMESTAMP);
        // Set Joins
        cmd.join(T_EDH.C_EMPLOYEE_ID, Q_MAX_DATE.findQueryColumn(T_EDH.C_EMPLOYEE_ID))
          .where(T_EDH.C_DATE_FROM.is(Q_MAX_DATE.findQueryColumn(MAX_DATE_FROM)));
        cmd.join(T_EMP.C_EMPLOYEE_ID, T_EDH.C_EMPLOYEE_ID);
        cmd.join(T_DEP.C_DEPARTMENT_ID, T_EDH.C_DEPARTMENT_ID);
        // Set Constraints
        cmd.where(T_EMP.C_RETIRED.is(false));
        // Set Order
        cmd.orderBy(T_EMP.C_LASTNAME);
        cmd.orderBy(T_EMP.C_FIRSTNAME);


        // Query Records and print output
        printQueryResults(cmd, conn);
        
        // Define an updateable query

View Full Code Here

    }


    private boolean databaseExists(Connection conn)
    {
        // Check wether DB exists
        DBCommand cmd = db.createCommand();
        cmd.select(db.DEPARTMENTS.count());
        int deps = db.querySingleInt(cmd, -1, conn);
        return (deps >= 0);
    }

View Full Code Here

        emp.setNew(false);
    }


    public List<Employee> searchEmmployee(Integer id, String firstName, String lastName, Integer department)
    {
        DBCommand cmd = db.createCommand();
        cmd.select(T_EMP.getColumns());


        DBCompareExpr comp;


        if (id != null)
            comp = T_EMP.EMPLOYEE_ID.is(id);
        else
            comp = T_EMP.EMPLOYEE_ID.isNot(null);


        if (firstName != null && !firstName.equals(""))
        {
            comp = comp.and(T_EMP.FIRSTNAME.like(firstName));
        }


        if (lastName != null && !lastName.equals(""))
        {
            comp = comp.and(T_EMP.LASTNAME.like(lastName));
        }


        if (department != null)
        {
            comp = comp.and(T_EMP.DEPARTMENT_ID.is(department));
        }


        cmd.where(comp);


        DBReader reader = new DBReader();
        reader.open(cmd, conn);
        List<Employee> lst = reader.getBeanList(Employee.class);
        return lst;

View Full Code Here

        return lst;
    }


    public List<Department> getDepartments()
    {
        DBCommand cmd = db.createCommand();
        cmd.select(T_DEP.getColumns());


        DBReader reader = new DBReader();
        reader.open(cmd, conn);
        return reader.getBeanList(Department.class);
    }

View Full Code Here

        // Iterate through all changed indexes
        DBColumn[] keyColumns = rowset.getKeyColumns();
        for (DBIndex idx : changed)
        {
            // Select all key columns
            DBCommand cmd = rowset.getDatabase().createCommand();
            cmd.select(keyColumns);
            // add constraints
            boolean allNull = true;
            DBColumn[] idxColumns = idx.getColumns();
            for (int i=0; i<idxColumns.length; i++)
            {   // Check if column has changed
                Object value = record.getValue(idxColumns[i]);
                cmd.where(idxColumns[i].is(value));
                if (value!=null)
                    allNull = false;
            }
            // Check whether all constraints are null
            if (allNull)
                continue; 
            // Exclude current record
            if (record.isNew()==false)
            {   // add restriction
                if (keyColumns.length>1)
                {   // Multiple key columns
                    Object value = record.getValue(keyColumns[0]);
                    DBCompareExpr notExpr = keyColumns[0].is(value);
                    for (int i=1; i<keyColumns.length; i++)
                    {   // Check if column has changed
                        cmd.where(keyColumns[i].is(value));
                    }
                    cmd.where(notExpr.not());
                }
                else
                {   // Single key column
                    Object value = record.getValue(keyColumns[0]);
                    cmd.where(keyColumns[0].isNot(value));
                }
            }
            // Query now
            DBReader reader = new DBReader();
            try {

View Full Code Here

     * 
     * @param queryCmd
     */
    public final void initItems(DBCommand queryCmd, int pageSize)
    {
        DBCommand countCmd = queryCmd.clone();
        initItems(queryCmd, countCmd, 0);
    }

View Full Code Here

        // DBReader
        BeanListTableInfo lti = (BeanListTableInfo) getTableInfo();
        DBReader r = new DBReader();
        try
        { // Check command
            DBCommand queryCmd = lti.getQueryCmd();
            if (queryCmd == null)
                throw new ObjectNotValidException(this);


            boolean loadPageFromPosition = lti.isValid() && lti.isAllowPagination();
            lti.setValid(false);


            if (lti.isSortOrderChanged())
            { // Set Sort order
                setOrderBy(queryCmd);
                lti.setSortOrderChanged(false);
            }
            
            int position = 0;
            int skipRows = 0;
            int maxItems = maxItemCount;
            if (loadPageFromPosition)
            {   // detect position
                position = lti.getPosition();
                if (position > lti.getItemCount() - lti.getPageSize())
                { // position > count of entries is not possible, set to max
                    position = lti.getItemCount() - lti.getPageSize();
                }
                if (position < 0)
                { // position < 0 is not possible, set to 0
                    position = 0;
                }
                // maxItems
                maxItems = lti.getPageSize();
                skipRows = position;
                // constraint
                queryCmd.clearLimit();
                DBDatabaseDriver driver = queryCmd.getDatabase().getDriver(); 
                if (driver.isSupported(DBDriverFeature.QUERY_LIMIT_ROWS))
                {   // let the database limit the rows
                    if (driver.isSupported(DBDriverFeature.QUERY_SKIP_ROWS))
                    {   // let the database skip the rows
                        queryCmd.skipRows(skipRows);
                        skipRows = 0;
                    }
                    queryCmd.limitRows(skipRows+maxItems);
                }
            }


            // DBReader.open must always be surrounded with a try {} finally {} block!
            r.open(queryCmd, getConnection(queryCmd));

View Full Code Here


    @Override
    public DBCommand getItemQueryCmd()
    {
        BeanListTableInfo lti = (BeanListTableInfo) getTableInfo();
        DBCommand cmd = lti.getQueryCmd().clone();
        
        Set<Object[]> items = getSelectedItemKeys();
        if (items.size()>0)
        {
            DBColumn[] pk = rowset.getKeyColumns();
            DBColumnExpr keyExpr = pk[0];
           
            for (int i=1; i<pk.length; i++)
            {
                keyExpr = keyExpr.append(pk[i]);
            }
            
            String[] keys = new String[items.size()];
            int i = 0;


            for (Object[] item : items)
            {
                keys[i++] = StringUtils.arrayToString(item, "");
            }
            if (isInvertSelection())
                cmd.where(keyExpr.notIn(keys));
            else
                cmd.where(keyExpr.in(keys));
        }
        // clear previous settings without the where causes
        cmd.clearSelect();
        cmd.clearGroupBy();
        // clear skip and limit!
        cmd.clearLimit(); 
        return cmd;
    }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

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

com.vsked.empire.samples.db.SampleApp

org.apache.empire.data.DataType

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

org.apache.empire.db.expr.compare.DBCompareExpr

org.apache.empire.db.expr.join.DBJoinExpr

org.apache.empire.db.expr.join.DBJoinExprEx

org.apache.empire.db.expr.set.DBSetExpr

org.apache.empire.db.mssql.DBDatabaseDriverMSSqlTest

org.apache.empire.db.oracle.DBCommandOracle

org.apache.empire.db.oracle.DBDatabaseDriverOracle

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.