Changeset 34880
- Timestamp:
- 2021-02-16T13:36:22+13:00 (3 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
gs2-extensions/tipple-bridge/trunk/src/src/main/java/sqlCli.java
r34830 r34880 3 3 import picocli.CommandLine.Command; 4 4 import picocli.CommandLine.Option; 5 import picocli.CommandLine.Parameters;6 5 7 import java.io.File;8 6 import java.sql.*; 9 7 import java.util.HashMap; 10 8 import com.google.gson.Gson; 11 import com.google.gson.JsonObject;12 9 import java.io.FileWriter; 13 10 … … 15 12 TODO: 16 13 for the CLI: 17 - default values 18 - read in config files 19 - 14 - Error handling 15 - Subcommands? 20 16 21 17 */ 22 18 19 //-u su41Tipple -p --port 3306 --dbname tippleTest1 20 //@/home/su41/configFileTest 23 21 24 22 /* … … 28 26 public class sqlCli implements Runnable { 29 27 //our cli arguments 30 // -- need to set the default value, which if we set that then the required = true fields can be removed 28 31 29 @Option(names = {"-u", "--user"}, description = "The user name") 32 30 String userName; … … 51 49 String projectID; 52 50 53 @Option(names = {"--fileName"}, description = "Name of the db", required = true) 51 //defaultValue in case the user doesn't enter in an option for it 52 @Option(names = {"--fileName"}, description = "Name of the db", 53 required = true, defaultValue = "myData.json") 54 54 String fileName; 55 55 56 //-u su41Tipple -p --port 3306 --dbname tippleTest1 56 @Option(names = {"--host"}, description = "Type of host e.g. localhost ", 57 required = true, defaultValue = "localhost") 58 String host; 59 57 60 58 61 public void run() { 59 62 System.out.println("Hello, " + userName); 60 databaseConnect(userName, password, portNo, dbName );63 databaseConnect(userName, password, portNo, dbName, projectID, fileName, host); 61 64 } 62 65 … … 66 69 } 67 70 68 /*69 Haven't commited to svn yet because70 1. I needed to refactor the code so that the password wasn't hardcoded in71 2. I want to get a .svnignore file going but literally cannot figure out how72 71 73 */ 74 75 76 private static void databaseConnect(String user, String pass, int port, String dbName){ 72 private static void databaseConnect(String user, String pass, int port, String dbName, 73 String projectID, String filename, String host){ 77 74 Connection conn; 78 75 Statement stmt; 79 76 80 String dbUrl = "jdbc:mysql://localhost:" + port +"/"+ dbName;77 // String dbUrl = "jdbc:mysql://localhost:" + port +"/"+ dbName; 81 78 82 //rewrite 83 try (FileWriter file = new FileWriter("tippleData.json")){ 79 //TODO: are we changing / putting the type of database into a variable too? 80 String dbUrl = "jdbc:mysql://" + host + ":" + port +"/"+ dbName; 81 82 //originally in the try () 83 // FileWriter file = new FileWriter("tippleData.json") 84 try (FileWriter file = new FileWriter(filename)){ 84 85 //register the jdbc driver 85 86 Class.forName("com.mysql.cj.jdbc.Driver"); … … 91 92 //create and execute query 92 93 stmt = conn.createStatement(); 93 String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId=3"; 94 //remember to put in the projectId parameter instead of hardcoding it 95 String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId="+projectID; 94 96 /* 95 97 String sql = "select td_Document.id, td_Document.content " + 96 98 "from td_Document, td_ContentItem, td_ContentItem_Document " + 97 "where td_ContentItem.projectId=3 and td_ContentItem.id = td_ContentItem_Document.ContentItemId " + 99 "where td_ContentItem.projectId=" + projectID + " and td_ContentItem.id = 100 td_ContentItem_Document.ContentItemId " + 98 101 "and td_ContentItem_Document.documentId = td_Document.id;"; 99 102 */ … … 104 107 System.out.println("columns " + dbColumn); 105 108 106 //List<HashMap<String, Object>> myList = new ArrayList<>();107 109 Gson myGSon = new Gson(); 108 file.write("["); 110 file.write("["); 111 112 //does nothing, assuming the try catch picks up the error first, 113 if(!myResult.next()){ 114 System.out.println("Access denied"); 115 } 109 116 110 117 while(myResult.next()){ 111 112 118 //string/object as key/value pair 113 119 HashMap<String, Object> dbRow = new HashMap<String, Object>(dbColumn); … … 134 140 WARNING: All illegal access operations will be denied in a future release 135 141 */ 136 137 138 139 142 if(resultMetaData.getColumnTypeName(i).equals("DATETIME")){ 140 143 dbRow.put(resultMetaData.getColumnName(i), myResult.getDate(i)); … … 147 150 // System.out.println("no datetime datetime"); 148 151 } 149 150 151 152 } 152 //myList.add(dbRow);153 153 //JsonObject jsonObject = myGSon.toJsonTree(dbRow).getAsJsonObject(); 154 154 155 155 // using a hashmap wont work with this as its not an array 156 156 // JsonArray jsonArray = myGSon.toJsonTree(dbRow).getAsJsonArray(); 157 String anObject= myGSon.toJson(dbRow);157 String dbRowAsJson = myGSon.toJson(dbRow); 158 158 159 //printing st uff out159 //printing statements for checking output 160 160 // System.out.println("As normal: " + dbRow); 161 161 // System.out.println("===="); 162 162 //System.out.println("As JSON: " + anObject); 163 163 164 165 164 //saving to json file 166 165 167 file.write( anObject);166 file.write(dbRowAsJson); 168 167 //file.write(jsonArray.toString()); 168 //Adding commas between each row that we write to our file, as we need it to be valid json 169 169 if(!myResult.isLast()){ 170 170 file.write(","); 171 171 } 172 172 } 173 //write the closing bracket, and flush the writer 173 174 file.write("]"); 174 175 file.flush(); 176 file.close(); 175 177 //System.out.println(myList); 178 System.out.println("Closing connection..."); 176 179 myResult.close(); 177 180 stmt.close(); … … 181 184 //catch for class.name 182 185 e.printStackTrace(); 186 //print out wrong username or password instead of the stack trace? 187 System.out.println("encountered error"); 183 188 } 184 189 }
Note:
See TracChangeset
for help on using the changeset viewer.