Derby, SQL and CSV

Bear in mind that this is a pretty greenhorn-question, so please, be patient with me.

So as a person who knows little to nothing about databases, undesirable conditions have lead me into a situation where I am to perform a task I know little about how to do.

In my hour of need, I come to you guys/gals.

Firstly, my task requires me to use Apache Derby to achieve the desired goals alongside Eclipse.

I've been given a single database located at res/myCSVFile.csv.

The text file with the CSV extension looks like this:

letter,name
A,alpha-static
B,beta-static
C,charlie-static

Doesn't look too threatening, right?

I know I can build a nice HashMap out of my CSV file like this:

Map<String, myFileType> myHashMap = new HashMap<>();
try(CSVReader rdr = new CSVReader(new FileReader("res/myCSVFile.csv"), ',','"',1)){
    for(String[] row : rdr.readAll()){
        File imagefile = new File(row[1]);
        myHashMap.put(row[0], new myFileType(imagefile, Integer.parseInt(row[2])));
    }
}

I know CSV separates columns by commas, so I assume what I see in this file are two columns, one labeled "letter" with the values "A", "B" and "C" under it, and another labeled "name" with "alpha-static", "beta-static" and "charlie-static" under it.

My situation requires me to use Derby to manipulate the above data as an SQL database. Knowing full well that a CSV file looks nothing like a SQL file, I know there is some conversion involved.

I don't know if Java can (or should) do this for me somehow, but I assume not.

Now, I do presume the SQL variant of my CSV file would look something like:

DROP TABLE possibleSqlTable;
CREATE TABLE possibleSqlTable(
  letter VARCHAR(1) NOT NULL PRIMARY KEY
, name VARCHAR(14)
);
INSERT INTO possibleSqlTable(letter,name) VALUES ('A','alpha-static');
INSERT INTO possibleSqlTable(letter,name) VALUES ('B','beta-static');
INSERT INTO possibleSqlTable(letter,name) VALUES ('C','charlie-static');

That's just my guess in the dark at how this SQL database would look (correct me if I'm wrong). Assume that's saved as res/possibleSqlDatabase.sql.

Now, how do I get Derby in on the action?

Assuming my SQL database is correct as I've guessed above, how do I for example query a statement?

How would I, for example, map "A" to "alpha-static"? Can I do this with a simple HashMap? I'd love to see how, if that'd be possible.

I do have a basic, VERY basic, understanding of how SQL-commands work, but I would love some examples using my database.

When answering, just treat me like a preschooler trying to learn this stuff. I really am that new.

Solution

Here is an example.

In this case I create a local Database /tmp/test. Attention: If you want to use Derby as a service, you have to change the URL in getConnection() and start the database by yourself. You also can change the filename of the database at the same position.

The example can read the whole data with getData() or a single value with getOneName(String).

In the constructor we create and fill the database (if necessary), read the whole data, read a single letter and read a non exististing letter.

Hope that helps

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;


public class TestApp {


    public static void main(String[] args) throws SQLException {
        TestApp myTestApp = new TestApp();
    }

    public TestApp() throws SQLException {
        this.createAndFillDatabase();
        Map<String, String> allData = this.getData();
        String oneData = this.getOneName("A");
        String noData = this.getOneName("D");
        System.out.println("allData = " + allData);
        System.out.println("oData   = " + oneData);
        System.out.println("noData  = " + noData);
    }

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:derby:/tmp/test;create=true");
    }

    private void createAndFillDatabase() throws SQLException {
        try (Connection conn = getConnection()) {
            ResultSet tables = conn.getMetaData().getTables(null, null, "POSSIBLESQLTABLE", null);
            if (!tables.next()) {
                // create table
                Statement stmt = conn.createStatement();
                stmt.execute("CREATE TABLE possibleSqlTable(\n"
                        + "  letter VARCHAR(1) NOT NULL PRIMARY KEY\n"
                        + ", name VARCHAR(14)\n"
                        + ")");
                // insert data
                String[][] data = {{"A", "alpha-static"}, {"B", "beta-static"}, {"C", "charlie-static"}};
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO possibleSqlTable(letter,name) VALUES (?,?)");
                for (String[] row : data) {
                    pstmt.setString(1, row[0]);
                    pstmt.setString(2, row[1]);
                    pstmt.execute();
                }
                conn.commit();
            }
        }
    }

    private Map<String, String> getData() throws SQLException {
        Map<String, String> result = new HashMap<>();
        try (Connection conn = getConnection()) {
            Statement stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select * from possibleSqlTable");
            while (results.next()) {
                result.put(results.getString("letter"), results.getString("name"));
            }
        }
        return result;
    }

    private String getOneName(String letter) throws SQLException {
        String result = null;
        try (Connection conn = getConnection()) {
            PreparedStatement pstmt = conn.prepareStatement("select * from possibleSqlTable where letter = ?");
            pstmt.setString(1, letter);
            ResultSet results = pstmt.executeQuery();
            while (results.next()) {
                result = results.getString("name");
            }
        }
        return result;
    }
}