Discussions

News: A Database-centric Approach to J2EE Application Development

  1. How do client/server shops enter the J2EE world? Toon Koppelaars proposes how they can move into the our world using database-centric mapping.

    There is actually no such thing as one J2EE architecture.
    The author studies various alternative MVC approaches by looking at the amount of application code that is deployed in each tier.

    The article:
    - presents alternative architectural approaches
    - establishes some classification of application code
    - proceeds with mapping of code into execution environments
    - outlines the database centric approach and use of VPD, Application Context to simplify development
    - wraps up with lessons learned
      * Do not trust Java Geeks
      * VPD Hard and Soft Parses
      * Policy function fires Twice
      * Sorting
    The biggest benefit of the database-centric approach can be found in the Total Cost of Ownership (TCO) of the J2EE application.

    History has taught us that 20 percent or less of the TCO of any software application lies in the initial creation of the application. The bulk, 80 percent or more, of the TCO lies in the run and maintenance phase of the application.
    Read this award-winning article on OTN:
    A Database-centric Approach to J2EE Application Development

    Congratulations to Toon Koppelaars who wrote this article.
    -
    Sudhakar Ramakrishnan
    Oracle Corp.

    Threaded Messages (121)

  2. It's the DB, stupid.[ Go to top ]

    "... Data Logic and Business Logic Code in PL/SQL will enable you to considerably reduce the risk ..."

    DB SQL is key to project success. Shops and Java geeks that do not learn this will not be effective (even in PHP and ASP this is true).

    .V
  3. It's the ORM, stupid.[ Go to top ]

    "... Data Logic and Business Logic Code in PL/SQL will enable you to considerably reduce the risk ..."DB SQL is key to project success. Shops and Java geeks that do not learn this will not be effective (even in PHP and ASP this is true)..V
    I know this is controversial, but... I could not disagree more strongly.

    I have known situations where PL/SQL equivalents have come close to killing off projects. The reason? It's proprietary - migration between databases is hard, and companies can be stuck with innapropriate legacy systems. I feel that the DB vendors are decades behind the times, desperately trying to keep developers coding in proprietary languages embedded within databases. I strongly believe that the future is in Object/Relational systems that allow queries, data relations and constraints to expressed in portable ways. Once object persistence query languages become suitably expressive (such as in Hibernate and JDOQL 2.0) the DB admins can optimise these queries, and not PL/SQL. The alternative is for the DB vendors to realise that this is the 2000s and not the 1970s and come up with standard, guaranteed compatible SQL implementations. Business Logic must be portable. Well, that's my experience anyway.
  4. It's the ORM, stupid.[ Go to top ]

    +1

    It has always been the ORM. Embedding some logic in the database (Transact SQL, PL/SQL whatever procedural languaje they have) and pulling back the info to an upper layer in Java it's nothing but an ugly hack in my point of view.

    To make an analogy, think about rich client interfaces for the common web browser. It involves heavy Javascript coding, messing with some XML HTTP transport, CSS, and linking that to our Java presentation layer. What a mess!

    I rather deal with an object oriented domain model which can even use some functional business rules engine to help, and that is transparently persisted and retrieved.

    I am not completely against coding some things in DB procedural languajes, things like referential integrity triggers for example, or table and index maintenance scripts, thing a common DBA should be aware of.
  5. It's the ORM, stupid.[ Go to top ]

    I guess I have problems with Domain models in lieu of complex table structures. We attempted to use a strong Domain model to support our application behavior but nothing ended up making sense and everything seemed to just hide hacks.

    Example Issue: Account has Patientss which is true in all cases, but what if you wanted a reserved scope of those Patients that are active in a given period?

    To me, domain models seem to fall short in favor of a procedural approach. Finer grained object without component relationships.

    Please share your thoughts---
  6. It's the ORM, stupid.[ Go to top ]

    what if you wanted a reserved scope of those Patients that are active in a given period?
    You ask for them?

    Query q = pm.newQuery( Patient.class );
    q.declareParameters( "Account a" );
    q.setFilter( "account == a && active == true" );
    Collection<Patient> patients = q.execute( anAccount );

    God bless,
    -Toby Reyelts
  7. It's the ORM, stupid.[ Go to top ]

    You ask for them?Query q = pm.newQuery( Patient.class );q.declareParameters( "Account a" );q.setFilter( "account == a && active == true" );Collection<Patient> patients = q.execute( anAccount );God bless,-Toby Reyelts
    Thank you Toby, that's exactly what I am talking about.

    (By the way what's that 1700 table database abomination someone posted in another thread, got me thinking....hell on earth...should redesign...)

    Saludos
  8. RE : It's the ORM, stupid.[ Go to top ]

    Wrong ... its the domain

    How about :

    Account account = Account.findById("x");
    Iterator activePatients = account.listActivePatients(DateRange dateRange);

    within Account implementation :

    public class Account {
       ...

       public Iterator listPatients(DateRange dateRange) {
           return getAccountSystem().listPatients().iterator();
       }
        
       //Delegated Approach to utilize back end filtering if list can be large
       public Iterator listActivePatients(DateRange dateRange) {
          ...
           return getAccountSystem().listActivePatients(dateRange).iterator();
       }

       //Direct approach to utilize if list is known to not be to large
       public Iterator listActivePatients(DateRange dateRange) {
         return new FilteredIterator(listPatients(), new IPredicate() {
                 public boolean accept(Object obj) {
                     return ((Patient) obj).determineIfActiveWithinRange(dateRange);
                 }
          });
       }
    }

    The AccountSystem is an implemantion of a pluggable service component that implements an interface defined within your domain model abstracting out the integration with the backend ... allows direct use of jdbc or hibernate or ibatis or whatever ... this is not the concern of the problem domain it is merely one possible implementation of the integration layer.

    (1) UI -> PD <- SI(hibernate) -> DB
    (2) UI -> PD <- SI(JDBC) -> DB
    (3) UI -> PD <- SI(...) -> DB

    PD remain identical across all implementations and ecnapsulates business logic
    in a coherent, consistent, testable, manageable layer.

    (Thanks Jeff Deluca + Paul Szego from nebulon)
  9. It's the ORM + DB, stupid.[ Go to top ]

    I guess I have problems with Domain models in lieu of complex table structures. We attempted to use a strong Domain model to support our application behavior but nothing ended up making sense and everything seemed to just hide hacks.Example Issue: Account has Patientss which is true in all cases, but what if you wanted a reserved scope of those Patients that are active in a given period?To me, domain models seem to fall short in favor of a procedural approach. Finer grained object without component relationships.Please share your thoughts---
    It depends. If your application is merely a front end to the DB where the logic involves basically filtering of sets then I think the way to go is table approach, maybe expressing some of your logic as stored procedures.

    Even then, most likely you will need some "Helper" objects besides JDBC to perform the retrieval and format the results to be consumable by any other component of the application (presentation, transformation, etc.)

    Now this kind of programming is what seems cumbersome to me, because basically I am forced to think about the transformation of data records into some form of object structure, even if it's simple. Supose I have a DB schema with 1700 tables (like MANY people has been posting as example recently) ,and I am suposed to retrieve a certain set of data by performing joins, comparations and such. It's ridiculous to think of a domain model with 1600+ clases, therefore I need to apply some custom query and then hidrate some kind of generic object with the result so it can be prepared for presentation, transformation, flattening, whatever.

    My point is that a good domain model has to take such things in consideration, but it's NOT the role of the domain logic to mess with the DB, I think.

    Eric Evans has a very good argument in favor of domain models in his book and he proposes the idea utilizing a Repository object to perform the DB operations, decoupling data access specific routines from business logic.

    This starts to make senese when you use an object persistence engine like Hibernate. I think this argument of where to put the business logic is old, and we should accept the fact that procedural logic in the DB adds a number of things to your application:

    - Puts business logic in a different place than your main application
    - It's harder to maintain and syncronize
    - It's vendor specific
    + It's faster than ORM because can be agresively tuned
    .. some other things you can think of.

    I would like to hear more from the people
  10. It's the ORM + DB, stupid.[ Go to top ]

    I've been looking at persistence mappers and how they possibly relate to domain models.

    I believe that business logic should be stateless or reside in a stateless object-- when you introduce state, you lose your ability to reuse/couple to other objects. Basically, business logic acts upon state as provided by something like Hibernate or JDO. I'm heading towards a Service Oriented Architecture (it's not a framework, just a pattern!).

    When I start to revisit the concept of Domain models, they only can function in accordance to application requirements, not business requirements (there is an important seperation here).

    Example:
    OrderSession has an Order object that has state, and has access to business services where it can pass off the order and other state data to process.

    Primarily speaking, the Order object doesn't know how to persist itself nor does it know if it's a valid order and its line items are valid. The OrderSession coordinates the when/how with our stateless business services.
  11. It's the ORM + DB, stupid.[ Go to top ]

    When I start to revisit the concept of Domain models, they only can function in accordance to application requirements, not business requirements (there is an important seperation here).
    I think I understand your distinction between application and business requirements, hope I got it right, but I think the domain model IS driven by business requirements. After you're done (at least initially) with some domain model you can tune it to the applications requirements (this includes the database).

    Here's an example: recently we built an small on-line purchasing application using a domain model. The PurchaseOrder object has an state property that represents it's current place in the process flow (typical), one business requirement states that some administrators can alter some of the data in the order which triggers a change of state. We implemented this for this very simple application using the state pattern which works just as expected, and more important, our customer understood what we were up to. Now here comes the database. Since every different state involves a subclass of "State" you just can´t save it so a table, you need some more thinking. Fortunately we used some home grown ORM to save the hierarchy of objects to the database and we fine tuned the SQL for performance. Now, to be even more heretic than anyone here, we built this thingie in object oriented PHP against an SQL Server 2000 db!
    This kind of development is what I think represents application requirements, if I got it right.


    Later the system grew and we turned it into Java. Because the initial domain model was rightly done, transition was a breeze, code was generated from the model and we use Hibernate for persistance now. I expect that we can get some more control over the generated SQL in the next version of Hibernate.

    I believe you can´t get to this level having most of your business logic in the DB, supose we were going to switch from SQL Server to Oracle or DB2.

    Just a thought.
  12. It's the ORM + DB, stupid.[ Go to top ]

    I am not so smart to design databases with 1700 tables, but good designed database doe's not conflict with any clients (ORM is not an exeption too). I know single system implemented this way, I do not know how many tables and procedures it has, because I saw Views only and any client is free to connect,update and do not care about "business logic", it secure and safe to connect with any programming language or reporting tool. This article advocates this way too, it is not about procedures vs OOP, I found it is about the right database design. There are more components in system than database and ORM is not a wrong technology too, but database designed in the right way is open for any architecture or technology and I can not understand how it is not portable. I recomend to read this article for everybody, there is more things to read, not political stuff only.
  13. I think this argument of where to put the business logic is old, and we should accept the fact that procedural logic in the DB adds a number of things to your application:

    - Puts business logic in a different place than your main application
    - It's harder to maintain and syncronize
    - It's vendor specific
    + It's faster than ORM because can be agresively tuned .. some other things you can think of.
    + makes your business logic reusable by a dozen of other applications within your enterprise, most of which are not written in Java...
  14. Not Java only[ Go to top ]

    The architecture should not be Java only. How would you design you architecture if it was not Java, but some other lang (generic lang for purpose of the discussion)?

    So you want to port the application to another lang, what is now your design?

    And design patterns should be portable also, you should be able to implement in another lang,

    Very few shops have only one language. I think some of the people that maybe know only one language (less experience developing?) have heard a bit to much marketing; and that is not scientific. I argue you to master SQL, check out "SQL for Smarties" by Celko - not one approach in there AFAIK works in O/R/M(ex: tree graphs like self joins), but work in Relational Mapping (iBatis , JDBC, Ted Husted's approach, etc.)
    Long after Java, there will still be SQL you will find. I think that some sales vendors over sold what ORM should be used for, it is very narrow.

    So in a muti language shop, would you lean on a ANSI SQL engine more, would you duplicate the ORM logic?
    For example, display a list of customers, then display a list of invoices, then display the items orderd. (Select or ? )
    To tune it, does the DBA need to know "Java" and how it works? How do you do a "showplan"-do you know what statement it does?

    Do you think dual layers make it less effective?

    .V
  15. Not Java only[ Go to top ]

    The architecture should not be Java only. How would you design you architecture if it was not Java, but some other lang (generic lang for purpose of the discussion)
    The same way, with ORM or some other database abstraction.
    Very few shops have only one language.
    They usually develop one app in one language though. For example, there may be a legacy of C++, but now they use Java.
    Long after Java, there will still be SQL you will find
    Only in legacy systems, I would expect. The same way that there is a lot of COBOL and assembler still around. But that is no excuse for how we develop *now*.
    I think that some sales vendors over sold what ORM should be used for, it is very narrow.
    Why? Query languages in good ORM systems can do almost everything that SQL can do (there are even trigger-style callbacks in JDO).
    So in a muti language shop, would you lean on a ANSI SQL engine more, would you duplicate the ORM logic?
    You choose one language to write the tier that accesses the database, then expose these functions to other languages in standard ways, such as CORBA, SOAP or sockets.
    For example, display a list of customers, then display a list of invoices, then display the items orderd. (Select or ? )
    This is what you use the query language of the ORM form
    To tune it, does the DBA need to know "Java" and how it works?
    No, just the portable query language of the ORM layer: Hibernate QL or JDOQL for example.
  16. Not Java only[ Go to top ]

    Query languages in good ORM systems can do almost everything that SQL can do
    What is the purpose with a new query language if it do almost the same thing as the old one (SQL)? The best thing with SQL is that it is standardized and it could be used in many databases and many programming languages. EJB-QL or JDO-QL can never achive this because it is tied to a specific programming language, and a specific task (O/R mapping).

    Fredrik,
    http://butler.sourceforge.net
  17. Not Java only[ Go to top ]

    Query languages in good ORM systems can do almost everything that SQL can do
    What is the purpose with a new query language if it do almost the same thing as the old one (SQL)? The best thing with SQL is that it is standardized and it could be used in many databases and many programming languages. EJB-QL or JDO-QL can never achive this because it is tied to a specific programming language, and a specific task (O/R mapping).Fredrik,http://butler.sourceforge.net
    Yes, home made, procedure oriented query languages is the main flaw in current ORM specifications and implementations.
  18. Not Java only[ Go to top ]

    Yes, home made, procedure oriented query languages is the main flaw in current ORM specifications and implementations.
    Which query languages do you mean? JDOQL, for example, is not 'home made' - its a Java standard produced by teams of experts, and supported by multiple vendors.

    What do you mean by 'procedure oriented'? Query languages such as JDOQL have nothing to do with procedures - they express the same things as SQL, but are portable. I can run the same JDOQL on any JDO vendors product, and on any of the databases supported by these products. Only a very minimal subset of SQL is portable in this way.

    Why is a standard, multi-vendor, portable query language a 'flaw'?
  19. Not Java only[ Go to top ]

    Yes, home made, procedure oriented query languages is the main flaw in current ORM specifications and implementations.
    Which query languages do you mean? JDOQL, for example, is not 'home made' - its a Java standard produced by teams of experts, and supported by multiple vendors. What do you mean by 'procedure oriented'? Query languages such as JDOQL have nothing to do with procedures - they express the same things as SQL, but are portable. I can run the same JDOQL on any JDO vendors product, and on any of the databases supported by these products. Only a very minimal subset of SQL is portable in this way.Why is a standard, multi-vendor, portable query language a 'flaw'?
    JDOQL the same kind of standard as VBScript and It must be better to use JavaScript for this reason, it is standard and more powerfull than JDOQL, but I do not think it is a good idea to replace SQL with JDO script. SQL is a defacto standard and it is more object oriented, I see no meaning to reinvent it at home .
  20. Not Java only[ Go to top ]

    I can run the same JDOQL on any JDO vendors product, and on any of the databases supported by these products. Only a very minimal subset of SQL is portable in this way.Why is a standard, multi-vendor, portable query language a 'flaw'?
    Because JDOQL is only used through JDO. Normally the a relational database is access not only from JDO. Often there are application modules written in other languages than java. Tools for reporting, datawarehouse, import/export, etc, is also accessing the database. It is much better to have a global standard for query languages, instead of a standard limited only to JDO access.
  21. Not Java only[ Go to top ]

    >What is the purpose with a new query language if it do almost the same thing as the old one (SQL)? The best thing with SQL is that it is standardized and it could be used in many databases and many programming languages. EJB-QL or JDO-QL can never achive this because it is tied to a specific programming language, and a specific task (O/R mapping).
    There are standards for SQL, but very few DB vendors use them. Compare the SQL of MS Access, MySQL, PostgreSQL and Oracle for example - there are vast differences. Look at the effort required to port PL/SQL (Oracle) to the PostgreSQL or SQLServer equivalents. EJB-QL and JDOQL may be tied to one language, but they are standards that are followed by many vendors. JDOQL is be tied to O/R mapping, but O/R mapping is not a specific task - its a very general purpose way of using a database.
  22. Not Java only[ Go to top ]

    There are standards for SQL, but very few DB vendors use them. Compare the SQL of MS Access, MySQL, PostgreSQL and Oracle for example - there are vast differences.
    Wrong. A majority of database vendors supports ANSI-SQL 92, entry level. Except of the dig dragons (Oracle and DB2), the support for ANSI-SQL is even better. I am working with an application that are portable between Informix, Oracle and SQL Server. In 99% of the cases we use exactly the same SQL statements.
    EJB-QL and JDOQL may be tied to one language, but they are standards that are followed by many vendors.
    I think that there are a lot more vendors that follow ANSI-SQL than follows JDOQL. If your claim is true (that all JDO vendors fully supports the standard) it is only because it is a rather small number of JDO vendors.

    Don't you agree that it is better to have one query language, instead of two (one for JDO and one for the rest)?

    Fredrik,
    http://butler.sourceforge.net
  23. Not Java only[ Go to top ]

    There are standards for SQL, but very few DB vendors use them. Compare the SQL of MS Access, MySQL, PostgreSQL and Oracle for example - there are vast differences.
    Wrong. A majority of database vendors supports ANSI-SQL 92, entry level. Except of the dig dragons (Oracle and DB2), the support for ANSI-SQL is even better. I am working with an application that are portable between Informix, Oracle and SQL Server. In 99% of the cases we use exactly the same SQL statements.
    I'm sorry, but I find this impossible to believe. There are products to convert Oracle PL/SQL to SQL Server precisely because (to quote from one product's website) "Its a tedious manual effort". Having used both Oracle PL/SQL and pgplSQL (PostgreSQL) I know that the syntax is very different, and these differences are described on the PostgreSQL website. To quote from a Stanford University document: "There is a great deal of difference between the Oracle and SQL standard approaches." MySQL production release does not have sub-selects. I could go on and on.
  24. Not Java only[ Go to top ]

    Try it yourself before to read one product's website or vist this website:
    http://www.jcc.com/SQLPages/jccs_sql.htm
  25. Not Java only[ Go to top ]

    Having used both Oracle PL/SQL and pgplSQL (PostgreSQL) I know that the syntax is very different, and these differences are described on the PostgreSQL website.
    Wait a minute. First we very talking about query languages (SQL vs JDO-QL), and now you are talking about a non-standard feature in Oracle. I agree 100% that if you are using stored procedures you will lose all portability, but that is not my point. What I am trying to say is that SQL as a query language is very standardized.

    Fredrik,
    http://butler.sourceforge.net
  26. Not Java only[ Go to top ]

    BTW PL/SQL is not a single language, databases support COBOL,C++,JAVA,perl,ADA,FORTRAN, TCL ..., you can use PHP to write PostgreSQL prcedures. Doe's it mean PostgreSQL is not portable because it supports PHP stored procedures ?
  27. Not Java only[ Go to top ]

    PL/SQL is not a single language,
    PL/SQL is a single language used in Oracle database. It is based on Ada. Oracle also has support for java stored procedures.

    There are also an ANSI standard for stored procedures (similar to PL/SQL, but not exactly the same). But this standard is supported only by a few database vendords. So I consider all stored procedures as un-portable.
    Doe's it mean PostgreSQL is not portable because it supports PHP stored procedures ?
    I don't know how many database vendors that support PHP stored procedures. But it don't think it is many. So yes, a stored procedure written in PHP is not very portable.

    But back to query languages. An application using PostgreSQL and ANSI-SQL as query language is most likely portable to another database vendor.

    Fredrik,
    http://butler.sourceforge.net
  28. In 21st century, when we are all enjoying a great ORM system like Hibernate (and maybe other to reach it's level, soon) - this article and, with all due respect, the thinking of the author miserably stinks of the past century.

    I do not think there is anything to comment on - when the rest of the world is thinking about integration and RDBMS-independance, information-sharing and universal approaches, Aspects and OO Design patterns, stating that those who do not trap theirselves into the ugly PL/SQL nightmare will lose - causes just a bitter smile.
  29. In 21st century, when we are all enjoying a great ORM system like Hibernate (and maybe other to reach it's level, soon)
    Hibernate will NOT be the tool for the 21st century. It makes database programming much harder than it should be. History should have learned you that the fashion in the software industry changes very rapidly. And old thing are coming back sometimes too. OO databases is nothing else but an old network database with a new name.

    The software industry still have a big problem that database programming is too complex. First EJB comes and fails, second JDO comes and have recently failed. Hibernate is a step in the right direction, but other more useful tools will come. Compared to the old-fashion way with embedded SQL, Hibernate is too complex. Senior COBOL and C programmers are laughing at the problems that "OO" projects encounter, because of the complex and inadequate persistence frameworks of today.

    PL/SQL is actually an example of a programming language that is effecient for its task and easy to learn. You are tied up to a specific database vendor, but it will run on almost every operating system.

    Fredrik,
    http://butler.sourceforge.net
  30. second JDO comes and have recently failed.
    This will come as a surprise to the JDO Vendors, who are experiencing an growing market and expanding their companies.
  31. second JDO comes and have recently failed.
    This will come as a surprise to the JDO Vendors, who are experiencing an growing market and expanding their companies.
    Maybe you have missed it, but Sun has recently announced to drop JDO and incorporate it into EJB. I think that can be considered as a failure for JDO.

    Fredrik,
    http://butler.sourceforge.net
  32. Maybe you have missed it, but Sun has recently announced to drop JDO and incorporate it into EJB. I think that can be considered as a failure for JDO.
    Au contraire.

    Sun realized that two different specs were incorporating ORM, and wanted to split that out to make a re-usable piece for EJB and JDO to sit on top of.

    Spec #1: ORM - defines the standard mechanism and the APIs/SPIs for mapping Java objects efficiently and effectively AND PORTABLY to and from RDBMSs.

    Spec #2: JDO - already has an API, already has users, but ORM was never standardized until 2.0, so that standardization effort is becoming part of spec #1 above.

    Spec #3: EJB (specifically, the portions dealing with container managed persistence for entity beans) - like JDO, already has an API, already has users, but ORM was done poorly, and was getting redone for version 3.0, so that effort is going to spec #1 above.

    This is a huge win for JDO, and a huge win for customers who have long complained of confusion with respect to ORM standards in Java (since there were none.)

    Peace,

    Cameron Purdy
    Tangosol, Inc.
    Coherence: Shared Memories for J2EE Clusters
  33. Set theory anyone?[ Go to top ]

    .. the support for ANSI-SQL is even better. I am working with an application that are portable between Informix, Oracle and SQL Server. In 99% of the cases we use exactly the same SQL statements.
    Agreed that SQL is a widespread standard. As a Java developer, I wish some of the newer developers were open minded about the experiences other people had. For example working on sets, intersections and unions - this is what relational maping gives you, especialy for a big, complex and scaleable aritecture.
    Take the advice, read "SQL for Smarties".
    And over time, when you deploy apps in langages other than Java, I hope you rember that some of the old "COBOL" people are not wrong, just becuase they are experienced. There is no magic in technology, utilmately, somone has to know how diferent types of joins work.

    The slowest part of Java is data access, and not becuase of Java, but becuase some of the Java people have inadequate SQL training (such as how a SQL join gets parsed, compiled, optimized and executed. Rember - computers only know machine code, they do not know SQL).

    .V
  34. Set theory anyone?[ Go to top ]

    Agreed that SQL is a widespread standard.
    I'd be interested in how the major differences between SQL language, datatypes, features and syntax in Oracle, PostgreSQL, MySQL and SQL Server fit in with the definition of SQL as a 'widespread standard'.
  35. Set theory anyone?[ Go to top ]

    I'd be interested in how the major differences between SQL language, datatypes, features and syntax in Oracle, PostgreSQL, MySQL and SQL Server fit in with the definition of SQL as a 'widespread standard'.
    They all support ANSI-SQL 92, entry level (or more). Just because they have vendor specific additions, it does not mean that they don't support the ANSI standard.

    Fredrik,
    http://butler.sourceforge.net
  36. Set theory anyone?[ Go to top ]

    Agreed that SQL is a widespread standard.
    I'd be interested in how the major differences between SQL language, datatypes, features and syntax in Oracle, PostgreSQL, MySQL and SQL Server fit in with the definition of SQL as a 'widespread standard'.
    Download hibernate or OJB source code and see "Dialects", differences are trivial, ORM can support many databases, because databases are portable, but it is not prohabited to use exotic stuff too.
  37. Set theory anyone?[ Go to top ]

    Agreed that SQL is a widespread standard. As a Java developer, I wish some of the newer developers were open minded about the experiences other people had. For example working on sets, intersections and unions - this is what relational maping gives you, especialy for a big, complex and scaleable aritecture.
    Take the advice, read "SQL for Smarties".
    And over time, when you deploy apps in langages other than Java, I hope you rember that some of the old "COBOL" people are not wrong, just becuase they are experienced. There is no magic in technology, utilmately, somone has to know how diferent types of joins work.

    The slowest part of Java is data access, and not becuase of Java, but becuase some of the Java people have inadequate SQL training (such as how a SQL join gets parsed, compiled, optimized and executed. Rember - computers only know machine code, they do not know SQL).
    Vic, you (and many other commenters here) are right, but no one idea is an absolute truth that applies equally well to every application. It just so happens that the applications that you work on seem to be well-suited for direct SQL access.

    In addition to the application model that you describe, which I have seen work well for certain application, I have also seen applications that make extensive use of ORM, and without it, in my opinion, they would not have been possible to build.

    As incredulous as you are that people don't just drop their tools and go back to writing SQL statements for each different data operation in their application, I am incredulous that you (and others) would assume that there is a single answer to the challenges of building database-based applications.

    Peace,

    Cameron Purdy
    Tangosol, Inc.
    Coherence: Shared Memories for J2EE Clusters
  38. Set theory anyone?[ Go to top ]

    dude's
    The effort to integrate the niche of Datasource is always head ache to developers and arch....Xquery is one pointers which can solve ur flat file to webservice problem.Liquid Data from Bea is a good effort towards !!!!

    Thanks,
    manu
  39. Set theory anyone ?[ Go to top ]

    Yep, I think people on this forum need to stop doing : "The only projects I have worked on (which represents the universe of all known applications in the world) have been done the A way, and therefore, all apps need to be done the A way". And then these same people complained about Microsoft doing their my way or the highway act.
  40. It's the ORM, stupid.[ Go to top ]

    I feel that the DB vendors are decades behind the times, desperately trying to keep developers coding in proprietary languages embedded within databases.
     I am not sure JAVA is more portable or less proprietary than PL/SQL stuff, but SQLj was invented to run JAVA code on any RDBMS (it looks like oracle invented it). So the reason to code business rules and access control on deticated client is not a partability and it is not a technology fault.
  41. It's the ORM, stupid.[ Go to top ]

    I feel that the DB vendors are decades behind the times, desperately trying to keep developers coding in proprietary languages embedded within databases.
    I am not sure JAVA is more portable or less proprietary than PL/SQL stuff, but SQLj was invented to run JAVA code on any RDBMS (it looks like oracle invented it). So the reason to code business rules and access control on deticated client is not a partability and it is not a technology fault.
    Java is demonstratably more portable than PL/SQL. I routinely move Java + JDO code between databases with no code changes - I simply have a set of configuration files for each database type. This saves a lot of time and money - I can develop using PostgreSQL or MySQL and then perform final testing and deployment on Oracle.

    SQLj does not run Java on any platform: You embed proprietary SQL in Java in proprietary ways. Its yet another way that you get tied in to a DB and the associated tools.
  42. It's the ORM, stupid.[ Go to top ]

    Right Steve,

    God forbid you now think about using indexes, or BLASPHEMY ! foreign keys in the database, not to mention views ! Should you do that, I think your toy app may stop working on MySQL. And we would simply die if that happened, simply wither and die.

    Steve, real applications don't change database because they can. Real applications invest money in their database systems, and deployment efforts, and tuning, and testing. They don't just go: hey how about we switch to mysql next week, our db2 is just so... IBMish!

    Really, some people need to grow up and get over the stupid ideas of code purity being tainted by evil databases.
    Invest your efforts in isolating the data layer, abstracting as your domain requires, trying to come up with good OO where it's needed, not just cry foul because SQL is not OOP.

    Take care now
  43. It's the ORM, stupid.[ Go to top ]

    Right Steve,God forbid you now think about using indexes, or BLASPHEMY ! foreign keys in the database, not to mention views ! Should you do that, I think your toy app may stop working on MySQL. And we would simply die if that happened, simply wither and die.Steve, real applications don't change database because they can. Real applications invest money in their database systems, and deployment efforts, and tuning, and testing. They don't just go: hey how about we switch to mysql next week, our db2 is just so... IBMish!Really, some people need to grow up and get over the stupid ideas of code purity being tainted by evil databases.Invest your efforts in isolating the data layer, abstracting as your domain requires, trying to come up with good OO where it's needed, not just cry foul because SQL is not OOP.Take care now
    Of course I use indexes and foreign keys - I would be foolish if I didn't. I define the indexes in the metadata of my JDO system. (JDO 2.0 will allow the use of views). They are applied to the database by the JDO system, in a manner appropriate to the particular database engine I am using. They can't be applied in MySQL, so the JDO system doesn't try, but then I don't use MySQL as a deployment system, and only occasionally as a development system. When I use a powerful database engine such as PostgreSQL or Oracle, the JDO system I use makes full use of indexing and foreign keys to give high speed and guarantee integrity. I don't know Hibernate well, but I'm sure it is equally adept at using the underlying DB engine.
  44. Real applications invest money in their database systems, and deployment efforts, and tuning, and testing. They don't just go: hey how about we switch to mysql next week, our db2 is just so... IBMish!
    Maybe in the business you are working in you can just order the customer
    to use Oracle or whatever you tuned your application (or SQL) for.

    Other lifeformes may need to adapt their application to the customer's already
    existing database infrastructure, trying to get the best out of this situation.
    Here ORM á la JDO helps a lot. Tuning is best kept to experts, but
    we can't afford to turn all our Java developers into Oracle/DB2/mySQL/younamedit
    experts. So we let our JDO vendor try to optimize access and use
    features like foreign keys, views etc.
  45. Just to play devil advocate[ Go to top ]

    Coding in stored procedures might mean that you are locked in to the DB vendor, but on the other hand you are not locked-in to a specific language outside.
    So you can use Java, .Net, Powerbuilder or anything else that can do a DB connection using the same set of business rules you only coded once.

    And for the person who complained about lack of development tools for PL/SQL:
    PL/SQL debugger - JDeveloper, TOAD and others...
    PL/SQL unit testing - UTPLSQL - http://oracle.oreilly.com/utplsql/
  46. Re: Just to play devil advocate[ Go to top ]

    Coding in stored procedures might mean that you are locked in to the DB vendor, but on the other hand you are not locked-in to a specific language outside.
    So you can use Java, .Net, Powerbuilder or anything else that can do a DB connection using the same set of business rules you only coded once.

    And for the person who complained about lack of development tools for PL/SQL:
    PL/SQL debugger - JDeveloper, TOAD and others...
    PL/SQL unit testing - UTPLSQL - http://oracle.oreilly.com/utplsql/
    But during the development of a pl/sql project with java/.net/whatEverPlatform you need to hire - pl/sql developers - dbas - java/.net/whatEverPlatform developers but during a db free project only - dbas - java/.net/whatEverPlatform developers DB developers magicly disappears!
  47. It's the ORM, stupid.[ Go to top ]

    .
    Business Logic must be portable. Well, that's my experience anyway.
    Nodding in agreement...
    ... Data Logic and Business Logic Code in PL/SQL will enable you to considerably reduce the risk ..."DB SQL is key to project success. Shops and Java geeks that do not learn this will not be effective (even in PHP and ASP this is true)...
    Business logic should not be dependent on your choice of data persistance and storage or data manipulation language. Database is there for data, not for business. Otherwise next time you will have a project that involves database, install TOAD on every users machine and give them SQL manual, hey, better than that, write macros and have them run those. They should be EXTREMELY happy!

    What if your application ships both enterprise and standalone? Are you going to ship large enterprise-scale RDBMS with every app? What if your client cannot afford expensive RDBMS implementation? What are you going to do then? Tell the client to go to hell, as your pride and attachment to the DB vendor is bigger than a common business sense? C'mon, flexibility and data-layer abstraction are the things that should not be discussed - they should be followed.

    The fact that you can do evertyhing you need in PL/SQL, including definiton and invokation of Java stored procedures, does not mean you should. You can theoretically write applications in assembly, but would you?

    Developers need to see the BIG PICTURE outside of the nice comfy cave of "one language, one DB vendor environment". Invest in your architecture, design and approach, not in specific implementation.
  48. It's the ORM, stupid.[ Go to top ]

    What if your application ships both enterprise and standalone? Are you going to ship large enterprise-scale RDBMS with every app? What if your client cannot afford expensive RDBMS implementation? What are you going to do then? Tell the client to go to hell, as your pride and attachment to the DB vendor is bigger than a common business sense? C'mon, flexibility and data-layer abstraction are the things that should not be discussed - they should be followed.
    +1

    This is exactly my position.
  49. WHAT IS THIS![ Go to top ]

    "... Data Logic and Business Logic Code in PL/SQL will enable you to considerably reduce the risk ..."DB SQL is key to project success. Shops and Java geeks that do not learn this will not be effective (even in PHP and ASP this is true)..V
    I can't believe this topic is coming up. It is one thing for Oracle to spew this crap but to get it posted here? Maybe we should resurrect the Object-oriented analysis vs Data Flow Analysis arguments again. Remember that one? The one we had in the late '80s early '90s...

    There are still people who believe in Data Analysis and doing data flow diagrams to build applications but there are people who still believe that the earth is flat ( http://www.flat-earth.org/ ) too.

    I can't believe that TSS posts this and in effect says this is a idea worthy of considerartion. Maybe if they posted it AND explained why it is a BAD idea to have thick databases.
  50. WHAT IS THIS![ Go to top ]

    I can't believe that TSS posts this and in effect says this is a idea worthy of considerartion. Maybe if they posted it AND explained why it is a BAD idea to have thick databases.
    You can read it and decide it is BAD or GOOD yourself, I hope you can decide it without any TSS studies.
    I agree, it is too conservative for most applications, but idea to "hide" access control, validation, complex queries in Views and triggers is good. It can eliminate RPC layers, helps for integration, it can be more secure and performant ... . You pay for it, but I think your win many things too.

    BTW the earth is not flat, but it can be modeled this way too.
  51. WHAT IS THIS![ Go to top ]

    I can't believe that TSS posts this and in effect says this is a idea worthy of considerartion. Maybe if they posted it AND explained why it is a BAD idea to have thick databases.
    You can read it and decide it is BAD or GOOD yourself, I hope you can decide it without any TSS studies.I agree, it is too conservative for most applications, but idea to "hide" access control, validation, complex queries in Views and triggers is good. It can eliminate RPC layers, helps for integration, it can be more secure and performant ... . You pay for it, but I think your win many things too.BTW the earth is not flat, but it can be modeled this way too.
    It is not "too conservative", it is a bad idea.

    Since there are few if any schools that teach good design skills and most developers are left to the developer community on the web to figure out what is good and what is not good all, of us have a great deal of personal responsibility. It is one thing for Oracle to feed us BS and call it a good design; it is another for a reputable site like TSS to participate in the spread of that BS.

    This is a forum that people turn to for discussion AND guidance. We should not be throwing them to the database wolves.
  52. WHAT IS THIS![ Go to top ]

    There is no "good" or "bad" design for all cases, just because SUN is not RDBMS vendor doe's not make RDBMS the "wrong" technology and JAVA "right" programming language. There is a motivation for data driven architecture in this article, do you know some motivation for your favorite architecture ? Is it easy-of-use and OOP ?
    It can not solve all problems and there is nothing new, but I have found a lot of good information in this article.
  53. WHAT IS THIS![ Go to top ]

    Maybe we should resurrect the Object-oriented analysis vs Data Flow Analysis arguments again.
    OO analys can never replace can never replace data analysis. Data analysis is made for designing the database schema, and OO analysis is made for designing the applications. Both are importatnt.

    I am not proposing the usage of stored procedures due to the portablility problems, but they have some advantages. They provide much better performance, because the db calls are in-process. Today a lot of software is made os-specific, so making db-vendor-specific applications is maybe not the worst sin to do.
    I can't believe that TSS posts this and in effect says this is a idea worthy of considerartion.
    I would have been more thankful if TSS could have explained why EJB is pure crap and prevented companies from investing a fortune in unnecessary J2EE servers.
  54. It's the DB, stupid.[ Go to top ]

    It's true.

    The most important skill for 90% of server-side Java development? ... *SQL*

    Nothing is more expressive and powerful. That is why DAO layers like iBatis are so powerful. They express data operations in terms of SQL. Every other abstraction is a reduction in power.

    Ben
  55. It's the DB, stupid.[ Go to top ]

    It's true.The most important skill for 90% of server-side Java development? ... *SQL*Nothing is more expressive and powerful. That is why DAO layers like iBatis are so powerful. They express data operations in terms of SQL. Every other abstraction is a reduction in power.Ben
    SQL is the most important skill for server-side Java for historical reasons: widespread use of ORM systems is very new, whereas JDBC and SQLJ have been around for a very long time.

    There are plenty of systems that are at least as expressive and powerful than SQL. The query language of JDO 2.0 can express pretty much the same operations, for example. Personally, I find declarative languages such as Prolog to be far more powerful, if by power you mean the ability to extract meaningful information from data with the minimum program code.
  56. It's the DB, stupid.[ Go to top ]

    SQL is the most important skill for server-side Java for historical reasons: widespread use of ORM systems is very new, whereas JDBC and SQLJ have been around for a very long time. There are plenty of systems that are at least as expressive and powerful than SQL. The query language of JDO 2.0 can express pretty much the same operations, for example. Personally, I find declarative languages such as Prolog to be far more powerful, if by power you mean the ability to extract meaningful information from data with the minimum program code.
    Agreed, declarative languajes are prefect for the job. In a previous post I mentioned something like "domain models or business rules".

    As it has been said in TSS before, business rules have a great momentum because they allow you to express your requirements in a very clear way and interact with the rest of the system seamlessly. Now the question... if we cast so much controversy about business login in objects or in the DB, how do you propose to persist the state of an application built around business rules? any ideas...
  57. It's the DB, stupid.[ Go to top ]

    SQL is the most important skill for server-side Java for historical reasons:...
    SQL has a rich history, but it's importance to Java development is not primarily historical. We don't use SQL because it is legacy, we use it because it's power and flexiblity is unmatched.

    When data moves away from tabular structure, like XML stored in a filesystem, or in some BLOB, its value to the business is reduced because it becomes less accessible.

    See post above about multi-platform stuff. Before there was SOAP or RMI or whatever the SOA stuff of the day is, there was another approach, a shared database. This is still one of the most effective ways to reduce application silos.
  58. It's the DB, stupid.[ Go to top ]

    SQL is the most important skill for server-side Java for historical reasons:...
    SQL has a rich history, but it's importance to Java development is not primarily historical. We don't use SQL because it is legacy, we use it because it's power and flexiblity is unmatched.When data moves away from tabular structure, like XML stored in a filesystem, or in some BLOB, its value to the business is reduced because it becomes less accessible.See post above about multi-platform stuff. Before there was SOAP or RMI or whatever the SOA stuff of the day is, there was another approach, a shared database. This is still one of the most effective ways to reduce application silos.
    SQL's power is not unmatched - its simply one of many ways to request data from a relational store. There are other ways that are just as expressive: Hibernate Query Language, for example.

    As for moving away from a tabular structure - the relational model is just one way to represent data, but its hardly ideal for many cases. Its hard to represent irregular data, or inheritance, or tree structures.

    But anyway, my point is that there is no single 'SQL' - there are a large number of often incompatible dialects.
  59. It's all about education.[ Go to top ]

    Its hard to represent irregular data, or inheritance, or tree structures.
    Dealing with trees is not that difficult in RDBMS, simply represent them as nested sets:
    http://www.dbmsmag.com/9604d06.html

    Also I recommend book "SQL for Smarties" by Joe Celko
  60. It's all about education.[ Go to top ]

    Its hard to represent irregular data, or inheritance, or tree structures.
    Dealing with trees is not that difficult in RDBMS, simply represent them as nested sets:http://www.dbmsmag.com/9604d06.htmlAlso I recommend book "SQL for Smarties" by Joe Celko
    You can do it of course, but its far from an easy or obvious use of a relational store.

    So what about irregular data, or inheritance?
  61. It's all about education.[ Go to top ]

    Its hard to represent irregular data, or inheritance, or tree structures.
    Dealing with trees is not that difficult in RDBMS, simply represent them as nested sets:http://www.dbmsmag.com/9604d06.htmlAlso I recommend book "SQL for Smarties" by Joe Celko
    You can do it of course, but its far from an easy or obvious use of a relational store.
    Yes, it takes time to understand the trick, but then it kind of obvious.
    So what about irregular data, or inheritance?
    Well, objects of any complexity (and their graphs) can be stored in 3 or 4 tables, not very performant (:, but possible.

    Optional attributes can be stored as dependent objects in 2nd table ( 2 tables per object), inheritance can be modelled via additional tables with 1-to-1 relationship to the master...)

    OK, OK, RDBMS are not good at storing irregular data, but I would say that computers in general are not that good when dealing with non structured data.
  62. data:application 1:1, 1:n, n:1[ Go to top ]

    Bigger business == more stabe database and more versatile
    users applications : intranet rich client, web, batch,
    monitor/audit, reports... Some Java, some proC, some PHP,
    some excell. ( I am sure most of us know that there is life
    outside Java (?) )

    Tell me, please, how many times you see enterprise
    change Oracle for DB2 (staying in the same Java code, of course!)?
    Now, please, the same question, how many times you see
    the same database with the same (or slowly evolving) data and
    re-writing C to C++ to SmallTalk to Java to NET to Java to another Java to
    Applets, to Servletts to EJB to Hibernate to JDO,
    SOA, OPHP, WS-1, Tiles, Struts to JDBC to DTO, ... wait next are
    SDO, ADF, AOP, EJB3, Hibernate3, ORM99...

    Thus,
    -- datastorage has to be client/user/application agnostic.
    -- Direct data access must be application agnostic
    -- No application/domain object graphs must be permanently
       stored as unit.
    -- ORM is unavoidable for any application with domain
       (I did some ORM between tables and PLSQL domain almost-objects !)
    -- ORM can be implemented with jdbc, hibernate, BMP, Toplink, there is no
       difference in main task, translate normalized data
       to/from domain object (plus transactional control, of course)
    -- Maximum reasonable (at least, low and mid-level) integrity must
       be enforced in datastorage level.

    Also, who told you that SP is database? PL/SQL, TSQL,
    JSP (java stored procedures) are PROGRAMMING LANGUAGEs,
    just physically sitting inside database. No question, Java
    will win over PLSQL in every department, except one -
    I would say main one in many cases - data handling.

    As usual, truth is somewhere in the middle...

    Alex V.
  63. You need to balance your requirement and environment to distribute the load to Middleware or database to have a optimal performance.
  64. Let's back to COBOL[ Go to top ]

    This article is funny, indeed. I saw and participated in few project which implemnted business logic in PL/SQL. Only thing I can say - this approach leads to
    * maintenability problems (migration between databases, integration with other systems, hard debugging, lack of decent development tools etc.),
    * testability problems (ever tried to implement unit test in PL/SQL?)
    * supportablility (where can I find PL/SQL package doing for example - assymetric cryptography?)

    And come one - it's 21st centoury we have object oriented languages, apsects, IoC containers, ORM tools, decent IDEs, frameworks... Buisness loginc is not only about managing data - it's about... business - it's much more complicated in most cases.

    My opinion is - let RDMS manage data, let application servers manage applications.

    Artur
  65. Let's back to COBOL[ Go to top ]

    My opinion is - let RDMS manage data, let application servers manage applications.Artur
    Why do you think RDBMS can not be application server too ? It solves popular problems better, you can "hot deploy" stuff without restart, there are a lot of good monitoring tools, distributed databases are "transparent" without any DTO,DAO,XML and Facade stuff. How it can be harder to maintain than EJB stuff ?

    BTW I do not need COBOL in practice myself, but I faund it is well designed for file processing and I do not think something better exists for this use case. It is more designed to read than to write, but it is not wrong too.
  66. Let's back to COBOL[ Go to top ]

    Why do you think RDBMS can not be application server too ? It solves popular problems better, you can "hot deploy" stuff without restart, there are a lot of good monitoring tools, distributed databases are "transparent" without any DTO,DAO,XML and Facade stuff. How it can be harder to maintain than EJB stuff ?
    Because Oracle solves some common problems well, doesn't automatically mean that it's is good choice. As I said - I participated in few project written in PL/SQL. These were hardest to maintain projects I've ever seen. Integration with other legacy systems was complete pain (try to call legacy system via CORBA), there were almost no things we used to in real application environments (frameworks, IDEs, tools, libraries etc.). You mentioned about monitoring - yep Oracle has superb monitoring - but only if we are talking about monitoring of database. When it comes to application - even as simple things as logging was a pain (we ended with log4plsql - which is simple frontend to log4j), not to mention about easy debugging or things like JMX etc. And the worst thing - PL/SQL dialect. As I said it's 2004 now. We don't have to write procedural code any longer - since we have better choices. PL/SQL is ugly, there almost no common design patterns, modeling tools - I'm aware of (we are talking about applications, not about data modeling) and it's completly unreadable unless You are the author of the code.

    To be honest - having parts of Your application written in PL/SQL usually gives You far better performacne than implementing this in pure Java in some cases - but this is at the cost I mentioned above. If this cost is acceptable for You ok. But in the most applications I've seen it was too high.

    Artur
  67. Let's back to COBOL[ Go to top ]

    I agree with you about data vs application modeling.
    It is wrong to design database for PL/SQL applications too. Serious database must be open for any application and programming language and UDF stuff is good to extend RDBMS functionality for special needs, not to develop applications(It is possible, but I do not think this is a good way too).
    I have developed many toys (single database talks with single application) all ways good for this kind of toys (I found PHP is very good one). I more integrate with serious systems than develop, but I found well designed database is the most important component in system.
  68. Let's back to COBOL[ Go to top ]

    I do not need COBOL in practice myself, but I faund it is well designed for file processing and I do not think something better exists for this use case. It is more designed to read than to write, but it is not wrong too.
    I have worked with both COBOL and Java, and I don't agree that COBOL is better for file processing. COBOL has some built-in support for reading and writing flat files, but a component for doing this in java is very easy to build. Unfortunately java geeks only talks about XML and don't care about flat files. But I they would, there would be many open source libraries for handling flat files availible.
  69. For PL/SQL shops only[ Go to top ]

    "This article assumes you are an Oracle PL/SQL shop. If you are, then coding all Data Logic and Business Logic Code in PL/SQL will enable you to considerably reduce the risk in your (first) J2EE project. We are not saying that it will be easy though. On the contrary, there is still a whole new world for you to discover and adopt. Approaching this in a databasecentric way however, will enable you to not make it more complex than it needs to be."

    If you have a large pl/sql code base already and are trying to build your first j2ee app and don't want to screw up this is probably a good way to go, but if not I wouldn't recommend it. I don't know how many of you have upgraded database versions but it's not a fun process. I wouldn't want my business logic tied to a specific version of oracle.
  70. I agree that to some extent you put logic on DB, but this is also the key for database vendor lock-in.
  71. Can be maintenance hell[ Go to top ]

    PL/SQL logic s good for few procedure and tables but when it reaches stage of 300-500K line of code in PL/SQL then it is nightmare. It is very difficult to find dependency between upper and database layer. You change something in PL/SQL BL and do not know what else will fail in upper layer. Data intensive operations can exist in DB vendor language not beyond that otherwise it is overkill for it.
  72. Don't Trust Oracle[ Go to top ]

    I have a couple of issues with this article. First, it's intended user base appears to be a PL/SQL developer who is contemplating writing a web application FOR THE FIRST TIME as part of the difficulty of learning to write J2EE is learning HTML, stateless sessions, and Javascript.

    The explanation of fat vs. thin client also seems to be targeted at newbies into the realm of application rather than database development.

    Also, the statement that writing a line of PL/SQL code instead of a writing a line of Java code reduces risk is um... silly. Risk? What risks is he discussing here? The risk of having code portable across database platforms. That risk goes way up with PL/SQL. The risk of hardcoding business logic in the database rather than in a pluggable layer. That risk skyrockets.

    What risks are we talking about? The risk to Oracle's marketing share? That stays low.

    Now, if this truly is a beginner's primer, I understand where he's coming from. He's saying that it's okay to continue with you know (PL/SQL) rather than make the jump to Java. There IS a lot to learn with Java. However, to make the statement "Don't trust Java geeks" is offensive. As someone who considers himself a Java guy, I want to know where this guy gets off. He sounds like a paranoid DBA whose to scared to let anyone touch his precious database. So, here's the challenge. I am willing to wager that a Java-centric application can beat a database-centric application on a number of key performance indicators:

    1. Maintainability
    2. Scalability
    3. Time to market
    4. Performance

    I think that TCO could go to the database-centric architecture because putting all the work into the database could reduce the number of servers needed.
  73. Don't Trust Oracle[ Go to top ]

    Now, if this truly is a beginner's primer, I understand where he's coming from.
    This article does seem out of place on TSS, maybe Oracle shouldn't let the Database Marketing guys write Java articles :). That being said, I think there are a couple of points that may be interesting to a team of Database developers looking at Java for the first time. Think of it as a way of slowly bringing them over to the J2EE side...

    There are projects out there where the application requirements and development constraints (budget, team skills, and schedule) do not permit and complete switch of ideology. There are also development organizations who are very happy with their PL/SQL approach to solving their problems or need to develop new applications in J2EE that must share legacy databases. For these customers there must always be an option to build their application to requirements on time and on budget. If you have a development team and an application where doing so using PL/SQL works then by all means you should have that option to "dip in your toe".

    There is a strong divide on recent threads between purely relational approaches and OO approaches to the persistence layer challenge. For those people that this article is obviously targeted at, using "no persistence layer" should be an option, and you should have ideas to help with the development. OTOH, persistence layers like TopLink should strive towards meeting the demands of the Java developer AND the DBA. You should be able to develop completely on the OO side of things and let the persistence layer handle the database. Additionally you should have the option to get the most out of your database or use skills you are comfortable with (SQL, Stored procs, query hints, and advanced database features such as Virtual Private Database, and XML-DB). In the end, the goal is to allow a DBA to do their job effectively and the Java programmer can build their application effectively without having to limit the design on either side.

     - Don
  74. IHMO:
    Database layer has longes lifecycle. You would want to put only hard rules in your database layer (ref integrity uniwuiness and such)
    Then your persistent object model - next layer often has shorter (or the same) lifecycle and should not have much of business rules but should enforce hard rules (just like database) such as bi-directional association maintenance, cardinality, metadata driven model rules etc
    Next layer is a process layer - helper classes and fascades which change quite frequently with business rule change (and you might very well have multiple of those for the same object model) and finally the most volatile is GUI

    Now if you are going to put most of it in your PLSQL I find it sure way to developing a monolithyc hard to reuse and debug system.
    Unless I have an overhelming reason (such as extremly data intensive high volume processes) to go this route I would rather go with a layered system
  75. Where the business logic is placed has nothing to do with the API that I choose to use to access my database. That is, just because I write SQL by hand, doesn't mean that I'm putting logic in my database. Nor does it mean that my application is any less OO. Application logic - and where it goes - is entirely a layering and design issue. ORM and SQL are simply implementation options.

    When I released JPetStore 1.0, everyone agreed that the logic had been effectively separated from the database (where Microsoft had put it in .Net Pet Shop). Yet, in JPetStore, the SQL is hand coded. Furthermore, everyone agreed that JPetStore had an effective OO design, complete with a business object model, mvc web tier and a DAO persistence layer (none of which MS PetShop had). It also ran on at least 11 databases that I know of. It did all of this without an ORM.

    Point: ORM is not a requirement it's an option. Similarly, SQL is not a requirement, it's an option. Some people like the flexibility and performance of writing SQL. Others like the flexibility and performance of ORM. Some projects are better suited for ORM. Other projects are better suited to SQL.

    Two projects ago, I used iBATIS. On my last project, I used Hibernate. On my current project I'm using JDBC.

    Failing to be flexible and practical is what will kill a project.

    Cheers,

    Clinton Begin
    http://www.ibatis.com
  76. Could not agree more; this is so obvious that it's just hilarious to see the legions of ORM zealots "tug their unmentionables" when words such as SQL, database, stored procedure come up.

    Grow up, look around. Oracle is one of the fines pieces of software that was built, yet the ignorant horde wants to forget all about the existance of RDBMS. Go use prevayler, yes, that will teach you.

    Clinton - respect, as always. iBATIS is one of the most common sense, practical database toolkit I've seen, getting the job done right, fast and properly.
  77. Please do not take me wrong, I am not against databases at all. In fact I will never allow a crappy database model hidden behind OO object model in my shop. Data has much longer life span. It is the most important part of an enterprise.
    I have a strong database modeling and development backgroung (quite a few years of client server) and it does not scare me to use PL/SQL at all when I feel I need it. I just happen to believe that consise clean domain models are much easer to learn, use and maintain then PL/SQL layer on top of database tables. Yes you do loose some of the flexibility of SQL (but you still have your good database model if you absolutely need it) but you also gain a lot. Each additional layer on top of database obviously reduce your flexibility due to its specialization but it serves its purpose and adds its own value. Of course these goal could be met with PL/SQL but it is harder to do it with PL/SQL than with Java with all power of the language and JDK behind it.
    We do provide some limited level of PL/SQL API to our data models for consumers who can not or do not want to use our java API - heper packages for more complex aspects of data access. I am tempted to try to produce a more sophisticated PL/SQL API using oracle objects one day :-)
  78. Oracle is one of the fines pieces of software that was built, yet the ignorant horde wants to forget all about the existance of RDBMS.
    I agree - Oracle is superb: fast and reliable. But, I don't want to have to install a copy on my laptop. I use PostgreSQL on my laptop, where I write and test many of my applications. I sometimes use Oracle for deployment. Abstracting the database using a good ORM system gives this flexibility.

    No-one is suggesting that anyone forgets about RDBMS: the suggestion is that with modern ORM its both possible, and good practice, to use tools that allow the developer to forget about the proprietary details of specific DBs.

    Personally, I'm not an ORM fanatic - I would support any way of allowing me to use relational databases while keeping my applications portable.
  79. I agree - Oracle is superb: fast and reliable. But, I don't want to have to install a copy on my laptop. I use PostgreSQL on my laptop, where I write and test many of my applications. I sometimes use Oracle for deployment. Abstracting the database using a good ORM system gives this flexibility.No-one is suggesting that anyone forgets about RDBMS: the suggestion is that with modern ORM its both possible, and good practice, to use tools that allow the developer to forget about the proprietary details of specific DBs.Personally, I'm not an ORM fanatic - I would support any way of allowing me to use relational databases while keeping my applications portable.
    "I don't want to have to install a copy on my laptop" is it a reason to choose technology or architecture ? Is this "easy-of-use" stuff is more usefull for you or for your customer and users ?
  80. "I don't want to have to install a copy on my laptop" is it a reason to choose technology or architecture ? Is this "easy-of-use" stuff is more usefull for you or for your customer and users ?
    e: All of the above.
  81. "I don't want to have to install a copy on my laptop" is it a reason to choose technology or architecture ? Is this "easy-of-use" stuff is more usefull for you or for your customer and users ?Its extremely useful for both. If I develop an application, the customer has a choice where to deploy it. They can start with embedded database, such as Cloudbase for very low-volume, then move up to a scalable open-source system such as PostgreSQL as the volume grows, and finally deploy on clustered Oracle if the need arises. There is a real market advantage to this.
  82. "I don't want to have to install a copy on my laptop" is it a reason to choose technology or architecture ? Is this "easy-of-use" stuff is more usefull for you or for your customer and users ?Its extremely useful for both. If I develop an application, the customer has a choice where to deploy it. They can start with embedded database, such as Cloudbase for very low-volume, then move up to a scalable open-source system such as PostgreSQL as the volume grows, and finally deploy on clustered Oracle if the need arises. There is a real market advantage to this.I have designed my first j2ee application this way, I was too lame to make right decisions about databases, so it was designed to run on any database except MySQL, we have tested it on Cloudscape and DB2 and deployed on PostgresSQL and later it was deployed Oracle (after some hasks to make driver happy). It was possible to implement this toy in many ways and we spend 5% of project time for data access coding. This stuff is implemented with plain JDBC and OO domain model, but I do not believe it is possible to implement enterprise systems this way. It is not ORM or OOP fault, well designed systems can be implemented in many ways, but I see this is not a common practice to design performant and secure systems with RDBMS help. It is a junior mistake from my practice, it needs to be fixed later any way.
  83. Since we are anyways in the topic of database-centric development, I thought I would throw in two more relevant articles. I think what is interesting is to see the interesting divide: how a DBA and possibly a middle-tier(J2EE) developer thinks.

    This divide needs to be bridged by understanding the core issues from both angles and understand what each side is trying to say. I am not advocating one over the other. As Allan explains in his article "Ideally, a greater awareness of today's tuning challenges will also foster changes in the application-design process that make tuning and administration easier down the road. "By considering tuning and monitoring issues at the architectural design stage," Edwards points out, "you can maximize the amount of information available for tuning the application after it's implemented" — thus taking an important step toward what may be the true holy grail for DBAs: the self-tuning multi-tier application.

    I am sharing these pieces only so that we may understand issues. This way we can understand each others world better: The developer and the DBA.

    BTW: I am myself in the J2EE camp :-). Just trying to be open to new ways of looking at architectural issues.
    From the article Taming Multi-Tier Tuning by Allan Edwards
    ""The vast majority of the applications that I see tend to have multi-tiered architectures, with J2EE running within the application server," says Edwards. "In this situation, transaction control and session control are often abstracted up, away from the database and application tiers — sometimes with all sessions executing as the same Oracle user — which has big implications for DBAs. It makes tuning much more complex than in a pure client/server environment. Furthermore, the application logic is being developed by Java specialists who generally have a shrinking appreciation for the database layer and related implications." "

    "The change from a client/server to multi-tiered architectures has affected several significant aspects of database performance optimization and tuning, according to Edwards — the two most important being resource management and tracing of database workloads. "
    Savvy Supersizing Strategies
    Design Strategies for Large Databases by Daniel Liu

    The design strategies that Liu recommends for large, multi-tiered database systems fall into three basic categories: architectural, component-related, and database-specific.

    With strategies like horizontal architecture scaling, efficient database structuring, and use of efficient components such as 64-bit platforms and networked storage systems, you can design your large-database system for high performance and availability on existing hardware. And tuning tools such as Liu's scripts for monitoring alert-log files and concurrent transactions will help you keep performance optimal as your database continues to grow.

    To borrow Liu's restaurant metaphor, don't think of your popular database just as an overfilled restaurant straining its capacity. Think of it as an opportunity to start a chain — the possibilities are limitless.
    Both these articles again have a DBA orientation.
    -
    Sudhakar
    Oracle Corp
  84. Right tool for the job[ Go to top ]

    It all comes down to the right tool for the job.

    If you have a database that is only going to be accessed by a single application and this database may change, then you definitely want to consider storing your business logic in a middle tier, not the DB.

    However, if you have a database that is going to be accessed by multiple programs written in multiple languages (very common in large companies that have been around for a while) then you need to consider implementing at least some business rules in the data layer to reduce duplicate code and provide data integrity.

    The last project I worked on was a large enterprise health care app that needed to talk with the big 3 (Oracle, SQL Server, and DB2). We kept everything in the Middle Tier and it made changing DB's easier.

    The current project I'm working on is accessing a database that has been around for years and isn't going anywhere anytime soon. It is accessed by hundreds of different programs performing various tasks. Imagine all the duplicate BL code that might be slightly different in each program. Some things should be kept in the DB. Unfortunately it's not so there is a lot of code duplication.

    There are lots of different possiblities and a competent architect will evaluate them all before making a design decision.
  85. Right tool for the job[ Go to top ]

    The current project I'm working on is accessing a database that has been around for years and isn't going anywhere anytime soon. It is accessed by hundreds of different programs performing various tasks. Imagine all the duplicate BL code that might be slightly different in each program. Some things should be kept in the DB. Unfortunately it's not so there is a lot of code duplication.
    So have one System (application) ask another System (application) to do something on its behalf and leave the business logic in the other System were it belongs.
  86. RE: Right tool for the job[ Go to top ]

    So have one System (application) ask another System (application) to do something on its behalf and leave the business logic in the other System were it belongs.
    Haha.

    Not the easy thing to do when you're just a contractor and these systems are guarded like children by their various departments that are not just spread accross the country but globally.
  87. RE: Right tool for the job[ Go to top ]

    So have one System (application) ask another System (application) to do something on its behalf and leave the business logic in the other System were it belongs.
    Haha.Not the easy thing to do when you're just a contractor and these systems are guarded like children by their various departments that are not just spread accross the country but globally.
    Yup. The life of an Evangelist/Missionary is not an easy one.
  88. Re: Right tool for the job[ Go to top ]

    It all comes down to the right tool for the job.

    If you have a database that is only going to be accessed by a single application and this database may change, then you definitely want to consider storing your business logic in a middle tier, not the DB.

    However, if you have a database that is going to be accessed by multiple programs written in multiple languages (very common in large companies that have been around for a while) then you need to consider implementing at least some business rules in the data layer to reduce duplicate code and provide data integrity.

    The last project I worked on was a large enterprise health care app that needed to talk with the big 3 (Oracle, SQL Server, and DB2). We kept everything in the Middle Tier and it made changing DB's easier.

    The current project I'm working on is accessing a database that has been around for years and isn't going anywhere anytime soon. It is accessed by hundreds of different programs performing various tasks. Imagine all the duplicate BL code that might be slightly different in each program. Some things should be kept in the DB. Unfortunately it's not so there is a lot of code duplication.

    There are lots of different possiblities and a competent architect will evaluate them all before making a design decision.
    What about protecting the precious data in any RDBMS and write a single SOA based application to serve those hundreds of programs? So that nobody writes the code hundreds of times, you are DB independent, programming language independent..
  89. To the all people interested in the topic I suggest reading great book by Scott W Ambler: Agile Database Techniques- Effective Strategies for the Agile Software Developer .
    It really covers a lot of things very well!
  90. Ambler is a great resource for usefull information. He also wrote one
    of the classic paper "Mapping Objects To Relational Databases" . Nice read, it clarifies a lot of things.
  91. Unprofessional[ Go to top ]

    Nothing really that insightful in the article. Moreover, the tone of the author is unprofessional. "Java boys" and "Java geeks" don't add credibility or substance. The guy obvious is a database dude out to push his agenda.
  92. Unprofessional[ Go to top ]

    Nothing really that insightful in the article. Moreover, the tone of the author is unprofessional. "Java boys" and "Java geeks" don't add credibility or substance. The guy obvious is a database dude out to push his agenda.
    He is just describing the reality. Programmers can mostly be clearly separated into different groups (Java, Microsoft, Unix, etc) with different "religons". Maybe the word "geek" is not appropiate in an article like this.

    About 5 years ago, the usage of stored procedures was considered to be state-of-the-art programming. Your way of designing an application will probably also be considered obsolete in five years.
  93. Not an evangelist[ Go to top ]

    Nothing really that insightful in the article. Moreover, the tone of the author is unprofessional. "Java boys" and "Java geeks" don't add credibility or substance. The guy obvious is a database dude out to push his agenda.
    He is just describing the reality. Programmers can mostly be clearly separated into different groups (Java, Microsoft, Unix, etc) with different "religons". Maybe the word "geek" is not appropiate in an article like this. About 5 years ago, the usage of stored procedures was considered to be state-of-the-art programming. Your way of designing an application will probably also be considered obsolete in five years.
    I am not an evangelist. Each language, technology, framework, methodology, etc. has its strong points and weak points, and each must be evaluated within the proper context. Store procs are great for some things, not great for others. Same with Java.
  94. Unprofessional[ Go to top ]

    Nothing really that insightful in the article. Moreover, the tone of the author is unprofessional. "Java boys" and "Java geeks" don't add credibility or substance. The guy obvious is a database dude out to push his agenda.
    Apologies for the 'tone'.
    It originates from a personal experience in one of our J2EE projects sofar. Should not have made it like that into the paper.

    And yes I'm a database dude, struggling hard, very hard, to learn J2EE.
    As are many other database 'dudes' and gals.
  95. Unprofessional[ Go to top ]

    Nothing really that insightful in the article. Moreover, the tone of the author is unprofessional. "Java boys" and "Java geeks" don't add credibility or substance. The guy obvious is a database dude out to push his agenda.
    Apologies for the 'tone'.It originates from a personal experience in one of our J2EE projects sofar. Should not have made it like that into the paper.And yes I'm a database dude, struggling hard, very hard, to learn J2EE.As are many other database 'dudes' and gals.
    Most understand your struggle. Until you and the other 'dudes' :) realize it really isn't "data", it will continue to be hard. Sorry. And until it happens, we will continue to have JOBOL solutions.

    Now I am beginning to understand why people thing Swing is difficult and slow. If one doesn't take an OO approach to Java, it will ge.
  96. It isnt'data[ Go to top ]

    Most understand your struggle. Until you and the other 'dudes' :) realize it really isn't "data", it will continue to be hard. Sorry. And until it happens, we will continue to have JOBOL solutions.Now I am beginning to understand why people thing Swing is difficult and slow. If one doesn't take an OO approach to Java, it will ge.
    What do you mean it isn't data? Maybe it is. Maybe that is. Maybe we should come to understand that in some/many/most/all situations it IS data (or at least data too). Why should it first and foremost and perhaps even exclusively be Objects? Is it a means or an end? Why can't we accept the fact that 100K worth of bits sometimes should be seen/manipulated/expressed as an object - for example an image that knows how to write itself, how to display itself or that at least can tell us what its size is - and at other times can and really should be dealt with as data - that must be made persistent, should be made available upon request from various requestors, should adhere to certain constraints etc.

    Why this black and white approach? Or did I miss the tongue-in-cheek? In which case you should read this reply as satirical pun rather well meant advise.

    Lucas
  97. It's sad that this debate always tends to become a religious war instead of at least attempting to bring up some arguments. My experience with O/R mapping layers isn't a pleasant one in practice but that may change over time. However, I think they also have some architectural drawbacks:

    * Using only plain SQL statements and no SP's (which is the norm with O/R layers) leads to a situation where lots of intermediary result sets are fetched over the network unnecessarily. It scales much better to have a few coarse grained SP calls instead of a huge number of small SQL statements.

    * Advanced DBMS features cannot be used effectively. Java folks claim that to use such DBMS features should be avoided to keep everything portable. I agree to some extent. Unfortunately, to avoid these features at all cost sometimes means that much more code has to be written and that this code may perform much worse than if DBMS specific features had been used. One of the reasons is, that code in the application layer cannot make use of database indexes nor of the DBMS optimizer. There are tasks where you _have to_ use indexes. It makes no sense to replicate the indexing scheme of the database with lots of Maps that have to be maintained by application code.

    * Optimization is very limited because it's inevitably DBMS specific. Much more so than most Java folks would assume. Sure, you can try to put optimizations in the mapping infrastructure, but since most the mapping code ultimately runs on the Java side, the possibilities for optimization are very limited indeed. Admittedly, it might be possible to tackle this problem by building better mapping infrastructures than are common today.

    * SP's are a way to abstract from the database schema. Schema's of complex systems are there to support not just one but many different applications over a longer period of time. This means that they are highly normalized and unnecessarily complex for each particular application. It makes sense to abstract from this complexity via SP's and views so that application developers need not know every aspect of the schema. Of course, this is very different for single application databases.

    I think there is no way to spare architects and designers the effort to find the right balance between DBMS specific code and portable Java code on a case by case basis. But I think it'd be wrong not to use DBMS features just because people don't know them. It doesn't make sense to write tens of thousands of lines of portable code just to avoid a few hundered lines of PL/SQL that could be ported to various other DBMS in much less time than it takes to duplicate all this functionality in the application layer.
  98. You are talking about existing limitations in tools. ORM idea is not wrong, probably implementations are not ready for RDBMS ( good SQL subset support ). There is more noise and populism around this technology than value at this time.
    I hope this populism will stop and we will see more serious implementations (Lame users will kill this populizm like EJB themself anyway )
  99. A few arguments for a change[ Go to top ]

    <Alexander Jerusalem>
    It doesn't make sense to write tens of thousands of lines of portable code just to avoid a few hundered lines of PL/SQL that could be ported to various other DBMS in much less time than it takes to duplicate all this functionality in the application layer.
    </Alexander Jerusalem>

    Good point. It is always better to keep the data centric bussiness logic code close to the data rather than in the middle-tier. With regard to Porting, we have a comprehensive migration tool(SwisSQL
    to help with porting Stored Procedures. We also have a SwisSQL API (Java and C#) which converts database specific SQL queries in the middle tier to different dialects at run time. The reason we came up with this tool is to encourage users to directly code to the relational model. In our practical experience, we have found that too be the best way to address application evolution and scalability. Complex applications become unnecessarily complex to maintain with a OR mapping layer. I have written an article on this subject. Not a sound technical one, but still gives some arguments for keeping the business logic relational model aware.
    Here is the link to the article.
    href="Should" rel="nofollow">http://www.swissql.com/article/database-logic.html">Should Application Business Logic be Relational Model Aware?</a>
  100. A few arguments for a change[ Go to top ]

    Sorry, the link to my article "Should Application Business Logic Be Relational Model Aware? did not come properly in my last post.
    Here it is again.

    Should Application Business Logic Be Relational Model Aware?

    Sekar
  101. You are talking about existing limitations in tools.
    That's true to some extent. But there are things like the use of indexes that are just not doable outside of the DBMS. So any O/R layer would itself have to be located inside the database to make use of them.
  102. You are talking about existing limitations in tools.
    That's true to some extent. But there are things like the use of indexes that are just not doable outside of the DBMS. So any O/R layer would itself have to be located inside the database to make use of them.
    Database designed using this article recomendations must be accessable from any client or framework with all features client needs, clients see the view only and do not need to see something more, they can not create indexes, it is a good policy for procedures too. Database must not be an application itself and application must not be a databse.
    I am sure a good way is to have *all* constraints and to protect data in database. Stuff like UI, data processing and formating must be implemented by application. Some applications run on server some on client, it depends on use case.
    Both extreame ways are wrong from maintainence and integration point of view, "easy" way makes data integraty and security dependant on client, "hard" way limits methods to access data. "Hard" way is not so wrong, because it is easy to fix using "CREATE VIEW" command, but it is very hard to fix the "Easy" way.
  103. A few arguments for a change[ Go to top ]

    It's sad that this debate always tends to become a religious war instead of at least attempting to bring up some arguments.
    I hope that I have managed to put forward some arguments. There seems to be some misunderstanding of what some modern O/R mapping layers can do:
    Using only plain SQL statements and no SP's (which is the norm with O/R layers) leads to a situation where lots of intermediary result sets are fetched over the network unnecessarily.
    There is no requirement that O/R layers have to work over networks - for performance the software can be sited on the same server as the the database, or even run in an app server which is part of the DB suite.
    Advanced DBMS features cannot be used effectively. Java folks claim that to use such DBMS features should be avoided to keep everything portable.
    Such DBMS features should be avoided explicitly within Java code, but there is nothing to stop a good O/R layer from using such features on specific database. Most do!
    There are tasks where you _have to_ use indexes. It makes no sense to replicate the indexing scheme of the database with lots of Maps that have to be maintained by application code.
    Why is there this persistent view that O/R layers can't use indexes? Of course they can! On the JDO system I use I specify which fields of which Java classes should be indexes in the JDO metadata. The JDO system creates indexes and (where possible, given the underlying DB) foreign keys to maintain the integrity of object links.
    Optimization is very limited because it's inevitably DBMS specific. Much more so than most Java folks would assume. Sure, you can try to put optimizations in the mapping infrastructure, but since most the mapping code ultimately runs on the Java side, the possibilities for optimization are very limited indeed.
    On the contrary - consider the best O/R layers as being like optimizing JITs which translate portable query languages into DB-dependent SQL. Its not uncommon for the very best of the current JDO systems to produce SQL than benchmarks faster than hand-crafted SQL. There is a HUGE amount of effort being put into performance: This is why standards like JDO are good, as they encourage competition.
    Schema's of complex systems are there to support not just one but many different applications over a longer period of time. This means that they are highly normalized and unnecessarily complex for each particular application. It makes sense to abstract from this complexity via SP's and views so that application developers need not know every aspect of the schema. Of course, this is very different for single application databases.
    The O/R layer need not determine the schema: There are plenty of tools to allow the schema (or subsets of it) to produce Java classes than can be used in a specific application.
    I think there is no way to spare architects and designers the effort to find the right balance between DBMS specific code and portable Java code on a case by case basis. But I think it'd be wrong not to use DBMS features just because people don't know them. It doesn't make sense to write tens of thousands of lines of portable code just to avoid a few hundered lines of PL/SQL that could be ported to various other DBMS in much less time than it takes to duplicate all this functionality in the application layer.
    Again, the true situation is often the exact opposite: With transparent persistence systems like JDO and Hibernate, where much of the action of the underlying store is implicit, the amount of portable code in Java could well be less than in PL/SQL, as things like the SQL for joins are automatically generated.

    I'm not 100% against using PL/SQL if it is really necessary. My development strategy is this: Stick to portable code unless there really are serious performance problems that really can't be dealt with by tweaking the O/R query language. If things really are slow, talk to the O/R vendor - they can often provide useful hints about improving things.

    What I am totally against is the developer diving straight into Oracle PL/SQL or some other proprietary language, as this just isn't necessary in most cases.
  104. A few arguments for a change[ Go to top ]

    Using only plain SQL statements and no SP's (which is the norm with O/R layers) leads to a situation where lots of intermediary result sets are fetched over the network unnecessarily.
    There is no requirement that O/R layers have to work over networks - for performance the software can be sited on the same server as the the database, or even run in an app server which is part of the DB suite.
    When I said "network" I didn't necessarily mean two boxes connected by a cable but simply separate processes that communicate via TCP/IP. You always incur a lot more overhead for inter process communication than for in process. So if there is a quick succession of closesly related SQL statements, they should not need to cross process boundaries.
    Advanced DBMS features cannot be used effectively. Java folks claim that to use such DBMS features should be avoided to keep everything portable.
    Such DBMS features should be avoided explicitly within Java code, but there is nothing to stop a good O/R layer from using such features on specific database. Most do!
    Well, I have to believe you when you say that. As far as I have seen, most O/R frameworks try to steer clear of DBMS specifics as far as possible. But I'm open for any examples you might be able to come up with...
    There are tasks where you _have to_ use indexes. It makes no sense to replicate the indexing scheme of the database with lots of Maps that have to be maintained by application code.
    Why is there this persistent view that O/R layers can't use indexes? Of course they can! On the JDO system I use I specify which fields of which Java classes should be indexes in the JDO metadata. The JDO system creates indexes and (where possible, given the underlying DB) foreign keys to maintain the integrity of object links.
    That's a misunderstanding, I should have explained that better. I didn't mean to say that O/R frameworks cannot use the indexes at all. What I'm saying is that if you load your data into an object model and you do all your business rules there, including consistency checking and any searching, sorting and calculations that might be necessary, then you do this without the help of DBMS indexes, which is a bad idea.
    Optimization is very limited because it's inevitably DBMS specific. Much more so than most Java folks would assume. Sure, you can try to put optimizations in the mapping infrastructure, but since most the mapping code ultimately runs on the Java side, the possibilities for optimization are very limited indeed.
    On the contrary - consider the best O/R layers as being like optimizing JITs which translate portable query languages into DB-dependent SQL. Its not uncommon for the very best of the current JDO systems to produce SQL than benchmarks faster than hand-crafted SQL. There is a HUGE amount of effort being put into performance: This is why standards like JDO are good, as they encourage competition.
    But there are lots of optimizations that cannot be done without knowledge of how a particular application typically accesses some part of the database. DBAs analyse such patterns and then make the changes accordingly. There is no way for a JDO vendor to do this for me generically. If, on the other hand, the JDO vendor allows me to optimize the mapping code manually, then I don't see much benefit in comparison to working with the DBMS directly.
    Schema's of complex systems are there to support not just one but many different applications over a longer period of time. This means that they are highly normalized and unnecessarily complex for each particular application. It makes sense to abstract from this complexity via SP's and views so that application developers need not know every aspect of the schema. Of course, this is very different for single application databases.
    The O/R layer need not determine the schema: There are plenty of tools to allow the schema (or subsets of it) to produce Java classes than can be used in a specific application.
    I didn't say that the O/R layer determines the schema. What I'm saying is that SPs can be used as a function based API that insulates the application developer from the complexities of a schema. Are there any O/R frameworks that you know able to use stored procedures?
    I think there is no way to spare architects and designers the effort to find the right balance between DBMS specific code and portable Java code on a case by case basis. But I think it'd be wrong not to use DBMS features just because people don't know them. It doesn't make sense to write tens of thousands of lines of portable code just to avoid a few hundered lines of PL/SQL that could be ported to various other DBMS in much less time than it takes to duplicate all this functionality in the application layer.
    Again, the true situation is often the exact opposite: With transparent persistence systems like JDO and Hibernate, where much of the action of the underlying store is implicit, the amount of portable code in Java could well be less than in PL/SQL, as things like the SQL for joins are automatically generated.I'm not 100% against using PL/SQL if it is really necessary. My development strategy is this: Stick to portable code unless there really are serious performance problems that really can't be dealt with by tweaking the O/R query language.
    My strategy is a little different and performance isn't even the most important issue. The most important issue is productivity. The systems I build are mostly data oriented and much less algorithmic. To deal with data in a set oriented, declarative fashion is simply much less effort than to code everything procedurally in an object model. And then I often have a mixture of relational and document data, more and more XML, and I need to query, transform, extract, analyse, mix and match in all kinds of ways. DBMS are just hugely better at handling this in an efficient manner.

    My procedural code falls in one of two categories: Either it is glue code that connects set oriented operations. If that's the case, I do it within SPs because it's much more efficient than having lots of roundtrips to the database and because it keeps all SQL under the guard of the DBMS which helps me to manage dependencies. Or the procedural code is concerned with controller logic, handling UI events, controlling workflows or the occasional special purpose algorithm. And that's what I do in Java. Frankly, I see no reason for mapping all my nice flexible database schemas into a static object model to which I then have to code in a procedural way.
  105. A few arguments for a change[ Go to top ]

    Well, I have to believe you when you say that. As far as I have seen, most O/R frameworks try to steer clear of DBMS specifics as far as possible. But I'm open for any examples you might be able to come up with...
    The Versant JDO (JDOGenie) and Libelis Lido JDO are good examples.
    That's a misunderstanding, I should have explained that better. I didn't mean to say that O/R frameworks cannot use the indexes at all. What I'm saying is that if you load your data into an object model and you do all your business rules there, including consistency checking and any searching, sorting and calculations that might be necessary, then you do this without the help of DBMS indexes, which is a bad idea.
    No. Searching is done with SQL and indexes in the database. Calculations (such as sum() and count()) are done with SQL in the database. Consistency checking is done using relational controls in the database. O/R frameworks and their query languages are a way of expressing these searches, calculations and checks, but a good O/R framework will use DBMS facilities to do this. For example, searching all instances of a Class for those with a particular field value is NOT done by fetching the instances into memory, and searching there. Its done using SELECT statements generated by the O/R system.
    But there are lots of optimizations that cannot be done without knowledge of how a particular application typically accesses some part of the database. DBAs analyse such patterns and then make the changes accordingly. There is no way for a JDO vendor to do this for me generically. If, on the other hand, the JDO vendor allows me to optimize the mapping code manually, then I don't see much benefit in comparison to working with the DBMS directly.
    That is like saying that if some of my code needs to be in assembly language in order to give good speed, there is no benefit in using a high level language at all.

    Portability is always of benefit, even if its not 100%.
    Are there any O/R frameworks that you know able to use stored procedures?
    Yes. JDO 2.0 will be able to do this, and many implementations of JDO 1.0.1 already can.
    My strategy is a little different and performance isn't even the most important issue. The most important issue is productivity.
    I disagree. Having seen how some companies have suffered from being tied in to legacy code, I would say, for me at least, portability is something that should take first priority.
    Frankly, I see no reason for mapping all my nice flexible database schemas into a static object model to which I then have to code in a procedural way.
    Who says the object model has to be static, or code has to be procedural?
  106. ORM tool in process with db[ Go to top ]

    There is no requirement that O/R layers have to work over networks - for performance the software can be sited on the same server as the the database, or even run in an app server which is part of the DB suite.
    Exactly. And with ORM standards like JDO or EJB 3, perhaps database vendors can write standards-compliant implementations that optionally run in process with the database, and take advantage of proprietary db features to make it very, very fast.

    Client applications could run JDOQL or HQL, instead of SQL, directly against the ORM running on the database server. So in some cases, like thick clients hitting the db/orm server directly, you wouldn't need an application server. And you wouldn't be violating encapsulation, because clients would still be going through the domain layer.

    You could even deny raw SQL access to the underlying tables to further encapsulate things. Or at least try to only use SQL hitting the tables directly for special cases. At this point, you're getting very close to an object-oriented database, but in a more evolutionary fashion.

    One problem of course is that this would not be language agnostic. ORM tools tend to be language specific - JDO and Hibernate for Java, Object Spaces and NHibernate for .NET, etc. There's probably no fundamental reason for this, however. It seems one could come up with an ORM tool that could use the same mapping info to map tables to either Java or C#, for example.

    I'm totally on the "It's the ORM, stupid" side of this debate, but I think there are two valid arguments in the db-centric point of view:

    1. Running the domain logic in the database process can be faster.
    2. Keeping the domain logic closer to the data it operates on provides better encapsulation.

    The problem with doing that in stored procedures is that those languages tend to be proprietary and not very OO. (I think you can call Java from PL/SQL though? I don't know anything about Oracle.) But the idea of putting data and behavior close together is of course a very object-oriented idea. That's what an object is. By putting pure data in the database server, and the business logic in the application server, we're asking for trouble. The data and behavior could literally be miles apart. Now the standard answer of course is to adopt a service oriented architecture, where one app owns the data, and other apps call services on the first app. If followed, this preserves encapsulation. But in practice, it's often just too tempting for the other apps to hit the database directly. Thus bypassing or duplicating the business logic. But if the db server can server up domain objects, you still have encapsultion. And it might encourage dba's and application developers to collaborate more on the domain model.
  107. The problem with doing that in stored procedures is that those languages tend to be proprietary and not very OO. (I think you can call Java from PL/SQL though? I don't know anything about Oracle.)
    Oracle has been supporting Java stored procedures for years, the same goes for DB2. Still I think most people who know both Java and PL/SQL well enough to make the choice based on the merit, would choose PL/SQL 90 % of the time. Not because Java is generally a worse language than PL/SQL (both have a lot going for them) but because PL/SQL is so well integrated with SQL that it is much more productive for the kind of code that you usually put into stored procedures. My suspicion is that many Java developers simply don't bother to try.
  108. Re: ORM tool in process with db[ Go to top ]

    it's not only about giving it a try, what about programming issues. any language used should be good at those indespensible issues : - versioning (especially in pl sql????) - a powerful IDE (compare toad to eclipse or wsad!!)
  109. A few arguments for a change[ Go to top ]

    * Advanced DBMS features cannot be used effectively. Java folks claim that to use such DBMS features should be avoided to keep everything portable. I agree to some extent. Unfortunately, to avoid these features at all cost sometimes means that much more code has to be written and that this code may perform much worse than if DBMS specific features had been used. One of the reasons is, that code in the application layer cannot make use of database indexes nor of the DBMS optimizer. There are tasks where you _have to_ use indexes. It makes no sense to replicate the indexing scheme of the database with lots of Maps that have to be maintained by application code.
     layer.
    Not true. In fact, most of use, I believe, believe that an ORM should take advantage of DB-specific features. And if it does not, then SB specfic features should just be used in such a way that they are isolated.

    I've never written applications that didn't use indices.
  110. Window-on-date apps only[ Go to top ]

    I think he under-estimates the complexities of some of today's applications and thus incorrectly relegates the middle tier to nothing much than a UI-database mediator. How would you handle declarative security in Oracle or plugin different authentication domains in PL/SQL? How do you aggregate different content from different sources in a stored procedure? I know Oracle continues to expand on PL/SQL's features but obviously PL/SQL cannot do everything otherwise we wouldn't need a more full featured, general language. His breakdown of a typical application is not complete and is focused on areas where PL/SQL shines. If your application only involves queries, inserts and updates than obviously the work horse is SQL. He makes the assertion that all applications are "window-on-data" applications. A good portion are, but there are a good portion that has to integrate with other systems/applications, etc.. This is where the middle tier shines. His depiction that it's just one nice little database doesn't work.
  111. Window-on-date apps only[ Go to top ]

    He makes the assertion that all applications are "window-on-data" applications. A good portion are, but there are a good portion that has to integrate with other systems/applications, etc.. This is where the middle tier shines. His depiction that it's just one nice little database doesn't work.
    This is an interesting topic, I code this middle tier in JAVA too, but I think distributed database must be a good way for integration (gateway stuff), It saunds like a dream, but it must be very good to have "view" for all integrated systems and IBM has an open protocol for this reason, but I do not know how it works in practice.
  112. Layer v. OO[ Go to top ]

    To me, PL/SQL (stored procdure)provides good business services. It's a layer and very cost effective. Shops do not cosider a lot on portability. It's impractical or too costly to make everything OO, especially when dealing with RDBMS. OOD finds ways to wrap relational DB, but they are not always good ones considering cost-effectiveness and performance. In realty the vast majority of applications are not OO. SOA is good way to address this issue. Theoretically OO methods are closer to the real life and therefore build better models. As a matter of fact, OO? are still evolving rapidly, esp on data accessing. Nowadays RDBMS is sophicated enough. New DBMS can evolve into managing XML & persistence layer and replace the current OO wrapping layers, or simply become ODBMS. It will depend on the market demands and the market power of vendors.
  113. There are situations where frankly you don't have to be a rocket scientist to find out that implementing data oriented business logic has to be done close to the data, inside the relational database.

    Let me present a case:

    * Data sits most of the times persistently stored in relational databases.
    * Data is very often used by more than one application.
    * Applications - especially in large organizations with a long IT history - are often built using different technologies, ranging from Cobol, Visual Basic and Oracle Forms to C++, Java and some .Net dialect.

    When several applications built in different technologies access the same underlying data - i.e. the same database tables and views - you would have a very hard time implementing the business rules to enforce on that data outside the database; there is no common middle tier for these applications; the only common ground is from the moment they access the database. So any common, data oriented logic, should be implemented in the business rule. The only alternative I can see is to implement that business logic over and over again in every client application. Not a very good idea - although in practice that happens quite a lot of course.

    With the advent of WebServices and the SOA, you would expect applications to access those to get access to services and resources. The WebService becomes the common ground and whatever stack is implementing the WebService can choose 'internally' where best to implement the business rules. If your WebService is implemented in Java sitting on top of JDBC talking to a database, you can pick and choose the best place for the business logic. If no one accesses the database in any other way than through the WebService, implementing the rules somewhere in Java code is fine. Or in C# if that is your vehicle for implementing the WebService. Or C++. Or Cobol.

    Of course no one really believes that every high performing application will do all of its data access through WebServices. So there will probably always be situations where applications (Java, VB, C#, Oracle Forms) directly access resources in the database. In those cases, you should strongly consider implementing the central, common, data oriented business logic inside the database.

    Lucas Jellema
    AMIS Services (http://technology.amis.nl)
  114. I can recommend this link
    http://www.sts.tu-harburg.de/people/pa.hupe/studenttheses/GichahiMT.pdf
    If somebody wants to know about "business logic" and the ways to implement it, it is bit more scientific than discussions like object vs. procedure. Probably it will help to understand why it is lame to harcode "business logic" in sexy objects.
  115. There are situations where frankly you don't have to be a rocket scientist to find out that implementing data oriented business logic has to be done close to the data
    Yup. That is what OOP is all about.
    , inside the relational database.Let me present a case:* Data sits most of the times persistently stored in relational databases.*
    Oh, you mean close to the persistance store.
     Data is very often used by more than one application.* Applications - especially in large organizations with a long IT history - are often built using different technologies, ranging from Cobol, Visual Basic and Oracle Forms to C++, Java and some .Net dialect.When several applications built in different technologies access the same underlying data - i.e. the same database tables and views - you would have a very hard time implementing the business rules to enforce on that data outside the database;
    So don't do that.

     
    With the advent of WebServices and the SOA, you would expect applications to access those to get access to services and resources. The WebService becomes the common ground and whatever stack is implementing the WebService can choose 'internally' where best to implement the business rules.
    Or Message Queueing. Or Corba. Or ... .


     
    Of course no one really believes that every high performing application will do all of its data access through WebServices. So there will probably always be situations where applications (Java, VB, C#, Oracle Forms) directly access resources in the database.
    The use that application (system) to access its persistance store. Use other techniques to message other systems.
  116. Almost all of the replies to this thread are about layered architectures, separation of concerns and all other nice things we've learned the last few decades about how to build information systems.

    The core message of the article is different, however. No one can possibly disagree that strong data-related logic should be put closest to the data as possible. Yes, I agree we are stuck then to proprietary languages like PL/SQL, as IBM seems to be the only one following the official SQL standard here to a considerable extent. What we are building, however, is not information systems that are to be portable easily across multiple types of rdbms (no matter how nice if would be), we are building information systems that do the job and have optimal performance.
    My current project deals with large bulk ata processing, and a set of sql update statements run against a record set consisting of 100K records is several orders of magnitude faster than the equivalent data-logic rewritten using a separate ORM layer. For this project it's the difference between success and failure.

    Don't get me wrong: I favour ORM-like solutions in lots of situations, just not in every possible one.

    Frido
  117. My current project deals with large bulk data processing, and a set of sql update statements run against a record set consisting of 100K records is several orders of magnitude faster than the equivalent data-logic rewritten using a separate ORM layer. For this project it's the difference between success and failure.Don't get me wrong: I favour ORM-like solutions in lots of situations, just not in every possible one.Frido
    And understandably so. It is a matter of presupposistion. If you start with "data" you will end up with a data solution. Look first to see if you can change this -
    My current project deals with large bulk ata processing
    - then maybe you can use OOP. Then again you might be doing it the best possible way.
  118. This wasn't a particularly well-written or insightful article, but then again, neither are most of the comments here. Most anti-DBMS arguments I see tend to stem from a lack of training and insight into them, and annoyance with SQL. Granted, SQL can be annoying. But it also can be extraordinarily effective, maintainable, and highly performant -- provided you have the training and knowledge to work with it -- like anything else in this industry.

    Using PL/SQL judiciously does not sink projects. And knowing Oracle inside-and-out has saved more projects than I can count.

    If the primary goal of your project is portability, then I suggest you don't either have a very poor sense of priorities (how about functionality? productivity?), or you don't have a very interesting system. Some business logic requires access to large amounts of data. This doesn't make sense to write in an application server that has to pull all of this data over a network, instantiate into objects, and then process row-at-a-time. Set oriented logic is much more effective and maintainable.

    Secondly, business logic outside of stored procedures assumes an "application-owned database", eliminating the potential for database-level integration. Perhaps that's why XML is so popular these days, but database-level integration is still a useful tool in the toolbox, and should be considered as part of any trade-off analysis.

    Contrary to the hype, ORM are not particularly the best approach to data persistence. They are useful in cases where you must have a domain model. Many applications do not have a domain model, nor do they need one. Often people try to force fit a domain model, leading to aneimic JavaBeans, and the ORM is used as nothing more than a strongly-typed JDBC, using custom data holders instead of ResultSets.

    DBMS are the most complex pieces of software in use in an enterprise today -- significantly more complex than any J2EE application server, though those are getting there. The variations in models of concurrency/locking, SQL support, tuning features and options, etc. between databases are quite large. These semantics must be understand when writing a large scale transactional system, especially when porting.
  119. This wasn't a particularly well-written or insightful article, but then again, neither are most of the comments here.


    Well, thanks for adding your [not particularly well-written nor insightful] comments to the pile.
  120. I don't see any harm if application has to make lot of databse calls ...
  121. I don't see any harm if application has to make lot of databse calls ...
    i absolutely agree.. we don't have cpu, memory or network limitage in 2006 :)
  122. The lengthy discussion, here, itself - shows that this topic is not a simple one and there are plenty who take one or another side.

    The liberals say: "it is a matter of choice and depands on what are your goals in a specific project"

    Is it,really?

    For me, there are two major criterias to assess the quality of a program code
    1) (Almost) No code duplication
    2) Shortest source code providing the same result.

    I do beleive that these are not just my personal choices. I think those two criterias have proven to indicate to a reusable and maintainable (!!!) code.

    Do not forget - the life of a code begins ONLY after it is deployed to the production environment and you have to maintain it! Until that - it's all jokes and fun. That's when the real thing begins and when it starts to HURT.

    Thesis: Maintainability and Scalibility are the key!

    So, then why do I hate SQL in my code? Because, working in Java - my business logic is object-oriented. You can _not_ create a well-designed Java application which is not OO. OK, my business logic is OO but then - I need to use SQL??? But all these data-models and queries that I need to construct separately - THEY DUPLICATE MY OO LOGIC!!! That's code-duplication! Also, I do have to write all that SQL manually, even though I already have the same in my Java code, so source ain't gonna be the shortest! And - every time I change my Java code, I need to change SQL queries and table structure, too. Damn!

    Who will argue that this does hurt both maintainability and scalability?

    How do I modify my code with Hibernate? My persistence meta-information is in XDoclet form right in my Java-code. I do not write anything extra, outside the pure Java code. When I change something in the Java-code, I run my nice ANT task, which goes through the changed configurated (aut-generated using XDoclet) and using Hibernate Schema Update, does ALL the necessary changes in the database. All I do is: type "ant database".

    For me, a Java architect, Database is a persistence layer. Why should I have Business Logic (!) in a persistence layer? I don't want to know ANYTHING about my persistence layer, except that it stores data persistently. My business logic should have NOTHING to do with it!

    This is why I would hate to see SQL in my Java code and as far as my thesis, above, holds - I can not be wrong.

    But DB-centric advocates, also, have a "winning" argument, don't they? What was that - "PL/SQL provides better performance"?

    Well, we have worked with it, ok? :) PL/SQL can be damn slow, too! So there is no win - any DB access, via ORM, direct SQL or PL/SQL can be slow enough (usually is) to be too slow. That's why we have cache layer.

    Well, good news - my ORM framework - Hibernate allows me to integrate different cache frameworks (from JBossCache to the wonderful - Tangasol Coherense) transparently. So, I do not have to worry about that, either, do I?

    And in addition to all these - I can get RDBMS-independance, if I am careful enough. If my client suddently decides Oracle is too expensive, or new client comes that needs MySQL - here we go - I just smile slightly and say "sure, no prob!".

    Does not that feel great?

    And last but not least - Nobody is trying to diminish the importance of SQL! ORM tools are using SQL behind the scenes - that's what they do and I beleive Hibernate is the most popular exactly because Gavin made it as SQL-friendly as possible. But it needs proper usage, that's all!