Better SQLExceptions in Java 6

Discussions

News: Better SQLExceptions in Java 6

  1. Better SQLExceptions in Java 6 (14 messages)

    Heinz Kabutz' latest JavaSpecialists newsletter is "Better SQLExceptions in Java 6," detailing the new exception hierarchy available with JDBC 4.0. His coda is interesting:
    This is all very nice, but something like this should have been available in Java 1.0. To now go back and fix all the legacy code is just not practical. New code is usually done with the Java Persistence API, not direct JDBC calls. Still, I am pleased to see this finally being added to JDBC.
    So... is that actually true? While a number of projects are able to use JPA (because it's not tied specifically to Java EE), is it valid to say that code is "usually done" with JPA over JDBC?

    Threaded Messages (14)

  2. The new exceptions aren't enough of an improvement. For example, if you have a constraint violation, your DB knows what constaint is being violated, and if it told your application it could translate it into an intelligent error message. But no, we still just get a string containing the entire error message, and a SQL state.
  3. Re: Better SQLExceptions in Java 6[ Go to top ]

    For example, if you have a constraint violation, your DB knows what constaint is being violated, and if it told your application it could translate it into an intelligent error message.

    But no, we still just get a string containing the entire error message, and a SQL state.
    For this, you have to standardize the error messages / codes of each DBMS first. Tell that to the DB vendors. Anyway, if your application generates a constraint exception it's not the fault of your database (which has to complain).
  4. Re: Better SQLExceptions in Java 6[ Go to top ]

    For this, you have to standardize the error messages / codes of each DBMS first.
    No, you don't. All you need are named constaints. I believe most databases support them. So when the DB says constaint USER_UNIQUE_NAME was violated, your application knows that you just tried to create a duplicate user entry and it should prompt for a different username.
    Anyway, if your application generates a constraint exception it's not the fault of your database (which has to complain).
    Uhuh, and this violates the DRY principle. So I declaritively specify a constraint in the DDL for my database, and then I write a bunch of procedural code to do the exact same thing. Or maybe I use XML or annotations + a third-party library do the checking. It's still being repeated, both in the CPU sense and in the code sense. The database is going to check the constraint anyway. Databases are good at that kind of thing, and provide a nice, clean, declarative language for specifying many common constraints. What's wrong with letting the database do what it is good at and then report it back to the application?
  5. Re: Better SQLExceptions in Java 6[ Go to top ]

    For this, you have to standardize the error messages / codes of each DBMS first.
    No, you don't. All you need are named constaints. I believe most databases support them. So when the DB says constaint USER_UNIQUE_NAME was violated, your application knows that you just tried to create a duplicate user entry and it should prompt for a different username.
    Ok, without standardization of exception responses, it's up to your application to disassemble the constraints (if available, hopefully). That's what I said.
    Anyway, if your application generates a constraint exception it's not the fault of your database (which has to complain).
    Uhuh, and this violates the DRY principle. So I declaritively specify a constraint in the DDL for my database, and then I write a bunch of procedural code to do the exact same thing. Or maybe I use XML or annotations + a third-party library do the checking. It's still being repeated, both in the CPU sense and in the code sense. The database is going to check the constraint anyway. Databases are good at that kind of thing, and provide a nice, clean, declarative language for specifying many common constraints.

    What's wrong with letting the database do what it is good at and then report it back to the application?
    My understanding of a database is a generic, at least ANSI SQL compliant persistence storage. Today, with my domain model, I develop the DDL for the database (hey, we have JPA(/Hibernate/TopLink/..) + Annotations now). If my business logic, model or data access layer is not aware of logical (!) constraints, it's the fault of my application. In my opinion, the database's constraint ensures integrity to the database only. Relying on other layers regarding the DRY principle is no excuse for a bad use case modeling (e.g. a product providing MySQL support for InnoDB and MyISAM, both are JDBC compliant database engines).
  6. Re: Better SQLExceptions in Java 6[ Go to top ]

    Relying on other layers regarding the DRY principle is no excuse for a bad use case modeling (e.g. a product providing MySQL support for InnoDB and MyISAM, both are JDBC compliant database engines).
    Since when do use cases mention database technologies? They're supposed to be from the user perspective. Ok, let's say you have a requirement to support multiple database engines, some with vastly different capabilities. In that case I can better understand wanting to push things up into the application layer.
    If my business logic, model or data access layer is not aware of logical (!) constraints, it's the fault of my application.
    What I'm suggesting is far from a lack of awareness. The application has to be very aware of the containts, it simply doesn't have to provide the enforcement mechanism. It still must translate what the database tells it into meaningful results.
    Today, with my domain model, I develop the DDL for the database (hey, we have JPA(/Hibernate/TopLink/..) + Annotations now).
    In some cases a technology stack like this adds tremendous value. In others it is just excess baggage. In some cases thinking of your domain in OO terms is better than in relational terms. In some cases relational terms are better. And in many cases it doesn't make that big of a difference. I think it could greatly simplify many applications to have a standard way for the database to more effectively communicate errors back to the application, so that the application can interpret them and take necessary actions.
  7. Re: Better SQLExceptions in Java 6[ Go to top ]

    For this, you have to standardize the error messages / codes of each DBMS first.


    No, you don't. All you need are named constaints. I believe most databases support them. So when the DB says constaint USER_UNIQUE_NAME was violated, your application knows that you just tried to create a duplicate user entry and it should prompt for a different username.

    Anyway, if your application generates a constraint exception it's not the fault of your database (which has to complain).


    Uhuh, and this violates the DRY principle. So I declaritively specify a constraint in the DDL for my database, and then I write a bunch of procedural code to do the exact same thing. Or maybe I use XML or annotations + a third-party library do the checking. It's still being repeated, both in the CPU sense and in the code sense. The database is going to check the constraint anyway. Databases are good at that kind of thing, and provide a nice, clean, declarative language for specifying many common constraints.

    What's wrong with letting the database do what it is good at and then report it back to the application?
    You bring up an interesting idea but I'm wondering when it would be most worthwhile. For your standard web data-entry application I'm not sure how you'd avoid DRY principal violations. Typically you have multiple levels of validation: JavaScript, business logic, and finally database. For many scenarios I wouldn't want to hit my database to validate - I'd rather have that closer to the client. For example, if a string was too long, etc.
  8. Re: Better SQLExceptions in Java 6[ Go to top ]

    You bring up an interesting idea but I'm wondering when it would be most worthwhile. For your standard web data-entry application I'm not sure how you'd avoid DRY principal violations. Typically you have multiple levels of validation: JavaScript, business logic, and finally database. For many scenarios I wouldn't want to hit my database to validate - I'd rather have that closer to the client. For example, if a string was too long, etc.
    How much overhead does making a (1) trip to the server, and (2) trip to the database add in real-world execution? In how many environments does it matter? My guess is that in high-volume internet sites avoiding the trips is important. But for your average intranet application it doesn't really matter. Also, for simple validation like length, you could access that from the db metadata and push it up the the UI.
  9. Re: Better SQLExceptions in Java 6[ Go to top ]

    You bring up an interesting idea but I'm wondering when it would be most worthwhile. For your standard web data-entry application I'm not sure how you'd avoid DRY principal violations. Typically you have multiple levels of validation: JavaScript, business logic, and finally database.

    For many scenarios I wouldn't want to hit my database to validate - I'd rather have that closer to the client. For example, if a string was too long, etc.


    How much overhead does making a (1) trip to the server, and (2) trip to the database add in real-world execution? In how many environments does it matter?

    My guess is that in high-volume internet sites avoiding the trips is important. But for your average intranet application it doesn't really matter.

    Also, for simple validation like length, you could access that from the db metadata and push it up the the UI.
    I'm not knocking the idea but it just seems a bit retro to me. I think what you gain in DRY you lose in having all of your business logic/validation in a centralized location. The DB is only going to go so far in helping validate. Really, only allowing certain data types and sizes. I think most of the SQL errors that could be thrown that you'd be able to handle (not faults) would be whittled down quite a bit in development and testing. Take a field like "phone number" for example. Other than size of the field, the database won't help with validation. So why would I want every business rule on telephones to be in my Java except maximum length? Should that validation be handled via some DAO exception trapping or alongside the area codes, dashes, extensions, etc validation? Pulling validations from the dao metadata is also interesting and would be more viable if there was a true standard for metadata across different databases. I'm wondering if it would be better as a generate once function instead of reading the metadata dynamically. How often does the data store shape change once you are in production? Usually any change is additive and doesn't affect existing tables or columns. I hope I'm not setting up a strawman argument here. Just that for the most part I've always been advised to consolidate business logic on the middle tier. I can see more of your point with the duplicate record issue. Normally you'd have to start a transaction, check to see if the key already exists, and then do the insert if it is valid. If you had better exceptions coming back from the DB you may just go ahead and attempt the insert and handle the exception. That probably is more analogous to an exceptional occurrence.
  10. Re: Better SQLExceptions in Java 6[ Go to top ]

    I'm not knocking the idea but it just seems a bit retro to me.
    It is...but then I spent a couple months working in LISP and I've suddenly found anomymous-inner-classes-as-functors popping up in my Java and find myself wishing for closures. Sometimes there are some good ideas hiding in old technology.
    Other than size of the field, the database won't help with validation.
    If your database is extenisble enough you could write a function and stick it in a check contraint to validate the phone number. With some databases you could even write the function in Java...
    So why would I want every business rule on telephones to be in my Java except maximum length?
    I agree. Scattering validation rules to the four winds seems like a very bad idea. Unfortunately this happens a lot. There are some rules
    How often does the data store shape change once you are in production?
    Too often and not often enough. But that's another issue...
    I hope I'm not setting up a strawman argument here. Just that for the most part I've always been advised to consolidate business logic on the middle tier.
    I'll think of this as a thought experiment...so a strawman argument would be completely acceptable... So what exactly is business logic? 1. Point-in-time data validation rules 2. Validation of data invariants 3. Lots of other stuff.. So #1 would be something like validating that a specified credit card number actually identifies an account that matches the other identification information specified. IMHO these belong in the middle tier. #2 would be things like lengths, not nulls, uniqueness, foreign key references, range boundaries, etc. IMHO these should be in the database tier, because the database should make certain that the invariants always hold. However, errors in regards to these validations must make it all the way back up to the user in a useful form, and at times it may be very beneficial to have them checked prior to entry into the database tier. Frequently applications check their data-related invariants in the middle tier and not in the database tier. Two bad things arise from this. (1) is that some genius writes some code that modifies data w/o doing proper checks, and the application starts crashing because those were being treated as invariants all over the code. (2) is it becomes extremely difficult to modify the data without going through the API. Being able to occasionally attack the database with SQL without worry about completely hosing it up is very valuable from an operations perspective. Overall, I think it would be very beneficial if the database tier and the application tier were not so strongly separated. But I'm not sure exactly how thick the divide should be. At times I think it should go away, and other times I think it should just be less like the Berlin wall and more like a Canadian border crossing (well...crossing under the old rules...).
  11. Re: Better SQLExceptions in Java 6[ Go to top ]

    Yeah, I'm hearing you. I'm a database-first developer many times. When I think of an enterprise, I think of data stores before I think of software since that is the heart of the enterprise. Still, I think there would need to be some sort of data tier revolution before I'd chance coding my business rules (even if it is just validation) there again. I recently had to tear down Java stored procedures at a company because, while easy to write, they are not at all easy to maintain. At the client the developers didn't have the rights to install them so every change required an email to the DBA group. Making sure the right version rolled out with the J2EE code was a pain too. This is a bit of a tangent from the conversation but my point is that I'm conservative enough to not want to do anything too novel on the data tier. Going back to the article and your first comment I could see some usefulness for having a set of standardized exceptions over what we have now with JDBC. Spring's exception wrappings is a step in the right direction. I'll have to read the Java 6 spec to see what they are doing.
  12. Re: Better SQLExceptions in Java 6[ Go to top ]

    At the client the developers didn't have the rights to install them so every change required an email to the DBA group.
    I would call that extremely agile compared to what I've seen. I think the...difficulty...in dealing with the DBA group in large organizations has lead to some less-than-ideal technical decisions. On a number of occasions I've seen developers design around the need to make database changes solely to avoid the DB change process.
  13. Re: Better SQLExceptions in Java 6[ Go to top ]

    At the client the developers didn't have the rights to install them so every change required an email to the DBA group.


    I would call that extremely agile compared to what I've seen. I think the...difficulty...in dealing with the DBA group in large organizations has lead to some less-than-ideal technical decisions. On a number of occasions I've seen developers design around the need to make database changes solely to avoid the DB change process.
    Yeah, we've done that here too. Moved a lot of lookup code tables to configuration files to avoid DBA & data modeling overhead. That's part of being a desinger though right? Not just purity but designing to the reality of the situation.
  14. Re: Better SQLExceptions in Java 6[ Go to top ]

    This is all very nice, but something like this should have been available in Java 1.0. To now go back and fix all the legacy code is just not practical.
    I think JDBC came in in Java 1.1... (Sorry, couldn't resist nitpicking.)
    New code is usually done with the Java Persistence API, not direct JDBC calls. Still, I am pleased to see this finally being added to JDBC.
    While the JPA standard for O/R mapping is great news, there are still plenty of reasons to use lower-level SQL-oriented approaches in some applications, or in particular parts of applications using O/RM. For example, the need to use stored procedures, or perform efficient set-based operations. O/RM is valuable but isn't a universal solution. Spring has offered a rich exception hierarchy for data access, and sophisticated, configurable exception translation, since way before 1.0 (i.e. for nearly 4 years). It works on Java 1.3 and above, and not only covers JDBC but also all mainstream O/R mapping technologies, including JPA, even supporting mixed use. (JPA can throw multiple differents exceptions that aren't all derived from a JPA base class, btw.) It even allows for custom subclasses of DataAccessException to be thrown when specific database exception codes are encountered: for example, if an Oracle stored procedure raises an exception with a code above 20,000, which is allowed for application use. Rod Johnson Interface21 - Spring from the Source: Support, Training, Consulting
  15. Re: Better SQLExceptions in Java 6[ Go to top ]

    This is all very nice, but something like this should have been available in Java 1.0. To now go back and fix all the legacy code is just not practical.

    I think JDBC came in in Java 1.1... (Sorry, couldn't resist nitpicking.)
    Thanks for pointing that out, Rod, I didn't know that. The first version of Java that I really used was 1.1.
    Spring has offered a rich exception hierarchy for data access, and sophisticated, configurable exception translation, since way before 1.0 (i.e. for nearly 4 years).
    Perhaps I should have written: "New code is usually done with the Java Persistence API or the Spring Framework, not direct JDBC calls." I've updated my newsletter accordingly. Heinz