Changeset 34880 for gs2-extensions

Timestamp:

2021-02-16T13:36:22+13:00 (3 years ago)

Author:

davidb

Message:

Early working version that generates json ouput file. Committing at this point to test on a fresh checkout

File:

• gs2-extensions/tipple-bridge/trunk/src/src/main/java/sqlCli.java (modified) (10 diffs)

gs2-extensions/tipple-bridge/trunk/src/src/main/java/sqlCli.java

@@ -3 +3 @@
 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;
 
@@ -15 +12 @@
 TODO:
 for the CLI:
-- default values
-- read in config files
--
+- Error handling
+- Subcommands?
 
  */
 
+//-u su41Tipple -p --port 3306 --dbname tippleTest1
+//@/home/su41/configFileTest
 
 /*
@@ -28 +26 @@
 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;
@@ -51 +49 @@
     String projectID;
 
-    @Option(names = {"--fileName"}, description = "Name of the db", required = true)
+    //defaultValue in case the user doesn't enter in an option for it
+    @Option(names = {"--fileName"}, description = "Name of the db",
+            required = true, defaultValue = "myData.json")
     String fileName;
 
-//-u su41Tipple -p --port 3306 --dbname tippleTest1
+    @Option(names = {"--host"}, description = "Type of host e.g. localhost ",
+            required = true, defaultValue = "localhost")
+    String host;
+
 
     public void run() {
         System.out.println("Hello, " + userName);
-        databaseConnect(userName, password, portNo, dbName);
+        databaseConnect(userName, password, portNo, dbName, projectID, fileName, host);
     }
 
@@ -66 +69 @@
     }
 
-/*
-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){
+    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;
+       // String dbUrl = "jdbc:mysql://localhost:" + port +"/"+ dbName;
 
-        //rewrite
-        try (FileWriter file = new FileWriter("tippleData.json")){
+        //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")
+        try (FileWriter file = new FileWriter(filename)){
             //register the jdbc driver
             Class.forName("com.mysql.cj.jdbc.Driver");
@@ -91 +92 @@
             //create and execute query
             stmt = conn.createStatement();
-            String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId=3";
+            //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=3 and td_ContentItem.id = td_ContentItem_Document.ContentItemId " +
+                    "where td_ContentItem.projectId=" + projectID + " and td_ContentItem.id =
+                    td_ContentItem_Document.ContentItemId " +
                     "and td_ContentItem_Document.documentId = td_Document.id;";
 */
@@ -104 +107 @@
             System.out.println("columns " + dbColumn);
 
-            //List<HashMap<String, Object>> myList = new ArrayList<>();
             Gson myGSon = new Gson();
-            file.write("[");
+           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);
@@ -134 +140 @@
 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));
@@ -147 +150 @@
                        // 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);
+               String dbRowAsJson = myGSon.toJson(dbRow);
 
-                    //printing stuff out
+                    //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(anObject);
+                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();
@@ -181 +184 @@
             //catch for class.name
             e.printStackTrace();
+            //print out wrong username or password instead of the stack trace?
+            System.out.println("encountered error");
         }
     }