我不知道如何使用JPQL中的[AND]和[OR]。

huangapple go评论162阅读模式
英文:

I don't know how to use JPQL [AND] and [OR]

问题

Book.java

@Table(name = "books")
@NamedQueries({
		@NamedQuery(name = "searchNameAndAuthor", query = "SELECT b FROM Book AS b WHERE b.book_name = :book_name and b.author = :author"),
		@NamedQuery(name = "searchNameAndAuthorCount", query = "SELECT count(b) FROM Book AS b WHERE b.book_name = :book_name and b.author = :author")
})
@Entity
public class Book {
	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "isbn_code", length = 17)
	private Integer isbn_code;

	@Column(name = "book_name", length = 30, nullable = false)
	private String book_name;

	@Column(name = "author", length = 20)
	private String author;

	@Column(name = "publisher", length = 20)
	private String publisher;

	@Column(name = "publish_date", length = 8)
	private Date publish_date;

	@Column(name = "stock", length = 4)
	private Integer stock;

search.jsp

<c:import url="/WEB-INF/views/layout/app.jsp">
	<c:param name="content">
		<c:if test="${flush != null}">
			<div id="flush_success">
				<c:out value="${flush}"></c:out>
			</div>
		</c:if>
		<h2>図書館ようこそ</h2>
		<form method="POST" action="<c:url value='/books/search/result' />">
			<label for="book_name">調べたいタイトル</label><br />
			<input type="text" name="book_name" value="${book_name}" /> <br />
			<br />
			<label for="author">著者</label><br />
			<input type="text" name="author" value="${author}" /> <br />
			<br />
			<button type="submit">検索</button>
		</form>
	</c:param>
</c:import>

请注意,上面的翻译已经去除了代码中的HTML和XML标签,只保留了文本内容。如果您需要更多的帮助或其他翻译,请随时提问。

英文:

I made application what it will be able to search book of name and author.I use JPQL to realize it.When I fill two textbox(name of book and author),it search right.But I fill one textbox,it search not found.
How to fix?

Book.java

@Table(name = &quot;books&quot;)
@NamedQueries({
		@NamedQuery(name = &quot;searchNameAndAuthor&quot;, query = &quot;SELECT b FROM Book AS b WHERE b.book_name =:book_name and b.author=:author&quot;),
		@NamedQuery(name = &quot;searchNameAndAuthorCount&quot;, query = &quot;SELECT count(b) FROM Book AS b WHERE b.book_name =:book_name and b.author=:author&quot;)
})
@Entity
public class Book {
	@Id
	@Column(name = &quot;id&quot;)
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = &quot;isbn_code&quot;, length = 17)
	private Integer isbn_code;

	@Column(name = &quot;book_name&quot;, length = 30, nullable = false)
	private String book_name;

	@Column(name = &quot;author&quot;, length = 20)
	private String author;

	@Column(name = &quot;publisher&quot;, length = 20)
	private String publisher;

	@Column(name = &quot;publish_date&quot;, length = 8)
	private Date publish_date;

	@Column(name = &quot;stock&quot;, length = 4)
	private Integer stock;

search.jsp

&lt;c:import url=&quot;/WEB-INF/views/layout/app.jsp&quot;&gt;
	&lt;c:param name=&quot;content&quot;&gt;
		&lt;c:if test=&quot;${flush != null}&quot;&gt;
			&lt;div id=&quot;flush_success&quot;&gt;
				&lt;c:out value=&quot;${flush}&quot;&gt;&lt;/c:out&gt;
			&lt;/div&gt;
		&lt;/c:if&gt;
		&lt;h2&gt;図書館ようこそ&lt;/h2&gt;
		&lt;form method=&quot;POST&quot; action=&quot;&lt;c:url value=&#39;/books/search/result&#39; /&gt;&quot;&gt;
			&lt;label for=&quot;book_name&quot;&gt;調べたいタイトル&lt;/label&gt;&lt;br /&gt; &lt;input type=&quot;text&quot;
				name=&quot;book_name&quot; value=&quot;${book_name}&quot; /&gt; &lt;br /&gt;
			&lt;br /&gt;
			&lt;label for=&quot;author&quot;&gt;著者&lt;/label&gt;&lt;br /&gt; &lt;input type=&quot;text&quot;
				name=&quot;author&quot; value=&quot;${author}&quot; /&gt; &lt;br /&gt;
			&lt;br /&gt;
			&lt;button type=&quot;submit&quot;&gt;検索&lt;/button&gt;
		&lt;/form&gt;
	&lt;/c:param&gt;
&lt;/c:import&gt;

答案1

得分: 1

需要处理空值情况。一种方法是检查参数是否为null,使用OR条件。

SELECT b FROM Book AS b WHERE (:book_name IS NULL OR b.book_name = :book_name) 
                          and (:author IS NULL OR b.author = :author)
英文:

You need to handle the null case also. A way can be checking is the parameter is null as OR condition

SELECT b FROM Book AS b WHERE (:book_name IS NULL OR b.book_name =:book_name) 
                          and (:author IS NULL OR  b.author=:author)

huangapple
  • 本文由 发表于 2020年10月11日 15:57:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/64301795.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定