Derby, SQL and CSV

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;
    }
}