List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:org.cloudgraph.rdb.service.JDBCSupport.java
protected List<PropertyPair> executeInsertWithGeneratedKeys(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values, Connection con) { List<PropertyPair> resultKeys = new ArrayList<PropertyPair>(); PreparedStatement statement = null; List<InputStream> streams = null; ResultSet generatedKeys = null; try {/* w w w . j a v a 2 s . co m*/ if (log.isDebugEnabled()) { log.debug("execute: " + sql.toString()); StringBuilder paramBuf = createParamDebug(values); log.debug("params: " + paramBuf.toString()); } statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS); for (PropertyPair pair : values.values()) { PlasmaProperty valueProp = pair.getProp(); if (pair.getValueProp() != null) valueProp = pair.getValueProp(); int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue()); Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue()); if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) { statement.setObject(pair.getColumn(), jdbcValue, jdbcType); } else { byte[] bytes = (byte[]) jdbcValue; long len = bytes.length; ByteArrayInputStream is = new ByteArrayInputStream(bytes); statement.setBinaryStream(pair.getColumn(), is, len); if (streams == null) streams = new ArrayList<InputStream>(); streams.add(is); } } statement.execute(); generatedKeys = statement.getGeneratedKeys(); ResultSetMetaData rsMeta = generatedKeys.getMetaData(); int numcols = rsMeta.getColumnCount(); if (log.isDebugEnabled()) log.debug("returned " + numcols + " keys"); if (generatedKeys.next()) { // FIXME; without metadata describing which properties // are actually a sequence, there is guess work // involved in matching the values returned // automatically from PreparedStatment as they // are anonymous in terms of the column names // making it impossible to match them to a metadata // property. List<Property> pkPropList = type.findProperties(KeyType.primary); if (pkPropList == null || pkPropList.size() == 0) throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'"); if (pkPropList.size() > 1) throw new DataAccessException("multiple pri-key properties found for type '" + type.getName() + "' - cannot map to generated keys"); PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0); // FIXME: need to find properties per column by physical name // alias // in case where multiple generated pri-keys for (int i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnName(i); if (log.isDebugEnabled()) log.debug("returned key column '" + columnName + "'"); int columnType = rsMeta.getColumnType(i); Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop); PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); resultKeys.add(pair); } } } catch (Throwable t) { throw new DataAccessException(t); } finally { try { if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } if (streams != null) try { for (InputStream stream : streams) stream.close(); } catch (IOException e) { log.error(e.getMessage(), e); } } return resultKeys; }
From source file:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn// w w w.ja v a 2 s . c om * ? * @param start * 0 * @param pageSize * ? * @param sql * ? * @param paramMap * ? * @return */ @SuppressWarnings("unchecked") public ResultModel getResultList(Connection conn, String sql, Map<String, Object> paramMap, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } ResultModel resultModel = new ResultModel(); boolean supportsPhysicalPage = false; PreparedStatement psmt = null; ResultSetMetaData rsmd = null; ResultSet rs = null; Dialect dialect = null; try { dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { logger.debug("sql=" + sql); supportsPhysicalPage = dialect.supportsPhysicalPage(); sql = dialect.getLimitString(sql, start, pageSize); logger.debug(">>sql=" + sql); } List<Object> values = null; if (paramMap != null) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); sql = sqlExecutor.getSql(); values = (List<Object>) sqlExecutor.getParameter(); } logger.debug("sql:\n" + sql); logger.debug("values:" + values); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); rs = psmt.executeQuery(); rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setIndex(i); column.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.lower(StringTools.camelStyle(column.getColumnLabel()))); columns.add(column); } resultModel.setHeaders(columns); if (!supportsPhysicalPage) { this.skipRows(rs, start); } int k = 0; while (rs.next() && k++ < pageSize) { int index = 0; RowModel rowModel = new RowModel(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); ColumnDefinition c = new ColumnDefinition(); c.setColumnName(column.getColumnName()); c.setColumnLabel(column.getColumnLabel()); c.setName(column.getName()); c.setJavaType(column.getJavaType()); c.setPrecision(column.getPrecision()); c.setScale(column.getScale()); String javaType = column.getJavaType(); index = index + 1; if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); c.setValue(value); } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.longValue()); } } else if ("Double".equals(javaType)) { try { Double value = rs.getDouble(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.doubleValue()); } } else if ("Boolean".equals(javaType)) { Boolean value = rs.getBoolean(column.getIndex()); c.setValue(value); } else if ("Date".equals(javaType)) { Timestamp value = rs.getTimestamp(column.getIndex()); c.setValue(value); } else { c.setValue(rs.getObject(column.getIndex())); } rowModel.addColumn(c); } resultModel.addRow(rowModel); } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return resultModel; }
From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * @param fromConn/*w w w . j a v a2 s. co m*/ * @param toConn * @param sql * @param fromTableName * @param toTableName * @param colNewToOldMap * @param verbatimDateMapper * @param newColDefValues * @param sourceServerType * @param destServerType * @return */ public static boolean copyTable(final Connection fromConn, final Connection toConn, final String sql, final String countSQL, final String fromTableName, final String toTableName, final Map<String, String> colNewToOldMap, final Map<String, String> verbatimDateMapper, final Map<String, String> newColDefValues, final SERVERTYPE sourceServerType, final SERVERTYPE destServerType) { //Timestamp now = new Timestamp(System.currentTimeMillis()); IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); if (frame != null) { frame.setDesc("Copying Table " + fromTableName); } log.info("Copying Table " + fromTableName); List<String> fromFieldNameList = getFieldNamesFromSchema(fromConn, fromTableName); String sqlStr = sql + " ORDER BY " + fromTableName + "." + fromFieldNameList.get(0); log.debug(sqlStr); int numRecs; if (countSQL == null) { numRecs = getNumRecords(fromConn, fromTableName); } else { numRecs = getCountAsInt(fromConn, countSQL); } setProcess(0, numRecs); DBTableInfo tblInfo = DBTableIdMgr.getInstance().getInfoByTableName(toTableName); Statement updateStatement = null; String id = ""; try { updateStatement = toConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { BasicSQLUtils.removeForeignKeyConstraints(toConn, BasicSQLUtils.myDestinationServerType); } //HashMap<String, Integer> newDBFieldHash = new HashMap<String, Integer>(); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(toConn, toTableName); //int inx = 1; //for (FieldMetaData fmd : newFieldMetaData) //{ // newDBFieldHash.put(fmd.getName(), inx++); //} Statement stmt = fromConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); //System.out.println(sqlStr); ResultSet rs = stmt.executeQuery(sqlStr); ResultSetMetaData rsmd = rs.getMetaData(); Vector<Integer> dateColumns = new Vector<Integer>(); //System.out.println(toTableName); Hashtable<String, Integer> fromHash = new Hashtable<String, Integer>(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { String colName = rsmd.getColumnName(i); fromHash.put(colName, i); //System.out.println(rsmd.getColumnName(i)+" -> "+i); if (rsmd.getColumnType(i) == java.sql.Types.DATE || colName.toLowerCase().endsWith("date") || colName.toLowerCase().startsWith("date")) { //System.out.println("Date: "+rsmd.getColumnName(i)+" -> "+i); dateColumns.add(i); } } Hashtable<String, String> oldNameToNewNameHash = new Hashtable<String, String>(); if (colNewToOldMap != null) { for (String newName : colNewToOldMap.keySet()) { String oldName = colNewToOldMap.get(newName); System.out .println("Mapping oldName[" + (oldName == null ? newName : oldName) + " -> " + newName); oldNameToNewNameHash.put(oldName == null ? newName : oldName, newName); } } // System.out.println("Num Cols: "+rsmd.getColumnCount()); Map<String, PartialDateConv> dateMap = new Hashtable<String, PartialDateConv>(); String insertSQL = null; // Get the columns that have dates in case we get a TimestampCreated date that is null // and then we can go looking for an older date to try to figure it out Integer timestampModifiedInx = fromHash.get("TimestampModified"); Integer timestampCreatedInx = fromHash.get("TimestampCreated"); boolean isAccessionTable = fromTableName.equals("accession"); boolean hasInstIdCol = fromTableName.equals("permit") || fromTableName.equals("journal") || fromTableName.equals("referencework"); StringBuffer str = new StringBuffer(1024); int count = 0; while (rs.next()) { boolean skipRecord = false; dateMap.clear(); // Start by going through the resultset and converting all dates from Integers // to real dates and keep the verbatium date information if it is a partial date for (int i : dateColumns) { String oldColName = rsmd.getColumnName(i); Integer oldColIndex = fromHash.get(oldColName); if (oldColIndex == null) { log.error("Couldn't find new column for old column for date for Table[" + fromTableName + "] Col Name[" + newFieldMetaData.get(i).getName() + "]"); continue; } if (oldColIndex > newFieldMetaData.size()) { continue; } String newColName = colNewToOldMap != null ? oldNameToNewNameHash.get(oldColName) : null; if (newColName == null) { newColName = oldColName; } Object dataObj = rs.getObject(i); if (dataObj instanceof Integer) { PartialDateConv datep = new PartialDateConv(); getPartialDate((Integer) dataObj, datep); // fills in Verbatim also dateMap.put(newColName, datep); } } // OK here we make sure that both the created dated ad modified date are not null // and we copy the date if one has a value and the other does not. Date timestampCreatedCached = now; Date timestampModifiedCached = now; if (timestampModifiedInx != null && timestampCreatedInx != null) { timestampModifiedCached = rs.getDate(timestampModifiedInx); timestampCreatedCached = rs.getDate(timestampCreatedInx); if (timestampModifiedCached == null && timestampCreatedCached == null) { timestampCreatedCached = Calendar.getInstance().getTime(); timestampModifiedCached = Calendar.getInstance().getTime(); } else if (timestampModifiedCached == null && timestampCreatedCached != null) { timestampModifiedCached = new Date(timestampCreatedCached.getTime()); } else { timestampCreatedCached = timestampModifiedCached != null ? new Date(timestampModifiedCached.getTime()) : new Date(); } } else { if (timestampModifiedInx != null) { timestampModifiedCached = rs.getDate(timestampModifiedInx); if (timestampModifiedCached == null) { timestampModifiedCached = now; } } if (timestampCreatedInx != null) { timestampCreatedCached = rs.getDate(timestampCreatedInx); if (timestampCreatedCached == null) { timestampCreatedCached = now; } } } str.setLength(0); if (insertSQL == null) { StringBuffer fieldList = new StringBuffer(); fieldList.append("( "); for (int i = 0; i < newFieldMetaData.size(); i++) { if ((i > 0) && (i < newFieldMetaData.size())) { fieldList.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName + " "); } fieldList.append(")"); str.append("INSERT INTO " + toTableName + " " + fieldList + " VALUES ("); insertSQL = str.toString(); log.debug(str); } else { str.append(insertSQL); } id = rs.getString(1); // For each column in the new DB table... for (int i = 0; i < newFieldMetaData.size(); i++) { FieldMetaData newFldMetaData = newFieldMetaData.get(i); String newColName = newFldMetaData.getName(); String oldMappedColName = null; //System.out.println("["+newColName+"]"); // Get the Old Column Index from the New Name // String oldName = colNewToOldMap != null ? colNewToOldMap.get(newColName) : newColName; Integer columnIndex = fromHash.get(newColName); if (columnIndex == null && colNewToOldMap != null) { oldMappedColName = colNewToOldMap.get(newColName); if (oldMappedColName != null) { columnIndex = fromHash.get(oldMappedColName); } else if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldNames == null || ignoreMappingFieldNames.get(newColName) == null)) { String msg = "No Map for table [" + fromTableName + "] from New Name[" + newColName + "] to Old Name[" + oldMappedColName + "]"; log.error(msg); writeErrLog(msg); } } else { oldMappedColName = newColName; } String verbatimDateFieldName = null; if (verbatimDateMapper != null) { verbatimDateFieldName = verbatimDateMapper.get(newColName); } //System.out.println("new["+newColName+"] old["+oldMappedColName+"]"); if (columnIndex != null) { if (i > 0) str.append(", "); Object dataObj = rs.getObject(columnIndex); if (idMapperMgr != null && oldMappedColName != null && oldMappedColName.endsWith("ID")) { IdMapperIFace idMapper = idMapperMgr.get(fromTableName, oldMappedColName); if (idMapper != null) { int showNullOption = SHOW_NULL_FK; int showFkLookUpOption = SHOW_FK_LOOKUP; int oldPrimaryKeyId = rs.getInt(columnIndex); if (oldMappedColName.equalsIgnoreCase(fromTableName + "id")) { showNullOption = SHOW_NULL_PM; showFkLookUpOption = SHOW_PM_LOOKUP; } // if the value was null, getInt() returns 0 // use wasNull() to distinguish real 0 from a null return if (rs.wasNull()) { dataObj = null; if (isOptionOn(showNullOption)) { String msg = "Unable to Map " + (showNullOption == SHOW_NULL_FK ? "Foreign" : "Primary") + " Key Id[NULL] old Name[" + oldMappedColName + "] colInx[" + columnIndex + "] newColName[" + newColName + "]"; log.error(msg); writeErrLog(msg); skipRecord = true; } } else { dataObj = idMapper.get(oldPrimaryKeyId); if (dataObj == null && isOptionOn(showFkLookUpOption)) { String msg = "Unable to Map Primary Id[" + oldPrimaryKeyId + "] old Name[" + oldMappedColName + "] table[" + fromTableName + "]"; log.error(msg); writeErrLog(msg); skipRecord = true; } } } else { if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldIDs == null || ignoreMappingFieldIDs.get(oldMappedColName) == null)) { // !!!!!!!!!!!!!!!!!!!!!!!!!!!!! // XXX Temporary fix so it doesn't hide other errors // Josh has promised his first born if he doesn't fix this! // !!!!!!!!!!!!!!!!!!!!!!!!!!!!! if (!oldMappedColName.equals("RankID")) { //idMapperMgr.dumpKeys(); String msg = "No ID Map for [" + fromTableName + "] Old Column Name[" + oldMappedColName + "]"; log.error(msg); writeErrLog(msg); skipRecord = true; } } } } // First check to see if it is null if (dataObj == null) { if (newFldMetaData.getName().equals("TimestampCreated")) { if (timestampCreatedInx != null) { if (isAccessionTable) { Date date = UIHelper .convertIntToDate(rs.getInt(fromHash.get("DateAccessioned"))); str.append(date != null ? getStrValue(date) : getStrValue(timestampCreatedCached, newFldMetaData.getType())); } else { str.append(getStrValue(timestampCreatedCached, newFldMetaData.getType())); } } else { str.append(getStrValue(timestampCreatedCached, newFldMetaData.getType())); } } else if (newFldMetaData.getName().equals("TimestampModified")) { if (timestampModifiedInx != null) { if (isAccessionTable) { Date date = UIHelper .convertIntToDate(rs.getInt(fromHash.get("DateAccessioned"))); str.append(date != null ? getStrValue(date) : getStrValue(timestampCreatedCached, newFldMetaData.getType())); } else { str.append(getStrValue(timestampModifiedCached, newFldMetaData.getType())); } } else { str.append(getStrValue(timestampModifiedCached, newFldMetaData.getType())); } } else { str.append("NULL"); } } else if (dataObj instanceof Integer && (newFldMetaData.getSqlType() == java.sql.Types.DATE || newColName.toLowerCase().endsWith("date") || newColName.toLowerCase().startsWith("date"))) { PartialDateConv datePr = dateMap.get(newColName); if (datePr != null) { str.append(datePr.getDateStr()); } else { str.append("NULL"); } } else if (verbatimDateFieldName != null) { PartialDateConv datePr = dateMap.get(newColName); str.append(datePr != null ? datePr.getVerbatim() : "NULL"); } else if (dataObj instanceof Number) { DBFieldInfo fi = tblInfo.getFieldByColumnName(newColName); String type = newFldMetaData.getType().toLowerCase().startsWith("tiny") ? fi.getType() : newFldMetaData.getType(); str.append(getStrValue(dataObj, type)); } else { if (columnValueMapper != null) { BasicSQLUtilsMapValueIFace valueMapper = columnValueMapper.get(newColName); if (valueMapper != null) { dataObj = valueMapper.mapValue(dataObj); } } if (dataObj instanceof String && newFldMetaData.isString()) { DBFieldInfo fi = tblInfo.getFieldByColumnName(newColName); String s = (String) dataObj; if (s.length() > fi.getLength()) { String msg = String.format( "Truncating Table '%s' Field '%s' with Length %d, db len %d Value[%s]", toTableName, newColName, s.length(), fi.getLength(), s); tblWriter.logError(msg); log.error(msg); dataObj = s.substring(0, fi.getLength()); } } str.append(getStrValue(dataObj, newFldMetaData.getType())); } } else if (hasInstIdCol && newFldMetaData.getName().equals("InstitutionID")) { if (i > 0) str.append(", "); str.append("1"); } else if (newColName.endsWith("Version")) { if (i > 0) str.append(", "); str.append("0"); } else if (newColName.endsWith("DatePrecision")) { if (i > 0) str.append(", "); String cName = newColName.substring(0, newColName.length() - 9); PartialDateConv datePr = dateMap.get(cName); if (datePr != null) { str.append(datePr.getPartial()); } else { str.append("NULL"); } } else if (idMapperMgr != null && newColName.endsWith("ID") && oneToOneIDHash != null && oneToOneIDHash.get(newColName) != null) { IdMapperIFace idMapper = idMapperMgr.get(toTableName, newColName); if (idMapper != null) { idMapper.setShowLogErrors(false); Integer newPrimaryId = idMapper.get(Integer.parseInt(id)); if (newPrimaryId != null) { if (i > 0) str.append(", "); str.append(newPrimaryId); } else { if (i > 0) str.append(", "); str.append("NULL"); if (isOptionOn(SHOW_VAL_MAPPING_ERROR)) { String msg = "For Table[" + fromTableName + "] mapping new Column Name[" + newColName + "] ID[" + id + "] was not mapped"; log.error(msg); writeErrLog(msg); skipRecord = true; } } } } else // there was no old column that maps to this new column { String newColValue = null; if (newColDefValues != null) { newColValue = newColDefValues.get(newColName); } if (newColValue == null) { newColValue = "NULL"; //System.out.println("ignoreMappingFieldNames" + ignoreMappingFieldNames); //System.out.println("ignoreMappingFieldNames.get(colName)" + ignoreMappingFieldNames.get(colName)); if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldNames == null || ignoreMappingFieldNames.get(newColName) == null)) { String msg = "For Table[" + fromTableName + "] mapping new Column Name[" + newColName + "] was not mapped"; log.error(msg); writeErrLog(msg); skipRecord = true; } } if (i > 0) str.append(", "); BasicSQLUtilsMapValueIFace valueMapper = columnValueMapper.get(newFldMetaData.getName()); if (valueMapper != null) { newColValue = valueMapper.mapValue(newColValue); } str.append(newColValue); } } str.append(")"); if (frame != null) { if (count % 500 == 0) { frame.setProcess(count); } } else { if (count % 2000 == 0) { log.info(toTableName + " processed: " + count); } } //setQuotedIdentifierOFFForSQLServer(toConn, BasicSQLUtils.myDestinationServerType); //exeUpdateCmd(updateStatement, "SET FOREIGN_KEY_CHECKS = 0"); //if (str.toString().toLowerCase().contains("insert into locality")) //{ //log.debug(str.toString()); //} //String str2 = "SET QUOTED_IDENTIFIER ON"; //log.debug("executing: " + str); //updateStatement.execute(str2); // updateStatement.close(); if (!skipRecord) { if (isOptionOn(SHOW_COPY_TABLE)) { log.debug("executing: " + str); } int retVal = exeUpdateCmd(updateStatement, str.toString()); if (retVal == -1) { rs.close(); stmt.clearBatch(); stmt.close(); return false; } } count++; // if (count == 1) break; } if (frame != null) { frame.setProcess(count); } else { log.info(fromTableName + " processed " + count + " records."); } rs.close(); stmt.clearBatch(); stmt.close(); } catch (SQLException ex) { ex.printStackTrace(); edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex); //e.printStackTrace(); log.error(sqlStr); log.error(ex); log.error("ID: " + id); } finally { try { updateStatement.clearBatch(); updateStatement.close(); } catch (SQLException ex) { } } BasicSQLUtils.setFieldsToIgnoreWhenMappingNames(null);//meg added return true; }
From source file:org.agnitas.dao.impl.RecipientDaoImpl.java
@Override public CaseInsensitiveMap<CsvColInfo> readDBColumns(int companyID) { String sqlGetTblStruct = "SELECT * FROM customer_" + companyID + "_tbl WHERE 1=0"; CsvColInfo aCol = null;/*from w ww . j a v a2 s. c om*/ int colType; CaseInsensitiveMap<CsvColInfo> dbAllColumns = new CaseInsensitiveMap<CsvColInfo>(); DataSource ds = (DataSource) this.applicationContext.getBean("dataSource"); Connection con = DataSourceUtils.getConnection(ds); try { Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery(sqlGetTblStruct); ResultSetMetaData meta = rset.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) { if (!meta.getColumnName(i).equals("change_date") && !meta.getColumnName(i).equals("creation_date") && !meta.getColumnName(i).equals("datasource_id")) { // if (meta.getColumnName(i).equals("customer_id")) { // if (status == null) { // initStatus(getWebApplicationContext()); // } // if (!( mode == ImportWizardServiceImpleImpl.MODE_ONLY_UPDATE && status.getKeycolumn().equals("customer_id"))) { // continue; // } // } aCol = new CsvColInfo(); aCol.setName(meta.getColumnName(i)); aCol.setLength(meta.getColumnDisplaySize(i)); aCol.setType(CsvColInfo.TYPE_UNKNOWN); aCol.setActive(false); aCol.setNullable(meta.isNullable(i) != 0); colType = meta.getColumnType(i); aCol.setType(dbTypeToCsvType(colType)); dbAllColumns.put(meta.getColumnName(i), aCol); } } rset.close(); stmt.close(); } catch (Exception e) { logger.error("readDBColumns (companyID: " + companyID + ")", e); } DataSourceUtils.releaseConnection(con, ds); return dbAllColumns; }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testResultSetMetaData() throws SQLException { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, " + "c1*2, sentences(null, null, null) as b, c17, c18, c20, c21, c22, c23 from " + dataTypeTableName + " limit 1"); ResultSetMetaData meta = res.getMetaData(); ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null); assertEquals(20, meta.getColumnCount()); assertTrue(colRS.next());/* www . j a v a 2 s . com*/ assertEquals("c1", meta.getColumnName(1)); assertEquals(Types.INTEGER, meta.getColumnType(1)); assertEquals("int", meta.getColumnTypeName(1)); assertEquals(11, meta.getColumnDisplaySize(1)); assertEquals(10, meta.getPrecision(1)); assertEquals(0, meta.getScale(1)); assertEquals("c1", colRS.getString("COLUMN_NAME")); assertEquals(Types.INTEGER, colRS.getInt("DATA_TYPE")); assertEquals("int", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(1), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(1), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c2", meta.getColumnName(2)); assertEquals("boolean", meta.getColumnTypeName(2)); assertEquals(Types.BOOLEAN, meta.getColumnType(2)); assertEquals(1, meta.getColumnDisplaySize(2)); assertEquals(1, meta.getPrecision(2)); assertEquals(0, meta.getScale(2)); assertEquals("c2", colRS.getString("COLUMN_NAME")); assertEquals(Types.BOOLEAN, colRS.getInt("DATA_TYPE")); assertEquals("boolean", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getScale(2), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c3", meta.getColumnName(3)); assertEquals(Types.DOUBLE, meta.getColumnType(3)); assertEquals("double", meta.getColumnTypeName(3)); assertEquals(25, meta.getColumnDisplaySize(3)); assertEquals(15, meta.getPrecision(3)); assertEquals(15, meta.getScale(3)); assertEquals("c3", colRS.getString("COLUMN_NAME")); assertEquals(Types.DOUBLE, colRS.getInt("DATA_TYPE")); assertEquals("double", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(3), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(3), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c4", meta.getColumnName(4)); assertEquals(Types.VARCHAR, meta.getColumnType(4)); assertEquals("string", meta.getColumnTypeName(4)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(4)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(4)); assertEquals(0, meta.getScale(4)); assertEquals("c4", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("string", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(4), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(4), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("a", meta.getColumnName(5)); assertEquals(Types.ARRAY, meta.getColumnType(5)); assertEquals("array", meta.getColumnTypeName(5)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(5)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(5)); assertEquals(0, meta.getScale(5)); assertEquals("c5", colRS.getString("COLUMN_NAME")); assertEquals(Types.ARRAY, colRS.getInt("DATA_TYPE")); assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c6", meta.getColumnName(6)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(6)); assertEquals("map", meta.getColumnTypeName(6)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(6)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(6)); assertEquals(0, meta.getScale(6)); assertEquals("c6", colRS.getString("COLUMN_NAME")); assertEquals(Types.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c7", meta.getColumnName(7)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(7)); assertEquals("map", meta.getColumnTypeName(7)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(7)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(7)); assertEquals(0, meta.getScale(7)); assertEquals("c7", colRS.getString("COLUMN_NAME")); assertEquals(Types.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c8", meta.getColumnName(8)); assertEquals(Types.STRUCT, meta.getColumnType(8)); assertEquals("struct", meta.getColumnTypeName(8)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(8)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(8)); assertEquals(0, meta.getScale(8)); assertEquals("c8", colRS.getString("COLUMN_NAME")); assertEquals(Types.STRUCT, colRS.getInt("DATA_TYPE")); assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c9", meta.getColumnName(9)); assertEquals(Types.TINYINT, meta.getColumnType(9)); assertEquals("tinyint", meta.getColumnTypeName(9)); assertEquals(4, meta.getColumnDisplaySize(9)); assertEquals(3, meta.getPrecision(9)); assertEquals(0, meta.getScale(9)); assertEquals("c9", colRS.getString("COLUMN_NAME")); assertEquals(Types.TINYINT, colRS.getInt("DATA_TYPE")); assertEquals("tinyint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(9), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(9), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c10", meta.getColumnName(10)); assertEquals(Types.SMALLINT, meta.getColumnType(10)); assertEquals("smallint", meta.getColumnTypeName(10)); assertEquals(6, meta.getColumnDisplaySize(10)); assertEquals(5, meta.getPrecision(10)); assertEquals(0, meta.getScale(10)); assertEquals("c10", colRS.getString("COLUMN_NAME")); assertEquals(Types.SMALLINT, colRS.getInt("DATA_TYPE")); assertEquals("smallint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(10), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(10), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c11", meta.getColumnName(11)); assertEquals(Types.FLOAT, meta.getColumnType(11)); assertEquals("float", meta.getColumnTypeName(11)); assertEquals(24, meta.getColumnDisplaySize(11)); assertEquals(7, meta.getPrecision(11)); assertEquals(7, meta.getScale(11)); assertEquals("c11", colRS.getString("COLUMN_NAME")); assertEquals(Types.FLOAT, colRS.getInt("DATA_TYPE")); assertEquals("float", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(11), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(11), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c12", meta.getColumnName(12)); assertEquals(Types.BIGINT, meta.getColumnType(12)); assertEquals("bigint", meta.getColumnTypeName(12)); assertEquals(20, meta.getColumnDisplaySize(12)); assertEquals(19, meta.getPrecision(12)); assertEquals(0, meta.getScale(12)); assertEquals("c12", colRS.getString("COLUMN_NAME")); assertEquals(Types.BIGINT, colRS.getInt("DATA_TYPE")); assertEquals("bigint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(12), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(12), colRS.getInt("DECIMAL_DIGITS")); assertEquals("_c12", meta.getColumnName(13)); assertEquals(Types.INTEGER, meta.getColumnType(13)); assertEquals("int", meta.getColumnTypeName(13)); assertEquals(11, meta.getColumnDisplaySize(13)); assertEquals(10, meta.getPrecision(13)); assertEquals(0, meta.getScale(13)); assertEquals("b", meta.getColumnName(14)); assertEquals(Types.ARRAY, meta.getColumnType(14)); assertEquals("array", meta.getColumnTypeName(14)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(14)); assertEquals(0, meta.getScale(14)); // Move the result of getColumns() forward to match the columns of the query assertTrue(colRS.next()); // c13 assertTrue(colRS.next()); // c14 assertTrue(colRS.next()); // c15 assertTrue(colRS.next()); // c16 assertTrue(colRS.next()); // c17 assertEquals("c17", meta.getColumnName(15)); assertEquals(Types.TIMESTAMP, meta.getColumnType(15)); assertEquals("timestamp", meta.getColumnTypeName(15)); assertEquals(29, meta.getColumnDisplaySize(15)); assertEquals(29, meta.getPrecision(15)); assertEquals(9, meta.getScale(15)); assertEquals("c17", colRS.getString("COLUMN_NAME")); assertEquals(Types.TIMESTAMP, colRS.getInt("DATA_TYPE")); assertEquals("timestamp", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(15), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(15), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c18", meta.getColumnName(16)); assertEquals(Types.DECIMAL, meta.getColumnType(16)); assertEquals("decimal", meta.getColumnTypeName(16)); assertEquals(18, meta.getColumnDisplaySize(16)); assertEquals(16, meta.getPrecision(16)); assertEquals(7, meta.getScale(16)); assertEquals("c18", colRS.getString("COLUMN_NAME")); assertEquals(Types.DECIMAL, colRS.getInt("DATA_TYPE")); assertEquals("decimal", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(16), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(16), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); // skip c19, since not selected by query assertTrue(colRS.next()); assertEquals("c20", meta.getColumnName(17)); assertEquals(Types.DATE, meta.getColumnType(17)); assertEquals("date", meta.getColumnTypeName(17)); assertEquals(10, meta.getColumnDisplaySize(17)); assertEquals(10, meta.getPrecision(17)); assertEquals(0, meta.getScale(17)); assertEquals("c20", colRS.getString("COLUMN_NAME")); assertEquals(Types.DATE, colRS.getInt("DATA_TYPE")); assertEquals("date", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(17), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(17), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c21", meta.getColumnName(18)); assertEquals(Types.VARCHAR, meta.getColumnType(18)); assertEquals("varchar", meta.getColumnTypeName(18)); // varchar columns should have correct display size/precision assertEquals(20, meta.getColumnDisplaySize(18)); assertEquals(20, meta.getPrecision(18)); assertEquals(0, meta.getScale(18)); assertEquals("c21", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("varchar", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(18), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(18), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c22", meta.getColumnName(19)); assertEquals(Types.CHAR, meta.getColumnType(19)); assertEquals("char", meta.getColumnTypeName(19)); // char columns should have correct display size/precision assertEquals(15, meta.getColumnDisplaySize(19)); assertEquals(15, meta.getPrecision(19)); assertEquals(0, meta.getScale(19)); assertEquals("c23", meta.getColumnName(20)); assertEquals(Types.BINARY, meta.getColumnType(20)); assertEquals("binary", meta.getColumnTypeName(20)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(20)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(20)); assertEquals(0, meta.getScale(20)); assertEquals("c22", colRS.getString("COLUMN_NAME")); assertEquals(Types.CHAR, colRS.getInt("DATA_TYPE")); assertEquals("char", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(19), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(19), colRS.getInt("DECIMAL_DIGITS")); for (int i = 1; i <= meta.getColumnCount(); i++) { assertFalse(meta.isAutoIncrement(i)); assertFalse(meta.isCurrency(i)); assertEquals(ResultSetMetaData.columnNullable, meta.isNullable(i)); } }
From source file:com.p5solutions.core.jpa.orm.EntityUtility.java
/** * Build the database-meta-dta for a given table entity, using an existing * connection./*from w w w. j a v a 2 s.com*/ * * @param table * annotation * @param detail * {@link EntityDetail} probably provided by the * {@link #cacheEntityDetails} * @param connection * an existing mock or real, database connection. */ protected void buildColumnMetaData(Table table, EntityDetail<?> detail, Connection connection) { Statement stmt = null; ResultSet rs = null; try { String sql = "SELECT * FROM " + table.name() + " WHERE 1=0"; stmt = connection.createStatement(); // set the maximum result set to zero, just in-case!? stmt.setMaxRows(0); rs = stmt.executeQuery(sql); ResultSetMetaData rsMeta = rs.getMetaData(); logger.info("** Building Database MetaData for Table " + table.name()); for (int ic = 1; ic <= rsMeta.getColumnCount(); ic++) { String columnName = rsMeta.getColumnName(ic); ParameterBinder binder = detail.getParameterBinderByAny(columnName); if (binder == null) { if (logger.isErrorEnabled()) { String error = " -- Column " + columnName + " as defined by the table meta-data, cannot be found within the scope of " + detail.getEntityClass(); logger.error(error); } // TODO ?? throw new RuntimeException(new // NoColumnDefinedException(error)); } else { ParameterBinderColumnMetaData columnMetaData = new ParameterBinderColumnMetaData(); // columnMetaData.setColumnIndex(ic); // USELESS, EVERY UNIQUE QUERY // STRING WOULD RESULT IN A DIFFERENT INDEX. EASIER TO CACHE IT BASED // ON UNIQUE QUERY STRINGS. // columnMetaData.setColumnLabel(rsMeta.getColumnLabel(ic)); columnMetaData.setColumnName(columnName); columnMetaData.setLength(rsMeta.getColumnDisplaySize(ic)); columnMetaData.setPrecision(rsMeta.getPrecision(ic)); columnMetaData.setScale(rsMeta.getScale(ic)); columnMetaData.setColumnType(rsMeta.getColumnType(ic)); columnMetaData.setColumnTypeName(rsMeta.getColumnTypeName(ic)); binder.setColumnMetaData(columnMetaData); if (logger.isDebugEnabled()) { logger.debug(" -- [" + columnMetaData.toString() + "]"); } } } } catch (SQLException e) { logger.error(">> *UNABLE* to retrieve meta data for table " + table.name() + ", doesn't exist?"); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { ; } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { ; } stmt = null; } } }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private DataEntry getDataEntryFromRS(ResultSet rs) throws SQLException { DataEntry dataEntry = new DataEntry(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); int columnType; String value;/*from ww w .j av a2 s.c o m*/ ParamValue paramValue; Time sqlTime; Date sqlDate; Timestamp sqlTimestamp; Blob sqlBlob; BigDecimal bigDecimal; InputStream binInStream; boolean useColumnNumbers = this.isUsingColumnNumbers(); for (int i = 1; i <= columnCount; i++) { /* retrieve values according to the column type */ columnType = metaData.getColumnType(i); switch (columnType) { /* handle string types */ case Types.VARCHAR: /* fall through */ case Types.LONGVARCHAR: /* fall through */ case Types.CHAR: /* fall through */ case Types.CLOB: /* fall through */ case Types.NCHAR: /* fall through */ case Types.NCLOB: /* fall through */ case Types.NVARCHAR: /* fall through */ case Types.LONGNVARCHAR: value = rs.getString(i); paramValue = new ParamValue(value); break; /* handle numbers */ case Types.INTEGER: /* fall through */ case Types.TINYINT: /* fall through */ case Types.SMALLINT: value = ConverterUtil.convertToString(rs.getInt(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.DOUBLE: value = ConverterUtil.convertToString(rs.getDouble(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.FLOAT: value = ConverterUtil.convertToString(rs.getFloat(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.BOOLEAN: /* fall through */ case Types.BIT: value = ConverterUtil.convertToString(rs.getBoolean(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.DECIMAL: bigDecimal = rs.getBigDecimal(i); if (bigDecimal != null) { value = ConverterUtil.convertToString(bigDecimal); } else { value = null; } paramValue = new ParamValue(value); break; /* handle data/time values */ case Types.TIME: /* handle time data type */ sqlTime = rs.getTime(i); if (sqlTime != null) { value = this.convertToTimeString(sqlTime); } else { value = null; } paramValue = new ParamValue(value); break; case Types.DATE: /* handle date data type */ sqlDate = rs.getDate(i); if (sqlDate != null) { value = ConverterUtil.convertToString(sqlDate); } else { value = null; } paramValue = new ParamValue(value); break; case Types.TIMESTAMP: sqlTimestamp = rs.getTimestamp(i, calendar); if (sqlTimestamp != null) { value = this.convertToTimestampString(sqlTimestamp); } else { value = null; } paramValue = new ParamValue(value); break; /* handle binary types */ case Types.BLOB: sqlBlob = rs.getBlob(i); if (sqlBlob != null) { value = this.getBase64StringFromInputStream(sqlBlob.getBinaryStream()); } else { value = null; } paramValue = new ParamValue(value); break; case Types.BINARY: /* fall through */ case Types.LONGVARBINARY: /* fall through */ case Types.VARBINARY: binInStream = rs.getBinaryStream(i); if (binInStream != null) { value = this.getBase64StringFromInputStream(binInStream); } else { value = null; } paramValue = new ParamValue(value); break; /* handling User Defined Types */ case Types.STRUCT: Struct udt = (Struct) rs.getObject(i); paramValue = new ParamValue(udt); break; case Types.ARRAY: paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY); Array dataArray = (Array) rs.getObject(i); if (dataArray == null) { break; } paramValue = this.processSQLArray(dataArray, paramValue); break; case Types.NUMERIC: bigDecimal = rs.getBigDecimal(i); if (bigDecimal != null) { value = ConverterUtil.convertToString(bigDecimal); } else { value = null; } paramValue = new ParamValue(value); break; case Types.BIGINT: value = ConverterUtil.convertToString(rs.getLong(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; /* handle all other types as strings */ default: value = rs.getString(i); paramValue = new ParamValue(value); break; } dataEntry.addValue(useColumnNumbers ? Integer.toString(i) : metaData.getColumnLabel(i), paramValue); } return dataEntry; }
From source file:ProcessRequest.java
public JSONArray parseQueryResults(ResultSet rs, String table) throws SQLException, JSONException { JSONArray resultJSONArray = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); int columns = rsmd.getColumnCount(); while (rs.next()) { JSONObject result = new JSONObject(); JSONObject resultMeta = new JSONObject(); resultMeta.put("table", table); result.put("metadata", resultMeta); for (int i = 1; i <= columns; i++) { //out.println("<td>"+rs.getString(i)+"</td>"); int type = rsmd.getColumnType(i); //result.put(rsmd.getColumnName(i), rs.get) switch (type) { case Types.BIT: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.TINYINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.SMALLINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.INTEGER: //System.out.println(rsmd.getColumnName(i) + " type: "+type); result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.BIGINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.FLOAT: result.put(rsmd.getColumnName(i), rs.getFloat(i)); break; case Types.REAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DOUBLE: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.NUMERIC: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DECIMAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.CHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.VARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.DATE: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; }//w w w . j ava2 s . c o m case Types.TIME: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.TIMESTAMP: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.BINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.VARBINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.LONGVARBINARY: result.put(rsmd.getColumnName(i), rs.getLong(i)); break; case Types.NULL: result.put(rsmd.getColumnName(i), ""); break; case Types.BOOLEAN: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.ROWID: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.NCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.NVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGNVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.SQLXML: case Types.NCLOB: case Types.DATALINK: case Types.REF: case Types.OTHER: case Types.JAVA_OBJECT: case Types.DISTINCT: case Types.STRUCT: case Types.ARRAY: case Types.BLOB: case Types.CLOB: default: result.put(rsmd.getColumnName(i), rs.getString(i)); break; } } //if(table.equals("Ticket")) //System.out.println(result.toString(5)); resultJSONArray.put(result); } return resultJSONArray; }
From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java
public void exportChildDB(String uuidForChild, OutputStream os) throws DAOException { PrintStream out = new PrintStream(os); Set<String> tablesToSkip = new HashSet<String>(); {/* w w w . j ava2 s . c om*/ tablesToSkip.add("hl7_in_archive"); tablesToSkip.add("hl7_in_queue"); tablesToSkip.add("hl7_in_error"); tablesToSkip.add("formentry_archive"); tablesToSkip.add("formentry_queue"); tablesToSkip.add("formentry_error"); tablesToSkip.add("sync_class"); tablesToSkip.add("sync_import"); tablesToSkip.add("sync_record"); tablesToSkip.add("sync_server"); tablesToSkip.add("sync_server_class"); tablesToSkip.add("sync_server_record"); // TODO: figure out which other tables to skip // tablesToSkip.add("obs"); // tablesToSkip.add("concept"); // tablesToSkip.add("patient"); } List<String> tablesToDump = new ArrayList<String>(); Session session = sessionFactory.getCurrentSession(); String schema = (String) session.createSQLQuery("SELECT schema()").uniqueResult(); log.warn("schema: " + schema); // Get all tables that we'll need to dump { Query query = session.createSQLQuery( "SELECT tabs.table_name FROM INFORMATION_SCHEMA.TABLES tabs WHERE tabs.table_schema = '" + schema + "'"); for (Object tn : query.list()) { String tableName = (String) tn; if (!tablesToSkip.contains(tableName.toLowerCase())) tablesToDump.add(tableName); } } log.warn("tables to dump: " + tablesToDump); String thisServerGuid = getGlobalProperty(SyncConstants.PROPERTY_SERVER_UUID); // Write the DDL Header as mysqldump does { out.println("-- ------------------------------------------------------"); out.println("-- Database dump to create an openmrs child server"); out.println("-- Schema: " + schema); out.println("-- Parent GUID: " + thisServerGuid); out.println("-- Parent version: " + OpenmrsConstants.OPENMRS_VERSION); out.println("-- ------------------------------------------------------"); out.println(""); out.println("/*!40101 SET CHARACTER_SET_CLIENT=utf8 */;"); out.println("/*!40101 SET NAMES utf8 */;"); out.println("/*!40103 SET TIME_ZONE='+00:00' */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;"); out.println("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;"); out.println("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;"); out.println("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;"); out.println("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;"); out.println("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;"); out.println(""); } try { // JDBC way of doing this // Connection conn = // DriverManager.getConnection("jdbc:mysql://localhost/" + schema, // "test", "test"); Connection conn = sessionFactory.getCurrentSession().connection(); try { Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); // Get the create database statement ResultSet rs = st.executeQuery("SHOW CREATE DATABASE " + schema); for (String tableName : tablesToDump) { out.println(); out.println("--"); out.println("-- Table structure for table `" + tableName + "`"); out.println("--"); out.println("DROP TABLE IF EXISTS `" + tableName + "`;"); out.println("SET @saved_cs_client = @@character_set_client;"); out.println("SET character_set_client = utf8;"); rs = st.executeQuery("SHOW CREATE TABLE " + tableName); while (rs.next()) { out.println(rs.getString("Create Table") + ";"); } out.println("SET character_set_client = @saved_cs_client;"); out.println(); { out.println("-- Dumping data for table `" + tableName + "`"); out.println("LOCK TABLES `" + tableName + "` WRITE;"); out.println("/*!40000 ALTER TABLE `" + tableName + "` DISABLE KEYS */;"); boolean first = true; rs = st.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); int rowNum = 0; boolean insert = false; while (rs.next()) { if (rowNum == 0) { insert = true; out.print("INSERT INTO `" + tableName + "` VALUES "); } ++rowNum; if (first) { first = false; } else { out.print(", "); } if (rowNum % 20 == 0) { out.println(); } out.print("("); for (int i = 1; i <= numColumns; ++i) { if (i != 1) { out.print(","); } if (rs.getObject(i) == null) { out.print("NULL"); } else { switch (md.getColumnType(i)) { case Types.VARCHAR: case Types.CHAR: case Types.LONGVARCHAR: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.BIGINT: case Types.DECIMAL: case Types.NUMERIC: out.print(rs.getBigDecimal(i)); break; case Types.BIT: out.print(rs.getBoolean(i)); break; case Types.INTEGER: case Types.SMALLINT: case Types.TINYINT: out.print(rs.getInt(i)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: out.print(rs.getDouble(i)); break; case Types.BLOB: case Types.VARBINARY: case Types.LONGVARBINARY: Blob blob = rs.getBlob(i); out.print("'"); InputStream in = blob.getBinaryStream(); while (true) { int b = in.read(); if (b < 0) { break; } char c = (char) b; if (c == '\'') { out.print("\'"); } else { out.print(c); } } out.print("'"); break; case Types.CLOB: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.DATE: out.print("'" + rs.getDate(i) + "'"); break; case Types.TIMESTAMP: out.print("'" + rs.getTimestamp(i) + "'"); break; default: throw new RuntimeException("TODO: handle type code " + md.getColumnType(i) + " (name " + md.getColumnTypeName(i) + ")"); } } } out.print(")"); } if (insert) { out.println(";"); insert = false; } out.println("/*!40000 ALTER TABLE `" + tableName + "` ENABLE KEYS */;"); out.println("UNLOCK TABLES;"); out.println(); } } } finally { conn.close(); } // Now we mark this as a child out.println("-- Now mark this as a child database"); if (uuidForChild == null) uuidForChild = SyncUtil.generateUuid(); out.println("update global_property set property_value = '" + uuidForChild + "' where property = '" + SyncConstants.PROPERTY_SERVER_UUID + "';"); // Write the footer of the DDL script { out.println("/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;"); out.println("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;"); out.println("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;"); out.println("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;"); out.println("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;"); out.println("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;"); } out.flush(); out.close(); } catch (IOException ex) { log.error("IOException", ex); } catch (SQLException ex) { log.error("SQLException", ex); } }
From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java
private Map<String, Object> getMetaDataMap(String channelId, long messageId, int metaDataId) { PreparedStatement statement = null; ResultSet resultSet = null;//from w w w . ja va 2 s. co m try { Map<String, Object> values = new HashMap<String, Object>(); values.put("localChannelId", getLocalChannelId(channelId)); // do not cache this statement since metadata columns may be added/removed statement = connection.prepareStatement(querySource.getQuery("getMetaDataMap", values)); statement.setLong(1, messageId); statement.setInt(2, metaDataId); Map<String, Object> metaDataMap = new HashMap<String, Object>(); resultSet = statement.executeQuery(); if (resultSet.next()) { ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); int columnCount = resultSetMetaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { MetaDataColumnType metaDataColumnType = MetaDataColumnType .fromSqlType(resultSetMetaData.getColumnType(i)); Object value = null; switch (metaDataColumnType) {//@formatter:off case STRING: value = resultSet.getString(i); break; case NUMBER: value = resultSet.getBigDecimal(i); break; case BOOLEAN: value = resultSet.getBoolean(i); break; case TIMESTAMP: Timestamp timestamp = resultSet.getTimestamp(i); if (timestamp != null) { value = Calendar.getInstance(); ((Calendar) value).setTimeInMillis(timestamp.getTime()); } break; default: throw new Exception("Unrecognized MetaDataColumnType"); } //@formatter:on metaDataMap.put(resultSetMetaData.getColumnName(i).toUpperCase(), value); } } return metaDataMap; } catch (Exception e) { throw new DonkeyDaoException(e); } finally { close(resultSet); close(statement); } }