JPA SQL查询(JPQL)中的字符串搜索运行时错误 [英] String search runtime error in JPA SQL query (JPQL)

问题描述

当尝试通过JPA从数据库检索表并将其填充到Table SWT小部件(org.eclipse.swt.widgets.Table)时，在执行以下Java代码时遇到转换错误:

When trying to retrieve a table from database through JPA and fill it into the Table SWT widget (org.eclipse.swt.widgets.Table), I get a conversion error while executing the following Java code:

String SQL = String.format("select t.id, t.one, t.two from Test t where t.one like '%%s%' order by t.id",one); //variable one is defined as a parameter of the method (whole method not included here for simplicity)

//this is the code that uses the SQL variable above
EntityManager connection = DB.Connection().createEntityManager(); //the EntityManagerFactory
TypedQuery<Object[]> query = connection.createQuery(SQL, Object[].class); /*gives the same as cast (TypedQuery<Object[]>) */
List<Object[]> tablelist = query.getResultList();
connection.close();

SWTtable.removeAll(); //clears all the elements in the table

for (Object[] row : tablelist) {
    final TableItem item = new TableItem(SWTtable, SWT.None);
    item.setData(row[0]);
    item.setText(0, row[1].toString());
}

运行时第String SQL = String.format("select t.id, t.one, t.two from Test p where p.one like '%%s%' order by p.id",one);行的错误java.util.UnknownFormatConversionException: Conversion = '''

问题在String SQL变量中可能是'%%s%'.我需要保留'%%s%'，以便搜索通过上述查询选择的全部或一条记录.

The problem seams to be '%%s%' in the String SQL variable. I need to retain the '%%s%', in order to search for all or one of the records selected with the query above.

• '%%'-返回所有记录，但没有String参数
• '%s'-如果SQL查询的语句为 正确

• '%%' - returns all records, but with no String parameter
• '%s' - gives a specific record if the statement of the SQL query is correct

其他所有问题，不是我写的字符串'%% s％'，但是我需要保留它或通过保持相同的结果来更改它.我想要的结果是给出所有记录，但是在接收到String参数时返回该特定记录.

Everything else gives a problem, the string '%%s%' was not written by me, but I need to retain it or change it by maintaining the same results. The result I want is give all records, but upon receiving the String parameter return that specific record.

这里可能存在的问题超出了我的JPA知识范围.

What could be the problem here is beyond my scope of JPA knowledge.

推荐答案

我刚刚检查了TypedQuery API，您可以为其设置参数.

I just checked the TypedQuery API, you can set the parameters to it.

TypedQuery<Object[]> query = connection.createQuery("select t.id, t.one, t.two from Test t where t.one like :one order by t.id", Object[].class);
query.setParamter("one", "%" + s + "%");
List<Object[]> tablelist = query.getResultList();

更新:

似乎这两个条件似乎是分开的，就像其他答案所建议的那样，您可以通过生成查询来完成，例如

It seems like those two conditions are seems separate like other answer suggests, you could do by generating your query something like

StringBuilder query = new StringBuilder("select t.id, t.one, t.two from Test t "); 
if (StringUtils.isNotEmpty(s)) { 
    query.append(" where t.one like :one "); 
} 
query.append(" order by t.id");
TypedQuery<Object[]> query = connection.createQuery(query.toString());

这篇关于JPA SQL查询(JPQL)中的字符串搜索运行时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！