List of usage examples for java.sql PreparedStatement executeQuery
ResultSet executeQuery() throws SQLException;
PreparedStatement object and returns the ResultSet object generated by the query. From source file:com.l2jfree.loginserver.tools.L2AccountManager.java
/** * Launches the interactive account manager. * /*from w w w . j a va 2 s .c om*/ * @param args ignored */ public static void main(String[] args) { // LOW rework this crap Util.printSection("Account Management"); _log.info("Please choose:"); //_log.info("list - list registered accounts"); _log.info("reg - register a new account"); _log.info("rem - remove a registered account"); _log.info("prom - promote a registered account"); _log.info("dem - demote a registered account"); _log.info("ban - ban a registered account"); _log.info("unban - unban a registered account"); _log.info("quit - exit this application"); BufferedReader br = new BufferedReader(new InputStreamReader(System.in)); L2AccountManager acm = new L2AccountManager(); String line; try { while ((line = br.readLine()) != null) { line = line.trim(); Connection con = null; switch (acm.getState()) { case USER_NAME: line = line.toLowerCase(); try { con = L2Database.getConnection(); PreparedStatement ps = con .prepareStatement("SELECT superuser FROM account WHERE username LIKE ?"); ps.setString(1, line); ResultSet rs = ps.executeQuery(); if (!rs.next()) { acm.setUser(line); _log.info("Desired password:"); acm.setState(ManagerState.PASSWORD); } else { _log.info("User name already in use."); acm.setState(ManagerState.INITIAL_CHOICE); } rs.close(); ps.close(); } catch (SQLException e) { _log.error("Could not access database!", e); acm.setState(ManagerState.INITIAL_CHOICE); } finally { L2Database.close(con); } break; case PASSWORD: try { MessageDigest sha = MessageDigest.getInstance("SHA"); byte[] pass = sha.digest(line.getBytes("US-ASCII")); acm.setPass(HexUtil.bytesToHexString(pass)); } catch (NoSuchAlgorithmException e) { _log.fatal("SHA1 is not available!", e); Shutdown.exit(TerminationStatus.ENVIRONMENT_MISSING_COMPONENT_OR_SERVICE); } catch (UnsupportedEncodingException e) { _log.fatal("ASCII is not available!", e); Shutdown.exit(TerminationStatus.ENVIRONMENT_MISSING_COMPONENT_OR_SERVICE); } _log.info("Super user: [y/n]"); acm.setState(ManagerState.SUPERUSER); break; case SUPERUSER: try { if (line.length() != 1) throw new IllegalArgumentException("One char required."); else if (line.charAt(0) == 'y') acm.setSuper(true); else if (line.charAt(0) == 'n') acm.setSuper(false); else throw new IllegalArgumentException("Invalid choice."); _log.info("Date of birth: [yyyy-mm-dd]"); acm.setState(ManagerState.DOB); } catch (IllegalArgumentException e) { _log.info("[y/n]?"); } break; case DOB: try { Date d = Date.valueOf(line); if (d.after(new Date(System.currentTimeMillis()))) throw new IllegalArgumentException("Future date specified."); acm.setDob(d); _log.info("Ban reason ID or nothing:"); acm.setState(ManagerState.SUSPENDED); } catch (IllegalArgumentException e) { _log.info("[yyyy-mm-dd] in the past:"); } break; case SUSPENDED: try { if (line.length() > 0) { int id = Integer.parseInt(line); acm.setBan(L2BanReason.getById(id)); } else acm.setBan(null); try { con = L2Database.getConnection(); PreparedStatement ps = con.prepareStatement( "INSERT INTO account (username, password, superuser, birthDate, banReason) VALUES (?, ?, ?, ?, ?)"); ps.setString(1, acm.getUser()); ps.setString(2, acm.getPass()); ps.setBoolean(3, acm.isSuper()); ps.setDate(4, acm.getDob()); L2BanReason lbr = acm.getBan(); if (lbr == null) ps.setNull(5, Types.INTEGER); else ps.setInt(5, lbr.getId()); ps.executeUpdate(); _log.info("Account " + acm.getUser() + " has been registered."); ps.close(); } catch (SQLException e) { _log.error("Could not register an account!", e); } finally { L2Database.close(con); } acm.setState(ManagerState.INITIAL_CHOICE); } catch (NumberFormatException e) { _log.info("Ban reason ID or nothing:"); } break; case REMOVE: acm.setUser(line.toLowerCase()); try { con = L2Database.getConnection(); PreparedStatement ps = con.prepareStatement("DELETE FROM account WHERE username LIKE ?"); ps.setString(1, acm.getUser()); int cnt = ps.executeUpdate(); if (cnt > 0) _log.info("Account " + acm.getUser() + " has been removed."); else _log.info("Account " + acm.getUser() + " does not exist!"); ps.close(); } catch (SQLException e) { _log.error("Could not remove an account!", e); } finally { L2Database.close(con); } acm.setState(ManagerState.INITIAL_CHOICE); break; case PROMOTE: acm.setUser(line.toLowerCase()); try { con = L2Database.getConnection(); PreparedStatement ps = con .prepareStatement("UPDATE account SET superuser = ? WHERE username LIKE ?"); ps.setBoolean(1, true); ps.setString(2, acm.getUser()); int cnt = ps.executeUpdate(); if (cnt > 0) _log.info("Account " + acm.getUser() + " has been promoted."); else _log.info("Account " + acm.getUser() + " does not exist!"); ps.close(); } catch (SQLException e) { _log.error("Could not promote an account!", e); } finally { L2Database.close(con); } acm.setState(ManagerState.INITIAL_CHOICE); break; case DEMOTE: acm.setUser(line.toLowerCase()); try { con = L2Database.getConnection(); PreparedStatement ps = con .prepareStatement("UPDATE account SET superuser = ? WHERE username LIKE ?"); ps.setBoolean(1, false); ps.setString(2, acm.getUser()); int cnt = ps.executeUpdate(); if (cnt > 0) _log.info("Account " + acm.getUser() + " has been demoted."); else _log.info("Account " + acm.getUser() + " does not exist!"); ps.close(); } catch (SQLException e) { _log.error("Could not demote an account!", e); } finally { L2Database.close(con); } acm.setState(ManagerState.INITIAL_CHOICE); break; case UNBAN: acm.setUser(line.toLowerCase()); try { con = L2Database.getConnection(); PreparedStatement ps = con .prepareStatement("UPDATE account SET banReason = ? WHERE username LIKE ?"); ps.setNull(1, Types.INTEGER); ps.setString(2, acm.getUser()); int cnt = ps.executeUpdate(); if (cnt > 0) _log.info("Account " + acm.getUser() + " has been unbanned."); else _log.info("Account " + acm.getUser() + " does not exist!"); ps.close(); } catch (SQLException e) { _log.error("Could not demote an account!", e); } finally { L2Database.close(con); } acm.setState(ManagerState.INITIAL_CHOICE); break; case BAN: line = line.toLowerCase(); try { con = L2Database.getConnection(); PreparedStatement ps = con .prepareStatement("SELECT superuser FROM account WHERE username LIKE ?"); ps.setString(1, line); ResultSet rs = ps.executeQuery(); if (rs.next()) { acm.setUser(line); _log.info("Ban reason ID:"); acm.setState(ManagerState.REASON); } else { _log.info("Account does not exist."); acm.setState(ManagerState.INITIAL_CHOICE); } rs.close(); ps.close(); } catch (SQLException e) { _log.error("Could not access database!", e); acm.setState(ManagerState.INITIAL_CHOICE); } finally { L2Database.close(con); } break; case REASON: try { int ban = Integer.parseInt(line); con = L2Database.getConnection(); PreparedStatement ps = con .prepareStatement("UPDATE account SET banReason = ? WHERE username LIKE ?"); ps.setInt(1, ban); ps.setString(2, acm.getUser()); ps.executeUpdate(); _log.info("Account " + acm.getUser() + " has been banned."); ps.close(); } catch (NumberFormatException e) { _log.info("Ban reason ID:"); } catch (SQLException e) { _log.error("Could not ban an account!", e); } finally { L2Database.close(con); } acm.setState(ManagerState.INITIAL_CHOICE); break; default: line = line.toLowerCase(); if (line.equals("reg")) { _log.info("Desired user name:"); acm.setState(ManagerState.USER_NAME); } else if (line.equals("rem")) { _log.info("User name:"); acm.setState(ManagerState.REMOVE); } else if (line.equals("prom")) { _log.info("User name:"); acm.setState(ManagerState.PROMOTE); } else if (line.equals("dem")) { _log.info("User name:"); acm.setState(ManagerState.DEMOTE); } else if (line.equals("unban")) { _log.info("User name:"); acm.setState(ManagerState.UNBAN); } else if (line.equals("ban")) { _log.info("User name:"); acm.setState(ManagerState.BAN); } else if (line.equals("quit")) Shutdown.exit(TerminationStatus.MANUAL_SHUTDOWN); else _log.info("Incorrect command."); break; } } } catch (IOException e) { _log.fatal("Could not process input!", e); } finally { IOUtils.closeQuietly(br); } }
From source file:de.tudarmstadt.ukp.csniper.resbuild.EvaluationItemFixer.java
public static void main(String[] args) { connect(HOST, DATABASE, USER, PASSWORD); Map<Integer, String> items = new HashMap<Integer, String>(); Map<Integer, String> failed = new HashMap<Integer, String>(); // fetch coveredTexts of dubious items and clean it PreparedStatement select = null; try {/*from ww w. j av a 2 s .co m*/ StringBuilder selectQuery = new StringBuilder(); selectQuery.append("SELECT * FROM EvaluationItem "); selectQuery.append("WHERE LOCATE(coveredText, ' ') > 0 "); selectQuery.append("OR LOCATE('" + LRB + "', coveredText) > 0 "); selectQuery.append("OR LOCATE('" + RRB + "', coveredText) > 0 "); selectQuery.append("OR LEFT(coveredText, 1) = ' ' "); selectQuery.append("OR RIGHT(coveredText, 1) = ' ' "); select = connection.prepareStatement(selectQuery.toString()); log.info("Running query [" + selectQuery.toString() + "]."); ResultSet rs = select.executeQuery(); while (rs.next()) { int id = rs.getInt("id"); String coveredText = rs.getString("coveredText"); try { // special handling of double whitespace: in this case, re-fetch the text if (coveredText.contains(" ")) { coveredText = retrieveCoveredText(rs.getString("collectionId"), rs.getString("documentId"), rs.getInt("beginOffset"), rs.getInt("endOffset")); } // replace bracket placeholders and trim the text coveredText = StringUtils.replace(coveredText, LRB, "("); coveredText = StringUtils.replace(coveredText, RRB, ")"); coveredText = coveredText.trim(); items.put(id, coveredText); } catch (IllegalArgumentException e) { failed.put(id, e.getMessage()); } } } catch (SQLException e) { log.error("Exception while selecting: " + e.getMessage()); } finally { closeQuietly(select); } // write logs BufferedWriter bwf = null; BufferedWriter bws = null; try { bwf = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(LOG_FAILED)), "UTF-8")); for (Entry<Integer, String> e : failed.entrySet()) { bwf.write(e.getKey() + " - " + e.getValue() + "\n"); } bws = new BufferedWriter( new OutputStreamWriter(new FileOutputStream(new File(LOG_SUCCESSFUL)), "UTF-8")); for (Entry<Integer, String> e : items.entrySet()) { bws.write(e.getKey() + " - " + e.getValue() + "\n"); } } catch (IOException e) { log.error("Got an IOException while writing the log files."); } finally { IOUtils.closeQuietly(bwf); IOUtils.closeQuietly(bws); } log.info("Texts for [" + items.size() + "] items need to be cleaned up."); // update the dubious items with the cleaned coveredText PreparedStatement update = null; try { String updateQuery = "UPDATE EvaluationItem SET coveredText = ? WHERE id = ?"; update = connection.prepareStatement(updateQuery); int i = 0; for (Entry<Integer, String> e : items.entrySet()) { int id = e.getKey(); String coveredText = e.getValue(); // update item in database update.setString(1, coveredText); update.setInt(2, id); update.executeUpdate(); log.debug("Updating " + id + " with [" + coveredText + "]"); // show percentage of updated items i++; int part = (int) Math.ceil((double) items.size() / 100); if (i % part == 0) { log.info(i / part + "% finished (" + i + "/" + items.size() + ")."); } } } catch (SQLException e) { log.error("Exception while updating: " + e.getMessage()); } finally { closeQuietly(update); } closeQuietly(connection); }
From source file:Main.java
public static void main(String[] args) throws Exception { ResultSet rs = null;// ww w. j ava 2s . c o m Connection conn = null; PreparedStatement pstmt = null; PreparedStatement pstmt2 = null; conn = getOracleConnection(); String[] columnNames = { "id", "name", "content", "date_created" }; Object[] inputValues = new Object[columnNames.length]; inputValues[0] = new java.math.BigDecimal(100); inputValues[1] = new String("String Value"); inputValues[2] = new String("This is my resume."); inputValues[3] = new Timestamp((new java.util.Date()).getTime()); // prepare blob object from an existing binary column String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)"; pstmt = conn.prepareStatement(insert); pstmt.setObject(1, inputValues[0]); pstmt.setObject(2, inputValues[1]); pstmt.setObject(3, inputValues[2]); pstmt.setObject(4, inputValues[3]); pstmt.executeUpdate(); String query = "select id, name, content, date_created from resume where id=?"; pstmt2 = conn.prepareStatement(query); pstmt2.setObject(1, inputValues[0]); rs = pstmt2.executeQuery(); Object[] outputValues = new Object[columnNames.length]; if (rs.next()) { for (int i = 0; i < columnNames.length; i++) { outputValues[i] = rs.getObject(i + 1); } } System.out.println("id=" + ((java.math.BigDecimal) outputValues[0]).toString()); System.out.println("name=" + ((String) outputValues[1])); System.out.println("content=" + ((Clob) outputValues[2])); System.out.println("date_created=" + ((java.sql.Date) outputValues[3]).toString()); rs.close(); pstmt.close(); pstmt2.close(); conn.close(); }
From source file:com.intelius.iap4.TigerLineHit.java
public static void main(String[] args) { String _tigerDs = "jdbc:h2:/home/sxu/playground/tiger"; ResultSet rs = null;//ww w . ja v a 2s. co m PreparedStatement ps = null; List<TigerLineHit> ret = new ArrayList<TigerLineHit>(); try { // if (_tigerDs instanceof JdbcDataSource) { // JdbcDataSource ds = (JdbcDataSource) _tigerDs; // conn = ds.getPooledConnection().getConnection(); // }else{ // conn = _tigerDs.getConnection(); // } //try address "540 westerly parkway, state college, pa 16801" Class.forName("org.h2.Driver"); Connection conn = DriverManager.getConnection(_tigerDs, "sa", ""); ps = conn.prepareStatement(generateSelectQuery("PA")); int i = 1; String streetNum = "540"; String zip = "16801"; ps.setString(i++, "Westerly"); ps.setString(i++, streetNum); ps.setString(i++, streetNum); ps.setString(i++, streetNum); ps.setString(i++, streetNum); ps.setString(i++, streetNum); ps.setString(i++, streetNum); ps.setString(i++, streetNum); ps.setString(i++, streetNum); ps.setString(i++, zip); ps.setString(i++, zip); rs = ps.executeQuery(); while (rs.next()) { TigerLineHit hit = new TigerLineHit(); hit.streetNum = streetNum; hit.tlid = rs.getLong("tlid"); hit.frAddL = rs.getString("fraddl"); hit.frAddR = rs.getString("fraddr"); hit.toAddL = rs.getString("toaddl"); hit.toAddR = rs.getString("toaddr"); hit.zipL = rs.getString("zipL"); hit.zipR = rs.getString("zipR"); hit.toLat = rs.getFloat("tolat"); hit.toLon = rs.getFloat("tolong"); hit.frLat = rs.getFloat("frlat"); hit.frLon = rs.getFloat("tolong"); hit.lat1 = rs.getFloat("lat1"); hit.lat2 = rs.getFloat("lat2"); hit.lat3 = rs.getFloat("lat3"); hit.lat4 = rs.getFloat("lat4"); hit.lat5 = rs.getFloat("lat5"); hit.lat6 = rs.getFloat("lat6"); hit.lat7 = rs.getFloat("lat7"); hit.lat8 = rs.getFloat("lat8"); hit.lat9 = rs.getFloat("lat9"); hit.lat10 = rs.getFloat("lat10"); hit.lon1 = rs.getFloat("long1"); hit.lon2 = rs.getFloat("long2"); hit.lon3 = rs.getFloat("long3"); hit.lon4 = rs.getFloat("long4"); hit.lon5 = rs.getFloat("long5"); hit.lon6 = rs.getFloat("long6"); hit.lon7 = rs.getFloat("long7"); hit.lon8 = rs.getFloat("long8"); hit.lon9 = rs.getFloat("long9"); hit.lon10 = rs.getFloat("long10"); hit.fedirp = rs.getString("fedirp"); hit.fetype = rs.getString("fetype"); hit.fedirs = rs.getString("fedirs"); ret.add(hit); // System.out.println(ret.toString()); // } } catch (Exception e) { e.printStackTrace(); } finally { //DbUtils.closeQuietly(conn); DbUtils.closeQuietly(rs); DbUtils.closeQuietly(ps); } //return ret; }
From source file:Main.java
public static Object readJavaObject(Connection conn, long id) throws Exception { PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL); pstmt.setLong(1, id);/* w ww . j a va 2s . c o m*/ ResultSet rs = pstmt.executeQuery(); rs.next(); Object object = rs.getObject("object_value"); String className = object.getClass().getName(); rs.close(); pstmt.close(); return object; }
From source file:Main.java
public static Object readJavaObject(Connection conn, long id) throws Exception { PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL); pstmt.setLong(1, id);//from www. jav a 2 s.co m ResultSet rs = pstmt.executeQuery(); rs.next(); Object object = rs.getObject(1); String className = object.getClass().getName(); rs.close(); pstmt.close(); return object; }
From source file:SerializeJavaObjects_MySQL.java
public static Object readJavaObject(Connection conn, long id) throws Exception { PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL); pstmt.setLong(1, id);/* w w w .j a v a 2 s . com*/ ResultSet rs = pstmt.executeQuery(); rs.next(); Object object = rs.getObject(1); String className = object.getClass().getName(); rs.close(); pstmt.close(); System.out.println("readJavaObject: done de-serializing: " + className); return object; }
From source file:com.afforess.nsdump.Test.java
public static void testRegionDump() throws IOException, SQLException { RegionsDump dump = new RegionsDump(); dump.parse();/* ww w. j ava2 s . c om*/ Connection conn = dump.getDatabaseConnection(); PreparedStatement statement = conn.prepareStatement("SELECT (numnations) FROM regions"); ResultSet result = statement.executeQuery(); int total = 0, regions = 0; while (result.next()) { total += result.getInt(1); regions++; } System.out.println("Total nations: " + total); System.out.println("Total regions: " + regions); result.close(); conn.close(); File db = new File("./ns-db.h2.db"); db.delete(); }
From source file:com.afforess.nsdump.Test.java
public static void testNationDump() throws IOException, SQLException { NationsDump dump = new NationsDump(); dump.parse();// w ww.j a va2 s. c o m Connection conn = dump.getDatabaseConnection(); PreparedStatement statement = conn.prepareStatement("SELECT (name) FROM nations"); ResultSet result = statement.executeQuery(); int total = 0; while (result.next()) { total++; } result.close(); System.out.println("Total nations: " + total); statement = conn.prepareStatement("SELECT * FROM nations WHERE name = 'sakhovelo'"); result = statement.executeQuery(); result.next(); for (int i = 1; i <= 10; i++) { if (i == 4) { Clob clob = result.getClob(i); String motto = clob.getSubString(1, (int) clob.length()); String mottoEscaped = StringEscapeUtils.unescapeHtml(motto); System.out.println("Raw: " + motto + " Escaped: " + mottoEscaped); } else { System.out.println(result.getString(i)); } } File db = new File("./ns-db.h2.db"); db.delete(); }
From source file:Main.java
public static byte[] getBLOB(int id, Connection conn) throws Exception { ResultSet rs = null;/*from ww w . j a v a 2 s . co m*/ PreparedStatement pstmt = null; String query = "SELECT photo FROM MyPictures WHERE id = ?"; try { pstmt = conn.prepareStatement(query); pstmt.setInt(1, id); rs = pstmt.executeQuery(); rs.next(); Blob blob = rs.getBlob(3); // materialize BLOB onto client return blob.getBytes(1, (int) blob.length()); } finally { rs.close(); pstmt.close(); conn.close(); } }