List of usage examples for java.sql PreparedStatement close
void close() throws SQLException;
Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. From source file:Main.java
public static void main(String[] args) throws Exception { Connection 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()); String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)"; PreparedStatement pstmt = conn.prepareStatement(insert); pstmt.setObject(1, inputValues[0]);/*from w w w . j av a 2 s . c om*/ 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=?"; PreparedStatement pstmt2 = conn.prepareStatement(query); pstmt2.setObject(1, inputValues[0]); ResultSet 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:Main.java
public static void main(String[] args) throws Exception { String deptName = "oldName"; String newDeptName = "newName"; ResultSet rs = null;// ww w .ja v a2s. c o m Connection conn = null; PreparedStatement pstmt = null; PreparedStatement pstmt2 = null; try { conn = getConnection(); // prepare query for getting a REF object and PrepareStatement object String refQuery = "select manager from dept_table where dept_name=?"; pstmt = conn.prepareStatement(refQuery); pstmt.setString(1, deptName); rs = pstmt.executeQuery(); java.sql.Ref ref = null; if (rs.next()) { ref = rs.getRef(1); } if (ref == null) { System.out.println("error: could not get a reference for manager."); System.exit(1); } String query = "INSERT INTO dept_table(dept_name, manager)values(?, ?)"; pstmt2 = conn.prepareStatement(query); pstmt2.setString(1, newDeptName); pstmt2.setRef(2, ref); // execute query, and return number of rows created int rowCount = pstmt2.executeUpdate(); System.out.println("rowCount=" + rowCount); } finally { pstmt.close(); pstmt2.close(); conn.close(); } }
From source file:Main.java
public static void main(String[] args) throws Exception { String deptName = "oldName"; String newDeptName = "newName"; ResultSet rs = null;/* w w w .jav a2 s . c o m*/ Connection conn = null; PreparedStatement pstmt = null; PreparedStatement pstmt2 = null; try { conn = getConnection(); // prepare query for getting a REF object and PrepareStatement object String refQuery = "select manager from dept_table where dept_name=?"; pstmt = conn.prepareStatement(refQuery); pstmt.setString(1, deptName); rs = pstmt.executeQuery(); java.sql.Ref ref = null; if (rs.next()) { ref = rs.getRef("manager"); } if (ref == null) { System.out.println("error: could not get a reference for manager."); System.exit(1); } String query = "INSERT INTO dept_table(dept_name, manager)values(?, ?)"; pstmt2 = conn.prepareStatement(query); pstmt2.setString(1, newDeptName); pstmt2.setRef(2, ref); // execute query, and return number of rows created int rowCount = pstmt2.executeUpdate(); System.out.println("rowCount=" + rowCount); } finally { pstmt.close(); pstmt2.close(); conn.close(); } }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); System.out.println("Got Connection."); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar);"); st.executeUpdate("create view surveyView as (select * from survey);"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); st.executeUpdate("insert into survey (id,name ) values (2,'anotherValue')"); ResultSet rs = null;//w w w . j a v a 2 s. co m PreparedStatement ps = null; String query = "select id, name from survey where id = ?"; ps = conn.prepareStatement(query); // specify values for all input parameters ps.setInt(1, 001); // set the first parameter: id // now, PreparedStatement object is ready to be executed. rs = ps.executeQuery(); // iterate the result set object while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); System.out.println("[id=" + id + "][name=" + name + "]"); } // NOTE: you may use PreparedStatement as many times as you want // here we use it for another set of parameters: ps.setInt(1, 002); // set the first parameter: id // now, PreparedStatement object is ready to be executed. rs = ps.executeQuery(); // iterate the result set object while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); System.out.println("[id=" + id + "][name=" + name + "]"); } rs.close(); ps.close(); conn.close(); }
From source file:DemoUpdatableResultSet.java
public static void main(String[] args) { ResultSet rs = null;/*from w ww.j a va 2 s . c o m*/ Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); String query = "select id, name, age from employees where age > ?"; pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setInt(1, 20); // set input values rs = pstmt.executeQuery(); // create an updatable ResultSet // update a column value in the current row. rs.absolute(2); // moves the cursor to the 2nd row of rs rs.updateString("name", "newName"); // updates the 'name' column of row 2 to newName rs.updateRow(); // updates the row in the data source // insert column values into the insert row. rs.moveToInsertRow(); // moves cursor to the insert row rs.updateInt(1, 1234); // 1st column id=1234 rs.updateString(2, "newName"); // updates the 2nd column rs.updateInt(3, 99); // updates the 3rd column to 99 rs.insertRow(); rs.moveToCurrentRow(); } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
From source file:com.l2jfree.loginserver.tools.L2AccountManager.java
/** * Launches the interactive account manager. * /* w ww . ja va2 s. co m*/ * @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:Main.java
public static void main(String[] argv) throws Exception { Connection dbConnection = null; PreparedStatement preparedStatement = null; Class.forName(DB_DRIVER);/*from w ww . ja v a2s. c om*/ dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD); String insertTableSQL = "INSERT INTO Person" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" + "(?,?,?,?)"; preparedStatement = dbConnection.prepareStatement(insertTableSQL); dbConnection.setAutoCommit(false); java.util.Date today = new java.util.Date(); preparedStatement.setInt(1, 101); preparedStatement.setString(2, "101"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, new java.sql.Timestamp(today.getTime())); preparedStatement.addBatch(); preparedStatement.setInt(1, 102); preparedStatement.setString(2, "102"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, new java.sql.Timestamp(today.getTime())); preparedStatement.addBatch(); preparedStatement.setInt(1, 103); preparedStatement.setString(2, "103"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, new java.sql.Timestamp(today.getTime())); preparedStatement.addBatch(); preparedStatement.executeBatch(); dbConnection.commit(); preparedStatement.close(); dbConnection.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); System.out.println("Got Connection."); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar);"); st.executeUpdate("create view surveyView as (select * from survey);"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); PreparedStatement pstmt = null; ParameterMetaData paramMetaData = null; String query = "select * from survey where id > ? and name = ?"; pstmt = conn.prepareStatement(query); paramMetaData = pstmt.getParameterMetaData(); if (paramMetaData == null) { System.out.println("db vendor does NOT support ParameterMetaData"); } else {//www. ja v a2 s . c om System.out.println("db vendor supports ParameterMetaData"); // find out the number of dynamic parameters int paramCount = paramMetaData.getParameterCount(); System.out.println("paramCount=" + paramCount); System.out.println("-------------------"); for (int param = 1; param <= paramCount; param++) { System.out.println("param number=" + param); int nullable = paramMetaData.isNullable(param); if (nullable == ParameterMetaData.parameterNoNulls) { System.out.println("parameter will not allow NULL values."); } else if (nullable == ParameterMetaData.parameterNullable) { System.out.println("parameter will allow NULL values."); } else { System.out.println("nullability of a parameter is unknown."); } } } pstmt.close(); conn.close(); }
From source file:GetParamMetaData.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/*from w w w.ja v a 2s . c o m*/ PreparedStatement pstmt; ParameterMetaData pmd; String sql = "UPDATE COFFEES SET SALES = ? " + "WHERE COF_NAME = ?"; try { Class.forName("myDriver.ClassName"); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { con = DriverManager.getConnection(url, "myLogin", "myPassword"); pstmt = con.prepareStatement(sql); pmd = pstmt.getParameterMetaData(); int totalDigits = pmd.getPrecision(1); int digitsAfterDecimal = pmd.getScale(1); boolean b = pmd.isSigned(1); System.out.println("The first parameter "); System.out.println(" has precision " + totalDigits); System.out.println(" has scale " + digitsAfterDecimal); System.out.println(" may be a signed number " + b); int count = pmd.getParameterCount(); System.out.println("count is " + count); for (int i = 1; i <= count; i++) { int type = pmd.getParameterType(i); String typeName = pmd.getParameterTypeName(i); System.out.println("Parameter " + i + ":"); System.out.println(" type is " + type); System.out.println(" type name is " + typeName); } pstmt.close(); con.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); System.out.println("Got Connection."); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar);"); st.executeUpdate("create view surveyView as (select * from survey);"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); PreparedStatement pstmt = null; ParameterMetaData paramMetaData = null; String query = "select * from survey where id > ? and name = ?"; pstmt = conn.prepareStatement(query); paramMetaData = pstmt.getParameterMetaData(); if (paramMetaData == null) { System.out.println("db vendor does NOT support ParameterMetaData"); } else {/*from ww w.j a v a2s. c o m*/ System.out.println("db vendor supports ParameterMetaData"); // find out the number of dynamic parameters int paramCount = paramMetaData.getParameterCount(); System.out.println("paramCount=" + paramCount); System.out.println("-------------------"); for (int param = 1; param <= paramCount; param++) { System.out.println("param number=" + param); int paramMode = paramMetaData.getParameterMode(param); System.out.println("param mode=" + paramMode); if (paramMode == ParameterMetaData.parameterModeOut) { System.out.println("the parameter's mode is OUT."); } else if (paramMode == ParameterMetaData.parameterModeIn) { System.out.println("the parameter's mode is IN."); } else if (paramMode == ParameterMetaData.parameterModeInOut) { System.out.println("the parameter's mode is INOUT."); } else { System.out.println("the mode of a parameter is unknown."); } } } pstmt.close(); conn.close(); }