You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Cedric Hurst <ce...@gmail.com> on 2007/12/19 08:43:40 UTC
Case-insensitive LIKE comparison in JPQL
I'm trying to do a relatively simple comparison of an named parameter to a
column in a database using LIKE. The following JPQL code works, but the
LIKE comparison is case-sensitive:
SELECT u FROM User u WHERE u.username like :username
What I'm looking for is something along the lines of Hibernate's ILIKE, so
that if I have a named parameter "Joe", it will match a column in the
database with a value of "joe", "JOE", or "Joe".
If this were like plain-old SQL, I would imagine I could simply wrap both
u.username and :username in the LOWER() function:
SELECT u FROM User u WHERE lower(u.username) like lower(:username)
But OpenJPA doesn't seem to like that:
<openjpa-1.0.0-r5715:6560 nonfatal user error>
org.apache.openjpa.persistence.ArgumentException: Encountered "lower ( u .
username ) like lower" at character 28, but expected: ["(", ")", "+", "-",
".", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY",
"AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT", "COUNT",
"CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC",
"DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING",
"IN", "INNER", "IS", "JOIN", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE",
"LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT",
"OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT",
"SUBSTRING", "SUM", "TRAILING", "TRIM", "UPDATE", "UPPER", "WHERE",
<BOOLEAN_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>,
<STRING_LITERAL>].
at
org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9285)
at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9162)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1858)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1836)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1702)
at
org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1664)
at org.apache.openjpa.kernel.jpql.JPQL.where_clause(JPQL.java:1511)
at org.apache.openjpa.kernel.jpql.JPQL.select_statement(JPQL.java:90)
at org.apache.openjpa.kernel.jpql.JPQL.parseQuery(JPQL.java:62)
at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:1659)
... 31 more
Any suggestions?
--
View this message in context: http://www.nabble.com/Case-insensitive-LIKE-comparison-in-JPQL-tp14412491p14412491.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: @TableGenerator usage
Posted by Gilberto C Andrade <gi...@secad.to.gov.br>.
Gilberto C Andrade wrote:
> Hi again!
>
> I have some POJOs with this config (changing name and generator
> attributes. off course!):
> [code]
> @Id
> @TableGenerator(
> name="categoria_id_gerador",
> table="id_gerador",
> pkColumnName="id_nome",
> valueColumnName="id_valor",
> allocationSize=1
> )
> @GeneratedValue(strategy=GenerationType.TABLE,
> generator="categoria_id_gerador")
>
> [/code]
> And in my unit test I do init someone of them:
>
> [code]
> private EntityManagerFactory emf = null;
> private EntityManager em = null;
> private static final String initId = "UPDATE gfi.id_gerador SET
> id_valor = 1 WHERE id_nome = ?";
>
> public InventarioServiceTest() { }
>
> @Before
> public void setUp() {
> emf = Persistence.createEntityManagerFactory("gfi-corePU");
> em = emf.createEntityManager();
> em.getTransaction().begin();
> //init @TableGenerator(id_gerador)
> em.createNativeQuery(initId).setParameter(1,
> "item_id_gerador").executeUpdate();
> em.createNativeQuery(initId).setParameter(1,
> "categoria_id_gerador").executeUpdate();
> em.createNativeQuery(initId).setParameter(1,
> "produto_id_gerador").executeUpdate();
> //Clean tables
> em.createNativeQuery("DELETE FROM gfi.item").executeUpdate();
> em.createNativeQuery("DELETE FROM gfi.produto").executeUpdate();
> em.createNativeQuery("DELETE FROM gfi.categoria").executeUpdate();
> em.createNativeQuery("DELETE FROM
> gfi.unidade_medida").executeUpdate();
> //Necessário para o teste do produto
> em.createNativeQuery("INSERT INTO gfi.categoria (cd_categoria,
> descricao_categoria, dt_cadastro, nome_categoria) "+
> "VALUES (1, 'Vários tipos de roupas.', '2007-12-17
> 15:26:08.586', 'Vestuário')").executeUpdate();
> //Necessário para o teste do item
> em.createNativeQuery("INSERT INTO gfi.produto (cd_produto,
> cd_categoria, dt_cadastro, nome_produto, VERSION, descricao_produto) "+
> "VALUES (1, 1, '2007-12-17 15:26:10.451', 'Calça
> Jeans', 1, 'Calça Jeans estilo sertanejo.') ").executeUpdate();
> em.createNativeQuery("INSERT INTO gfi.unidade_medida
> (cd_unidade_medida, descricao_unidade, VERSION) "+
> "VALUES ('MT', 'Metros', 1) ").executeUpdate();
> em.getTransaction().commit();
> }
> [/code]
> I don't know why, but openjpa doesn't print any trace on the setup() method.
>
> But the SQL generate is totally wrong (on persistRemoveCategoriaTest()):
>
> [debug]
>
> [DEBUG] InventarioServiceTest -
> persistRemoveCategoriaTest - Criação de uma instância da classe Categoria
>
> 1368 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 5311938 executing prepstmnt 30318493 SELECT ID_VALOR FROM id_gerador
> WHERE ID_NOME = ? FOR UPDATE WITH RR [params=(String) DEFAULT]
> 1368 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 5311938 [0 ms] spent
> 1380 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 18012078 executing prepstmnt 3824284 INSERT INTO id_gerador (ID_NOME,
> ID_VALOR) VALUES (?, ?) [params=(String) DEFAULT, (int) 0]
> 1384 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 18012078 [2 ms] spent
> 1388 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 11147203 executing prepstmnt 8331873 SELECT ID_VALOR FROM id_gerador
> WHERE ID_NOME = ? FOR UPDATE WITH RR [params=(String) DEFAULT]
> 1388 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 11147203 [0 ms] spent
> 1420 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 11147203 executing prepstmnt 30170403 UPDATE id_gerador SET ID_VALOR = ?
> WHERE ID_NOME = ? AND ID_VALOR = ? [params=(long) 1, (String) DEFAULT,
> (long) 0]
> 1424 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 11147203 [3 ms] spent
> 1440 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 21995759 executing prepstmnt 31255 SELECT ID_VALOR FROM id_gerador WHERE
> ID_NOME = ? FOR UPDATE WITH RR [params=(String) DEFAULT]
> 1440 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 21995759 [0 ms] spent
> 1440 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 21995759 executing prepstmnt 21470969 UPDATE id_gerador SET ID_VALOR = ?
> WHERE ID_NOME = ? AND ID_VALOR = ? [params=(long) 2, (String) DEFAULT,
> (long) 1]
> 1443 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 21995759 [1 ms] spent
> 1468 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 17741109 executing prepstmnt 17310327 INSERT INTO categoria
> (cd_categoria, descricao_categoria, dt_cadastro, nome_categoria) VALUES
> (?, ?, ?, ?) [params=(int) 1, (String) Vários tipos de sapatos.,
> (Timestamp) 2007-12-19 16:25:39.682, (String) Sapatos]
> 1528 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
> 17741109 [60 ms] spent
> 0 gfi-corePU INFO [main] openjpa.Runtime - Starting OpenJPA
> 1.1.0-SNAPSHOT
> [/debug]
>
> Shouldn't openjpa get the ID from the table id_gerador where the value
> was equal to categoria_id_gerador (for POJO Categoria)? Where does
> DEFAULT come from?
>
>
> My test:
>
> [code]
> @Test
> public void persistRemoveCategoriaTest() {
> log.debug("\npersistRemoveCategoriaTest - Criação de uma
> instância da classe Categoria\n");
> String cNome = "Sapatos";
> String cDescricao = "Vários tipos de sapatos.";
> Categoria c = new Categoria(cNome, cDescricao);
> assertNull("cdCategoria antes do método
> persist:",c.getCdCategoria());
> em.getTransaction().begin();
> em.persist(c);
> em.getTransaction().commit();
> log.debug("\nObjeto pós gravação: \n"+c);
> assertNotNull("cdCategoria pós persist:",c.getCdCategoria());
> }
> @Test
> [/code]
>
I forgot to complete:
select * from GFI.ID_GERADOR
DEFAULT 10
Shouldn't be:
select * from GFI.ID_GERADOR
produto_id_gerador 1
item_id_gerador 1
categoria_id_gerador 2
Gilberto
@TableGenerator usage
Posted by Gilberto C Andrade <gi...@secad.to.gov.br>.
Hi again!
I have some POJOs with this config (changing name and generator
attributes. off course!):
[code]
@Id
@TableGenerator(
name="categoria_id_gerador",
table="id_gerador",
pkColumnName="id_nome",
valueColumnName="id_valor",
allocationSize=1
)
@GeneratedValue(strategy=GenerationType.TABLE,
generator="categoria_id_gerador")
[/code]
And in my unit test I do init someone of them:
[code]
private EntityManagerFactory emf = null;
private EntityManager em = null;
private static final String initId = "UPDATE gfi.id_gerador SET
id_valor = 1 WHERE id_nome = ?";
public InventarioServiceTest() { }
@Before
public void setUp() {
emf = Persistence.createEntityManagerFactory("gfi-corePU");
em = emf.createEntityManager();
em.getTransaction().begin();
//init @TableGenerator(id_gerador)
em.createNativeQuery(initId).setParameter(1,
"item_id_gerador").executeUpdate();
em.createNativeQuery(initId).setParameter(1,
"categoria_id_gerador").executeUpdate();
em.createNativeQuery(initId).setParameter(1,
"produto_id_gerador").executeUpdate();
//Clean tables
em.createNativeQuery("DELETE FROM gfi.item").executeUpdate();
em.createNativeQuery("DELETE FROM gfi.produto").executeUpdate();
em.createNativeQuery("DELETE FROM gfi.categoria").executeUpdate();
em.createNativeQuery("DELETE FROM
gfi.unidade_medida").executeUpdate();
//Necessário para o teste do produto
em.createNativeQuery("INSERT INTO gfi.categoria (cd_categoria,
descricao_categoria, dt_cadastro, nome_categoria) "+
"VALUES (1, 'Vários tipos de roupas.', '2007-12-17
15:26:08.586', 'Vestuário')").executeUpdate();
//Necessário para o teste do item
em.createNativeQuery("INSERT INTO gfi.produto (cd_produto,
cd_categoria, dt_cadastro, nome_produto, VERSION, descricao_produto) "+
"VALUES (1, 1, '2007-12-17 15:26:10.451', 'Calça
Jeans', 1, 'Calça Jeans estilo sertanejo.') ").executeUpdate();
em.createNativeQuery("INSERT INTO gfi.unidade_medida
(cd_unidade_medida, descricao_unidade, VERSION) "+
"VALUES ('MT', 'Metros', 1) ").executeUpdate();
em.getTransaction().commit();
}
[/code]
I don't know why, but openjpa doesn't print any trace on the setup() method.
But the SQL generate is totally wrong (on persistRemoveCategoriaTest()):
[debug]
[DEBUG] InventarioServiceTest -
persistRemoveCategoriaTest - Criação de uma instância da classe Categoria
1368 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
5311938 executing prepstmnt 30318493 SELECT ID_VALOR FROM id_gerador
WHERE ID_NOME = ? FOR UPDATE WITH RR [params=(String) DEFAULT]
1368 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
5311938 [0 ms] spent
1380 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
18012078 executing prepstmnt 3824284 INSERT INTO id_gerador (ID_NOME,
ID_VALOR) VALUES (?, ?) [params=(String) DEFAULT, (int) 0]
1384 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
18012078 [2 ms] spent
1388 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
11147203 executing prepstmnt 8331873 SELECT ID_VALOR FROM id_gerador
WHERE ID_NOME = ? FOR UPDATE WITH RR [params=(String) DEFAULT]
1388 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
11147203 [0 ms] spent
1420 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
11147203 executing prepstmnt 30170403 UPDATE id_gerador SET ID_VALOR = ?
WHERE ID_NOME = ? AND ID_VALOR = ? [params=(long) 1, (String) DEFAULT,
(long) 0]
1424 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
11147203 [3 ms] spent
1440 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
21995759 executing prepstmnt 31255 SELECT ID_VALOR FROM id_gerador WHERE
ID_NOME = ? FOR UPDATE WITH RR [params=(String) DEFAULT]
1440 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
21995759 [0 ms] spent
1440 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
21995759 executing prepstmnt 21470969 UPDATE id_gerador SET ID_VALOR = ?
WHERE ID_NOME = ? AND ID_VALOR = ? [params=(long) 2, (String) DEFAULT,
(long) 1]
1443 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
21995759 [1 ms] spent
1468 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
17741109 executing prepstmnt 17310327 INSERT INTO categoria
(cd_categoria, descricao_categoria, dt_cadastro, nome_categoria) VALUES
(?, ?, ?, ?) [params=(int) 1, (String) Vários tipos de sapatos.,
(Timestamp) 2007-12-19 16:25:39.682, (String) Sapatos]
1528 gfi-corePU TRACE [main] openjpa.jdbc.SQL - <t 27041558, conn
17741109 [60 ms] spent
0 gfi-corePU INFO [main] openjpa.Runtime - Starting OpenJPA
1.1.0-SNAPSHOT
[/debug]
Shouldn't openjpa get the ID from the table id_gerador where the value
was equal to categoria_id_gerador (for POJO Categoria)? Where does
DEFAULT come from?
My test:
[code]
@Test
public void persistRemoveCategoriaTest() {
log.debug("\npersistRemoveCategoriaTest - Criação de uma
instância da classe Categoria\n");
String cNome = "Sapatos";
String cDescricao = "Vários tipos de sapatos.";
Categoria c = new Categoria(cNome, cDescricao);
assertNull("cdCategoria antes do método
persist:",c.getCdCategoria());
em.getTransaction().begin();
em.persist(c);
em.getTransaction().commit();
log.debug("\nObjeto pós gravação: \n"+c);
assertNotNull("cdCategoria pós persist:",c.getCdCategoria());
}
@Test
[/code]
Re: Case-insensitive LIKE comparison in JPQL
Posted by Cedric Hurst <ce...@gmail.com>.
Thanks Michael, your suggestion worked. It might be a little tricky to
ensure that the value is lowered every time a named parameter is provided.
But in my particular case, the Entity bean is only accessed by Session beans
so I have a bit more control over the interaction.
It would be nice to have the ILIKE keyword supported in OpenJPA, though.
Maybe I'll open a JIRA.
Michael Bouschen-2 wrote:
>
> Hi Cedric,
>
> according to the JPQL spec the pattern value following the keyword LIKE
> must be a string literal or a string-valued input parameter. So I assume
> the expression lower(:username) is the problem the OpenJPA query
> compiler is complaining about. As a workaround you could try "lowering"
> the value for the username input parameter yourself before passing it to
> the setParameter call. Then the JPQL would look like:
> SELECT u FROM User u WHERE lower(u.username) LIKE :username
>
> Regards Michael
>
--
View this message in context: http://www.nabble.com/Case-insensitive-LIKE-comparison-in-JPQL-tp14412491p14417658.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Case-insensitive LIKE comparison in JPQL
Posted by Michael Bouschen <mb...@spree.de>.
Hi Cedric,
according to the JPQL spec the pattern value following the keyword LIKE
must be a string literal or a string-valued input parameter. So I assume
the expression lower(:username) is the problem the OpenJPA query
compiler is complaining about. As a workaround you could try "lowering"
the value for the username input parameter yourself before passing it to
the setParameter call. Then the JPQL would look like:
SELECT u FROM User u WHERE lower(u.username) LIKE :username
Regards Michael
> I'm trying to do a relatively simple comparison of an named parameter to a
> column in a database using LIKE. The following JPQL code works, but the
> LIKE comparison is case-sensitive:
>
> SELECT u FROM User u WHERE u.username like :username
>
> What I'm looking for is something along the lines of Hibernate's ILIKE, so
> that if I have a named parameter "Joe", it will match a column in the
> database with a value of "joe", "JOE", or "Joe".
>
> If this were like plain-old SQL, I would imagine I could simply wrap both
> u.username and :username in the LOWER() function:
>
> SELECT u FROM User u WHERE lower(u.username) like lower(:username)
>
> But OpenJPA doesn't seem to like that:
>
> <openjpa-1.0.0-r5715:6560 nonfatal user error>
> org.apache.openjpa.persistence.ArgumentException: Encountered "lower ( u .
> username ) like lower" at character 28, but expected: ["(", ")", "+", "-",
> ".", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY",
> "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT", "COUNT",
> "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC",
> "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING",
> "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE",
> "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT",
> "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT",
> "SUBSTRING", "SUM", "TRAILING", "TRIM", "UPDATE", "UPPER", "WHERE",
> <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>,
> <STRING_LITERAL>].
> at
> org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9285)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9162)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1858)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1836)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1702)
> at
> org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1664)
> at org.apache.openjpa.kernel.jpql.JPQL.where_clause(JPQL.java:1511)
> at org.apache.openjpa.kernel.jpql.JPQL.select_statement(JPQL.java:90)
> at org.apache.openjpa.kernel.jpql.JPQL.parseQuery(JPQL.java:62)
> at
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:1659)
> ... 31 more
>
> Any suggestions?
>
--
Tech@Spree Engineering GmbH Tel.: +49/(0)30/235 520-33
Buelowstr. 66 Fax.: +49/(0)30/217 520-12
10783 Berlin mailto:mbo.tech@spree.de
Geschaeftsfuehrung: Anna-Kristin Proefrock
Sitz Berlin, Amtsgericht Charlottenburg, HRB 564 52