Results 1 to 11 of 11
Like Tree1Likes
  • 1 Post By Eranga

Thread: Some missing info on how Java and SQL work together

  1. #1
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Some missing info on how Java and SQL work together

    Hi,

    I'm working on simplifying basic SQL management. Therefore, taking into account that I had no idea on the relation between Java and SQL, I've been trying to learn about it. But I don't get to understand it. For example, I've seen that I must create kind of a connection between the data and the Java application. So I guess that, for creating basic tables, something like this should be enough:

    Java Code:
        public static void createTable(String tableName, ArrayList<String> columns) {
            Statement stmt = connection.createStatement();
            String command = "CREATE TABLE" + tableName + "(";
    
            for (String target : columns) {
                command = command.concat(target);
            }
            command = command.concat(")");
            try {
                stmt.executeUpdate(command);
            } catch (SQLException ex) {
                Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    1-After investigating, it seems than the best way to connect to a local source of data is through simulation by using some software like XAMPP. But I keep having some hope so, is there any way to make a really-local connection?
    2-For creating the data and testing my methods, I want to use LibreOffice. But it just allows me to create an ODB file. I know then how to create tables and do SQL and PL/SQL stuff on it, but I have no idea on how to stablish a relation between the Java application and that file. Any help, please?

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default Re: Some missing info on how Java and SQL work together

    Don't use environment like XAMPP at the beginning. Because those will hide the key points from you.

    With Java are you trying to create tables in your SQL database? I wonder what kind of design it was. That kind of design normally need to take on if you want to create tables dynamically. Otherwise you should have a well structured database with required tables. Then basically what you have to do is create/manage a connection to the database from Java application and manipulate data (like insert, delete, update, etc...), dealing with DataTables, DataSet and so on.
    Tolls likes this.

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,920
    Rep Power
    19

    Default Re: Some missing info on how Java and SQL work together

    Essentially what Eranga says.
    If you are trying to learn JDBC then pick a simple database to use (not Access, or a spreadsheet), possibly Derby or HSQL or even MySQL, any of which will involve some effort on your part to get set up...effort that will not be wasted. Then create a simple table with a few rows in it.
    From that point you can then try and get a simple Java program to query that table and print the results.

  4. #4
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: Some missing info on how Java and SQL work together

    mm I keep being a bit of confused. I think I didn't explain myself properly before.

    With Java are you trying to create tables in your SQL database? I wonder what kind of design it was.
    I haven't designed the database, because this isn't for a concrete program. In my free time, I'm developing a library (that of course I'll share as soon as it grows for a little bit) for simplifying some common tasks (not taking into account performance, as I'm just few months experienced with Java and programming), like playing music, handling files, inputs, network issues, etc. So I need the methods to be as generic, flexible and adaptive as possible.
    So, what I was expecting to do was kind of:
    Java Code:
    public static void runCommand(String command, String databaseName (or kind of thing like this)){
    try{
    //Create connection
    //Run command
    } catch (Exception ex){
    e.printStackTrace();
    }
    }
    In respect to what you Tolls recommended me:

    possibly Derby or HSQL or even MySQL,
    I've been investigating a very little bit about HSQL. It is said to be better than MySQL for small applications, which are the target of my library, and found what I think is the program to use it, HyperSQL. So please, tell me if it's I've understood properly and I'm on the right way and then I'll proceed to download and start playing with it.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,920
    Rep Power
    19

    Default Re: Some missing info on how Java and SQL work together

    It's one (like Derby) that is Java based and can be embedded in your app (that is the database is a part of your application).
    It's probably quite a good one to pick.

  6. #6
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: Some missing info on how Java and SQL work together

    Ok I've advanced a little bit further now. I've been reading the chapter dedicated to connections in the guide contained in HyperSQL. Now I think I understand them much better than before. Also I've been playing with the HyperSQL program. But then I've tried to access the database that I had created with HyperSQL from an extremely simple java application:

    Java Code:
    package utilstest;
    
    import java.sql.*;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    /**
     *
     * @author jorge
     */
    public class run {
    
        public static void main(String[] args) {
            try {
                Connection c = DriverManager.getConnection("jdbc:hsqldb:file:testdb", "SA", "");
            } catch (SQLException ex) {
                Logger.getLogger(run.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    But...
    java.sql.SQLException: No suitable driver found for jdbc:hsqldb:file:testdb
    at java.sql.DriverManager.getConnection(DriverManager .java:602)
    at java.sql.DriverManager.getConnection(DriverManager .java:185)
    at utilstest.run.main(run.java:15)

    ???

  7. #7
    Stoyicker is offline Member
    Join Date
    Jan 2012
    Posts
    32
    Rep Power
    0

    Default Re: Some missing info on how Java and SQL work together

    Solved now. I had forgotten to get the driver for the connection to work. For people with the same problem, something like this should be ok:

    Java Code:
    Class.forName("org.hsqldb.jdbc.JDBCDriver");
    By the way, to solve the ClassNotFoundException that will probably be thrown up there, import the hsqldb-2.2.7\hsqldb\lib\hsqldb.jar file into the project.
    Last edited by Stoyicker; 01-22-2012 at 05:13 PM. Reason: Added some more info for other people having similar issues

  8. #8
    HowardHyde is offline Member
    Join Date
    Jan 2012
    Posts
    12
    Rep Power
    0

    Default Re: Some missing info on how Java and SQL work together

    Unless you are writing a database design or administration tool, I'm not sure why you would want to execute DDL statements from a Java client. Would you define Java classes using SQL? (Hey, wait a minute! That's a great idea!)
    You may find the following book helpful for getting Java and SQL to work together: 'Java Web Database Application Development'. See: JavaWebDB.com.

  9. #9
    HowardHyde is offline Member
    Join Date
    Jan 2012
    Posts
    12
    Rep Power
    0

    Default Re: Some missing info on how Java and SQL work together

    Forgot to mention re: Class.forName("org.hsqldb.jdbc.JDBCDriver");
    That statement should no longer be necessary if you are using JDBC version 4 or higher. Just ensure that your driver file is visible to the CLASSPATH.

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,920
    Rep Power
    19

    Default Re: Some missing info on how Java and SQL work together

    In the case of embedded databases it does seem to occasionally be used as a way of setting up the databse.
    Of course the other option is to just supply a skeleton one in your jar file that you then stream to whatever location on the file system, but it's really a case of horses for courses.

  11. #11
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,188
    Rep Power
    19

    Default Re: Some missing info on how Java and SQL work together

    Howard - No more advertising in the technical forums please.

    db
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

Similar Threads

  1. Files missing from Java cache
    By amaduta in forum Advanced Java
    Replies: 0
    Last Post: 10-31-2011, 11:55 AM
  2. missing info
    By java-noob in forum New To Java
    Replies: 2
    Last Post: 04-20-2010, 09:55 AM
  3. java.sql.SQLException: Missing IN or OUT parameter at index:: 1
    By Stephen Douglas in forum New To Java
    Replies: 2
    Last Post: 04-08-2010, 08:45 PM
  4. Need info abt Java certification
    By kavitha2005 in forum New To Java
    Replies: 14
    Last Post: 12-18-2008, 01:28 PM
  5. Replies: 1
    Last Post: 12-15-2008, 08:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •