public void testSqlFunctionAsAlias() throws Exception {
String functionName = locateAppropriateDialectFunctionNameForAliasTest();
if (functionName == null) {
log.info("Dialect does not list any no-arg functions");
return;
}
log.info("Using function named [" + functionName + "] for 'function as alias' test");
String query =
"select "
+ functionName
+ " from Simple as "
+ functionName
+ " where "
+ functionName
+ ".id = 10";
Session s = openSession();
Transaction t = s.beginTransaction();
Simple simple = new Simple();
simple.setName("Simple 1");
s.save(simple, new Long(10));
t.commit();
s.close();
s = openSession();
t = s.beginTransaction();
List result = s.find(query);
assertTrue(result.size() == 1);
assertTrue(result.get(0) instanceof Simple);
s.delete(result.get(0));
t.commit();
s.close();
}
public void testDialectSQLFunctions() throws Exception {
Session s = openSession();
Transaction t = s.beginTransaction();
Iterator iter = s.iterate("select max(s.count) from Simple s");
if (getDialect() instanceof MySQLDialect) assertTrue(iter.hasNext() && iter.next() == null);
Simple simple = new Simple();
simple.setName("Simple Dialect Function Test");
simple.setAddress("Simple Address");
simple.setPay(new Float(45.8));
simple.setCount(2);
s.save(simple, new Long(10));
// Test to make sure allocating an specified object operates correctly.
assertTrue(
s.find("select new org.hibernate.test.legacy.S(s.count, s.address) from Simple s").size()
== 1);
// Quick check the base dialect functions operate correctly
assertTrue(s.find("select max(s.count) from Simple s").size() == 1);
assertTrue(s.find("select count(*) from Simple s").size() == 1);
if (getDialect() instanceof OracleDialect) {
// Check Oracle Dialect mix of dialect functions - no args (no parenthesis and single arg
// functions
java.util.List rset =
s.find("select s.name, sysdate(), trunc(s.pay), round(s.pay) from Simple s");
assertNotNull("Name string should have been returned", (((Object[]) rset.get(0))[0]));
assertNotNull("Todays Date should have been returned", (((Object[]) rset.get(0))[1]));
assertEquals("trunc(45.8) result was incorrect ", new Float(45), ((Object[]) rset.get(0))[2]);
assertEquals("round(45.8) result was incorrect ", new Float(46), ((Object[]) rset.get(0))[3]);
simple.setPay(new Float(-45.8));
s.update(simple);
// Test type conversions while using nested functions (Float to Int).
rset = s.find("select abs(round(s.pay)) from Simple s");
assertEquals("abs(round(-45.8)) result was incorrect ", new Float(46), rset.get(0));
// Test a larger depth 3 function example - Not a useful combo other than for testing
assertTrue(s.find("select trunc(round(sysdate())) from Simple s").size() == 1);
// Test the oracle standard NVL funtion as a test of multi-param functions...
simple.setPay(null);
s.update(simple);
Integer value =
(Integer)
s.find("select MOD( NVL(s.pay, 5000), 2 ) from Simple as s where s.id = 10").get(0);
assertTrue(0 == value.intValue());
}
if ((getDialect() instanceof HSQLDialect)) {
// Test the hsql standard MOD funtion as a test of multi-param functions...
Integer value =
(Integer) s.find("select MOD(s.count, 2) from Simple as s where s.id = 10").get(0);
assertTrue(0 == value.intValue());
}
s.delete(simple);
t.commit();
s.close();
}
public void testSQLFunctions() throws Exception {
Session s = openSession();
Transaction t = s.beginTransaction();
Simple simple = new Simple();
simple.setName("Simple 1");
s.save(simple, new Long(10));
if (getDialect() instanceof DB2Dialect) {
s.find("from Simple s where repeat('foo', 3) = 'foofoofoo'");
s.find("from Simple s where repeat(s.name, 3) = 'foofoofoo'");
s.find("from Simple s where repeat( lower(s.name), 3 + (1-1) / 2) = 'foofoofoo'");
}
assertTrue(s.find("from Simple s where upper( s.name ) ='SIMPLE 1'").size() == 1);
if (!(getDialect() instanceof HSQLDialect)) {
assertTrue(
s.find(
"from Simple s where not( upper( s.name ) ='yada' or 1=2 or 'foo'='bar' or not('foo'='foo') or 'foo' like 'bar' )")
.size()
== 1);
}
if (!(getDialect() instanceof MySQLDialect)
&& !(getDialect() instanceof SybaseDialect)
&& !(getDialect() instanceof MckoiDialect)
&& !(getDialect() instanceof InterbaseDialect)
&& !(getDialect()
instanceof TimesTenDialect)) { // My SQL has a funny concatenation operator
assertTrue(
s.find("from Simple s where lower( s.name || ' foo' ) ='simple 1 foo'").size() == 1);
}
if ((getDialect() instanceof SybaseDialect)) {
assertTrue(
s.find("from Simple s where lower( s.name + ' foo' ) ='simple 1 foo'").size() == 1);
}
if ((getDialect() instanceof MckoiDialect) || (getDialect() instanceof TimesTenDialect)) {
assertTrue(
s.find("from Simple s where lower( concat(s.name, ' foo') ) ='simple 1 foo'").size()
== 1);
}
Simple other = new Simple();
other.setName("Simple 2");
other.setCount(12);
simple.setOther(other);
s.save(other, new Long(20));
// s.find("from Simple s where s.name ## 'cat|rat|bag'");
assertTrue(s.find("from Simple s where upper( s.other.name ) ='SIMPLE 2'").size() == 1);
assertTrue(s.find("from Simple s where not ( upper( s.other.name ) ='SIMPLE 2' )").size() == 0);
assertTrue(
s.find(
"select distinct s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2")
.size()
== 1);
assertTrue(
s.find(
"select s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2 order by s.other.count")
.size()
== 1);
Simple min = new Simple();
min.setCount(-1);
s.save(min, new Long(30));
if (!(getDialect() instanceof MySQLDialect)
&& !(getDialect() instanceof HSQLDialect)) { // My SQL has no subqueries
assertTrue(
s.find("from Simple s where s.count > ( select min(sim.count) from Simple sim )").size()
== 2);
t.commit();
t = s.beginTransaction();
assertTrue(
s.find(
"from Simple s where s = some( select sim from Simple sim where sim.count>=0 ) and s.count >= 0")
.size()
== 2);
assertTrue(
s.find(
"from Simple s where s = some( select sim from Simple sim where sim.other.count=s.other.count ) and s.other.count > 0")
.size()
== 1);
}
Iterator iter =
s.iterate("select sum(s.count) from Simple s group by s.count having sum(s.count) > 10");
assertTrue(iter.hasNext());
assertTrue(new Integer(12).equals(iter.next()));
assertTrue(!iter.hasNext());
if (!(getDialect() instanceof MySQLDialect)) {
iter = s.iterate("select s.count from Simple s group by s.count having s.count = 12");
assertTrue(iter.hasNext());
}
s.iterate(
"select s.id, s.count, count(t), max(t.date) from Simple s, Simple t where s.count = t.count group by s.id, s.count order by s.count");
Query q = s.createQuery("from Simple s");
q.setMaxResults(10);
assertTrue(q.list().size() == 3);
q = s.createQuery("from Simple s");
q.setMaxResults(1);
assertTrue(q.list().size() == 1);
q = s.createQuery("from Simple s");
assertTrue(q.list().size() == 3);
q = s.createQuery("from Simple s where s.name = ?");
q.setString(0, "Simple 1");
assertTrue(q.list().size() == 1);
q = s.createQuery("from Simple s where s.name = ? and upper(s.name) = ?");
q.setString(1, "SIMPLE 1");
q.setString(0, "Simple 1");
q.setFirstResult(0);
assertTrue(q.iterate().hasNext());
q =
s.createQuery(
"from Simple s where s.name = :foo and upper(s.name) = :bar or s.count=:count or s.count=:count + 1");
q.setParameter("bar", "SIMPLE 1");
q.setString("foo", "Simple 1");
q.setInteger("count", 69);
q.setFirstResult(0);
assertTrue(q.iterate().hasNext());
q = s.createQuery("select s.id from Simple s");
q.setFirstResult(1);
q.setMaxResults(2);
iter = q.iterate();
int i = 0;
while (iter.hasNext()) {
assertTrue(iter.next() instanceof Long);
i++;
}
assertTrue(i == 2);
q = s.createQuery("select all s, s.other from Simple s where s = :s");
q.setParameter("s", simple);
assertTrue(q.list().size() == 1);
q = s.createQuery("from Simple s where s.name in (:name_list) and s.count > :count");
HashSet set = new HashSet();
set.add("Simple 1");
set.add("foo");
q.setParameterList("name_list", set);
q.setParameter("count", new Integer(-1));
assertTrue(q.list().size() == 1);
ScrollableResults sr = s.createQuery("from Simple s").scroll();
sr.next();
sr.get(0);
sr.close();
s.delete(other);
s.delete(simple);
s.delete(min);
t.commit();
s.close();
}