I am new to Java and writing some code to retreive data from a DB view.
1. The code can access a view alone in the DB, so only data retreival using 'select where' query. (no update, delete, create queries )
2. The view contains 18,000 rows with 150 coulmns.
3. Number of columns may decrease/increase often.
As of now I am storing the JDBC result set in List<Map<String,String>>
Most of the times the query sent is "select * from table_name". I need all the columns for my processing and i do not need to take care of what is the column type(timestamp,varchar,etc all will be treated as string values).
Is this a right approach? Should I use a ORM for this? I was told ORM will be helpful if i have to persist the data in object . In my case i need the column name and its value for each row. so I'll end up having List of customObjects instead of Maps if I use hibernate. Please guide me on finding an efficient approach.
JDBC is a very low-level API. It allows you to work with the nuts and bolts that a relational database consists of directly: tables, columns, rows, etc. When you are programming in an object oriented language, you are dealing with classes and objects, and often you want to convert data from rows in the database to objects in your program.
An ORM (Object Relational Mapping) framework does that for you: it maps database tables to classes and rows to instances of those classes. It does all the tedious, low-level work for you, so that if you for example want to do a query, you can directly get a list of objects back, instead of a ResultSet that you have to iterate and convert all returned rows to objects yourself. Likewise for when you want to store data in the database: you pass your object to the ORM framework and it will automatically convert it to records in a table for you.
Most ORM frameworks do more than one-to-one mapping of classes to tables. They can also map relationships between classes etc.
If you are doing very dynamic queries, as you seem to be doing in your code (creating a map with column names from the metadata of the ResultSet), then an ORM might not work very well for you. An ORM works best if you have a limited set of tables, so that you can map each table to a class.
I think doing generic queries and storing the results in a list of maps is not a very good design. It provides no type safety (all selected values are stored as strings, no matter what their actual types are) and you'd need to do all kinds of checks all the time to see if the right columns were selected.
The type of the column is not required because all the column values will be processed as String variables only. I was going through some hibernate materials and I found that they access and update tables in DB. I am working with a view alone. will this create any issue while mapping? There is an ID tag in hbm.xml which should have the primary key.
In this view primary key is composite(four columns). How to have this in ID? Can I use generator for ID if I use view, not a table?