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

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

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

/*
TODO:
for the CLI:
- default values
- read in config files
-

 */


/*
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
    // -- need to set the default value, which if we set that then the required = true fields can be removed
    @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")
    int portNo;

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

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

    @Option(names = {"--fileName"}, description = "Name of the db", required = true)
    String fileName;

//-u su41Tipple -p --port 3306 --dbname tippleTest1

    public void run() {
        System.out.println("Hello, " + userName);
        databaseConnect(userName, password, portNo, dbName);
    }

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

/*
Haven't commited to svn yet because
1. I needed to refactor the code so that the password wasn't hardcoded in
2. I want to get a .svnignore file going but literally cannot figure out how

 */


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

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

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

            //create and execute query
            stmt = conn.createStatement();
            String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId=3";
/*
            String sql = "select td_Document.id, td_Document.content " +
                    "from td_Document, td_ContentItem, td_ContentItem_Document " +
                    "where td_ContentItem.projectId=3 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("columns " + dbColumn);

            //List<HashMap<String, Object>> myList = new ArrayList<>();
            Gson myGSon = new Gson();
            file.write("[");

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


                }
                //myList.add(dbRow);
                //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 anObject = myGSon.toJson(dbRow);

                    //printing stuff out
               // System.out.println("As normal: " + dbRow);
               // System.out.println("====");
                //System.out.println("As JSON: " + anObject);


                //saving to json file

                file.write(anObject);
                //file.write(jsonArray.toString());
                if(!myResult.isLast()){
                    file.write(",");
                }
            }
            file.write("]");
            file.flush();
            //System.out.println(myList);
            myResult.close();
            stmt.close();
            conn.close();
        }
        catch(Exception e){
            //catch for class.name
            e.printStackTrace();
        }
    }
}