How to create a h2 database from some fields of a CSV file

Question

i would like to create a new embedded h2 database from a CSV file. Here is the snippet of the csv file

Country,City,AccentCity,Region,Population,Latitude,Longitude

ad,aixovall,Aixovall,06,,42.4666667,1.4833333

ad,andorra,Andorra,07,,42.5,1.5166667

ad,andorra la vella,Andorra la Vella,07,20430,42.5,1.5166667

ad,andorra-vieille,Andorra-Vieille,07,,42.5,1.5166667

ad,andorre,Andorre,07,,42.5,1.5166667

I don't want to retrieve all the fields of the csv file. Actually, i want them all except the City and Region fields.

And further, i want to insert the content of the csv file into the database ONLY IF the content of POPULATION is not empty.

Thus, in the csv example above, we must only insert the 3rd row into the h2 table WORLDCITIES because its 'population' field is indicated.

Here is a snippet of code i wrote. But, as you can see, it is not enough yet:

conn = DriverManager.getConnection(connectionURL, connectionProps);
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE WORLDCITIES"
        + " AS SELECT COUNTRY, ACCENTCITY, POPULATION, LATITUDE, LONGITUDE"
        + " FROM CSVREAD('snippet.csv'));

And if i understand correctly, CSVREAD create the fields using the VARCHAR type, but i want the things like this:

COUNTRY VARCHAR(3), ACCENTCITY VARCHAR(40), POPULATION FLOAT, LATITUDE FLOAT, LONGITUDE FLOAT

Thanks in advance for helping.

Answer 1

You can add the column definitions in the CREATE TABLE as documented and combine this with a WHERE clause. Please note using CREATE TABLE AS SELECT is a bit faster than separate CREATE TABLE and INSERT INTO statements (not sure if speed is very important for you):

CREATE TABLE WORLDCITIES(
  COUNTRY VARCHAR(3), 
  ACCENTCITY VARCHAR(40), 
  POPULATION FLOAT, 
  LATITUDE FLOAT, 
  LONGITUDE FLOAT)
AS SELECT 
  COUNTRY, 
  ACCENTCITY, 
  POPULATION, 
  LATITUDE, 
  LONGITUDE
FROM CSVREAD('snippet.csv') 
WHERE POPULATION IS NOT NULL;

Answer 2

finally, i proceeded like this as you adviced. I only put the parts of the code which i think is more related to the question :)

`

private final String createTableString = ""
    + "CREATE TABLE IF NOT EXISTS " + _tableName
    + " ("
    + "id INT UNSIGNED NOT NULL AUTO_INCREMENT, "
    + "country VARCHAR(3) NOT NULL, "
    + "city VARCHAR(40) NOT NULL, "
    + "region VARCHAR (5) NOT NULL, "
    + "population FLOAT NOT NULL, "
    + "latitude FLOAT NOT NULL, "
    + "longitude FLOAT NOT NULL, "
    + "PRIMARY KEY(id)"
    + " );";

private final String insertString = ""
    + "INSERT INTO " + _tableName
    + " (country, city, region, population, latitude, longitude) "
    + "VALUES (?,?,?,?,?,?)"
    + ";";

public void go() throws IOException, SQLException {

    loadDriver();
    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put("user", "");
    connectionProps.put("password", "");
    String connectionURL = _protocol + _subprotocol + _dbName + _dbSettings;
    ResultSet rs = null;

    try {
        conn = DriverManager.getConnection(connectionURL, connectionProps);
        logger.info("Connected to {} database.", _dbName);

        conn.setAutoCommit(false);
        Savepoint savept1 = conn.setSavepoint();

        Statement stmt = conn.createStatement();
        try {
            stmt.execute(createTableString);
            logger.info("The table '{}' created successfully", _tableName);
        } catch (SQLException sqle) {
            logger.error("Error while creating the table '{}'", _tableName);
            printSQLException(sqle);
        }

        PreparedStatement pstmt = conn.prepareStatement(insertString);
        _allStatements.add(pstmt);
        /* rs:                           pstmt:
         * 1 -> COUNTRY                  
         * 2 -> CITY                     1 -> COUNTRY
         * 3 -> ACCENTCITY               2 -> CITY
         * 4 -> REGION                   3 -> REGION
         * 5 -> POPULATION               4 -> POPULATION
         * 6 -> LATITUDE                 5 -> LATITUDE
         * 7 -> LONGITUDE                6 -> LONGITUDE
         */
        rs = Csv.getInstance().read(_csvFileName, null, _csvCharset);
        int rowCount = 0;
        while (rs.next()) {
            if (rs.getFloat(5) != 0) { // If population is not null.
                pstmt.setString(1, rs.getString(1)); // country
                pstmt.setString(2, rs.getString(3)); // city (accentcity in reality)
                pstmt.setString(3, rs.getString(4)); // region
                pstmt.setFloat(4, rs.getFloat(5));   // population
                pstmt.setFloat(5, rs.getFloat(6));   // latitude
                pstmt.setFloat(6, rs.getFloat(7));   // longitude
                pstmt.addBatch();
                rowCount ++;
            }
        }
        int[] rowsUpdated = pstmt.executeBatch();
        for (int i=0; i<rowsUpdated.length; i++) {
            if (rowsUpdated[i] == -2) {
                logger.error("Execution {}: unknown number of rows inserted.", i);
                logger.error("Rolling back ...");
                conn.rollback(savept1);
            } else {
                logger.trace("Successful: execution {}, {} rows updated !", i, rowsUpdated[i]);
            }
        }
        conn.commit();
    }
    finally { // release all open resources to avoid unnecessary memory usage.

....`

Thanks!