Example usage for org.hibernate.jdbc Work Work

List of usage examples for org.hibernate.jdbc Work Work

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.jdbc.*
  5. Work
  6. Work

Introduction

In this page you can find the example usage for org.hibernate.jdbc Work Work.

Prototype

Work

Source Link

Usage

From source file:Hibernate4Access.java

License:Apache License

public void runScript() {
    getSession().doWork(new Work() {
        public void execute(Connection conn) throws SQLException {
            if (JdbcHelper.isExec(conn)) {
                return;
            }// w  ww  .  j a  v a2s.c o  m
            try {
                String databaseType = JdbcHelper.getDatabaseType(conn);
                String schema = "db/schema-" + databaseType + ".sql";
                ScriptRunner runner = new ScriptRunner(conn, true);
                runner.runScript(schema);
            } catch (Exception e) {
                throw new SnakerException(e);
            }
        }
    });
}

From source file:alma.acs.tmcdb.TestPojosCascading.java

License:Open Source License

private void createDB() throws HibernateUtilException {

    String url = hibernateUtil.getConfiguration().getProperty("hibernate.connection.url");

    if (url.contains("oracle")) {
        return;/*  w ww . j  a v a2 s .c  om*/
    }
    hibernateUtil.beginTransaction();
    hibernateUtil.getSession().doWork(new Work() {
        public void execute(Connection conn) throws SQLException {
            conn.setAutoCommit(true);
            runScriptFile(CREATE_TMCDB_SWCORE, conn);
            runScriptFile(CREATE_TMCDB_SWEXT, conn);
        }
    });
    hibernateUtil.commitTransaction();
}

From source file:alma.acs.tmcdb.TestPojosCascading.java

License:Open Source License

private void dropDB() throws HibernateUtilException {

    String url = hibernateUtil.getConfiguration().getProperty("hibernate.connection.url");

    if (!url.contains("mem")) {
        hibernateUtil.beginTransaction();
        hibernateUtil.getSession().doWork(new Work() {
            public void execute(Connection conn) throws SQLException {
                conn.setAutoCommit(true);
                runScript("delete from event;" + "delete from eventchannel;" + "delete from baciproperty;"
                        + "delete from component;" + "delete from componenttype;"
                        + "delete from namedloggerconfig;" + "delete from loggingconfig;"
                        + "delete from eventchannel;" + "delete from computer;" + "delete from networkdevice;"
                        + "delete from configuration", conn);
            }//from  ww w.  j ava2  s  . c  o  m
        });
        hibernateUtil.commitTransaction();
        return;
    }
    hibernateUtil.beginTransaction();
    hibernateUtil.getSession().doWork(new Work() {
        public void execute(Connection conn) throws SQLException {
            conn.setAutoCommit(true);
            runScriptFile(DROP_TMCDB_SWEXT, conn);
            runScriptFile(DROP_TMCDB_SWCORE, conn);
        }
    });
    hibernateUtil.commitTransaction();
}

From source file:alma.acs.tmcdb.TestPojosPersistence.java

License:Open Source License

public void testShutdown() throws HibernateUtilException {

    String url = hibernateUtil.getConfiguration().getProperty("hibernate.connection.url");
    if (url.contains("file")) {
        hibernateUtil.beginTransaction();
        hibernateUtil.getSession().doWork(new Work() {
            public void execute(Connection conn) throws SQLException {
                runScript("shutdown", conn);
            }/*  www  .  j a v a 2 s .co  m*/
        });
        hibernateUtil.commitTransaction();
    }

}

From source file:alma.acs.tmcdb.TestPojosPersistence.java

License:Open Source License

private void dropDB() throws HibernateUtilException {

    String url = hibernateUtil.getConfiguration().getProperty("hibernate.connection.url");

    if (!url.contains("mem")) {
        hibernateUtil.beginTransaction();
        hibernateUtil.getSession().doWork(new Work() {
            public void execute(Connection conn) throws SQLException {
                conn.setAutoCommit(true);
                runScript("delete from event;" + "delete from eventchannel;" + "delete from baciproperty;"
                        + "delete from snmptrapsink;" + "delete from powerstripsocket;"
                        + "delete from networkpowerstrip;" + "delete from networkdevicesnmpconfig;"
                        + "delete from component;" + "delete from componenttype;"
                        + "delete from namedloggerconfig;" + "delete from loggingconfig;"
                        + "delete from eventchannel;" + "delete from computer;" + "delete from networkdevice;"
                        + "delete from configuration", conn);
            }/*from   w  w w.  j av a2s .co  m*/
        });
        hibernateUtil.commitTransaction();
        return;
    }
    hibernateUtil.beginTransaction();
    hibernateUtil.getSession().doWork(new Work() {
        public void execute(Connection conn) throws SQLException {
            conn.setAutoCommit(true);
            runScriptFile(DROP_TMCDB_SWEXT, conn);
            runScriptFile(DROP_TMCDB_SWCORE, conn);
        }
    });
    hibernateUtil.commitTransaction();
}

From source file:at.treedb.db.hibernate.DAOhibernate.java

License:Open Source License

/**
 * Creates an instance of a Hibernate DAO.
 * /*w ww.java 2  s  .  c  o m*/
 * @param database
 *            database
 * @param dbURL
 *            database URL
 * @param dbUser
 *            database User
 * @param dbPWD
 *            database password
 * @return {@code DAOhibernate} object
 * @throws Exception
 */
public static synchronized DAOhibernate getInstance(DAO.DB database, DDL_STRATEGY dll, String dbURL,
        String dbUser, String dbPWD, PERSISTENCE_CFG_CREATE_STRATEGY creationStrategy) throws Exception {
    if (instance == null) {
        if (dbPWD == null) {
            dbPWD = "";
        }
        String hibernateDialect = null;
        String dbDriver = null;
        if (database == null) {
            throw new Exception("DAOhibernate.getInstance(): Database must be set!");
        }
        switch (database) {
        case H2:
            dbDriver = "org.h2.Driver";
            hibernateDialect = "H2Dialect";
            break;
        case DERBY:
            if (dbURL.contains("jdbc:derby://")) {
                dbDriver = "org.apache.derby.jdbc.ClientDriver";
            } else {
                dbDriver = "org.apache.derby.jdbc.EmbeddedDriver";
            }
            hibernateDialect = "DerbyTenSevenDialect";
            break;
        case HSQLDB:
            dbDriver = "org.hsqldb.jdbc.JDBCDriver";
            hibernateDialect = "HSQLDialect";
            break;
        case MYSQL:
            dbDriver = "com.mysql.jdbc.Driver";
            hibernateDialect = "MySQLDialect";
            break;
        case MARIADB:
            dbDriver = "org.mariadb.jdbc.Driver";
            hibernateDialect = "MySQLDialect";
            break;
        case POSTGRES:
            dbDriver = "org.postgresql.Driver";
            hibernateDialect = "PostgreSQLDialect";
            break;
        case SQLSERVER:
            dbDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            hibernateDialect = "SQLServerDialect";
            break;
        case ORACLE:
            dbDriver = "oracle.jdbc.OracleDriver";
            hibernateDialect = "Oracle10gDialect";
            break;
        case FIREBIRD:
            dbDriver = "org.firebirdsql.jdbc.FBDriver";
            hibernateDialect = "FirebirdDialect";
            break;
        case DB2:
            dbDriver = "com.ibm.db2.jcc.DB2Driver";
            hibernateDialect = "DB2Dialect";
            break;
        case SQLITE:
            dbDriver = "org.sqlite.JDBC";
            hibernateDialect = "SQLiteDialect";
            break;
        default:
            throw new Exception("Missing DB implementation for" + database);
        }
        String hbm2ddl = "none";
        switch (dll) {
        case VALIDATE:
            hbm2ddl = "validate";
            break;
        case UPDATE:
            hbm2ddl = "update";
            break;
        case CREATE:
            hbm2ddl = "create";
            break;

        }
        ReplaceText[] rt = new ReplaceText[] { new ReplaceText("creationDate", (new Date()).toString()),
                new ReplaceText("hbm2ddl", hbm2ddl), new ReplaceText("dbDriver", dbDriver),
                new ReplaceText("dbURL", dbURL), new ReplaceText("dialect", hibernateDialect),
                new ReplaceText("dbUser", dbUser), new ReplaceText("dbPwd", dbPWD), };

        URI hibernateCfg = DAOhibernate.class.getResource("/hibernateTemplate.cfg.xml").toURI();

        String cfgStr = new String(
                Stream.readInputStream(DAOhibernate.class.getResourceAsStream("/hibernateTemplate.cfg.xml")));

        String xml = ReplaceText.replaceText(cfgStr, rt);
        String path = hibernateCfg.toString();
        int skip = 0;
        boolean jar = false;
        if (path.startsWith("file:")) {
            skip = "file:".length();
        } else if (path.startsWith("jar:file:")) {
            skip = "jar:file:".length();
            jar = true;
        }
        path = path.replace("%20", " ");
        path = path.substring(skip);
        path = path.substring(0, path.lastIndexOf("/")) + "/hibernateTemplate.cfg";

        if (jar) {
            path = path.substring(0, path.indexOf("WEB-INF"));

            String tomcat = path + "WEB-INF/classes/META-INF/";
            new File(tomcat).mkdirs();
            Stream.writeString(new File(tomcat + "hibernateTemplate.cfg"), xml);
            path += "hibernateTemplate.cfg";
        }

        Configuration cfg = new Configuration();
        if (creationStrategy == PERSISTENCE_CFG_CREATE_STRATEGY.NO_FILE_CREATION) {
            cfg.configure();
        } else if (creationStrategy == PERSISTENCE_CFG_CREATE_STRATEGY.DEFAULT_LOCATION) {
            File file = new File(path);
            Stream.writeString(file, xml);
            cfg.configure(file);
        } else {
            File tmpFile = File.createTempFile("hibernateTemplate", ".cfg");
            Stream.writeString(tmpFile, xml);
            cfg.configure(tmpFile);
        }

        @SuppressWarnings("rawtypes")
        ArrayList<Class> annotations = null;
        annotations = loadEnitiyClasses(DBentities.getClassesAsList(), cfg);
        serviceRegistry = new StandardServiceRegistryBuilder().applySettings(cfg.getProperties()).build();
        sessionFactory = cfg.buildSessionFactory(serviceRegistry);

        instance = new DAOhibernate(database);
        instance.setConfiguration(cfg);
        DAOhibernate.annotatedClasses = annotations;

        instance.beginTransaction();
        instance.getActualSession().doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                databaseName = connection.getMetaData().getDatabaseProductName();
                databaseVersion = connection.getMetaData().getDatabaseProductVersion();
            }
        });
        instance.endTransaction();

    }
    return instance;
}

From source file:br.com.gartech.nfse.integrador.dao.GenericDAOImpl.java

@SuppressWarnings("unchecked")
public List<T> findByCriteria(DetachedCriteria detachedCriteria, int firstResult, int maxResults) {
    Session session = getSession();//from  w  w w.  j av  a  2  s  . c o  m

    session.doWork(new Work() {
        @Override
        public void execute(Connection cnctn) throws SQLException {
            cnctn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        }
    });

    Criteria criteria = detachedCriteria.getExecutableCriteria(session);
    criteria.setFirstResult(firstResult);
    if (maxResults > 0) {
        criteria.setMaxResults(maxResults);
    }

    //        criteria.setLockMode(LockMode.READ);  //linha adicionada para no fazer LOCK nas consulta j que  apenas consulta.
    List<T> result = criteria.list();
    return result;
}

From source file:br.gov.jfrj.siga.dp.dao.CpDao.java

License:Open Source License

/**
 * Importa logins e senhas do antigo esquema ACESSO_TOMCAT
 * /*  w w  w.  ja v  a2  s .co m*/
 * @return
 * @throws SQLException
 * @throws NumberFormatException
 * @throws SQLException
 * @throws BadPaddingException
 * @throws IllegalBlockSizeException
 * @throws NoSuchPaddingException
 * @throws NoSuchAlgorithmException
 * @throws InvalidKeyException
 * @throws AplicacaoException
 */
public void importarAcessoTomcat() throws SQLException, InvalidKeyException, NoSuchAlgorithmException,
        NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException, AplicacaoException {
    final Date dt = consultarDataEHoraDoServidor();
    final String s = "SELECT * FROM ACESSO_TOMCAT.USUARIO";

    getSessao().doWork(new Work() {
        public void execute(Connection conn) throws SQLException {
            final PreparedStatement ps = conn.prepareStatement(s);
            try {
                final ResultSet rset = ps.executeQuery();
                CpTipoIdentidade tid = consultar(1, CpTipoIdentidade.class, false);
                while (rset.next()) {
                    final String login = (String) rset.getObject(1);
                    final String senha = (String) rset.getObject(2);
                    Long cpf;
                    try {
                        cpf = ((BigDecimal) rset.getObject(4)).longValue();
                    } catch (NullPointerException e1) {
                        System.out.println("CPF nulo:" + login);
                        continue;
                    }
                    if (!Character.isDigit(login.charAt(2))) {
                        System.out.println("Login sem matricula:" + login);
                        continue;
                    }
                    final long longmatricula = Long.parseLong(login.substring(2));

                    DpPessoa pessoa;
                    try {
                        pessoa = consultarPorCpfMatricula(cpf, longmatricula);
                    } catch (org.hibernate.NonUniqueResultException e) {
                        System.out.println("Mais de um registro retornado:" + login);
                        continue;
                    }
                    if (pessoa == null) {
                        System.out.println("Pessoa nao localizada:" + login);
                        continue;
                    }

                    CpIdentidade id = new CpIdentidade();
                    id.setCpOrgaoUsuario(pessoa.getOrgaoUsuario());
                    id.setCpTipoIdentidade(tid);
                    id.setDpPessoa(pessoa);
                    id.setDscSenhaIdentidade(senha);

                    // BASE64Encoder encoderBase64 = new BASE64Encoder();
                    // String chave =
                    // encoderBase64.encode(id.getDpPessoa().getIdInicial()
                    // .toString().getBytes());
                    // String senhaCripto = encoderBase64.encode(Criptografia
                    // .criptografar(senha, chave));
                    // id.setDscSenhaIdentidadeCripto(senhaCripto);
                    // id.setDscSenhaIdentidadeCriptoSinc(senhaCripto);

                    id.setDtCancelamentoIdentidade(null);
                    id.setDtCriacaoIdentidade(dt);
                    id.setDtExpiracaoIdentidade(null);
                    id.setHisDtFim(null);
                    id.setHisDtIni(dt);
                    // id.setIdCpIdentidade(null);
                    id.setNmLoginIdentidade(login);
                    gravar(id);
                    id.setHisIdIni(id.getIdIdentidade());
                    gravar(id);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                ps.close();
            }
        }
    });

}

From source file:br.gov.jfrj.siga.dp.dao.CpDaoTest.java

License:Open Source License

public static void printSchemaUpdateScript(final SessionFactory sf, final Configuration cfg,
        final Dialect dialect) {
    try {/*from   ww  w  .j  ava 2  s .  com*/
        HibernateUtil.getSessao().doWork(new Work() {
            public void execute(Connection connection) throws SQLException {
                DatabaseMetadata metadata;
                metadata = new DatabaseMetadata(connection, dialect);
                String[] schemaUpdateScript = cfg.generateSchemaUpdateScript(dialect, metadata);
                for (String stmt : schemaUpdateScript) {
                    System.out.println(stmt + ";");
                }
            }
        });

    } catch (HibernateException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:br.gov.jfrj.siga.hibernate.ExDao.java

License:Open Source License

public List consultarPorResponsavel(final DpPessoa o, final DpLotacao lot) throws SQLException {
    try {/*from  w ww .j ava 2 s  .c o  m*/
        final String s = " SELECT ID, DESCR, ORDEM, SUM(C1) C1, SUM(C2) C2 FROM ( " +
        // " -- pes " +
                " SELECT EST.ID_ESTADO_DOC ID, EST.DESC_ESTADO_DOC DESCR,  EST.ORDEM_ESTADO_DOC ORDEM, COUNT(1) C1, 0 C2 "
                + " FROM EX_ESTADO_DOC EST, DP_PESSOA_SIN PES, EX_MOVIMENTACAO MOVR " + " WHERE "
                + " ID_PESSOA_INICIAL = ? " + " AND MOVR.DT_FIM_MOV IS NULL " + " AND NOT MOVR.NUM_VIA IS NULL "
                + " AND NOT MOVR.NUM_VIA = 0 " + " AND MOVR.ID_ESTADO_DOC =  EST.ID_ESTADO_DOC "
                + " AND MOVR.ID_RESP = PES.ID_PESSOA "
                + " AND EST.ID_ESTADO_DOC <> 9 AND EST.ID_ESTADO_DOC <> 10 AND EST.ID_ESTADO_DOC <> 11 AND EST.ID_ESTADO_DOC <> 6 AND EST.ID_ESTADO_DOC <> 8 AND EST.ID_ESTADO_DOC <> 12 AND EST.ID_ESTADO_DOC <> 13 "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " + " UNION "
                + " SELECT EST.ID_ESTADO_DOC ID, EST.DESC_ESTADO_DOC DESCR,  EST.ORDEM_ESTADO_DOC ORDEM, COUNT(1) C1, 0 C2 "
                + " FROM EX_ESTADO_DOC EST, DP_PESSOA_SIN PES, EX_MOVIMENTACAO MOVR " + " WHERE "
                + " ID_PESSOA_INICIAL = ? " + " AND MOVR.DT_FIM_MOV IS NULL " + " AND NOT MOVR.NUM_VIA IS NULL "
                + " AND NOT MOVR.NUM_VIA = 0 " + " AND MOVR.ID_ESTADO_DOC =  EST.ID_ESTADO_DOC "
                + " AND MOVR.ID_ESTADO_DOC = 11 AND MOVR.ID_CADASTRANTE = PES.ID_PESSOA "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " + " UNION "
                + " SELECT EST.ID_ESTADO_DOC ID, EST.DESC_ESTADO_DOC DESCR,  EST.ORDEM_ESTADO_DOC ORDEM, COUNT(1) C1, 0 C2 "
                + " FROM EX_ESTADO_DOC EST, DP_PESSOA_SIN PES, EX_DOCUMENTO DOCR " + " WHERE "
                + " ID_PESSOA_INICIAL = ? " + " AND DOCR.ID_CADASTRANTE = PES.ID_PESSOA "
                + " AND DOCR.DT_FINALIZACAO IS NULL " + " AND EST.ID_ESTADO_DOC = 1 "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " + " UNION "
                + " SELECT -1 ID, 'Em Trnsito' DESCR, 3, COUNT(1) C1, 0 C2 "
                + " FROM EX_ESTADO_DOC EST, DP_PESSOA_SIN PES, EX_MOVIMENTACAO MOVR " + " WHERE "
                + " ID_PESSOA_INICIAL = ? " + " AND MOVR.DT_FIM_MOV IS NULL " + " AND NOT MOVR.NUM_VIA IS NULL "
                + " AND NOT MOVR.NUM_VIA = 0 " + " AND MOVR.ID_ESTADO_DOC =  EST.ID_ESTADO_DOC "
                + " AND (MOVR.ID_SUBSCRITOR = PES.ID_PESSOA) " + " AND EST.ID_ESTADO_DOC = 3 "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " + " UNION "
                + " SELECT -3 ID, 'Em Trnsito Eletrnico' DESCR, 4, COUNT(1) C1, 0 C2 "
                + " FROM EX_ESTADO_DOC EST, DP_PESSOA_SIN PES, EX_MOVIMENTACAO MOVR, EX_DOCUMENTO DOC "
                + " WHERE " + " ID_PESSOA_INICIAL = ? " + " AND MOVR.DT_FIM_MOV IS NULL "
                + " AND NOT MOVR.NUM_VIA IS NULL " + " AND NOT MOVR.NUM_VIA = 0 "
                + " AND MOVR.ID_ESTADO_DOC =  EST.ID_ESTADO_DOC " + " AND (MOVR.ID_SUBSCRITOR = PES.ID_PESSOA) "
                + " AND EST.ID_ESTADO_DOC = 3 " + " AND DOC.FG_ELETRONICO = 'S' "
                + " AND MOVR.ID_DOC = DOC.ID_DOC "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " +
                // " -- lot " +
                " UNION "
                + " SELECT EST.ID_ESTADO_DOC ID, EST.DESC_ESTADO_DOC DESCR,  EST.ORDEM_ESTADO_DOC ORDEM, 0 C1, COUNT(1) C2 "
                + " FROM EX_ESTADO_DOC EST, DP_LOTACAO_SIN LOT, EX_MOVIMENTACAO MOVR " + " WHERE "
                + " ID_LOTACAO_INI = ? " + " AND MOVR.DT_FIM_MOV IS NULL " + " AND NOT MOVR.NUM_VIA IS NULL "
                + " AND NOT MOVR.NUM_VIA = 0 " + " AND MOVR.ID_ESTADO_DOC =  EST.ID_ESTADO_DOC "
                + " AND MOVR.ID_LOTA_RESP = LOT.ID_LOTACAO "
                + " AND EST.ID_ESTADO_DOC <> 9 AND EST.ID_ESTADO_DOC <> 10 AND EST.ID_ESTADO_DOC <> 11 AND EST.ID_ESTADO_DOC <> 6 AND EST.ID_ESTADO_DOC <> 8 AND EST.ID_ESTADO_DOC <> 12 AND EST.ID_ESTADO_DOC <> 13 "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " + " UNION "
                + " SELECT EST.ID_ESTADO_DOC ID, EST.DESC_ESTADO_DOC DESCR,  EST.ORDEM_ESTADO_DOC ORDEM, 0 C1, COUNT(1) C2 "
                + " FROM EX_ESTADO_DOC EST, DP_LOTACAO_SIN LOT, EX_MOVIMENTACAO MOVR " + " WHERE "
                + " ID_LOTACAO_INI = ? " + " AND MOVR.DT_FIM_MOV IS NULL " + " AND NOT MOVR.NUM_VIA IS NULL "
                + " AND NOT MOVR.NUM_VIA = 0 " + " AND MOVR.ID_ESTADO_DOC =  EST.ID_ESTADO_DOC "
                + " AND MOVR.ID_ESTADO_DOC = 11 AND MOVR.ID_LOTA_CADASTRANTE = LOT.ID_LOTACAO "
                + " AND EST.ID_ESTADO_DOC <> 9 AND EST.ID_ESTADO_DOC <> 10 AND EST.ID_ESTADO_DOC <> 11 AND EST.ID_ESTADO_DOC <> 6 AND EST.ID_ESTADO_DOC <> 8 AND EST.ID_ESTADO_DOC <> 12 AND EST.ID_ESTADO_DOC <> 13 "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " + " UNION "
                + " SELECT EST.ID_ESTADO_DOC ID, EST.DESC_ESTADO_DOC DESCR,  EST.ORDEM_ESTADO_DOC ORDEM, 0 C1, COUNT(1) C2 "
                + " FROM EX_ESTADO_DOC EST, DP_LOTACAO_SIN LOT, EX_DOCUMENTO DOCR " + " WHERE "
                + " ID_LOTACAO_INI = ? " + " AND DOCR.ID_LOTA_CADASTRANTE = LOT.ID_LOTACAO "
                + " AND DOCR.DT_FINALIZACAO IS NULL " + " AND EST.ID_ESTADO_DOC = 1 "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " + " UNION "
                + " SELECT -1 ID, 'Em Trnsito' DESCR, 3, 0 C1, COUNT(1) C2 "
                + " FROM EX_ESTADO_DOC EST, DP_LOTACAO_SIN LOT, EX_MOVIMENTACAO MOVR " + " WHERE "
                + " ID_LOTACAO_INI = ? " + " AND MOVR.DT_FIM_MOV IS NULL " + " AND NOT MOVR.NUM_VIA IS NULL "
                + " AND NOT MOVR.NUM_VIA = 0 " + " AND MOVR.ID_ESTADO_DOC =  EST.ID_ESTADO_DOC "
                + " AND (MOVR.ID_LOTA_SUBSCRITOR = LOT.ID_LOTACAO) " + " AND EST.ID_ESTADO_DOC = 3 "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " " + " UNION "
                + " SELECT -3 ID, 'Em Trnsito Eletrnico' DESCR, 4, 0 C1, COUNT(1) C2 "
                + " FROM EX_ESTADO_DOC EST, DP_LOTACAO_SIN LOT, EX_MOVIMENTACAO MOVR, EX_DOCUMENTO DOC "
                + " WHERE " + " ID_LOTACAO_INI = ? " + " AND MOVR.DT_FIM_MOV IS NULL "
                + " AND NOT MOVR.NUM_VIA IS NULL " + " AND NOT MOVR.NUM_VIA = 0 "
                + " AND MOVR.ID_ESTADO_DOC =  EST.ID_ESTADO_DOC "
                + " AND (MOVR.ID_LOTA_SUBSCRITOR = LOT.ID_LOTACAO) " + " AND EST.ID_ESTADO_DOC = 3 "
                + " AND DOC.FG_ELETRONICO = 'S' " + " AND MOVR.ID_DOC = DOC.ID_DOC "
                + " GROUP BY EST.ID_ESTADO_DOC, EST.DESC_ESTADO_DOC,  EST.ORDEM_ESTADO_DOC " + " "
                + " ) GROUP BY ID, DESCR, ORDEM ORDER BY ORDEM";

        final List result = new ArrayList<Object[]>();
        getSessao().doWork(new Work() {
            @Override
            public void execute(Connection conn) throws SQLException {
                PreparedStatement psBlob = conn.prepareStatement(s);
                ResultSet rset = null;

                try {
                    psBlob.setLong(1, o.getIdPessoaIni());
                    psBlob.setLong(2, o.getIdPessoaIni());
                    psBlob.setLong(3, o.getIdPessoaIni());
                    psBlob.setLong(4, o.getIdPessoaIni());
                    psBlob.setLong(5, o.getIdPessoaIni());
                    psBlob.setLong(6, lot.getIdLotacaoIni());
                    psBlob.setLong(7, lot.getIdLotacaoIni());
                    psBlob.setLong(8, lot.getIdLotacaoIni());
                    psBlob.setLong(9, lot.getIdLotacaoIni());
                    psBlob.setLong(10, lot.getIdLotacaoIni());

                    final String j = psBlob.toString();
                    rset = psBlob.executeQuery();

                    while (rset.next()) {
                        final Object[] ao = new Object[4];
                        ao[0] = rset.getObject(1);
                        ao[1] = rset.getObject(2);
                        ao[2] = rset.getObject(4);
                        ao[3] = rset.getObject(5);
                        result.add(ao);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    if (!psBlob.isClosed())
                        psBlob.close();
                    if (!rset.isClosed())
                        rset.close();
                }
            }
        });

        return result;

        /*
         * final Query query = getSessao().getNamedQuery(
         * "consultarPorResponsavelSQL"); query.setLong("pessoa",
         * o.getIdPessoaIni()); query.setLong("lotacao",
         * o.getLotacao().getIdLotacaoIni());
         * 
         * return query.list();
         */
    } catch (final NullPointerException e) {
        return null;
    }
}