List of usage examples for java.sql DatabaseMetaData getDatabaseProductName
String getDatabaseProductName() throws SQLException;
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;// w ww. j a va 2 s. c o m 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:org.wso2.carbon.registry.core.utils.RegistryUtils.java
/** * Method to obtain a unique identifier for the database connection. * * @param connection the database connection. * * @return the unique identifier./*from w w w . ja v a2s.co m*/ */ public static String getConnectionId(Connection connection) { String connectionId = null; try { // The connection URL is unique enough to be used as an identifier since one thread // makes one connection to the given URL according to our model. DatabaseMetaData connectionMetaData = connection.getMetaData(); if (connectionMetaData != null) { String productName = connectionMetaData.getDatabaseProductName(); if (MY_SQL_PRODUCT_NAME.equals(productName)) { /* For MySQL getUserName() method executes 'SELECT USER()' query on DB via mysql connector causing a huge number of 'SELECT USER()' queries to be executed. Hence removing username when the DB in use is MySQL. */ connectionId = connectionMetaData.getURL(); } else { connectionId = (connectionMetaData.getUserName() != null ? connectionMetaData.getUserName().split("@")[0] : connectionMetaData.getUserName()) + "@" + connectionMetaData.getURL(); } } } catch (SQLException e) { log.error("Failed to construct the connectionId.", e); } return connectionId; }
From source file:mondrian.spi.impl.JdbcDialectImpl.java
protected String deduceProductName(DatabaseMetaData databaseMetaData) { try {/*from ww w.j av a 2s. c o m*/ return databaseMetaData.getDatabaseProductName(); } catch (SQLException e) { throw Util.newInternal(e, "while detecting database product"); } }
From source file:ro.nextreports.designer.dbviewer.DefaultDBViewer.java
public DBInfo getDBInfo(String schemaName, int mask, Connection con) throws NextSqlException { String info = ""; List<String> keywords = new ArrayList<String>(); List<DBTable> tables = new ArrayList<DBTable>(); List<DBProcedure> procedures = new ArrayList<DBProcedure>(); Dialect dialect;/*from w w w . j a v a2s .co m*/ try { dialect = DialectUtil.getDialect(con); } catch (Exception ex) { ex.printStackTrace(); throw new NextSqlException("Could not get Dialect.", ex); } try { DatabaseMetaData dbmd = con.getMetaData(); if ((mask & DBInfo.INFO) == DBInfo.INFO) { StringBuffer sb = new StringBuffer(); sb.append(I18NSupport.getString("database.product")).append(dbmd.getDatabaseProductName()) .append("\r\n"); sb.append(I18NSupport.getString("database.product.version")) .append(dbmd.getDatabaseProductVersion()).append("\r\n"); sb.append(I18NSupport.getString("database.driver.name")).append(dbmd.getDriverName()) .append("\r\n"); sb.append(I18NSupport.getString("database.driver.version")).append(dbmd.getDriverVersion()) .append("\r\n"); info = sb.toString(); } if ((mask & DBInfo.SUPPORTED_KEYWORDS) == DBInfo.SUPPORTED_KEYWORDS) { StringTokenizer st = new StringTokenizer(dbmd.getSQLKeywords(), ","); while (st.hasMoreTokens()) { keywords.add(st.nextToken()); } } // Get a ResultSet that contains all of the tables in this database // We specify a table_type of "TABLE" to prevent seeing system tables, // views and so forth boolean tableMask = ((mask & DBInfo.TABLES) == DBInfo.TABLES); boolean viewMask = ((mask & DBInfo.VIEWS) == DBInfo.VIEWS); if (tableMask || viewMask) { String[] tableTypes; if (tableMask && viewMask) { tableTypes = new String[] { "TABLE", "VIEW" }; } else if (tableMask) { tableTypes = new String[] { "TABLE" }; } else { tableTypes = new String[] { "VIEW" }; } String pattern = tableMask ? Globals.getTableNamePattern() : Globals.getViewNamePattern(); ResultSet allTables = dbmd.getTables(null, schemaName, pattern, tableTypes); try { while (allTables.next()) { String table_name = allTables.getString("TABLE_NAME"); String table_type = allTables.getString("TABLE_TYPE"); // discard recycle bin tables String ignoreTablePrefix = dialect.getRecycleBinTablePrefix(); if ((table_name == null) || ((ignoreTablePrefix != null) && table_name.startsWith(ignoreTablePrefix))) { continue; } if ((mask & DBInfo.INDEXES) == DBInfo.INDEXES) { ResultSet indexList = null; try { // Get a list of all the indexes for this table indexList = dbmd.getIndexInfo(null, schemaName, table_name, false, false); List<DBIndex> indexes = new ArrayList<DBIndex>(); while (indexList.next()) { String index_name = indexList.getString("INDEX_NAME"); String column_name = indexList.getString("COLUMN_NAME"); if (!index_name.equals("null")) { DBIndex index = new DBIndex(index_name, column_name); indexes.add(index); } } DBTable table = new DBTable(schemaName, table_name, table_type, indexes); tables.add(table); } catch (SQLException e) { throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(indexList); } } else { DBTable table = new DBTable(schemaName, table_name, table_type); tables.add(table); } } } catch (SQLException e) { throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(allTables); } } boolean procedureMask = ((mask & DBInfo.PROCEDURES) == DBInfo.PROCEDURES); if (procedureMask) { String pattern = Globals.getProcedureNamePattern(); if (pattern == null) { pattern = "%"; } ResultSet rs = dbmd.getProcedures(null, schemaName, pattern); try { while (rs.next()) { String spName = rs.getString("PROCEDURE_NAME"); int spType = rs.getInt("PROCEDURE_TYPE"); String catalog = rs.getString("PROCEDURE_CAT"); // System.out.println("Stored Procedure Name: " + spName); // if (spType == DatabaseMetaData.procedureReturnsResult) { // System.out.println("procedure Returns Result"); // } else if (spType == DatabaseMetaData.procedureNoResult) { // System.out.println("procedure No Result"); // } else { // System.out.println("procedure Result unknown"); // } procedures.add(new DBProcedure(schemaName, catalog, spName, spType)); } } catch (SQLException e) { throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(rs); } } } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } return new DBInfo(info, tables, procedures, keywords); }
From source file:org.acmsl.queryj.tools.handlers.DatabaseMetaDataRetrievalHandler.java
/** * Retrieves the product name./*from w w w.ja v a 2s . c om*/ * @param metaData the database metadata. * @return the product name. * @throws QueryJBuildException if the product name information is not available. */ @NotNull protected String retrieveProductName(@NotNull final DatabaseMetaData metaData) throws QueryJBuildException { @NotNull String result = ""; @Nullable QueryJBuildException t_ExceptionToThrow = null; try { result = metaData.getDatabaseProductName(); } catch (@NotNull final SQLException sqlException) { @Nullable final Log t_Log = UniqueLogFactory.getLog(DatabaseMetaDataRetrievalHandler.class); if (t_Log != null) { t_Log.error("Cannot retrieve database vendor's product name.", sqlException); } t_ExceptionToThrow = new CannotRetrieveDatabaseInformationException(sqlException); } if (t_ExceptionToThrow != null) { throw t_ExceptionToThrow; } return result; }
From source file:org.openbizview.util.Acccat4.java
/** * Leer Datos de categoria2/*from w w w. java2 s. c o m*/ * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String query = ""; if (b_codrol == null) { b_codrol = " - "; } if (b_codrol.equals("")) { b_codrol = " - "; } if (b_codcat1 == null) { b_codcat1 = " - "; } if (b_codcat1 == "") { b_codcat1 = " - "; } if (b_codcat2 == null) { b_codcat2 = " - "; } if (b_codcat2 == "") { b_codcat2 = " - "; } if (b_codcat3 == null) { b_codcat3 = " - "; } if (b_codcat3 == "") { b_codcat3 = " - "; } String[] veccodrol = b_codrol.split("\\ - ", -1); String[] veccodcat1 = b_codcat1.split("\\ - ", -1); String[] veccodcat2 = b_codcat2.split("\\ - ", -1); String[] veccodcat3 = b_codcat3.split("\\ - ", -1); switch (productName) { case "Oracle": query += " select * from "; query += " ( select query.*, rownum as rn from"; query += " ( SELECT trim(a.b_codrol), trim(a.b_codcat1), trim(b.descat1), trim(a.b_codcat2), trim(c.descat2), trim(a.b_codcat3), trim(d.descat3), trim(a.b_codcat4), trim(e.descat4)"; query += " FROM acccat4 a, bvtcat1 b, bvtcat2 c, bvtcat3 d, bvtcat4 e"; query += " WHERE a.b_codcat1=b.codcat1 "; query += " and a.b_codcat1=c.b_codcat1 "; query += " and a.b_codcat2=c.codcat2 "; query += " and a.b_codcat1=d.b_codcat1 "; query += " and a.b_codcat2=d.b_codcat2 "; query += " and a.b_codcat3=d.codcat3 "; query += " and a.b_codcat1=e.b_codcat1 "; query += " and a.b_codcat2=e.b_codcat2 "; query += " and a.b_codcat3=e.b_codcat3 "; query += " and a.b_codcat4=e.codcat4 "; query += " and A.instancia=B.instancia"; query += " and A.instancia=c.instancia"; query += " and A.instancia=d.instancia"; query += " and A.instancia=e.instancia"; query += " and a.b_codrol like '" + veccodrol[0] + "%'"; query += " and A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'"; query += " and A.b_codcat2 like '" + veccodcat2[0].toUpperCase() + "%'"; query += " and A.b_codcat3 like '" + veccodcat3[0].toUpperCase() + "%'"; query += " AND a.b_codcat1||b.descat1||a.b_codcat2||c.descat2||a.b_codcat3||d.descat3||a.b_codcat4||e.descat4 like '%" + ((String) filterValue).toUpperCase() + "%'"; query += " AND a.instancia = '" + instancia + "'"; query += " order by " + sortField + ") query"; query += " ) where rownum <= " + pageSize; query += " and rn > (" + first + ")"; break; case "PostgreSQL": query += " SELECT trim(a.b_codrol), trim(a.b_codcat1), trim(b.descat1), trim(a.b_codcat2), trim(c.descat2), trim(a.b_codcat3), trim(d.descat3), trim(a.b_codcat4), trim(e.descat4)"; query += " FROM acccat4 a, bvtcat1 b, bvtcat2 c, bvtcat3 d, bvtcat4 e"; query += " WHERE a.b_codcat1=b.codcat1 "; query += " and a.b_codcat1=c.b_codcat1 "; query += " and a.b_codcat2=c.codcat2 "; query += " and a.b_codcat1=d.b_codcat1 "; query += " and a.b_codcat2=d.b_codcat2 "; query += " and a.b_codcat3=d.codcat3 "; query += " and a.b_codcat1=e.b_codcat1 "; query += " and a.b_codcat2=e.b_codcat2 "; query += " and a.b_codcat3=e.b_codcat3 "; query += " and a.b_codcat4=e.codcat4 "; query += " and A.instancia=B.instancia"; query += " and A.instancia=c.instancia"; query += " and A.instancia=d.instancia"; query += " and A.instancia=e.instancia"; query += " and a.b_codrol like '" + veccodrol[0] + "%'"; query += " and A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'"; query += " and A.b_codcat2 like '" + veccodcat2[0].toUpperCase() + "%'"; query += " and A.b_codcat3 like '" + veccodcat3[0].toUpperCase() + "%'"; query += " AND a.b_codcat1||b.descat1||a.b_codcat2||c.descat2||a.b_codcat3||d.descat3||a.b_codcat4||e.descat4 like '%" + ((String) filterValue).toUpperCase() + "%'"; query += " AND a.instancia = '" + instancia + "'"; query += " order by " + sortField; query += " LIMIT " + pageSize; query += " OFFSET " + first; break; case "Microsoft SQL Server": query += " SELECT TOP " + pageSize; query += " TOT.ROW_NUM, "; query += " TOT.B_CODROL, "; query += " TOT.B_CODCAT1, "; query += " TOT.DESCAT1, "; query += " TOT.B_CODCAT2, "; query += " TOT.DESCAT2, "; query += " TOT.B_CODCAT3, "; query += " TOT.DESCAT3 "; query += " TOT.B_CODCAT4, "; query += " TOT.DESCAT4 "; query += " FROM (SELECT "; query += " ROW_NUMBER() OVER (ORDER BY A.B_CODROL ASC) AS ROW_NUM, "; query += " A.B_CODROL, "; query += " A.B_CODCAT1, "; query += " B.DESCAT1, "; query += " A.B_CODCAT2, "; query += " C.DESCAT2, "; query += " A.B_CODCAT3, "; query += " D.DESCAT3 "; query += " A.B_CODCAT4, "; query += " E.DESCAT4 "; query += " FROM ACCCAT3 A, BVTCAT1 B, BVTCAT2 C, BVTCAT3 D, BVTCAT3 E "; query += " WHERE "; query += " A.B_CODCAT1=B.CODCAT1 "; query += " AND A.B_CODCAT1=C.B_CODCAT1 "; query += " AND A.B_CODCAT2=C.CODCAT2 "; query += " AND A.B_CODCAT1=D.B_CODCAT1 "; query += " AND A.B_CODCAT2=D.B_CODCAT2 "; query += " AND A.B_CODCAT3=D.CODCAT3 "; query += " AND a.b_codcat1=e.b_codcat1 "; query += " AND a.b_codcat2=e.b_codcat2 "; query += " AND a.b_codcat3=e.b_codcat3 "; query += " AND a.b_codcat4=e.codcat4 "; query += " WHERE "; query += " TOT.B_CODROL = '" + veccodrol[0] + "'"; query += " AND TOT.B_CODCAT1 LIKE '" + veccodcat1[0].toUpperCase() + "%'"; query += " AND TOT.B_CODCAT2 LIKE '" + veccodcat2[0].toUpperCase() + "%'"; query += " AND TOT.B_CODCAT3 LIKE '" + veccodcat3[0].toUpperCase() + "%'"; query += " AND TOT.B_CODCAT1+TOT.DESCAT1+TOT.B_CODCAT2+TOT.DESCAT2+TOT.B_CODCAT3+TOT.DESCAT3+TOT.B_CODCAT4+TOT.DESCAT4 LIKE '%" + ((String) filterValue).toUpperCase() + "%'"; query += " AND tot.instancia = '" + instancia + "'"; query += " ORDER BY " + sortField; break; } pstmt = con.prepareStatement(query); ////System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Acccat4 select = new Acccat4(); select.setB_codrol(r.getString(1)); select.setB_codcat1(r.getString(2)); select.setDescat1(r.getString(3)); select.setB_codcat2(r.getString(4)); select.setDescat2(r.getString(5)); select.setB_codcat3(r.getString(6)); select.setDescat3(r.getString(7)); select.setB_codcat4(r.getString(8)); select.setDescat4(r.getString(9)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); }
From source file:com.jaspersoft.jasperserver.util.test.BaseServiceSetupTestNG.java
/** * Returns database product name to decide which schema should be used * (database specific schemas with upper case for Oracle and special date functions for SQLServer) * Suppose to use it instead of//from www. j a v a2 s . c om * test.databaseFlavor=oracle * test.foodmart.upperCaseNames=true * @param dsUri * datasource uri * @return String * database vendor name, has to be one of listed in applicationContext-semanticLayer.xml beanId = sqlGeneratorFactory */ public String getDatabaseProductName(String dsUri) { ReportDataSource ds = (ReportDataSource) getRepositoryService().getResource(m_exContext, dsUri); ReportDataSourceService jdss; if (ds instanceof JndiJdbcReportDataSource) { jdss = m_jndiJdbcDataSourceServiceFactory.createService(ds); } else { jdss = m_jdbcDataSourceServiceFactory.createService(ds); } HashMap params = new HashMap(); jdss.setReportParameterValues(params); try { Connection conn = (Connection) params.get(JRParameter.REPORT_CONNECTION); DatabaseMetaData metadata = conn.getMetaData(); jdss.closeConnection(); return metadata.getDatabaseProductName(); } catch (SQLException ex) { throw new IllegalArgumentException("Cannot get database vendor name", ex); } }
From source file:nl.b3p.gis.writers.B3pOgcSqlWriter.java
private void createTable(DatabaseMetaData dbmd, String tablename, FeatureSchema fs, String geomColumn, boolean attributeNamesToLowerCase, String srid, Geometry aGeom, boolean sameGeomType, int dimension) throws SQLException, Exception { //check if the product is supported if (SUPPORTED_DIALECTS.contains(dbmd.getDatabaseProductName())) { StringBuffer sb = new StringBuffer(); sb.append("CREATE TABLE \""); sb.append(tablename);//from w w w .j a v a2 s. co m sb.append("\"("); boolean firstColumn = true; boolean geomColumnFound = false; //create a create table script without the geometry column. for (int i = 0; i < fs.getAttributeCount(); i++) { AttributeType at = fs.getAttributeType(i); if (at.equals(AttributeType.GEOMETRY)) { geomColumnFound = true; if (geomColumn == null) { if (attributeNamesToLowerCase) { geomColumn = fs.getAttributeName(i).toLowerCase(); } else { geomColumn = fs.getAttributeName(i); } } } else { if (!firstColumn) { sb.append(", "); } firstColumn = false; sb.append("\""); if (attributeNamesToLowerCase) { sb.append(fs.getAttributeName(i).toLowerCase()); } else { sb.append(fs.getAttributeName(i)); } sb.append("\" "); if (at.equals(AttributeType.DATE)) { sb.append("date"); } else if (at.equals(AttributeType.DOUBLE)) { sb.append("double precision"); } else if (at.equals(AttributeType.INTEGER)) { sb.append("bigint"); } else { sb.append("varchar(255)"); } } } sb.append(")WITH OIDS"); StringBuffer sbg = null; //if there is a geomColum needed create the create script. if (geomColumnFound) { //if it is a postgres/postgis db if (dbmd.getDatabaseProductName().equalsIgnoreCase(SqlMetaDataUtils.PRODUCT_POSTGRES)) { // get the database name. String s = dbmd.getURL(); String url = dbmd.getURL(); String db = url.split("/")[url.split("/").length - 1]; //create the script for the addGeometryColumn function sbg = new StringBuffer(); sbg.append("select addGeometryColumn(\'"); sbg.append("public"); sbg.append("\', \'"); sbg.append(tablename); sbg.append("\', \'"); sbg.append(geomColumn); sbg.append("\', "); //if the srid is given as param use it. Otherwise try to get the srid out of the geometry or use -1 as srid if (srid != null) { sbg.append(srid); } else if (aGeom != null) { if (aGeom.getSRID() >= 0) { sbg.append(aGeom.getSRID()); } } else { sbg.append(-1); } sbg.append(", \'"); //if not all geometries are of the same type create a global geometry column if (!sameGeomType || aGeom == null) { sbg.append(POSTGRES_GLOBALGEOMETRY); } else { sbg.append(aGeom.getGeometryType().toUpperCase()); } sbg.append("\', "); //if the dimension is given use it, otherwise try to get it out of the Geometry of use 2 if (dimension > 0) { sbg.append(dimension); } else { if (aGeom.getDimension() > 1) { sbg.append(aGeom.getDimension()); } else { sbg.append("2"); } } sbg.append(")"); } } //execute the create table script. PreparedStatement statement = null; if (sbg.toString() != null) { sb.append(";"); sb.append(sbg.toString()); } statement = connection.prepareStatement(sb.toString()); statement.execute(); statement.close(); } else { log.error("CREATE TABLE not supported for " + dbmd.getDatabaseProductName()); throw new Exception("CREATE TABLE not supported for " + dbmd.getDatabaseProductName()); } }
From source file:org.openbizview.util.Sgc001b.java
/** * Leer Datos de paises// ww w . j a va 2 s . c o m * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { //System.out.println("entre al metodo SELECT"); Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String validar = "1"; String querycon = "SELECT BI_SGC014('" + login.toUpperCase() + "') AS VALIDAR FROM DUAL"; //System.out.println(querycon); //System.out.println(JNDI); consulta.selectPntGenerica(querycon, JNDI); rows = consulta.getRows(); tabla = consulta.getArray(); //System.out.println(tabla[0][0]); if (tabla[0][0].equals(validar)) { if (comp == null) { comp = " - "; } if (comp == "") { comp = " - "; } if (area == null) { area = " - "; } if (area == "") { area = " - "; } if (codigo == null) { codigo = " - "; } if (codigo == "") { codigo = " - "; } String[] veccomp = comp.split("\\ - ", -1); String[] vecarea = area.split("\\ - ", -1); String[] veccodigo = codigo.split("\\ - ", -1); //Consulta paginada String query = "SELECT * FROM"; query += "(select query.*, rownum as rn from"; query += "(SELECT A.COMP, A.AREA, A.CODIGO, A.TIPVAL, TO_CHAR(A.FECCAM,'DD/MM/YYYY') AS FECCAM, A.TOLINF, B.DESCR AS DESC1, C.DESCR AS DESC2,D.NOMIND AS DESC3, E.DESCR AS DESC4, A.REGIST "; query += "FROM SGC001B A, SGC005 B, SGC006 C, SGC001 D, TUBDER11 E "; query += "WHERE A.COMP = B.CODIGO "; query += "AND A.COMP = C.COMP "; query += "AND A.AREA = C.CODIGO "; query += "AND A.COMP = D.COMP "; query += "AND A.AREA = D.AREA "; query += "AND A.CODIGO = D.CODIGO "; query += "AND A.TIPVAL = E.CODIGO "; query += "AND TRIM(A.COMP) LIKE TRIM('%" + veccomp[0] + "%') "; query += "AND TRIM(A.AREA) LIKE TRIM('%" + vecarea[0] + "%') "; query += "AND TRIM(A.CODIGO) LIKE TRIM('%" + veccodigo[0] + "%') "; query += "GROUP BY A.COMP, A.AREA, A.CODIGO, A.TIPVAL, A.FECCAM, A.TOLINF, B.DESCR, C.DESCR, D.NOMIND, E.DESCR, A.REGIST ORDER BY 1,2,3"; query += ")query ) "; query += " WHERE ROWNUM <=" + pageSize; query += " AND rn > (" + first + ")"; pstmt = con.prepareStatement(query); //System.out.println(query); //System.out.println("***ADMINISTRADOR***"); r = pstmt.executeQuery(); while (r.next()) { Sgc001b select = new Sgc001b(); select.setZcomp(r.getString(1) + " - " + r.getString(7)); select.setZarea(r.getString(2) + " - " + r.getString(8)); select.setZcodigo(r.getString(3) + " - " + r.getString(9)); select.setZtvalti(r.getString(4) + " - " + r.getString(10)); select.setZfeccai(r.getString(5)); select.setZtolinf(r.getString(6)); select.setZdesc1(r.getString(7)); select.setZdesc2(r.getString(8)); select.setZdesc3(r.getString(9)); select.setZdesc4(r.getString(10)); select.setZdelete( r.getString(1) + "" + r.getString(2) + "" + r.getString(3) + "" + r.getString(11)); select.setZregist(r.getString(11)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); } else { if (comp == null) { comp = " - "; } if (comp == "") { comp = " - "; } if (area == null) { area = " - "; } if (area == "") { area = " - "; } if (codigo == null) { codigo = " - "; } if (codigo == "") { codigo = " - "; } String[] veccomp = comp.split("\\ - ", -1); String[] vecarea = area.split("\\ - ", -1); String[] veccodigo = codigo.split("\\ - ", -1); //Consulta paginada String query = " SELECT"; query += " TOT.*"; query += " FROM (select "; query += " DISTINCT RES.*"; query += " from (SELECT "; query += " A.COMP, A.AREA, A.CODIGO, A.TIPVAL, TO_CHAR(A.FECCAM,'DD/MM/YYYY') AS FECCAM, A.TOLINF, B.DESCR AS DESC1, C.DESCR AS DESC2, "; query += " D.NOMIND AS DESC3, E.DESCR AS DESC4, A.REGIST "; query += " FROM "; query += " SGC001B A, SGC005 B, SGC006 C, SGC001 D, TUBDER11 E, SGC009 F"; query += " WHERE "; query += " A.COMP = B.CODIGO "; query += " AND A.COMP = C.COMP "; query += " AND A.AREA = C.CODIGO "; query += " AND A.COMP = D.COMP "; query += " AND A.AREA = D.AREA "; query += " AND A.CODIGO = D.CODIGO "; query += " AND A.TIPVAL = E.CODIGO "; query += " AND A.COMP = F.COMP"; query += " AND A.AREA = F.AREA"; query += " AND A.CODIGO = F.INDICA"; query += " AND F.CODUSER = '" + login.toUpperCase() + "'"; query += " UNION ALL"; query += " SELECT "; query += " A.COMP, A.AREA, A.CODIGO, A.TIPVAL, TO_CHAR(A.FECCAM,'DD/MM/YYYY') AS FECCAM, A.TOLINF, B.DESCR AS DESC1, C.DESCR AS DESC2, "; query += " D.NOMIND AS DESC3, E.DESCR AS DESC4, A.REGIST "; query += " FROM "; query += " SGC001B A, SGC005 B, SGC006 C, SGC001 D, TUBDER11 E "; query += " WHERE "; query += " A.COMP = B.CODIGO "; query += " AND A.COMP = C.COMP "; query += " AND A.AREA = C.CODIGO "; query += " AND A.COMP = D.COMP "; query += " AND A.AREA = D.AREA "; query += " AND A.CODIGO = D.CODIGO "; query += " AND A.TIPVAL = E.CODIGO "; query += " AND A.COMP||A.AREA IN (SELECT COMP||AREA FROM SGC008 WHERE CODUSER = '" + login.toUpperCase() + "' AND DUEPRO = '1')"; query += " ) RES"; query += " WHERE "; query += " TRIM(RES.COMP) LIKE TRIM('%" + veccomp[0] + "%') "; query += " AND TRIM(RES.AREA) LIKE TRIM('%" + vecarea[0] + "%') "; query += " AND TRIM(RES.CODIGO) LIKE TRIM('%" + veccodigo[0] + "%') ORDER BY 1,2,3) TOT"; query += " WHERE "; query += " ROWNUM <=" + pageSize; query += " AND ROWNUM > (" + first + ")"; pstmt = con.prepareStatement(query); //System.out.println(query); //System.out.println("***NO ADMINISTRADOR***"); r = pstmt.executeQuery(); while (r.next()) { Sgc001b select = new Sgc001b(); select.setZcomp(r.getString(1) + " - " + r.getString(7)); select.setZarea(r.getString(2) + " - " + r.getString(8)); select.setZcodigo(r.getString(3) + " - " + r.getString(9)); select.setZtvalti(r.getString(4) + " - " + r.getString(10)); select.setZfeccai(r.getString(5)); select.setZtolinf(r.getString(6)); select.setZdesc1(r.getString(7)); select.setZdesc2(r.getString(8)); select.setZdesc3(r.getString(9)); select.setZdesc4(r.getString(10)); select.setZdelete( r.getString(1) + "" + r.getString(2) + "" + r.getString(3) + "" + r.getString(11)); select.setZregist(r.getString(11)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); } }
From source file:org.openbizview.util.Sgc001c.java
/** * Leer Datos de paises//from w ww . j a v a 2 s. c o m * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { //System.out.println("entre al metodo SELECT"); Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String validar = "1"; String querycon = "SELECT BI_SGC014('" + login.toUpperCase() + "') AS VALIDAR FROM DUAL"; //System.out.println(querycon); //System.out.println(JNDI); consulta.selectPntGenerica(querycon, JNDI); rows = consulta.getRows(); tabla = consulta.getArray(); //System.out.println(tabla[0][0]); if (tabla[0][0].equals(validar)) { if (comp == null) { comp = " - "; } if (comp == "") { comp = " - "; } if (area == null) { area = " - "; } if (area == "") { area = " - "; } if (codigo == null) { codigo = " - "; } if (codigo == "") { codigo = " - "; } String[] veccomp = comp.split("\\ - ", -1); String[] vecarea = area.split("\\ - ", -1); String[] veccodigo = codigo.split("\\ - ", -1); //Consulta paginada String query = "SELECT * FROM"; query += "(select query.*, rownum as rn from"; query += "(SELECT A.COMP, A.AREA, A.CODIGO, A.TIPVAL, TO_CHAR(A.FECCAM,'DD/MM/YYYY') AS FECCAM, A.TOLSUP, B.DESCR AS DESC1, C.DESCR AS DESC2, D.NOMIND AS DESC3, E.DESCR AS DESC4, A.REGIST "; query += "FROM SGC001C A, SGC005 B, SGC006 C, SGC001 D, TUBDER11 E "; query += "WHERE A.COMP = B.CODIGO "; query += "AND A.COMP = C.COMP "; query += "AND A.AREA = C.CODIGO "; query += "AND A.COMP = D.COMP "; query += "AND A.AREA = D.AREA "; query += "AND A.CODIGO = D.CODIGO "; query += "AND A.TIPVAL = E.CODIGO "; query += "AND TRIM(A.COMP) LIKE TRIM('%" + veccomp[0] + "%') "; query += "AND TRIM(A.AREA) LIKE TRIM('%" + vecarea[0] + "%') "; query += "AND TRIM(A.CODIGO) LIKE TRIM('%" + veccodigo[0] + "%') "; query += "GROUP BY A.COMP, A.AREA, A.CODIGO, A.TIPVAL, A.FECCAM, A.TOLSUP, B.DESCR, C.DESCR, D.NOMIND, E.DESCR, A.REGIST ORDER BY 1,2,3"; query += ")query ) "; query += " WHERE ROWNUM <=" + pageSize; query += " AND rn > (" + first + ")"; pstmt = con.prepareStatement(query); //System.out.println(query); //System.out.println("***ADMINISTRADOR***"); r = pstmt.executeQuery(); while (r.next()) { Sgc001c select = new Sgc001c(); select.setZcomp(r.getString(1) + " - " + r.getString(7)); select.setZarea(r.getString(2) + " - " + r.getString(8)); select.setZcodigo(r.getString(3) + " - " + r.getString(9)); select.setZtvalts(r.getString(4) + " - " + r.getString(10)); select.setZfeccas(r.getString(5)); select.setZtolsup(r.getString(6)); select.setZdesc1(r.getString(7)); select.setZdesc2(r.getString(8)); select.setZdesc3(r.getString(9)); select.setZdesc4(r.getString(10)); select.setZdelete( r.getString(1) + "" + r.getString(2) + "" + r.getString(3) + "" + r.getString(11)); select.setZregist(r.getString(11)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); } else { if (comp == null) { comp = " - "; } if (comp == "") { comp = " - "; } if (area == null) { area = " - "; } if (area == "") { area = " - "; } if (codigo == null) { codigo = " - "; } if (codigo == "") { codigo = " - "; } String[] veccomp = comp.split("\\ - ", -1); String[] vecarea = area.split("\\ - ", -1); String[] veccodigo = codigo.split("\\ - ", -1); //Consulta paginada String query = " SELECT"; query += " TOT.*"; query += " FROM (select "; query += " DISTINCT RES.*"; query += " from (SELECT "; query += " A.COMP, A.AREA, A.CODIGO, A.TIPVAL, TO_CHAR(A.FECCAM,'DD/MM/YYYY') AS FECCAM, A.TOLSUP, B.DESCR AS DESC1, C.DESCR AS DESC2, "; query += " D.NOMIND AS DESC3, E.DESCR AS DESC4, A.REGIST "; query += " FROM "; query += " SGC001C A, SGC005 B, SGC006 C, SGC001 D, TUBDER11 E, SGC009 F"; query += " WHERE "; query += " A.COMP = B.CODIGO "; query += " AND A.COMP = C.COMP "; query += " AND A.AREA = C.CODIGO "; query += " AND A.COMP = D.COMP "; query += " AND A.AREA = D.AREA "; query += " AND A.CODIGO = D.CODIGO "; query += " AND A.TIPVAL = E.CODIGO "; query += " AND A.COMP = F.COMP"; query += " AND A.AREA = F.AREA"; query += " AND A.CODIGO = F.INDICA"; query += " AND F.CODUSER = '" + login.toUpperCase() + "'"; query += " UNION ALL"; query += " SELECT "; query += " A.COMP, A.AREA, A.CODIGO, A.TIPVAL, TO_CHAR(A.FECCAM,'DD/MM/YYYY') AS FECCAM, A.TOLSUP, B.DESCR AS DESC1, C.DESCR AS DESC2, "; query += " D.NOMIND AS DESC3, E.DESCR AS DESC4, A.REGIST "; query += " FROM "; query += " SGC001C A, SGC005 B, SGC006 C, SGC001 D, TUBDER11 E "; query += " WHERE "; query += " A.COMP = B.CODIGO "; query += " AND A.COMP = C.COMP "; query += " AND A.AREA = C.CODIGO "; query += " AND A.COMP = D.COMP "; query += " AND A.AREA = D.AREA "; query += " AND A.CODIGO = D.CODIGO "; query += " AND A.TIPVAL = E.CODIGO "; query += " AND A.COMP||A.AREA IN (SELECT COMP||AREA FROM SGC008 WHERE CODUSER = '" + login.toUpperCase() + "' AND DUEPRO = '1')"; query += " ) RES"; query += " WHERE "; query += " TRIM(RES.COMP) LIKE TRIM('%" + veccomp[0] + "%') "; query += " AND TRIM(RES.AREA) LIKE TRIM('%" + vecarea[0] + "%') "; query += " AND TRIM(RES.CODIGO) LIKE TRIM('%" + veccodigo[0] + "%') ORDER BY 1,2,3) TOT"; query += " WHERE "; query += " ROWNUM <=" + pageSize; query += " AND ROWNUM > (" + first + ")"; pstmt = con.prepareStatement(query); //System.out.println(query); //System.out.println("***NO ADMINISTRADOR***"); r = pstmt.executeQuery(); while (r.next()) { Sgc001c select = new Sgc001c(); select.setZcomp(r.getString(1) + " - " + r.getString(7)); select.setZarea(r.getString(2) + " - " + r.getString(8)); select.setZcodigo(r.getString(3) + " - " + r.getString(9)); select.setZtvalts(r.getString(4) + " - " + r.getString(10)); select.setZfeccas(r.getString(5)); select.setZtolsup(r.getString(6)); select.setZdesc1(r.getString(7)); select.setZdesc2(r.getString(8)); select.setZdesc3(r.getString(9)); select.setZdesc4(r.getString(10)); select.setZdelete( r.getString(1) + "" + r.getString(2) + "" + r.getString(3) + "" + r.getString(11)); select.setZregist(r.getString(11)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); } }