12 Replies Latest reply: May 29, 2009 9:49 AM by JosAH RSS

    Java Databases, anyone with knowledge of SQL please help.

    843789
      If I were to make a JAVA program that was to hold information on members of a sports club, IE name, address, age, tournaments won etc...
      I would need to be able to add, modify, sort and delete data in a nice user interface. How would I go about doing it?
      Should I do the database in SQL? Is there a way to create an SQL database and access it from my JAVA code?
      I have learnt SQL at university but we were given accounts on the Oracle website to d it all but I wouldn't be able to use that.

      So basically my question is, how can I make a local SQL database(written by me) which can be accessed via my java program.

      Thank you
        • 1. Re: Java Databases, anyone with knowledge of SQL please help.
          843789
          DarkDevice wrote:
          Should I do the database in SQL?
          Using SQL to access your database is a good and common way, yes.
          Is there a way to create an SQL database and access it from my JAVA code?
          Of course. You use JDBC to access your database.
          So basically my question is, how can I make a local SQL database(written by me) which can be accessed via my java program.
          Sure. Either install your own server (MySQL, PostrgreSQL, Oracle, MS SQL Server, ... pick any one). or you could even use an embedded database server (that runs inside of the Java VM). For those I suggest HSQLDB, but there's also Derby (alias JavaDB) and H2.
          • 2. Re: Java Databases, anyone with knowledge of SQL please help.
            843789
            Thank you very much for the quick reply.

            To install my own server do I have to purchase software or is it free?
            This wouldn't be for commercial purposes but actually for a university project.
            • 3. Re: Java Databases, anyone with knowledge of SQL please help.
              843789
              DarkDevice wrote:
              To install my own server do I have to purchase software or is it free?
              There are tons of different database servers, some are free, others are commercial.

              MySQL, PostgreSQL as well as all the embedded ones I mentioned are free.

              Oracle, DB2, MS SQL Server are commercial products.
              This wouldn't be for commercial purposes but actually for a university project.
              Most commercial products have a free version that can be used for personal use and/or university projects, you could check with that.
              • 4. Re: Java Databases, anyone with knowledge of SQL please help.
                JosAH
                ... also JavaDB (Derby) comes bundled with your JDK installation (version 1.6) so there's nothing more to install ...

                kind regards,

                Jos
                • 5. Re: Java Databases, anyone with knowledge of SQL please help.
                  843789
                  Thanks for replies, I am a little confused, could you please correct my understanding if its wrong:

                  My choices are:
                  Use mySQL to write the database and use JDBC to access/modify it which is done in my java code

                  or

                  I can create the database in javaDB and access/modify it the same way?

                  which is the preferred way of doing it?


                  Sorry for my ignorance, without any hands on experience here its a little difficult to grasp the concept.

                  Thank you,
                  Simon
                  • 6. Re: Java Databases, anyone with knowledge of SQL please help.
                    JosAH
                    DarkDevice wrote:
                    Thanks for replies, I am a little confused, could you please correct my understanding if its wrong:

                    My choices are:
                    Use mySQL to write the database and use JDBC to access/modify it which is done in my java code

                    or

                    I can create the database in javaDB and access/modify it the same way?

                    which is the preferred way of doing it?


                    Sorry for my ignorance, without any hands on experience here its a little difficult to grasp the concept.

                    Thank you,
                    Simon
                    The only advantage of using JavaDB (Derby) would be that you have no need to download anything again because you already have it (if you have the 1.6 JDK). Both need JDBC do communicate with the data base and you already have that too. I don't know why Sun supports both MySQL and JavaDB though ...

                    kind regards,

                    Jos
                    • 7. Re: Java Databases, anyone with knowledge of SQL please help.
                      843789
                      Just to illustrate something.

                      Rest of program -> Your DAO stuff -> JDBC -> Database

                      At the very least the rest of your program should be agnostic to the database you have chosen (and even a database at all). And with some skill and a bit of luck your DAO code should also be agnostic. JDBC provides a standard interface to SQL based data sources. So you can connect to whatever sort of database you want. In a perfect world you'll just plug in whatever driver you want and talk to different databases as you like without changing any code.

                      This can be a bit tricky at times because of vendor variations in supported SQL syntax and features but you should try your best to avoid vendor specific things.

                      So the answer to your question is you can use either MySQL or JavaDB or whatever other database that has a JDBC driver. Why would you choose MySQL or JavaDB? JavaDB is embedded and this can be good if your application is a stand-alone desktop application because the user will not be required to install a database server (like MySQL) in order for your application to work. If your application was multi-user, client-server type of system then something more like MySQL would be in order (as an aside personally I would recommend against MySQL for this and use something like postgresql instead but whatever).
                      • 8. Re: Java Databases, anyone with knowledge of SQL please help.
                        JosAH
                        Note that JavaDB comes with two drivers: an embedded one (one process accesses the database) and a network/server driver that allows for multiple processes accessing the database simultaneously.

                        kind regards,

                        Jos
                        • 9. Re: Java Databases, anyone with knowledge of SQL please help.
                          843789
                          JosAH wrote:
                          Note that JavaDB comes with two drivers: an embedded one (one process accesses the database) and a network/server driver that allows for multiple processes accessing the database simultaneously.

                          kind regards,

                          Jos
                          I can connect multiple users to MS Access as well but that doesn't make it enterprise either. ;)

                          Derby is fine and yes you can have multiple processes but it isn't really an enterprise ready solution. For the OP's purposes it certainly sounds like JavaDB is fine, I was just trying to explain what the differences between that and something like MySQL are.

                          Which everyone else did as well... but you know, sometimes different explanations help.
                          • 10. Re: Java Databases, anyone with knowledge of SQL please help.
                            JosAH
                            cotton.m wrote:
                            JosAH wrote:
                            Note that JavaDB comes with two drivers: an embedded one (one process accesses the database) and a network/server driver that allows for multiple processes accessing the database simultaneously.

                            kind regards,

                            Jos
                            I can connect multiple users to MS Access as well but that doesn't make it enterprise either. ;)

                            Derby is fine and yes you can have multiple processes but it isn't really an enterprise ready solution. For the OP's purposes it certainly sounds like JavaDB is fine, I was just trying to explain what the differences between that and something like MySQL are.

                            Which everyone else did as well... but you know, sometimes different explanations help.
                            I think the word 'enterprise' should be in the filter's NaughtyWords list ...

                            kind regards,

                            Jos ;-)
                            • 11. Re: Java Databases, anyone with knowledge of SQL please help.
                              843789
                              okay brilliant! For this project the software would be stored on one computer at the sports club. There would be no client/server connectivity. So JavaDB sounds perfect.

                              There will be a bit more involved than just modifying/viewing data, otherwise the project would be too small, so assuming JavaDB is flexible, it'll just be the challenges of programming to get me the rest of the way.

                              Last question then I'm done

                              Are there any books/ internet links/tutorials that you could recommend that will help me in accomplishing such tasks?

                              Thank you very much for your time
                              Simon :)
                              • 12. Re: Java Databases, anyone with knowledge of SQL please help.
                                JosAH
                                DarkDevice wrote:
                                Last question then I'm done

                                Are there any books/ internet links/tutorials that you could recommend that will help me in accomplishing such tasks?
                                JavaDB comes with quite a bit of substantial documentation in a couple of formats; it's more than enough to get you up and running. It is installed in a different directory from all your other Java stuff.

                                kind regards,

                                Jos