Re: Local database library

From:

=?UTF-8?B?QXJuZSBWYWpow7hq?= <arne@vajhoej.dk>

Newsgroups:

comp.lang.java.databases

Date:

Tue, 01 Oct 2013 20:53:32 -0400

Message-ID:

<524b6e8d$0$304$14726298@news.sunsite.dk>

On 10/1/2013 6:41 PM, Kevin Nathan wrote:

On Tue, 01 Oct 2013 17:38:01 -0400
Arne Vajh??j <arne@vajhoej.dk> wrote:

On 10/1/2013 4:52 PM, Kevin Nathan wrote:

I'm a Java newbie, but have been programming for many years. I'm
converting an existing application from C++ to Java. In an effort to
clean it up a bit, I would like to build a local library of database
access routines because right now everyone does their own connecting
and SQL writing, with way too much duplication.

Create one or more classes that in more or less OOP style encapsulate
the JDBC code.

That should not be much different between Java and C++.

It's not, and that is what I am doing now, but at present it needs to
be copied into source files (at least, that's what the other
developers have done -- I'm trying to find a better way). I am more
interested in learning about the file structure I would use for a
library. I think I have it started, but a tutorial on building a
library from scratch would be advantageous at this point. It's a lot of
trial and error right now from the little bit of info I have found on
the internet.

The Java standard and somewhat enforced by the tools is a source
file per top level type.

So the object model more or less determines the source files.

Often in Java you would not use JDBC and SQL but instead an ORM,
given a time crunch that may not be an option for you even if the
task at hand leaned towards it (and that is not give since you
did not specify what you are working on).

I am a self-taught programmer and have never been involved with an ORM
so that's another whole level of instruction I will need! :-) I am
using NetBeans and that is helping a bit. But again, a tutorial on
building a Java library would be the best. This is the best I've found
on that, so far:

http://stackoverflow.com/questions/6205483/creating-accessing-libraries-in-netbeans

which is why I am still doing a lot of trial and error.

We have about 30 tables in the database (for livestock auction markets)
and I want to be able to just call a function from the main programs to
get the data, like:

   getTransData()
   getBuyerData()...

instead of having this in the main file:

try {
     Statement st = (Statement) con.createStatement();
     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)";
     ResultSet rs = st.executeQuery(command);
} else {
     return;
}

So, I know what I *want* to do, just not the best way to do it in Java.
If that makes any sense.

Java is OO like C++.

So:

getTransData()
getBuyerData()...

assumes that the methods are in the same class as the calling method.

If you want to move it out you will need to call a method on an
object.

Maybe something like:

TransactionManager txm = new TransactionManager();
List<Transaction> txl = txm.getTransactions();

Arne