Catch truncation errors

I have a small application with an embedded database. Sometimes Is get truncation err开发者_C百科ors when trying to insert varchars which exceeds the maximum size of the corresponding database column.

I wish to detect this before insert/updating and show a correct message to the user.

Now I presume that there are two possibilities to achieve this.

1. Get the maximum length of the column of interest through the DatabaseMetaData object. You could reduce the performance lack by using Singletons or similar constructions.

2. Keep the maximum lengths in the Java code (eg: in ResourceBundle or Properties file) and check against these values. Downside is off course that Java code and database must be in sync. This is error prone.

What would be the best approach?

---

The only answer that won't require maintenance is getting the maximum length of the column of interest at database connect time.

If you use Integer.valueOf(...) you can store this in an object, which the lower values (according to the current JVM specs) backs to a singleton pool anyway. This will unload a lot of memory performance issues, as all the columns will eventually refer to the few unique values you likely have in your database.

Also, digging around in the DatabaseMetaData, I would look for any flags that indicate that columns would be truncated upon larger than data inserts. It may provide the switch to know if your code is needed.

By putting the values in a property file, you ease the detection of the issue, but at the cost of possibly getting them out of sync. Such techniques are effectively quick implementations with little up-front cost, but they create latent issues. Whether the issue ever gets raised will be unknown, but given enough time, even the remote possibilities are encountered.

---

Combination of both the approaches. During application build time, you use DatabaseMetaData to dynamically create a resource bundle.

---

One solution would be to use a CLOB. I don't know what other requirements you have for this field.

Also, Use the smallest max character value you have as a constant in the java code. This handles it having to be in sync or db dependent and it's more or less arbitrary anyway. Users don't care what the max size is, they just need to know what the max size is or be kept from making an error automatically.