Data retrieval bike

Anyone engaged in industrial development bloody enterprise not once had to deal with writing a layer of work with the database. We are faced with this.

Our project is built on the Finnish framework Vaadin and pure JDBC at the base of the database operation layer. Without experience with JDBC, we pile up a rather large layer of spaghetti code, and then we valiantly dealt with it.

About how we struggled with this and what bike was invented under the cut.

What led to this decision

In Vaadin, you can display data in the UI components using the BeanItem Container , you can read it here.

Briefly about the subject area

A certain number of entities that are shown to the user and the user must edit them: delete, modify, add.

Entities were described using the Bean specification:

 public class Element implements Serializable{ private Integer id = 0; private String name = ""; private Float price = 0.0F; // getter and setter for property } 

The container for such an element is created simply:

 BeanItemContainer<Element> container = new BeanItemContainer<>(Element.class); 

And further this container is substituted in UI with the help of methods setContainerDataSource(...) .

To get this container, you must first parse it into instances of the Element class from the ResultSet obtained by querying the database.

Initially, the solution was of this type:

 public Element(ResutlSet rs){ try { id = rs.getInt("id"); }catch(SqlException e){ id = 0; e.printStackTrace(); } try { name = rs.getString("name"); }catch(SqlException e){ name = ""; e.printStackTrace(); } try { price = rs.getFloat("price"); }catch(SqlException e){ price = 0.0f; e.printStackTrace(); } } 

And then the angry gazes of the Java gurus should be directed at me and karma will fly into the minuses.
But the concept is this: if during the parsing of data from the ResultSet field caused an error, the system should not fall out with Exception and continue to work and write error logs (logging here and further will not be a topic for a separate article).

But I admit such a terrible code, and after reading Uncle Bob’s famous book , there was an overwhelming desire to fix this code.

As a result, we wrote a library that takes upon itself the acquisition of data from the ResultSet .

Implementation

Using the Decorator pattern, we created a small Executor library.

This library extends ResultSet functionality by adding secure methods for retrieving data.

 public Integer getInt(String columnName) { Integer i = 0; try { i = rs.getInt(columnName); } catch (SQLException ignored) { } return i; } public Integer getIntNull(String columnName) { Integer i = 0; try { i = rs.getInt(columnName); if (rs.wasNull()) return null; } catch (SQLException ignored) { } return i; } public String getString(String columnName) { String s = ""; try { s = rs.getString(columnName); if (s == null) return ""; } catch (SQLException ignored) { } return s; } 

Thus, a 100% guarantee of getting results and working without Exception when processing data that looks a bit scary in Vaadin.

To the question, and if Exception needed, the answer is: plans to add a constructor for the Executor with the type of Exception called, because the simple SqlExecption not informative, and implement the methods for correct operation.

The following API is planned ( UPD: suggestions are made from the comment ):

 public Executor(ResultSet rs, Class<? extends RuntimeException> exceptionClass){ this.rs = rs; this.exceptionClass = exceptionClass; } public Integer getIntThrow(String columnName) { Integer i = 0; try { i = rs.getInt(columnName); if (rs.wasNull()) return null; } catch (SQLException ex) { throw exceptionClass.newInstance(); } return i; } 

And use case

 public Element(ResutlSet rs){ Executor ex = new Executor(rs, CreateElementException.class); id = ex.getIntThrow("id"); name = ex.getStringThrow("name"); price = ex.getFloatThrow("price"); } private class CreateElementException extends RuntimeException{ private String message = ""; public CreateElementException(String message){ this.message = message; } @Override public String getMessage(){ return "Exception with access to column with name " + this.message; } } 

Examples of using

What happens with the code after using this library. The constructor for the Element class has changed to the following:

 public Element(ResutlSet rs){ Executor ex = new Executor(rs); id = ex.getInt("id"); name = ex.getString("name"); price = ex.getFloat("price"); } 

As a result:

• the code became less.
• reduction of code base volumes, with the presence of a large number of classes describing entities.
• the code becomes clearer for perception and the space for errors becomes smaller.
• easier to write tests.
• the appearance of an additional library in dependencies.

Conclusion

Another bike made bikes need to be able to do too , which provides universal and secure access to receive data from the database. People wishing to use the source library are welcome to GitHub . I would like to get an assessment of the solution bicycle and constructive suggestions and comments.