Java tutorial
/* * Copyright (C) 2014 Artitelly Solutions Inc, www.CloudTestSoftware.com * * Licensed under the Common Development and Distribution License (CDDL-1.0) (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://opensource.org/licenses/CDDL-1.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.testmax.util; import java.io.File; import java.io.FileInputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Properties; import org.dom4j.Document; import org.dom4j.DocumentException; import org.dom4j.Element; import org.dom4j.io.SAXReader; import com.testmax.framework.*; public class CreateActionSpec { private static HashMap<String, HashMap<String, Element>> dbEnvMap = null; private static Properties configProp = null; private static String dbname = null; private static String pacakage = null; private static String procedure = null; public static void main(String[] args) throws Exception { int fcount = 0; String apiPrifix = null; String xml = "\n"; String plparm = "\n"; String plvar = "\n"; String dbms_cols = ""; String dbms_out = ""; int plcount = 0; if (args.length != 4) { System.out.println("Please pass below env parameters"); System.out.println("[dbname] is the tag defined in your db_<env>.xml under config folder"); System.out.println("[pacakage],[Oracle Package Name][Oracle API Name within Package][API Alias Name]"); System.out.println("[API Name],[Oracle Package Name][Oracle API Name within Package][API Alias Name]"); System.out.println( "[Prefix] is a short logical name to prefix with the API parameter i.e. a1 or api1 etc."); System.out.println("Example: inventory win_put_pkg get_pickup_rng pt"); return; } else { dbname = args[0].toUpperCase(); pacakage = args[1].toUpperCase(); procedure = args[2].toUpperCase(); apiPrifix = args[3].toUpperCase(); } dbEnvMap = new HashMap<String, HashMap<String, Element>>(); configProp = new Properties(); configProp.load(new FileInputStream( "." + "/data" + File.separator + "config" + File.separator + "/config.properties")); parseDbDataFiles(); getDatabaseEnv(dbname); Connection conn = getOracleConnection(); DatabaseMetaData dbMetaData = conn.getMetaData(); ResultSet rs = dbMetaData.getProcedureColumns(pacakage, null, //pass schema name procedure, null); while (rs.next()) { // get stored procedure metadata String procedureCatalog = rs.getString(1); String procedureSchema = rs.getString(2); String procedureName = rs.getString(3); String columnName = rs.getString(4); short columnReturn = rs.getShort(5); int columnDataType = rs.getInt(6); String columnReturnTypeName = rs.getString(7); int columnPrecision = rs.getInt(8); int columnByteLength = rs.getInt(9); short columnScale = rs.getShort(10); short columnRadix = rs.getShort(11); short columnNullable = rs.getShort(12); String columnRemarks = rs.getString(13); /*System.out.println("stored Procedure name="+procedureName); System.out.println("procedureCatalog=" + procedureCatalog); System.out.println("procedureSchema=" + procedureSchema); System.out.println("procedureName=" + procedureName); */ //System.out.println("columnName=" + columnName); //System.out.println("columnReturnTypeName=" + columnReturnTypeName); /*System.out.println("columnReturn=" + columnReturn); System.out.println("columnDataType=" + columnDataType); System.out.println("columnReturnTypeName=" + columnReturnTypeName); System.out.println("columnPrecision=" + columnPrecision); System.out.println("columnByteLength=" + columnByteLength); System.out.println("columnScale=" + columnScale); System.out.println("columnRadix=" + columnRadix); System.out.println("columnNullable=" + columnNullable); System.out.println("columnRemarks=" + columnRemarks); */ String descriptor = null; if (getColumnDatatype(columnReturnTypeName).equals("table")) { descriptor = " type=\"table\" descriptor=\"\" arraydescriptor=\"\""; } else { descriptor = " descriptor=\"" + getColumnDatatype(columnReturnTypeName) + "\""; } xml = xml + "\t<" + getColumnReturn(columnReturn) + " name=\"" + apiPrifix.toLowerCase() + "_" + columnName.toLowerCase() + "\"" + descriptor + ">NULL</" + getColumnReturn(columnReturn) + ">\n"; if (getColumnReturn(columnReturn).toLowerCase().contains("output")) { if (dbms_cols == "") { dbms_cols = "'" + columnName.toLowerCase() + "'"; } else { dbms_cols += "||','||'" + columnName.toLowerCase() + "'"; } if (dbms_out == "") { dbms_out = columnName.toLowerCase(); } else { dbms_out += "||','||" + columnName.toLowerCase(); } } fcount++; //PLSQL String colDataType = getColumnDatatype(columnReturnTypeName); if (colDataType.equalsIgnoreCase("VARCHAR")) { colDataType = colDataType + "(200)" + ":=NULL;\n"; } else if (colDataType.equalsIgnoreCase("CURSOR")) { colDataType = "SYS_REFCURSOR;\n"; } else { colDataType = colDataType + ":=NULL;\n"; } plvar = plvar + "\t" + columnName.toLowerCase() + " " + colDataType; plparm = plparm + (plcount != 0 && plcount < fcount ? ",\n" : "") + "\t\t" + columnName.toLowerCase(); plcount++; } xml = "<api name=\"" + pacakage.toLowerCase() + "." + procedure.toLowerCase() + "\" dbName=\"" + dbname.toLowerCase() + "\" fields=\"" + fcount + "\">" + xml + "</api>"; System.out.println(xml); dbms_cols = "\n\tdbms_output.put_line(" + dbms_cols + ");"; dbms_out = "\n\tdbms_output.put_line(" + dbms_out + ");"; System.out.println("\n\ndeclare\n" + plvar + "\nbegin\t\n" + pacakage.toLowerCase() + "." + procedure.toLowerCase() + "(" + plparm + ");" + dbms_cols + dbms_out + " \nend;"); rs.close(); conn.close(); } private static String getColumnReturn(short columnReturn) { switch (columnReturn) { case DatabaseMetaData.procedureColumnIn: return ("input"); case DatabaseMetaData.procedureColumnOut: return ("output"); case DatabaseMetaData.procedureColumnInOut: return ("inoutput"); case DatabaseMetaData.procedureColumnReturn: return ("return"); case DatabaseMetaData.procedureColumnResult: return ("resultset"); default: return ""; } } private static String getColumnDatatype(String columnReturnTypeName) { String datatype = null; if (columnReturnTypeName.contains("VARCHAR")) { datatype = "VARCHAR"; } else if (columnReturnTypeName.contains("DATE")) { datatype = "DATE"; } else if (columnReturnTypeName.contains("NUMBER")) { datatype = "NUMBER"; } else if (columnReturnTypeName.contains("CURSOR")) { datatype = "CURSOR"; } else if (columnReturnTypeName.contains("INTEGER")) { datatype = "NUMBER"; } else if (columnReturnTypeName.contains("FLOAT")) { datatype = "NUMBER"; } else if (columnReturnTypeName.contains("DOUBLE")) { datatype = "NUMBER"; } else if (columnReturnTypeName.contains("TABLE")) { datatype = "table"; } return (datatype); } public static Connection getOracleConnection() throws Exception { String port = "1521"; String username = "Inventory"; String password = "inve3e"; String rac = "sdc-scan-r1"; String service = "wminve3_be.walmart.com"; Element dbEnv = null; if (!dbname.isEmpty()) { dbEnv = getDatabaseEnv(dbname.toLowerCase()); if (dbEnv != null) { List<Element> elmlist = dbEnv.elements(); for (Element elm : elmlist) { if (elm.getName().equalsIgnoreCase("user")) { username = elm.getText(); } else if (elm.getName().equalsIgnoreCase("pwd")) { password = elm.getText(); } else if (elm.getName().equalsIgnoreCase("host") && !elm.getText().isEmpty()) { rac = elm.getText(); } else if (elm.getName().equalsIgnoreCase("service")) { service = elm.getText(); } else if (elm.getName().equalsIgnoreCase("rac") && !elm.getText().isEmpty()) { rac = elm.getText(); } } } } String url = "jdbc:oracle:thin:@(DESCRIPTION =" + "(ADDRESS_LIST =" + "(ADDRESS = (PROTOCOL = TCP)(HOST =" + rac + ")(PORT = 1521))" + ")" + "(CONNECT_DATA =" + "(SERVICE_NAME = " + service + ")" + ")" + ")"; DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection(url, username, password); return conn; } /* * Returns database env Element from db_qa2 or db_qa3.xml based on the dbTag name supplied */ public static Element getDatabaseEnv(String dbTag) { HashMap<String, Element> db = dbEnvMap.get("db_" + getConfig("QA_TEST_ENV").toLowerCase()); return (db.get(dbTag)); } /** * Get a WM APP configuration attribute * @param key the attribute to get * @return the value or null if not existing */ public static String getConfig(String key) { String val = null; if (key != null) { val = configProp.getProperty(key); return val; } return null; } private static void parseDbDataFiles() { dbEnvMap.put("db_" + getConfig("QA_TEST_ENV").toLowerCase(), loadDbDataFile(new File("." + "/data" + File.separator + "config" + File.separator + "db_" + getConfig("QA_TEST_ENV").toLowerCase() + ".xml"))); } private static HashMap loadDbDataFile(File dbDataFile) { List<Element> elms = scanDbDataFiles(dbDataFile); HashMap<String, Element> data = new HashMap<String, Element>(); for (Element element : elms) { String elementName = element.getName(); if (!elementName.isEmpty()) { data.put(elementName, element); } } return data; } private static List<Element> scanDbDataFiles(File configFile) { List<Element> elements = new ArrayList<Element>(); Document doc = null; try { doc = new SAXReader().read(configFile); } catch (DocumentException e) { WmLog.getCoreLogger().info( ">>>Exception:<<<" + ">>> Failed in reading Data XML file " + configFile.getAbsolutePath()); System.out.println(">>>Exception:<<<" + ">>> Failed in Reading XML Data file"); } Element rootEle = doc.getRootElement(); elements.addAll(rootEle.elements()); return elements; } }