Examples with DatastoreAdapter org.datanucleus.store.rdbms.adapter.DatastoreAdapter used on opensource projects

Example 36 with DatastoreAdapter

use of org.datanucleus.store.rdbms.adapter.DatastoreAdapter in project datanucleus-rdbms by datanucleus.

the class StringMatchesMethod method getExpression.

/* (non-Javadoc)
     * @see org.datanucleus.store.rdbms.sql.method.SQLMethod#getExpression(org.datanucleus.store.rdbms.sql.expression.SQLExpression, java.util.List)
     */
public SQLExpression getExpression(SQLStatement stmt, SQLExpression expr, List<SQLExpression> args) {
    if (args == null || args.size() > 2) {
        throw new NucleusException("Incorrect arguments for String.matches(StringExpression)");
    } else if (!(args.get(0) instanceof StringExpression) && !(args.get(0) instanceof ParameterLiteral)) {
        throw new NucleusException("Incorrect arguments for String.matches(StringExpression)");
    }
    SQLExpression likeExpr = args.get(0);
    if (!(likeExpr instanceof StringExpression) && !(likeExpr instanceof CharacterExpression) && !(likeExpr instanceof ParameterLiteral)) {
        throw new NucleusException(Localiser.msg("060003", "like/matches", "StringExpression", 0, "StringExpression/CharacterExpression/ParameterLiteral"));
    }
    SQLExpression escapeExpr = null;
    if (args.size() > 1) {
        escapeExpr = args.get(1);
    }
    if ((likeExpr instanceof StringLiteral || likeExpr instanceof ParameterLiteral) && likeExpr.isParameter()) {
        // Argument as parameter needs translation to use SQL "LIKE" syntax, so has to be embedded as literal
        stmt.getQueryGenerator().useParameterExpressionAsLiteral((SQLLiteral) likeExpr);
    }
    SQLExpressionFactory exprFactory = stmt.getSQLExpressionFactory();
    if (expr instanceof StringLiteral && likeExpr instanceof StringLiteral) {
        // String.matches(String) so evaluate in-memory
        String primary = (String) ((StringLiteral) expr).getValue();
        String pattern = (String) ((StringLiteral) likeExpr).getValue();
        return new BooleanLiteral(stmt, exprFactory.getMappingForType(boolean.class, false), primary.matches(pattern));
    } else if (expr instanceof StringLiteral) {
        return getBooleanLikeExpression(stmt, expr, likeExpr, escapeExpr);
    } else if (expr instanceof StringExpression && likeExpr instanceof StringLiteral) {
        // Convert the pattern to use the regex constructs suitable for the datastore
        String pattern = (String) ((StringLiteral) likeExpr).getValue();
        if (stmt.getQueryGenerator().getQueryLanguage().equalsIgnoreCase(Query.LANGUAGE_JDOQL)) {
            // JDOQL input is in java.lang.String regular expression format, so convert to SQL like
            boolean caseSensitive = false;
            if (pattern.startsWith("(?i)")) {
                caseSensitive = true;
                pattern = pattern.substring(4);
            }
            DatastoreAdapter dba = stmt.getDatastoreAdapter();
            RegularExpressionConverter converter = new RegularExpressionConverter(dba.getPatternExpressionZeroMoreCharacters().charAt(0), dba.getPatternExpressionAnyCharacter().charAt(0), dba.getEscapeCharacter().charAt(0));
            if (caseSensitive) {
                SQLExpression patternExpr = exprFactory.newLiteral(stmt, likeExpr.getJavaTypeMapping(), converter.convert(pattern).toLowerCase());
                return getBooleanLikeExpression(stmt, expr.invoke("toLowerCase", null), patternExpr, escapeExpr);
            }
            SQLExpression patternExpr = exprFactory.newLiteral(stmt, likeExpr.getJavaTypeMapping(), converter.convert(pattern));
            return getBooleanLikeExpression(stmt, expr, patternExpr, escapeExpr);
        }
        SQLExpression patternExpr = exprFactory.newLiteral(stmt, likeExpr.getJavaTypeMapping(), pattern);
        return getBooleanLikeExpression(stmt, expr, patternExpr, escapeExpr);
    } else if (expr instanceof StringExpression) {
        return getExpressionForStringExpressionInput(stmt, expr, likeExpr, escapeExpr);
    } else {
        throw new NucleusException(Localiser.msg("060001", "matches", expr));
    }
}

Example 37 with DatastoreAdapter

use of org.datanucleus.store.rdbms.adapter.DatastoreAdapter in project tests by datanucleus.

the class StoredProcedureTest method testExecuteInputParamAndOutputParam.

public void testExecuteInputParamAndOutputParam() {
    if (vendorID == null) {
        return;
    }
    if (storeMgr instanceof RDBMSStoreManager) {
        DatastoreAdapter dba = ((RDBMSStoreManager) storeMgr).getDatastoreAdapter();
        if (!dba.supportsOption(DatastoreAdapter.STORED_PROCEDURES)) {
            LOG.warn("Database doesnt support stored procedures so ignoring the test");
            return;
        }
    }
    String procName = "DN_PROC_INPUTPARAM_OUTPUTPARAM";
    RDBMSStoreManager rdbmsMgr = (RDBMSStoreManager) storeMgr;
    ManagedConnection mc = rdbmsMgr.getConnectionManager().getConnection(-1);
    try {
        Connection conn = (Connection) mc.getConnection();
        Statement stmt = conn.createStatement();
        // Drop it first
        String dropStmt = "DROP PROCEDURE IF EXISTS " + procName;
        stmt.execute(dropStmt);
        // Create it
        String createStmt = "CREATE PROCEDURE " + procName + "(IN PARAM1 VARCHAR(255), OUT PARAM2 INT) BEGIN " + "SELECT COUNT(*) INTO PARAM2 FROM JPA_AN_PERSON WHERE FIRSTNAME = PARAM1; END";
        stmt.execute(createStmt);
    } catch (SQLException sqle) {
        fail("Exception in drop-create of stored procedure : " + sqle.getMessage());
    } finally {
        mc.close();
    }
    try {
        JPAEntityManager em = (JPAEntityManager) getEM();
        EntityTransaction tx = em.getTransaction();
        try {
            tx.begin();
            Person p1 = new Person(101, "Fred", "Flintstone", "fred.flintstone@warnerbros.com");
            Person p2 = new Person(102, "Fred", "Gravel", "fred.gravel@warnerbros.com");
            em.persist(p1);
            em.persist(p2);
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
        em = (JPAEntityManager) getEM();
        tx = em.getTransaction();
        try {
            tx.begin();
            // Get value to compare against
            Query q = em.createQuery("SELECT COUNT(p) FROM " + Person.class.getName() + " p WHERE p.firstName = 'Fred'");
            Long count = (Long) q.getSingleResult();
            // Execute stored proc and compare
            StoredProcedureQuery spq = em.createStoredProcedureQuery(procName);
            spq.registerStoredProcedureParameter("PARAM1", String.class, ParameterMode.IN);
            spq.registerStoredProcedureParameter("PARAM2", Integer.class, ParameterMode.OUT);
            spq.setParameter("PARAM1", "Fred");
            boolean val = spq.execute();
            assertFalse("Flag for result set returned true but should have been false", val);
            Object paramVal = spq.getOutputParameterValue("PARAM2");
            assertEquals("Output parameter is incorrect", new Integer(count.intValue()), paramVal);
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
    } finally {
        // Cleanup data
        clean(Person.class);
    }
}

Example 38 with DatastoreAdapter

use of org.datanucleus.store.rdbms.adapter.DatastoreAdapter in project tests by datanucleus.

the class StoredProcedureTest method testExecuteWithoutParamsReturningResultSet.

public void testExecuteWithoutParamsReturningResultSet() {
    if (vendorID == null) {
        return;
    }
    if (storeMgr instanceof RDBMSStoreManager) {
        DatastoreAdapter dba = ((RDBMSStoreManager) storeMgr).getDatastoreAdapter();
        if (!dba.supportsOption(DatastoreAdapter.STORED_PROCEDURES)) {
            LOG.warn("Database doesnt support stored procedures so ignoring the test");
            return;
        }
    }
    String procName = "DN_PROC_NOPARAMS_RS";
    RDBMSStoreManager rdbmsMgr = (RDBMSStoreManager) storeMgr;
    ManagedConnection mc = rdbmsMgr.getConnectionManager().getConnection(-1);
    try {
        Connection conn = (Connection) mc.getConnection();
        Statement stmt = conn.createStatement();
        // Drop it first
        String dropStmt = "DROP PROCEDURE IF EXISTS " + procName;
        stmt.execute(dropStmt);
        // Create it
        String createStmt = "CREATE PROCEDURE " + procName + "() BEGIN " + "SELECT COUNT(*) FROM JPA_AN_PERSON; END";
        stmt.execute(createStmt);
    } catch (SQLException sqle) {
        fail("Exception in drop-create of stored procedure : " + sqle.getMessage());
    } finally {
        mc.close();
    }
    try {
        JPAEntityManager em = (JPAEntityManager) getEM();
        EntityTransaction tx = em.getTransaction();
        try {
            tx.begin();
            Person p = new Person(101, "Fred", "Flintstone", "fred.flintstone@warnerbros.com");
            em.persist(p);
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
        em = (JPAEntityManager) getEM();
        tx = em.getTransaction();
        try {
            tx.begin();
            // Get value to compare against
            Query q = em.createQuery("SELECT COUNT(p) FROM " + Person.class.getName() + " p");
            Long count = (Long) q.getSingleResult();
            // Execute stored proc and compare
            StoredProcedureQuery spq = em.createStoredProcedureQuery(procName);
            boolean val = spq.execute();
            assertTrue("Return from execute should have been true", val);
            List results = spq.getResultList();
            assertNotNull("ResultSet was null!", results);
            assertEquals("Number of results was wrong", 1, results.size());
            assertEquals("Result set result was wrong", count, results.get(0));
            assertFalse("More results present but should be the end", spq.hasMoreResults());
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
    } finally {
        // Cleanup data
        clean(Person.class);
    }
}

Example 39 with DatastoreAdapter

use of org.datanucleus.store.rdbms.adapter.DatastoreAdapter in project tests by datanucleus.

the class StoredProcedureTest method testNamedProcWithMultipleResultSet.

public void testNamedProcWithMultipleResultSet() {
    if (vendorID == null) {
        return;
    }
    if (storeMgr instanceof RDBMSStoreManager) {
        DatastoreAdapter dba = ((RDBMSStoreManager) storeMgr).getDatastoreAdapter();
        if (!dba.supportsOption(DatastoreAdapter.STORED_PROCEDURES)) {
            LOG.warn("Database doesnt support stored procedures so ignoring the test");
            return;
        }
    }
    String procName = "DN_PROC_NAMED_RS2";
    RDBMSStoreManager rdbmsMgr = (RDBMSStoreManager) storeMgr;
    ManagedConnection mc = rdbmsMgr.getConnectionManager().getConnection(-1);
    try {
        Connection conn = (Connection) mc.getConnection();
        Statement stmt = conn.createStatement();
        // Drop it first
        String dropStmt = "DROP PROCEDURE IF EXISTS " + procName;
        stmt.execute(dropStmt);
        // Create it
        String createStmt = "CREATE PROCEDURE " + procName + "(IN PARAM1 VARCHAR(255), IN PARAM2 VARCHAR(255)) BEGIN " + "SELECT * FROM JPA_AN_PERSON WHERE FIRSTNAME = PARAM1;" + "SELECT * FROM JPA_AN_ACCOUNT WHERE USERNAME = PARAM2;" + "END";
        stmt.execute(createStmt);
    } catch (SQLException sqle) {
        fail("Exception in drop-create of stored procedure : " + sqle.getMessage());
    } finally {
        mc.close();
    }
    try {
        JPAEntityManager em = (JPAEntityManager) getEM();
        EntityTransaction tx = em.getTransaction();
        try {
            tx.begin();
            Person p = new Person(101, "Fred", "Flintstone", "fred.flintstone@warnerbros.com");
            em.persist(p);
            Account a = new Account();
            a.setUsername("Fred");
            a.setEnabled(true);
            em.persist(a);
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
        em = (JPAEntityManager) getEM();
        tx = em.getTransaction();
        try {
            tx.begin();
            LOG.info(">> Executing stored proc");
            // Execute stored proc and compare
            StoredProcedureQuery spq = em.createNamedStoredProcedureQuery("myNamedSP2");
            spq.setParameter("PARAM1", "Fred");
            spq.setParameter("PARAM2", "Fred");
            boolean val = spq.execute();
            assertTrue("Return from execute should have been true", val);
            List results = spq.getResultList();
            assertNotNull("ResultSet was null!", results);
            assertEquals("Number of results was wrong", 1, results.size());
            for (Object result : results) {
                LOG.info(">> result=" + result);
            }
            assertTrue("More results should be present but werent", spq.hasMoreResults());
            List results2 = spq.getResultList();
            assertNotNull("ResultSet2 was null!", results2);
            assertEquals("Number of results2 was wrong", 1, results2.size());
            for (Object result : results2) {
                LOG.info(">> result=" + result);
            }
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
    } finally {
        // Cleanup data
        clean(Account.class);
        clean(Person.class);
    }
}

Example 40 with DatastoreAdapter

use of org.datanucleus.store.rdbms.adapter.DatastoreAdapter in project tests by datanucleus.

the class StoredProcedureTest method testGetResultListWithoutParamsReturningResultSet.

public void testGetResultListWithoutParamsReturningResultSet() {
    if (vendorID == null) {
        return;
    }
    if (storeMgr instanceof RDBMSStoreManager) {
        DatastoreAdapter dba = ((RDBMSStoreManager) storeMgr).getDatastoreAdapter();
        if (!dba.supportsOption(DatastoreAdapter.STORED_PROCEDURES)) {
            LOG.warn("Database doesnt support stored procedures so ignoring the test");
            return;
        }
    }
    String procName = "DN_PROC_NOPARAMS_RS";
    RDBMSStoreManager rdbmsMgr = (RDBMSStoreManager) storeMgr;
    ManagedConnection mc = rdbmsMgr.getConnectionManager().getConnection(-1);
    try {
        Connection conn = (Connection) mc.getConnection();
        Statement stmt = conn.createStatement();
        // Drop it first
        String dropStmt = "DROP PROCEDURE IF EXISTS " + procName;
        stmt.execute(dropStmt);
        // Create it
        String createStmt = "CREATE PROCEDURE " + procName + "() BEGIN " + "SELECT COUNT(*) FROM JPA_AN_PERSON; END";
        stmt.execute(createStmt);
    } catch (SQLException sqle) {
        fail("Exception in drop-create of stored procedure : " + sqle.getMessage());
    } finally {
        mc.close();
    }
    try {
        JPAEntityManager em = (JPAEntityManager) getEM();
        EntityTransaction tx = em.getTransaction();
        try {
            tx.begin();
            Person p = new Person(101, "Fred", "Flintstone", "fred.flintstone@warnerbros.com");
            em.persist(p);
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
        em = (JPAEntityManager) getEM();
        tx = em.getTransaction();
        try {
            tx.begin();
            // Get value to compare against
            Query q = em.createQuery("SELECT COUNT(p) FROM " + Person.class.getName() + " p");
            Long count = (Long) q.getSingleResult();
            // Execute stored proc and compare
            StoredProcedureQuery spq = em.createStoredProcedureQuery(procName);
            List results = spq.getResultList();
            assertNotNull("ResultSet was null!", results);
            assertEquals("Number of results was wrong", 1, results.size());
            assertEquals("Result set result was wrong", count, results.get(0));
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
    } finally {
        // Cleanup data
        clean(Person.class);
    }
}