source: gs2-extensions/tipple-bridge/trunk/src/src/main/java/sqlCli.java@ 34889

import com.google.gson.JsonArray;
import picocli.CommandLine;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;

import java.sql.*;
import java.util.HashMap;
import com.google.gson.Gson;
import java.io.FileWriter;

/*
TODO:
for the CLI:
- Error handling
- Subcommands?

 */

//-u su41Tipple -p --port 3306 --dbname tippleTest1
//@/home/su41/configFileTest

/*
The mixinStandardHelpOptions = true adds in --help and --version options
 */
@Command (name="sqlCli", description = "Arguments for database connection", mixinStandardHelpOptions = true)
public class sqlCli implements Runnable {
    //our cli arguments

    @Option(names = {"-u", "--user"}, description = "The user name")
    String userName;

    //interactive allows the user to omit the argument value and instead enter it as a prompt
    /*
    NOTE: if you are running an interactive option on an IDE, most likely will be using something
    like the javaw.exe library which tries to mimic the terminal.
    The point is that interactive option would usually make whatever you enter hidden (it doesn't
    echo back) but on the IDE it will echo back.
     */
    @Option(names = {"-p", "--password"}, description = "User's password", interactive = true)
    String password;

    @Option(names = {"--port"},  description="Port number", defaultValue="3306")
    int portNo;

    @Option(names = {"--dbname"}, description="Name of the database", defaultValue="koicarp_test")
    String dbName;

    @Option(names = {"--projectID"}, description = "ID number of the project")
    String projectID;

    //defaultValue in case the user doesn't enter in an option for it
    @Option(names = {"--fileName"}, description = "Name of output JSON file",
            required = true, defaultValue = "myData.json")
    String fileName;

    @Option(names = {"--host"}, description = "Type of host e.g. localhost ",
            required = true, defaultValue = "localhost")
    String host;


    public void run() {
        databaseConnect(userName, password, portNo, dbName, projectID, fileName, host);
    }

    public static void main(String... args) {
        int exitCode = new CommandLine(new sqlCli()).execute(args);
        System.exit(exitCode);
    }


    private static void databaseConnect(String user, String pass, int port, String dbName,
                                        String projectID, String filename, String host){
        Connection conn;
        Statement stmt;

       // String dbUrl = "jdbc:mysql://localhost:" + port +"/"+ dbName;

        //TODO: are we changing / putting the type of database into a variable too?
        String dbUrl = "jdbc:mysql://" + host + ":" + port +"/"+ dbName;

        //originally in the try ()
        // FileWriter file = new FileWriter("tippleData.json")
    System.out.println("Saving output to:");
    System.out.println("  " + filename);
    
        try (FileWriter file = new FileWriter(filename)){
            //register the jdbc driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            //opening the connection
            System.out.println("Connecting to database:");
        System.out.println("  " + dbUrl);
        
            conn = DriverManager.getConnection(dbUrl, user, pass);

            //create and execute query
            stmt = conn.createStatement();
            //remember to put in the projectId parameter instead of hardcoding it
            String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId="+projectID;
/*
            String sql = "select td_Document.id, td_Document.content " +
                    "from td_Document, td_ContentItem, td_ContentItem_Document " +
                    "where td_ContentItem.projectId=" + projectID + " and td_ContentItem.id =
                    td_ContentItem_Document.ContentItemId " +
                    "and td_ContentItem_Document.documentId = td_Document.id;";
*/
            ResultSet myResult = stmt.executeQuery(sql);
            ResultSetMetaData resultMetaData = myResult.getMetaData();

            int dbColumn = resultMetaData.getColumnCount();
            System.out.println("  Number of columns in table retrieved: " + dbColumn);

            Gson myGSon = new Gson();
        file.write("[");

            //does nothing, assuming the try catch picks up the error first,
            if(!myResult.next()){
                System.out.println("Access denied");
            }

            while(myResult.next()){
                //string/object as key/value pair
                HashMap<String, Object> dbRow = new HashMap<String, Object>(dbColumn);

                //maybe use some kind of arrayList?
                // ArrayList<Map<String, Object>> dbRowArray = new ArrayList<>(dbColumn);

                for(int i = 1; i <= dbColumn; i++){
                    /*
                    doing checking this way would mean we have to have a case
                    for every data type,
                    if a data type changes in the database that's probably fine, but
                    if a new data type is sent in, the code may not handle it
                     */
                    /*
                    Without this kind of check though, the output would throw a pretty nasty
                    warning about illegal reflective access. Though it would still print the
                    date out fine. Think it switched to using some other java option?

                    WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.google.gson.internal.bind.ReflectiveTypeAdapterFactory (file:/home/su41/.gradle/caches/modules-2/files-2.1/com.google.code.gson/gson/2.7/751f548c85fa49f330cecbb1875893f971b33c4e/gson-2.7.jar) to field java.time.LocalDateTime.date
WARNING: Please consider reporting this to the maintainers of com.google.gson.internal.bind.ReflectiveTypeAdapterFactory
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
                     */
                    if(resultMetaData.getColumnTypeName(i).equals("DATETIME")){
                        dbRow.put(resultMetaData.getColumnName(i), myResult.getDate(i));
                        //System.out.println("has a datetime");

                    }

                    else{
                        dbRow.put(resultMetaData.getColumnName(i), myResult.getObject(i));
                       // System.out.println("no datetime datetime");
                    }
                }
                //JsonObject jsonObject = myGSon.toJsonTree(dbRow).getAsJsonObject();

                // using a hashmap wont work with this as its not an array
               // JsonArray jsonArray = myGSon.toJsonTree(dbRow).getAsJsonArray();
               String dbRowAsJson = myGSon.toJson(dbRow);

                    //printing statements for checking output
               // System.out.println("As normal: " + dbRow);
               // System.out.println("====");
                //System.out.println("As JSON: " + anObject);

                //saving to json file

                file.write(dbRowAsJson);
                //file.write(jsonArray.toString());
                //Adding commas between each row that we write to our file, as we need it to be valid json
                if(!myResult.isLast()){
                    file.write(",");
                }
            }
            //write the closing bracket, and flush the writer
            file.write("]");
            file.flush();
            file.close();
            //System.out.println(myList);
            System.out.println("Closing connection...");
            myResult.close();
            stmt.close();
            conn.close();
        }
        catch(Exception e){
            //catch for class.name
            e.printStackTrace();
            //print out wrong username or password instead of the stack trace?
            System.out.println("encountered error");
        }
    }
}