Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #616 > unrolled thread

Local database library

Back to article view | Back to comp.lang.java.databases

Contents

  Local database library Kevin Nathan <knathan@project54.com> - 2013-10-01 13:52 -0700
    Re: Local database library Arne Vajhøj <arne@vajhoej.dk> - 2013-10-01 17:38 -0400
      Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-01 15:41 -0700
        Re: Local database library Arne Vajhøj <arne@vajhoej.dk> - 2013-10-01 20:53 -0400
          Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-02 10:09 -0700
            Re: Local database library Martin Gregorie <martin@address-in-sig.invalid> - 2013-10-02 19:49 +0000
              Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-04 10:48 -0700
                Re: Local database library Martin Gregorie <martin@address-in-sig.invalid> - 2013-10-05 15:43 +0000
                  Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-05 11:19 -0700
            Re: Local database library Arne Vajhøj <arne@vajhoej.dk> - 2013-10-02 22:05 -0400
              Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-04 10:57 -0700
                Re: Local database library Arne Vajhøj <arne@vajhoej.dk> - 2013-10-04 22:00 -0400
                  Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-05 11:28 -0700
                    Re: Local database library Arne Vajhøj <arne@vajhoej.dk> - 2013-10-05 15:15 -0400
                      Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-05 12:49 -0700
    Re: Local database library "John B. Matthews" <nospam@nospam.invalid> - 2013-10-02 15:37 -0400
      Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-04 11:02 -0700
        Re: Local database library Lew <lewbloch@gmail.com> - 2013-10-04 15:56 -0700
          Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-05 12:06 -0700
            Re: Local database library Lew <lewbloch@gmail.com> - 2013-10-15 16:41 -0700
              Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-26 12:08 -0700
                Re: Local database library Martin Gregorie <martin@address-in-sig.invalid> - 2013-10-26 21:04 +0000
                  Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-26 18:58 -0700
                Re: Local database library Arne Vajhøj <arne@vajhoej.dk> - 2013-10-26 18:39 -0400
                  Re: Local database library Kevin Nathan <knathan@project54.com> - 2013-10-26 19:04 -0700
              Re: Local database library Arne Vajhøj <arne@vajhoej.dk> - 2013-10-26 18:33 -0400

Page 2 of 2 — ← Prev page 1 [2]

#642

On Tue, 15 Oct 2013 16:41:16 -0700 (PDT)
Lew <lewbloch@gmail.com> wrote:

>> and even more complex queries into JPA, I will start that process.  
>
>That would be insane and stupid.
>
>You don't convert queries into JPA; that's utterly backwards. If you
>have a data orientation, you use straight-up JDBC, not JPA.
>
>JPA is for creating persistent *objects*.
>

I have mentioned in this thread that I am *very* new to Java. But I've
been programming since 1979 (very little formal training). I mentioned
JPA because you said that was by far the easiest to code and maintain.
I know nothing about it.


>> Until that happens, I will have to muddle through with the brute
>> force methods that a previous programmer used...  
>
>It is weird to hear SQL described as "brute force".
>

It's not SQL that is the brute force method, it's putting the SQL into
every Java file that needs it, often the same query, or very similar,
query. It works, but not what I would like to see. The data access is
not split out from the rest of the code, and that was what I was
wanting to do. 


>Your queries, if they are reasonably standard SQL, go directly into
>JDBC 'Statement' objects. (Use 'PreparedStatement'.)
>

That's what we use. But the previous programmers simply stuck them in
every class (copy/paste), as needed, so the SQL is scattered throughout
the code. In the C++ code we have a locally written DAO library of
various classes like Transaction, Seller, Buyer, etc., and if you need
a list of Transactions by Seller, you simply include those classes and
call methods. So, I was hoping to make a simple mirror of our C++ DAO
library. I can't make it work. I don't know what I am missing (very new
to Java, remember). 

And I obviously cannot explain my problem well enough, so I will not
bother you with this anymore. Maybe, if I knew Java better, I could
formulate a better question or series of questions. For now, I will
just continue putting the raw SQL into every class and later, when I
know what I am doing in Java, I will clean it up.

Thanks for all of your efforts to help!

-- 
Kevin Nathan (Arizona, USA)  
Linux is not a destination, it's a journey -- enjoy the trip!

Linux 3.7.10-1.16-desktop
 11:50am  up 37 days 22:03,  15 users,  load average: 0.62, 0.63, 0.56

[toc] | [prev] | [next] | [standalone]

#643

On Sat, 26 Oct 2013 12:08:11 -0700, Kevin Nathan wrote:

> It's not SQL that is the brute force method, it's putting the SQL into
> every Java file that needs it, often the same query, or very similar,
> query. It works, but not what I would like to see. The data access is
> not split out from the rest of the code, and that was what I was wanting
> to do.
>
No, don't do that.

As I said earlier, put all your SQL into one class. You'll find a lot of 
commonality: starting/stopping the session and transaction start, commit/
rollback need only be written once with this approach. On top of that you 
need one method for each singleton select, update, insert or delete  and 
three methods for each query that uses a cursor (declare+run the query, 
fetch a row, close the cursor). But, before doing anything *collect all 
the queries* and look at them - you'll probably find several that are 
common to all programs and that, with minor tweaks such as adjusting the 
attribute list, can be combined.

Then use your judgement as to whether you want to write one large class 
containing all the SQL and is common to all programs or whether you want 
to break it up: one class for data retrieval and another for updates may 
work. If you break it up, put the common code for controlling sessions 
and transactions and dealing with SQL exceptions in a superclass and 
split the rest into two or more subclasses. Actually, once you've 
collected the SQL statements and looked at them, the way you'll want to 
group them into classes should become obvious.
 

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#646

On Sat, 26 Oct 2013 21:04:47 +0000 (UTC)
Martin Gregorie <martin@address-in-sig.invalid> wrote:

>No, don't do that.
>
>As I said earlier, put all your SQL into one class. 
><snip>
>But, before doing anything *collect all the queries* and look at them
>- you'll probably find several that are common to all programs and
>that, with minor tweaks such as adjusting the attribute list, can be
>combined.
><snip>
>Actually, once you've collected the SQL statements and looked at them,
>the way you'll want to group them into classes should become obvious.
>

We have, roughly, 20 classes in C++ dealing with just under 40 tables,
so I believe they have it broken up about right. Give me a few days to
put together a small sample of what they were doing in C++ and how I'd
like to implement it in Java (and, how part of it is already
implemented in Java).

My main problem is my unfamiliarity with Java, which is slowly
improving each day. I will see if I can't explain it better with a
simple example... 


-- 
Kevin Nathan (Arizona, USA)  
Linux is not a destination, it's a journey -- enjoy the trip!

Linux 3.7.10-1.16-desktop
 18:43pm  up 38 days  4:56,  15 users,  load average: 0.67, 0.69, 0.61

[toc] | [prev] | [next] | [standalone]

#645

On 10/26/2013 3:08 PM, Kevin Nathan wrote:
> On Tue, 15 Oct 2013 16:41:16 -0700 (PDT)
> Lew <lewbloch@gmail.com> wrote:
>>> and even more complex queries into JPA, I will start that process.
>>
>> That would be insane and stupid.
>>
>> You don't convert queries into JPA; that's utterly backwards. If you
>> have a data orientation, you use straight-up JDBC, not JPA.
>>
>> JPA is for creating persistent *objects*.
>>
>
> I have mentioned in this thread that I am *very* new to Java. But I've
> been programming since 1979 (very little formal training). I mentioned
> JPA because you said that was by far the easiest to code and maintain.
> I know nothing about it.

It often is.

But not always.

> It's not SQL that is the brute force method, it's putting the SQL into
> every Java file that needs it, often the same query, or very similar,
> query. It works, but not what I would like to see. The data access is
> not split out from the rest of the code, and that was what I was
> wanting to do.
>
>> Your queries, if they are reasonably standard SQL, go directly into
>> JDBC 'Statement' objects. (Use 'PreparedStatement'.)
>
> That's what we use. But the previous programmers simply stuck them in
> every class (copy/paste), as needed, so the SQL is scattered throughout
> the code. In the C++ code we have a locally written DAO library of
> various classes like Transaction, Seller, Buyer, etc., and if you need
> a list of Transactions by Seller, you simply include those classes and
> call methods. So, I was hoping to make a simple mirror of our C++ DAO
> library. I can't make it work. I don't know what I am missing (very new
> to Java, remember).

You need to distinguish between:
- DAO classes vs no DAO classes
- SQL in many classes vs SQL in one class

Regarding the last question, then I will suggest
having the SQL near the code using the SQL. That
is where it belongs.

 > And I obviously cannot explain my problem well enough, so I will not
 > bother you with this anymore. Maybe, if I knew Java better, I could
 > formulate a better question or series of questions. For now, I will
 > just continue putting the raw SQL into every class and later, when I
 > know what I am doing in Java, I will clean it up.

I think you should post more questions - maybe with some small
examples.

Arne

[toc] | [prev] | [next] | [standalone]

#647

On Sat, 26 Oct 2013 18:39:04 -0400
Arne Vajhøj <arne@vajhoej.dk> wrote:

>You need to distinguish between:
>- DAO classes vs no DAO classes
>- SQL in many classes vs SQL in one class
>
>Regarding the last question, then I will suggest
>having the SQL near the code using the SQL. That
>is where it belongs.
>

There is a pretty good structure right now in the C++ classes and I
*think* that is sort of what I want to follow. I just need to figure
out how to do it in Java.


>I think you should post more questions - maybe with some small
>examples.
>

I will work on that over the next few days and try to come up with a
simple, decent example that shows my problem(s).


-- 
Kevin Nathan (Arizona, USA)  
Linux is not a destination, it's a journey -- enjoy the trip!

Linux 3.7.10-1.16-desktop
 18:59pm  up 38 days  5:11,  15 users,  load average: 0.37, 0.38, 0.46

[toc] | [prev] | [next] | [standalone]

#644

On 10/15/2013 7:41 PM, Lew wrote:
> Kevin Nathan wrote:
>> This is *not* something for general use by others. We have about 60
>> clients and add maybe one or two a year, some years. It's only for our
>> use in-house and will never see the light of day. If they keep me on
>> long enough, I will redo the program for JPA, but a quick solution is
>> necessary. Once I learn how to convert SQL like this:
>>
>> String command =
>>            "SELECT "
>>          + "SELLERID, HEAD, DESCRIP, "
>>          + "ROUND(WEIGHT/HEAD) AS AVG_WGT, "
>>          + "WEIGHT, BUYERID, PRICE, BUYERPEN, "
>>          + "(SELECT name FROM seller "
>>          + "WHERE date=(SELECT MAX(start) FROM sales WHERE sel=1) "
>>          + "AND number=trans.sellerid) AS NAME, "
>>          + "(SELECT GROUP_CONCAT(tag_num) FROM backtags "
>>          + "WHERE trans_id=trans.trans_id and ckin_date=(SELECT
>> MAX(start) FROM sales "
>>          + "WHERE sel=1) GROUP BY trans_id) AS ALLTAGS "
>>          + "FROM trans "
>>          + "WHERE TRANS_ID = '" + transNum + "' "
>>          + "AND date=(SELECT MAX(start) FROM sales WHERE sel=1)";
>>
>> and even more complex queries into JPA, I will start that process.
>
> That would be insane and stupid.
>
> You don't convert queries into JPA; that's utterly backwards. If you have
> a data orientation, you use straight-up JDBC, not JPA.
>
> JPA is for creating persistent *objects*.
>
> It is not for creating queries or data sets.

I would claim that JPQL is for creating queries. After all
the Q does mean query.

:-)

In fact is is quite common to use queries with JPA.

And code using JDBC with SQL queries will except for
very simple cases also require queries when converted
to JPA.

Obviously the queries will be very different with an ORM
compared to raw SQL. That is why ORM's typical somes with
their own query language designed for the ORM context.

My point is that I don't see a conceptual problem
with converting JDBC and SQL queries into JPA and
queries.

It will just be a different type of query.

And one would need to consider if the query is
JPA friendly or not.

> JPA is for object orientation. JDBC is for set orientation.

True.

But often both can be used to solve the same problem.

Arne

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | comp.lang.java.databases

csiph-web