Database programming with Java

Discussion in 'Java' started by John O'Conner, Jan 14, 2004.

  1. MS Access has made database programming simple for those who choose to
    work in that environment. What db development environments are
    available for Java developers to make quick db applications? What are
    some of db options and Java libraries for creating databases for only
    2-10 users in a LAN environment?

    Additionally, can you recommend a tutorial for how to design db apps
    in Java? I'm primarily interested in where the db SQL code should
    go...obviously not in the actual objects I'm storing, right? I mean, I
    don't want my Customer objects to worry about how to store
    themselves...

    Best regards,
    John O'Conner
     
    John O'Conner, Jan 14, 2004
    #1
    1. Advertising

  2. John O'Conner

    Rhino Guest

    I'm not sure what you mean by the word "quick" in your second sentence.

    Two DBMSes (DataBase Management Systems) that I use with my applications,
    applets, and servlets are DB2 and MySQL. I find both of them quick in the
    sense that they aren't terribly hard to use and the Java coding I need to do
    to access my databases - whether for creating tables, populating them, or
    changing them - is not very hard either so I can do it quickly. On the other
    hand, I've been using and teaching DB2 for many years and writing programs
    that access it for just as long. MySQL has enough similarities to DB2 that
    I've been able to get functional with it fairly quickly, even though I
    haven't been using it very long.

    Some people use "quick" to mean that something is free and/or is easy to
    install and configure. I've been led to believe that DB2 is free as long as
    you aren't using it in a production environment. MySQL is apparently also
    free under some similar circumstances, although I'm no lawyer so don't quote
    me on that! I found installation and configuration for both quite
    straight-forward and fast but I've been using relational databases for a
    long time and know the concepts. Someone who is new to relational databases
    and doesn't understand the key concepts may find both products confusing and
    time-consuming to install and configure - or not: your mileage may vary!

    As far as size goes, I'm not aware of any DBMSes that are actually targetted
    specifically for very small user bases like 2-10 people or LAN environments.
    Most serious DBMSes, like DB2, are written to be scalable from very small
    environments like the one you're describing to very large ones that include
    thousands or millions of users and tables that can hold terabytes of data on
    a variety of different hardware platforms.

    In its most basic sense, designing an application to use a database is not
    very different from designing a database to use any other kind of data
    store, like a flat file. A program written to access a few lines of a flat
    file would be identical to one that accesses millions of rows in a
    relational database, except for the few lines of the program that actually
    read or update the data.

    The SQL statements that read and update the relational data certainly don't
    get stored in the database in the normal course of events. (Actually, the
    *catalog* of the database probably WILL store the statements but that's for
    purposes of optimizing the performance of the program and is more-or-less
    transparent to you; your program certainly won't try to access those stored
    statements directly.) The statements that access your database are coded in
    your program along these lines:
    /* Create a new empty demonstration table. */

    String createTableSQL =

    "create table " + demoTable + " " +

    "(empno char(6) not null, " +

    "firstnme varchar(12) not null, " +

    "midinit char(1) not null, " +

    "lastname varchar(15) not null, " +

    "workdept char(3), " +

    "phoneno char(4), " +

    "hiredate date, " +

    "job char(8), " +

    "edlevel smallint not null, " +

    "sex char(1), " +

    "birthdate date, " +

    "salary decimal(9,2), " +

    "bonus decimal(9,2), " +

    "comm decimal(9,2), " +

    "primary key(empno))";

    Statement createTableStmt = null;

    try {

    createTableStmt = conn01.createStatement();

    int numRows = createTableStmt.executeUpdate(createTableSQL);

    if (DEBUG) System.out.println(CLASS_NAME + "." + METHOD_NAME + " -
    numRows = " + numRows);

    }

    catch (SQLException sql_excp) {

    System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
    SQLException attempting to create " + demoTable + ". Error: " +
    sql_excp);

    sql_excp.printStackTrace();

    System.exit(16);

    }

    /* Dispose of the statement and commit. */

    try {

    createTableStmt.close();

    conn01.commit();

    }

    catch (SQLException sql_excp) {

    System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
    SQLException while closing statement or committing. Error: " +
    sql_excp);

    sql_excp.printStackTrace();

    System.exit(16);

    }


    However, your programs can also generate SQL statements dynamically. For
    example, you could show a table and its contents to your user, then give
    them a text area into which they could write SQL that extracts a subset of
    the data that meets conditions of interest to them, and then read that
    statement into your program, execute the statement, and display the
    resulting subset for the user. Of course you could also do inserts, updates,
    and deletes in the same fashion. You could also start with only a text area
    and let the user write any SQL they like against any table they like and
    then simply take the statement into your program and try to execute it.

    To see some examples that illustrate some of what I'm saying, have a look at
    the demo\jfc\TableExample\src folder in your JDK, especially
    TableExample2.java. Also, contact me offline if you want to have some
    relatively short sample JDBC programs that I've written; I've got two for
    DB2 and two for MySQL.

    Also, for a free online tutorial on using JDBC for your database access,
    have a look at
    http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/. I think
    it's a pretty good place to start if you've never looked at accessing a
    database from a Java program before.

    There's one other good place to talk databases and java:
    comp.lang.java.databases. Someone there may be able to suggest databases
    that are "quick" in the sense that *you* mean by that word ;-)

    Rhino


    "John O'Conner" <> wrote in message
    news:...
    > MS Access has made database programming simple for those who choose to
    > work in that environment. What db development environments are
    > available for Java developers to make quick db applications? What are
    > some of db options and Java libraries for creating databases for only
    > 2-10 users in a LAN environment?
    >
    > Additionally, can you recommend a tutorial for how to design db apps
    > in Java? I'm primarily interested in where the db SQL code should
    > go...obviously not in the actual objects I'm storing, right? I mean, I
    > don't want my Customer objects to worry about how to store
    > themselves...
    >
    > Best regards,
    > John O'Conner
     
    Rhino, Jan 14, 2004
    #2
    1. Advertising

  3. John O'Conner wrote:

    > MS Access has made database programming simple for those who choose to
    > work in that environment. What db development environments are
    > available for Java developers to make quick db applications? What are
    > some of db options and Java libraries for creating databases for only
    > 2-10 users in a LAN environment?


    It won't matter which DB you'll use (you can even use Acces), you only need
    to learn JDBC, which will behave the same for all RDBMS's out there (the
    actual SQL you'll write is dependent on the DB offcourse).
    You can find a JDBC tutorial at
    http://java.sun.com/docs/books/tutorial/jdbc/

    > Additionally, can you recommend a tutorial for how to design db apps
    > in Java? I'm primarily interested in where the db SQL code should
    > go...obviously not in the actual objects I'm storing, right? I mean, I
    > don't want my Customer objects to worry about how to store
    > themselves...


    What you need is the DAO pattern:
    http://java.sun.com/blueprints/patterns/DAO.html

    > Best regards,
    > John O'Conner



    --
    Kind regards,
    Christophe Vanfleteren
     
    Christophe Vanfleteren, Jan 14, 2004
    #3
  4. John O'Conner

    Tony Morris Guest

    Once you've learnt JDBC, you will probably be inclined to manage your SQL a
    little better.
    I suggest looking at O-R Mapping tools, such as Hibernate.

    --
    Tony Morris
    (BInfTech, Cert 3 I.T., SCJP[1.4], SCJD)
    Software Engineer
    IBM Australia - Tivoli Security Software
    (2003 VTR1000F)


    "Christophe Vanfleteren" <> wrote in message
    news:DAjNb.12380$-ops.be...
    > John O'Conner wrote:
    >
    > > MS Access has made database programming simple for those who choose to
    > > work in that environment. What db development environments are
    > > available for Java developers to make quick db applications? What are
    > > some of db options and Java libraries for creating databases for only
    > > 2-10 users in a LAN environment?

    >
    > It won't matter which DB you'll use (you can even use Acces), you only

    need
    > to learn JDBC, which will behave the same for all RDBMS's out there (the
    > actual SQL you'll write is dependent on the DB offcourse).
    > You can find a JDBC tutorial at
    > http://java.sun.com/docs/books/tutorial/jdbc/
    >
    > > Additionally, can you recommend a tutorial for how to design db apps
    > > in Java? I'm primarily interested in where the db SQL code should
    > > go...obviously not in the actual objects I'm storing, right? I mean, I
    > > don't want my Customer objects to worry about how to store
    > > themselves...

    >
    > What you need is the DAO pattern:
    > http://java.sun.com/blueprints/patterns/DAO.html
    >
    > > Best regards,
    > > John O'Conner

    >
    >
    > --
    > Kind regards,
    > Christophe Vanfleteren
     
    Tony Morris, Jan 14, 2004
    #4
  5. On Wed, 14 Jan 2004 22:41:39 GMT, Christophe Vanfleteren wrote:
    >John O'Conner wrote:


    >It won't matter which DB you'll use (you can even use Acces), you only need
    >to learn JDBC, which will behave the same for all RDBMS's out there (the
    >actual SQL you'll write is dependent on the DB offcourse).
    >You can find a JDBC tutorial at
    >http://java.sun.com/docs/books/tutorial/jdbc/
    >


    This is not true, you can access databases under Java in many ways.
    JDBC is just a standard equivalent of ODBC, and mostly used probably,
    but not the only one.
    Many database access libraries for Java use JDBC underneith them, but
    you'll won't have to learn JDBC to use them.

    --
    Gerbrand van Dieijen
     
    Gerbrand van Dieijen, Jan 15, 2004
    #5
  6. On 14 Jan 2004 12:25:56 -0800, John O'Conner wrote:
    >MS Access has made database programming simple for those who choose to
    >work in that environment. What db development environments are
    >available for Java developers to make quick db applications? What are
    >some of db options and Java libraries for creating databases for only
    >2-10 users in a LAN environment?
    >
    >Additionally, can you recommend a tutorial for how to design db apps
    >in Java? I'm primarily interested in where the db SQL code should
    >go...obviously not in the actual objects I'm storing, right? I mean, I
    >don't want my Customer objects to worry about how to store
    >themselves...
    >


    JBuilder has an (Visual) Developer which probably most similar with regard
    to ease of use to MS Access. You can quickly build up a database form,
    etc. just like in Access but you'll have the full power of Java.

    However, in your case (and many cases), the problem is probably not so
    much accessing the database, but how to get your data and logic (Customer,
    etc.) into a database or onto disk.

    You could use JDO for this. This way a object is automatically stored and
    loaded into a database. Tables and fields, are created automatically and
    loading and saving is done almost transparantly.
    I use JDO for quite a while now, and I find that I work much more
    efficient then with manually storing objects via SQL into databases.
    More information on jdo can be found at :http://www.jdocentral.com

    There are other systems for storing java objects in databases
    transparantly, but JDO is an offical standard.

    Feel free to reply or post if you want more information.


    --
    Gerbrand van Dieijen
     
    Gerbrand van Dieijen, Jan 15, 2004
    #6
  7. John O'Conner

    Tony Morris Guest

    There is only one way to access a database from Java, and that is JDBC.
    JDBC is not equivalent to ODBC except that provides a common functionality
    (the ability to access a database).

    If one were to attempt to issue database commands using the proprietrary
    database protocol (which is possible), one would effectively be writing a
    JDBC driver that is non-compliant with the specification.

    If you are going to use JDBC, it would pay to learn how it works, the
    specification, and the API involved.

    --
    Tony Morris
    (BInfTech, Cert 3 I.T., SCJP[1.4], SCJD)
    Software Engineer
    IBM Australia - Tivoli Security Software
    (2003 VTR1000F)


    "Gerbrand van Dieijen" <> wrote in message
    news:...
    > On Wed, 14 Jan 2004 22:41:39 GMT, Christophe Vanfleteren wrote:
    > >John O'Conner wrote:

    >
    > >It won't matter which DB you'll use (you can even use Acces), you only

    need
    > >to learn JDBC, which will behave the same for all RDBMS's out there (the
    > >actual SQL you'll write is dependent on the DB offcourse).
    > >You can find a JDBC tutorial at
    > >http://java.sun.com/docs/books/tutorial/jdbc/
    > >

    >
    > This is not true, you can access databases under Java in many ways.
    > JDBC is just a standard equivalent of ODBC, and mostly used probably,
    > but not the only one.
    > Many database access libraries for Java use JDBC underneith them, but
    > you'll won't have to learn JDBC to use them.
    >
    > --
    > Gerbrand van Dieijen
    >
     
    Tony Morris, Jan 15, 2004
    #7
  8. Tony Morris wrote:

    > There is only one way to access a database from Java, and that is JDBC.
    > JDBC is not equivalent to ODBC except that provides a common functionality
    > (the ability to access a database).
    >
    > If one were to attempt to issue database commands using the proprietrary
    > database protocol (which is possible), one would effectively be writing a
    > JDBC driver that is non-compliant with the specification.
    >
    > If you are going to use JDBC, it would pay to learn how it works, the
    > specification, and the API involved.
    >


    I agree. That is also why I didn't mention alternatives like Hibernate, JDO,
    or things like Spring JDBC. It doesn't hurt to know the basics before you
    use another abstraction.

    --
    Kind regards,
    Christophe Vanfleteren
     
    Christophe Vanfleteren, Jan 15, 2004
    #8
  9. John O'Conner

    Wendy S Guest

    "Tony Morris" <> wrote in message
    news:bu53ob$p76$...
    > There is only one way to access a database from Java, and that is JDBC.


    Not true. There are databases that have their own Java API's, such as IBM's
    UniData and UniVerse which have UniObjects for Java. (There is a JDBC
    driver, but it's a square-peg-round-hole solution when the data is
    multi-valued.)

    Anyway, it's not pertinent to the original question, but there are other
    ways to talk to databases with Java (Sockets, SOAP over HTTP) and not all
    DBMSs speak SQL as their first language.

    --
    Wendy in Chandler, AZ
     
    Wendy S, Jan 15, 2004
    #9
  10. John O'Conner

    Tony Morris Guest

    Ok, I was hoping I didn't get caught out.
    I didn't want to confuse the issue any further.
    My apologies.

    --
    Tony Morris
    (BInfTech, Cert 3 I.T., SCJP[1.4], SCJD)
    Software Engineer
    IBM Australia - Tivoli Security Software
    (2003 VTR1000F)


    "Wendy S" <> wrote in message
    news:bu75a0$gq5$...
    > "Tony Morris" <> wrote in message
    > news:bu53ob$p76$...
    > > There is only one way to access a database from Java, and that is JDBC.

    >
    > Not true. There are databases that have their own Java API's, such as

    IBM's
    > UniData and UniVerse which have UniObjects for Java. (There is a JDBC
    > driver, but it's a square-peg-round-hole solution when the data is
    > multi-valued.)
    >
    > Anyway, it's not pertinent to the original question, but there are other
    > ways to talk to databases with Java (Sockets, SOAP over HTTP) and not all
    > DBMSs speak SQL as their first language.
    >
    > --
    > Wendy in Chandler, AZ
    >
    >
     
    Tony Morris, Jan 15, 2004
    #10
  11. John O'Conner

    steve Guest

    On Thu, 15 Jan 2004 4:25:56 +0800, John O'Conner wrote
    (in message <>):

    > MS Access has made database programming simple for those who choose to
    > work in that environment. What db development environments are
    > available for Java developers to make quick db applications? What are
    > some of db options and Java libraries for creating databases for only
    > 2-10 users in a LAN environment?
    >
    > Additionally, can you recommend a tutorial for how to design db apps
    > in Java? I'm primarily interested in where the db SQL code should
    > go...obviously not in the actual objects I'm storing, right? I mean, I
    > don't want my Customer objects to worry about how to store
    > themselves...
    >
    > Best regards,
    > John O'Conner


    check out jdeveloper preview on oracle's site ( down load the preview
    version)
    or even the eclipse project.
    finally there are even companies giving away free software allowing you to
    design something graphically ( drag & drop) then producing java code to
    get/modify records.

    steve
     
    steve, Jan 18, 2004
    #11
  12. John O'Conner

    steve Guest

    On Thu, 15 Jan 2004 4:25:56 +0800, John O'Conner wrote
    (in message <>):

    > MS Access has made database programming simple for those who choose to
    > work in that environment. What db development environments are
    > available for Java developers to make quick db applications? What are
    > some of db options and Java libraries for creating databases for only
    > 2-10 users in a LAN environment?
    >
    > Additionally, can you recommend a tutorial for how to design db apps
    > in Java? I'm primarily interested in where the db SQL code should
    > go...obviously not in the actual objects I'm storing, right? I mean, I
    > don't want my Customer objects to worry about how to store
    > themselves...
    >
    > Best regards,
    > John O'Conner


    oops sorry.

    the jdeveloper has a number of test cases /projects, showing clearly where &
    how the code should be placed.
    but for the record , put your business case sql code on the server, then just
    do function/procedure calls to it from your app, & return result sets.
     
    steve, Jan 18, 2004
    #12
  13. Thanks to all for the great answers. Your help is much appreciated.

    --
    John O'Conner
     
    John O'Conner, Jan 19, 2004
    #13
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul Perrone
    Replies:
    0
    Views:
    14,487
    Paul Perrone
    Aug 21, 2003
  2. Derek Clarkson

    Database programming with Java

    Derek Clarkson, Jan 14, 2004, in forum: Java
    Replies:
    1
    Views:
    347
    John O'Conner
    Jan 14, 2004
  3. Matt
    Replies:
    35
    Views:
    10,710
    George Neuner
    Jul 22, 2004
  4. Casey Hawthorne
    Replies:
    4
    Views:
    1,042
    Jarek Zgoda
    Aug 4, 2006
  5. Joe Mayo
    Replies:
    168
    Views:
    3,442
    David Thompson
    Oct 22, 2007
Loading...

Share This Page