List of usage examples for java.sql ResultSetMetaData getColumnLabel
String getColumnLabel(int column) throws SQLException;
From source file:com.rapid.server.Designer.java
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { RapidRequest rapidRequest = new RapidRequest(this, request); try {//w w w.j a va2 s .co m String output = ""; // read bytes from request body into our own byte array (this means we can deal with images) InputStream input = request.getInputStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); for (int length = 0; (length = input.read(_byteBuffer)) > -1;) outputStream.write(_byteBuffer, 0, length); byte[] bodyBytes = outputStream.toByteArray(); // get the rapid application Application rapidApplication = getApplications().get("rapid"); // check we got one if (rapidApplication != null) { // get rapid security SecurityAdapter rapidSecurity = rapidApplication.getSecurityAdapter(); // check we got some if (rapidSecurity != null) { // get user name String userName = rapidRequest.getUserName(); if (userName == null) userName = ""; // check permission if (rapidSecurity.checkUserRole(rapidRequest, Rapid.DESIGN_ROLE)) { Application application = rapidRequest.getApplication(); if (application != null) { if ("savePage".equals(rapidRequest.getActionName())) { String bodyString = new String(bodyBytes, "UTF-8"); getLogger().debug("Designer POST request : " + request.getQueryString() + " body : " + bodyString); JSONObject jsonPage = new JSONObject(bodyString); // instantiate a new blank page Page newPage = new Page(); // set page properties newPage.setId(jsonPage.optString("id")); newPage.setName(jsonPage.optString("name")); newPage.setTitle(jsonPage.optString("title")); newPage.setFormPageType(jsonPage.optInt("formPageType")); newPage.setLabel(jsonPage.optString("label")); newPage.setDescription(jsonPage.optString("description")); newPage.setSimple(jsonPage.optBoolean("simple")); // look in the JSON for an event array JSONArray jsonEvents = jsonPage.optJSONArray("events"); // add the events if we found one if (jsonEvents != null) newPage.setEvents(Control.getEvents(this, jsonEvents)); // look in the JSON for a styles array JSONArray jsonStyles = jsonPage.optJSONArray("styles"); // if there were styles if (jsonStyles != null) newPage.setStyles(Control.getStyles(this, jsonStyles)); // if there are child controls from the page loop them and add to the pages control collection JSONArray jsonControls = jsonPage.optJSONArray("childControls"); if (jsonControls != null) { for (int i = 0; i < jsonControls.length(); i++) { // get the JSON control JSONObject jsonControl = jsonControls.getJSONObject(i); // call our function so it can go iterative newPage.addControl(createControl(jsonControl)); } } // if there are roles specified for this page JSONArray jsonUserRoles = jsonPage.optJSONArray("roles"); if (jsonUserRoles != null) { List<String> userRoles = new ArrayList<String>(); for (int i = 0; i < jsonUserRoles.length(); i++) { // get the JSON role String jsonUserRole = jsonUserRoles.getString(i); // add to collection userRoles.add(jsonUserRole); } // assign to page newPage.setRoles(userRoles); } // look in the JSON for a sessionVariables array JSONArray jsonSessionVariables = jsonPage.optJSONArray("sessionVariables"); // if we found one if (jsonSessionVariables != null) { List<String> sessionVariables = new ArrayList<String>(); for (int i = 0; i < jsonSessionVariables.length(); i++) { sessionVariables.add(jsonSessionVariables.getString(i)); } newPage.setSessionVariables(sessionVariables); } // look in the JSON for a pageVisibilityRules array JSONArray jsonVisibilityConditions = jsonPage.optJSONArray("visibilityConditions"); // if we found one if (jsonVisibilityConditions != null) { List<Condition> visibilityConditions = new ArrayList<Condition>(); for (int i = 0; i < jsonVisibilityConditions.length(); i++) { visibilityConditions .add(new Condition(jsonVisibilityConditions.getJSONObject(i))); } newPage.setVisibilityConditions(visibilityConditions); } // look in the JSON for a pageVisibilityRules array is an and or or (default to and) String jsonConditionsType = jsonPage.optString("conditionsType", "and"); // set what we got newPage.setConditionsType(jsonConditionsType); // retrieve the html body String htmlBody = jsonPage.optString("htmlBody"); // if we got one trim it and retain in page if (htmlBody != null) newPage.setHtmlBody(htmlBody.trim()); // look in the JSON for rolehtml JSONArray jsonRolesHtml = jsonPage.optJSONArray("rolesHtml"); // if we found some if (jsonRolesHtml != null) { // instantiate the roles html collection ArrayList<Page.RoleHtml> rolesHtml = new ArrayList<Page.RoleHtml>(); // loop the entries for (int i = 0; i < jsonRolesHtml.length(); i++) { // get the entry JSONObject jsonRoleHtml = jsonRolesHtml.getJSONObject(i); // retain the html String html = jsonRoleHtml.optString("html"); // trim it if there is one if (html != null) html = html.trim(); // create an array to hold the roles ArrayList<String> roles = new ArrayList<String>(); // get the roles JSONArray jsonRoles = jsonRoleHtml.optJSONArray("roles"); // if we got some if (jsonRoles != null) { // loop them for (int j = 0; j < jsonRoles.length(); j++) { // get the role String role = jsonRoles.getString(j); // add it to the roles collections roles.add(role); } } // create and add a new roleHtml object rolesHtml.add(new Page.RoleHtml(roles, html)); } // add it to the page newPage.setRolesHtml(rolesHtml); } // fetch a copy of the old page (if there is one) Page oldPage = application.getPages().getPage(getServletContext(), newPage.getId()); // if the page's name changed we need to remove it if (oldPage != null) { if (!oldPage.getName().equals(newPage.getName())) { oldPage.delete(this, rapidRequest, application); } } // save the new page to file newPage.save(this, rapidRequest, application, true); // get any pages collection (we're only sent it if it's been changed) JSONArray jsonPages = jsonPage.optJSONArray("pages"); // if we got some if (jsonPages != null) { // make a new map for the page orders Map<String, Integer> pageOrders = new HashMap<String, Integer>(); // loop the page orders for (int i = 0; i < jsonPages.length(); i++) { // add the order to the map pageOrders.put(jsonPages.getJSONObject(i).getString("id"), i); } // replace the application pageOrders map application.setPageOrders(pageOrders); // save the application and the new orders application.save(this, rapidRequest, true); } boolean jsonPageOrderReset = jsonPage.optBoolean("pageOrderReset"); if (jsonPageOrderReset) { // empty the application pageOrders map so everything goes alphabetical application.setPageOrders(null); } // send a positive message output = "{\"message\":\"Saved!\"}"; // set the response type to json response.setContentType("application/json"); } else if ("testSQL".equals(rapidRequest.getActionName())) { // turn the body bytes into a string String bodyString = new String(bodyBytes, "UTF-8"); JSONObject jsonQuery = new JSONObject(bodyString); JSONArray jsonInputs = jsonQuery.optJSONArray("inputs"); JSONArray jsonOutputs = jsonQuery.optJSONArray("outputs"); Parameters parameters = new Parameters(); if (jsonInputs != null) { for (int i = 0; i < jsonInputs.length(); i++) parameters.addNull(); } DatabaseConnection databaseConnection = application.getDatabaseConnections() .get(jsonQuery.optInt("databaseConnectionIndex", 0)); ConnectionAdapter ca = databaseConnection.getConnectionAdapter(getServletContext(), application); DataFactory df = new DataFactory(ca); int outputs = 0; if (jsonOutputs != null) outputs = jsonOutputs.length(); String sql = jsonQuery.getString("SQL"); // some jdbc drivers need the line breaks removing before they'll work properly - here's looking at you MS SQL Server! sql = sql.replace("\n", " "); if (outputs == 0) { df.getPreparedStatement(rapidRequest, sql, parameters); } else { ResultSet rs = df.getPreparedResultSet(rapidRequest, sql, parameters); ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); if (outputs > cols) throw new Exception(outputs + " outputs, but only " + cols + " column" + (cols > 1 ? "s" : "") + " selected"); for (int i = 0; i < outputs; i++) { JSONObject jsonOutput = jsonOutputs.getJSONObject(i); String field = jsonOutput.optString("field", ""); if (!"".equals(field)) { field = field.toLowerCase(); boolean gotOutput = false; for (int j = 0; j < cols; j++) { String sqlField = rsmd.getColumnLabel(j + 1).toLowerCase(); if (field.equals(sqlField)) { gotOutput = true; break; } } if (!gotOutput) { rs.close(); df.close(); throw new Exception("Field \"" + field + "\" from output " + (i + 1) + " is not present in selected columns"); } } } // close the recordset rs.close(); // close the data factory df.close(); } // send a positive message output = "{\"message\":\"OK\"}"; // set the response type to json response.setContentType("application/json"); } else if ("uploadImage".equals(rapidRequest.getActionName()) || "import".equals(rapidRequest.getActionName())) { // get the content type from the request String contentType = request.getContentType(); // get the position of the boundary from the content type int boundaryPosition = contentType.indexOf("boundary="); // derive the start of the meaning data by finding the boundary String boundary = contentType.substring(boundaryPosition + 10); // this is the double line break after which the data occurs byte[] pattern = { 0x0D, 0x0A, 0x0D, 0x0A }; // find the position of the double line break int dataPosition = Bytes.findPattern(bodyBytes, pattern); // the body header is everything up to the data String header = new String(bodyBytes, 0, dataPosition, "UTF-8"); // find the position of the filename in the data header int filenamePosition = header.indexOf("filename=\""); // extract the file name String filename = header.substring(filenamePosition + 10, header.indexOf("\"", filenamePosition + 10)); // find the position of the file type in the data header int fileTypePosition = header.toLowerCase().indexOf("type:"); // extract the file type String fileType = header.substring(fileTypePosition + 6); if ("uploadImage".equals(rapidRequest.getActionName())) { // check the file type if (!fileType.equals("image/jpeg") && !fileType.equals("image/gif") && !fileType.equals("image/png")) throw new Exception("Unsupported file type"); // get the web folder from the application String path = rapidRequest.getApplication().getWebFolder(getServletContext()); // create a file output stream to save the data to FileOutputStream fos = new FileOutputStream(path + "/" + filename); // write the file data to the stream fos.write(bodyBytes, dataPosition + pattern.length, bodyBytes.length - dataPosition - pattern.length - boundary.length() - 9); // close the stream fos.close(); // log the file creation getLogger().debug("Saved image file " + path + filename); // create the response with the file name and upload type output = "{\"file\":\"" + filename + "\",\"type\":\"" + rapidRequest.getActionName() + "\"}"; } else if ("import".equals(rapidRequest.getActionName())) { // check the file type if (!"application/x-zip-compressed".equals(fileType) && !"application/zip".equals(fileType)) throw new Exception("Unsupported file type"); // get the name String appName = request.getParameter("name"); // check we were given one if (appName == null) throw new Exception("Name must be provided"); // get the version String appVersion = request.getParameter("version"); // check we were given one if (appVersion == null) throw new Exception("Version must be provided"); // make the id from the safe and lower case name String appId = Files.safeName(appName).toLowerCase(); // make the version from the safe and lower case name appVersion = Files.safeName(appVersion); // get application destination folder File appFolderDest = new File( Application.getConfigFolder(getServletContext(), appId, appVersion)); // get web contents destination folder File webFolderDest = new File( Application.getWebFolder(getServletContext(), appId, appVersion)); // look for an existing application of this name and version Application existingApplication = getApplications().get(appId, appVersion); // if we have an existing application if (existingApplication != null) { // back it up first existingApplication.backup(this, rapidRequest, false); } // get a file for the temp directory File tempDir = new File(getServletContext().getRealPath("/WEB-INF/temp")); // create it if not there if (!tempDir.exists()) tempDir.mkdir(); // the path we're saving to is the temp folder String path = getServletContext() .getRealPath("/WEB-INF/temp/" + appId + ".zip"); // create a file output stream to save the data to FileOutputStream fos = new FileOutputStream(path); // write the file data to the stream fos.write(bodyBytes, dataPosition + pattern.length, bodyBytes.length - dataPosition - pattern.length - boundary.length() - 9); // close the stream fos.close(); // log the file creation getLogger().debug("Saved import file " + path); // get a file object for the zip file File zipFile = new File(path); // load it into a zip file object ZipFile zip = new ZipFile(zipFile); // unzip the file zip.unZip(); // delete the zip file zipFile.delete(); // unzip folder (for deletion) File unZipFolder = new File( getServletContext().getRealPath("/WEB-INF/temp/" + appId)); // get application folders File appFolderSource = new File( getServletContext().getRealPath("/WEB-INF/temp/" + appId + "/WEB-INF")); // get web content folders File webFolderSource = new File(getServletContext() .getRealPath("/WEB-INF/temp/" + appId + "/WebContent")); // check we have the right source folders if (webFolderSource.exists() && appFolderSource.exists()) { // get application.xml file File appFileSource = new File(appFolderSource + "/application.xml"); if (appFileSource.exists()) { // delete the appFolder if it exists if (appFolderDest.exists()) Files.deleteRecurring(appFolderDest); // delete the webFolder if it exists if (webFolderDest.exists()) Files.deleteRecurring(webFolderDest); // copy application content Files.copyFolder(appFolderSource, appFolderDest); // copy web content Files.copyFolder(webFolderSource, webFolderDest); try { // load the new application (but don't initialise, nor load pages) Application appNew = Application.load(getServletContext(), new File(appFolderDest + "/application.xml"), false); // update application name appNew.setName(appName); // get the old id String appOldId = appNew.getId(); // make the new id appId = Files.safeName(appName).toLowerCase(); // update the id appNew.setId(appId); // get the old version String appOldVersion = appNew.getVersion(); // make the new version appVersion = Files.safeName(appVersion); // update the version appNew.setVersion(appVersion); // update the created date appNew.setCreatedDate(new Date()); // set the status to In development appNew.setStatus(Application.STATUS_DEVELOPMENT); // a map of actions that might be removed from any of the pages Map<String, Integer> removedActions = new HashMap<String, Integer>(); // look for page files File pagesFolder = new File( appFolderDest.getAbsolutePath() + "/pages"); // if the folder is there if (pagesFolder.exists()) { // create a filter for finding .page.xml files FilenameFilter xmlFilenameFilter = new FilenameFilter() { public boolean accept(File dir, String name) { return name.toLowerCase().endsWith(".page.xml"); } }; // loop the .page.xml files for (File pageFile : pagesFolder.listFiles(xmlFilenameFilter)) { BufferedReader reader = new BufferedReader( new InputStreamReader(new FileInputStream(pageFile), "UTF-8")); String line = null; StringBuilder stringBuilder = new StringBuilder(); while ((line = reader.readLine()) != null) { stringBuilder.append(line); stringBuilder.append("\n"); } reader.close(); // retrieve the xml into a string String fileString = stringBuilder.toString(); // prepare a new file string which will update into String newFileString = null; // if the old app did not have a version (for backwards compatibility) if (appOldVersion == null) { // replace all properties that appear to have a url, and all created links - note the fix for cleaning up the double encoding newFileString = fileString .replace("applications/" + appOldId + "/", "applications/" + appId + "/" + appVersion + "/") .replace("~?a=" + appOldId + "&", "~?a=" + appId + "&v=" + appVersion + "&") .replace("~?a=" + appOldId + "&amp;", "~?a=" + appId + "&v=" + appVersion + "&"); } else { // replace all properties that appear to have a url, and all created links - note the fix for double encoding newFileString = fileString .replace( "applications/" + appOldId + "/" + appOldVersion + "/", "applications/" + appId + "/" + appVersion + "/") .replace( "~?a=" + appOldId + "&v=" + appOldVersion + "&", "~?a=" + appId + "&v=" + appVersion + "&") .replace( "~?a=" + appOldId + "&amp;v=" + appOldVersion + "&amp;", "~?a=" + appId + "&v=" + appVersion + "&"); } // now open the string into a document Document pageDocument = XML.openDocument(newFileString); // get an xpath factory XPathFactory xPathfactory = XPathFactory.newInstance(); XPath xpath = xPathfactory.newXPath(); // an expression for any attributes with a local name of "type" XPathExpression expr = xpath .compile("//@*[local-name()='type']"); // get them NodeList nl = (NodeList) expr.evaluate(pageDocument, XPathConstants.NODESET); // get out system actions JSONArray jsonActions = getJsonActions(); // if we found any elements with a type attribute and we have system actions if (nl.getLength() > 0 && jsonActions.length() > 0) { // a list of action types List<String> types = new ArrayList<String>(); // loop the json actions for (int i = 0; i < jsonActions.length(); i++) types.add(jsonActions.getJSONObject(i) .optString("type").toLowerCase()); // loop the action attributes we found for (int i = 0; i < nl.getLength(); i++) { // get this attribute Attr a = (Attr) nl.item(i); // get the value of the type String type = a.getTextContent().toLowerCase(); // get the element the attribute is in Node n = a.getOwnerElement(); // if we don't know about this action type if (!types.contains(type)) { // get the parent node Node p = n.getParentNode(); // remove this node p.removeChild(n); // if we have removed this type already if (removedActions.containsKey(type)) { // increment the entry for this type removedActions.put(type, removedActions.get(type) + 1); } else { // add an entry for this type removedActions.put(type, 1); } } // got type check } // attribute loop } // attribute and system action check // use the transformer to write to disk TransformerFactory transformerFactory = TransformerFactory .newInstance(); Transformer transformer = transformerFactory .newTransformer(); DOMSource source = new DOMSource(pageDocument); StreamResult result = new StreamResult(pageFile); transformer.transform(source, result); } // page xml file loop } // pages folder check // now initialise with the new id but don't make the resource files (this reloads the pages and sets up the security adapter) appNew.initialise(getServletContext(), false); // get the security for this application SecurityAdapter security = appNew.getSecurityAdapter(); // if we have one if (security != null) { // assume we don't have the user boolean gotUser = false; // get the current users record from the adapter User user = security.getUser(rapidRequest); // check the current user is present in the app's security adapter if (user != null) { // now check the current user password is correct too if (security.checkUserPassword(rapidRequest, userName, rapidRequest.getUserPassword())) { // we have the right user with the right password gotUser = true; } else { // remove this user as the password does not match security.deleteUser(rapidRequest); } } // if we don't have the user if (!gotUser) { // get the current user from the Rapid application User rapidUser = rapidSecurity.getUser(rapidRequest); // create a new user based on the Rapid user user = new User(userName, rapidUser.getDescription(), rapidUser.getPassword()); // add the new user security.addUser(rapidRequest, user); } // add Admin and Design roles for the new user if required if (!security.checkUserRole(rapidRequest, com.rapid.server.Rapid.ADMIN_ROLE)) security.addUserRole(rapidRequest, com.rapid.server.Rapid.ADMIN_ROLE); if (!security.checkUserRole(rapidRequest, com.rapid.server.Rapid.DESIGN_ROLE)) security.addUserRole(rapidRequest, com.rapid.server.Rapid.DESIGN_ROLE); } // if any items were removed if (removedActions.keySet().size() > 0) { // a description of what was removed String removed = ""; // loop the entries for (String type : removedActions.keySet()) { int count = removedActions.get(type); removed += "removed " + count + " " + type + " action" + (count == 1 ? "" : "s") + " on import\n"; } // get the current description String description = appNew.getDescription(); // if null set to empty string if (description == null) description = ""; // add a line break if need be if (description.length() > 0) description += "\n"; // add the removed description += removed; // set it back appNew.setDescription(description); } // reload the pages (actually clears down the pages collection and reloads the headers) appNew.getPages().loadpages(getServletContext()); // save application (this will also initialise and rebuild the resources) appNew.save(this, rapidRequest, false); // add application to the collection getApplications().put(appNew); // delete unzip folder Files.deleteRecurring(unZipFolder); // send a positive message output = "{\"id\":\"" + appNew.getId() + "\",\"version\":\"" + appNew.getVersion() + "\"}"; } catch (Exception ex) { // delete the appFolder if it exists if (appFolderDest.exists()) Files.deleteRecurring(appFolderDest); // if the parent is empty delete it too if (appFolderDest.getParentFile().list().length <= 1) Files.deleteRecurring(appFolderDest.getParentFile()); // delete the webFolder if it exists if (webFolderDest.exists()) Files.deleteRecurring(webFolderDest); // if the parent is empty delete it too if (webFolderDest.getParentFile().list().length <= 1) Files.deleteRecurring(webFolderDest.getParentFile()); // rethrow exception throw ex; } } else { // delete unzip folder Files.deleteRecurring(unZipFolder); // throw excpetion throw new Exception("Must be a valid Rapid " + Rapid.VERSION + " file"); } } else { // delete unzip folder Files.deleteRecurring(unZipFolder); // throw excpetion throw new Exception("Must be a valid Rapid file"); } } } getLogger().debug("Designer POST response : " + output); PrintWriter out = response.getWriter(); out.print(output); out.close(); } // got an application } // got rapid design role } // got rapid security } // got rapid application } catch (Exception ex) { getLogger().error("Designer POST error : ", ex); sendException(rapidRequest, response, ex); } }
From source file:org.fastcatsearch.datasource.reader.DBReader.java
@Override public SchemaSetting getAutoGeneratedSchemaSetting() { Map<String, String> properties = singleSourceConfig.getProperties(); String jdbcSourceId = properties.get("jdbcSourceId"); String dataSQL = properties.get("dataSQL"); IRService service = ServiceManager.getInstance().getService(IRService.class); Connection con = null;// www . ja v a 2 s. c o m PreparedStatement pst = null; ResultSet res = null; ResultSetMetaData meta = null; try { JDBCSourceInfo jdbcInfo = service.getJDBCSourceInfo(jdbcSourceId); if (jdbcInfo != null) { con = getConnection(jdbcInfo); } logger.trace("get jdbc connection : {}", con); if (con != null) { logger.trace("executing sql :{}", dataSQL); pst = con.prepareStatement(dataSQL); pst.setFetchSize(1); pst.setMaxRows(1); res = pst.executeQuery(); res.next(); meta = res.getMetaData(); SchemaSetting setting = new SchemaSetting(); PrimaryKeySetting primaryKeySetting = new PrimaryKeySetting(); List<FieldSetting> fieldSettingList = new ArrayList<FieldSetting>(); List<AnalyzerSetting> analyzerSetting = new ArrayList<AnalyzerSetting>(); List<GroupIndexSetting> groupIndexSetting = new ArrayList<GroupIndexSetting>(); List<IndexSetting> indexSetting = new ArrayList<IndexSetting>(); List<FieldIndexSetting> fieldIndexSetting = new ArrayList<FieldIndexSetting>(); logger.trace("columnCount:{}", meta.getColumnCount()); String tableName = null; for (int inx = 0; inx < meta.getColumnCount(); inx++) { if (tableName == null) { tableName = meta.getTableName(inx + 1); } FieldSetting field = new FieldSetting(); Type type = null; int size = 0; switch (meta.getColumnType(inx + 1)) { case Types.INTEGER: case Types.TINYINT: case Types.SMALLINT: case Types.NUMERIC: type = Type.INT; break; case Types.BIGINT: type = Type.LONG; break; case Types.FLOAT: type = Type.FLOAT; break; case Types.DOUBLE: type = Type.DOUBLE; break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: type = Type.DATETIME; break; case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: type = Type.STRING; break; default: type = Type.STRING; break; } field.setId(meta.getColumnLabel(inx + 1)); field.setName(field.getId()); field.setType(type); field.setSize(size); logger.trace("field add {}", field); fieldSettingList.add(field); } setting.setFieldSettingList(fieldSettingList); setting.setPrimaryKeySetting(primaryKeySetting); setting.setFieldIndexSettingList(fieldIndexSetting); setting.setAnalyzerSettingList(analyzerSetting); setting.setGroupIndexSettingList(groupIndexSetting); setting.setIndexSettingList(indexSetting); return setting; } } catch (IRException e) { logger.error("", e); } catch (SQLException e) { logger.error("", e); } finally { if (res != null) try { res.close(); } catch (SQLException ignore) { } if (pst != null) try { pst.close(); } catch (SQLException ignore) { } if (con != null) try { con.close(); } catch (SQLException ignore) { } } return null; }
From source file:com.belle.yitiansystem.merchant.service.impl.MerchantsService.java
/** * sql?/*www . jav a 2s. co m*/ * * @author wang.m * @throws SQLException */ private List<Map<String, Object>> getMapBysql(String sql, Object[] param) throws SQLException { List<Map<String, Object>> maps = null; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); if (null != param && param.length > 0) { for (int i = 0; i < param.length; i++) { pstmt.setObject(i + 1, param[i]); } } rs = pstmt.executeQuery(); Map<String, Object> map = null; maps = new ArrayList<Map<String, Object>>(); while (rs.next()) { map = new HashMap<String, Object>(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0; i < rsmd.getColumnCount();) { ++i; String key = rsmd.getColumnLabel(i).toLowerCase(); if (map.containsKey(key)) { throw new IllegalArgumentException("?key " + key); } map.put(key, rs.getObject(i)); } maps.add(map); } } catch (Exception e) { // TODO: handle exception logger.error("sql?!", e); } finally { close(conn, pstmt, rs); } return maps; }
From source file:com.belle.yitiansystem.merchant.service.impl.MerchantsService.java
/** * ???3?/*from w w w.java 2 s. co m*/ * * @throws SQLException * */ private List<Map<String, Object>> getCommodityCatb2cByStructName(String structName) throws SQLException { List<Map<String, Object>> maps = null; String sql = "select struct_name,id,no,cat_name from tbl_commodity_catb2c WHERE level = 3 and delete_flag=1 "; if (StringUtils.isNotBlank(structName)) { // struct_name LIKE ? AND sql += " and struct_name like '" + structName + "%'"; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); Map<String, Object> map = null; maps = new ArrayList<Map<String, Object>>(); while (rs.next()) { map = new HashMap<String, Object>(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0; i < rsmd.getColumnCount();) { ++i; String key = rsmd.getColumnLabel(i).toLowerCase(); if (map.containsKey(key)) { throw new IllegalArgumentException("?key " + key); } map.put(key, rs.getObject(i)); } maps.add(map); } } catch (Exception e) { // TODO: handle exception logger.error("???3?!", e); } finally { close(conn, pstmt, rs); } } return maps; }
From source file:org.pentaho.di.core.database.Database.java
private ValueMetaInterface getValueFromSQLType(ResultSetMetaData rm, int i, boolean ignoreLength, boolean lazyConversion) throws KettleDatabaseException, SQLException { // TODO If we do lazy conversion, we need to find out about the encoding ////from w ww .j av a2s . co m // Some housekeeping stuff... // if (valueMetaPluginClasses == null) { try { valueMetaPluginClasses = ValueMetaFactory.getValueMetaPluginClasses(); Collections.sort(valueMetaPluginClasses, new Comparator<ValueMetaInterface>() { @Override public int compare(ValueMetaInterface o1, ValueMetaInterface o2) { // Reverse the sort list return (Integer.valueOf(o1.getType()).compareTo(Integer.valueOf(o2.getType()))) * -1; } }); } catch (Exception e) { throw new KettleDatabaseException("Unable to get list of instantiated value meta plugin classes", e); } } // Extract the name from the result set meta data... // String name; if (databaseMeta.isMySQLVariant() && getDatabaseMetaData().getDriverMajorVersion() > 3) { name = new String(rm.getColumnLabel(i)); } else { name = new String(rm.getColumnName(i)); } // Check the name, sometimes it's empty. // if (Const.isEmpty(name) || Const.onlySpaces(name)) { name = "Field" + (i + 1); } // Ask all the value meta types if they want to handle the SQL type. // The first to reply something gets the job... // ValueMetaInterface valueMeta = null; for (ValueMetaInterface valueMetaClass : valueMetaPluginClasses) { ValueMetaInterface v = valueMetaClass.getValueFromSQLType(databaseMeta, name, rm, i, ignoreLength, lazyConversion); if (v != null) { valueMeta = v; break; } } if (valueMeta != null) { return valueMeta; } throw new KettleDatabaseException("Unable to handle database column '" + name + "', on column index " + i + " : not a handled data type"); }
From source file:org.openlogics.gears.jdbc.map.BeanResultHandler.java
/** * * @param resultSet/*from w ww. j av a 2 s.c o m*/ * @param useColumnLabel * @param instantiate * @return * @throws SQLException */ private T mapResultSet(ResultSet resultSet, boolean useColumnLabel, Initializer<T> instantiate) throws SQLException { try { //T obj = requiredType.newInstance(); if (instantiate == null || instantiate.getType() == null) { throw new IllegalArgumentException("Initializer can not be null neither the type to instantiate."); } ResultSetMetaData rsmd = resultSet.getMetaData(); Class requiredType = instantiate.getType(); if (!Map.class.isAssignableFrom(requiredType)) { T obj = instantiate.newInstance(resultSet); //Adecuate RESULTS to BEAN struct List<Field> fields = getInheritedFields(requiredType);//requiredType.getDeclaredFields(); for (Field field : fields) { String metName = getSetterName(field.getName()); Method method = null; String columnName = ""; try { method = requiredType.getMethod(metName, field.getType()); } catch (NoSuchMethodException ex) { //LOGGER.warn("Can't bind a result to method " + metName + " of class " + requiredType.getName()); continue; } catch (SecurityException ex) { //LOGGER.warn("Can't bind a result to method " + metName + " of class " + requiredType.getName()); continue; } Object value = null; try { ColumnRef c = field.getAnnotation(ColumnRef.class); if (c != null) { columnName = c.value().trim(); } columnName = columnName.length() > 0 ? columnName : field.getName(); value = resultSet.getObject(columnName); method.invoke(obj, value); } catch (IllegalArgumentException ex) { if (value == null) { continue; } logger.debug("Type found in database is '" + value.getClass().getName() + "', but target object requires '" + field.getType().getName() + "': " + ex.getLocalizedMessage()); //if this is thrown the try to fix this error using the following: //If is a big decimal, maybe pojo has double or float attributes try { if (value instanceof BigDecimal || value instanceof Number) { if (Double.class.isAssignableFrom(field.getType()) || double.class.isAssignableFrom(field.getType())) { method.invoke(obj, ((BigDecimal) value).doubleValue()); continue; } else if (Float.class.isAssignableFrom(field.getType()) || float.class.isAssignableFrom(field.getType())) { method.invoke(obj, ((BigDecimal) value).floatValue()); continue; } else if (Long.class.isAssignableFrom(field.getType()) || long.class.isAssignableFrom(field.getType())) { method.invoke(obj, ((BigDecimal) value).longValue()); continue; } else { logger.warn("Tried to fix the mismatch problem, but couldn't: " + "Trying to inject an object of class " + value.getClass().getName() + " to an object of class " + field.getType()); } } else if (value instanceof Date) { Date dd = (Date) value; if (java.sql.Date.class.isAssignableFrom(field.getType())) { method.invoke(obj, new java.sql.Date(dd.getTime())); continue; } else if (Timestamp.class.isAssignableFrom(field.getType())) { method.invoke(obj, new Timestamp(dd.getTime())); continue; } else if (Time.class.isAssignableFrom(field.getType())) { method.invoke(obj, new Time(dd.getTime())); continue; } } } catch (IllegalArgumentException x) { printIllegalArgumentException(x, field, value); } catch (InvocationTargetException x) { x.printStackTrace(); } //throw new DataSourceException("Can't execute method " + method.getName() + " due to "+ex.getMessage(), ex); logger.warn( "Can't execute method " + method.getName() + " due to: " + ex.getMessage() + "."); } catch (InvocationTargetException ex) { //throw new DataSourceException("Can't inject an object into method " + method.getName(), ex); logger.warn("Can't inject an object into method " + method.getName() + " due to: " + ex.getMessage()); } catch (SQLException ex) { logger.warn("Target object has a field '" + columnName + "', this was not found in query results, " + "this cause that attribute remains NULL or with default value."); } } return obj; } else { ImmutableMap.Builder<String, Object> obj = new ImmutableMap.Builder<String, Object>(); //Adecuate results to BEAN for (int i = 1; i <= rsmd.getColumnCount(); i++) { String column = useColumnLabel ? rsmd.getColumnLabel(i) : rsmd.getColumnName(i); Object value = resultSet.getObject(i); obj.put(column, value); } return (T) obj.build(); } } catch (IllegalAccessException ex) { throw new SQLException( "Object of class " + instantiate.getType().getName() + " doesn't provide a valid access. It's possible be private or protected access only.", ex); } }
From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java
public Field resultToField(ResultSetMetaData md, ResultSet rs, int columnIndex, int maxClobSize, int maxBlobSize, DataType userSpecifiedType, UnknownTypeAction unknownTypeAction, boolean timestampToString) throws SQLException, IOException, StageException { Field field;//from ww w .java 2s. c om if (userSpecifiedType != DataType.USE_COLUMN_TYPE) { // If user specifies the data type, overwrite the column type returned by database. field = Field.create(Field.Type.valueOf(userSpecifiedType.getLabel()), rs.getObject(columnIndex)); } else { // All types as of JDBC 2.0 are here: // https://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.ARRAY // Good source of recommended mappings is here: // http://www.cs.mun.ca/java-api-1.5/guide/jdbc/getstart/mapping.html switch (md.getColumnType(columnIndex)) { case Types.BIGINT: field = Field.create(Field.Type.LONG, rs.getObject(columnIndex)); break; case Types.BINARY: case Types.LONGVARBINARY: case Types.VARBINARY: field = Field.create(Field.Type.BYTE_ARRAY, rs.getBytes(columnIndex)); break; case Types.BIT: case Types.BOOLEAN: field = Field.create(Field.Type.BOOLEAN, rs.getObject(columnIndex)); break; case Types.CHAR: case Types.LONGNVARCHAR: case Types.LONGVARCHAR: case Types.NCHAR: case Types.NVARCHAR: case Types.VARCHAR: field = Field.create(Field.Type.STRING, rs.getObject(columnIndex)); break; case Types.CLOB: case Types.NCLOB: field = Field.create(Field.Type.STRING, getClobString(rs.getClob(columnIndex), maxClobSize)); break; case Types.BLOB: field = Field.create(Field.Type.BYTE_ARRAY, getBlobBytes(rs.getBlob(columnIndex), maxBlobSize)); break; case Types.DATE: field = Field.create(Field.Type.DATE, rs.getDate(columnIndex)); break; case Types.DECIMAL: case Types.NUMERIC: field = Field.create(Field.Type.DECIMAL, rs.getBigDecimal(columnIndex)); field.setAttribute(HeaderAttributeConstants.ATTR_SCALE, String.valueOf(rs.getMetaData().getScale(columnIndex))); field.setAttribute(HeaderAttributeConstants.ATTR_PRECISION, String.valueOf(rs.getMetaData().getPrecision(columnIndex))); break; case Types.DOUBLE: field = Field.create(Field.Type.DOUBLE, rs.getObject(columnIndex)); break; case Types.FLOAT: case Types.REAL: field = Field.create(Field.Type.FLOAT, rs.getObject(columnIndex)); break; case Types.INTEGER: field = Field.create(Field.Type.INTEGER, rs.getObject(columnIndex)); break; case Types.ROWID: field = Field.create(Field.Type.STRING, rs.getRowId(columnIndex).toString()); break; case Types.SMALLINT: case Types.TINYINT: field = Field.create(Field.Type.SHORT, rs.getObject(columnIndex)); break; case Types.TIME: field = Field.create(Field.Type.TIME, rs.getObject(columnIndex)); break; case Types.TIMESTAMP: final Timestamp timestamp = rs.getTimestamp(columnIndex); if (timestampToString) { field = Field.create(Field.Type.STRING, timestamp == null ? null : timestamp.toString()); } else { field = Field.create(Field.Type.DATETIME, timestamp); if (timestamp != null) { final long actualNanos = timestamp.getNanos() % NANOS_TO_MILLIS_ADJUSTMENT; if (actualNanos > 0) { field.setAttribute(FIELD_ATTRIBUTE_NANOSECONDS, String.valueOf(actualNanos)); } } } break; // Ugly hack until we can support LocalTime, LocalDate, LocalDateTime, etc. case Types.TIME_WITH_TIMEZONE: OffsetTime offsetTime = rs.getObject(columnIndex, OffsetTime.class); field = Field.create(Field.Type.TIME, Date.from(offsetTime.atDate(LocalDate.MIN).toInstant())); break; case Types.TIMESTAMP_WITH_TIMEZONE: OffsetDateTime offsetDateTime = rs.getObject(columnIndex, OffsetDateTime.class); field = Field.create(Field.Type.ZONED_DATETIME, offsetDateTime.toZonedDateTime()); break; //case Types.REF_CURSOR: // JDK8 only case Types.SQLXML: case Types.STRUCT: case Types.ARRAY: case Types.DATALINK: case Types.DISTINCT: case Types.JAVA_OBJECT: case Types.NULL: case Types.OTHER: case Types.REF: default: if (unknownTypeAction == null) { return null; } switch (unknownTypeAction) { case STOP_PIPELINE: throw new StageException(JdbcErrors.JDBC_37, md.getColumnType(columnIndex), md.getColumnLabel(columnIndex)); case CONVERT_TO_STRING: Object value = rs.getObject(columnIndex); if (value != null) { field = Field.create(Field.Type.STRING, rs.getObject(columnIndex).toString()); } else { field = Field.create(Field.Type.STRING, null); } break; default: throw new IllegalStateException("Unknown action: " + unknownTypeAction); } } } return field; }
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;/* w w w . j a v a 2 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:lasige.steeldb.jdbc.BFTRowSet.java
/** * Initializes the given <code>RowSetMetaData</code> object with the values * in the given <code>ResultSetMetaData</code> object. * * @param md the <code>RowSetMetaData</code> object for this * <code>CachedRowSetImpl</code> object, which will be set with * values from rsmd//from w w w . ja va 2 s . co m * @param rsmd the <code>ResultSetMetaData</code> object from which new * values for md will be read * @throws SQLException if an error occurs */ private void initMetaData(RowSetMetaDataImpl md, ResultSetMetaData rsmd) throws SQLException { int numCols = rsmd.getColumnCount(); md.setColumnCount(numCols); for (int col = 1; col <= numCols; col++) { md.setAutoIncrement(col, rsmd.isAutoIncrement(col)); if (rsmd.isAutoIncrement(col)) updateOnInsert = true; md.setCaseSensitive(col, false); md.setCurrency(col, false); md.setNullable(col, rsmd.isNullable(col)); md.setSigned(col, rsmd.isSigned(col)); md.setSearchable(col, false); /* * The PostgreSQL drivers sometimes return negative columnDisplaySize, * which causes an exception to be thrown. Check for it. */ int size = rsmd.getColumnDisplaySize(col); if (size < 0) { size = 0; } md.setColumnDisplaySize(col, 0); if (StringUtils.isNotBlank(rsmd.getColumnLabel(col))) { md.setColumnLabel(col, rsmd.getColumnLabel(col).toLowerCase()); } if (StringUtils.isNotBlank(rsmd.getColumnName(col))) { md.setColumnName(col, rsmd.getColumnName(col).toLowerCase()); } md.setSchemaName(col, null); /* * Drivers return some strange values for precision, for non-numeric data, including reports of * non-integer values; maybe we should check type, & set to 0 for non-numeric types. */ int precision = rsmd.getPrecision(col); if (precision < 0) { precision = 0; } md.setPrecision(col, 0); /* * It seems, from a bug report, that a driver can sometimes return a negative * value for scale. javax.sql.rowset.RowSetMetaDataImpl will throw an exception * if we attempt to set a negative value. As such, we'll check for this case. */ int scale = rsmd.getScale(col); if (scale < 0) { scale = 0; } md.setScale(col, 0); md.setTableName(col, null); md.setCatalogName(col, null); md.setColumnType(col, -1); md.setColumnTypeName(col, null); } if (conn != null) { // JDBC 4.0 mandates as does the Java EE spec that all DataBaseMetaData methods // must be implemented, therefore, the previous fix for 5055528 is being backed out dbmslocatorsUpdateCopy = conn.getMetaData().locatorsUpdateCopy(); } }