List of usage examples for org.apache.commons.dbutils DbUtils close
public static void close(Statement stmt) throws SQLException
Statement, avoid closing if null. From source file:com.intelligentz.appointmentz.controllers.addBerry.java
@Override public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { try {/* w ww . j av a 2 s. c o m*/ String room_id = req.getParameter("room_id"); String hospital_id = req.getParameter("hospital_id"); String auth = req.getParameter("auth"); String serial = req.getParameter("serial"); connection = DBConnection.getDBConnection().getConnection(); String SQL12 = "select serial,auth from rpi where serial=? or auth=?"; preparedStmt = connection.prepareStatement(SQL12); preparedStmt.setString(1, serial); preparedStmt.setString(2, auth); // execute the preparedstatement resultSet = preparedStmt.executeQuery(); if (resultSet.next()) { res.sendRedirect("./addRPI.jsp?status=This Serial Number Or Auth Already Available&serial=" + serial + "&auth=" + auth + "&room_id=" + room_id); return; } JsonObject jsonRequest = new JsonObject(); jsonRequest.addProperty("serial", serial); jsonRequest.addProperty("auth_code", auth); jsonRequest.addProperty("url", URLs.DEVICE_CALL_BACK_URL); jsonRequest.addProperty("event", "PRESSED"); String reqBody = new Gson().toJson(jsonRequest); logger.info("Device Add====== Request: " + reqBody); String response = new IdeaBizAPIHandler().sendAPICall(URLs.DEVICE_REG_URL, RequestMethod.POST, reqBody, "", ContentTypes.TYPE_JSON, ContentTypes.TYPE_TEXT, AuthorizationTypes.TYPE_BEARER); logger.info("Device Add====== Response: " + response); if (response.contains("Invalid")) { JsonObject jsonObject = new JsonParser().parse(response).getAsJsonObject(); String errorMessage = jsonObject.get("desc").getAsString(); res.sendRedirect("./addRPI.jsp?status=" + errorMessage + "&serial=" + serial + "&auth=" + auth + "&room_id=" + room_id); } else { String SQL1 = "insert into rpi ( room_id, auth, serial) VALUES (?,?,?)"; preparedStmt = connection.prepareStatement(SQL1); preparedStmt.setString(1, room_id); preparedStmt.setString(2, auth); preparedStmt.setString(3, serial); // execute the preparedstatement preparedStmt.execute(); res.sendRedirect("./home?status=Device successfully added!"); } } catch (SQLException | PropertyVetoException | JsonIOException ex) { LOGGER.log(Level.SEVERE, null, ex); res.sendRedirect("./error.jsp?error=Error in adding device!\n+" + ex.toString() + ""); } catch (IdeabizException e) { e.printStackTrace(); } finally { try { //DbUtils.closeQuietly(resultSet); DbUtils.closeQuietly(preparedStmt); DbUtils.close(connection); } catch (SQLException ex) { Logger.getLogger(register.class.getName()).log(Level.SEVERE, ex.toString(), ex); } } }
From source file:dbutils.ExampleJDBC.java
/** * ?// w ww.ja v a 2 s.c om */ public static void insertAndUpdateData() throws SQLException { Connection conn = getConnection(); QueryRunner qr = new QueryRunner(); try { //??insert? Object[] insertParams = { "John Doe", "", 12, 3 }; int inserts = qr.update(conn, "INSERT INTO test_student(name,gender,age,team_id) VALUES (?,?,?,?)", insertParams); System.out.println("inserted " + inserts + " data"); Object[] updateParams = { "John Doe Update", "John Doe" }; int updates = qr.update(conn, "UPDATE test_student SET name=? WHERE name=?", updateParams); System.out.println("updated " + updates + " data"); } catch (SQLException e) { e.printStackTrace(); conn.rollback(); } finally { DbUtils.close(conn); } }
From source file:com.fyp.conflictanalysistestmav.controller.GraphController.java
public static ArrayList<Node> getGraphCategoryList(String disease) { ArrayList<Node> graphCategoryList = new ArrayList<>(); try {//from ww w.j a v a2 s . c om connection = DBConnection.getDBConnection().getConnection(); String SQL1 = "SELECT category_id, graph_category_id, category_name " + "FROM disease natural join graph " + "natural join graph_category natural join category " + "where disease_name = ? and " + "graph_id = disease_id and " + "graph_category.category_id = category.category_id;"; preparedStatement = connection.prepareStatement(SQL1); preparedStatement.setString(1, disease); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { Node node = new Node(); node.setId(resultSet.getString("category_id")); node.setName(resultSet.getString("category_name")); node.setGraph_category_id(resultSet.getString("graph_category_id")); node.setType(NodeTypes.NODE_TYPE_CATEGORY); graphCategoryList.add(node); } } catch (SQLException | IOException | PropertyVetoException ex) { LOGGER.log(Level.SEVERE, ex.toString(), ex); } finally { try { DbUtils.closeQuietly(resultSet); DbUtils.closeQuietly(preparedStatement); DbUtils.close(connection); } catch (SQLException ex) { LOGGER.log(Level.SEVERE, ex.toString(), ex); } } return graphCategoryList; }
From source file:de.tu_berlin.dima.oligos.db.JdbcConnector.java
/** * @deprecated As of 0.3.1, replaced by {@link #getTables()} and * {@link #getTables(SchemaRef)}.// w w w . j av a2 s . c om * @param schema * @return * @throws SQLException */ @Deprecated public Collection<String> getTables(final String schema) throws SQLException { ResultSet result = metaData.getTables(null, schema, null, null); List<String> tables = Lists.newArrayList(); while (result.next()) { String table = result.getString("TABLE_NAME"); tables.add(table); } DbUtils.close(result); return tables; }
From source file:com.versatus.jwebshield.securitylock.SecurityLockService.java
private SecurityLock checkSecurityLock(int userId, String ip) throws SQLException { logger.debug("checkAccountLock: userid=" + userId); logger.debug("checkAccountLock: ip=" + ip); SecurityLock res;/*from w w w.j ava 2 s . c o m*/ Object[] params = new Object[] { userId, ip }; QueryRunner run = new QueryRunner(); Connection conn = dbHelper.getConnection(); BeanHandler<SecurityLock> rsh = new BeanHandler(SecurityLock.class) { @Override public SecurityLock handle(ResultSet rs) throws SQLException { SecurityLock brp = null; if (rs.first()) { brp = new BasicRowProcessor().toBean(rs, SecurityLock.class); } return brp; } }; try { res = run.query(conn, lockCheckSql, rsh, params); logger.debug("checkAccountLock: response=" + res); if (res != null) { if (res.isLock()) { logger.debug("checkAccountLock: Calendar.getInstance()=" + Calendar.getInstance().getTime()); logger.debug("checkAccountLock: TimeWhenUnlock()=" + res.getTimeWhenUnlock()); logger.debug("checkAccountLock: is time to ulock=" + Calendar.getInstance().getTime().after(res.getTimeWhenUnlock())); if (Calendar.getInstance().getTime().after(res.getTimeWhenUnlock())) { logger.info("unlocking IP " + res.getIp()); int r = run.update(conn, resetLockSql, new Object[] { ip }); logger.debug("checkAccountLock: reset response=" + r); res = run.query(conn, lockCheckSql, rsh, params); logger.debug("checkAccountLock: after reset response=" + res); } } } else { res = new SecurityLock(); res.setLock(false); } } finally { try { DbUtils.close(conn); } catch (SQLException e) { // ignore } } return res; }
From source file:com.intelligentz.appointmentz.controllers.Data.java
public static String equipmentsGetRPI(String hospital_id) { String rpi = ""; try {/*ww w .j av a2s. co m*/ connection = DBConnection.getDBConnection().getConnection(); String SQL = "select * from rpi natural join room where hospital_id = ?"; preparedStatement = connection.prepareStatement(SQL); preparedStatement.setString(1, hospital_id); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { String auth = resultSet.getString("auth"); String serial = resultSet.getString("serial"); String room_id = resultSet.getString("room_id"); String room_number = resultSet.getString("room_number"); rpi += "<tr><form action='./deleteRPI' method='post'><td>" + auth + "</td><input type='hidden' name='auth' value='" + auth + "'>"; rpi += "<td>" + serial + "</td><input type='hidden' name='serial' value='" + serial + "'><td>" + room_number + "</td><td>" + room_id + "</td>"; rpi += "<input type='hidden' name='room_number' value='" + room_number + "'>"; rpi += "<input type='hidden' name='room_id' value='" + room_id + "'>"; rpi += "<td><button type=\"submit\" onClick=\"return confirm('Do you wish to delete the Device. Ref: Serial = " + serial + ", rel: Room: " + room_number + " ');\" style='color:red'>delete</button></td>"; rpi += "</form>"; rpi += "<form action='./editRPI' method='post'><input type='hidden' name='room_number' value='" + room_number + "'><input type='hidden' name='room_id' value='" + room_id + "'><input type='hidden' name='serial' value='" + serial + "'><input type='hidden' name='auth' value='" + auth + "'>"; rpi += "<td><button type=\"submit\" style='color:blue'>edit</button></td>"; rpi += "</form></tr>"; } } catch (SQLException | IOException | PropertyVetoException e) { //throw new IllegalStateException rpi = "Error"; } finally { try { DbUtils.closeQuietly(resultSet); DbUtils.closeQuietly(preparedStatement); DbUtils.close(connection); } catch (SQLException ex) { Logger.getLogger(register.class.getName()).log(Level.SEVERE, ex.toString(), ex); } } return rpi; }
From source file:net.gcolin.simplerepo.search.SearchController.java
public SearchController(ConfigurationManager configManager) throws IOException { this.configManager = configManager; File plugins = new File(configManager.getRoot(), "plugins"); plugins.mkdirs();//from w w w . jav a 2 s . c o m System.setProperty("derby.system.home", plugins.getAbsolutePath()); BasicDataSource s = new BasicDataSource(); s.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver"); s.setUrl("jdbc:derby:search" + (new File(plugins, "search").exists() ? "" : ";create=true")); s.setUsername("su"); s.setPassword(""); s.setMaxTotal(10); s.setMinIdle(0); s.setDefaultAutoCommit(true); datasource = s; Set<String> allTables = new HashSet<>(); Connection connection = null; try { try { connection = datasource.getConnection(); connection.setAutoCommit(false); DatabaseMetaData dbmeta = connection.getMetaData(); try (ResultSet rs = dbmeta.getTables(null, null, null, new String[] { "TABLE" })) { while (rs.next()) { allTables.add(rs.getString("TABLE_NAME").toLowerCase()); } } if (!allTables.contains("artifact")) { QueryRunner run = new QueryRunner(); run.update(connection, "CREATE TABLE artifactindex(artifact bigint NOT NULL, version bigint NOT NULL)"); run.update(connection, "INSERT INTO artifactindex (artifact,version) VALUES (?,?)", 1L, 1L); run.update(connection, "CREATE TABLE artifact(id bigint NOT NULL,groupId character varying(120), artifactId character varying(120),CONSTRAINT artifact_pkey PRIMARY KEY (id))"); run.update(connection, "CREATE TABLE artifactversion(artifact_id bigint NOT NULL,id bigint NOT NULL," + "version character varying(100)," + "reponame character varying(30)," + "CONSTRAINT artifactversion_pkey PRIMARY KEY (id)," + "CONSTRAINT fk_artifactversion_artifact_id FOREIGN KEY (artifact_id) REFERENCES artifact (id) )"); run.update(connection, "CREATE TABLE artifacttype(version_id bigint NOT NULL,packaging character varying(20) NOT NULL,classifier character varying(30)," + "CONSTRAINT artifacttype_pkey PRIMARY KEY (version_id,packaging,classifier)," + "CONSTRAINT fk_artifacttype_version FOREIGN KEY (version_id) REFERENCES artifactversion (id))"); run.update(connection, "CREATE INDEX artifactindex ON artifact(groupId,artifactId)"); run.update(connection, "CREATE INDEX artifactgroupindex ON artifact(groupId)"); run.update(connection, "CREATE INDEX artifactversionindex ON artifactversion(version)"); } connection.commit(); } catch (SQLException ex) { connection.rollback(); throw ex; } finally { DbUtils.close(connection); } } catch (SQLException ex) { throw new IOException(ex); } }
From source file:com.mirth.connect.server.userutil.DatabaseConnection.java
/** * Closes the database connection./* w w w . ja va2 s . c om*/ */ public void close() { try { DbUtils.close(connection); } catch (SQLException e) { logger.warn(e); } }
From source file:com.intelligentz.appointmentz.controllers.Data.java
public static String checkRoomId(String room_number, String hospital_id) { String check = "default"; try {//from ww w . ja va 2s .com connection = DBConnection.getDBConnection().getConnection(); String SQL = "select room_number from room where hospital_id = ? and room_number = ?"; preparedStatement = connection.prepareStatement(SQL); preparedStatement.setString(1, hospital_id); preparedStatement.setString(2, room_number); resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { check = "Available"; } else { check = "Unavailable"; } } catch (SQLException | IOException | PropertyVetoException e) { check = "Error"; } finally { try { DbUtils.closeQuietly(resultSet); DbUtils.closeQuietly(preparedStatement); DbUtils.close(connection); } catch (SQLException ex) { Logger.getLogger(register.class.getName()).log(Level.SEVERE, ex.toString(), ex); } } return check; }
From source file:de.tu_berlin.dima.oligos.db.JdbcConnector.java
/** * @deprecated As of version 0.3.1, replaced by {@link #getColumns(TableRef)} * @param schema/*from www . j a v a 2s. co m*/ * @param table * @return * @throws SQLException */ @Deprecated public Collection<String> getColumns(final String schema, final String table) throws SQLException { ResultSet result = metaData.getColumns(null, schema, table, null); List<String> columns = Lists.newArrayList(); while (result.next()) { String column = result.getString("COLUMN_NAME"); columns.add(column); } DbUtils.close(result); return columns; }