Package com.mysema.query

Source Code of com.mysema.query.SelectOracleBase

package com.mysema.query;

import java.sql.SQLException;

import com.mysema.query.sql.SQLExpressions;
import com.mysema.query.sql.SQLSerializer;
import com.mysema.query.sql.domain.QEmployee;
import com.mysema.query.sql.oracle.OracleQuery;
import com.mysema.testutil.IncludeIn;
import junit.framework.Assert;
import org.junit.Ignore;
import org.junit.Test;
import static com.mysema.query.Constants.employee;
import static com.mysema.query.Target.ORACLE;
import static com.mysema.query.sql.oracle.OracleGrammar.level;

public class SelectOracleBase extends AbstractBaseTest {

    protected OracleQuery oracleQuery() {
        return new OracleQuery(connection, configuration) {
            @Override
            protected SQLSerializer serialize(boolean forCountRow) {
                SQLSerializer serializer = super.serialize(forCountRow);
                String rv = serializer.toString();
                if (expectedQuery != null) {
                   Assert.assertEquals(expectedQuery, rv.replace('\n', ' '));
                   expectedQuery = null;
                }
                logger.debug(rv);
                return serializer;
            }
        };
    }

    @Test
    @Ignore
    public void ConnectBy() throws SQLException {
        // TODO : come up with a legal case
        oracleQuery().from(employee)
            .where(level.eq(-1))
            .connectBy(level.lt(1000))
            .list(employee.id);
    }

    @Test
    @IncludeIn(ORACLE)
    @SkipForQuoted
    public void ConnectByPrior() throws SQLException {
        expectedQuery =  "select e.ID, e.LASTNAME, e.SUPERIOR_ID " +
                        "from EMPLOYEE e " +
                        "connect by prior e.ID = e.SUPERIOR_ID";
        oracleQuery().from(employee)
            .connectByPrior(employee.id.eq(employee.superiorId))
            .list(employee.id, employee.lastname, employee.superiorId);
    }

    @Test
    @IncludeIn(ORACLE)
    @SkipForQuoted
    public void ConnectByPrior2() throws SQLException {
        if (configuration.getUseLiterals()) return;

        expectedQuery =
                "select e.ID, e.LASTNAME, e.SUPERIOR_ID " +
                "from EMPLOYEE e " +
                "start with e.ID = ? " +
                "connect by prior e.ID = e.SUPERIOR_ID";
        oracleQuery().from(employee)
            .startWith(employee.id.eq(1))
            .connectByPrior(employee.id.eq(employee.superiorId))
            .list(employee.id, employee.lastname, employee.superiorId);
    }

    @Test
    @IncludeIn(ORACLE)
    @SkipForQuoted
    public void ConnectByPrior3() throws SQLException {
        if (configuration.getUseLiterals()) return;

        expectedQuery =
                "select e.ID, e.LASTNAME, e.SUPERIOR_ID " +
                "from EMPLOYEE e " +
                "start with e.ID = ? " +
                "connect by prior e.ID = e.SUPERIOR_ID " +
                "order siblings by e.LASTNAME";
        oracleQuery().from(employee)
            .startWith(employee.id.eq(1))
            .connectByPrior(employee.id.eq(employee.superiorId))
            .orderSiblingsBy(employee.lastname)
            .list(employee.id, employee.lastname, employee.superiorId);
    }

    @Test
    @IncludeIn(ORACLE)
    @SkipForQuoted
    public void ConnectByPrior4() throws SQLException {
        if (configuration.getUseLiterals()) return;

        expectedQuery =
                "select e.ID, e.LASTNAME, e.SUPERIOR_ID " +
                "from EMPLOYEE e " +
                "connect by nocycle prior e.ID = e.SUPERIOR_ID";
        oracleQuery().from(employee)
            .connectByNocyclePrior(employee.id.eq(employee.superiorId))
            .list(employee.id, employee.lastname, employee.superiorId);
    }

    @Test
    @IncludeIn(ORACLE)
    @SkipForQuoted
    public void SumOver() throws SQLException{
//        SQL> select deptno,
//        2  ename,
//        3  sal,
//        4  sum(sal) over (partition by deptno
//        5  order by sal,ename) CumDeptTot,
//        6  sum(sal) over (partition by deptno) SalByDept,
//        7  sum(sal) over (order by deptno, sal) CumTot,
//        8  sum(sal) over () TotSal
//        9  from emp
//       10  order by deptno, sal;
        expectedQuery = "select e.LASTNAME, e.SALARY, " +
            "sum(e.SALARY) over (partition by e.SUPERIOR_ID order by e.LASTNAME, e.SALARY), " +
            "sum(e.SALARY) over (order by e.SUPERIOR_ID, e.SALARY), " +
            "sum(e.SALARY) over () from EMPLOYEE e order by e.SALARY asc, e.SUPERIOR_ID asc";

        oracleQuery().from(employee)
            .orderBy(employee.salary.asc(), employee.superiorId.asc())
            .list(
               employee.lastname,
               employee.salary,
               SQLExpressions.sum(employee.salary).over().partitionBy(employee.superiorId).orderBy(employee.lastname, employee.salary),
               SQLExpressions.sum(employee.salary).over().orderBy(employee.superiorId, employee.salary),
               SQLExpressions.sum(employee.salary).over());

        // shorter version
        QEmployee e = employee;
        oracleQuery().from(e)
            .orderBy(e.salary.asc(), e.superiorId.asc())
            .list(e.lastname, e.salary,
               SQLExpressions.sum(e.salary).over().partitionBy(e.superiorId).orderBy(e.lastname, e.salary),
               SQLExpressions.sum(e.salary).over().orderBy(e.superiorId, e.salary),
               SQLExpressions.sum(e.salary).over());
    }

}
TOP

Related Classes of com.mysema.query.SelectOracleBase

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
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.