Some missing info on how Java and SQL work together

[Stoyicker]

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:

    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?

[Eranga]

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]

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.

[Stoyicker]

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:

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.

[Tolls]

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.

[Stoyicker]

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:

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)

???

[Stoyicker]

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:

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.

[HowardHyde]

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.

[HowardHyde]

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.

[Tolls]

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.

[DarrylBurke]

Howard - No more advertising in the technical forums please.

db