-1

I have a table Menu that has column id and parent. Can we use normal JDBC (no ORM) to load hierarchical data from the database? Can we have a generic solution for all databases?

Mat
  • 202,337
  • 40
  • 393
  • 406
Barcelona
  • 9
  • 2
  • What database is this? Support for SQL queries involving hierarchies vary across databases. – Vineet Reynolds Aug 13 '11 at 16:29
  • 3
    Surely you can. The ORM argument is a non-argument. ORMs uses "normal JDBC" under the covers. Do you have a constructive and technical programming question? – BalusC Aug 13 '11 at 16:29
  • Currently, my application supports multiple kind of database. MySql, Oracle, PostgreSql and Sql server are main databases – Barcelona Aug 13 '11 at 16:31
  • So, your question is more SQL-based than JDBC-based? JDBC is just the SQL executor. You control the selected data using SQL. – BalusC Aug 13 '11 at 16:32
  • Can we have a generic solution for this??? I mean we just use JDBC API – Barcelona Aug 13 '11 at 16:34
  • I found this link http://www.patentgenius.com/image/6845376-6.html. Just use JDBC API and resultSet to retrieve hierarchy data – Barcelona Aug 13 '11 at 16:38
  • JDBC is definitely generic for all DBs. You just have to write the proper SQLs and figure if a single SQL query is understood by all DBs. Again, JDBC is just the SQL executor. – BalusC Aug 13 '11 at 16:44

2 Answers2

2

First, design your database tables to store hierarchies. Secondly, use recursive CTEs (Common Table Expressions) to get the hierarchical data. Without any more information about the structure of your database tables, and the queries used, I do not think it is possible to give a more specific answer.

Note, that the queries eventually used across databases could be different (I haven't attempted verifying recursive CTE support across databases), even though recursive CTEs are a part of the SQL99 specification. So, there might be no generic SQL query, but there is certainly a generic approach that you can adopt.

Community
  • 1
  • 1
Vineet Reynolds
  • 76,006
  • 17
  • 150
  • 174
  • I mean generic is generic solution, not generic sql query. It's algorithm to solve this. Thanks for your help Vineet :) – Barcelona Aug 13 '11 at 16:47
  • 1
    Read up on CTEs. You don't need an algorithm, if the database can do the job for you; all you need in a database model that is suitable for a CTE. – Vineet Reynolds Aug 13 '11 at 16:48
0

If your data is in a relational database, and your schema represents it in a hierarchical way, then JDBC can extract it - assuming that you can write the appropriate queries.

I fear that there's nothing generic about. You'll have to write those queries, load the results into objects or data structures, and pass them to your UI for rendering.

duffymo
  • 305,152
  • 44
  • 369
  • 561