<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0">
<base href="https://hibernate.atlassian.net">
<title>Message Title</title>
</head>
<body class="jira" style="color: #333333; font-family: Arial, sans-serif; font-size: 14px; line-height: 1.429">
<table id="background-table" cellpadding="0" cellspacing="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; background-color: #f5f5f5; border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0" bgcolor="#f5f5f5">
<!-- header here -->
<tbody>
<tr>
<td id="header-pattern-container" style="padding: 0; border-collapse: collapse; padding: 10px 20px">
<table id="header-pattern" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="header-avatar-image-container" valign="top" style="padding: 0; border-collapse: collapse; vertical-align: top; width: 32px; padding-right: 8px" width="32"> <img id="header-avatar-image" class="image_fix" src="https://avatar-cdn.atlassian.com/87d23a18975bbe6295df17bfb5be65dd?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2F87d23a18975bbe6295df17bfb5be65dd%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue" height="32" width="32" border="0" style="border-radius: 3px; vertical-align: top"> </td>
<td id="header-text-container" valign="middle" style="padding: 0; border-collapse: collapse; vertical-align: middle; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px"> <a class="user-hover" rel="michael.stewart" id="email_michael.stewart" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=michael.stewart" style="color:#6c797f;; color: #3b73af; text-decoration: none">Michael Stewart</a> <strong>created</strong> an issue </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="email-content-container" style="padding: 0; border-collapse: collapse; padding: 0 20px">
<table id="email-content-table" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; border-spacing: 0; border-collapse: separate">
<tbody>
<tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<td class="email-content-rounded-top mobile-expand" style="padding: 0; border-collapse: collapse; color: #ffffff; padding: 0 15px 0 16px; height: 15px; background-color: #ffffff; border-left: 1px solid #cccccc; border-top: 1px solid #cccccc; border-right: 1px solid #cccccc; border-bottom: 0; border-top-right-radius: 5px; border-top-left-radius: 5px; height: 10px; line-height: 10px; padding: 0 15px 0 16px; mso-line-height-rule: exactly" height="10" bgcolor="#ffffff"> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff" bgcolor="#ffffff">
<table class="page-title-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td class="page-title-pattern-first-line " style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; padding-top: 10px"> <a href="https://hibernate.atlassian.net/browse/HHH" style="color: #3b73af; text-decoration: none">Hibernate ORM</a> / <a href="https://hibernate.atlassian.net/browse/HHH-12196" style="color: #3b73af; text-decoration: none"><img src="cid:jira-generated-image-avatar-fa357159-5461-468d-b6d2-4b4d3e94517a" height="16" width="16" border="0" align="absmiddle" alt="Improvement" style="vertical-align: text-bottom"></a> <a href="https://hibernate.atlassian.net/browse/HHH-12196" style="color: #3b73af; text-decoration: none">HHH-12196</a> </td>
</tr>
<tr>
<td style="vertical-align: top;; padding: 0; border-collapse: collapse; padding-right: 5px; font-size: 20px; line-height: 30px; mso-line-height-rule: exactly" class="page-title-pattern-header-container"> <span class="page-title-pattern-header" style="font-family: Arial, sans-serif; padding: 0; font-size: 20px; line-height: 30px; mso-text-raise: 2px; mso-line-height-rule: exactly; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-12196" style="color: #3b73af; text-decoration: none">Sybase Dilaect not supporting max result - paging</a> </span> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand wrapper-special-margin" style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff; padding-top: 10px; padding-bottom: 5px" bgcolor="#ffffff">
<table class="keyvalue-table" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Issue Type:</th>
<td class="has-icon" style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-avatar-fa357159-5461-468d-b6d2-4b4d3e94517a" height="16" width="16" border="0" align="absmiddle" alt="Improvement" style="vertical-align: text-bottom"> Improvement </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Affects Versions:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> 5.2.12 </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Assignee:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> Unassigned </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Components:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> hibernate-core </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Created:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> 02/Jan/2018 04:53 AM </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Priority:</th>
<td class="has-icon" style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-static-major-57f09e3c-7993-4b4d-9b84-48de29dd4133" height="16" width="16" border="0" align="absmiddle" alt="Major" style="vertical-align: text-bottom"> Major </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Reporter:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <a class="user-hover" rel="michael.stewart" id="email_michael.stewart" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=michael.stewart" style="color:#6c797f;; color: #3b73af; text-decoration: none">Michael Stewart</a> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand issue-description-container" style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff; padding-top: 5px; padding-bottom: 10px" bgcolor="#ffffff">
<table class="text-paragraph-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 2px">
<tbody>
<tr>
<td class="text-paragraph-pattern-container mobile-resize-text " style="padding: 0; border-collapse: collapse; padding: 0 0 10px"> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0; margin-top: 0">Hi,</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">I am a contributor to EAI Open Source Platform Ikasan (<a href="http://www.ikasan.com/" class="external-link" rel="nofollow" style="color: #3b73af; text-decoration: none">http://www.ikasan.com/</a>).</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">We have a real world implementation of this platform against a Sybase DB with Hibernate as our ORM. We service large amounts of real time data on our bus and we have harvesting and housekeeping processes continually running. We observed locking and blocking spids when under heaving load. We performed a deep dive to discover that Hibernate is not limiting the result set size when issuing queries as follows:</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-java" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
Criteria criteria = session.createCriteria(WiretapFlowEvent.class);
criteria.add(Restrictions.eq(<span class="code-quote" style="color: #009100">"harvested"</span>, <span class="code-keyword" style="color: #000091">false</span>));
criteria.setFirstResult(0);
criteria.setMaxResults(housekeepingBatchSize);
criteria.addOrder(Order.asc(<span class="code-quote" style="color: #009100">"timestamp"</span>));
List<WiretapEvent> wiretaps = criteria.list();
<span class="code-keyword" style="color: #000091">return</span> wiretaps;
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">I have re-mediated this by creating our own Hibernate dialect:</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-java" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
<span class="code-keyword" style="color: #000091">public</span> <span class="code-keyword" style="color: #000091">class </span>IkasanSybaseASE157Dialect <span class="code-keyword" style="color: #000091">extends</span> SybaseASE157Dialect
{
@Override
<span class="code-keyword" style="color: #000091">public</span> <span class="code-object" style="color: #910091">boolean</span> supportsLimit()
{
<span class="code-keyword" style="color: #000091">return</span> <span class="code-keyword" style="color: #000091">true</span>;
}
@Override
<span class="code-keyword" style="color: #000091">public</span> LimitHandler buildLimitHandler(<span class="code-object" style="color: #910091">String</span> sql, RowSelection selection)
{
<span class="code-keyword" style="color: #000091">return</span> <span class="code-keyword" style="color: #000091">new</span> IkasanSybaseASE157LimitHandler(sql, selection);
}
}
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">Along with out own limit handler:</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-java" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
<span class="code-keyword" style="color: #000091">public</span> <span class="code-keyword" style="color: #000091">class </span>IkasanSybaseASE157LimitHandler <span class="code-keyword" style="color: #000091">extends</span> AbstractLimitHandler
{
<span class="code-keyword" style="color: #000091">public</span> IkasanSybaseASE157LimitHandler(<span class="code-object" style="color: #910091">String</span> sql, RowSelection selection)
{
<span class="code-keyword" style="color: #000091">super</span>(sql, selection);
}
@Override
<span class="code-keyword" style="color: #000091">public</span> <span class="code-object" style="color: #910091">String</span> getProcessedSql()
{
<span class="code-object" style="color: #910091">String</span> select = sql.substring(0, sql.indexOf(<span class="code-quote" style="color: #009100">"select"</span>) + <span class="code-quote" style="color: #009100">"select"</span>.length());
<span class="code-object" style="color: #910091">String</span> statement = sql.substring(sql.indexOf(<span class="code-quote" style="color: #009100">"select"</span>) + <span class="code-quote" style="color: #009100">"select"</span>.length(), sql.length());
<span class="code-keyword" style="color: #000091">if</span>(selection.getFirstRow() != <span class="code-keyword" style="color: #000091">null</span> && selection.getMaxRows() != <span class="code-keyword" style="color: #000091">null</span>)
{
<span class="code-keyword" style="color: #000091">return</span> select + <span class="code-quote" style="color: #009100">" top "</span> + (selection.getFirstRow() + selection.getMaxRows()) + statement;
}
<span class="code-keyword" style="color: #000091">else</span> <span class="code-keyword" style="color: #000091">if</span>(selection.getMaxRows() != <span class="code-keyword" style="color: #000091">null</span>)
{
<span class="code-keyword" style="color: #000091">return</span> select + <span class="code-quote" style="color: #009100">" top "</span> + selection.getMaxRows() + statement;
}
<span class="code-keyword" style="color: #000091">else</span>
{
<span class="code-keyword" style="color: #000091">return</span> sql;
}
}
@Override
<span class="code-keyword" style="color: #000091">public</span> <span class="code-object" style="color: #910091">boolean</span> supportsLimit()
{
<span class="code-keyword" style="color: #000091">return</span> <span class="code-keyword" style="color: #000091">true</span>;
}
<span class="code-keyword" style="color: #000091">public</span> <span class="code-object" style="color: #910091">int</span> bindLimitParametersAtStartOfQuery(PreparedStatement statement, <span class="code-object" style="color: #910091">int</span> index) <span class="code-keyword" style="color: #000091">throws</span> SQLException
{
<span class="code-keyword" style="color: #000091">return</span> 0;
}
<span class="code-keyword" style="color: #000091">public</span> <span class="code-object" style="color: #910091">int</span> bindLimitParametersAtEndOfQuery(PreparedStatement statement, <span class="code-object" style="color: #910091">int</span> index) <span class="code-keyword" style="color: #000091">throws</span> SQLException {
<span class="code-keyword" style="color: #000091">return</span> 0;
}
}
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">Is there any reason that this sort of functionality has not been implemented in Hibernate. I realise that Sybase has some limitations and does not support offset. However, i think that my solution goes part way to solving the problem, and I have confirmed that the burden on out database is greatly reduced.</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">Could you please share some thoughts on this? Have I overlooked anything? If not would you consider including this kind of functionality?</p> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff" bgcolor="#ffffff"> <script type="application/ld+json">
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"description": "View Issue",
"potentialAction": {
"@type": "ViewAction",
"target": "https://hibernate.atlassian.net/browse/HHH-12196?inbox=true&",
"name": "View Issue"
},
"publisher": {
"@type": "Organization",
"name": "Atlassian",
"url": "https://www.atlassian.com"
}
}
</script>
<table id="actions-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px">
<tbody>
<tr>
<td id="actions-pattern-container" valign="middle" style="padding: 0; border-collapse: collapse; padding: 10px 0 10px 24px; vertical-align: middle; padding-left: 0">
<table align="left" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td class="actions-pattern-action-icon-container" style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 0; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-12196#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none"> <img class="actions-pattern-action-icon-image" src="cid:jira-generated-image-static-comment-icon-9f3dc176-27ae-4a0c-ae5f-ba2eefdbf1fe" alt="Add Comment" title="Add Comment" height="16" width="16" border="0" style="vertical-align: middle"> </a> </td>
<td class="actions-pattern-action-text-container" style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 4px; padding-left: 5px"> <a href="https://hibernate.atlassian.net/browse/HHH-12196#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none">Add Comment</a> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table> </td>
</tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<tr>
<td class="email-content-rounded-bottom mobile-expand" style="padding: 0; border-collapse: collapse; color: #ffffff; padding: 0 15px 0 16px; height: 5px; line-height: 5px; background-color: #ffffff; border-top: 0; border-left: 1px solid #cccccc; border-bottom: 1px solid #cccccc; border-right: 1px solid #cccccc; border-bottom-right-radius: 5px; border-bottom-left-radius: 5px; mso-line-height-rule: exactly" height="5" bgcolor="#ffffff"> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="footer-pattern" style="padding: 0; border-collapse: collapse; padding: 12px 20px">
<table id="footer-pattern-container" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="footer-pattern-mobile-separated-links" class="mobile-resize-text" width="100%" colspan="2" style="padding: 0; border-collapse: collapse; color: #999999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px"> Get Jira notifications on your phone! Download the Jira Cloud app for <a href="https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail" style="color: #3b73af; text-decoration: none">Android</a> or <a href="https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8" style="color: #3b73af; text-decoration: none">iOS</a>
<hr> </td>
</tr>
<tr>
<td id="footer-pattern-text" class="mobile-resize-text" width="100%" style="padding: 0; border-collapse: collapse; color: #999999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px"> This message was sent by Atlassian Jira <span id="footer-build-information">(v1001.0.0-SNAPSHOT#100075-<span title="3f29be8f978012eb901cb2aa46fd8279aa570499" data-commit-id="3f29be8f978012eb901cb2aa46fd8279aa570499}">sha1:3f29be8</span>)</span> </td>
<td id="footer-pattern-logo-desktop-container" valign="top" style="padding: 0; border-collapse: collapse; padding-left: 20px; vertical-align: top">
<table style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="footer-pattern-logo-desktop-padding" style="padding: 0; border-collapse: collapse; padding-top: 3px; opacity: 0.150"> <img id="footer-pattern-logo-desktop" src="cid:jira-generated-image-static-footer-desktop-logo-5d099886-db65-42ac-923e-9f8ccbada457" alt="Atlassian logo" title="Atlassian logo" width="192" height="24" class="image_fix"> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table>
<img src="https://u4043402.ct.sendgrid.net/wf/open?upn=YNXLe5Uzuivj8jGpuXra04BSJ-2BS7raa1puTAtyVUP-2F0UG25sB-2BHToWWBMcbXwnNUJwIcJxsGGbx9shrvRDPjkwV4YOb-2BgI54MNuHdSpWluKyPBNBak5ohIYy6eqfkTDy3uXhvpet-2FzLP0c-2FvzO55uObA1BBqV83ATwKHYPrbaLaKPQGFsooo2Y9VftC9wfrj5k-2FaC3e0toKf18cHn8IWJvyKGpGl1I-2BsHeMx-2Bfqhpqgq9lTa3UcNDAVNHmOQ3St8IKeUjzPb3s6r-2Bg1bHUubser6rEChFkNBHQWuaOnoachuFmh0JMWKrxmoRvdNhI3ev-2BVxIrQ2N2TnLeAoWkLqgSreHpuVu-2FalhQAP2zY087HCMF6ol5266jc6NRMu3DzCSfRFCfkJpuAzbTfoiwQ2GKkV4Zdr4UGZAcVpxpYXU62YwePa-2BQpObzNY-2BQYlCE2roUuaRsl20HAbiik2jYRvSA-3D-3D" alt="" width="1" height="1" border="0" style="height:1px !important;width:1px !important;border-width:0 !important;margin-top:0 !important;margin-bottom:0 !important;margin-right:0 !important;margin-left:0 !important;padding-top:0 !important;padding-bottom:0 !important;padding-right:0 !important;padding-left:0 !important;"/>
</body>
</html>