How can I make my java code compatible with all the tables in database from which I need to extract the data and then writing it back to Excel file

Question

There is a database (northwind) on my machine and I have to write a code in java so as to extract the data from the table (Customers) stored in the database.

If this was only specific to Customers table then I would have done it but I want to make my code generic so that I can extract data from other tables also by simply giving the name of the table in a string variable.

Please have a look to my code.

Main class

package main;

import java.io.File;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import model.TableModel;
import service.DBConnection;
import service.WriteExcel;

public class Main {

public static void main(String[] args) throws SQLException, ClassNotFoundException {

    double start = System.nanoTime();
    String tableName = "Customers";
    Class<?> c = Class.forName(tableName);
    Connection conn = new DBConnection().getConnection();
    System.out.println("Connection Established");

    QueryRunner run = new QueryRunner();
    ResultSetHandler<List<TableModel>> resultHandler = new BeanListHandler<TableModel>(c.getClass())
    List<TableModel> data = run.query(conn, "SELECT * FROM `" + tableName + "`;",
            resultHandler);

    WriteExcel we = new WriteExcel(tableName+"_sheet", new File(tableName+".xlsx"));
    we.writeMultipleRows(data);
    we.writeWorkbookToFile();
    System.out.println("File Written Succesfully");
    conn.close();

    System.out.println("Time Taken: " + (System.nanoTime()-start)/1000000+" ms");

}

}

In the above code, at line 27, If the statement would have been as follows

ResultSetHandler<List<TableModel>> resultHandler = new BeanListHandler<TableModel>(Customers.class);

This is running perfectly, as I said I want this statement to be independent of the table name, making my code more general.

TableModel

package model;

import java.util.List;

public interface TableModel {

public List<String> getObjectAsList();

}

Customers

package model;

import java.util.ArrayList;
import java.util.List;

public class Customers implements TableModel {

private String customerId;
private String companyName;
private String contactName;
private String contactTitle;
private String address;
private String city;
private String region;
private String postalCode;
private String country;
private String phone;
private String fax;


public String getCustomerId() {
    return customerId;
}

public void setCustomerId(String customerId) {
    this.customerId = customerId;
}

public String getCompanyName() {
    return companyName;
}

public void setCompanyName(String companyName) {
    this.companyName = companyName;
}

public String getContactName() {
    return contactName;
}

public void setContactName(String contactName) {
    this.contactName = contactName;
}

public String getContactTitle() {
    return contactTitle;
}

public void setContactTitle(String contactTitle) {
    this.contactTitle = contactTitle;
}

public String getAddress() {
    return address;
}

public void setAddress(String address) {
    this.address = address;
}

public String getCity() {
    return city;
}

public void setCity(String city) {
    this.city = city;
}

public String getRegion() {
    return region;
}

public void setRegion(String region) {
    this.region = region;
}

public String getPostalCode() {
    return postalCode;
}

public void setPostalCode(String postalCode) {
    this.postalCode = postalCode;
}

public String getCountry() {
    return country;
}

public void setCountry(String country) {
    this.country = country;
}

public String getPhone() {
    return phone;
}

public void setPhone(String phone) {
    this.phone = phone;
}

public String getFax() {
    return fax;
}

public void setFax(String fax) {
    this.fax = fax;
}

public List<String> getObjectAsList(){
    List<String> fields = new ArrayList<>();
    fields.add(customerId);
    fields.add(companyName);
    fields.add(contactName);
    fields.add(contactTitle);
    fields.add(address);
    fields.add(city);
    fields.add(region);
    fields.add(postalCode);
    fields.add(country);
    fields.add(phone);
    fields.add(fax);
    return fields;
}

@Override
public String toString() {
    return "{ CustomerID = "+getCustomerId()+","
            + " CompanyName = "+getCompanyName()+","
            + " ContactName = "+getContactName()+","
            + " ContactTitle = "+getContactTitle()+","
            + " Address = "+getAddress()+","
            + " City = "+getCity()+","
            + " Region = "+getRegion()+","
            + " PostalCode = "+getPostalCode()+","
            + " Country = "+getCountry()+","
            + " Phone = "+getPhone()+","
            + " Fax = "+getFax()+"}";
}

}

I have used DbUtils library for extracting database. Any further suggestion for enhancing my code is welcomed.

Answer 1

If I understand your question right, you could try something like below.

To query the table, you can use run.query(SQL, ResultHandler).

    ResultSetHandler<List<Map<String, Object>>> resultHandler = genericResultHandler();

    List<Map<String, Object>> result = null;

    // Execute the SQL statement and return the results in a List of
    // T objects generated by the BeanListHandler.
    try
    {
        result = run.query(sqlQuery, resultHandler, varargs);
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }

    result.stream().forEach(System.out::println);

The interesting part here is the private method genericResultHandler. For demonstration purposes, I used a HashMap to store the values and the corresponding cloumn names.

private ResultSetHandler<List<Map<String, Object>>> genericResultHandler()
{
    return new ResultSetHandler<List<Map<String, Object>>>()
    {
        @Override
        public List<Map<String, Object>> handle(java.sql.ResultSet rs) throws SQLException
        {
            List<Map<String, Object>> result = new ArrayList<>();

            // Query all rows of the table.
            while (rs.next())
            {
                // Get metadata of the table.
                java.sql.ResultSetMetaData meta = rs.getMetaData();
                int cols = meta.getColumnCount();
                Map<String, Object> data = new HashMap<>();

                // For each column store column name and value of the cell into the hashmap.
                for (int i = 1; i < cols; i++)
                {
                    String colName = meta.getColumnName(i);
                    Object value = rs.getObject(colName);
                    data.put(colName, value);
                }

                // Add the row to the result list. 
                result.add(data);
            }

            return result;
        }
    };
}

Afterwards some imports I have used:

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

Output would be something like this (for my test table):

{month=JANUARY, temperature=1.6, globalradiation=0.0, monthid=1}
{month=FEBRUARY, temperature=-0.9, globalradiation=0.0, monthid=2}
{month=MARCH, temperature=0.9, globalradiation=0.0, monthid=3}
{month=APRIL, temperature=7.2, globalradiation=0.0, monthid=4}
{month=MAY, temperature=14.1, globalradiation=0.0, monthid=5}