aspose file tools*
The moose likes JDBC and the fly likes Java GUI design for a Database Explorer Like TOAD Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java GUI design for a Database Explorer Like TOAD" Watch "Java GUI design for a Database Explorer Like TOAD" New topic
Author

Java GUI design for a Database Explorer Like TOAD

Jhansi Nallan
Greenhorn

Joined: Jan 25, 2011
Posts: 11
Hello Coders!

I need to create Java GUI application for Database Editor, which connects to Oracle DB. I'd like to create something exactly like TOAD . The Java GUI application should be able to Display the Table structure in the left navigation menu and the user should be able to execute Select/Update/modify and Delete queries through this GUI. Also, the Application should be able to provide export functionality of the result set.


The project has a very short deadline. -) , am looking for pointers/suggestions on the Java API to support the above functionality and the most easiest way to implement this within short duration.

Appreciate any suggestions at the earliest.

Thanks


OCJP,OCJWD
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61233
    
  66

Why re-invent the wheel? A number of such Java products already exist. Squirell SQL is just one of them.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Jhansi Nallan
Greenhorn

Joined: Jan 25, 2011
Posts: 11
Thanks much for your reply.. It was a requirement for one of my client and unfortunately i cannot suggest them any tools because of some legacy issues. Can you please suggest sites/any samples that will help me with my requirement?
Thanks again!

Bear Bibeault wrote:Why re-invent the wheel? A number of such Java products already exist. Squirell SQL is just one of them.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

what legacy issues? as most likely any API's we point you to will have exactly the same issues as squirrel. Squirrel being written in java.
Jhansi Nallan
Greenhorn

Joined: Jan 25, 2011
Posts: 11
Thanks Wendy,

Can i ask, is it possible to customize this product to suffice our requirements? requirements as in to provide authentication, and authorization to login into this product?
please let me know.


Wendy Gibbons wrote:what legacy issues? as most likely any API's we point you to will have exactly the same issues as squirrel. Squirrel being written in java.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1716
    
  14

Download Oracle's free SQLDeveloper tool, which will do everything you want (and more), is well-documented with plenty of tutorials, and will cost your company nothing to maintain/develop. It also includes tools for data modelling and database design/creation etc.

Don't be a victim of Not Invented Here Syndrome!


No more Blub for me, thank you, Vicar.
Jhansi Nallan
Greenhorn

Joined: Jan 25, 2011
Posts: 11
Thanks Chris, This is exactly what i was looking for, however i donot see the entire source code when imported this to eclipse.
Iam wondering i might need to have a grip of source code in case if there are any requirements to the UserInterface? - I hope i have explained my problem correctly.

Thanks again..

chris webster wrote:Download Oracle's free SQLDeveloper tool, which will do everything you want (and more), is well-documented with plenty of tutorials, and will cost your company nothing to maintain/develop. It also includes tools for data modelling and database design/creation etc.

Don't be a victim of Not Invented Here Syndrome!
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1716
    
  14

Hi Jhansi,

SQLDeveloper is not open source, so if you require open source you need to look elsewhere. But you might also want to sit down with your project's customers and build a MoSCoW list of your requirements, as it sounds like it might be hard to satisfy all of them in the time available.

From your first post, you want:

a Java GUI application for a database editor which connects to Oracle DB
something exactly like TOAD
the Java GUI application should be able to display the table structure in the left navigation menu and the user should be able to execute Select/Update/modify and Delete queries through this GUI
the application should be able to provide export functionality of the result set

All these requirements can be met by one of the free downloads described above, which also allows you to meet your "very short deadline" .

You also mentioned the possible need to:

customize this product to suffice our requirements
to provide authentication, and authorization to login into this product

This might be harder to realise properly in your short time scale.

So far you haven't explained what extra requirements would require you to customise the interface, and to be honest, if your timescale really is very short, you should think carefully about your priorities here.

Using a suitable pre-built tool will ensure you have a secure, robust, flexible, thoroughly tested, well documented and maintained database editor (all for free), but will limit your options for tweaking the interface. So which is more important, being able to control the damage users can do to your database, or being able to change the interface?

With regard to security, most of these tools e.g. SQLDeveloper use database authentication and authorisation i.e. you can control precisely what users can do by controlling the permissions you give their DB accounts. You should do this anyway, as you don't want somebody logging into the GUI and then being able to trash your entire database.

As a database developer, I'd say the security aspect is definitely a "Must" requirement, while the customisable interface is more of a "Should" or "Could", but that is a decision for your project's customers and will depend on how much they want to pay.

Finally, you talk about a "Java API", but what is this API for?

  • If you want to manipulate the data, then you already have an API: it's called SQL.
  • If you want to do this from within Java, there are already plenty of APIS and tools - JDBC, ORMs like Hibernate, and so on.
  • If you want to control how users manipulate domain classes (and the underlying data), then you probably need to do this the way you would do it in any other n-tier Java database application, or define a DSL (maybe using a JVM language like Groovy?).

  • Why do you need yet another API?

    Given your short timescales, it sounds like you need to sit down with your users and go through a reality check as to what you need to implement, what you can get for free, which of these "requirements" are really just examples of YAGNI, and how much time/money you (and your employers) are prepared to invest in building/testing/deploying/documenting/maintaining this new tool if you build it all yourself.

    Good luck.
    Jhansi Nallan
    Greenhorn

    Joined: Jan 25, 2011
    Posts: 11
    Hi Chris,

    Thank you for the detailed information. Here are some of those 'extra' requirements for which i thought i would need to customize the code to fulfill these. I might be wrong.

    1. The user will have the ability to login based on their defined user roles, Since the DB tables has critical data,it is important that not
    every user should have access to update queries.

    2. if a new role has to be created, i cannot modify the functionality

    3. Couldnt find where the creation of login credentuals are to be set in the tool, I have downloaded the SQLDeveloper and checked all the Help Menu.

    4. Password should be expired in 90 days, a customised note for the password expiry dates is to be sent

    5. There is a need to add a firm specific waiver information on some use cases.

    There are few more, but the above listed are some of the must-have functionalities with respect to the requirements. Now, keeping the above in mind, though i could understand that the SQL Developer is enough capable to provide all the features of the Perfect DB Explorer, there is a need to customize this tool for atleast few of the points above.

    Request your further guidance and suggestions on the above. Thank you so much so far for all the useful important information.


    chris webster wrote:Hi Jhansi,

    SQLDeveloper is not open source, so if you require open source you need to look elsewhere. But you might also want to sit down with your project's customers and build a MoSCoW list of your requirements, as it sounds like it might be hard to satisfy all of them in the time available.

    From your first post, you want:

    a Java GUI application for a database editor which connects to Oracle DB
    something exactly like TOAD
    the Java GUI application should be able to display the table structure in the left navigation menu and the user should be able to execute Select/Update/modify and Delete queries through this GUI
    the application should be able to provide export functionality of the result set

    All these requirements can be met by one of the free downloads described above, which also allows you to meet your "very short deadline" .

    You also mentioned the possible need to:

    customize this product to suffice our requirements
    to provide authentication, and authorization to login into this product

    This might be harder to realise properly in your short time scale.

    So far you haven't explained what extra requirements would require you to customise the interface, and to be honest, if your timescale really is very short, you should think carefully about your priorities here.

    Using a suitable pre-built tool will ensure you have a secure, robust, flexible, thoroughly tested, well documented and maintained database editor (all for free), but will limit your options for tweaking the interface. So which is more important, being able to control the damage users can do to your database, or being able to change the interface?

    With regard to security, most of these tools e.g. SQLDeveloper use database authentication and authorisation i.e. you can control precisely what users can do by controlling the permissions you give their DB accounts. You should do this anyway, as you don't want somebody logging into the GUI and then being able to trash your entire database.

    As a database developer, I'd say the security aspect is definitely a "Must" requirement, while the customisable interface is more of a "Should" or "Could", but that is a decision for your project's customers and will depend on how much they want to pay.

    Finally, you talk about a "Java API", but what is this API for?

  • If you want to manipulate the data, then you already have an API: it's called SQL.
  • If you want to do this from within Java, there are already plenty of APIS and tools - JDBC, ORMs like Hibernate, and so on.
  • If you want to control how users manipulate domain classes (and the underlying data), then you probably need to do this the way you would do it in any other n-tier Java database application, or define a DSL (maybe using a JVM language like Groovy?).

  • Why do you need yet another API?

    Given your short timescales, it sounds like you need to sit down with your users and go through a reality check as to what you need to implement, what you can get for free, which of these "requirements" are really just examples of YAGNI, and how much time/money you (and your employers) are prepared to invest in building/testing/deploying/documenting/maintaining this new tool if you build it all yourself.

    Good luck.
    Wendy Gibbons
    Bartender

    Joined: Oct 21, 2008
    Posts: 1107

    Jhansi Nallan wrote:Hi Chris,

    Thank you for the detailed information. Here are some of those 'extra' requirements for which i thought i would need to customize the code to fulfill these. I might be wrong.

    1. The user will have the ability to login based on their defined user roles, Since the DB tables has critical data,it is important that not
    every user should have access to update queries.

    2. if a new role has to be created, i cannot modify the functionality

    3. Couldnt find where the creation of login credentuals are to be set in the tool, I have downloaded the SQLDeveloper and checked all the Help Menu.

    4. Password should be expired in 90 days, a customised note for the password expiry dates is to be sent

    5. There is a need to add a firm specific waiver information on some use cases.

    There are few more, but the above listed are some of the must-have functionalities with respect to the requirements. Now, keeping the above in mind, though i could understand that the SQL Developer is enough capable to provide all the features of the Perfect DB Explorer, there is a need to customize this tool for atleast few of the points above.

    Request your further guidance and suggestions on the above. Thank you so much so far for all the useful important information.


    chris webster wrote:Hi Jhansi,

    SQLDeveloper is not open source, so if you require open source you need to look elsewhere. But you might also want to sit down with your project's customers and build a MoSCoW list of your requirements, as it sounds like it might be hard to satisfy all of them in the time available.

    From your first post, you want:

    a Java GUI application for a database editor which connects to Oracle DB
    something exactly like TOAD
    the Java GUI application should be able to display the table structure in the left navigation menu and the user should be able to execute Select/Update/modify and Delete queries through this GUI
    the application should be able to provide export functionality of the result set

    All these requirements can be met by one of the free downloads described above, which also allows you to meet your "very short deadline" .

    You also mentioned the possible need to:

    customize this product to suffice our requirements
    to provide authentication, and authorization to login into this product

    This might be harder to realise properly in your short time scale.

    So far you haven't explained what extra requirements would require you to customise the interface, and to be honest, if your timescale really is very short, you should think carefully about your priorities here.

    Using a suitable pre-built tool will ensure you have a secure, robust, flexible, thoroughly tested, well documented and maintained database editor (all for free), but will limit your options for tweaking the interface. So which is more important, being able to control the damage users can do to your database, or being able to change the interface?

    With regard to security, most of these tools e.g. SQLDeveloper use database authentication and authorisation i.e. you can control precisely what users can do by controlling the permissions you give their DB accounts. You should do this anyway, as you don't want somebody logging into the GUI and then being able to trash your entire database.

    As a database developer, I'd say the security aspect is definitely a "Must" requirement, while the customisable interface is more of a "Should" or "Could", but that is a decision for your project's customers and will depend on how much they want to pay.

    Finally, you talk about a "Java API", but what is this API for?

  • If you want to manipulate the data, then you already have an API: it's called SQL.
  • If you want to do this from within Java, there are already plenty of APIS and tools - JDBC, ORMs like Hibernate, and so on.
  • If you want to control how users manipulate domain classes (and the underlying data), then you probably need to do this the way you would do it in any other n-tier Java database application, or define a DSL (maybe using a JVM language like Groovy?).

  • Why do you need yet another API?

    Given your short timescales, it sounds like you need to sit down with your users and go through a reality check as to what you need to implement, what you can get for free, which of these "requirements" are really just examples of YAGNI, and how much time/money you (and your employers) are prepared to invest in building/testing/deploying/documenting/maintaining this new tool if you build it all yourself.

    Good luck.


    personally i would implement items 1-4 in the database: setting up user roles, as if you do this in the gui what is to stop someone downloading another gui and using that.
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1716
        
      14

    Wendy's right. Oracle has a very flexible, robust and sophisticated system for managing user permissions etc. Why not use it, instead of re-inventing a square wheel?

    1. The user will have the ability to login based on their defined user roles, Since the DB tables has critical data,it is important that not every user should have access to update queries.
    2. if a new role has to be created, i cannot modify the functionality

    Your DBA can implement this via database authentication/authorisation/roles etc, so the same rules apply regardless of how a user manages to connect to the database.

    3. Couldnt find where the creation of login credentuals are to be set in the tool, I have downloaded the SQLDeveloper and checked all the Help Menu.

    You do this by logging into SQL Developer as a DBA user, then setting up the appropriate user account details. Another job for your DBA.

    4. Password should be expired in 90 days, a customised note for the password expiry dates is to be sent.

    Your DBA can implement this in the database, so the same rules apply regardless of how a user manages to connect to the database.
    Jhansi Nallan
    Greenhorn

    Joined: Jan 25, 2011
    Posts: 11
    Thank you Chris and Wendy for your valuable suggestions and wonderful ideas.

    I had put forth the proposal for this tool - everything except that there should not be any provision for executing Ad-Hoc queries was appreciated.
    Now, its because that the important aspect is not to facilitate to execute the Ad-Hoc queries, I think i should have to undergo re-inventing the wheel ,

    But, looking forward for sure to any more suggestions/expertise solutions to fullfill this important most requirement. Appreciate all your constant help and Thank you in advance - Hope i will get it soon.

    Thank you both,


    chris webster wrote:Wendy's right. Oracle has a very flexible, robust and sophisticated system for managing user permissions etc. Why not use it, instead of re-inventing a square wheel?



    1. The user will have the ability to login based on their defined user roles, Since the DB tables has critical data,it is important that not every user should have access to update queries.
    2. if a new role has to be created, i cannot modify the functionality

    Your DBA can implement this via database authentication/authorisation/roles etc, so the same rules apply regardless of how a user manages to connect to the database.

    3. Couldnt find where the creation of login credentuals are to be set in the tool, I have downloaded the SQLDeveloper and checked all the Help Menu.

    You do this by logging into SQL Developer as a DBA user, then setting up the appropriate user account details. Another job for your DBA.

    4. Password should be expired in 90 days, a customised note for the password expiry dates is to be sent.

    Your DBA can implement this in the database, so the same rules apply regardless of how a user manages to connect to the database.
     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
     
    subject: Java GUI design for a Database Explorer Like TOAD