Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > JDBC, PreparedStatement and named parameters

Reply
Thread Tools

JDBC, PreparedStatement and named parameters

 
 
Andreas Leitgeb
Guest
Posts: n/a
 
      07-20-2012
I'm rather new to JDBC, and somewhat experienced with
database access in other languages, like with ProC or
with the sqlplus-shell.

While in C/C++ eSql with ProC (not sure about correct
nomenclature) I can use named variables for the parameters,
jdbc seems like it would only allow positional parameters.

With Google, I stumbled over this approach:

http://www.javaworld.com/javaworld/j...w-04-jdbc.html

which boils down to pre-processing the sql-statement, replacing
the named variables by "?" while creating an array of the parameters
such, that e.g. a snippet like this:
"... :a :c :b :a ..." and setting a -> x1, b -> x2, c -> x3
would turn into
"... ? ? ? ? ..." with an parameter array of { x1, x3, x2, x1 }

That almost looks like what I was hoping for, but when I see
that the article is 5 years old, then I'd hope that something
similar might meanwhile have been turned into a standard...

Does anyone here know of a way to use either named variables,
or at least something like these "foo {1} bar {0}" things
used in jsf4j and java.text.MessageFormat?

 
Reply With Quote
 
 
 
 
markspace
Guest
Posts: n/a
 
      07-20-2012
On 7/20/2012 2:39 AM, Andreas Leitgeb wrote:
>
> Does anyone here know of a way to use either named variables,
> or at least something like these "foo {1} bar {0}" things
> used in jsf4j and java.text.MessageFormat?



Well, JPA obviously, but have you looked at any database frameworks or
helpers at all?


<http://commons.apache.org/dbutils/>


 
Reply With Quote
 
 
 
 
Andreas Leitgeb
Guest
Posts: n/a
 
      07-20-2012
markspace <-@> wrote:
> On 7/20/2012 2:39 AM, Andreas Leitgeb wrote:
>> Does anyone here know of a way to use either named variables,
>> or at least something like these "foo {1} bar {0}" things
>> used in jsf4j and java.text.MessageFormat?

> Well, JPA obviously, but have you looked at any database frameworks or
> helpers at all?
> <http://commons.apache.org/dbutils/>


The DbUtils examples all seem to use "?", so I wonder, if
this DbUtils really can do more, or if it was just a quick-shot
answer.

My question was a bit more specific than just about how to use
parameters at all. Rather, it was about how to de-couple the
exact shape of the where-clause from the array of parameters
that provides the variable parts.

Now, do Apache DbUtils really address this?

 
Reply With Quote
 
markspace
Guest
Posts: n/a
 
      07-20-2012
On 7/20/2012 7:52 AM, Andreas Leitgeb wrote:

> My question was a bit more specific than just about how to use
> parameters at all. Rather, it was about how to de-couple the
> exact shape of the where-clause from the array of parameters
> that provides the variable parts.
>
> Now, do Apache DbUtils really address this?



I don't understand the word "shape" when applied to parsing, databases,
or Java. I think perhaps you need to think about what your actual
requirements are.

I also mentioned JPA. How does that work for you?



 
Reply With Quote
 
Andreas Leitgeb
Guest
Posts: n/a
 
      07-20-2012
markspace <-@> wrote:
> On 7/20/2012 7:52 AM, Andreas Leitgeb wrote:
>> My question was a bit more specific than just about how to use
>> parameters at all. Rather, it was about how to de-couple the
>> exact shape of the where-clause from the array of parameters
>> that provides the variable parts.
>> Now, do Apache DbUtils really address this?

> I don't understand the word "shape" when applied to parsing, databases,
> or Java.


Thanks for trying to help, anyway.

 
Reply With Quote
 
markspace
Guest
Posts: n/a
 
      07-20-2012
On 7/20/2012 9:26 AM, Andreas Leitgeb wrote:
> markspace <-@> wrote:
>> On 7/20/2012 7:52 AM, Andreas Leitgeb wrote:
>>> My question was a bit more specific than just about how to use
>>> parameters at all. Rather, it was about how to de-couple the
>>> exact shape of the where-clause from the array of parameters
>>> that provides the variable parts.
>>> Now, do Apache DbUtils really address this?

>> I don't understand the word "shape" when applied to parsing, databases,
>> or Java.

>
> Thanks for trying to help, anyway.



Here's a hint: when you post asking for help making something work like
<insert non-Java system here>, most of us are going to have no idea what
you are really asking for.

Personally, the more I think about it, the more I think having a second
layer of binding that you have to deal with is going to be a bigger pita
than just using indexes. That jdbcUtils package I pointed you at made
it very easy to do that, if you'd bothered to read the documentation
further than just noticing the question marks in the SQL statements.

I've had MyBatis recommended to me when I asked a similar question. I
haven't looked at it, but a quick review of their user guide looks like
it has some sort of bindings available by name/property.

Stuff is out there, but "works like X" isn't going to help anyone who
isn't familiar with X. I certainly can't read your mind, or develop
requirements just by staring at the word "X".





 
Reply With Quote
 
Daniel Pitts
Guest
Posts: n/a
 
      07-20-2012
On 7/20/12 2:39 AM, Andreas Leitgeb wrote:
> I'm rather new to JDBC, and somewhat experienced with
> database access in other languages, like with ProC or
> with the sqlplus-shell.
>
> While in C/C++ eSql with ProC (not sure about correct
> nomenclature) I can use named variables for the parameters,
> jdbc seems like it would only allow positional parameters.
>
> With Google, I stumbled over this approach:
>
> http://www.javaworld.com/javaworld/j...w-04-jdbc.html
>
> which boils down to pre-processing the sql-statement, replacing
> the named variables by "?" while creating an array of the parameters
> such, that e.g. a snippet like this:
> "... :a :c :b :a ..." and setting a -> x1, b -> x2, c -> x3
> would turn into
> "... ? ? ? ? ..." with an parameter array of { x1, x3, x2, x1 }
>
> That almost looks like what I was hoping for, but when I see
> that the article is 5 years old, then I'd hope that something
> similar might meanwhile have been turned into a standard...
>
> Does anyone here know of a way to use either named variables,
> or at least something like these "foo {1} bar {0}" things
> used in jsf4j and java.text.MessageFormat?
>


Depending on your needs, a JPA provider (such as Hibernate) may be a
better approach. It moves you away from low-level SQL, and into more
object oriented notation.

It isn't always the best solution, and my experience with Hibernate has
been mixed. It's worth looking into and learning about it. They tend
to be useful if you do exactly what they were designed for, and then
they get in your way when you need to do something different.



 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      07-20-2012
Daniel Pitts wrote:
> Depending on your needs, a JPA provider (such as Hibernate) may be a


A word to the wise: if you do use Hibernate itself, as opposed to, say,
Apache OpenJPA or EclipseLink, make sure you use it as a JPA framework,
and not as old-style Hibernate.

> better approach. It moves you away from low-level SQL, and into more
> object oriented notation.


For certain values of "better".

It is often better to use raw JDBC.

You can more or less fake out named parameters with a combination of
java.sql.PreparedStatement and an enum for the indexes.

JPA has its own query language that directly supports named parameters.

> It isn't always the best solution, and my experience with Hibernate has
> been mixed. It's worth looking into and learning about it. They tend


Hibernate is fine if you restrict yourself to the newer JPA approach.

EclipseLink and OpenJPA know no other way.

Keep your EntityManagers short-lived and don't share them across threads.

Keep your EntityManagerFactory long-lived, and IIRC it's shareable.

You have to use JPA in an idiomatically Java way to get its full value.

> to be useful if you do exactly what they were designed for, and then
> they get in your way when you need to do something different.


The use case for raw JDBC is bulk operations.

For object-to-relational mapping the JPA ORM frameworks are great.

Don't get too fancy with your JPA.

--
Lew
 
Reply With Quote
 
Andreas Leitgeb
Guest
Posts: n/a
 
      07-20-2012
markspace <-@> wrote:
> On 7/20/2012 9:26 AM, Andreas Leitgeb wrote:
>> Thanks for trying to help, anyway.

> Here's a hint: when you post asking for help making something work like
> <insert non-Java system here>, most of us are going to have no idea what
> you are really asking for.


The answers I got did indicate to me that the feature I wanted
is just not as common and near hand (to my hand, that is), as I'd
hoped it would be.

 
Reply With Quote
 
markspace
Guest
Posts: n/a
 
      07-20-2012
On 7/20/2012 2:04 PM, Andreas Leitgeb wrote:
> markspace <-@> wrote:
>> On 7/20/2012 9:26 AM, Andreas Leitgeb wrote:
>>> Thanks for trying to help, anyway.

>> Here's a hint: when you post asking for help making something work like
>> <insert non-Java system here>, most of us are going to have no idea what
>> you are really asking for.

>
> The answers I got did indicate to me that the feature I wanted
> is just not as common and near hand (to my hand, that is), as I'd
> hoped it would be.



I still wish you'd be a little more forthcoming about what it is you are
looking here. At least half the reason I post answers here is so that I
also can learn things. What is so great about this eSql and ProC that
you'd hope to find it in a Java library?

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mismatch in Statement and PreparedStatement execution in Oracle DB. Alex Kizub Java 10 02-11-2010 09:20 PM
Difference between Statement and preparedStatement (for SQL databases) ? Ken Philips Java 4 11-05-2008 01:04 AM
PreparedStatement and prewiev SQL query tomas Java 3 09-16-2006 04:14 PM
Discuss: PreparedStatement and Connection Pooling Raj Java 1 08-25-2003 10:34 PM
Execution time problem with Insert Query using PreparedStatement Manoj S. P. Java 2 07-02-2003 03:52 PM



Advertisments