cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From conflue...@apache.org
Subject [CONF] Apache Cayenne Documentation: Scripting SQLTemplate (page edited)
Date Sun, 17 Jun 2007 07:34:00 GMT
<html>
<head>
    <base href="http://cwiki.apache.org/confluence" />
    <style type="text/css">
    <!--
    body, p, td, table, tr, .bodytext, .stepfield {
	font-family: Verdana, arial, sans-serif;
	font-size: 11px;
	line-height: 16px;
	color: #000000;
	font-weight: normal;
}
#PageContent {
	text-align: left;
	background-color: #fff;
	padding: 0px;
	margin: 0px;
    padding-bottom:20px;
}
/*
** when this stylesheet is used for the Tiny MCE Wysiwyg editor's edit area, we can't
** use an id=PageContent or class=wiki-content, so we must
** set the body style to that used for PageContent, and p to that used for wiki-content.
*/

body {
	margin: 0px;
	padding: 0px;
	text-align: center;
    background-color: #f0f0f0;
}

@media print {

body {
    background-color: #fff;
}

}

.monospaceInput {
    font:12px monospace
}

.wiki-content p, .commentblock p {
    margin: 16px 0px 16px 0px;
    padding: 0px;
}

.wiki-content-preview {
    padding: 5px;
    border-left: 1px solid #3c78b5;
    border-right: 1px solid #3c78b5;
}

ul, ol {
    margin-top: 2px;
    margin-bottom: 2px;
    padding-top: 0px;
    padding-bottom: 0px;
}

pre {
    padding: 0px;
    margin-top: 5px;
    margin-left: 15px;
    margin-bottom: 5px;
    margin-right: 5px;
    text-align: left;
}

.helpheading {
    font-weight: bold;
    background-color: #D0D9BD;
        border-bottom: 1px solid #3c78b5;
        padding: 4px 4px 4px 4px;
        margin: 0px;
        margin-top: 10px;
}
.helpcontent {
        padding: 4px 4px 20px 4px;
    background-color: #f5f7f1;
}

.code {
 	border: 1px dashed #3c78b5;
    font-size: 11px;
	font-family: Courier;
    margin: 10px;
	line-height: 13px;
}

.focusedComment {
    background: #ffffce;
}

.commentBox, .focusedComment {
    padding: 10px;
    margin: 5px 0 5px 0;
    border: 1px #bbb solid;
}

.codeHeader {
    background-color: #f0f0f0;
 	border-bottom: 1px dashed #3c78b5;
    padding: 3px;
	text-align: center;
}

.codeContent {
    text-align: left;
    background-color: #f0f0f0;
    padding: 3px;
}

.preformatted {
 	border: 1px dashed #3c78b5;
    font-size: 11px;
	font-family: Courier;
    margin: 10px;
	line-height: 13px;
}

.preformattedHeader {
    background-color: #f0f0f0;
 	border-bottom: 1px dashed #3c78b5;
    padding: 3px;
	text-align: center;
}

.preformattedContent {
    background-color: #f0f0f0;
    padding: 3px;
}

.panel {
 	border: 1px dashed #3c78b5;
    margin: 10px;
    margin-top: 0px;
}

.panelHeader {
    background-color: #f0f0f0;
 	border-bottom: 1px dashed #3c78b5;
    padding: 3px;
	text-align: center;
}

.panelContent {
    background-color: #f0f0f0;
    padding: 5px;
}

.anonymousAlert {
    background-color: #f0f0f0;
 	border: 1px dashed red;
    font-size: 11px;
    padding: 10px 5px 10px 5px;
    margin: 4px;
	line-height: 13px;
}

.lockAlert {
    background-color: #f0f0f0;
    width: 50%;
 	border: 1px dashed red;
    font-size: 11px;
    padding: 10px 5px 10px 5px;
    margin: 4px;
	line-height: 13px;
}


.code-keyword {
  color: #000091;
  background-color: inherit;
}

.code-object {
  color: #910091;
  background-color: inherit;
}

.code-quote {
  color: #009100;
  background-color: inherit;
}

.code-comment {
  color: #808080;
  background-color: inherit;
}


.code-xml .code-keyword {
  color: inherit;
  font-weight: bold;
}

.code-tag {
  color: #000091;
  background-color: inherit;
}

.breadcrumbs {
    background-color: #f0f0f0;
 	border-color: #3c78b5;
	border-width: 1px 0px 1px 0px;
	border-style: solid;
    font-size: 11px;
    padding: 3px 0px 3px 0px;
}

.navmenu {
    border: 1px solid #ccc;
}

.menuheading {
    font-weight: bold;
    background-color: #f0f0f0;
 	border-bottom: 1px solid #3c78b5;
	padding: 4px 4px 2px 4px;
}

.menuitems {
	padding: 4px 4px 20px 4px;
}

.rightpanel {
    border-left: 1px solid #ccc;
    border-bottom: 1px solid #ccc;
}

#helpheading {
    text-align: left;
    font-weight: bold;
    background-color: #D0D9BD;
 	border-bottom: 1px solid #3c78b5;
	padding: 4px 4px 4px 4px;
	margin: 0px;
}
#helpcontent {
	padding: 4px 4px 4px 4px;
    background-color: #f5f7f1;
}
.helptab-unselected {
    font-weight: bold;
	padding: 5px;
    background-color: #f5f7f1;
}
.helptab-selected {
    font-weight: bold;
    background-color: #D0D9BD;
	padding: 5px;
}
.helptabs {
    margin: 0px;
    background-color: #f5f7f1;
	padding: 5px;
}
.infopanel-heading {
    font-weight: bold;
	padding: 4px 0px 2px 0px;
}

.pagebody {
}

.pageheader {
	padding: 5px 5px 5px 0px;
 	border-bottom: 1px solid #3c78b5;
}

.pagetitle {
	font-size: 22px;
	font-weight: bold;
	font-family: Arial, sans-serif;
	color: #003366;
}

.newpagetitle {
    color: #ccc !important;
}

.steptitle {
	font-size: 18px;
	font-weight: bold;
	font-family: Arial, sans-serif;
	color: #003366;
	margin-bottom: 7px;
}

.substeptitle {
    font-size: 12px;
    font-weight: bold;
    font-family: Arial, sans-serif;
    color: #003366;
    margin: 2px 4px 4px 4px;
    padding: 2px 4px 1px 4px;
}

.stepdesc {
    font-family: Verdana, arial, sans-serif;
	font-size: 11px;
	line-height: 16px;
	font-weight: normal;
    color: #666666;
    margin-top: 7px;
    margin-bottom: 7px;
}

.steplabel {
    font-weight: bold;
    margin-right: 4px;
    color: black;
    float: left;
    width: 15%;
    text-align: right;
}

.stepfield {
    background: #f0f0f0;
    padding: 5px;
}

.submitButtons{
    margin-top:5px;
    text-align:right;
}

.formtitle {
	font-size: 12px;
	font-weight: bold;
	font-family: Arial, sans-serif;
	color: #003366;
}

.sectionbottom {
    border-bottom: 1px solid #3c78b5;
}

.topRow {
    border-top: 2px solid #3c78b5;
}

.tabletitle {
	font-size: 14px;
	font-weight: bold;
	font-family: Arial, sans-serif;
    padding: 3px 0px 2px 0px;
    margin: 8px 4px 2px 0px;
	color: #003366;
	border-bottom: 2px solid #3c78b5;
}
.pagesubheading {
    color: #666666;
    font-size: 10px;
    padding: 0px 0px 5px 0px;
}

HR {
	color: 3c78b5;
	height: 1;
}

A:link, A:visited, A:active, A:hover {
	color: #003366;
}

h1 A:link, h1 A:visited, h1 A:active {
	text-decoration: none;
}

h1 A:hover {
    border-bottom: 1px dotted #003366;
}

.wiki-content > :first-child, .commentblock > :first-child {
    margin-top: 3px;
}

.logocell {
    padding: 10px;
}

input {
	font-family: verdana, geneva, arial, sans-serif;
	font-size: 11px;
	color: #000000;
}

textarea, textarea.editor {
	font-family: verdana, geneva, arial, sans-serif;
	font-size: 11px;
	color: #333333;
}

/* use logoSpaceLink instead.
.spacenametitle {
	font: 21px/31px Impact, Arial, Helvetica;
    font-weight: 100;
    color: #999999;
	margin: 0px;
}
.spacenametitle img {
  margin: 0 0 -4px 0;
}
.spacenametitle a {
    text-decoration: none;
    color: #999999;
}
.spacenametitle a:visited {
    text-decoration: none;
    color: #999999;
}*/

.spacenametitle-printable {
	font: 20px/25px Impact, Arial, Helvetica;
    font-weight: 100;
    color: #999999;
	margin: 0px;
}
.spacenametitle-printable a {
    text-decoration: none;
    color: #999999;
}
.spacenametitle-printable a:visited {
    text-decoration: none;
    color: #999999;
}

.blogDate {
	font-weight: bold;
	text-decoration: none;
	color: black;
}

.blogSurtitle {
    background: #f0f0f0;
 	border: 1px solid #ddd;
	padding: 3px;
	margin: 1px 1px 10px 1px;
}

.blogHeading {
    font-size: 20px;
    line-height: normal;
    font-weight: bold;
    padding: 0px;
    margin: 0px;
}

.blogHeading a {
   text-decoration: none;
   color: black;
}

.endsection {
	align: right;
	color: #666666;
	margin-top: 10px;
}
.endsectionleftnav {
	align: right;
	color: #666666;
	margin-top: 10px;
}

h1 {
	font-size: 24px;
	line-height: normal;
	font-weight: bold;
	background-color: #f0f0f0;
	color: #003366;
 	border-bottom: 1px solid #3c78b5;
	padding: 2px;
	margin: 36px 0px 4px 0px;
}

h2 {
	font-size: 18px;
	line-height: normal;
	font-weight: bold;
	background-color: #f0f0f0;
 	border-bottom: 1px solid #3c78b5;
	padding: 2px;
	margin: 27px 0px 4px 0px;
}

h3 {
	font-size: 14px;
	line-height: normal;
	font-weight: bold;
	background-color: #f0f0f0;
	padding: 2px;
	margin: 21px 0px 4px 0px;
}

h4 {
	font-size: 12px;
	line-height: normal;
	font-weight: bold;
	background-color: #f0f0f0;
	padding: 2px;
	margin: 18px 0px 4px 0px;
}

h4.search {
	font-size: 12px;
	line-height: normal;
	font-weight: normal;
	background-color: #f0f0f0;
	padding: 4px;
	margin: 18px 0px 4px 0px;
}

h5 {
	font-size: 10px;
	line-height: normal;
	font-weight: bold;
	background-color: #f0f0f0;
	padding: 2px;
	margin: 14px 0px 4px 0px;
}

h6 {
	font-size: 8px;
	line-height: normal;
	font-weight: bold;
	background-color: #f0f0f0;
	padding: 2px;
	margin: 14px 0px 4px 0px;
}

.smallfont {
    font-size: 10px;
}
.descfont {
    font-size: 10px;
    color: #666666;
}
.smallerfont {
    font-size: 9px;
}
.smalltext {
    color: #666666;
    font-size: 10px;
}
.smalltext a {
    color: #666666;
}
.smalltext-blue {
    color: #3c78b5;
    font-size: 10px;
}
.surtitle {
    margin-left: 1px;
    margin-bottom: 5px;
    font-size: 14px;
    color: #666666;
}

/* css hack found here:  http://www.fo3nix.pwp.blueyonder.co.uk/tutorials/css/hacks/ */
.navItemOver { font-size: 10px; font-weight: bold; color: #ffffff; background-color: #003366; cursor: hand; voice-family: '\'}\''; voice-family:inherit; cursor: pointer;}
.navItemOver a { color: #ffffff; background-color:#003366; text-decoration: none; }
.navItemOver a:visited { color: #ffffff; background-color:#003366; text-decoration: none; }
.navItemOver a:hover { color: #ffffff; background-color:#003366; text-decoration: none; }
.navItem { font-size: 10px; font-weight: bold; color: #ffffff; background-color: #3c78b5; }
.navItem a { color: #ffffff; text-decoration: none; }
.navItem a:hover { color: #ffffff; text-decoration: none; }
.navItem a:visited { color: #ffffff; text-decoration: none; }

div.padded { padding: 4px; }
div.thickPadded { padding: 10px; }
h3.macrolibrariestitle {
    margin: 0px 0px 0px 0px;
}

div.centered { text-align: center; margin: 10px; }
div.centered table {margin: 0px auto; text-align: left; }

.tableview table {
    margin: 0;
}

.tableview th {
    text-align: left;
    color: #003366;
    font-size: 12px;
    padding: 5px 0px 0px 5px;
    border-bottom: 2px solid #3c78b5;
}
.tableview td {
    text-align: left;
    border-color: #ccc;
    border-width: 0px 0px 1px 0px;
    border-style: solid;
    margin: 0;
    padding: 4px 10px 4px 5px;
}

.grid {
    margin: 2px 0px 5px 0px;
    border-collapse: collapse;
}
.grid th  {
    border: 1px solid #ccc;
    padding: 2px 4px 2px 4px;
    background: #f0f0f0;
    text-align: center;
}
.grid td  {
    border: 1px solid #ccc;
    padding: 3px 4px 3px 4px;
}
.gridHover {
	background-color: #f9f9f9;
}

td.infocell {
    background-color: #f0f0f0;
}
.label {
	font-weight: bold;
	color: #003366;
}

label {
	font-weight: bold;
	color: #003366;
}

.error {
	background-color: #fcc;
}

.errorBox {
	background-color: #fcc;
    border: 1px solid #c00;
    padding: 5px;
    margin: 5px;
}

.errorMessage {
	color: #c00;
}

.success {
	background-color: #dfd;
}

.successBox {
	background-color: #dfd;
    border: 1px solid #090;
    padding: 5px;
    margin-top:5px;
    margin-bottom:5px;
}

blockquote {
	padding-left: 10px;
	padding-right: 10px;
	margin-left: 5px;
	margin-right: 0px;
	border-left: 1px solid #3c78b5;
}

table.confluenceTable
{
    margin: 5px;
    border-collapse: collapse;
}

/* Added as a temporary fix for CONF-4223. The table elements appear to be inheriting the border: none attribute from the sectionMacro class */
table.confluenceTable td.confluenceTd
{
    border-width: 1px;
    border-style: solid;
    border-color: #ccc;
    padding: 3px 4px 3px 4px;
}

/* Added as a temporary fix for CONF-4223. The table elements appear to be inheriting the border: none attribute from the sectionMacro class */
table.confluenceTable th.confluenceTh
{
    border-width: 1px;
    border-style: solid;
    border-color: #ccc;
    padding: 3px 4px 3px 4px;
    background-color: #f0f0f0;
    text-align: center;
}

td.confluenceTd
{
    border-width: 1px;
    border-style: solid;
    border-color: #ccc;
    padding: 3px 4px 3px 4px;
}

th.confluenceTh
{
    border-width: 1px;
    border-style: solid;
    border-color: #ccc;
    padding: 3px 4px 3px 4px;
    background-color: #f0f0f0;
    text-align: center;
}

DIV.small {
	font-size: 9px;
}

H1.pagename {
	margin-top: 0px;
}

IMG.inline  {}

.loginform {
    margin: 5px;
    border: 1px solid #ccc;
}

/* The text how the "This is a preview" comment should be shown. */
.previewnote { text-align: center;
                font-size: 11px;
                    color: red; }

/* How the preview content should be shown */
.previewcontent { background: #E0E0E0; }

/* How the system messages should be shown (DisplayMessage.jsp) */
.messagecontent { background: #E0E0E0; }

/* How the "This page has been modified..." -comment should be shown. */
.conflictnote { }

.createlink {
    color: maroon;
}
a.createlink {
    color: maroon;
}
.templateparameter {
    font-size: 9px;
    color: darkblue;
}

.diffadded {
    background: #ddffdd;
    padding: 1px 1px 1px 4px;
	border-left: 4px solid darkgreen;
}
.diffdeleted {
    color: #999;
    background: #ffdddd;
    padding: 1px 1px 1px 4px;
	border-left: 4px solid darkred;
}
.diffnochange {
    padding: 1px 1px 1px 4px;
	border-left: 4px solid lightgrey;
}
.differror {
    background: brown;
}
.diff {
    font-family: lucida console, courier new, fixed-width;
	font-size: 12px;
	line-height: 14px;
}
.diffaddedchars {
    background-color:#99ff99;
    font-weight:bolder;
}
.diffremovedchars {
    background-color:#ff9999;
    text-decoration: line-through;
    font-weight:bolder;
}

.greybackground {
    background: #f0f0f0
}

.greybox {
 	border: 1px solid #ddd;
	padding: 3px;
	margin: 1px 1px 10px 1px;
}

.borderedGreyBox {
    border: 1px solid #cccccc;
    background-color: #f0f0f0;
    padding: 10px;
}

.greyboxfilled {
 	border: 1px solid #ddd;
    background: #f0f0f0;
    padding: 3px;
	margin: 1px 1px 10px 1px;
}

.navBackgroundBox {
    padding: 5px 5px 5px 5px;
    font-size: 22px;
	font-weight: bold;
	font-family: Arial, sans-serif;
	color: white;
    background: #3c78b5;
    text-decoration: none;
}

.previewBoxTop {
	background-color: #f0f0f0;
    border-width: 1px 1px 0px 1px;
    border-style: solid;
    border-color: #3c78b5;
    padding: 5px;
    margin: 5px 0px 0px 0px;
    text-align: center;
}
.previewContent {
    background-color: #fff;
 	border-color: #3c78b5;
	border-width: 0px 1px 0px 1px;
	border-style: solid;
	padding: 10px;
	margin: 0px;
}
.previewBoxBottom {
	background-color: #f0f0f0;
    border-width: 0px 1px 1px 1px;
    border-style: solid;
    border-color: #3c78b5;
    padding: 5px;
    margin: 0px 0px 5px 0px;
    text-align: center;
}

.functionbox {
    background-color: #f0f0f0;
 	border: 1px solid #3c78b5;
	padding: 3px;
	margin: 1px 1px 10px 1px;
}

.functionbox-greyborder {
    background-color: #f0f0f0;
 	border: 1px solid #ddd;
	padding: 3px;
	margin: 1px 1px 10px 1px;
}

.search-highlight {
    background-color: #ffffcc;
}

/* normal (white) background */
.rowNormal {
    background-color: #ffffff;
 }

/* alternate (pale yellow) background */
.rowAlternate {
    background-color: #f7f7f7;
}

/* used in the list attachments table */
.rowAlternateNoBottomColor {
    background-color: #f7f7f7;
}

.rowAlternateNoBottomNoColor {
}

.rowAlternateNoBottomColor td {
    border-bottom: 0px;
}

.rowAlternateNoBottomNoColor td {
    border-bottom: 0px;
}

/* row highlight (grey) background */
.rowHighlight {
    background-color: #f0f0f0;

}

TD.greenbar {FONT-SIZE: 2px; BACKGROUND: #00df00; BORDER: 1px solid #9c9c9c; PADDING: 0px; }
TD.redbar {FONT-SIZE: 2px; BACKGROUND: #df0000; BORDER: 1px solid #9c9c9c; PADDING: 0px; }
TD.darkredbar {FONT-SIZE: 2px; BACKGROUND: #af0000; BORDER: 1px solid #9c9c9c; PADDING: 0px; }

TR.testpassed {FONT-SIZE: 2px; BACKGROUND: #ddffdd; PADDING: 0px; }
TR.testfailed {FONT-SIZE: 2px; BACKGROUND: #ffdddd; PADDING: 0px; }

.toolbar  {
    margin: 0px;
    border-collapse: collapse;
}

.toolbar td  {
    border: 1px solid #ccc;
    padding: 2px 2px 2px 2px;
    color: #ccc;
}

td.noformatting {
    border-width: 0px;
    border-style: none;
    text-align: center;
	padding: 0px;
}

.commentblock {
    margin: 12px 0 12px 0;
}

/*
 * Divs displaying the license information, if necessary.
 */
.license-eval, .license-none, .license-nonprofit {
    border-top: 1px solid #bbbbbb;
    text-align: center;
    font-size: 10px;
    font-family: Verdana, Arial, Helvetica, sans-serif;
}

.license-eval, .license-none {
    background-color: #ffcccc;
}

.license-eval b, .license-none b {
    color: #990000
}

.license-nonprofit {
    background-color: #ffffff;
}

/*
 * The shadow at the bottom of the page between the main content and the
 * "powered by" section.
 */
.bottomshadow {
    height: 12px;
    background-image: url("$req.contextPath/images/border/border_bottom.gif");
    background-repeat: repeat-x;
}

/*
 * Styling of the operations box
 */
.navmenu .operations li, .navmenu .operations ul {
    list-style: none;
    margin-left: 0;
    padding-left: 0;
}

.navmenu .operations ul {
    margin-bottom: 9px;
}

.navmenu .label {
    font-weight: inherit;
}

/*
 * Styling of ops as a toolbar
 */
.toolbar div {
    display: none;
}

.toolbar .label {
    display: none;
}

.toolbar .operations {
    display: block;
}

.toolbar .operations ul {
    display: inline;
    list-style: none;
    margin-left: 10px;
    padding-left: 0;
}

.toolbar .operations li {
    list-style: none;
    display: inline;
}

/* list page navigational tabs */
#foldertab {
padding: 3px 0px 3px 8px;
margin-left: 0;
border-bottom: 1px solid #3c78b5;
font: bold 11px Verdana, sans-serif;
}

#foldertab li {
list-style: none;
margin: 0;
display: inline;
}

#foldertab li a {
padding: 3px 0.5em;
margin-left: 3px;
border: 1px solid #3c78b5;
border-bottom: none;
background: #3c78b5;
text-decoration: none;
}

#foldertab li a:link { color: #ffffff; }
#foldertab li a:visited { color: #ffffff; }

#foldertab li a:hover {
color: #ffffff;
background: #003366;
border-color: #003366;
}

#foldertab li a.current {
background: white;
border-bottom: 1px solid white;
color: black;
}

#foldertab li a.current:link { color: black; }
#foldertab li a.current:visited { color: black; }
#foldertab li a.current:hover {
background: white;
border-bottom: 1px solid white;
color: black;
}

/* alphabet list */
ul#squaretab {
margin-left: 0;
padding-left: 0;
white-space: nowrap;
font: bold 8px Verdana, sans-serif;
}

#squaretab li {
display: inline;
list-style-type: none;
}

#squaretab a {
padding: 2px 6px;
border: 1px solid #3c78b5;
}

#squaretab a:link, #squaretab a:visited {
color: #fff;
background-color: #3c78b5;
text-decoration: none;
}

#squaretab a:hover {
color: #ffffff;
background-color: #003366;
border-color: #003366;
text-decoration: none;
}

#squaretab li a#current {
background: white;
color: black;
}

.blogcalendar * {
    font-family:verdana, arial, sans-serif;
    font-size:x-small;
    font-weight:normal;
    line-height:140%;
    padding:2px;
}


table.blogcalendar {
    border: 1px solid #3c78b5;
}

.blogcalendar th.calendarhead, a.calendarhead {
    font-size:x-small;
    font-weight:bold;
    padding:2px;
    text-transform:uppercase;
    background-color: #3c78b5;
    color: #ffffff;
    letter-spacing: .3em;
    text-transform: uppercase;
}

.calendarhead:visited {color: white;}
.calendarhead:active {color: white;}
.calendarhead:hover {color: white;}

.blogcalendar th {
    font-size:x-small;
    font-weight:bold;
    padding:2px;
    background-color:#f0f0f0;
}

.blogcalendar td {
    font-size:x-small;
    font-weight:normal;
}

.searchGroup { padding: 0 0 10px 0; background: #f0f0f0; }
.searchGroupHeading { font-size: 10px; font-weight: bold; color: #ffffff; background-color: #3c78b5; padding: 2px 4px 1px 4px; }
.searchItem { padding: 1px 4px 1px 4px; }
.searchItemSelected { padding: 1px 4px 1px 4px; font-weight: bold; background: #ddd; }

/* permissions page styles */
.permissionHeading {
    border-bottom: #bbb; border-width: 0 0 1px 0; border-style: solid; font-size: 16px; text-align: left;
}
.permissionTab {
    border-width: 0 0 0 1px; border-style: solid; background: #3c78b5; color: #ffffff; font-size: 10px;
}
.permissionSuperTab {
    border-width: 0 0 0 1px; border-style: solid; background: #003366; color: #ffffff;
}
.permissionCell {
    border-left: #bbb; border-width: 0 0 0 1px; border-style: solid;
}

/* warning panel */
.warningPanel { background: #FFFFCE; border:#F0C000 1px solid; padding: 8px; margin: 10px; }
/* alert panel */
.alertPanel { background: #FFCCCC; border:#C00 1px solid; padding: 8px; margin: 10px; }
/* info panel */
.infoPanel { background: #D8E4F1; border:#3c78b5 1px solid; padding: 8px; margin: 10px; }

/* side menu highlighting (e.g. space content screen) */
.optionPadded { padding: 2px; }
.optionSelected { background-color: #ffffcc; padding: 2px; border: 1px solid #ddd; margin: -1px; }
.optionSelected a { font-weight: bold; text-decoration: none; color: black; }

/* information macros */
.noteMacro { border-style: solid; border-width: 1px; border-color: #F0C000; background-color: #FFFFCE; text-align:left; margin-top: 5px; margin-bottom: 5px}
.warningMacro { border-style: solid; border-width: 1px; border-color: #c00; background-color: #fcc; text-align:left; margin-top: 5px; margin-bottom: 5px}
.infoMacro { border-style: solid; border-width: 1px; border-color: #3c78b5; background-color: #D8E4F1; text-align:left; margin-top: 5px; margin-bottom: 5px}
.tipMacro { border-style: solid; border-width: 1px; border-color: #090; background-color: #dfd; text-align:left; margin-top: 5px; margin-bottom: 5px}
.informationMacroPadding { padding: 5px 0 0 5px; }

table.infoMacro td, table.warningMacro td, table.tipMacro td, table.noteMacro td, table.sectionMacro td {
    border: none;
}

table.sectionMacroWithBorder td.columnMacro { border-style: dashed; border-width: 1px; border-color: #cccccc;}

.pagecontent
{
    padding: 10px;
    text-align: left;
}

/* styles for links in the top bar */
.topBarDiv a:link {color: #ffffff;}
.topBarDiv a:visited {color: #ffffff;}
.topBarDiv a:active {color: #ffffff;}
.topBarDiv a:hover {color: #ffffff;}
.topBarDiv {color: #ffffff;}

.topBar {
    background-color: #003366;
}


/* styles for extended operations */
.greyLinks a:link {color: #666666; text-decoration:underline;}
.greyLinks a:visited {color: #666666; text-decoration:underline;}
.greyLinks a:active {color: #666666; text-decoration:underline;}
.greyLinks a:hover {color: #666666; text-decoration:underline;}
.greyLinks {color: #666666; display:block; padding: 10px}

.logoSpaceLink {color: #999999; text-decoration: none}
.logoSpaceLink a:link {color: #999999; text-decoration: none}
.logoSpaceLink a:visited {color: #999999; text-decoration: none}
.logoSpaceLink a:active {color: #999999; text-decoration: none}
.logoSpaceLink a:hover {color: #003366; text-decoration: none}

/* basic panel (basicpanel.vmd) style */
.basicPanelContainer {border: 1px solid #3c78b5; margin-top: 2px; margin-bottom: 8px; width: 100%}
.basicPanelTitle {padding: 5px; margin: 0px; background-color: #f0f0f0; color: black; font-weight: bold;}
.basicPanelBody {padding: 5px; margin: 0px}

.separatorLinks a:link {color: white}
.separatorLinks a:visited {color: white}
.separatorLinks a:active {color: white}

.greynavbar {background-color: #f0f0f0; border-top: 1px solid #3c78b5; margin-top: 2px}

div.headerField {
    float: left;
    width: auto;
    height: 100%;
}

.headerFloat {
    margin-left: auto;
    width: 50%;
}

.headerFloatLeft {
    float: left;
    margin-right: 20px;
    margin-bottom: 10px;
}

#headerRow {
    padding: 10px;
}

div.license-personal {
   background-color: #003366;
   color: #ffffff;
}

div.license-personal a {
   color: #ffffff;
}

.greyFormBox {
    border: 1px solid #cccccc;
    padding: 5px;
}

/* IE automatically adds a margin before and after form tags. Use this style to remove that */
.marginlessForm {
    margin: 0px;
}

.openPageHighlight {
    background-color: #ffffcc;
    padding: 2px;
    border: 1px solid #ddd;
}

.editPageInsertLinks, .editPageInsertLinks a
{
    color: #666666;
    font-weight: bold;
    font-size: 10px;
}

/* Style for label heatmap. */
.top10 a {
    font-weight: bold;
    font-size: 2em;
    color: #003366;
}
.top25 a {
    font-weight: bold;
    font-size: 1.6em;
    color: #003366;
}
.top50 a {
    font-size: 1.4em;
    color: #003366;
}
.top100 a {
    font-size: 1.2em;
    color: #003366;
}

.heatmap {
    list-style:none;
    width: 95%;
    margin: 0px auto;
}

.heatmap a {
    text-decoration:none;
}

.heatmap a:hover {
    text-decoration:underline;
}

.heatmap li {
    display: inline;
}

.minitab {
padding: 3px 0px 3px 8px;
margin-left: 0;
margin-top: 1px;
margin-bottom: 0px;
border-bottom: 1px solid #3c78b5;
font: bold 9px Verdana, sans-serif;
text-decoration: none;
float:none;
}
.selectedminitab {
padding: 3px 0.5em;
margin-left: 3px;
margin-top: 1px;
border: 1px solid #3c78b5;
background: white;
border-bottom: 1px solid white;
color: #000000;
text-decoration: none;
}
.unselectedminitab {
padding: 3px 0.5em;
margin-left: 3px;
margin-top: 1px;
border: 1px solid #3c78b5;
border-bottom: none;
background: #3c78b5;
color: #ffffff;
text-decoration: none;
}

a.unselectedminitab:hover {
color: #ffffff;
background: #003366;
border-color: #003366;
}

a.unselectedminitab:link { color: white; }
a.unselectedminitab:visited { color: white; }

a.selectedminitab:link { color: black; }
a.selectedminitab:visited { color: black; }

.linkerror { background-color: #fcc;}

a.labelOperationLink:link {text-decoration: underline}
a.labelOperationLink:active {text-decoration: underline}
a.labelOperationLink:visited {text-decoration: underline}
a.labelOperationLink:hover {text-decoration: underline}

a.newLabel:link {background-color: #ddffdd}
a.newLabel:active {background-color: #ddffdd}
a.newLabel:visited {background-color: #ddffdd}
a.newLabel:hover {background-color: #ddffdd}

ul.square {list-style-type: square}

.inline-control-link {
    background: #ffc;
    font-size: 9px;
    color: #666;
    padding: 2px;
    text-transform: uppercase;
    text-decoration: none;
}


.inline-control-link a:link {text-decoration: none}
.inline-control-link a:active {text-decoration: none}
.inline-control-link a:visited {text-decoration: none}
.inline-control-link a:hover {text-decoration: none}

.inline-control-link {
    background: #ffc;
    font-size: 9px;
    color: #666;
    padding: 2px;
    text-transform: uppercase;
    text-decoration: none;
    cursor: pointer;
}

div.auto_complete {
    width: 350px;
    background: #fff;
}
div.auto_complete ul {
    border: 1px solid #888;
    margin: 0;
    padding: 0;
    width: 100%;
    list-style-type: none;
}
div.auto_complete ul li {
    margin: 0;
    padding: 3px;
}
div.auto_complete ul li.selected {
    background-color: #ffb;
}
div.auto_complete ul strong.highlight {
    color: #800;
    margin: 0;
    padding: 0;
}

/******* Edit Page Styles *******/
.toogleFormDiv{
    border:1px solid #A7A6AA;
    background-color:white;
    padding:5px;
    margin-top: 5px;
}

.toogleInfoDiv{
    border:1px solid #A7A6AA;
    background-color:white;
    display:none;
    padding:5px;
    margin-top: 10px;
}

.inputSection{
    margin-bottom:20px;
}

#editBox{
   border:1px solid lightgray;
   background-color:#F0F0F0;
}

/******* Left Navigation Theme Styles ********/
.leftnav li a {
    text-decoration:none;
    color:white;
    margin:0px;
    display:block;
    padding:2px;
    padding-left:5px;
    background-color: #3c78b5;
    border-top:1px solid #3c78b5;
}

.leftnav li a:active {color:white;}
.leftnav li a:visited {color:white;}
.leftnav li a:hover {background-color: #003366; color:white;}

/* Added by Shaun during i18n */
.replaced
{
    background-color: #33CC66;
}

.topPadding
{
    margin-top: 20px;
}

/* new form style */
.form-block {
    padding: 6px;
}
.form-error-block {
    padding: 6px;
    background: #fcc;
    border-top: #f0f0f0 1px solid;
    border-bottom: #f0f0f0 1px solid;
    margin-bottom: 6px;
    padding: 0 12px 0 12px;
}
.form-element-large {
    font-size: 16px;
    font-weight: bold;
    font-family: Arial, sans-serif;
    color: #003366;
}

.form-element-small {
    font-size: 12px;
    font-weight: bold;
    font-family: Arial, sans-serif;
    color: #003366;
}

.form-header {
    background: lightyellow;
    border-top: #f0f0f0 1px solid;
    border-bottom: #f0f0f0 1px solid;
    margin-bottom: 6px;
    padding: 0 12px 0 12px;
}
.form-header p, .form-block p, .form-error-block p {
    line-height: normal;
    margin: 12px 0 12px 0;
}
.form-example {
    color: #888;
    font-size: 11px;
}
.form-divider {
    border-bottom: #ccc 1px solid;
    margin-bottom: 6px;
}
.form-buttons {
    margin-top: 6px;
    border-top: #ccc 1px solid;
    border-bottom: #ccc 1px solid;
    background: #f0f0f0;
    padding: 10px;
    text-align: center;
}
.form-buttons input {
    width: 100px;
}
.form-block .error {
    padding: 6px;
    margin-bottom: 6px;
}
    -->
    </style>
</head>
<body>

<div id="PageContent">
<table class="pagecontent" border="0" cellpadding="0" cellspacing="0" width="100%"><tr>
<td valign="top" class="pagebody">

    <div class="pageheader">
        <span class="pagetitle">
            Page Edited :
            <a href="http://cwiki.apache.org/confluence/display/CAYDOC">CAYDOC</a> :
            <a href="http://cwiki.apache.org/confluence/display/CAYDOC/Scripting+SQLTemplate">Scripting SQLTemplate</a>
        </span>
    </div>

     <p>
        <a href="http://cwiki.apache.org/confluence/display/CAYDOC/Scripting+SQLTemplate">Scripting SQLTemplate</a>
        has been edited by             <a href="http://cwiki.apache.org/confluence/display/~andrus">Andrus Adamchik</a>
            <span class="smallfont">(Jun 17, 2007)</span>.
     </p>
    
     <p>
                 <a href="http://cwiki.apache.org/confluence/pages/diffpagesbyversion.action?pageId=10524&originalVersion=5&revisedVersion=6">(View changes)</a>
     </p>

    <span class="label">Content:</span><br/>
    <div class="greybox wiki-content"><p>SQLTemplate's internal SQL string is a dynamic script that is processed at runtime to generate PreparedStatement SQL code. Dynamic nature of SQLTemplate makes possible a few important things - it allows to bind parameters on the fly; it provides a way to pass extra information to Cayenne that is not included in the SQL text; it supports including/excluding chunks of SQL depending on runtime parameters.</p>

<p>Scripting of SQL strings is done using <span class="nobr"><a href="http://jakarta.apache.org/velocity" title="Visit page outside Confluence" rel="nofollow">Jakarta Velocity<sup><img class="rendericon" src="/confluence/images/icons/linkext7.gif" height="7" width="7" align="absmiddle" alt="" border="0"/></sup></a></span>. Velocity was chosen primarily for its concise template language (no XML tags within SQL!) that doesn't conflict with the SQL syntax. When creating dynamic SQL template, all standard Velocity directives are available, including <tt>#set</tt>, <tt>#foreach</tt>, <tt>#if</tt>. However due to the nature of the SQL and the need to integrate it with Cayenne runtime, only a few Cayenne custom directives are normally used. These directives (<tt>#bind</tt>..., <tt>#result</tt>, <tt>#chain</tt>, <tt>#chunk</tt>) are described below.</p>
<table cellpadding='5' width='85%' cellspacing='8px' class='noteMacro' border="0" align='center'><colgroup><col width='24'><col></colgroup><tr><td valign='top'><img src="/confluence/images/icons/emoticons/warning.gif" width="16" height="16" align="absmiddle" alt="" border="0"></td><td><b class="strong">Directive Syntax Note</b><br />Velocity directives start with pound sign (#) and have their parameters separated by space, not comma. E.g. <tt>#bind('SOMESTRING' 'VARCHAR')</tt>.</td></tr></table>

<h3><a name="ScriptingSQLTemplate-NamedParameters"></a>Named Parameters</h3>
<p><tt>SQLTemplate.setParameters(java.util.Map)</tt> allows setting a number of named parameters that are used to build parts of the query. During template processing by Velocity all keys in the parameters map are available as variables. For example if the map contains a key "name", its value can be referenced as "$name" in the template. Value of the parameter will be inserted in the SQL unmodified:</p>

<h5><a name="ScriptingSQLTemplate-NamedParametersExample"></a>Named Parameters Example</h5>
<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-comment">// build SQLTemplate
</span><span class="code-object">String</span> sql = <span class="code-quote">"delete from $tableName"</span>;
SQLTemplate delete = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql, <span class="code-keyword">false</span>);
...
<span class="code-comment">// <span class="code-keyword">this</span> will create a query <span class="code-quote">"delete from ARTIST"</span>
</span>delete.setParameters(Collections.singletonMap(<span class="code-quote">"tableName"</span>, <span class="code-quote">"ARTIST"</span>));
...
<span class="code-comment">// <span class="code-keyword">this</span> will create a query <span class="code-quote">"delete from PAINTING"</span>
</span>delete.setParameters(Collections.singletonMap(<span class="code-quote">"tableName"</span>, <span class="code-quote">"PAINTING"</span>));</pre>
</div></div>

<h3><a name="ScriptingSQLTemplate-DescribingtheResults%23resultDirective"></a>Describing the Results - #result Directive</h3>

<p><tt>#result</tt> directive is used in selecting SQLTemplates to quickly map an arbitrary ResultSet to a DataObject (or a data row with known keys), and also to control Java types of result values. #result directive has a variable number of arguments:</p>

<ul>
	<li><tt>#result(columnName)</tt> - e.g. <tt>#result('ARTIST_NAME')</tt></li>
	<li><tt>#result(columnName javaType)</tt> - e.g. <tt>#result('DATE_OF_BIRTH' 'java.util.Date')</tt></li>
	<li><tt>#result(columnName javaType columnAlias)</tt> - e.g. <tt>#result('DATE_OF_BIRTH' 'java.util.Date' 'DOB')</tt> - in this case returned data row will use "DOB" instead of "DATE_OF_BIRTH" for the result value.</li>
</ul>


<table cellpadding='5' width='85%' cellspacing='8px' class='noteMacro' border="0" align='center'><colgroup><col width='24'><col></colgroup><tr><td valign='top'><img src="/confluence/images/icons/emoticons/warning.gif" width="16" height="16" align="absmiddle" alt="" border="0"></td><td>Generally "javaType" argument is a fully-qualified Java class name for a given result column. However for simplicity most common Java types used in JDBC can be specified without a package. These include all numeric types, primitives, String, SQL dates, BigDecimal and BigInteger. So "#result('A' 'String')", "#result('B' 'java.lang.String')" and "#result('C' 'int')" are all valid.</td></tr></table>

<p>While "select * from" queries may work just fine, in most cases it is a good idea to explicitly describe results.</p>

<h5><a name="ScriptingSQLTemplate-%23resultExamples"></a>#result Examples</h5>
<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-comment">// build selecting SQLTemplate
</span><span class="code-object">String</span> sql = <span class="code-quote">"SELECT"</span>
   + <span class="code-quote">" #result('ARTIST_ID' '<span class="code-object">int</span>'),"</span>
   + <span class="code-quote">" #result('ARTIST_NAME' '<span class="code-object">String</span>'),"</span>
   + <span class="code-quote">" #result('DATE_OF_BIRTH' 'java.util.Date')"</span>
   + <span class="code-quote">" FROM ARTIST"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql, <span class="code-keyword">true</span>);
...
DataContext context...;
List artists = context.performQuery(select);</pre>
</div></div>

<h3><a name="ScriptingSQLTemplate-BindingParameters%23bindDirective"></a>Binding Parameters - #bind Directive</h3>
<p>SQLTemplate uses <tt>#bind</tt> directive to indicate value binding. It has the same meaning as PreparedStatement question mark ("?"), however it also tells Cayenne about the nature of the bound value, so it should be used for all bindings. <tt>#bind()</tt> directive can have a variable number of arguments. The following are the valid invocation formats:</p>

<ul>
	<li><tt>#bind(value)</tt> - e.g. <tt>#bind($xyz)</tt> or <tt>#bind('somestring')</tt></li>
	<li><tt>#bind(value jdbcTypeName)</tt> - e.g. <tt>#bind($xyz 'VARCHAR')</tt>. Second argument is the name of JDBC type for this binding. Valid JDBC types are defined in java.sql.Types class. This form is the the most common and useful. It is generally preferred to the single argument form, as it explicitly tells what type of JDBC value this binding is.</li>
	<li><tt>#bind(value jdbcTypeName precision)</tt> - e.g. <tt>#bind($xyz 'DECIMAL' 2)</tt></li>
</ul>


<h5><a name="ScriptingSQLTemplate-%23binddirectiveexample"></a>#bind directive example</h5>
<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-comment">// build SQLTemplate
</span><span class="code-object">String</span> sql = <span class="code-quote">"update ARTIST set ARTIST_NAME = #bind($name) where ARTIST_ID = #bind($id)"</span>;
SQLTemplate update = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql, <span class="code-keyword">false</span>);
...
<span class="code-comment">// set parameters and run it...
</span>Map parameters = <span class="code-keyword">new</span> HashMap();
parameters.put(<span class="code-quote">"name"</span>, <span class="code-quote">"Publo Picasso"</span>);
parameters.put(<span class="code-quote">"id"</span>, <span class="code-keyword">new</span> <span class="code-object">Integer</span>(1001));
update.setParameters(parameters);

DataContext context...;
context.performNonSelectingQuery(update);</pre>
</div></div>

<p>SQLTemplate also supports binding Collections for building <tt>IN ( .. )</tt> queries. In any of the <tt>#bind</tt> invocation formats above, you may specify a Collection of values in place of <tt>value</tt>, and Cayenne will automatically expand it.</p>

<h5><a name="ScriptingSQLTemplate-Collectionexample"></a>Collection example</h5>
<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-comment">// build SQLTemplate
</span><span class="code-object">String</span> sql = "SELECT ARTIST_ID, ARTIST_NAME FROM ARTIST WHERE ARTIST_NAME IN (#bind($names))
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql, <span class="code-keyword">false</span>);
...
<span class="code-comment">// set parameters and run it...
</span>Map parameters = <span class="code-keyword">new</span> HashMap();
parameters.put(<span class="code-quote">"names"</span>, Arrays.asList(<span class="code-keyword">new</span> <span class="code-object">String</span>[] { <span class="code-quote">"Monet"</span>, <span class="code-quote">"Publo Picasso"</span>}));
select.setParameters(parameters);

DataContext context...;
List artists = context.performQuery(select);</pre>
</div></div>


<h3><a name="ScriptingSQLTemplate-NullValuesinBindings%23bindEqualand%23bindNotEqualDirectives"></a>Null Values in Bindings - #bindEqual and #bindNotEqual Directives</h3>
<p>Sometimes when a parameter is NULL, SQL code has to be changed. For example, instead of "WHERE COLUMN = ?", PreparedStatement should be rewritten as "WHERE COLUMN IS NULL", and instead of "WHERE COLUMN &lt;&gt; ?" - as "WHERE COLUMN IS NOT NULL". <tt>#bindEqual</tt> and <tt>#bindNotEqual</tt> directives are used to dynamically generate correct SQL string in this case. Their semantics is the same as #bind directive above, except that they do not require "=", "!=" or "&lt;&gt;" in front of them:</p>

<ul>
	<li><tt>#bindEqual(value)</tt>, <tt>#bindNotEqual(value)</tt></li>
	<li><tt>#bindEqual(value jdbcTypeName)</tt>, <tt>#bindNotEqual(value jdbcTypeName)</tt></li>
	<li><tt>#bindEqual(value jdbcTypeName precision)</tt>, <tt>#bindNotEqual(value jdbcTypeName precision)</tt></li>
</ul>


<h5><a name="ScriptingSQLTemplate-NullValueExamples"></a>Null Value Examples</h5>
<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-comment">// build SQLTemplate
</span><span class="code-comment">// note that <span class="code-quote">"="</span> is ommitted <span class="code-keyword">for</span> the second binding, since it is a part of the directive
</span><span class="code-object">String</span> sql = "update ARTIST set ARTIST_NAME = #bind($name) where ARTIST_ID #bindEqual($id);
SQLTemplate update = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql, <span class="code-keyword">false</span>);
...
<span class="code-comment">// set parameters and run it...
</span>Map parameters = <span class="code-keyword">new</span> HashMap();
parameters.put(<span class="code-quote">"name"</span>, <span class="code-quote">"Publo Picasso"</span>);
parameters.put(<span class="code-quote">"id"</span>, <span class="code-keyword">new</span> <span class="code-object">Integer</span>(1001));
update.setParameters(parameters);

DataContext context...;

<span class="code-comment">// after binding processing PrepapredStatement SQL will look like 
</span><span class="code-comment">// <span class="code-quote">"update ARTIST set ARTIST_NAME = ? where ARTIST_ID = ?"</span>
</span>context.performNonSelectingQuery(update);</pre>
</div></div>

<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-comment">// build SQLTemplate
</span><span class="code-comment">// note that <span class="code-quote">"!="</span> is omitted <span class="code-keyword">for</span> the second binding, since it is a part of the directive
</span><span class="code-object">String</span> sql = <span class="code-quote">"update ARTIST set ARTIST_NAME = #bind($name) where ARTIST_ID #bindNotEqual($id)"</span>;
SQLTemplate update = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql, <span class="code-keyword">false</span>);
...
<span class="code-comment">// set parameters and run it...
</span>Map parameters = <span class="code-keyword">new</span> HashMap();
parameters.put(<span class="code-quote">"name"</span>, <span class="code-quote">"Publo Picasso"</span>);
parameters.put(<span class="code-quote">"id"</span>, <span class="code-keyword">null</span>);
update.setParameters(parameters);

DataContext context...;

<span class="code-comment">// after binding processing PrepapredStatement SQL will look like 
</span><span class="code-comment">// <span class="code-quote">"update ARTIST set ARTIST_NAME = ? where ARTIST_ID IS NOT NULL"</span>
</span>context.performNonSelectingQuery(update);</pre>
</div></div>

<h3><a name="ScriptingSQLTemplate-BindingPersistentObjectValues%23bindObjectEqualand%23bindObjectNotEqualdirectives"></a>Binding Persistent Object Values - #bindObjectEqual and #bindObjectNotEqual directives</h3>

<p>It can be tricky to use a Persistent object (or an ObjectId) in a binding, especially for tables with compound primary keys. There are two directives to help with that - <tt>#bindObjectEqual</tt> and <tt>#bindObjectNotEqual</tt>. Long explicit form of these directives is the following:</p>

<ul>
	<li><tt>#bindObjectEqual(object columns idColumns)</tt></li>
	<li><tt>#bindObjectNotEqual(object columns idColumns)</tt></li>
</ul>


<p>An <tt>"object"</tt> argument can be one of Persistent, ObjectId or Map. <tt>"columns"</tt> and <tt>"idColumns"</tt> can be of type Object[], Collection or Object. What these directives do is build the SQL to match <tt>"columns"</tt> (i.e. the columns from the SQL query) against <tt>"idColumns"</tt> (i.e. the names of the PK columns for a given entity) for a given object. E.g.:</p>

<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-object">String</span> sql = <span class="code-quote">"SELECT * FROM PAINTING t0"</span>
                + <span class="code-quote">" WHERE #bindObjectEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER BY PAINTING_ID"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql);

Artist a = ....
select.setParameters(Collections.singletonMap(<span class="code-quote">"a"</span>, a));</pre>
</div></div>

<p>In case of compound PK, arrays can be used for the last two parameters:</p>

<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-object">String</span> sql = <span class="code-quote">"SELECT * FROM TABLE_X t0"</span>
                + <span class="code-quote">" WHERE #bindObjectEqual($a ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'] )"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(TableX.class, sql);</pre>
</div></div>

<p>In the case when SQL columns have the same names as PK columns, and there's no naming conflict that would force to use fully qualified column names, a short form of these directives can be used, where column names are inferred from the ObjectId:</p>

<ul>
	<li><tt>#bindObjectEqual(object)</tt></li>
	<li><tt>#bindObjectNotEqual(object)</tt></li>
</ul>


<p>E.g.:</p>

<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-object">String</span> sql = <span class="code-quote">"SELECT * FROM PAINTING WHERE #bindObjectEqual($a) ORDER BY PAINTING_ID"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql);</pre>
</div></div>

<h3><a name="ScriptingSQLTemplate-BuildingDynamicSQL%23chainand%23chunkDirectives"></a>Building Dynamic SQL - #chain and #chunk Directives</h3>
<p>Often it is desirable to exclude parts of the WHERE clause if some parameters are null or not set. This task is not trivial considering the semantics of a SQL statement. Consider this fairly simple example:</p>

<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-object">String</span> sql = <span class="code-quote">"SELECT DISTINCT"</span>
   + <span class="code-quote">" #result('ARTIST_ID' '<span class="code-object">int</span>'),"</span>
   + <span class="code-quote">" #result('ARTIST_NAME' '<span class="code-object">String</span>'),"</span>
   + <span class="code-quote">" #result('DATE_OF_BIRTH' 'java.util.Date')"</span>
   + <span class="code-quote">" FROM ARTIST t0"</span>
   + <span class="code-quote">" WHERE ARTIST_NAME LIKE #bind($name)"</span>
   + <span class="code-quote">" OR ARTIST_ID &gt; #bind($id)"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql);</pre>
</div></div>

<p>It would be nice to exclude ARTIST_NAME matching if "name" parameter is null, exclude ARTIST_ID matching if "id" is null, and exclude the whole WHERE clause if both are null. <tt>#chain</tt> and <tt>#chunk</tt> directives are used for this purpose. Each logical piece is wrapped in a conditional "chunk", and a number of chunks are grouped in a chain. If chain contains no chunks it doesn't render anything enclosed in it.</p>

<div class="code"><div class="codeContent">
<pre class="code-java"><span class="code-object">String</span> sql = <span class="code-quote">"SELECT DISTINCT"</span>
   + <span class="code-quote">" #result('ARTIST_ID' '<span class="code-object">int</span>'),"</span>
   + <span class="code-quote">" #result('ARTIST_NAME' '<span class="code-object">String</span>'),"</span>
   + <span class="code-quote">" #result('DATE_OF_BIRTH' 'java.util.Date')"</span>
   + <span class="code-quote">" FROM ARTIST t0"</span>
   + " #chain('OR' 'WHERE')                              <span class="code-comment">// start chain prefixed by WHERE, 
</span>                                                         <span class="code-comment">// and joined by OR
</span>   + <span class="code-quote">" #chunk($name) ARTIST_NAME LIKE #bind($name) #end"</span> <span class="code-comment">// ARTIST_NAME <span class="code-quote">"chunk"</span>
</span>   + <span class="code-quote">" #chunk($id) ARTIST_ID &gt; #bind($id) #end"</span>          <span class="code-comment">// ARTIST_ID <span class="code-quote">"chunk"</span>
</span>   + <span class="code-quote">" #end"</span>;                                            <span class="code-comment">// end of chain
</span>SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class, sql);</pre>
</div></div>
</div>


</td></tr></table></div>
<p>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
    <tr>
        <td height="12" background="http://cwiki.apache.org/confluence/images/border/border_bottom.gif"><img src="http://cwiki.apache.org/confluence/images/border/spacer.gif" width="1" height="1" border="0"/></td>
    </tr>
</table>

<div class="smalltext">
    Powered by
    <a href="http://www.atlassian.com/software/confluence/default.jsp?clicked=footer" class="smalltext">Atlassian Confluence</a>
    (Version: 2.2.9 Build:#527 Sep 07, 2006)
    -
    <a href="http://jira.atlassian.com/secure/BrowseProject.jspa?id=10470" class="smalltext">Bug/feature request</a><br/>
    <br>
    <a href="http://cwiki.apache.org/confluence/users/viewnotifications.action">Unsubscribe or edit your notifications preferences</a>

</div>

</body>
</html>


Mime
View raw message