public static void logInit(boolean force) throws IOException { if (!IsLogInitialized || force) { LogManager.getLogManager() .readConfiguration( LibRt.class.getClassLoader().getResourceAsStream("cfg.logging.properties")); String logFile = System.getProperty("CfgLog"); if (logFile != null) { LogManager.getLogManager().readConfiguration(new FileInputStream(logFile)); } try { LogLvlMax = Integer.parseInt(System.getProperty("LogLvlMax", LogLvlMax + "")); } catch (Exception ex) { } ; // XXX: mostrar mensaje? } }
/* uses badsource and badsink */ public void bad() throws Throwable { String data; /* get system property user.home */ /* POTENTIAL FLAW: Read data from a system property */ data = System.getProperty("user.home"); Connection dbConnection = null; try { dbConnection = IO.getDBConnection(); /* POTENTIAL FLAW: Set the catalog name with the value of data * allowing a nonexistent catalog name or unauthorized access to a portion of the DB */ dbConnection.setCatalog(data); } catch (SQLException exceptSql) { IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql); } finally { try { if (dbConnection != null) { dbConnection.close(); } } catch (SQLException exceptSql) { IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql); } } }
private void saveWorkbook(Workbook resultWorkbook, String fileName) throws IOException { String saveResultsProp = System.getProperty("saveResults"); if ("true".equalsIgnoreCase(saveResultsProp)) { OutputStream os = new BufferedOutputStream(new FileOutputStream(fileName)); resultWorkbook.write(os); os.flush(); os.close(); } }
public static void main(String args[]) throws SQLException { String url = "jdbc:oracle:oci8:@"; try { String url1 = System.getProperty("JDBC_URL"); if (url1 != null) url = url1; } catch (Exception e) { // If there is any security exception, ignore it // and use the default } // Create a OracleDataSource instance and set properties OracleDataSource ods = new OracleDataSource(); ods.setUser("hr"); ods.setPassword("hr"); ods.setURL(url); // Connect to the database Connection conn = ods.getConnection(); Statement stmt = conn.createStatement(); stmt.execute("delete from departments where department_id > 2000"); // Default batch value set to 50 for all prepared statements // belonging to this connection. ((OracleConnection) conn).setDefaultExecuteBatch(50); PreparedStatement ps = conn.prepareStatement("insert into departments values (?, ?, ?, ?)"); ps.setInt(1, 2010); ps.setString(2, "Import"); ps.setInt(3, 114); ps.setInt(4, 1700); // this execute does not actually happen at this point System.out.println(ps.executeUpdate()); ps.setInt(1, 2020); ps.setString(2, "Export"); ps.setInt(3, 145); ps.setInt(4, 2500); // this execute does not actually happen at this point int rows = ps.executeUpdate(); System.out.println("Number of rows updated before calling sendBatch: " + rows); // Execution of both previously batched executes will happen // at this point. The number of rows updated will be // returned by sendBatch. rows = ((OraclePreparedStatement) ps).sendBatch(); System.out.println("Number of rows updated by calling sendBatch: " + rows); ps.close(); conn.close(); }
// Fireup tomcat and register this servlet public static void main(String[] args) throws LifecycleException, SQLException { Tomcat tomcat = new Tomcat(); tomcat.setPort(8080); File base = new File(System.getProperty("java.io.tmpdir")); Context rootCtx = tomcat.addContext("/", base.getAbsolutePath()); Tomcat.addServlet(rootCtx, "log", new LogService()); rootCtx.addServletMapping("/*", "log"); tomcat.start(); tomcat.getServer().await(); }
public AnySQLDriver() { try { DriverManager.registerDriver(this); } catch (SQLException sqle) { } String cfgfile = System.getProperty("db.config"); VariableTable vt = new VariableTable(); if (cfgfile != null) { vt.loadFile(cfgfile); DBPhysicalManager.loadDBConfig(vt); DBLogicalManager.loadDBConfig(vt); DBPhysicalManager.markupAll(); } }
public void callData() { workingDir = System.getProperty("user.dir"); String fileName = workingDir + File.separator + "data" + File.separator + "log" + File.separator + "karaf.log"; WatchedDir = workingDir + File.separator + "data" + File.separator + "log"; try { LOG.info("log enabled ..."); Thread.sleep(10000); infile = new File(theNewestFile.toString()); LOG.info("karaf file ..." + theNewestFile); Thread.sleep(1000); LOG.info("parsing karaf file ..."); Thread.sleep(9000); Scanner scanner = new Scanner(infile); while (scanner.hasNext()) { line = scanner.nextLine(); if (line.contains("DEBUG") && line.contains("from")) { String phrase1 = line; String delims = "[,]+"; String[] tokens = phrase1.split(delims); String phrase2 = line; String delims2 = "[|]+"; String[] tokens2 = phrase2.split(delims2); time = tokens[0]; attempt = tokens2[5]; String phrase3 = line; String[] parts = phrase3.split(" "); srcIP = parts[parts.length - 1]; usecpluginAAAstore.addData(time, srcIP, attempt); LOG.info("Information stored in datastore is" + time + " " + srcIP + " " + attempt); } } PublishNotif publishNotif = new PublishNotif(); publishNotif.setdataBroker(dataBroker); publishNotif.Notify(); } catch (Exception e) { e.printStackTrace(); } }
// Logs a new entry in the library RSS file public static synchronized void addRSSEntry( String title, String link, String description, File rssFile) { // File rssFile=new File("nofile.xml"); try { System.out.println("Looking for RSS file: " + rssFile.getCanonicalPath()); if (rssFile.exists()) { SAXReader reader = new SAXReader(); Document document = reader.read(rssFile); Element root = document.getRootElement(); Element channel = root.element("channel"); List items = channel.elements("item"); int numItems = items.size(); items = null; if (numItems > 9) { Element removeThisItem = channel.element("item"); channel.remove(removeThisItem); } Element newItem = channel.addElement("item"); Element newTitle = newItem.addElement("title"); Element newLink = newItem.addElement("link"); Element newDescription = newItem.addElement("description"); newTitle.setText(title); newDescription.setText(description); newLink.setText(link); Element pubDate = channel.element("pubDate"); pubDate.setText((new java.util.Date()).toString()); // now save changes FileWriter mywriter = new FileWriter(rssFile); OutputFormat format = OutputFormat.createPrettyPrint(); format.setLineSeparator(System.getProperty("line.separator")); XMLWriter writer = new XMLWriter(mywriter, format); writer.write(document); writer.close(); } } catch (IOException ioe) { System.out.println("ERROR: Could not find the RSS file."); ioe.printStackTrace(); } catch (DocumentException de) { System.out.println("ERROR: Could not read the RSS file."); de.printStackTrace(); } catch (Exception e) { System.out.println("Unknown exception trying to add an entry to the RSS file."); e.printStackTrace(); } }
private void configureExportDatabaseConnection() throws java.sql.SQLException { String dbString = System.getProperty("CONFDB"); if (dbString == null || dbString.equals("") || dbString.indexOf('/') == -1 || dbString.indexOf('@') == -1) throw new java.sql.SQLException("No valid $CONFDB variable found : can not connect!"); String user = dbString.substring(0, dbString.indexOf('/')); String password = dbString.substring(dbString.indexOf('/') + 1, dbString.indexOf('@')); String url = dbString.substring(dbString.indexOf('@') + 1, dbString.length()); url = "jdbc:oracle:thin:@" + url; c.setUser(user); c.setUrl(url); c.setPassword(password); }
public static void main(String[] args) throws Exception { System.out.println(System.getProperty("java.library.path")); Class.forName("org.sqlite.JDBC"); Connection conn = DriverManager.getConnection( "jdbc:sqlite:D:\\MyProject\\java\\beaj\\src\\test\\Northwind.db"); Statement stat = conn.createStatement(); ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); // ImportProducts(stat); ImportCategories(stat); // ImportUser(stat); // ImportRole(stat); // ImportAuth(stat);ImportRoleAuth(stat); // ImportUserRole(stat); conn.close(); }
public static SQLException toSQLException(String msg, String sqlState, Throwable t) { if (t instanceof SQLException) { if (Debug.DEBUG && Debug.TRACE == Debug.TRACE_MAX && logger.isLoggable(MLevel.FINER)) { SQLException s = (SQLException) t; StringBuffer tmp = new StringBuffer(255); tmp.append("Attempted to convert SQLException to SQLException. Leaving it alone."); tmp.append(" [SQLState: "); tmp.append(s.getSQLState()); tmp.append("; errorCode: "); tmp.append(s.getErrorCode()); tmp.append(']'); if (msg != null) tmp.append(" Ignoring suggested message: '" + msg + "'."); logger.log(MLevel.FINER, tmp.toString(), t); SQLException s2 = s; while ((s2 = s2.getNextException()) != null) logger.log(MLevel.FINER, "Nested SQLException or SQLWarning: ", s2); } return (SQLException) t; } else { if (Debug.DEBUG) { // t.printStackTrace(); if (logger.isLoggable(MLevel.FINE)) logger.log(MLevel.FINE, "Converting Throwable to SQLException...", t); } if (msg == null) msg = "An SQLException was provoked by the following failure: " + t.toString(); if (VersionUtils.isAtLeastJavaVersion14()) { SQLException out = new SQLException(msg); out.initCause(t); return out; } else return new SQLException( msg + System.getProperty("line.separator") + "[Cause: " + ThrowableUtils.extractStackTrace(t) + ']', sqlState); } }
public static void log(String depbugstatement) { SimpleLayout layout = new SimpleLayout(); FileAppender appender = null; try { String userDir = System.getProperty("user.dir"); // FileInputStream fis = null; if (userDir.indexOf(":") != -1) { appender = new FileAppender(layout, "..\\server\\default\\log\\caIMAGE.log", false); } else { appender = new FileAppender(layout, "../log/caIMAGE.log", false); } } catch (Exception e) { e.printStackTrace(); } logger.addAppender(appender); logger.setLevel((Level) Level.DEBUG); logger.debug(depbugstatement); }
public void ejecutarReporte(String codigo) { try { cn = Conexion.realizarConexion(); String master = System.getProperty("user.dir") + "\\src\\reportes\\reporteEmpleado.jasper"; System.out.println("master: " + master); if (master == null) { showMessageDialog(null, "No se encontro el archivo", "Error", 0); System.exit(2); } JasperReport masterReport = null; try { masterReport = (JasperReport) JRLoader.loadObject(master); } catch (JRException ex) { showMessageDialog(null, "MasterReport:" + ex.getMessage(), "Error", 0); System.exit(3); } Map parametro = new HashMap(); parametro.put("chr_emplcodigo", codigo); JasperPrint jasperPrint = JasperFillManager.fillReport(masterReport, parametro, cn); JasperViewer jviewer = new JasperViewer(jasperPrint, false); jviewer.setTitle("Reporte"); jviewer.setVisible(true); } catch (ClassNotFoundException ex) { showMessageDialog(null, ex.getMessage(), "Error", 0); } catch (SQLException ex) { showMessageDialog(null, ex.getMessage(), "Error", 0); } catch (Exception ex) { showMessageDialog(null, ex.getMessage(), "Error", 0); } finally { try { cn.close(); } catch (Exception ex) { showMessageDialog(null, ex.getMessage(), "Error", 0); } } }
private String getDatabaseStructureInfo() { ResultSet schemaRs = null; ResultSet catalogRs = null; String nl = System.getProperty("line.separator"); StringBuffer sb = new StringBuffer(nl); // Let's give the user some feedback. The exception // is probably related to incorrect schema configuration. sb.append("Configured schema:").append(getSchema()).append(nl); sb.append("Configured catalog:").append(getCatalog()).append(nl); try { schemaRs = getMetaData().getSchemas(); sb.append("Available schemas:").append(nl); while (schemaRs.next()) { sb.append(" ").append(schemaRs.getString("TABLE_SCHEM")).append(nl); } } catch (SQLException e2) { GLogger.warn("Couldn't get schemas", e2); sb.append(" ?? Couldn't get schemas ??").append(nl); } finally { dbHelper.close(schemaRs, null); } try { catalogRs = getMetaData().getCatalogs(); sb.append("Available catalogs:").append(nl); while (catalogRs.next()) { sb.append(" ").append(catalogRs.getString("TABLE_CAT")).append(nl); } } catch (SQLException e2) { GLogger.warn("Couldn't get catalogs", e2); sb.append(" ?? Couldn't get catalogs ??").append(nl); } finally { dbHelper.close(catalogRs, null); } return sb.toString(); }
public File getTheNewestFile() { workingDir = System.getProperty("user.dir"); String fileName = workingDir + File.separator + "data" + File.separator + "log" + File.separator + "karaf.log"; WatchedDir = workingDir + File.separator + "data" + File.separator + "log"; try { LOG.info("log enabled ..."); File dir = new File(WatchedDir); Thread.sleep(10000); files = dir.listFiles(); Arrays.sort(files, LastModifiedFileComparator.LASTMODIFIED_COMPARATOR); for (int i = 0; i < files.length; i++) { file = files[i]; karafFiles += file.getName(); } LOG.info("karaf files:" + karafFiles); Arrays.sort(files, LastModifiedFileComparator.LASTMODIFIED_REVERSE); for (int i = 0; i < files.length; i++) { file = files[i]; RevOrder_ofFiles += file.getName(); } LOG.info("list of karaf File in reverse order" + " " + RevOrder_ofFiles); theNewestFile = files[0]; LOG.info("Latest karaf File" + theNewestFile); } catch (Exception e) { e.printStackTrace(); } return theNewestFile; }
/** * Takes a vector full of property lists and generates a report. * * @param args Command line arguments. args[0] should be the config filename. */ public static void main(String[] args) { // Load the database properties from properties file Properties properties = new Properties(); // Load config file String configFile = null; if (args.length > 0) configFile = args[0]; try { if (configFile == null) { System.out.println("Database config file not set."); return; } else properties.load(new FileInputStream(configFile)); } catch (IOException e) { System.out.println("Error opening config file."); } String url = properties.getProperty("databaseUrl"); String username = properties.getProperty("username"); String password = properties.getProperty("password"); String dir = System.getProperty("user.dir"); // Current working directory Connection con = null; // Try to open file containing javac output String output = ""; try { BufferedReader outputReader = new BufferedReader(new FileReader(dir + "/CompileOut.txt")); while (outputReader.ready()) output += outputReader.readLine() + '\n'; // Close file outputReader.close(); } catch (FileNotFoundException e) { System.out.println("Error opening compilation output file."); return; } catch (IOException e) { System.out.println("I/O Exception Occured."); return; } boolean hasDriver = false; // Create class for the driver try { Class.forName("com.mysql.jdbc.Driver"); hasDriver = true; } catch (Exception e) { System.out.println("Failed to load MySQL JDBC driver class."); } // Create connection to database if the driver was found if (hasDriver) { try { con = DriverManager.getConnection(url, username, password); } catch (SQLException e) { System.out.println("Couldn't get connection!"); } } // Check that a connection was made if (con != null) { long userEventId = -1; // Store results from the report into the database try { BufferedReader rd = new BufferedReader( new FileReader(dir + "/userId.txt")); // Read userId.txt to get userId String userId = rd.readLine(); // Store userId from text file rd.close(); // Insert the report into the table and get the auto_increment id for it Statement stmt = con.createStatement(); stmt.executeUpdate("INSERT INTO userEvents (userId) VALUES ('" + userId + "')"); ResultSet result = stmt.getGeneratedKeys(); result.next(); userEventId = result.getLong(1); // Close the statement stmt.close(); // Prepare statement for adding the compilation error to the userEvent PreparedStatement compErrorPrepStmt = con.prepareStatement( "INSERT INTO userEventCompilationErrors(userEventId, output) VALUES (?, ?)"); // Insert userEventId and docletId into the database compErrorPrepStmt.setLong(1, userEventId); compErrorPrepStmt.setString(2, output); compErrorPrepStmt.executeUpdate(); // Close the prepare statements compErrorPrepStmt.close(); } catch (Exception e) { System.out.println("Exception Occurred"); System.out.println(e); } // Store the java files for the report try { // Prepare statement for storing files PreparedStatement filePrepStmt = con.prepareStatement( "INSERT INTO files(userEventId, filename, contents) VALUES (" + userEventId + ", ?, ?)"); // Get the list of files from source.txt BufferedReader rd = new BufferedReader( new FileReader(dir + "/source.txt")); // Read userId.txt to get userId while (rd.ready()) { String filename = rd.readLine(); // Store userId from text file // Remove the "src/" from the beginning to get the real file name String realname = filename.substring(4); filePrepStmt.setString(1, realname); // Read in the contents of the files String contents = ""; File javaFile = new File(dir + "/" + filename); int length = (int) javaFile.length(); // Add parameter for file contents to the prepared statement and execute it filePrepStmt.setCharacterStream(2, new BufferedReader(new FileReader(javaFile)), length); filePrepStmt.executeUpdate(); } rd.close(); } catch (IOException e) { System.err.println("I/O Exception Occured."); } catch (SQLException e) { System.err.println("SQL Exception Occured."); } } }
@Override public String getRequestsGroupByProductSentInLastMonth() throws SQLException { Map<String, Integer> aMap = Maps.newLinkedHashMap(); final List<String> patterns = Lists.newArrayList(System.getProperty("PRODUCTS").split(",")); for (String aPattern : patterns) { aMap.put(aPattern, 0); } aMap.put(UNKNOWN, 0); Connection conn = RbDb.getConnection(); Statement statement = null; ResultSet rs = null; try { statement = conn.createStatement(); rs = statement.executeQuery( "SELECT summary FROM reviews_reviewrequest " + "WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= time_added " + "ORDER BY time_added"); String summary = null; boolean found = false; while (rs.next()) { summary = rs.getString(1); found = false; for (int i = 0; i < patterns.size(); ++i) { String key = patterns.get(i); if (summary.contains(key + "-")) { aMap.put(key, aMap.get(key) + 1); found = true; break; } } if (!found) { aMap.put(UNKNOWN, aMap.get(UNKNOWN) + 1); } } } finally { if (rs != null) { rs.close(); } if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } List<Pair<String, Integer>> aList = Lists.newArrayList(); for (Map.Entry<String, Integer> entry : aMap.entrySet()) { aList.add(new Pair<>(entry.getKey(), entry.getValue())); } return new GsonBuilder().create().toJson(aList); }
public final class Database { private static final String PATH_TO_SQL_SCRIPT = System.getProperty("user.dir") + "/DocumentTermsDump.sql"; private static final BasicDataSource dataSource = new BasicDataSource(); public static Connection getConnection(Configuration config) throws SQLException { String dbtype = config.getString("DB_TYPE"); switch (dbtype) { case "mysql": dataSource.setDriverClassName("com.mysql.jdbc.Driver"); } dataSource.setUrl( "jdbc:" + dbtype + "://" + config.getString("DB_HOSTNAME") + "/" + config.getString("DB_NAME") + "?autoReconnect=true&useSSL=false&rewriteBatchedStatements=true"); dataSource.setUsername(config.getString("DB_USER")); dataSource.setPassword(config.getString("DB_PASS")); return dataSource.getConnection(); } private Database() {} public static boolean checkForTables(Configuration config) { String sqlCheck = "SHOW TABLES"; try (Connection connection = Database.getConnection(config); PreparedStatement preparedStatement = connection.prepareStatement(sqlCheck); ResultSet res = preparedStatement.executeQuery()) { res.last(); if (res.getRow() != 3) { return false; } } catch (SQLException e) { e.printStackTrace(); return false; } return true; } public static boolean initDatabase(Configuration config) { try (Connection connection = Database.getConnection(config); Statement statement = connection.createStatement()) { Scanner s = new Scanner(FileUtil.getInputStreamFrom(PATH_TO_SQL_SCRIPT)); s.useDelimiter("(;(\r)?\n)|(--\n)"); while (s.hasNext()) { String line = s.next(); if (line.startsWith("/*!") && line.endsWith("*/")) { int i = line.indexOf(' '); line = line.substring(i + 1, line.length() - " */".length()); } if (line.trim().length() > 0) { statement.execute(line); } } } catch (SQLException | FileNotFoundException ex) { ex.printStackTrace(); return false; } return true; } }
public CrossVersionResultsStore() { this(new File(System.getProperty("user.home"), ".gradle-performance-test-data/results")); }
public void bad() throws Throwable { String data; if (IO.staticReturnsTrueOrFalse()) { /* get system property user.home */ /* POTENTIAL FLAW: Read data from a system property */ data = System.getProperty("user.home"); } else { /* FIX: Use a hardcoded string */ data = "foo"; } if (IO.staticReturnsTrueOrFalse()) { if (data != null) { String names[] = data.split("-"); int successCount = 0; Connection dbConnection = null; Statement sqlStatement = null; try { dbConnection = IO.getDBConnection(); sqlStatement = dbConnection.createStatement(); for (int i = 0; i < names.length; i++) { /* POTENTIAL FLAW: data concatenated into SQL statement used in executeBatch(), which could result in SQL Injection */ sqlStatement.addBatch( "update users set hitcount=hitcount+1 where name='" + names[i] + "'"); } int resultsArray[] = sqlStatement.executeBatch(); for (int i = 0; i < names.length; i++) { if (resultsArray[i] > 0) { successCount++; } } IO.writeLine("Succeeded in " + successCount + " out of " + names.length + " queries."); } catch (SQLException exceptSql) { IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql); } finally { try { if (sqlStatement != null) { sqlStatement.close(); } } catch (SQLException exceptSql) { IO.logger.log(Level.WARNING, "Error closing Statament", exceptSql); } try { if (dbConnection != null) { dbConnection.close(); } } catch (SQLException exceptSql) { IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql); } } } } else { if (data != null) { String names[] = data.split("-"); int successCount = 0; Connection dbConnection = null; PreparedStatement sqlStatement = null; try { /* FIX: Use prepared statement and executeBatch (properly) */ dbConnection = IO.getDBConnection(); sqlStatement = dbConnection.prepareStatement("update users set hitcount=hitcount+1 where name=?"); for (int i = 0; i < names.length; i++) { sqlStatement.setString(1, names[i]); sqlStatement.addBatch(); } int resultsArray[] = sqlStatement.executeBatch(); for (int i = 0; i < names.length; i++) { if (resultsArray[i] > 0) { successCount++; } } IO.writeLine("Succeeded in " + successCount + " out of " + names.length + " queries."); } catch (SQLException exceptSql) { IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql); } finally { try { if (sqlStatement != null) { sqlStatement.close(); } } catch (SQLException exceptSql) { IO.logger.log(Level.WARNING, "Error closing PreparedStatement", exceptSql); } try { if (dbConnection != null) { dbConnection.close(); } } catch (SQLException exceptSql) { IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql); } } } } }
public class LibRt { public static String CfgEncodingDflt = "ISO-8859-1"; // U: si no especificamos, un archivo a string se lee con este encoding public static final Logger logger = Logger.getLogger("rt"); public static final String EOL = "\n"; public static final int BUFF_SZ = 8192; public static java.util.Hashtable state = new java.util .Hashtable(); // U: accesible desde javascript para mantener estado entre llamadas AUNQUE // los scripts se ejecuten de cero en cada thread public static boolean IsLogInitialized = false; public static void logInit(boolean force) throws IOException { if (!IsLogInitialized || force) { LogManager.getLogManager() .readConfiguration( LibRt.class.getClassLoader().getResourceAsStream("cfg.logging.properties")); String logFile = System.getProperty("CfgLog"); if (logFile != null) { LogManager.getLogManager().readConfiguration(new FileInputStream(logFile)); } try { LogLvlMax = Integer.parseInt(System.getProperty("LogLvlMax", LogLvlMax + "")); } catch (Exception ex) { } ; // XXX: mostrar mensaje? } } // Primitiva de log public static int LogLvlMax = 9; // DFLT public static void logm(String t, int lvl, String msg, Object o) { // System.out.println("LOG:" + t + ":" + lvl + ":" + msg + ":" + (o != null ? ser_json(o) : // "")); if (lvl <= LogLvlMax) { logger.info(t + ":" + lvl + ":" + msg + ":" + (o != null ? ser_json(o) : "")); } } public static void logmex(String t, int lvl, String msg, Object o, Exception ex) { StringBuilder sb = new StringBuilder(); StackTraceElement se[] = ex.getStackTrace(); for (int i = 0; i < se.length; i++) { sb.append(se[i].getMethodName() + "@" + se[i].getFileName() + "@" + se[i].getLineNumber()); sb.append(" > "); } logm(t, lvl, msg + " EXCEPTION " + ex + " " + sb.toString(), o); } // *************************************************************************** // S: enc/base64 public static String enc_base64(String data) throws UnsupportedEncodingException { return enc_base64(data.getBytes("UTF-8")); } public static String enc_base64(byte[] data) throws UnsupportedEncodingException { return DatatypeConverter.printBase64Binary(data); } public static String enc_base64_r_str(String encoded) throws UnsupportedEncodingException { return new String(enc_base64_r(encoded), "UTF-8"); } public static byte[] enc_base64_r(String encoded) throws UnsupportedEncodingException { return DatatypeConverter.parseBase64Binary(encoded); } // *************************************************************************** // S: ser/json static final Gson gson = new Gson(); // A: encapsulamos // XXX: mejor usar Jackson? cual usan otros proyectos de EM? Por que? public static String ser_json(Object o) { String s = ""; try { s = gson.toJson(o); } catch (Exception ex) { s = o + ""; } return s; } // *************************************************************************** // S: ser/csv public static int serRsCsvToFile( ResultSet resultset, String[] columnNames, String path, int maxRows, String separator) { Writer writer; try { writer = fileWriter(path); } catch (UnsupportedEncodingException e) { logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN unsupported encondig", path); return -1; } catch (FileNotFoundException e) { logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN file not found", path); return -1; } catch (IOException e) { logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN file not found", path); return -1; } // A: writer escribe en el archivo especificado return serRsCsvToWriter(resultset, columnNames, writer, maxRows, separator); } public static int serDiccCsvToWriter(String str, String path, int maxRows, String separator) { Writer writer; try { writer = fileWriterAppend(path, false, null); logm("DBG", 9, "ABRE WRITER", null); } catch (UnsupportedEncodingException e) { logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN unsupported encondig", path); return -1; } catch (FileNotFoundException e) { logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN file not found", path); return -1; } catch (IOException e) { logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN file not found", path); return -1; } // A: writer escribe en el archivo especificado return serDiccCsvToWriter(str, writer, maxRows, separator); } public static String[] dbRsColumnNames(ResultSet resultset) throws SQLException { java.util.Vector<String> rv = new java.util.Vector<String>(); if (resultset != null) { logm("DBG", 9, "ColumnCount=0 Calculando cantidad columnas desde metadata", null); ResultSetMetaData md = resultset.getMetaData(); int columnCount = md.getColumnCount(); logm("DBG", 9, "ColumnCount", columnCount); for (int i = 1; i <= columnCount; i++) { rv.add(md.getColumnName(i).toLowerCase()); } } String[] r = rv.toArray(new String[0]); logm("DBG", 9, "DB dbRsColumnNames", r); return r; } public static int serRsCsvToWriter( ResultSet resultset, String[] columnNames, Writer writer, int maxRows, String separator) { int counter = 0; if (resultset != null) { if (columnNames == null) { try { logm("NFO", 9, "CSV titulos, obteniendo desde metadata", null); columnNames = dbRsColumnNames(resultset); } catch (SQLException e) { logmex("ERR", 1, "CSV titulos, obteniendo desde metadata", null, e); return -1; } } logm("DBG", 7, "CSV titulos", columnNames); // A: columnCount tiene el valor especificado o el default int columnCount = columnNames.length; // Itero el resultset escribiendo el output try { // XXX:OPCION escape separator si aparece en un valor? logm("DBG", 4, "ESCRIBIENDO ARCHIVO", resultset); for (int i = 0; i < columnCount - 1; i++) { logm("DBG", 9, "ESCRIBE COL: ", columnNames[i]); writer.write(columnNames[i]); writer.write(separator); } writer.write(columnNames[columnCount - 1]); writer.write(EOL); logm("DBG", 4, "SE ESCRIBIO LINEA DE COLUMNAS", null); logm("DBG", 4, "COUNTER", counter); logm("DBG", 4, "MAXROWS", maxRows); // A: escribi los nombres de las columnas boolean hasNext = resultset.next(); logm("DBG", 4, "NEXT", hasNext); while ((counter < maxRows || maxRows < 0) && hasNext) { logm("DBG", 4, "Escribiendo fila :", counter); String buf; for (int i = 1; i < columnCount; i++) { if ((buf = resultset.getString(i)) != null) { writer.write(buf); } logm("DBG", 9, "STR", buf); writer.write(separator); } if ((buf = resultset.getString(columnCount)) != null) { writer.write(buf); } logm("DBG", 9, "STR", buf); writer.write(EOL); counter++; // XXX:loguear un cartelito ej. cada 1000 hasNext = resultset.next(); } logm("DBG", 2, "termino de escribir lineas", null); } catch (SQLException s) { logmex("ERR", 0, "DB leyendo resultset para CSV", null, s); return -1; } catch (IOException e) { logmex("ERR", 0, "FILE WRITER CSV OUTPUT writing", null, e); return -1; } } else { logm("NFO", 3, "DB FILE CSV RESULTSET IS NULL, was expected?", null); } try { writer.close(); } catch (IOException e) { logmex("ERR", 0, "FILE WRITER CSV OUTPUT closing", null, e); return -1; } return counter; } @SuppressWarnings("unchecked") public static int serDiccCsvToWriter(String csv, Writer writer, int maxRows, String separator) { int counter = 0; if (csv != null) { try { writer.write(csv); writer.write(EOL); } catch (IOException e) { logmex("ERR", 0, "FILE WRITER CSV OUTPUT writing", null, e); return -1; } } else { logm("NFO", 3, "DB FILE CSV RESULTSET IS NULL, was expected?", null); } return counter; } public static int serDiccGroupByToWriter( ResultSet rs, Writer writer, int maxRows, String idPor, String[] idAcumulados, String campoAcumuladoNombre) { int rowsCount = 0; try { ArrayList<String> acumulado = null; String idActual = null; StringBuilder reg = null; reg = new StringBuilder(); String value = ""; if (rs != null) { ResultSetMetaData rsm = rs.getMetaData(); int countCol = rsm.getColumnCount(); String name = ""; for (int i = 1; i <= countCol; i++) { name = rsm.getColumnName(i); reg.append(name.toLowerCase()).append("\t"); } reg.append(campoAcumuladoNombre); writer.write(reg.toString() + EOL); while (rs.next()) { if (idActual == null) { reg = new StringBuilder(); acumulado = new ArrayList<String>(); idActual = rs.getString(idPor); for (int i = 1; i <= countCol; i++) { reg.append(rs.getString(i)).append("\t"); } for (String id : idAcumulados) { value = rs.getString(id); if (!rs.wasNull()) { acumulado.add(rs.getString(id)); } } } else { if (idActual.equals(rs.getString(idPor))) { for (String id : idAcumulados) { value = rs.getString(id); if (!rs.wasNull()) { acumulado.add(rs.getString(id)); } } } else { if (acumulado.size() > 0) { for (String str : acumulado) { reg.append(str).append(","); } reg.deleteCharAt(reg.length() - 1); } reg.append(EOL); writer.write(reg.toString()); rowsCount++; if (maxRows == rowsCount) { break; } idActual = rs.getString(idPor); reg = new StringBuilder(); acumulado = new ArrayList<String>(); for (int i = 1; i <= countCol; i++) { reg.append(rs.getString(i)).append("\t"); } for (String id : idAcumulados) { value = rs.getString(id); if (!rs.wasNull()) { acumulado.add(rs.getString(id)); } } } } } if (acumulado.size() > 0) { for (String str : acumulado) { reg.append(str).append(","); } reg.deleteCharAt(reg.length() - 1); } reg.append(EOL); writer.write(reg.toString()); rowsCount++; } } catch (SQLException e) { logm("ERR", 1, "Error al escribir registros", e); } catch (IOException e) { logm("ERR", 1, "Error al escribir registros", e); } return rowsCount; } public static void closeWriterAppend(Writer writer) { try { writer.close(); } catch (IOException e) { logmex("ERR", 0, "FILE WRITER CSV OUTPUT closing", null, e); } } // *************************************************************************** // S: db public static int dbRsColumnCount(ResultSet rs) throws SQLException { return rs.getMetaData().getColumnCount(); } // *************************************************************************** // S: digest public static MessageDigest digestCalc(String algorithm) throws NoSuchAlgorithmException { return MessageDigest.getInstance(algorithm); } public static String digestHexStr(MessageDigest digestCalc) { return (new HexBinaryAdapter()).marshal(digestCalc.digest()); } public static String digest(String data, String algorithm) throws NoSuchAlgorithmException { MessageDigest calc = digestCalc(algorithm); calc.update(data.getBytes()); return digestHexStr(calc); } // *************************************************************************** // S: file public static Writer fileWriter(String path) throws UnsupportedEncodingException, FileNotFoundException, IOException { return fileWriter(path, false, null); } public static Writer fileWriter(String path, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { return streamWriter(new FileOutputStream(path), zip, digestCalc); } public static Writer fileWriterAppend(String path, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { return streamWriter(new FileOutputStream(path, true), zip, digestCalc); } public static OutputStream streamForOutput_file( String path, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { return streamForOutput(new FileOutputStream(path), zip, digestCalc); } public static OutputStream streamForOutput(OutputStream os, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { OutputStream dos = digestCalc != null ? new DigestOutputStream(os, digestCalc) : os; OutputStream zos = zip ? new GZIPOutputStream(dos) : dos; return zos; } public static Writer streamWriter(OutputStream os, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { return new BufferedWriter( new OutputStreamWriter(streamForOutput(os, zip, digestCalc), "utf-8")); } public static InputStream streamForInput_file(String path, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { return streamForInput(new FileInputStream(path), zip, digestCalc); } public static Reader fileReader(String path, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { return streamReader(new FileInputStream(path), zip, digestCalc); } public static InputStream streamForInput(InputStream fis, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { InputStream dis = digestCalc != null ? new DigestInputStream(fis, digestCalc) : fis; InputStream zis = zip ? new GZIPInputStream(dis) : dis; return zis; } public static Reader streamReader(InputStream fis, boolean zip, MessageDigest digestCalc) throws UnsupportedEncodingException, FileNotFoundException, IOException { return new BufferedReader( new InputStreamReader(streamForInput(fis, zip, digestCalc))); // , "utf-8")); } public static int pipe_stream(InputStream is, OutputStream os, boolean wantsKeepOpen) throws IOException { // U: copia de un stream al otro int cnt = 0; int n; byte[] buffer = new byte[BUFF_SZ]; while ((n = is.read(buffer)) > -1) { cnt += n; os.write(buffer, 0, n); } if (!wantsKeepOpen) { is.close(); os.close(); } return cnt; } public static String get_stream(Reader isr) throws IOException { char[] buffer = new char[BUFF_SZ]; StringBuilder out = new StringBuilder(); logm("DBG", 9, "STREAM GET", isr + ""); for (; ; ) { int rsz = isr.read(buffer, 0, buffer.length); logm("DBG", 9, "STREAM GET READ", rsz); if (rsz < 0) break; out.append(buffer, 0, rsz); } String s = out.toString(); logm("DBG", 9, "STREAM GET RESULT", s); return s; } public static String get_stream(InputStream is) throws IOException { return get_stream(is, CfgEncodingDflt); } public static String get_stream(InputStream is, String encoding) throws IOException { if (encoding == null) { encoding = CfgEncodingDflt; } byte[] buffer = new byte[BUFF_SZ]; StringBuilder out = new StringBuilder(); logm("DBG", 9, "STREAM GET", is + ""); for (; ; ) { int rsz = is.read(buffer, 0, buffer.length); logm("DBG", 9, "STREAM GET READ", rsz); if (rsz < 0) break; out.append(new String(buffer, 0, rsz, encoding)); } String s = out.toString(); logm("DBG", 9, "STREAM GET RESULT", s); return s; } public static void set_stream(OutputStream os, String data, String encoding) throws IOException { os.write(data.getBytes(encoding)); os.close(); } public static void set_stream(Writer os, String data) throws IOException { os.write(data); os.close(); } public static String get_file( String path, boolean gzip, MessageDigest digestCalc, String encoding) throws UnsupportedEncodingException, IOException { try { return get_stream(streamForInput_file(path, gzip, digestCalc)); } catch (FileNotFoundException ex) { return ""; } } public static String get_resource(String path) throws IOException { java.io.InputStream srcs = null; try { srcs = LibRt.class.getResourceAsStream(path); return get_stream(srcs); } catch (IOException ex) { LibRt.logmex("DBG", 9, "RT GET_RESOURCE", path, ex); throw (ex); } } public static String get_resourceOrFile( String path, boolean gzip, MessageDigest digestCalc, String encoding) throws UnsupportedEncodingException, IOException { try { return get_resource(path); } catch (IOException ex) { return get_file(path, gzip, digestCalc, encoding); } } public static void set_file( String path, String data, boolean gzip, MessageDigest digestCalc, String encoding) throws UnsupportedEncodingException, IOException { set_stream(streamForOutput_file(path, gzip, digestCalc), data, CfgEncodingDflt); } public static String[] get_filelist(String path, boolean nodirs, boolean nofiles) { File folder = new File(path); if (folder.isDirectory()) { File[] listOfFiles = folder.listFiles(); java.util.Vector<String> r = new java.util.Vector<String>(); for (int i = 0; listOfFiles != null && i < listOfFiles.length; i++) { if ((listOfFiles[i].isFile() && !nofiles) || (listOfFiles[i].isDirectory() && !nodirs)) { r.add(listOfFiles[i].getName()); } } return r.toArray(new String[0]); } else { return null; // A: no existe o no es directorio } } public static FileLock lock_file(String path) throws IOException, FileNotFoundException { RandomAccessFile file = new RandomAccessFile(path, "rw"); FileChannel fileChannel = file.getChannel(); return fileChannel.tryLock(); } public static boolean unlock_file(FileLock fileLock) throws IOException, FileNotFoundException { if (fileLock != null) { fileLock.release(); return true; } return false; } public static String temp_filePath(String namePattern, String ext) throws IOException, FileNotFoundException { File temp = File.createTempFile("temp-file-name", ".tmp"); return temp.getAbsolutePath(); } // *************************************************************************** // S File: scp = copiar sobre ssh public static void set_file_scp( String srcFilePath, String dstUrl, String dstFilePath, String dstUser, String dstPass, String keyFilePath) throws SshException { // SEE: // http://code.google.com/p/securechannelfacade/source/browse/trunk/src/main/java/org/rev6/scf/SshConnection.java?r=12 SshConnection sshCx = null; String[] nameAndPort = dstUrl.split(":"); int dstPort = nameAndPort.length > 1 ? Integer.parseInt(nameAndPort[1]) : 22; try { if (keyFilePath == null) { sshCx = new SshConnection(nameAndPort[0], dstUser, dstPass); logm( "DBG", 7, "NET SSH CONNECT TO {HOST: '" + nameAndPort[0] + "', PORT:" + dstPort + ", USER:'******', auth: '" + "pass" + "'}", null); } else { sshCx = new SshConnection(nameAndPort[0], dstUser, new File(keyFilePath)); logm( "DBG", 7, "NET SSH CONNECT TO {HOST: '" + nameAndPort[0] + "', PORT:" + dstPort + ", USER:'******', auth: 'key', keyfile: '" + keyFilePath + "'}", null); } sshCx.setPort(dstPort); sshCx.connect(); ScpFile scpFile = new ScpFile(new File(srcFilePath), dstFilePath); sshCx.executeTask(new ScpUpload(scpFile)); } finally { if (sshCx != null) { sshCx.disconnect(); } } } public static void set_file_scp_pass( String srcFilePath, String dstUrl, String dstFilePath, String dstUser, String dstPass) throws SshException { set_file_scp(srcFilePath, dstUrl, dstFilePath, dstUser, dstPass, null); } public static void set_file_scp_key( String srcFilePath, String dstUrl, String dstFilePath, String dstUser, String keyFilePath) throws SshException { set_file_scp(srcFilePath, dstUrl, dstFilePath, dstUser, null, keyFilePath); } // *************************************************************************** // S: net/http public static HttpURLConnection httpCx(String url, String method, String usr, String pass) throws MalformedURLException, IOException, ProtocolException { // NB:https requiere HAVA_HOME para encontrar los certificados! URL obj = new URL(url); HttpURLConnection cx = (HttpURLConnection) obj.openConnection(); cx.setRequestMethod(method); if (usr != "") { String basicAuth = "Basic " + new String(enc_base64(usr + ":" + pass)); cx.setRequestProperty("Authorization", basicAuth); } // A: parameters set return cx; } public static void httpWrite(HttpURLConnection cx, String data) throws IOException, ProtocolException { cx.setDoOutput(true); DataOutputStream wr = new DataOutputStream(cx.getOutputStream()); wr.writeBytes(data); wr.flush(); wr.close(); } public static String httpRead(HttpURLConnection cx) throws UnsupportedEncodingException, IOException { // int responseCode = con.getResponseCode(); return get_stream(cx.getInputStream()); } public static String get_http(String url, String method, String data, String usr, String pass) throws IOException, ProtocolException, UnsupportedEncodingException { HttpURLConnection cx = httpCx(url, method, usr, pass); if (data != null) { httpWrite(cx, data); } return httpRead(cx); } // **************************************************************************** // S: Javascript public static LibRtJs JsImpl = System.getProperty("jsImpl", "RHINO").equals("JDK") ? new LibRtJsJdk() : new LibRtJsRhino(); public static LibRtJs jsEval(String js, String srcDesc, LibRtJs jsEnv, String[] args) throws Exception { logm("DBG", 9, "RT JS EVAL START PATH=" + srcDesc + " SRC", js); return JsImpl.jsEval(js, srcDesc, jsEnv, args); } public static Object jsCall(String funName, LibRtJs jsEnv, Object[] args) throws IOException, Exception { logm("DBG", 5, "RT JS CALL TRY", funName); return JsImpl.jsCall(funName, jsEnv, args); } public static LibRtJs jsLoad(String path, LibRtJs jsEnv, String[] args) throws IOException, Exception { logm("DBG", 5, "RT JS LOAD TRY", path); return JsImpl.jsLoad(path, jsEnv, args); } public static LibRtJs jsLoadAlone( String path, Hashtable<String, Object> env) { // U: ejecutar un script en un contexto separado (no comparte variables salvo las // que le pasemos en el hashtable) return JsImpl.jsLoadAlone(path, env); } // **************************************************************************** // S: paths public static String runtimePath() throws java.net.URISyntaxException, java.net.MalformedURLException { // U: para cargar librerias y assets, etc. // SEE: http://stackoverflow.com/questions/320542/how-to-get-the-path-of-a-running-jar-file URL url = LibRt.class.getResource(LibRt.class.getSimpleName() + ".class"); logm("DBG", 1, "RT runtimePath URL", url); URL urlOk = (url.getProtocol() == "jar" ? new URL(url.getPath()) : url); logm("DBG", 1, "RT runtimePath URL OK", urlOk); return new File(urlOk.toURI()).getParentFile().getPath(); } // **************************************************************************** // S: main public static void init() throws Exception { logInit(false); } public static void main(String[] args) throws Exception { init(); String mainPath = args.length > 0 ? args[0] : "0inicio.js"; try { jsLoad("librt.js", null, args); jsLoad(mainPath, null, args); } catch (Exception ex) { ex.printStackTrace(); logmex("ERR", 1, "RT RUNNING SCRIPTS", null, ex); } } private static Class<?> getClassFromJar(String pathToJar, String pkg, String classToGet) throws IOException, ClassNotFoundException, SecurityException, InstantiationException, IllegalAccessException, NoSuchMethodException, IllegalArgumentException, InvocationTargetException { JarFile jarFile = new JarFile(pathToJar); Enumeration e = jarFile.entries(); URL[] urls = {new URL("jar:file:" + pathToJar + "!/")}; ClassLoader cl = URLClassLoader.newInstance(urls); Class<?> c = Class.forName(pkg + "." + classToGet, true, cl); return c; } public static void executeMethodClass( String pathToJar, String pkg, String classToGet, String methodName, String pathToFile, long logIdSyncMin, long logIdSyncMax) throws IOException, ClassNotFoundException, SecurityException, InstantiationException, IllegalAccessException, NoSuchMethodException, IllegalArgumentException, InvocationTargetException { Class<?> c = getClassFromJar(pathToJar, pkg, classToGet); Method method = c.getDeclaredMethod(methodName, String.class, long.class, long.class); method.invoke(null, pathToFile, logIdSyncMin, logIdSyncMax); } }
public static void main(String[] args) throws Exception { String method = "main"; int location = 1000; int argsCount = args.length; String sourceType = ""; String sourceServer = ""; String sourceInstance = ""; int sourcePort = 0; String sourceDatabase = ""; String sourceSchema = ""; String sourceTable = ""; String refreshType = ""; String appendColumnName = ""; int appendColumnMax = 0; String gpDatabase = ""; int gpPort = 0; int queueId = 0; int connectionId = 0; String selectSQL = ""; // this is an external table that a user defines if (argsCount == 4) { gpDatabase = args[0]; gpPort = Integer.parseInt(args[1]); connectionId = Integer.parseInt(args[2]); selectSQL = args[3]; } // this is an extrenal table Outsourcer defines else if (argsCount == 13) { sourceType = args[0]; sourceServer = args[1]; sourceInstance = args[2]; sourcePort = Integer.parseInt(args[3]); sourceDatabase = args[4]; sourceSchema = args[5]; sourceTable = args[6]; refreshType = args[7]; appendColumnName = args[8]; appendColumnMax = Integer.parseInt(args[9]); gpDatabase = args[10]; gpPort = Integer.parseInt(args[11]); queueId = Integer.parseInt(args[12]); } String gpServer = "localhost"; String gpUserName = System.getProperty("user.name"); String sourceUser = ""; String sourcePass = ""; Connection gpConn = null; try { location = 3000; gpConn = CommonDB.connectGP(gpServer, gpPort, gpDatabase, gpUserName); if (argsCount == 13) { location = 3100; executeOS( sourceType, sourceServer, sourceInstance, sourcePort, sourceDatabase, sourceSchema, sourceTable, refreshType, appendColumnName, appendColumnMax, gpConn, queueId); } else if (argsCount == 4) { location = 3200; executeExt(gpConn, connectionId, selectSQL); } location = 4000; gpConn.close(); } catch (SQLException ex) { throw new SQLException( "(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")"); } finally { if (gpConn != null) gpConn.close(); } }
/** * Checks the <code>os.name</code> system property to see if it starts with "windows". * * @return <code>true</code> if <code>os.name</code> starts with "windows", else <code>false * </code>. */ public static boolean isWindowsOS() { return System.getProperty("os.name").toLowerCase().startsWith("windows"); }
// sqlbob@users 20020401 - patch 1.7.0 by sqlbob (RMP) - enhancements // sqlbob@users 20020401 - patch 537501 by ulrivo - command line arguments // sqlbob@users 20020407 - patch 1.7.0 - reengineering public class DatabaseManager extends Applet implements ActionListener, WindowListener, KeyListener { static final String NL = System.getProperty("line.separator"); static final int iMaxRecent = 24; Connection cConn; DatabaseMetaData dMeta; Statement sStatement; Menu mRecent; String sRecent[]; int iRecent; TextArea txtCommand; Button butExecute; Tree tTree; Panel pResult; long lTime; int iResult; // 0: grid; 1: text Grid gResult; TextArea txtResult; boolean bHelp; Frame fMain; Image imgEmpty; static boolean bMustExit; // (ulrivo): variables set by arguments from the commandline static String defDriver = "org.hsqldb.jdbcDriver"; static String defURL = "jdbc:hsqldb:."; static String defUser = "******"; static String defPassword = ""; static String defScript; static String defDirectory; /** * Method declaration * * @param c */ void connect(Connection c) { if (c == null) { return; } if (cConn != null) { try { cConn.close(); } catch (SQLException e) { } } cConn = c; try { dMeta = cConn.getMetaData(); sStatement = cConn.createStatement(); refreshTree(); } catch (SQLException e) { e.printStackTrace(); } } /** Method declaration */ public void init() { DatabaseManager m = new DatabaseManager(); m.main(); try { m.connect(ConnectionDialog.createConnection(defDriver, defURL, defUser, defPassword)); m.insertTestData(); m.refreshTree(); } catch (Exception e) { e.printStackTrace(); } } /** * Method declaration * * @param arg */ public static void main(String arg[]) { System.getProperties().put("sun.java2d.noddraw", "true"); // (ulrivo): read all arguments from the command line String lowerArg; boolean autoConnect = false; for (int i = 0; i < arg.length; i++) { lowerArg = arg[i].toLowerCase(); i++; if (i == arg.length) { showUsage(); return; } if (lowerArg.equals("-driver")) { defDriver = arg[i]; autoConnect = true; } else if (lowerArg.equals("-url")) { defURL = arg[i]; autoConnect = true; } else if (lowerArg.equals("-user")) { defUser = arg[i]; autoConnect = true; } else if (lowerArg.equals("-password")) { defPassword = arg[i]; autoConnect = true; } else if (lowerArg.equals("-dir")) { defDirectory = arg[i]; } else if (lowerArg.equals("-script")) { defScript = arg[i]; } else { showUsage(); return; } } bMustExit = true; DatabaseManager m = new DatabaseManager(); m.main(); Connection c = null; try { if (autoConnect) { c = ConnectionDialog.createConnection( defDriver, defURL, defUser, defPassword); } else { c = ConnectionDialog.createConnection(m.fMain, "Connect"); } } catch (Exception e) { e.printStackTrace(); } if (c == null) { return; } m.connect(c); } private static void showUsage() { System.out.println( "Usage: java DatabaseManager [-options]\n" + "where options include:\n" + " -driver <classname> jdbc driver class\n" + " -url <name> jdbc url\n" + " -user <name> username used for connection\n" + " -password <password> password for this user\n" + " -dir <path> default directory\n" + " -script <file> reads from script file\n"); } /** Method declaration */ void insertTestData() { try { DatabaseManagerCommon.createTestTables(sStatement); refreshTree(); txtCommand.setText(DatabaseManagerCommon.createTestData(sStatement)); refreshTree(); for (int i = 0; i < DatabaseManagerCommon.testDataSql.length; i++) { addToRecent(DatabaseManagerCommon.testDataSql[i]); } execute(); } catch (SQLException e) { e.printStackTrace(); } } /** Method declaration */ void main() { fMain = new Frame("HSQL Database Manager"); imgEmpty = createImage(new MemoryImageSource(2, 2, new int[4 * 4], 2, 2)); fMain.setIconImage(imgEmpty); fMain.addWindowListener(this); MenuBar bar = new MenuBar(); // used shortcuts: CERGTSIUDOLM String fitems[] = { "-Connect...", "--", "-Open Script...", "-Save Script...", "-Save Result...", "--", "-Exit" }; addMenu(bar, "File", fitems); String vitems[] = { "RRefresh Tree", "--", "GResults in Grid", "TResults in Text", "--", "1Shrink Tree", "2Enlarge Tree", "3Shrink Command", "4Enlarge Command" }; addMenu(bar, "View", vitems); String sitems[] = { "SSELECT", "IINSERT", "UUPDATE", "DDELETE", "--", "-CREATE TABLE", "-DROP TABLE", "-CREATE INDEX", "-DROP INDEX", "--", "-CHECKPOINT", "-SCRIPT", "-SET", "-SHUTDOWN", "--", "-Test Script" }; addMenu(bar, "Command", sitems); Menu recent = new Menu("Recent"); mRecent = new Menu("Recent"); bar.add(mRecent); String soptions[] = { "-AutoCommit on", "-AutoCommit off", "OCommit", "LRollback", "--", "-Disable MaxRows", "-Set MaxRows to 100", "--", "-Logging on", "-Logging off", "--", "-Insert test data" }; addMenu(bar, "Options", soptions); /* NB - 26052002 Restore is not implemented yet in the transfer tool */ String stools[] = {"-Dump", /*"-Restore",*/ "-Transfer"}; addMenu(bar, "Tools", stools); fMain.setMenuBar(bar); fMain.setSize(640, 480); fMain.add("Center", this); initGUI(); sRecent = new String[iMaxRecent]; Dimension d = Toolkit.getDefaultToolkit().getScreenSize(); Dimension size = fMain.getSize(); // (ulrivo): full size on screen with less than 640 width if (d.width >= 640) { fMain.setLocation((d.width - size.width) / 2, (d.height - size.height) / 2); } else { fMain.setLocation(0, 0); fMain.setSize(d); } fMain.show(); // (ulrivo): load query from command line if (defScript != null) { if (defDirectory != null) { defScript = defDirectory + File.separator + defScript; } txtCommand.setText(DatabaseManagerCommon.readFile(defScript)); } txtCommand.requestFocus(); } /** * Method declaration * * @param b * @param name * @param items */ void addMenu(MenuBar b, String name, String items[]) { Menu menu = new Menu(name); addMenuItems(menu, items); b.add(menu); } /** * Method declaration * * @param f * @param m */ void addMenuItems(Menu f, String m[]) { for (int i = 0; i < m.length; i++) { MenuItem item = new MenuItem(m[i].substring(1)); char c = m[i].charAt(0); if (c != '-') { item.setShortcut(new MenuShortcut(c)); } item.addActionListener(this); f.add(item); } } /** * Method declaration * * @param k */ public void keyPressed(KeyEvent k) {} /** * Method declaration * * @param k */ public void keyReleased(KeyEvent k) {} /** * Method declaration * * @param k */ public void keyTyped(KeyEvent k) { if (k.getKeyChar() == '\n' && k.isControlDown()) { k.consume(); execute(); } } /** * Method declaration * * @param ev */ public void actionPerformed(ActionEvent ev) { String s = ev.getActionCommand(); if (s == null) { if (ev.getSource() instanceof MenuItem) { MenuItem i; s = ((MenuItem) ev.getSource()).getLabel(); } } if (s.equals("Execute")) { execute(); } else if (s.equals("Exit")) { windowClosing(null); } else if (s.equals("Transfer")) { Transfer.work(null); } else if (s.equals("Dump")) { Transfer.work(new String[] {"-d"}); /* NB - 26052002 Restore is not implemented yet in the transfer tool */ /* } else if (s.equals("Restore")) { Transfer.work(new String[]{"-r"}); */ } else if (s.equals("Logging on")) { jdbcSystem.setLogToSystem(true); } else if (s.equals("Logging off")) { jdbcSystem.setLogToSystem(false); } else if (s.equals("Refresh Tree")) { refreshTree(); } else if (s.startsWith("#")) { int i = Integer.parseInt(s.substring(1)); txtCommand.setText(sRecent[i]); } else if (s.equals("Connect...")) { connect(ConnectionDialog.createConnection(fMain, "Connect")); refreshTree(); } else if (s.equals("Results in Grid")) { iResult = 0; pResult.removeAll(); pResult.add("Center", gResult); pResult.doLayout(); } else if (s.equals("Open Script...")) { FileDialog f = new FileDialog(fMain, "Open Script", FileDialog.LOAD); // (ulrivo): set default directory if set from command line if (defDirectory != null) { f.setDirectory(defDirectory); } f.show(); String file = f.getFile(); if (file != null) { txtCommand.setText(DatabaseManagerCommon.readFile(f.getDirectory() + file)); } } else if (s.equals("Save Script...")) { FileDialog f = new FileDialog(fMain, "Save Script", FileDialog.SAVE); // (ulrivo): set default directory if set from command line if (defDirectory != null) { f.setDirectory(defDirectory); } f.show(); String file = f.getFile(); if (file != null) { DatabaseManagerCommon.writeFile(f.getDirectory() + file, txtCommand.getText()); } } else if (s.equals("Save Result...")) { FileDialog f = new FileDialog(fMain, "Save Result", FileDialog.SAVE); // (ulrivo): set default directory if set from command line if (defDirectory != null) { f.setDirectory(defDirectory); } f.show(); String file = f.getFile(); if (file != null) { showResultInText(); DatabaseManagerCommon.writeFile(f.getDirectory() + file, txtResult.getText()); } } else if (s.equals("Results in Text")) { iResult = 1; pResult.removeAll(); pResult.add("Center", txtResult); pResult.doLayout(); showResultInText(); } else if (s.equals("AutoCommit on")) { try { cConn.setAutoCommit(true); } catch (SQLException e) { } } else if (s.equals("AutoCommit off")) { try { cConn.setAutoCommit(false); } catch (SQLException e) { } } else if (s.equals("Enlarge Tree")) { Dimension d = tTree.getMinimumSize(); d.width += 20; tTree.setMinimumSize(d); fMain.pack(); } else if (s.equals("Shrink Tree")) { Dimension d = tTree.getMinimumSize(); d.width -= 20; if (d.width >= 0) { tTree.setMinimumSize(d); } fMain.pack(); } else if (s.equals("Enlarge Command")) { txtCommand.setRows(txtCommand.getRows() + 1); fMain.pack(); } else if (s.equals("Shrink Command")) { int i = txtCommand.getRows() - 1; txtCommand.setRows(i < 1 ? 1 : i); fMain.pack(); } else if (s.equals("Commit")) { try { cConn.commit(); } catch (SQLException e) { } } else if (s.equals("Insert test data")) { insertTestData(); } else if (s.equals("Rollback")) { try { cConn.rollback(); } catch (SQLException e) { } } else if (s.equals("Disable MaxRows")) { try { sStatement.setMaxRows(0); } catch (SQLException e) { } } else if (s.equals("Set MaxRows to 100")) { try { sStatement.setMaxRows(100); } catch (SQLException e) { } } else if (s.equals("SELECT")) { showHelp(DatabaseManagerCommon.selectHelp); } else if (s.equals("INSERT")) { showHelp(DatabaseManagerCommon.insertHelp); } else if (s.equals("UPDATE")) { showHelp(DatabaseManagerCommon.updateHelp); } else if (s.equals("DELETE")) { showHelp(DatabaseManagerCommon.deleteHelp); } else if (s.equals("CREATE TABLE")) { showHelp(DatabaseManagerCommon.createTableHelp); } else if (s.equals("DROP TABLE")) { showHelp(DatabaseManagerCommon.dropTableHelp); } else if (s.equals("CREATE INDEX")) { showHelp(DatabaseManagerCommon.createIndexHelp); } else if (s.equals("DROP INDEX")) { showHelp(DatabaseManagerCommon.dropIndexHelp); } else if (s.equals("CHECKPOINT")) { showHelp(DatabaseManagerCommon.checkpointHelp); } else if (s.equals("SCRIPT")) { showHelp(DatabaseManagerCommon.scriptHelp); } else if (s.equals("SHUTDOWN")) { showHelp(DatabaseManagerCommon.shutdownHelp); } else if (s.equals("SET")) { showHelp(DatabaseManagerCommon.setHelp); } else if (s.equals("Test Script")) { showHelp(DatabaseManagerCommon.testHelp); } } /** * Method declaration * * @param s * @param help */ void showHelp(String help[]) { txtCommand.setText(help[0]); txtResult.setText(help[1]); bHelp = true; pResult.removeAll(); pResult.add("Center", txtResult); pResult.doLayout(); txtCommand.requestFocus(); txtCommand.setCaretPosition(help[0].length()); } /** * Method declaration * * @param e */ public void windowActivated(WindowEvent e) {} /** * Method declaration * * @param e */ public void windowDeactivated(WindowEvent e) {} /** * Method declaration * * @param e */ public void windowClosed(WindowEvent e) {} /** * Method declaration * * @param ev */ public void windowClosing(WindowEvent ev) { try { cConn.close(); } catch (Exception e) { } fMain.dispose(); if (bMustExit) { System.exit(0); } } /** * Method declaration * * @param e */ public void windowDeiconified(WindowEvent e) {} /** * Method declaration * * @param e */ public void windowIconified(WindowEvent e) {} /** * Method declaration * * @param e */ public void windowOpened(WindowEvent e) {} /** Method declaration */ void execute() { String sCmd = txtCommand.getText(); if (sCmd.startsWith("-->>>TEST<<<--")) { testPerformance(); return; } String g[] = new String[1]; try { lTime = System.currentTimeMillis(); sStatement.execute(sCmd); int r = sStatement.getUpdateCount(); if (r == -1) { formatResultSet(sStatement.getResultSet()); } else { g[0] = "update count"; gResult.setHead(g); g[0] = String.valueOf(r); gResult.addRow(g); } lTime = System.currentTimeMillis() - lTime; addToRecent(txtCommand.getText()); } catch (SQLException e) { lTime = System.currentTimeMillis() - lTime; g[0] = "SQL Error"; gResult.setHead(g); String s = e.getMessage(); s += " / Error Code: " + e.getErrorCode(); s += " / State: " + e.getSQLState(); g[0] = s; gResult.addRow(g); } updateResult(); System.gc(); } /** Method declaration */ void updateResult() { if (iResult == 0) { // in case 'help' has removed the grid if (bHelp) { pResult.removeAll(); pResult.add("Center", gResult); pResult.doLayout(); bHelp = false; } gResult.update(); gResult.repaint(); } else { showResultInText(); } txtCommand.selectAll(); txtCommand.requestFocus(); } /** * Method declaration * * @param r */ void formatResultSet(ResultSet r) { if (r == null) { String g[] = new String[1]; g[0] = "Result"; gResult.setHead(g); g[0] = "(empty)"; gResult.addRow(g); return; } try { ResultSetMetaData m = r.getMetaData(); int col = m.getColumnCount(); String h[] = new String[col]; for (int i = 1; i <= col; i++) { h[i - 1] = m.getColumnLabel(i); } gResult.setHead(h); while (r.next()) { for (int i = 1; i <= col; i++) { h[i - 1] = r.getString(i); if (r.wasNull()) { h[i - 1] = "(null)"; } } gResult.addRow(h); } r.close(); } catch (SQLException e) { } } /** Method declaration */ void testPerformance() { String all = txtCommand.getText(); StringBuffer b = new StringBuffer(); long total = 0; for (int i = 0; i < all.length(); i++) { char c = all.charAt(i); if (c != '\n') { b.append(c); } } all = b.toString(); String g[] = new String[4]; g[0] = "ms"; g[1] = "count"; g[2] = "sql"; g[3] = "error"; gResult.setHead(g); int max = 1; lTime = System.currentTimeMillis() - lTime; while (!all.equals("")) { int i = all.indexOf(';'); String sql; if (i != -1) { sql = all.substring(0, i); all = all.substring(i + 1); } else { sql = all; all = ""; } if (sql.startsWith("--#")) { max = Integer.parseInt(sql.substring(3)); continue; } else if (sql.startsWith("--")) { continue; } g[2] = sql; long l = 0; try { l = DatabaseManagerCommon.testStatement(sStatement, sql, max); total += l; g[0] = String.valueOf(l); g[1] = String.valueOf(max); g[3] = ""; } catch (SQLException e) { g[0] = g[1] = "n/a"; g[3] = e.toString(); } gResult.addRow(g); System.out.println(l + " ms : " + sql); } g[0] = "" + total; g[1] = "total"; g[2] = ""; gResult.addRow(g); lTime = System.currentTimeMillis() - lTime; updateResult(); } /** Method declaration */ void showResultInText() { String col[] = gResult.getHead(); int width = col.length; int size[] = new int[width]; Vector data = gResult.getData(); String row[]; int height = data.size(); for (int i = 0; i < width; i++) { size[i] = col[i].length(); } for (int i = 0; i < height; i++) { row = (String[]) data.elementAt(i); for (int j = 0; j < width; j++) { int l = row[j].length(); if (l > size[j]) { size[j] = l; } } } StringBuffer b = new StringBuffer(); for (int i = 0; i < width; i++) { b.append(col[i]); for (int l = col[i].length(); l <= size[i]; l++) { b.append(' '); } } b.append(NL); for (int i = 0; i < width; i++) { for (int l = 0; l < size[i]; l++) { b.append('-'); } b.append(' '); } b.append(NL); for (int i = 0; i < height; i++) { row = (String[]) data.elementAt(i); for (int j = 0; j < width; j++) { b.append(row[j]); for (int l = row[j].length(); l <= size[j]; l++) { b.append(' '); } } b.append(NL); } b.append(NL + height + " row(s) in " + lTime + " ms"); txtResult.setText(b.toString()); } /** * Method declaration * * @param s */ private void addToRecent(String s) { for (int i = 0; i < iMaxRecent; i++) { if (s.equals(sRecent[i])) { return; } } if (sRecent[iRecent] != null) { mRecent.remove(iRecent); } sRecent[iRecent] = s; if (s.length() > 43) { s = s.substring(0, 40) + "..."; } MenuItem item = new MenuItem(s); item.setActionCommand("#" + iRecent); item.addActionListener(this); mRecent.insert(item, iRecent); iRecent = (iRecent + 1) % iMaxRecent; } /** Method declaration */ private void initGUI() { Panel pQuery = new Panel(); Panel pCommand = new Panel(); pResult = new Panel(); pQuery.setLayout(new BorderLayout()); pCommand.setLayout(new BorderLayout()); pResult.setLayout(new BorderLayout()); Font fFont = new Font("Dialog", Font.PLAIN, 12); txtCommand = new TextArea(5, 40); txtCommand.addKeyListener(this); txtResult = new TextArea(20, 40); txtCommand.setFont(fFont); txtResult.setFont(new Font("Courier", Font.PLAIN, 12)); butExecute = new Button("Execute"); butExecute.addActionListener(this); pCommand.add("East", butExecute); pCommand.add("Center", txtCommand); gResult = new Grid(); setLayout(new BorderLayout()); pResult.add("Center", gResult); pQuery.add("North", pCommand); pQuery.add("Center", pResult); fMain.add("Center", pQuery); tTree = new Tree(); // (ulrivo): screen with less than 640 width Dimension d = Toolkit.getDefaultToolkit().getScreenSize(); if (d.width >= 640) { tTree.setMinimumSize(new Dimension(200, 100)); } else { tTree.setMinimumSize(new Dimension(80, 100)); } gResult.setMinimumSize(new Dimension(200, 300)); fMain.add("West", tTree); doLayout(); fMain.pack(); } /** Method declaration */ private void refreshTree() { tTree.removeAll(); try { int color_table = Color.yellow.getRGB(); int color_column = Color.orange.getRGB(); int color_index = Color.red.getRGB(); tTree.addRow("", dMeta.getURL(), "-", 0); String usertables[] = {"TABLE"}; ResultSet result = dMeta.getTables(null, null, null, usertables); Vector tables = new Vector(); // sqlbob@users Added remarks. Vector remarks = new Vector(); while (result.next()) { tables.addElement(result.getString(3)); remarks.addElement(result.getString(5)); } result.close(); for (int i = 0; i < tables.size(); i++) { String name = (String) tables.elementAt(i); String key = "tab-" + name + "-"; tTree.addRow(key, name, "+", color_table); // sqlbob@users Added remarks. String remark = (String) remarks.elementAt(i); if ((remark != null) && !remark.trim().equals("")) { tTree.addRow(key + "r", " " + remark); } ResultSet col = dMeta.getColumns(null, null, name, null); while (col.next()) { String c = col.getString(4); String k1 = key + "col-" + c + "-"; tTree.addRow(k1, c, "+", color_column); String type = col.getString(6); tTree.addRow(k1 + "t", "Type: " + type); boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls; tTree.addRow(k1 + "n", "Nullable: " + nullable); } col.close(); tTree.addRow(key + "ind", "Indices", "+", 0); ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false); String oldiname = null; while (ind.next()) { boolean nonunique = ind.getBoolean(4); String iname = ind.getString(6); String k2 = key + "ind-" + iname + "-"; if ((oldiname == null || !oldiname.equals(iname))) { tTree.addRow(k2, iname, "+", color_index); tTree.addRow(k2 + "u", "Unique: " + !nonunique); oldiname = iname; } String c = ind.getString(9); tTree.addRow(k2 + "c-" + c + "-", c); } ind.close(); } tTree.addRow("p", "Properties", "+", 0); tTree.addRow("pu", "User: "******"pr", "ReadOnly: " + cConn.isReadOnly()); tTree.addRow("pa", "AutoCommit: " + cConn.getAutoCommit()); tTree.addRow("pd", "Driver: " + dMeta.getDriverName()); tTree.addRow("pp", "Product: " + dMeta.getDatabaseProductName()); tTree.addRow("pv", "Version: " + dMeta.getDatabaseProductVersion()); } catch (SQLException e) { tTree.addRow("", "Error getting metadata:", "-", 0); tTree.addRow("-", e.getMessage()); tTree.addRow("-", e.getSQLState()); } tTree.update(); } }
public class MyLittleDatabase { private static Connection connection; private static final String DB_PATH = System.getProperty("user.home") + "/" + "scrollsdb.db"; private static MyLittleDatabase instance; static { try { Class.forName("org.sqlite.JDBC"); } catch (ClassNotFoundException e) { System.err.println("error while loading the JDBC-driver"); e.printStackTrace(); } } public static MyLittleDatabase getInstance() { if (instance == null) { instance = new MyLittleDatabase(); } return instance; } private MyLittleDatabase() { this.initDBConnection(); } private void initDBConnection() { try { if (connection != null) return; System.out.println("Creating Connection to Database..."); connection = DriverManager.getConnection("jdbc:sqlite:" + DB_PATH); if (!connection.isClosed()) System.out.println("...Connection established"); } catch (SQLException e) { throw new RuntimeException(e); } Runtime.getRuntime() .addShutdownHook( new Thread() { public void run() { try { if (!connection.isClosed() && connection != null) { connection.close(); if (connection.isClosed()) System.out.println("Connection to Database closed"); } } catch (SQLException e) { e.printStackTrace(); } } }); String sql = "create table if not exists players (name string, gold int, shards int, admin int, featuretype int, spectate int, trade int, challenge int, rating int, head int, body int, leg int, armback int, armfront int, idoltype string, idol1 int, idol2 int, idol3 int, idol4 int, idol5 int);"; try { Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); stmt.close(); sql = "CREATE INDEX IF NOT EXISTS nameidx ON players (name);"; stmt = connection.createStatement(); stmt.executeUpdate(sql); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } sql = "create table if not exists cards ( typeId int, owner int, tradeable int, level int);"; try { Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); stmt.close(); sql = "CREATE INDEX IF NOT EXISTS cardowneridx ON cards (owner);"; stmt = connection.createStatement(); stmt.executeUpdate(sql); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } sql = "create table if not exists decks ( name string, owner int, resources string, valid int, timestamp int, cardids string);"; try { Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); stmt.close(); sql = "CREATE INDEX IF NOT EXISTS deckowneridx ON decks (owner);"; stmt = connection.createStatement(); stmt.executeUpdate(sql); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } public synchronized long getFreeIDPlayer() { // dont use that! String sql = "SELECT rowid FROM players;"; long i = -1; try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(sql); long max = 1; while (rs.next()) { long m = rs.getLong(1); if (max <= m) max = m + 1; } i = max; stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } public long insertPlayer(String name) { long id = -1; String sql = "INSERT INTO players ( name, gold, shards, admin , featuretype , spectate , trade , challenge , rating , head, body, leg, armback, armfront, idoltype, idol1, idol2, idol3, idol4, idol5) " + "VALUES ( '" + name + "', 2000, 0, 0, 0, 1, 1, 1, 1000, 37, 11, 40, 1, 17, 'DEFAULT', 2, 2, 2, 2, 2);"; try { connection.setAutoCommit(false); // Starts transaction. Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()"); rs.next(); id = rs.getLong(1); stmt.close(); connection.commit(); } catch (SQLException e) { e.printStackTrace(); } if (id != -1) { addDefaultDecks(id); } return id; } private void addDefaultDecks(long playerid) { ArrayList<String> toadd = new ArrayList<String>(); String deck1 = "{\"deck\":\"Growth Preconstructed\",\"author\":\"Mojang\",\"types\":[56,91,40,100,115,44,100,45,44,49,94,84,89,38,78,75,49,22,42,156,53,156,88,88,84,63,63,18,89,18,17,40,21,21,22,65,91,40,17,26,26,16,16,75,41,115,13,53,114,13]}"; toadd.add(deck1); deck1 = "{\"deck\":\"Energy Preconstructed\",\"author\":\"Mojang\",\"types\":[152,27,152,151,143,39,77,2,151,87,96,96,98,98,139,96,141,98,141,141,2,139,130,80,80,24,107,35,68,82,77,24,149,27,118,83,107,24,35,68,1,67,149,67,150,83,87,66,66,32]}"; toadd.add(deck1); deck1 = "{\"deck\":\"Order Preconstructed\",\"author\":\"Mojang\",\"types\":[97,124,95,20,125,73,125,127,124,71,86,123,126,54,123,122,120,64,50,50,37,74,97,105,71,122,86,73,126,36,120,99,99,128,128,23,101,95,61,52,124,15,121,52,48,123,127,50,20,23]}"; toadd.add(deck1); deck1 = "{\"deck\":\"Decay Preconstructed\",\"author\":\"Mojang\",\"types\":[3,3,59,69,69,102,102,162,163,163,164,165,165,166,170,170,174,175,176,176,176,179,179,179,180,184,184,185,186,187,191,191,191,192,192,192,194,195,195,198,198,198,200,200,201,201,202,202,239,239]}"; toadd.add(deck1); deck1 = "{\"deck\":\"EnergySet1and2\",\"author\":\"Mojang\",\"types\":[24,27,35,39,47,66,68,70,79,80,83,85,87,90,96,98,108,110,118,140,141,142,143,147,148,149,150,151,152,153]}"; toadd.add(deck1); deck1 = "{\"deck\":\"GrowthSet1and2\",\"author\":\"Mojang\",\"types\":[4,13,16,17,18,29,34,38,40,41,43,44,49,56,60,63,76,84,88,89,91,94,100,112,113,114,115,129,154,189]}"; toadd.add(deck1); deck1 = "{\"deck\":\"OrderSet1and2\",\"author\":\"Mojang\",\"types\":[15,19,20,23,36,37,48,52,54,55,57,61,71,73,86,93,97,99,101,103,109,111,119,120,124,125,126,127,128,158]}"; toadd.add(deck1); deck1 = "{\"deck\":\"DecaySet1and2\",\"author\":\"Mojang\",\"types\":[59,69,102,131,137,159,162,164,165,168,169,170,171,172,174,176,177,180,181,182,184,185,190,191,194,195,197,198,199,202]}"; toadd.add(deck1); deck1 = "{\"deck\":\"Set3\",\"author\":\"Mojang\",\"types\":[1,2,25,32,67,77,81,82,107,130,138,139,145,146,160,21,22,26,30,33,42,45,53,58,65,75,78,104,117,156,50,51,62,64,74,92,95,105,116,121,122,123,155,157,193,3,161,163,166,173,175,179,183,186,187,188,192,196,200,201]}"; toadd.add(deck1); deck1 = "{\"deck\":\"Set4\",\"author\":\"Mojang\",\"types\":[135,203,208,211,212,213,214,215,216,217,218,219,221,222,257,134,178,206,209,228,229,230,231,232,234,237,238,240,254,133,204,205,207,210,223,224,225,226,227,235,236,241,256,258,239,242,243,244,245,246,247,248,249,250,251,252,253,255,259]}"; toadd.add(deck1); deck1 = "{\"deck\":\"Set5\",\"author\":\"Mojang\",\"types\":[261,289,290,291,292,293,294,295,296,297,302,303,304,307,310,263,273,274,275,280,298,305,306,308,311,312,313,314,315,316,260,264,265,266,267,268,269,270,271,272,276,278,279,309,319,262,277,281,282,283,284,285,286,287,288,299,300,301,317,318]}"; toadd.add(deck1); deck1 = "{\"deck\":\"Set6\",\"author\":\"Mojang\",\"types\":[332,342,343,344,345,349,350,351,352,353,362,380,381,382,540,322,339,340,341,347,363,364,365,366,367,368,369,379,383,385,320,333,337,338,346,354,355,356,357,358,359,360,361,384,541,321,334,335,336,348,370,371,372,373,374,375,376,377,378,542]}"; toadd.add(deck1); // test area targeting! // deck1 = // "{\"deck\":\"testing\",\"author\":\"Mojang\",\"types\":[25,246,244,244,245,343,343,343,343,343,343,343]}"; // toadd.add(deck1); for (String s : toadd) { addDeckANDCards(s, playerid); } } public void addDeck(String deckname, long playerid, String cardids) { // TODO: insert correct ressources :D Long l = System.currentTimeMillis() / 1000L; String sql = "INSERT OR IGNORE INTO decks ( name , owner , resources , valid , timestamp , cardids ) " + "VALUES ('" + deckname + "', " + playerid + ", 'GROWTH', 1," + l.intValue() + ",'" + cardids + "');"; sql += " UPDATE decks SET resources = 'GROWTH', valid = 1, timestamp = " + l.intValue() + ", cardids = '" + cardids + "'"; sql += " WHERE name ='" + deckname + "' AND owner = " + playerid; System.out.println(sql); Statement stmt; try { connection.setAutoCommit(false); // Starts transaction. stmt = connection.createStatement(); stmt.executeUpdate(sql); stmt.close(); connection.commit(); } catch (SQLException e) { e.printStackTrace(); } } private void addDeckANDCards(String deckstring, long playerid) { JSONObject jo = new JSONObject(deckstring); String deckname = jo.getString("deck"); JSONArray arrr = jo.getJSONArray("types"); String deckids = ""; ArrayList<Integer> toadd = new ArrayList<Integer>(); int j = 0; Statement stmt; try { connection.setAutoCommit(false); // ONLY FOR addNewCardNoClose stmt = connection.createStatement(); // ONLY FOR addNewCardNoClose for (int i = 0; i < arrr.length(); i++) { int typeid = arrr.getInt(i); /* toadd.add(typeid); j++; if(j>=100 || i == arrr.length()-1 ) { ArrayList<Integer> cardids = addNewCards(toadd, playerid); for(int iii : cardids) { if(!deckids.equals(""))deckids += ","; deckids += iii +""; System.out.print(iii); System.out.print(","); } toadd.clear(); j=0; }*/ if (!deckids.equals("")) deckids += ","; deckids += addNewCardNoClose(typeid, playerid, stmt) + ""; } stmt.close(); // ONLY FOR addNewCardNoClose connection.commit(); // ONLY FOR addNewCardNoClose } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } // now we have to add the deck Long l = System.currentTimeMillis() / 1000L; String sql = "INSERT INTO decks ( name , owner , resources , valid , timestamp , cardids ) " + "VALUES ('" + deckname + "', " + playerid + ", 'GROWTH', 1," + l.intValue() + ",'" + deckids + "');"; int deckid = -1; try { connection.setAutoCommit(false); // Starts transaction. stmt = connection.createStatement(); stmt.executeUpdate(sql); /*ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()"); rs.next(); deckid = rs.getInt(1);*/ stmt.close(); connection.commit(); } catch (SQLException e) { e.printStackTrace(); } } public long addNewCardNoClose(int typeid, long owner, Statement stmt) { long id = -1; String sql = "INSERT INTO cards ( typeId , owner , tradeable , level ) " + "VALUES (" + typeid + ", " + owner + ", 0, 0);"; try { stmt.executeUpdate(sql); ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()"); rs.next(); id = rs.getLong(1); } catch (SQLException e) { e.printStackTrace(); } return id; } public long addNewCard(int typeid, int owner) { long id = -1; String sql = "INSERT INTO cards ( typeId , owner , tradeable , level ) " + "VALUES (" + typeid + ", " + owner + ", 0, 0);"; try { /*Statement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); stmt.executeUpdate(sql); ResultSet rs = stmt.getGeneratedKeys(); rs.next(); id = rs.getInt(1); stmt.close();*/ connection.setAutoCommit(false); // Starts transaction. Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()"); rs.next(); id = rs.getLong(1); stmt.close(); connection.commit(); } catch (SQLException e) { e.printStackTrace(); } return id; } public ArrayList<Long> addNewCards(ArrayList<Integer> typeids, int owner) { // doesnt work properly... long id = -1; String sql = "INSERT INTO cards ( typeId , owner , tradeable , level ) VALUES "; boolean added = false; for (Integer iii : typeids) { if (added) sql += ","; sql += "(" + iii + ", " + owner + ", 0, 0)"; added = true; } System.out.println(sql); ArrayList<Long> cardids = new ArrayList<Long>(); try { /*Statement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); stmt.executeUpdate(sql); ResultSet rs = stmt.getGeneratedKeys(); rs.next(); id = rs.getInt(1); stmt.close();*/ connection.setAutoCommit(false); // Starts transaction. Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()"); while (rs.next()) { id = rs.getLong(1); cardids.add(id); } stmt.close(); connection.commit(); } catch (SQLException e) { e.printStackTrace(); } return cardids; } public Player getPlayerbyName(String name) { Player p = new Player(); Statement stmt; boolean added = false; try { stmt = connection.createStatement(); String sql = "SELECT rowid,* FROM players Where name= '" + name + "' ;"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { System.out.println("in db" + rs.getLong(1) + " " + rs.getString(2)); added = true; p.profileId = rs.getLong(1); p.name = rs.getString(2); p.gold = rs.getInt(3); p.shards = rs.getInt(4); p.admin = rs.getInt(5); p.featureType = rs.getInt(6); p.spectatePermission = rs.getInt(7); p.acceptTrades = rs.getInt(8); p.acceptChallenges = rs.getInt(9); p.rating = rs.getInt(10); p.head = rs.getInt(11); p.body = rs.getInt(12); p.leg = rs.getInt(13); p.armBack = rs.getInt(14); p.armFront = rs.getInt(15); p.idolType = rs.getString(16); p.idol1 = rs.getInt(17); p.idol2 = rs.getInt(18); p.idol3 = rs.getInt(19); p.idol4 = rs.getInt(20); p.idol5 = rs.getInt(21); break; } stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if (added == false) { long id = insertPlayer(name); p.name = name; p.profileId = id; System.out.println("new player was added: " + id); } return p; } public ArrayList<Deck> getDecksFromPlayerID(long id) { ArrayList<Deck> ds = new ArrayList<Deck>(); String retval = ""; Statement stmt; try { stmt = connection.createStatement(); String sql = "SELECT rowid,* FROM decks Where owner= " + id + ";"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { Deck d = new Deck(); d.deckname = rs.getString(2); d.playerowner = rs.getLong(3); d.ressis = rs.getString(4); d.valid = rs.getInt(5); d.timestamp = rs.getLong(6); String cs = rs.getString(7); for (String c : cs.split(",")) { try { d.cardIds.add(Integer.parseInt(c)); } catch (NumberFormatException nfe) { } } ds.add(d); } stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return ds; } public ArrayList<SmallCard> getCardsFromPlayerID(long id) { ArrayList<SmallCard> ds = new ArrayList<SmallCard>(); String retval = ""; Statement stmt; try { stmt = connection.createStatement(); String sql = "SELECT rowid,* FROM cards Where owner= " + id + ";"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { SmallCard d = new SmallCard(); // ( typeId int, owner int, tradeable int, level int) d.cardid = rs.getLong(1); d.typeid = rs.getInt(2); d.owner = rs.getLong(3); d.tradeable = rs.getInt(4); d.level = rs.getInt(5); ds.add(d); } stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return ds; } public ArrayList<Minion> getDeckFromPlayer(String deckname, long playerid, boolean iswhite) { ArrayList<Minion> deck = new ArrayList<Minion>(); Statement stmt; ArrayList<SmallCard> cards = new ArrayList<SmallCard>(); try { stmt = connection.createStatement(); String sql = "SELECT * FROM decks Where owner= " + playerid + " AND name = '" + deckname + "';"; ResultSet rs = stmt.executeQuery(sql); String cs = ""; if (rs.next()) { cs = rs.getString(6); } String inlist = "("; boolean added = false; for (String c : cs.split(",")) { if (added) inlist += ","; inlist += c; added = true; } inlist += ")"; sql = "SELECT rowid,* FROM cards Where owner= " + playerid + " AND rowid IN " + inlist + ";"; rs = stmt.executeQuery(sql); while (rs.next()) { SmallCard d = new SmallCard(); // ( typeId int, owner int, tradeable int, level int) d.cardid = rs.getLong(1); d.typeid = rs.getInt(2); d.owner = rs.getLong(3); d.tradeable = rs.getInt(4); d.level = rs.getInt(5); cards.add(d); } stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } CardDB cdb = CardDB.getInstance(); UColor colw = UColor.black; if (iswhite) colw = UColor.white; for (SmallCard sc : cards) { Minion m = new Minion(cdb.cardId2Card.get(sc.typeid), sc.cardid, colw); m.lvl = sc.level; deck.add(m); } return deck; } // String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;"; }
// // -------------------------------------------------------------------------------------------------------------------- // Class: RFFieldSQLDatabase // Description: RFFieldSQLDatabase interfaces SQL Database, supplies methods to access // data and passes JSON to/from calling classes. // // -------------------------------------------------------------------------------------------------------------------- public class RFFieldSQLDatabase { // Define class members private Connection conn = null; // MySql Database connection private static final String APPLICATION_NAME = "ECEN689Project1"; // Set the Application Name // Directory to store user credentials for this // application. private static final java.io.File DATA_STORE_DIR = new java.io.File(System.getProperty("user.dir"), ".store/ecen689project1"); private static FileDataStoreFactory DATA_STORE_FACTORY; // Global instance of the {@link FileDataStoreFactory}. private static HttpTransport HTTP_TRANSPORT; // Global instance of the HTTP transport // Global instance of the JSON factory. private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance(); private static Fusiontables FusionTables; // Global instance of the Fusion Tables // ----------------------------------------------------------------------------------------------------------------- // Create new instances of HTTP_TRANSPORT static // { // and DATA_STORE_FACTORY try // { // HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport(); DATA_STORE_FACTORY = new FileDataStoreFactory(DATA_STORE_DIR); } // catch (Throwable t) // { // t.printStackTrace(); // System.exit(1); // } // } // // ---------------------------------------------------------------------------------------------------------------- // Method: authorize // Inputs: none // Outputs: Credentials // Description: Creates an authorized credential object, will throw IO exception // ---------------------------------------------------------------------------------------------------------------- private static Credential authorize() throws IOException { // Load client secrets (from JSON file) InputStream in = Main.class.getResourceAsStream("/client_secret_ecen689project1.json"); GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in)); // Build flow and trigger user authorization request GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder( HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, Collections.singleton(FusiontablesScopes.FUSIONTABLES)) .setDataStoreFactory(DATA_STORE_FACTORY) .build(); // Authorize and get the credential Credential credential = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user"); System.out.println("Credentials saved to " + DATA_STORE_DIR.getAbsolutePath()); return credential; // Return the credential } // ---------------------------------------------------------------------------------------------------------------- // Method: getConnection // Inputs: database URL, username, password // Outputs: connection to DB // Description: Connects to MySql database // ---------------------------------------------------------------------------------------------------------------- private static Connection getConnection(String dbURL, String user, String password) throws SQLException, ClassNotFoundException { Class.forName("com.mysql.jdbc.Driver"); // Setup for the MySql JDBC Driver Properties props = new Properties(); // Build the properties props.put("user", user); props.put("password", password); props.put("autoReconnect", "true"); // Enabled auto-reconnection return DriverManager.getConnection(dbURL, props); // Return the connection to the database } // ---------------------------------------------------------------------------------------------------------------- // Method: Class Construction // Inputs: none // Outputs: none // Description: Class Construction // // ---------------------------------------------------------------------------------------------------------------- public RFFieldSQLDatabase() {} // ---------------------------------------------------------------------------------------------------------------- // Method: ConnectToDatabase // Inputs: none // Outputs: Success = TRUE / Failure = FALSE // Description: Establishes a connection to the database // ---------------------------------------------------------------------------------------------------------------- public boolean ConnectToDatabase(String host_address, boolean fusion_tables) { boolean status; // Return status (success / failure) if (fusion_tables == true) // Only create a connection to FT if the { // flag is set to true try { // Google Fusion Tables Authorization Credential credential = authorize(); // Client authorization // Set up global FusionTables instance FusionTables = new Fusiontables.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential) .setApplicationName(APPLICATION_NAME) .build(); } // catch (IOException e) // Print out exception messages { // System.err.println(e.getMessage()); // } // catch (Throwable t) // { // t.printStackTrace(); // } // } try // Try and connect to the MySQL Database { // Use ECEN_RF_Field Database System.out.println("Attempting Connection"); // Print out connection success // conn = getConnection("jdbc:mysql://69.5.199.164/ECEN_RF_Fields", "ecen689", // "ecen689$2016"); // conn = getConnection("jdbc:mysql://10.202.102.173/ECEN_RF_Fields", "ecen689", // "ecen689$2016"); conn = getConnection( "jdbc:mysql://" + host_address + "/ECEN_RF_Fields", "ecen689", "ecen689$2016"); System.out.println("Opened database successfully"); // Print out connection success status = true; // Success } // catch (Exception e) // Exception processing: { // System.err.println( "ConnectToDatabase: " + e.getMessage()); // Print the exception data and exit status = false; // Failure } // return status; // Return status } // ---------------------------------------------------------------------------------------------------------------- // Method: DisconnectDatabase // Inputs: none // Outputs: Success = TRUE / Failure = FALSE // Description: Disconnects the connection to the database // ---------------------------------------------------------------------------------------------------------------- public boolean DisconnectDatabase() { boolean status; // Return status (success / failure) try // Try and connect to the MySQL Database { // if (conn != null) conn.close(); // Close the connection if defined System.out.println("Database connection closed"); // Print out status status = true; // Success } // catch (Exception e) // Exception processing: { // Print the exception data and exit System.err.println("DisconnectDatabase: " + e.getMessage()); status = false; // Failure } // return status; // Return status } // ---------------------------------------------------------------------------------------------------------------- // Method: GetTableId // Inputs: none // Outputs: none // Description: Get Table ID from Table Nam // ---------------------------------------------------------------------------------------------------------------- private static String GetTableId(String tableName) throws IOException { String tid = null; // Set default return System.out.println("Get Table ID: " + tableName); // Print header // Get the table list Fusiontables.Table.List listTables = FusionTables.table().list(); TableList tablelist = listTables.execute(); // // If there are no tables, print that info if (tablelist.getItems() == null || tablelist.getItems().isEmpty()) { // System.out.println("No tables found!"); // } // else // Else, loop through tables, find match { // If it matches then save the table ID for (Table table : tablelist.getItems()) // { // if (table.getName().equals(tableName)) tid = table.getTableId(); } // Printout the results of that System.out.println(tableName + " - tableId: " + tid); // } // return tid; // Return the table ID } // ---------------------------------------------------------------------------------------------------------------- // Method: AddNewEntry // Inputs: RF Data Entry (JSON) // Outputs: Success = TRUE / Failure = FALSE // Description: Insert new data to table (executes SQL command) // ---------------------------------------------------------------------------------------------------------------- public boolean AddNewEntry(String json, boolean fusion_tables) { boolean status; // Return status (success / failure) try // Try to get JSON, and save data to database { // Gson gson = new GsonBuilder().create(); // Create Gson builder RFData RFMember = gson.fromJson(json, RFData.class); // Convert from JSON to RFData if (RFMember.XbeeID != -1) // If not default then save data { // Print debug information to port System.out.println( "Insert New RF Data into Table - XbeeID: " + RFMember.XbeeID + ", RSSI: " + RFMember.RSSI); SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String sql_string; // Build up SQL string sql_string = "INSERT INTO RF_Fields ("; // Insert SQL statement, Table: RF_Fields sql_string += "intXbeeID,"; // Field: intXbeeID sql_string += "intDeviceID,"; // Field: intDeviceID sql_string += "fltRSSI,"; // Field: fltRSSI sql_string += "fltLatitude,"; // Field: fltLatitude sql_string += "fltLongitude,"; // Field: fltLongitude sql_string += "fltYaw,"; // Field: fltYaw sql_string += "fltPitch,"; // Field: fltPitch sql_string += "fltRoll,"; // Field: fltRoll sql_string += "dtSampleDate) "; // Field: dtSampleDate sql_string += "VALUES ("; // Values indetifier sql_string += RFMember.XbeeID + ","; // Value: XbeeID sql_string += RFMember.DeviceID + ","; // Value: DeviceID sql_string += RFMember.RSSI + ","; // Value: RSSI sql_string += RFMember.Latitude + ","; // Value: Latitude sql_string += RFMember.Longitude + ","; // Value: Longitude sql_string += RFMember.Yaw + ","; // Value: Yaw sql_string += RFMember.Pitch + ","; // Value: Pitch sql_string += RFMember.Roll + ","; // Value: Roll sql_string += "'" + ft.format(RFMember.SampleDate) + "')"; System.out.println("SQL: " + sql_string); // Debug print the SQL statement // Statement stmt = conn.createStatement(); // Build SQL statement stmt.execute(sql_string); // Execute the SQL statement stmt.close(); // Close the statement status = true; // Success if (fusion_tables) // Only add to the fusion tables if the flag { // is set // Add to fusion table String tableId = GetTableId("RF Field Data"); // Get the "Customer Data" Table sql_string = "INSERT INTO " + tableId + " ("; // Insert SQL statement, Table: Table ID sql_string += "XbeeID,"; // Field: intXbeeID sql_string += "DeviceID,"; // Field: intDeviceID sql_string += "RSSI,"; // Field: fltRSSI sql_string += "Location,"; // Field: fltLatitude sql_string += "Longitude,"; // Field: fltLongitude sql_string += "Yaw,"; // Field: fltYaw sql_string += "Pitch,"; // Field: fltPitch sql_string += "Roll,"; // Field: fltRoll sql_string += "SampleDate) "; // Field: dtSampleDate sql_string += "VALUES ("; // Values indetifier sql_string += RFMember.XbeeID + ","; // Value: XbeeID sql_string += RFMember.DeviceID + ","; // Value: DeviceID sql_string += RFMember.RSSI + ","; // Value: RSSI sql_string += RFMember.Latitude + ","; // Value: Latitude sql_string += RFMember.Longitude + ","; // Value: Longitude sql_string += RFMember.Yaw + ","; // Value: Yaw sql_string += RFMember.Pitch + ","; // Value: Pitch sql_string += RFMember.Roll + ","; // Value: Roll sql_string += "'" + ft.format(RFMember.SampleDate) + "')"; Sql sql = FusionTables.query().sql(sql_string); // Build Fusion Query // Try and execute the SQL command try // { // sql.executeAndDownloadTo(System.out); // Execute command, stream to the system.out } // catch (IllegalArgumentException e) // { // } // } } // else // { // System.err.println("AddNewEntry: Invalid JSON data"); // Print the exception data and exit status = false; // Failure, invalid JSON or data } } // catch (Exception e) // Exception processing: { // System.err.println("AddNewEntry: " + e.getMessage()); // Print the exception data and exit status = false; // Failure } // return status; // Return status } // ---------------------------------------------------------------------------------------------------------------- // Method: ReadRecords // Inputs: Result Set and Array List // Outputs: (by ref) Array List // Description: Read the Records, Fill Array List with JSON data // ---------------------------------------------------------------------------------------------------------------- public void ReadRecords(ResultSet rs, ArrayList records) { Gson gson = new GsonBuilder().create(); // Create Gson builder try // Try to get JSON, and save data to database { // while (rs.next()) // Loop through all the returned records, until EOF { // RFData RFMember = new RFData(); // Create new RF data // Capture the data from the record set RFMember.SampleNumber = rs.getInt("intSampleNum"); // Get sample # RFMember.XbeeID = rs.getInt("intXbeeID"); // Get Xbee ID RFMember.DeviceID = rs.getInt("intDeviceID"); // Get Device ID RFMember.RSSI = rs.getFloat("fltRSSI"); // Get RSSI RFMember.Latitude = rs.getFloat("fltLatitude"); // Get Latitude RFMember.Longitude = rs.getFloat("fltLongitude"); // Get Longitude RFMember.Yaw = rs.getFloat("fltYaw"); // Get Yaw RFMember.Pitch = rs.getFloat("fltPitch"); // Get Pitch RFMember.Roll = rs.getFloat("fltRoll"); // Get Roll RFMember.SampleDate = rs.getTimestamp("dtSampleDate"); // Get Sample Date // Print debug information to port SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); System.out.println( "Got Record: # " + RFMember.SampleNumber + " - XbeeID: " + RFMember.XbeeID + ", RSSI: " + RFMember.RSSI + " Date/Time: " + ft.format(RFMember.SampleDate)); String json_record = gson.toJson(RFMember); // Get JSON data of this record records.add(json_record); // Add the JSON string to the record } } catch (Exception e) { System.err.println("ReadRecords: " + e.getMessage()); // Print the exception data and exit } } // ---------------------------------------------------------------------------------------------------------------- // Method: ListDataByEntryID // Inputs: SampleNum // Outputs: RF Data Entry (JSON) // Description: Get RF Data Entry // ---------------------------------------------------------------------------------------------------------------- public String ListDataByEntryID(int SampleNum) { String json = ""; // Return JSON entry try // Try to get JSON, and save data to database { // // Print debug information to port System.out.println("Select RF Data from Table - Sample #: " + SampleNum); String sql_string; // Build up SQL string sql_string = "SELECT "; // Select SQL statement sql_string += "intSampleNum,"; // Field: intSampleNum sql_string += "intXbeeID,"; // Field: intXbeeID sql_string += "intDeviceID,"; // Field: intDeviceID sql_string += "fltRSSI,"; // Field: fltRSSI sql_string += "fltLatitude,"; // Field: fltLatitude sql_string += "fltLongitude,"; // Field: fltLongitude sql_string += "fltYaw,"; // Field: fltYaw sql_string += "fltPitch,"; // Field: fltPitch sql_string += "fltRoll,"; // Field: fltRoll sql_string += "dtSampleDate "; // Field: dtSampleDate sql_string += "FROM RF_Fields "; // Table: RF_Fields sql_string += "WHERE "; // Where statement sql_string += "intSampleNum = "; // Field on Where and condition sql_string += SampleNum; // Condition value System.out.println("SQL: " + sql_string); // Debug print the SQL statement Statement stmt = conn.createStatement(); // Build SQL statement ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query while (rs.next()) // Loop through all the returned records, until EOF { // However, there should only be one return record Gson gson = new GsonBuilder().create(); // Create Gson builder RFData RFMember = new RFData(); // Create new RF data // Capture the data from the record set RFMember.SampleNumber = rs.getInt("intSampleNum"); // Get sample # RFMember.XbeeID = rs.getInt("intXbeeID"); // Get Xbee ID RFMember.DeviceID = rs.getInt("intDeviceID"); // Get Device ID RFMember.RSSI = rs.getFloat("fltRSSI"); // Get RSSI RFMember.Latitude = rs.getFloat("fltLatitude"); // Get Latitude RFMember.Longitude = rs.getFloat("fltLongitude"); // Get Longitude RFMember.Yaw = rs.getFloat("fltYaw"); // Get Yaw RFMember.Pitch = rs.getFloat("fltPitch"); // Get Pitch RFMember.Roll = rs.getFloat("fltRoll"); // Get Roll RFMember.SampleDate = rs.getTimestamp("dtSampleDate"); // Get Sample Date // Print debug information to port SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); System.out.println( "Got Record: # " + RFMember.SampleNumber + " - XbeeID: " + RFMember.XbeeID + ", RSSI: " + RFMember.RSSI + " Date/Time: " + ft.format(RFMember.SampleDate)); json = gson.toJson(RFMember); // Get JSON data from this record } rs.close(); // Close the record set stmt.close(); // Close the statement if (json == "") // { // System.err.println("No Record Found!"); // Print the fact that no record was found } } // catch (Exception e) // Exception processing: { // System.err.println( "ListDataByEntryID: " + e.getMessage()); // Print the exception data and exit } // return json; // Return JSON string } // ---------------------------------------------------------------------------------------------------------------- // Method: ListDataByDevice // Inputs: DeviceID // Outputs: RF Data Entry (JSON) Collection // Description: Get RF Data Entries // ---------------------------------------------------------------------------------------------------------------- public String ListDataByDevice(int DeviceID) { String json = ""; // Return JSON entry try // Try to get JSON, and save data to database { // // Print debug information to port Gson gson = new GsonBuilder().create(); // Create Gson builder System.out.println("Select RF Data from Table - Device ID: " + DeviceID); String sql_string; // Build up SQL string sql_string = "SELECT "; // Select SQL statement sql_string += "intSampleNum,"; // Field: intSampleNum sql_string += "intXbeeID,"; // Field: intXbeeID sql_string += "intDeviceID,"; // Field: intDeviceID sql_string += "fltRSSI,"; // Field: fltRSSI sql_string += "fltLatitude,"; // Field: fltLatitude sql_string += "fltLongitude,"; // Field: fltLongitude sql_string += "fltYaw,"; // Field: fltYaw sql_string += "fltPitch,"; // Field: fltPitch sql_string += "fltRoll,"; // Field: fltRoll sql_string += "dtSampleDate "; // Field: dtSampleDate sql_string += "FROM RF_Fields "; // Table: RF_Fields sql_string += "WHERE "; // Where statement sql_string += "intDeviceID = "; // Field on Where and condition sql_string += DeviceID; // Condition value System.out.println("SQL: " + sql_string); // Debug print the SQL statement Statement stmt = conn.createStatement(); // Build SQL statement ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query ArrayList records = new ArrayList(); // Build the Array List ReadRecords(rs, records); // Read the records json = gson.toJson(records); // Get JSON data from this record rs.close(); // Close the record set stmt.close(); // Close the statement if (records.isEmpty() == true) // If empty then print out no records found { // System.err.println("No Records Found!"); // Print the fact that no record was found } // } // catch (Exception e) // Exception processing: { // System.err.println( "ListDataByDevice: " + e.getMessage()); // Print the exception data and exit } // return json; // Return JSON string } // ---------------------------------------------------------------------------------------------------------------- // Method: ListDataByRSSI // Inputs: RSSI, GTE // Outputs: RF Data Entry (JSON) Collection // Description: Get RF Data Entries // ---------------------------------------------------------------------------------------------------------------- public String ListDataByRSSI(float RSSI, boolean GTE) { String json = ""; // Return JSON entry try // Try to get JSON, and save data to database { // Gson gson = new GsonBuilder().create(); // Create Gson builder // Print debug information to port if (GTE) System.out.println("Select RF Data from Table - RSSI >= " + RSSI); else System.out.println("Select RF Data from Table - RSSI < " + RSSI); String sql_string; // Build up SQL string sql_string = "SELECT "; // Select SQL statement sql_string += "intSampleNum,"; // Field: intSampleNum sql_string += "intXbeeID,"; // Field: intXbeeID sql_string += "intDeviceID,"; // Field: intDeviceID sql_string += "fltRSSI,"; // Field: fltRSSI sql_string += "fltLatitude,"; // Field: fltLatitude sql_string += "fltLongitude,"; // Field: fltLongitude sql_string += "fltYaw,"; // Field: fltYaw sql_string += "fltPitch,"; // Field: fltPitch sql_string += "fltRoll,"; // Field: fltRoll sql_string += "dtSampleDate "; // Field: dtSampleDate sql_string += "FROM RF_Fields "; // Table: RF_Fields sql_string += "WHERE "; // Where statement if (GTE) // Change condition based upon direction (GTE) sql_string += "fltRSSI >= "; // Field on Where and condition else sql_string += "fltRSSI < "; // Field on Where and condition sql_string += RSSI; // Condition value System.out.println("SQL: " + sql_string); // Debug print the SQL statement Statement stmt = conn.createStatement(); // Build SQL statement ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query ArrayList records = new ArrayList(); // Build the Array List ReadRecords(rs, records); // Read the records json = gson.toJson(records); // Get JSON data from this record rs.close(); // Close the record set stmt.close(); // Close the statement if (records.isEmpty() == true) // If empty then print out no records found { // System.err.println("No Records Found!"); // Print the fact that no record was found } // } // catch (Exception e) // Exception processing: { // System.err.println("ListDataByRSSI: " + e.getMessage()); // Print the exception data and exit } // return json; // Return JSON string } // ---------------------------------------------------------------------------------------------------------------- // Method: ListDataByGeoArea // Inputs: Start Latitude, Start Longitude, End Latitude, End Longitude (create // rectangle) // Outputs: RF Data Entry (JSON) Collection // Description: Get RF Data Entries // ---------------------------------------------------------------------------------------------------------------- public String ListDataByGeoArea( float StartLatitude, float StartLongitude, float EndLatitude, float EndLongitude) { String json = ""; // Return JSON entry try // Try to get JSON, and save data to database { // Gson gson = new GsonBuilder().create(); // Create Gson builder // Print debug information to port System.out.println("Select RF Data from Table - GeoLocation"); String sql_string; // Build up SQL string sql_string = "SELECT "; // Select SQL statement sql_string += "intSampleNum,"; // Field: intSampleNum sql_string += "intXbeeID,"; // Field: intXbeeID sql_string += "intDeviceID,"; // Field: intDeviceID sql_string += "fltRSSI,"; // Field: fltRSSI sql_string += "fltLatitude,"; // Field: fltLatitude sql_string += "fltLongitude,"; // Field: fltLongitude sql_string += "fltYaw,"; // Field: fltYaw sql_string += "fltPitch,"; // Field: fltPitch sql_string += "fltRoll,"; // Field: fltRoll sql_string += "dtSampleDate "; // Field: dtSampleDate sql_string += "FROM RF_Fields "; // Table: RF_Fields // If, 0,0,0,0 return all records if ((StartLatitude != 0) | (StartLongitude != 0) | (EndLatitude != 0) | (EndLongitude != 0)) { sql_string += "WHERE ("; // Where statement sql_string += "fltLatitude >= " + StartLatitude + " AND "; // Field on Where and condition sql_string += "fltLatitude >= " + StartLongitude + " AND "; // Field on Where and condition sql_string += "fltLatitude <= " + EndLatitude + " AND "; // Field on Where and condition sql_string += "fltLongitude <= " + EndLongitude + ")"; // Field on Where and condition } System.out.println("SQL: " + sql_string); // Debug print the SQL statement Statement stmt = conn.createStatement(); // Build SQL statement ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query ArrayList records = new ArrayList(); // Build the Array List ReadRecords(rs, records); // Read the records json = gson.toJson(records); // Get JSON data from this record rs.close(); // Close the record set stmt.close(); // Close the statement if (records.isEmpty() == true) // If empty then print out no records found { // System.err.println("No Records Found!"); // Print the fact that no record was found } // } // catch (Exception e) // Exception processing: { // System.err.println( "ListDataByGeoArea: " + e.getMessage()); // Print the exception data and exit } // return json; // Return JSON string } }
public static void main(String args[]) throws Exception { if ((System.getProperty("java.vm.name") != null) && System.getProperty("java.vm.name").equals("J9")) jvm = jvm.getJvm("j9_13"); else jvm = jvm.getJvm("currentjvm"); // ensure compatibility vCmd = jvm.getCommandLine(); try { bos = new BufferedOutputStream(System.out, 1024); System.out.println("Start testProperties to test property priority"); /** * ********************************************************** Test port setting priorty * ********************************************************** */ // derby.drda.portNumber set in derby.properties to 1528 System.out.println("Testing derby.properties Port 1528 "); Properties derbyProperties = new Properties(); derbyProperties.put("derby.drda.portNumber", "1528"); FileOutputStream propFile = new FileOutputStream("derby.properties"); derbyProperties.store(propFile, "testing derby.properties"); propFile.close(); // test start no parameters - Pickup 1528 from derby.properties derbyServerCmd("start", null); waitForStart("1528", 60000); System.out.println("Successfully Connected"); // shutdown - also picks up from derby.properties derbyServerCmd("shutdown", null); System.out.println("Testing System properties Port 1529 "); // test start with system property. Overrides derby.properties derbyServerCmd("start", "-Dderby.drda.portNumber=1529"); waitForStart("1529", 60000); System.out.println("Successfully Connected"); // shutdown - also picks up from System Properties derbyServerCmd("shutdown", "1529"); System.out.println("Testing command line option. Port 1530"); derbyServerCmd("start", "1530"); waitForStart("1530", 60000); System.out.println("Successfully Connected"); // shutdown - with command line option derbyServerCmd("shutdown", "1530"); /** * ******************************************************************** Test start server * specifying system properties without values * ******************************************************************* */ System.out.println("Testing start server by specifying system properties without values"); System.out.println("First shutdown server started on default port by the test harness"); // Shutdown the server started by test derbyServerCmd("shutdown", "1527"); execCmdDumpResults(startServerCmd, false); waitForStart("1527", 60000); // check that default properties are used listProperties("1527"); // Test trace and logconnections commands execCmdDumpResults(cmdTraceOn, true); execCmdDumpResults(cmdLogconnectionsOn, true); listProperties("1527"); execCmdDumpResults(cmdTraceOff, true); listProperties("1527"); derbyServerCmd("shutdown", "1527"); // Test error conditions in command-line execCmdDumpResults(cmdWithoutArgs, true); execCmdDumpResults(cmdUnknown, true); execCmdDumpResults(cmdWithWrongArgNum, true); System.out.println("End test"); bos.close(); } catch (Exception e) { e.printStackTrace(); // If something went wrong, // make sure all these servers are shutdown try { derbyServerCmd("shutdown", "1527"); } catch (Exception se) { } try { derbyServerCmd("shutdown", "1528"); } catch (Exception se) { } try { derbyServerCmd("shutdown", "1529"); } catch (Exception se) { } try { derbyServerCmd("shutdown", "1530"); } catch (Exception se) { } } finally { try { File fileToDelete = new File("derby.properties"); fileToDelete.delete(); } catch (Exception e) { e.printStackTrace(); } } }
static void initJDBC() { try { // load the ini file Properties ini = new Properties(); ini.load(new FileInputStream(System.getProperty("prop"))); // display the values we need System.out.println("driver=" + ini.getProperty("driver")); System.out.println("conn=" + ini.getProperty("conn")); System.out.println("user="******"user")); System.out.println("password=******"); name = ini.getProperty("name"); // Register jdbcDriver Class.forName(ini.getProperty("driver")); // make connection conn = DriverManager.getConnection( ini.getProperty("conn"), ini.getProperty("user"), ini.getProperty("password")); conn.setAutoCommit(false); // Create Statement stmt = conn.createStatement(); distPrepStmt = conn.prepareStatement( "INSERT INTO district " + " (d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1, d_street_2, d_city, d_state, d_zip) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); itemPrepStmt = conn.prepareStatement( "INSERT INTO item " + " (i_id, i_name, i_price, i_data, i_im_id) " + "VALUES (?, ?, ?, ?, ?)"); custPrepStmt = conn.prepareStatement( "INSERT INTO customer " + " (c_id, c_d_id, c_w_id, " + "c_discount, c_credit, c_last, c_first, c_credit_lim, " + "c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, " + "c_street_1, c_street_2, c_city, c_state, c_zip, " + "c_phone, c_since, c_middle, c_data) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); histPrepStmt = conn.prepareStatement( "INSERT INTO history " + " (h_c_id, h_c_d_id, h_c_w_id, " + "h_d_id, h_w_id, " + "h_date, h_amount, h_data) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); ordrPrepStmt = conn.prepareStatement( "INSERT INTO oorder " + " (o_id, o_w_id, o_d_id, o_c_id, " + "o_carrier_id, o_ol_cnt, o_all_local, o_entry_d) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); orlnPrepStmt = conn.prepareStatement( "INSERT INTO order_line " + " (ol_w_id, ol_d_id, ol_o_id, " + "ol_number, ol_i_id, ol_delivery_d, " + "ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); nworPrepStmt = conn.prepareStatement( "INSERT INTO new_order " + " (no_w_id, no_d_id, no_o_id) " + "VALUES (?, ?, ?)"); stckPrepStmt = conn.prepareStatement( "INSERT INTO stock " + " (s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data, " + "s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, " + "s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); whsePrepStmt = conn.prepareStatement( "INSERT INTO warehouse " + " (w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); } catch (SQLException se) { System.out.println(se.getMessage()); transRollback(); } catch (Exception e) { e.printStackTrace(); transRollback(); } // end try } // end initJDBC()
private void createFile() { fileName = System.getProperty("user.dir") + System.getProperty("file.separator") + fileName; f = new File(fileName); }
/** * Swing Tool for manageing a JDBC database. * * <p> * * <pre> * Usage: java DatabaseManagerSwing [-options] * where options include: * -driver <classname> jdbc driver class * -url <name> jdbc url * -user <name> username used for connection * -password <password> password for this user * -dir <path> default directory * -script <file> reads from script file * </pre> * * @version 1.7.0 */ public class DatabaseManagerSwing extends JApplet implements ActionListener, WindowListener, KeyListener { static final String NL = System.getProperty("line.separator"); static int iMaxRecent = 24; Connection cConn; DatabaseMetaData dMeta; Statement sStatement; JMenu mRecent; String sRecent[]; int iRecent; JTextArea txtCommand; JScrollPane txtCommandScroll; JButton butExecute; JTree tTree; JScrollPane tScrollPane; DefaultTreeModel treeModel; DefaultMutableTreeNode rootNode; JPanel pResult; long lTime; int iResult; // 0: grid; 1: text GridSwing gResult; JTable gResultTable; JScrollPane gScrollPane; JTextArea txtResult; JScrollPane txtResultScroll; JSplitPane nsSplitPane; // Contains query over results JSplitPane ewSplitPane; // Contains tree beside nsSplitPane boolean bHelp; JFrame fMain; static boolean bMustExit; String ifHuge = ""; JToolBar jtoolbar; // (ulrivo): variables set by arguments from the commandline static String defDriver = "org.hsqldb.jdbcDriver"; static String defURL = "jdbc:hsqldb:."; static String defUser = "******"; static String defPassword = ""; static String defScript; static String defDirectory; public void init() { DatabaseManagerSwing m = new DatabaseManagerSwing(); m.main(); try { m.connect(ConnectionDialogSwing.createConnection(defDriver, defURL, defUser, defPassword)); m.insertTestData(); m.refreshTree(); } catch (Exception e) { e.printStackTrace(); } } public static void main(String arg[]) { System.getProperties().put("sun.java2d.noddraw", "true"); // (ulrivo): read all arguments from the command line String lowerArg; boolean autoConnect = false; for (int i = 0; i < arg.length; i++) { lowerArg = arg[i].toLowerCase(); i++; bMustExit = true; if (lowerArg.equals("-driver")) { defDriver = arg[i]; autoConnect = true; } else if (lowerArg.equals("-url")) { defURL = arg[i]; autoConnect = true; } else if (lowerArg.equals("-user")) { defUser = arg[i]; autoConnect = true; } else if (lowerArg.equals("-password")) { defPassword = arg[i]; autoConnect = true; } else if (lowerArg.equals("-dir")) { defDirectory = arg[i]; } else if (lowerArg.equals("-script")) { defScript = arg[i]; } else if (lowerArg.equals("-noexit")) { bMustExit = false; i--; } else { showUsage(); return; } } DatabaseManagerSwing m = new DatabaseManagerSwing(); m.main(); Connection c = null; try { if (autoConnect) { c = ConnectionDialogSwing.createConnection(defDriver, defURL, defUser, defPassword); } else { c = ConnectionDialogSwing.createConnection(m.fMain, "Connect"); } } catch (Exception e) { e.printStackTrace(); } if (c == null) { return; } m.connect(c); } private void connect(Connection c) { if (c == null) { return; } if (cConn != null) { try { cConn.close(); } catch (SQLException e) { } } cConn = c; try { dMeta = cConn.getMetaData(); sStatement = cConn.createStatement(); refreshTree(); } catch (SQLException e) { e.printStackTrace(); } } private static void showUsage() { System.out.println( "Usage: java DatabaseManagerSwing [-options]\n" + "where options include:\n" + " -driver <classname> jdbc driver class\n" + " -url <name> jdbc url\n" + " -user <name> username used for connection\n" + " -password <password> password for this user\n" + " -dir <path> default directory\n" + " -script <file> reads from script file\n" + " -noexit do not call system.exit()"); } private void insertTestData() { try { DatabaseManagerCommon.createTestTables(sStatement); refreshTree(); txtCommand.setText(DatabaseManagerCommon.createTestData(sStatement)); refreshTree(); for (int i = 0; i < DatabaseManagerCommon.testDataSql.length; i++) { addToRecent(DatabaseManagerCommon.testDataSql[i]); } execute(); } catch (SQLException e) { e.printStackTrace(); } } void main() { CommonSwing.setDefaultColor(); fMain = new JFrame("HSQL Database Manager"); // (ulrivo): An actual icon. fMain.getContentPane().add(createToolBar(), "North"); fMain.setIconImage(CommonSwing.getIcon()); fMain.addWindowListener(this); JMenuBar bar = new JMenuBar(); // used shortcuts: CERGTSIUDOLM String fitems[] = { "-Connect...", "--", "-Open Script...", "-Save Script...", "-Save Result...", "--", "-Exit" }; addMenu(bar, "File", fitems); String vitems[] = {"RRefresh Tree", "--", "GResults in Grid", "TResults in Text"}; addMenu(bar, "View", vitems); String sitems[] = { "SSELECT", "IINSERT", "UUPDATE", "DDELETE", "---", "-CREATE TABLE", "-DROP TABLE", "-CREATE INDEX", "-DROP INDEX", "--", "-CHECKPOINT", "-SCRIPT", "-SET", "-SHUTDOWN", "--", "-Test Script" }; addMenu(bar, "Command", sitems); mRecent = new JMenu("Recent"); bar.add(mRecent); String soptions[] = { "-AutoCommit on", "-AutoCommit off", "OCommit", "LRollback", "--", "-Disable MaxRows", "-Set MaxRows to 100", "--", "-Logging on", "-Logging off", "--", "-Insert test data" }; addMenu(bar, "Options", soptions); String stools[] = {"-Dump", "-Restore", "-Transfer"}; addMenu(bar, "Tools", stools); fMain.setJMenuBar(bar); initGUI(); sRecent = new String[iMaxRecent]; Dimension d = Toolkit.getDefaultToolkit().getScreenSize(); Dimension size = fMain.getSize(); // (ulrivo): full size on screen with less than 640 width if (d.width >= 640) { fMain.setLocation((d.width - size.width) / 2, (d.height - size.height) / 2); } else { fMain.setLocation(0, 0); fMain.setSize(d); } fMain.show(); // (ulrivo): load query from command line if (defScript != null) { if (defDirectory != null) { defScript = defDirectory + File.separator + defScript; } // if insert stmet is thousands of records...skip showing it // as text. Too huge. StringBuffer buf = new StringBuffer(); ifHuge = DatabaseManagerCommon.readFile(defScript); if (4096 <= ifHuge.length()) { buf.append("This huge file cannot be edited. Please execute\n"); txtCommand.setText(buf.toString()); } else { txtCommand.setText(ifHuge); } } txtCommand.requestFocus(); } private void addMenu(JMenuBar b, String name, String items[]) { JMenu menu = new JMenu(name); addMenuItems(menu, items); b.add(menu); } private void addMenuItems(JMenu f, String m[]) { Dimension d = Toolkit.getDefaultToolkit().getScreenSize(); for (int i = 0; i < m.length; i++) { if (m[i].equals("--")) { f.addSeparator(); } else if (m[i].equals("---")) { // (ulrivo): full size on screen with less than 640 width if (d.width >= 640) { f.addSeparator(); } else { return; } } else { JMenuItem item = new JMenuItem(m[i].substring(1)); char c = m[i].charAt(0); if (c != '-') { item.setMnemonic(c); } item.addActionListener(this); f.add(item); } } } public void keyPressed(KeyEvent k) {} public void keyReleased(KeyEvent k) {} public void keyTyped(KeyEvent k) { if (k.getKeyChar() == '\n' && k.isControlDown()) { k.consume(); execute(); } } public void actionPerformed(ActionEvent ev) { String s = ev.getActionCommand(); if (s == null) { if (ev.getSource() instanceof JMenuItem) { JMenuItem i; s = ((JMenuItem) ev.getSource()).getText(); } } /* // button replace by toolbar if (s.equals("Execute")) { execute(); } else */ if (s.equals("Exit")) { windowClosing(null); } else if (s.equals("Transfer")) { Transfer.work(null); } else if (s.equals("Dump")) { Transfer.work(new String[] {"-d"}); } else if (s.equals("Restore")) { Transfer.work(new String[] {"-r"}); } else if (s.equals("Logging on")) { javaSystem.setLogToSystem(true); } else if (s.equals("Logging off")) { javaSystem.setLogToSystem(false); } else if (s.equals("Refresh Tree")) { refreshTree(); } else if (s.startsWith("#")) { int i = Integer.parseInt(s.substring(1)); txtCommand.setText(sRecent[i]); } else if (s.equals("Connect...")) { connect(ConnectionDialogSwing.createConnection(fMain, "Connect")); refreshTree(); } else if (s.equals("Results in Grid")) { iResult = 0; pResult.removeAll(); pResult.add(gScrollPane, BorderLayout.CENTER); pResult.doLayout(); gResult.fireTableChanged(null); pResult.repaint(); } else if (s.equals("Open Script...")) { JFileChooser f = new JFileChooser("."); f.setDialogTitle("Open Script..."); // (ulrivo): set default directory if set from command line if (defDirectory != null) { f.setCurrentDirectory(new File(defDirectory)); } int option = f.showOpenDialog(fMain); if (option == JFileChooser.APPROVE_OPTION) { File file = f.getSelectedFile(); if (file != null) { StringBuffer buf = new StringBuffer(); ifHuge = DatabaseManagerCommon.readFile(file.getAbsolutePath()); if (4096 <= ifHuge.length()) { buf.append("This huge file cannot be edited. Please execute\n"); txtCommand.setText(buf.toString()); } else { txtCommand.setText(ifHuge); } } } } else if (s.equals("Save Script...")) { JFileChooser f = new JFileChooser("."); f.setDialogTitle("Save Script"); // (ulrivo): set default directory if set from command line if (defDirectory != null) { f.setCurrentDirectory(new File(defDirectory)); } int option = f.showSaveDialog(fMain); if (option == JFileChooser.APPROVE_OPTION) { File file = f.getSelectedFile(); if (file != null) { DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtCommand.getText()); } } } else if (s.equals("Save Result...")) { JFileChooser f = new JFileChooser("."); f.setDialogTitle("Save Result..."); // (ulrivo): set default directory if set from command line if (defDirectory != null) { f.setCurrentDirectory(new File(defDirectory)); } int option = f.showSaveDialog(fMain); if (option == JFileChooser.APPROVE_OPTION) { File file = f.getSelectedFile(); if (file != null) { showResultInText(); DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtResult.getText()); } } } else if (s.equals("Results in Text")) { iResult = 1; pResult.removeAll(); pResult.add(txtResultScroll, BorderLayout.CENTER); pResult.doLayout(); showResultInText(); pResult.repaint(); } else if (s.equals("AutoCommit on")) { try { cConn.setAutoCommit(true); } catch (SQLException e) { } } else if (s.equals("AutoCommit off")) { try { cConn.setAutoCommit(false); } catch (SQLException e) { } } else if (s.equals("Commit")) { try { cConn.commit(); } catch (SQLException e) { } } else if (s.equals("Insert test data")) { insertTestData(); } else if (s.equals("Rollback")) { try { cConn.rollback(); } catch (SQLException e) { } } else if (s.equals("Disable MaxRows")) { try { sStatement.setMaxRows(0); } catch (SQLException e) { } } else if (s.equals("Set MaxRows to 100")) { try { sStatement.setMaxRows(100); } catch (SQLException e) { } } else if (s.equals("SELECT")) { showHelp(DatabaseManagerCommon.selectHelp); } else if (s.equals("INSERT")) { showHelp(DatabaseManagerCommon.insertHelp); } else if (s.equals("UPDATE")) { showHelp(DatabaseManagerCommon.updateHelp); } else if (s.equals("DELETE")) { showHelp(DatabaseManagerCommon.deleteHelp); } else if (s.equals("CREATE TABLE")) { showHelp(DatabaseManagerCommon.createTableHelp); } else if (s.equals("DROP TABLE")) { showHelp(DatabaseManagerCommon.dropTableHelp); } else if (s.equals("CREATE INDEX")) { showHelp(DatabaseManagerCommon.createIndexHelp); } else if (s.equals("DROP INDEX")) { showHelp(DatabaseManagerCommon.dropIndexHelp); } else if (s.equals("CHECKPOINT")) { showHelp(DatabaseManagerCommon.checkpointHelp); } else if (s.equals("SCRIPT")) { showHelp(DatabaseManagerCommon.scriptHelp); } else if (s.equals("SHUTDOWN")) { showHelp(DatabaseManagerCommon.shutdownHelp); } else if (s.equals("SET")) { showHelp(DatabaseManagerCommon.setHelp); } else if (s.equals("Test Script")) { showHelp(DatabaseManagerCommon.testHelp); } } private void showHelp(String help[]) { txtCommand.setText(help[0]); bHelp = true; pResult.removeAll(); pResult.add(txtResultScroll, BorderLayout.CENTER); pResult.doLayout(); txtResult.setText(help[1]); pResult.repaint(); txtCommand.requestFocus(); txtCommand.setCaretPosition(help[0].length()); } public void windowActivated(WindowEvent e) {} public void windowDeactivated(WindowEvent e) {} public void windowClosed(WindowEvent e) {} public void windowDeiconified(WindowEvent e) {} public void windowIconified(WindowEvent e) {} public void windowOpened(WindowEvent e) {} public void windowClosing(WindowEvent ev) { try { cConn.close(); } catch (Exception e) { } fMain.dispose(); if (bMustExit) { System.exit(0); } } private void clear() { ifHuge = ""; txtCommand.setText(ifHuge); } private void execute() { gResult.clear(); String sCmd = null; if (4096 <= ifHuge.length()) { sCmd = ifHuge; } else { sCmd = txtCommand.getText(); } if (sCmd.startsWith("-->>>TEST<<<--")) { testPerformance(); return; } String g[] = new String[1]; try { lTime = System.currentTimeMillis(); sStatement.execute(sCmd); int r = sStatement.getUpdateCount(); if (r == -1) { formatResultSet(sStatement.getResultSet()); } else { g[0] = "update count"; gResult.setHead(g); g[0] = "" + r; gResult.addRow(g); } lTime = System.currentTimeMillis() - lTime; addToRecent(txtCommand.getText()); gResult.fireTableChanged(null); } catch (SQLException e) { lTime = System.currentTimeMillis() - lTime; g[0] = "SQL Error"; gResult.setHead(g); String s = e.getMessage(); s += " / Error Code: " + e.getErrorCode(); s += " / State: " + e.getSQLState(); g[0] = s; gResult.addRow(g); gResult.fireTableChanged(null); } updateResult(); System.gc(); } private void updateResult() { if (iResult == 0) { // in case 'help' has removed the grid if (bHelp) { pResult.removeAll(); pResult.add(gScrollPane, BorderLayout.CENTER); pResult.doLayout(); gResult.fireTableChanged(null); pResult.repaint(); bHelp = false; } } else { showResultInText(); } txtCommand.selectAll(); txtCommand.requestFocus(); } private void formatResultSet(ResultSet r) { if (r == null) { String g[] = new String[1]; g[0] = "Result"; gResult.setHead(g); g[0] = "(empty)"; gResult.addRow(g); return; } try { ResultSetMetaData m = r.getMetaData(); int col = m.getColumnCount(); String h[] = new String[col]; for (int i = 1; i <= col; i++) { h[i - 1] = m.getColumnLabel(i); } gResult.setHead(h); while (r.next()) { for (int i = 1; i <= col; i++) { h[i - 1] = r.getString(i); if (r.wasNull()) { h[i - 1] = "(null)"; } } gResult.addRow(h); } r.close(); } catch (SQLException e) { } } private void testPerformance() { String all = txtCommand.getText(); StringBuffer b = new StringBuffer(); long total = 0; for (int i = 0; i < all.length(); i++) { char c = all.charAt(i); if (c != '\n') { b.append(c); } } all = b.toString(); String g[] = new String[4]; g[0] = "ms"; g[1] = "count"; g[2] = "sql"; g[3] = "error"; gResult.setHead(g); int max = 1; lTime = System.currentTimeMillis() - lTime; while (!all.equals("")) { int i = all.indexOf(';'); String sql; if (i != -1) { sql = all.substring(0, i); all = all.substring(i + 1); } else { sql = all; all = ""; } if (sql.startsWith("--#")) { max = Integer.parseInt(sql.substring(3)); continue; } else if (sql.startsWith("--")) { continue; } g[2] = sql; long l = 0; try { l = DatabaseManagerCommon.testStatement(sStatement, sql, max); total += l; g[0] = "" + l; g[1] = "" + max; g[3] = ""; } catch (SQLException e) { g[0] = g[1] = "n/a"; g[3] = e.toString(); } gResult.addRow(g); System.out.println(l + " ms : " + sql); } g[0] = "" + total; g[1] = "total"; g[2] = ""; gResult.addRow(g); lTime = System.currentTimeMillis() - lTime; updateResult(); } /** Method declaration */ private void showResultInText() { String col[] = gResult.getHead(); int width = col.length; int size[] = new int[width]; Vector data = gResult.getData(); String row[]; int height = data.size(); for (int i = 0; i < width; i++) { size[i] = col[i].length(); } for (int i = 0; i < height; i++) { row = (String[]) data.elementAt(i); for (int j = 0; j < width; j++) { int l = row[j].length(); if (l > size[j]) { size[j] = l; } } } StringBuffer b = new StringBuffer(); for (int i = 0; i < width; i++) { b.append(col[i]); for (int l = col[i].length(); l <= size[i]; l++) { b.append(' '); } } b.append(NL); for (int i = 0; i < width; i++) { for (int l = 0; l < size[i]; l++) { b.append('-'); } b.append(' '); } b.append(NL); for (int i = 0; i < height; i++) { row = (String[]) data.elementAt(i); for (int j = 0; j < width; j++) { b.append(row[j]); for (int l = row[j].length(); l <= size[j]; l++) { b.append(' '); } } b.append(NL); } b.append(NL + height + " row(s) in " + lTime + " ms"); txtResult.setText(b.toString()); } private void addToRecent(String s) { for (int i = 0; i < iMaxRecent; i++) { if (s.equals(sRecent[i])) { return; } } if (sRecent[iRecent] != null) { mRecent.remove(iRecent); } sRecent[iRecent] = s; if (s.length() > 43) { s = s.substring(0, 40) + "..."; } JMenuItem item = new JMenuItem(s); item.setActionCommand("#" + iRecent); item.addActionListener(this); mRecent.insert(item, iRecent); iRecent = (iRecent + 1) % iMaxRecent; } private void initGUI() { JPanel pCommand = new JPanel(); pResult = new JPanel(); nsSplitPane = new JSplitPane(JSplitPane.VERTICAL_SPLIT, pCommand, pResult); pCommand.setLayout(new BorderLayout()); pResult.setLayout(new BorderLayout()); Font fFont = new Font("Dialog", Font.PLAIN, 12); txtCommand = new JTextArea(5, 40); txtCommand.setMargin(new Insets(5, 5, 5, 5)); txtCommand.addKeyListener(this); txtCommandScroll = new JScrollPane(txtCommand); txtResult = new JTextArea(20, 40); txtResult.setMargin(new Insets(5, 5, 5, 5)); txtResultScroll = new JScrollPane(txtResult); txtCommand.setFont(fFont); txtResult.setFont(new Font("Courier", Font.PLAIN, 12)); /* // button replaced by toolbar butExecute = new JButton("Execute"); butExecute.addActionListener(this); pCommand.add(butExecute, BorderLayout.EAST); */ pCommand.add(txtCommandScroll, BorderLayout.CENTER); gResult = new GridSwing(); gResultTable = new JTable(gResult); gScrollPane = new JScrollPane(gResultTable); // getContentPane().setLayout(new BorderLayout()); pResult.add(gScrollPane, BorderLayout.CENTER); // Set up the tree rootNode = new DefaultMutableTreeNode("Connection"); treeModel = new DefaultTreeModel(rootNode); tTree = new JTree(treeModel); tScrollPane = new JScrollPane(tTree); tScrollPane.setPreferredSize(new Dimension(120, 400)); tScrollPane.setMinimumSize(new Dimension(70, 100)); txtCommandScroll.setPreferredSize(new Dimension(360, 100)); txtCommandScroll.setMinimumSize(new Dimension(180, 100)); gScrollPane.setPreferredSize(new Dimension(460, 300)); ewSplitPane = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT, tScrollPane, nsSplitPane); fMain.getContentPane().add(ewSplitPane, BorderLayout.CENTER); doLayout(); fMain.pack(); } /* Simple tree node factory method - set's parent and user object. */ private DefaultMutableTreeNode makeNode(Object userObject, MutableTreeNode parent) { DefaultMutableTreeNode node = new DefaultMutableTreeNode(userObject); if (parent != null) { treeModel.insertNodeInto(node, parent, parent.getChildCount()); } return node; } /* Clear all existing nodes from the tree model and rebuild from scratch. */ protected void refreshTree() { DefaultMutableTreeNode propertiesNode; DefaultMutableTreeNode leaf; // First clear the existing tree by simply enumerating // over the root node's children and removing them one by one. while (treeModel.getChildCount(rootNode) > 0) { DefaultMutableTreeNode child = (DefaultMutableTreeNode) treeModel.getChild(rootNode, 0); treeModel.removeNodeFromParent(child); child.removeAllChildren(); child.removeFromParent(); } treeModel.nodeStructureChanged(rootNode); treeModel.reload(); tScrollPane.repaint(); // Now rebuild the tree below its root try { // Start by naming the root node from its URL: rootNode.setUserObject(dMeta.getURL()); // get metadata about user tables by building a vector of table names String usertables[] = {"TABLE", "GLOBAL TEMPORARY", "VIEW"}; ResultSet result = dMeta.getTables(null, null, null, usertables); Vector tables = new Vector(); // sqlbob@users Added remarks. Vector remarks = new Vector(); while (result.next()) { tables.addElement(result.getString(3)); remarks.addElement(result.getString(5)); } result.close(); // For each table, build a tree node with interesting info for (int i = 0; i < tables.size(); i++) { String name = (String) tables.elementAt(i); DefaultMutableTreeNode tableNode = makeNode(name, rootNode); ResultSet col = dMeta.getColumns(null, null, name, null); // sqlbob@users Added remarks. String remark = (String) remarks.elementAt(i); if ((remark != null) && !remark.trim().equals("")) { makeNode(remark, tableNode); } // With a child for each column containing pertinent attributes while (col.next()) { String c = col.getString(4); DefaultMutableTreeNode columnNode = makeNode(c, tableNode); String type = col.getString(6); makeNode("Type: " + type, columnNode); boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls; makeNode("Nullable: " + nullable, columnNode); } col.close(); DefaultMutableTreeNode indexesNode = makeNode("Indices", tableNode); ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false); String oldiname = null; // A child node to contain each index - and its attributes while (ind.next()) { DefaultMutableTreeNode indexNode = null; boolean nonunique = ind.getBoolean(4); String iname = ind.getString(6); if ((oldiname == null || !oldiname.equals(iname))) { indexNode = makeNode(iname, indexesNode); makeNode("Unique: " + !nonunique, indexNode); oldiname = iname; } // And the ordered column list for index components makeNode(ind.getString(9), indexNode); } ind.close(); } // Finally - a little additional metadata on this connection propertiesNode = makeNode("Properties", rootNode); makeNode("User: "******"ReadOnly: " + cConn.isReadOnly(), propertiesNode); makeNode("AutoCommit: " + cConn.getAutoCommit(), propertiesNode); makeNode("Driver: " + dMeta.getDriverName(), propertiesNode); makeNode("Product: " + dMeta.getDatabaseProductName(), propertiesNode); makeNode("Version: " + dMeta.getDatabaseProductVersion(), propertiesNode); } catch (SQLException se) { propertiesNode = makeNode("Error getting metadata:", rootNode); makeNode(se.getMessage(), propertiesNode); makeNode(se.getSQLState(), propertiesNode); } treeModel.nodeStructureChanged(rootNode); treeModel.reload(); tScrollPane.repaint(); } protected JToolBar createToolBar() { JToolBar jtoolbar = new JToolBar(); jtoolbar.putClientProperty("JToolBar.isRollover", Boolean.TRUE); // --------------------------------------- JButton jbuttonClear = new JButton("Clear SQL Statement"); jbuttonClear.setToolTipText("Clear SQL Statement"); jbuttonClear.addActionListener( new ActionListener() { public void actionPerformed(ActionEvent actionevent) { clear(); } }); // --------------------------------------- JButton jbuttonExecute = new JButton("Execute SQL Statement"); jbuttonExecute.setToolTipText("Execute SQL Statement"); jbuttonExecute.addActionListener( new ActionListener() { public void actionPerformed(ActionEvent actionevent) { execute(); } }); jtoolbar.addSeparator(); jtoolbar.add(jbuttonClear); jtoolbar.addSeparator(); jtoolbar.add(jbuttonExecute); jtoolbar.addSeparator(); jbuttonClear.setAlignmentY(0.5F); jbuttonClear.setAlignmentX(0.5F); jbuttonExecute.setAlignmentY(0.5F); jbuttonExecute.setAlignmentX(0.5F); return jtoolbar; } }