List of usage examples for java.sql ResultSetMetaData getColumnCount
int getColumnCount() throws SQLException;
ResultSet object. From source file:jeeves.resources.dbms.Dbms.java
private Element buildResponse(ResultSet rs, Hashtable<String, String> formats) throws SQLException { ResultSetMetaData md = rs.getMetaData(); int colNum = md.getColumnCount(); // --- retrieve name and type of fields Vector<String> vHeaders = new Vector<String>(); Vector<Integer> vTypes = new Vector<Integer>(); for (int i = 0; i < colNum; i++) { vHeaders.add(md.getColumnLabel(i + 1).toLowerCase()); vTypes.add(new Integer(md.getColumnType(i + 1))); }//w w w . j a v a 2s. co m // --- build the jdom tree Element root = new Element(Jeeves.Elem.RESPONSE); while (rs.next()) { Element record = new Element(Jeeves.Elem.RECORD); for (int i = 0; i < colNum; i++) { String name = vHeaders.get(i).toString(); int type = ((Integer) vTypes.get(i)).intValue(); record.addContent(buildElement(rs, i, name, type, formats)); } root.addContent(record); } return root; }
From source file:com.kylinolap.query.test.KylinTestBase.java
protected int output(ResultSet resultSet, boolean needDisplay) throws SQLException { int count = 0; ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); StringBuilder sb = new StringBuilder("\n"); if (needDisplay) { for (int i = 1; i <= columnCount; i++) { sb.append(metaData.getColumnName(i)); sb.append("-"); sb.append(metaData.getTableName(i)); sb.append("-"); sb.append(metaData.getColumnTypeName(i)); if (i < columnCount) { sb.append("\t"); } else { sb.append("\n"); }//from w ww . j a v a 2 s. com } } while (resultSet.next()) { if (needDisplay) { for (int i = 1; i <= columnCount; i++) { sb.append(resultSet.getString(i)); if (i < columnCount) { sb.append("\t"); } else { sb.append("\n"); } } } count++; } printInfo(sb.toString()); return count; }
From source file:net.testdriven.psiprobe.controllers.sql.ExecuteSqlController.java
protected ModelAndView handleContext(String contextName, Context context, HttpServletRequest request, HttpServletResponse response) throws Exception { String resourceName = ServletRequestUtils.getStringParameter(request, "resource"); String sql = ServletRequestUtils.getStringParameter(request, "sql", null); if (sql == null || sql.equals("") || sql.trim().equals("")) { request.setAttribute("errorMessage", getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.required")); return new ModelAndView(getViewName()); }/*from www . j av a2s .c o m*/ int maxRows = ServletRequestUtils.getIntParameter(request, "maxRows", 0); int rowsPerPage = ServletRequestUtils.getIntParameter(request, "rowsPerPage", 0); int historySize = ServletRequestUtils.getIntParameter(request, "historySize", 0); // store current option values and query history in a session attribute HttpSession sess = request.getSession(); DataSourceTestInfo sessData = (DataSourceTestInfo) sess.getAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR); synchronized (sess) { if (sessData == null) { sessData = new DataSourceTestInfo(); sess.setAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR, sessData); } sessData.setMaxRows(maxRows); sessData.setRowsPerPage(rowsPerPage); sessData.setHistorySize(historySize); sessData.addQueryToHistory(sql); } DataSource dataSource = null; try { dataSource = getContainerWrapper().getResourceResolver().lookupDataSource(context, resourceName); } catch (NamingException e) { request.setAttribute("errorMessage", getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName })); } if (dataSource == null) { request.setAttribute("errorMessage", getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName })); } else { List<Map<String, String>> results = null; int rowsAffected; try { // TODO: use Spring's jdbc template? try (Connection conn = dataSource.getConnection()) { conn.setAutoCommit(true); try (PreparedStatement stmt = conn.prepareStatement(sql)) { boolean hasResultSet = stmt.execute(); if (!hasResultSet) { rowsAffected = stmt.getUpdateCount(); } else { results = new ArrayList<>(); try (ResultSet rs = stmt.getResultSet()) { ResultSetMetaData metaData = rs.getMetaData(); while (rs.next() && (maxRows < 0 || results.size() < maxRows)) { Map<String, String> record = new LinkedHashMap<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { String value = rs.getString(i); if (rs.wasNull()) { value = getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.sql.null"); } else { value = HtmlUtils.htmlEscape(value); } // a work around for IE browsers bug of not displaying // a border around an empty table column if (value.equals("")) { value = " "; } // Pad the keys of columns with existing labels so they are distinct String key = metaData.getColumnLabel(i); while (record.containsKey(key)) { key += " "; } record.put(HtmlUtils.htmlEscape(key), value); } results.add(record); } } rowsAffected = results.size(); } } } // store the query results in the session attribute in order // to support a result set pagination feature without re-executing the query synchronized (sess) { sessData.setResults(results); } ModelAndView mv = new ModelAndView(getViewName(), "results", results); mv.addObject("rowsAffected", String.valueOf(rowsAffected)); mv.addObject("rowsPerPage", String.valueOf(rowsPerPage)); return mv; } catch (SQLException e) { String message = getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.failure", new Object[] { e.getMessage() }); logger.error(message, e); request.setAttribute("errorMessage", message); } } return new ModelAndView(getViewName()); }
From source file:com.googlecode.psiprobe.controllers.sql.ExecuteSqlController.java
protected ModelAndView handleContext(String contextName, Context context, HttpServletRequest request, HttpServletResponse response) throws Exception { String resourceName = ServletRequestUtils.getStringParameter(request, "resource"); String sql = ServletRequestUtils.getStringParameter(request, "sql", null); if (sql == null || sql.equals("") || sql.trim().equals("")) { request.setAttribute("errorMessage", getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.required")); return new ModelAndView(getViewName()); }//from ww w . ja v a 2 s . c o m int maxRows = ServletRequestUtils.getIntParameter(request, "maxRows", 0); int rowsPerPage = ServletRequestUtils.getIntParameter(request, "rowsPerPage", 0); int historySize = ServletRequestUtils.getIntParameter(request, "historySize", 0); // store current option values and query history in a session attribute HttpSession sess = request.getSession(); DataSourceTestInfo sessData = (DataSourceTestInfo) sess.getAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR); synchronized (sess) { if (sessData == null) { sessData = new DataSourceTestInfo(); sess.setAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR, sessData); } sessData.setMaxRows(maxRows); sessData.setRowsPerPage(rowsPerPage); sessData.setHistorySize(historySize); sessData.addQueryToHistory(sql); } DataSource dataSource = null; try { dataSource = getContainerWrapper().getResourceResolver().lookupDataSource(context, resourceName, getContainerWrapper()); } catch (NamingException e) { request.setAttribute("errorMessage", getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName })); } if (dataSource == null) { request.setAttribute("errorMessage", getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName })); } else { List results = null; int rowsAffected = 0; try { // TODO: use Spring's jdbc template? Connection conn = dataSource.getConnection(); try { conn.setAutoCommit(true); PreparedStatement stmt = conn.prepareStatement(sql); try { boolean hasResultSet = stmt.execute(); if (!hasResultSet) { rowsAffected = stmt.getUpdateCount(); } else { results = new ArrayList(); ResultSet rs = stmt.getResultSet(); try { ResultSetMetaData metaData = rs.getMetaData(); while (rs.next() && (maxRows < 0 || results.size() < maxRows)) { Map record = new LinkedHashMap(); for (int i = 1; i <= metaData.getColumnCount(); i++) { String value = rs.getString(i); if (rs.wasNull()) { value = getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.sql.null"); } else { value = HtmlUtils.htmlEscape(value); } // a work around for IE browsers bug of not displaying // a border around an empty table column if (value.equals("")) { value = " "; } // Pad the keys of columns with existing labels so they are distinct String key = metaData.getColumnLabel(i); while (record.containsKey(key)) { key += " "; } record.put(HtmlUtils.htmlEscape(key), value); } results.add(record); } } finally { rs.close(); } rowsAffected = results.size(); } } finally { stmt.close(); } } finally { conn.close(); } // store the query results in the session attribute in order // to support a result set pagination feature without re-executing the query synchronized (sess) { sessData.setResults(results); } ModelAndView mv = new ModelAndView(getViewName(), "results", results); mv.addObject("rowsAffected", String.valueOf(rowsAffected)); mv.addObject("rowsPerPage", String.valueOf(rowsPerPage)); return mv; } catch (SQLException e) { String message = getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.failure", new Object[] { e.getMessage() }); logger.error(message, e); request.setAttribute("errorMessage", message); } } return new ModelAndView(getViewName()); }
From source file:com.nextep.datadesigner.sqlgen.impl.generator.DataSetGenerator.java
private ISQLScript buildScript(IDataSet set, IDataSet valuesSet, DeltaType type) { final ISQLScript script = CorePlugin.getTypedObjectFactory().create(ISQLScript.class); script.setScriptType(ScriptType.DATA); script.setName(type.name().toLowerCase() + "." + set.getName()); //$NON-NLS-1$ final StringBuilder buf = new StringBuilder(2000); final ISQLParser parser = SQLGenPlugin.getService(IGenerationService.class).getCurrentSQLParser(); // final IDataService dataService = DbgmPlugin.getService(IDataService.class); final IStorageService storageService = DbgmPlugin.getService(IStorageService.class); final IStorageHandle handle = valuesSet.getStorageHandle(); if (handle != null) { Connection conn = null;/* w w w. j ava2s .co m*/ Statement stmt = null; ResultSet rset = null; try { conn = storageService.getLocalConnection(); stmt = conn.createStatement(); stmt.execute(handle.getSelectStatement()); rset = stmt.getResultSet(); final ResultSetMetaData md = rset.getMetaData(); while (rset.next()) { final List<Object> values = new LinkedList<Object>(); for (int i = 1; i <= md.getColumnCount(); i++) { values.add(rset.getObject(i)); } switch (type) { case INSERT: buf.append(buildInsert(parser, set, values)); break; case UPDATE: buf.append(buildUpdate(parser, set, values)); break; case DELETE: buf.append(buildDelete(parser, set, values)); break; } } } catch (SQLException e) { throw new ErrorException("Data generation problem: " + e.getMessage(), e); } finally { safeClose(rset, stmt, conn); } } if (buf.length() == 0) { return null; } else { script.appendSQL(buf.toString()); return script; } }
From source file:fr.jetoile.hadoopunit.integrationtest.IntegrationBootstrapTest.java
@Test @Ignore/*from w w w . j a v a 2s .com*/ public void hiveServer2ShouldStart() throws InterruptedException, ClassNotFoundException, SQLException { // assertThat(Utils.available("127.0.0.1", 20103)).isFalse(); // Load the Hive JDBC driver LOGGER.info("HIVE: Loading the Hive JDBC Driver"); Class.forName("org.apache.hive.jdbc.HiveDriver"); // // Create an ORC table and describe it // // Get the connection Connection con = DriverManager .getConnection( "jdbc:hive2://" + configuration.getString(HadoopUnitConfig.HIVE_SERVER2_HOSTNAME_KEY) + ":" + configuration.getInt(HadoopUnitConfig.HIVE_SERVER2_PORT_KEY) + "/" + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY), "user", "pass"); // Create the DB Statement stmt; try { String createDbDdl = "CREATE DATABASE IF NOT EXISTS " + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY); stmt = con.createStatement(); LOGGER.info("HIVE: Running Create Database Statement: {}", createDbDdl); stmt.execute(createDbDdl); } catch (Exception e) { e.printStackTrace(); } // Drop the table incase it still exists String dropDdl = "DROP TABLE " + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY) + "." + configuration.getString(HadoopUnitConfig.HIVE_TEST_TABLE_NAME_KEY); stmt = con.createStatement(); LOGGER.info("HIVE: Running Drop Table Statement: {}", dropDdl); stmt.execute(dropDdl); // Create the ORC table String createDdl = "CREATE TABLE IF NOT EXISTS " + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY) + "." + configuration.getString(HadoopUnitConfig.HIVE_TEST_TABLE_NAME_KEY) + " (id INT, msg STRING) " + "PARTITIONED BY (dt STRING) " + "CLUSTERED BY (id) INTO 16 BUCKETS " + "STORED AS ORC tblproperties(\"orc.compress\"=\"NONE\")"; stmt = con.createStatement(); LOGGER.info("HIVE: Running Create Table Statement: {}", createDdl); stmt.execute(createDdl); // Issue a describe on the new table and display the output LOGGER.info("HIVE: Validating Table was Created: "); ResultSet resultSet = stmt.executeQuery( "DESCRIBE FORMATTED " + configuration.getString(HadoopUnitConfig.HIVE_TEST_TABLE_NAME_KEY)); int count = 0; while (resultSet.next()) { ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) { System.out.print(resultSet.getString(i)); } System.out.println(); count++; } assertEquals(33, count); // Drop the table dropDdl = "DROP TABLE " + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY) + "." + configuration.getString(HadoopUnitConfig.HIVE_TEST_TABLE_NAME_KEY); stmt = con.createStatement(); LOGGER.info("HIVE: Running Drop Table Statement: {}", dropDdl); stmt.execute(dropDdl); }
From source file:com.datatorrent.contrib.enrich.JDBCLoader.java
protected ArrayList<Object> getDataFrmResult(Object result) throws RuntimeException { try {//from ww w.j a va 2 s. c om ResultSet resultSet = (ResultSet) result; if (resultSet.next()) { ResultSetMetaData rsdata = resultSet.getMetaData(); // If the includefields is empty, populate it from ResultSetMetaData if (CollectionUtils.isEmpty(includeFieldInfo)) { if (includeFieldInfo == null) { includeFieldInfo = new ArrayList<>(); } for (int i = 1; i <= rsdata.getColumnCount(); i++) { String columnName = rsdata.getColumnName(i); // TODO: Take care of type conversion. includeFieldInfo.add(new FieldInfo(columnName, columnName, FieldInfo.SupportType.OBJECT)); } } ArrayList<Object> res = new ArrayList<Object>(); for (FieldInfo f : includeFieldInfo) { res.add(getConvertedData(resultSet.getObject(f.getColumnName()), f)); } return res; } else { return null; } } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:com.google.enterprise.connector.salesforce.storetype.DBStore.java
public DBStore(BaseConnector connector) { Connection connection = null; logger = Logger.getLogger(this.getClass().getPackage().getName()); logger.log(Level.INFO, "Initialize DBStore "); this.connector = connector; //each connector instance has its own table in the same database this.instance_table = "i_" + connector.getInstanceName(); Statement Stmt = null;//from w ww . j a v a2 s .c om ResultSet RS = null; DatabaseMetaData dbm = null; boolean table_exists = false; try { //check if the datasource/database exists Context initCtx = new InitialContext(); Context envCtx = (Context) initCtx.lookup("java:comp/env"); ds = (DataSource) envCtx.lookup(BaseConstants.CONNECTOR_DATASOURCE); connection = ds.getConnection(); connection.setAutoCommit(true); dbm = connection.getMetaData(); logger.log(Level.INFO, "Connected to databaseType " + dbm.getDatabaseProductName()); } catch (Exception ex) { logger.log(Level.SEVERE, "Exception initializing Store Datasource " + ex); connection = null; return; } try { if (dbm.getDatabaseProductName().equals("MySQL")) { //check if the per-connector table exists logger.log(Level.FINE, "Checking to see if connector DB exists..."); Stmt = connection.createStatement(); RS = Stmt.executeQuery("desc " + instance_table); ResultSetMetaData rsMetaData = RS.getMetaData(); if (rsMetaData.getColumnCount() > 0) table_exists = true; RS.close(); Stmt.close(); } else { logger.log(Level.SEVERE, "Unsupported DATABASE TYPE..." + dbm.getDatabaseProductName()); } } catch (Exception ex) { logger.log(Level.SEVERE, "Exception initializing Store " + ex); } try { //if the per-instance table doesn't exist, create it if (!table_exists) { logger.log(Level.INFO, "Creating Instance Table " + instance_table); if (dbm.getDatabaseProductName().equals("MySQL")) { Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); String create_stmt = ""; create_stmt = "CREATE TABLE `" + this.instance_table + "` (" + "`crawl_set` decimal(19,5) NOT NULL," + "`insert_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP," + "`crawl_data` MEDIUMTEXT default NULL," + "PRIMARY KEY (`crawl_set`)," + "KEY `set_index` (`crawl_set`)" + ") ENGINE=MyISAM;"; statement.addBatch(create_stmt); statement.executeBatch(); statement.close(); } else { logger.log(Level.INFO, "Instance Table " + instance_table + " already exists"); //connection.close(); //TODO: somehow figure out if we should delete this table here } } boolean qrtz_table_exists = false; if (dbm.getDatabaseProductName().equals("MySQL")) { //check if the per-connector table exists logger.log(Level.FINE, "Checking to see if quartz tables exists..."); Stmt = connection.createStatement(); try { RS = Stmt.executeQuery("desc QRTZ_JOB_DETAILS"); ResultSetMetaData rsMetaData = RS.getMetaData(); if (rsMetaData.getColumnCount() > 0) qrtz_table_exists = true; } catch (Exception ex) { logger.log(Level.INFO, "Could not find Quartz Tables...creating now.."); } RS.close(); Stmt.close(); } else { logger.log(Level.SEVERE, "Unsupported DATABASE TYPE..." + dbm.getDatabaseProductName()); } if (!qrtz_table_exists) { logger.log(Level.INFO, "Creating Global Quartz Table "); //the quartz db setup scripts are at //quartz-1.8.0/docs/dbTables/tables_mysql.sql //one set of Quartz tables can handle any number of triggers/crons Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); String create_stmt = "CREATE TABLE QRTZ_JOB_DETAILS (JOB_NAME VARCHAR(200) NOT NULL,JOB_GROUP VARCHAR(200) NOT NULL,DESCRIPTION VARCHAR(250) NULL,JOB_CLASS_NAME VARCHAR(250) NOT NULL,IS_DURABLE VARCHAR(1) NOT NULL,IS_VOLATILE VARCHAR(1) NOT NULL,IS_STATEFUL VARCHAR(1) NOT NULL,REQUESTS_RECOVERY VARCHAR(1) NOT NULL,JOB_DATA BLOB NULL,PRIMARY KEY (JOB_NAME,JOB_GROUP));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_JOB_LISTENERS ( JOB_NAME VARCHAR(200) NOT NULL, JOB_GROUP VARCHAR(200) NOT NULL, JOB_LISTENER VARCHAR(200) NOT NULL, PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER), FOREIGN KEY (JOB_NAME,JOB_GROUP) REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_FIRED_TRIGGERS ( ENTRY_ID VARCHAR(95) NOT NULL, TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, IS_VOLATILE VARCHAR(1) NOT NULL, INSTANCE_NAME VARCHAR(200) NOT NULL, FIRED_TIME BIGINT(13) NOT NULL, PRIORITY INTEGER NOT NULL, STATE VARCHAR(16) NOT NULL, JOB_NAME VARCHAR(200) NULL, JOB_GROUP VARCHAR(200) NULL, IS_STATEFUL VARCHAR(1) NULL, REQUESTS_RECOVERY VARCHAR(1) NULL, PRIMARY KEY (ENTRY_ID));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_TRIGGERS ( TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, JOB_NAME VARCHAR(200) NOT NULL, JOB_GROUP VARCHAR(200) NOT NULL, IS_VOLATILE VARCHAR(1) NOT NULL, DESCRIPTION VARCHAR(250) NULL, NEXT_FIRE_TIME BIGINT(13) NULL, PREV_FIRE_TIME BIGINT(13) NULL, PRIORITY INTEGER NULL, TRIGGER_STATE VARCHAR(16) NOT NULL, TRIGGER_TYPE VARCHAR(8) NOT NULL, START_TIME BIGINT(13) NOT NULL, END_TIME BIGINT(13) NULL, CALENDAR_NAME VARCHAR(200) NULL, MISFIRE_INSTR SMALLINT(2) NULL, JOB_DATA BLOB NULL, PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (JOB_NAME,JOB_GROUP) REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_SIMPLE_TRIGGERS ( TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, REPEAT_COUNT BIGINT(7) NOT NULL, REPEAT_INTERVAL BIGINT(12) NOT NULL, TIMES_TRIGGERED BIGINT(10) NOT NULL, PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_CRON_TRIGGERS ( TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, CRON_EXPRESSION VARCHAR(200) NOT NULL, TIME_ZONE_ID VARCHAR(80), PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_BLOB_TRIGGERS ( TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, BLOB_DATA BLOB NULL, PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_TRIGGER_LISTENERS ( TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, TRIGGER_LISTENER VARCHAR(200) NOT NULL, PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER), FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_CALENDARS ( CALENDAR_NAME VARCHAR(200) NOT NULL, CALENDAR BLOB NOT NULL, PRIMARY KEY (CALENDAR_NAME));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS ( TRIGGER_GROUP VARCHAR(200) NOT NULL, PRIMARY KEY (TRIGGER_GROUP));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_SCHEDULER_STATE ( INSTANCE_NAME VARCHAR(200) NOT NULL, LAST_CHECKIN_TIME BIGINT(13) NOT NULL, CHECKIN_INTERVAL BIGINT(13) NOT NULL, PRIMARY KEY (INSTANCE_NAME));"; statement.addBatch(create_stmt); create_stmt = "CREATE TABLE QRTZ_LOCKS ( LOCK_NAME VARCHAR(40) NOT NULL, PRIMARY KEY (LOCK_NAME));"; statement.addBatch(create_stmt); create_stmt = "INSERT INTO QRTZ_LOCKS values('TRIGGER_ACCESS');"; statement.addBatch(create_stmt); create_stmt = "INSERT INTO QRTZ_LOCKS values('JOB_ACCESS');"; statement.addBatch(create_stmt); create_stmt = "INSERT INTO QRTZ_LOCKS values('CALENDAR_ACCESS');"; statement.addBatch(create_stmt); create_stmt = "INSERT INTO QRTZ_LOCKS values('STATE_ACCESS');"; statement.addBatch(create_stmt); create_stmt = "INSERT INTO QRTZ_LOCKS values('MISFIRE_ACCESS');"; statement.addBatch(create_stmt); statement.executeBatch(); statement.close(); } else { logger.log(Level.INFO, "Global Quartz Table already exists "); } connection.close(); } catch (Exception ex) { logger.log(Level.SEVERE, "Exception Creating StoreTable " + ex); } }
From source file:psiprobe.controllers.sql.ExecuteSqlController.java
@Override protected ModelAndView handleContext(String contextName, Context context, HttpServletRequest request, HttpServletResponse response) throws Exception { String resourceName = ServletRequestUtils.getStringParameter(request, "resource"); String sql = ServletRequestUtils.getStringParameter(request, "sql", null); if (sql == null || sql.equals("") || sql.trim().equals("")) { request.setAttribute("errorMessage", getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.required")); return new ModelAndView(getViewName()); }/* w w w. j a v a 2 s . co m*/ int maxRows = ServletRequestUtils.getIntParameter(request, "maxRows", 0); int rowsPerPage = ServletRequestUtils.getIntParameter(request, "rowsPerPage", 0); int historySize = ServletRequestUtils.getIntParameter(request, "historySize", 0); // store current option values and query history in a session attribute HttpSession sess = request.getSession(false); DataSourceTestInfo sessData = (DataSourceTestInfo) sess.getAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR); synchronized (sess) { if (sessData == null) { sessData = new DataSourceTestInfo(); sess.setAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR, sessData); } sessData.setMaxRows(maxRows); sessData.setRowsPerPage(rowsPerPage); sessData.setHistorySize(historySize); sessData.addQueryToHistory(sql); } DataSource dataSource = null; try { dataSource = getContainerWrapper().getResourceResolver().lookupDataSource(context, resourceName, getContainerWrapper()); } catch (NamingException e) { request.setAttribute("errorMessage", getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName })); logger.trace("", e); } if (dataSource == null) { request.setAttribute("errorMessage", getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName })); } else { List<Map<String, String>> results = null; int rowsAffected = 0; try { // TODO: use Spring's jdbc template? try (Connection conn = dataSource.getConnection()) { conn.setAutoCommit(true); try (PreparedStatement stmt = conn.prepareStatement(sql)) { boolean hasResultSet = stmt.execute(); if (!hasResultSet) { rowsAffected = stmt.getUpdateCount(); } else { results = new ArrayList<>(); try (ResultSet rs = stmt.getResultSet()) { ResultSetMetaData metaData = rs.getMetaData(); while (rs.next() && (maxRows < 0 || results.size() < maxRows)) { Map<String, String> record = new LinkedHashMap<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { String value = rs.getString(i); if (rs.wasNull()) { value = getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.sql.null"); } else { value = HtmlUtils.htmlEscape(value); } // a work around for IE browsers bug of not displaying // a border around an empty table column if (value.isEmpty()) { value = " "; } // Pad the keys of columns with existing labels so they are distinct String key = metaData.getColumnLabel(i); while (record.containsKey(key)) { key += " "; } record.put(HtmlUtils.htmlEscape(key), value); } results.add(record); } } rowsAffected = results.size(); } } } // store the query results in the session attribute in order // to support a result set pagination feature without re-executing the query synchronized (sess) { sessData.setResults(results); } ModelAndView mv = new ModelAndView(getViewName(), "results", results); mv.addObject("rowsAffected", String.valueOf(rowsAffected)); mv.addObject("rowsPerPage", String.valueOf(rowsPerPage)); return mv; } catch (SQLException e) { String message = getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.failure", new Object[] { e.getMessage() }); logger.error(message, e); request.setAttribute("errorMessage", message); } } return new ModelAndView(getViewName()); }