List of usage examples for org.apache.commons.dbutils QueryRunner query
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException
From source file:it.attocchi.db.DbUtilsConnector.java
public List<Map<String, Object>> executeMap(boolean keepConnOpen, String aQuery) throws Exception { List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); // No DataSource so we must handle Connections manually QueryRunner run = new QueryRunner(); try {/*w w w .j a v a2 s .c o m*/ /* * Sembra che il like con i parametri ufficiali non funzioni, forse * dovuto al fatto che son tutti object */ logger.debug(aQuery); result = run.query(getConnection(), aQuery, new MapListHandler()); } finally { if (!keepConnOpen) close(); } return result; }
From source file:com.demo.admin.dao.impl.AdminDriverDaoImpl.java
@Override public Driver getDriverById(Long id) { QueryRunner queryRunner = dbHelper.getRunner(); Driver driver = null;/*w w w . ja v a 2s.c o m*/ BeanProcessor beanProcessor = new GenerousBeanProcessor(); RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor); try { driver = queryRunner.query("select * from demo_driver where id=? limit 1", new BeanHandler<Driver>(Driver.class, rowProcessor), id); } catch (SQLException e) { String methodName = Thread.currentThread().getStackTrace()[1].getMethodName(); logger.error("{}??{},{}", methodName, id); throw new RuntimeException(e); } return driver; }
From source file:com.gs.obevo.dbmetadata.impl.dialects.SybaseAseMetadataDialect.java
@Override public ImmutableCollection<ExtraRerunnableInfo> searchExtraViewInfo(DaSchema schema, String tableName, Connection conn) throws SQLException { String query = String/* w w w .j a v a 2s .c om*/ .format("select obj.name name, com.number number, colid2 colid2, colid colid, text text\n" + "from %1$s..syscomments com\n" + ", %1$s..sysobjects obj\n" + "where com.id = obj.id\n" + "and com.texttype = 0\n" + "and obj.type in ('V')\n" + "order by com.id, number, colid2, colid\n", schema.getName()); QueryRunner qr = new QueryRunner(); // using queryRunner so that we can reuse the connection ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(qr.query(conn, query, new MapListHandler())) .toImmutable(); ImmutableList<ExtraRerunnableInfo> viewInfos = maps .collect(new Function<Map<String, Object>, ExtraRerunnableInfo>() { @Override public ExtraRerunnableInfo valueOf(Map<String, Object> object) { return new ExtraRerunnableInfo((String) object.get("name"), null, (String) object.get("text"), null, ((Integer) object.get("colid2")).intValue(), ((Integer) object.get("colid")).intValue()); } }); return viewInfos.groupBy(ExtraRerunnableInfo.TO_NAME).multiValuesView() .collect(new Function<RichIterable<ExtraRerunnableInfo>, ExtraRerunnableInfo>() { @Override public ExtraRerunnableInfo valueOf(RichIterable<ExtraRerunnableInfo> objectInfos) { MutableList<ExtraRerunnableInfo> sortedInfos = objectInfos.toSortedList(Comparators .fromFunctions(ExtraRerunnableInfo.TO_ORDER2, ExtraRerunnableInfo.TO_ORDER1)); StringBuilder definitionString = sortedInfos.injectInto(new StringBuilder(), new Function2<StringBuilder, ExtraRerunnableInfo, StringBuilder>() { @Override public StringBuilder value(StringBuilder sb, ExtraRerunnableInfo rerunnableInfo) { return sb.append(rerunnableInfo.getDefinition()); } }); return new ExtraRerunnableInfo(sortedInfos.get(0).getName(), null, definitionString.toString()); } }).toList().toImmutable(); }
From source file:com.gs.obevo.dbmetadata.impl.dialects.SybaseAseMetadataDialect.java
@Override public ImmutableCollection<DaRoutine> searchExtraRoutines(final DaSchema schema, String procedureName, Connection conn) throws SQLException { String nameClause = procedureName != null ? "and obj.name = '" + procedureName + "'\n" : ""; String query = String.format( "select obj.name name, obj.type type, com.number number, colid2 colid2, colid colid, text text\n" + "from %1$s..syscomments com\n" + ", %1$s..sysobjects obj\n" + "where com.id = obj.id\n" + "and com.texttype = 0\n" + "and obj.type in ('SF', 'P')\n" + nameClause + "order by com.id, number, colid2, colid\n", schema.getName());/*from w w w . j a v a 2s. c o m*/ QueryRunner qr = new QueryRunner(); // using queryRunner so that we can reuse the connection ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(qr.query(conn, query, new MapListHandler())) .toImmutable(); ImmutableList<ExtraRerunnableInfo> routineInfos = maps .collect(new Function<Map<String, Object>, ExtraRerunnableInfo>() { @Override public ExtraRerunnableInfo valueOf(Map<String, Object> object) { String basename = (String) object.get("name"); int number = ((Integer) object.get("number")).intValue(); String specificName = number > 1 ? basename + ";" + number : basename; return new ExtraRerunnableInfo(basename, specificName, (String) object.get("text"), ((String) object.get("type")).trim(), ((Integer) object.get("colid2")).intValue(), ((Integer) object.get("colid")).intValue()); } }); return routineInfos.groupBy(ExtraRerunnableInfo.TO_SPECIFIC_NAME).multiValuesView() .collect(new Function<RichIterable<ExtraRerunnableInfo>, DaRoutine>() { @Override public DaRoutine valueOf(RichIterable<ExtraRerunnableInfo> objectInfos) { MutableList<ExtraRerunnableInfo> sortedInfos = objectInfos.toSortedList(Comparators .fromFunctions(ExtraRerunnableInfo.TO_ORDER2, ExtraRerunnableInfo.TO_ORDER1)); StringBuilder definitionString = sortedInfos.injectInto(new StringBuilder(), new Function2<StringBuilder, ExtraRerunnableInfo, StringBuilder>() { @Override public StringBuilder value(StringBuilder sb, ExtraRerunnableInfo rerunnableInfo) { return sb.append(rerunnableInfo.getDefinition()); } }); return new DaRoutinePojoImpl(sortedInfos.get(0).getName(), schema, sortedInfos.get(0).getType().equals("P") ? DaRoutineType.procedure : DaRoutineType.function, sortedInfos.get(0).getSpecificName(), definitionString.toString()); } }).toList().toImmutable(); }
From source file:com.demo.admin.dao.impl.AdminDriverDaoImpl.java
@Override public DriverPlace getDriverPlace(Long driverPlaceId) { QueryRunner queryRunner = dbHelper.getRunner(); DriverPlace driverPlace = null;//from w ww .j ava 2 s.co m BeanProcessor beanProcessor = new GenerousBeanProcessor(); RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor); try { driverPlace = queryRunner.query("select * from demo_driver_place where id=? limit 1", new BeanHandler<DriverPlace>(DriverPlace.class, rowProcessor), driverPlaceId); } catch (SQLException e) { String methodName = Thread.currentThread().getStackTrace()[1].getMethodName(); logger.error("{}??{}", methodName, driverPlaceId); throw new RuntimeException(e); } return driverPlace; }
From source file:com.neu.controller.LoginController.java
protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { DataSource ds = (DataSource) this.getApplicationContext().getBean("myDataSource"); String action = request.getParameter("action"); ModelAndView mv = new ModelAndView(); HttpSession session = request.getSession(); if (action.equalsIgnoreCase("login")) { try {/*from w ww . j a v a 2 s.co m*/ String userName = request.getParameter("user"); String password = request.getParameter("password"); QueryRunner run = new QueryRunner(ds); ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class); Object[] params = new Object[2]; params[0] = userName; params[1] = password; UsersBean ub = run.query("select * from userstable where userName =? and userPassword=?", user, params); if (ub != null) { ResultSetHandler<List<MessageBean>> messages = new BeanListHandler<MessageBean>( MessageBean.class); List<MessageBean> msg = run.query("select * from messages where userName =?", messages, userName); session.setAttribute("userName", userName); session.setAttribute("messageList", msg); mv.setViewName("userhome"); } else { mv.addObject("error", "true"); mv.setViewName("index"); } } catch (Exception ex) { System.out.println("Error Message" + ex.getMessage()); } } else if (action.equalsIgnoreCase("logout")) { session.invalidate(); mv.setViewName("index"); } else if (action.equalsIgnoreCase("signup")) { System.out.println("sign up"); // // String userName = request.getParameter("user"); // String password = request.getParameter("password"); // String emailObj = request.getParameter("emailObj"); // // System.out.println("printing details: " + userName + " " +password + " "+emailObj); mv.setViewName("signup"); } else if (action.equalsIgnoreCase("signupsubmit")) { System.out.println("sign up submit"); String userName = request.getParameter("user"); String password = request.getParameter("password"); String email = request.getParameter("email"); System.out.println("printing details: " + userName + " " + password + " " + email); if (userName.equals("") || (password.equals("")) || (email.equals(""))) { System.out.println("empty values"); mv.addObject("error", "true"); } else { ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class); Object[] params = new Object[3]; params[0] = userName; params[1] = password; params[2] = email; QueryRunner run = new QueryRunner(ds); int inserts = run.update("insert into userstable (UserName,UserPassword,UserEmail) values (?,?,?)", params);//Logic to insert into table System.out.println("inserts value " + inserts); if (inserts > 0) { mv.addObject("success", "true"); Email emailObj = new SimpleEmail(); emailObj.setHostName("smtp.googlemail.com");//If a server is capable of sending emailObj, then you don't need the authentication. In this case, an emailObj server needs to be running on that machine. Since we are running this application on the localhost and we don't have a emailObj server, we are simply asking gmail to relay this emailObj. emailObj.setSmtpPort(465); emailObj.setAuthenticator( new DefaultAuthenticator("contactapplication2017@gmail.com", "springmvc")); emailObj.setSSLOnConnect(true); emailObj.setFrom("webtools@hello.com");//This emailObj will appear in the from field of the sending emailObj. It doesn't have to be a real emailObj address.This could be used for phishing/spoofing! emailObj.setSubject("TestMail"); emailObj.setMsg("This is spring MVC Contact Application sending you the email"); emailObj.addTo(email);//Will come from the sign up details emailObj.send(); } } mv.setViewName("signup"); } return mv; }
From source file:com.demo.admin.dao.impl.AdminDriverDaoImpl.java
@Override public DriverAudit getDriverAudit(Long driverId) { QueryRunner queryRunner = dbHelper.getRunner(); DriverAudit driverAudit = null;/*from w w w. j av a 2 s . co m*/ BeanProcessor beanProcessor = new GenerousBeanProcessor(); RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor); try { driverAudit = queryRunner.query( "select HEX(uuid) as uuid,name,cell,sfz_a,sfz_b,jsz_a,xsz_a,driver_id,driver_place_id,license_plate,name_reject,cell_reject,sfz_a_reject,sfz_b_reject,jsz_a_reject,xsz_a_reject,license_plate_reject,created_time,updated_time from demo_driver_audit where driver_id=? limit 1", new BeanHandler<DriverAudit>(DriverAudit.class, rowProcessor), driverId); } catch (SQLException e) { String methodName = Thread.currentThread().getStackTrace()[1].getMethodName(); logger.error("{}??{}", methodName, driverId); throw new RuntimeException(e); } return driverAudit; }
From source file:com.fluke.database.dataservice.IntraDayDao.java
public List<IntradayTicker> getIntraday(String name, String date) { try {//w w w . ja va2 s . c o m QueryRunner run = new QueryRunner(DatabaseProperty.getDataSource()); String select = "select * from intraday where equity = ? and date=? order by time asc"; Object[] params = new Object[] { name, date }; ResultSetHandler rsh = new BeanListHandler(IntradayTicker.class); return (List<IntradayTicker>) run.query(select, rsh, params); } catch (SQLException ex) { throw new RuntimeException(ex); } }
From source file:hermes.store.schema.DefaultJDBCAdapter.java
public Collection<String> getStores(Connection connection) throws SQLException { final QueryRunner runner = new QueryRunner(); final ArrayList<String> stores = new ArrayList<String>(); Hermes.ui.getDefaultMessageSink().add("Getting message stores...."); runner.query(connection, statements.getStoresStatement(), new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { while (rs.next()) { stores.add(rs.getString(1)); }/* w w w. j a v a 2s. com*/ return stores; } }); Hermes.ui.getDefaultMessageSink().add("Getting message stores.... done."); return stores; }
From source file:io.seqware.pipeline.plugins.FileProvenanceQueryTool.java
@Override public ReturnValue do_run() { Path randomTempDirectory = null; Path originalReport = null;//from ww w. j ava 2 s .c o m Path bulkImportFile = null; try { if (options.has(this.inFileSpec)) { originalReport = FileSystems.getDefault().getPath(options.valueOf(inFileSpec)); } else { originalReport = populateOriginalReportFromWS(); } List<String> headers; List<Boolean> numericDataType; // construct column name and datatypes // convert file provenance report into derby bulk load format try (BufferedReader originalReader = Files.newBufferedReader(originalReport, Charset.defaultCharset())) { // construct column name and datatypes String headerLine = originalReader.readLine(); headers = Lists.newArrayList(); numericDataType = Lists.newArrayList(); for (String column : headerLine.split("\t")) { String editedColumnName = StringUtils.lowerCase(column).replaceAll(" ", "_").replaceAll("-", "_"); headers.add(editedColumnName); // note that Parent Sample SWID is a silly column that has colons in it numericDataType.add( !editedColumnName.contains("parent_sample") && (editedColumnName.contains("swid"))); } bulkImportFile = Files.createTempFile("import", "txt"); try (BufferedWriter derbyImportWriter = Files.newBufferedWriter(bulkImportFile, Charset.defaultCharset())) { Log.debug("Bulk import file written to " + bulkImportFile.toString()); while (originalReader.ready()) { String line = originalReader.readLine(); StringBuilder builder = new StringBuilder(); int i = 0; for (String colValue : line.split("\t")) { if (i != 0) { builder.append("\t"); } if (numericDataType.get(i)) { if (!colValue.trim().isEmpty()) { builder.append(colValue); } } else { // assume that this is a string // need to double quotes to preserve them, see // https://db.apache.org/derby/docs/10.4/tools/ctoolsimportdefaultformat.html builder.append("\"").append(colValue.replaceAll("\"", "\"\"")).append("\""); } i++; } derbyImportWriter.write(builder.toString()); derbyImportWriter.newLine(); } } } randomTempDirectory = Files.createTempDirectory("randomFileProvenanceQueryDir"); // try using in-memory for better performance String protocol = "jdbc:h2:"; if (options.has(useH2InMemorySpec)) { protocol = protocol + "mem:"; } Connection connection = spinUpEmbeddedDB(randomTempDirectory, "org.h2.Driver", protocol); // drop table if it exists already (running in IDE?) Statement dropTableStatement = null; try { dropTableStatement = connection.createStatement(); dropTableStatement.executeUpdate("DROP TABLE " + TABLE_NAME); } catch (SQLException e) { Log.debug("Report table didn't exist (normal)"); } finally { DbUtils.closeQuietly(dropTableStatement); } // create table creation query StringBuilder tableCreateBuilder = new StringBuilder(); // tableCreateBuilder tableCreateBuilder.append("CREATE TABLE " + TABLE_NAME + " ("); for (int i = 0; i < headers.size(); i++) { if (i != 0) { tableCreateBuilder.append(","); } if (numericDataType.get(i)) { tableCreateBuilder.append(headers.get(i)).append(" INT "); } else { tableCreateBuilder.append(headers.get(i)).append(" VARCHAR "); } } tableCreateBuilder.append(")"); bulkImportH2(tableCreateBuilder, connection, bulkImportFile); // query the database and dump the results to try (BufferedWriter outputWriter = Files.newBufferedWriter(Paths.get(options.valueOf(outFileSpec)), Charset.defaultCharset(), StandardOpenOption.CREATE, StandardOpenOption.TRUNCATE_EXISTING)) { // query the database and dump the results to QueryRunner runner = new QueryRunner(); List<Map<String, Object>> mapList = runner.query(connection, options.valueOf(querySpec), new MapListHandler()); // output header if (mapList.isEmpty()) { Log.fatal("Query had no results"); System.exit(-1); } StringBuilder builder = new StringBuilder(); for (String columnName : mapList.get(0).keySet()) { if (builder.length() != 0) { builder.append("\t"); } builder.append(StringUtils.lowerCase(columnName)); } outputWriter.append(builder); outputWriter.newLine(); for (Map<String, Object> rowMap : mapList) { StringBuilder rowBuilder = new StringBuilder(); for (Entry<String, Object> e : rowMap.entrySet()) { if (rowBuilder.length() != 0) { rowBuilder.append("\t"); } rowBuilder.append(e.getValue()); } outputWriter.append(rowBuilder); outputWriter.newLine(); } } DbUtils.closeQuietly(connection); Log.stdoutWithTime("Wrote output to " + options.valueOf(outFileSpec)); return new ReturnValue(); } catch (IOException | SQLException | ClassNotFoundException | InstantiationException | IllegalAccessException ex) { throw new RuntimeException(ex); } finally { if (originalReport != null) { FileUtils.deleteQuietly(originalReport.toFile()); } if (bulkImportFile != null) { FileUtils.deleteQuietly(bulkImportFile.toFile()); } if (randomTempDirectory != null && randomTempDirectory.toFile().exists()) { FileUtils.deleteQuietly(randomTempDirectory.toFile()); } } }