List of usage examples for java.sql Connection prepareStatement
PreparedStatement prepareStatement(String sql) throws SQLException;
PreparedStatement object for sending parameterized SQL statements to the database. From source file:InsertCustomType2_Oracle.java
public static void main(String[] args) { String id = "001"; String isbn = "1234567890"; String title = "Java Oracle"; String author = "java2s"; int edition = 1; // create the Book object Book book = new Book(isbn, title, author, edition); book.print();//from w ww.ja v a 2s.co m Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); // create type map java.util.Map map = conn.getTypeMap(); System.out.println("map=" + map); map.put("BOOK", Class.forName("Book")); System.out.println("map=" + map); String insert = "insert into book_table(ID, BOOK) values(?, ?)"; pstmt = conn.prepareStatement(insert); pstmt.setString(1, id); pstmt.setObject(2, book); pstmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); System.exit(1); } finally { try { pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = null; PreparedStatement pstmt = null; Statement stmt = null;//www. j ava 2 s . c om ResultSet rs = null; Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(); createXMLTable(stmt); File f = new File("build.xml"); long fileLength = f.length(); FileInputStream fis = new FileInputStream(f); String SQL = "INSERT INTO XML_Data VALUES (?,?)"; pstmt = conn.prepareStatement(SQL); pstmt.setInt(1, 100); pstmt.setAsciiStream(2, fis, (int) fileLength); pstmt.execute(); fis.close(); SQL = "SELECT Data FROM XML_Data WHERE id=100"; rs = stmt.executeQuery(SQL); if (rs.next()) { InputStream xmlInputStream = rs.getAsciiStream(1); int c; ByteArrayOutputStream bos = new ByteArrayOutputStream(); while ((c = xmlInputStream.read()) != -1) bos.write(c); System.out.println(bos.toString()); } rs.close(); stmt.close(); pstmt.close(); conn.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); Statement st = conn.createStatement(); // st.executeUpdate("drop table survey;"); st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); PreparedStatement pstmt = null; ParameterMetaData paramMetaData = null; String query = "select * from survey where id > ? "; System.out.println("conn=" + conn); pstmt = conn.prepareStatement(query); paramMetaData = pstmt.getParameterMetaData(); if (paramMetaData == null) { System.out.println("db vendor does NOT support ParameterMetaData"); } else {/*from w w w . j a v a2 s . co m*/ System.out.println("db vendor supports ParameterMetaData"); // find out the number of dynamic parameters int paramCount = paramMetaData.getParameterCount(); System.out.println("paramCount=" + paramCount); } pstmt.close(); conn.close(); }
From source file:Main.java
public static void main(String[] argv) throws Exception { String driverName = "com.jnetdirect.jsql.JSQLDriver"; Class.forName(driverName);//from www . ja v a2 s .c o m String serverName = "127.0.0.1"; String portNumber = "1433"; String mydatabase = serverName + ":" + portNumber; String url = "jdbc:JSQLConnect://" + mydatabase; String username = "username"; String password = "password"; Connection connection = DriverManager.getConnection(url, username, password); String sql = "INSERT INTO mysql_all_table(" + "col_boolean," + "col_byte," + "col_short," + "col_int," + "col_long," + "col_float," + "col_double," + "col_bigdecimal," + "col_string," + "col_date," + "col_time," + "col_timestamp," + "col_asciistream," + "col_binarystream," + "col_blob) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = connection.prepareStatement(sql); pstmt.setBoolean(1, true); pstmt.setByte(2, (byte) 123); pstmt.setShort(3, (short) 123); pstmt.setInt(4, 123); pstmt.setLong(5, 123L); pstmt.setFloat(6, 1.23F); pstmt.setDouble(7, 1.23D); pstmt.setBigDecimal(8, new BigDecimal(1.23)); pstmt.setString(9, "a string"); pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis())); pstmt.setTime(11, new Time(System.currentTimeMillis())); pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis())); File file = new File("infilename1"); FileInputStream is = new FileInputStream(file); pstmt.setAsciiStream(13, is, (int) file.length()); file = new File("infilename2"); is = new FileInputStream(file); pstmt.setBinaryStream(14, is, (int) file.length()); file = new File("infilename3"); is = new FileInputStream(file); pstmt.setBinaryStream(15, is, (int) file.length()); pstmt.executeUpdate(); }
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 www . ja v a2 s. 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); } pstmt.close(); conn.close(); }
From source file:com.l2jfree.loginserver.tools.L2GameServerRegistrar.java
/** * Launches the interactive game server registration. * /*from w ww .j av a 2 s . co m*/ * @param args ignored */ public static void main(String[] args) { // LOW rework this crap Util.printSection("Game Server Registration"); _log.info("Please choose:"); _log.info("list - list registered game servers"); _log.info("reg - register a game server"); _log.info("rem - remove a registered game server"); _log.info("hexid - generate a legacy hexid file"); _log.info("quit - exit this application"); BufferedReader br = new BufferedReader(new InputStreamReader(System.in)); L2GameServerRegistrar reg = new L2GameServerRegistrar(); String line; try { RegistrationState next = RegistrationState.INITIAL_CHOICE; while ((line = br.readLine()) != null) { line = line.trim().toLowerCase(); switch (reg.getState()) { case GAMESERVER_ID: try { int id = Integer.parseInt(line); if (id < 1 || id > 127) throw new IllegalArgumentException("ID must be in [1;127]."); reg.setId(id); reg.setState(next); } catch (RuntimeException e) { _log.info("You must input a number between 1 and 127"); } if (reg.getState() == RegistrationState.ALLOW_BANS) { Connection con = null; try { con = L2Database.getConnection(); PreparedStatement ps = con .prepareStatement("SELECT allowBans FROM gameserver WHERE id = ?"); ps.setInt(1, reg.getId()); ResultSet rs = ps.executeQuery(); if (rs.next()) { _log.info("A game server is already registered on ID " + reg.getId()); reg.setState(RegistrationState.INITIAL_CHOICE); } else _log.info("Allow account bans from this game server? [y/n]:"); ps.close(); } catch (SQLException e) { _log.error("Could not remove a game server!", e); } finally { L2Database.close(con); } } else if (reg.getState() == RegistrationState.REMOVE) { Connection con = null; try { con = L2Database.getConnection(); PreparedStatement ps = con.prepareStatement("DELETE FROM gameserver WHERE id = ?"); ps.setInt(1, reg.getId()); int cnt = ps.executeUpdate(); if (cnt == 0) _log.info("No game server registered on ID " + reg.getId()); else _log.info("Game server removed."); ps.close(); } catch (SQLException e) { _log.error("Could not remove a game server!", e); } finally { L2Database.close(con); } reg.setState(RegistrationState.INITIAL_CHOICE); } else if (reg.getState() == RegistrationState.GENERATE) { Connection con = null; try { con = L2Database.getConnection(); PreparedStatement ps = con .prepareStatement("SELECT authData FROM gameserver WHERE id = ?"); ps.setInt(1, reg.getId()); ResultSet rs = ps.executeQuery(); if (rs.next()) { reg.setAuth(rs.getString("authData")); byte[] b = HexUtil.hexStringToBytes(reg.getAuth()); Properties pro = new Properties(); pro.setProperty("ServerID", String.valueOf(reg.getId())); pro.setProperty("HexID", HexUtil.hexToString(b)); BufferedOutputStream os = new BufferedOutputStream( new FileOutputStream("hexid.txt")); pro.store(os, "the hexID to auth into login"); IOUtils.closeQuietly(os); _log.info("hexid.txt has been generated."); } else _log.info("No game server registered on ID " + reg.getId()); rs.close(); ps.close(); } catch (SQLException e) { _log.error("Could not generate hexid.txt!", e); } finally { L2Database.close(con); } reg.setState(RegistrationState.INITIAL_CHOICE); } break; case ALLOW_BANS: try { if (line.length() != 1) throw new IllegalArgumentException("One char required."); else if (line.charAt(0) == 'y') reg.setTrusted(true); else if (line.charAt(0) == 'n') reg.setTrusted(false); else throw new IllegalArgumentException("Invalid choice."); byte[] auth = Rnd.nextBytes(new byte[BYTES]); reg.setAuth(HexUtil.bytesToHexString(auth)); Connection con = null; try { con = L2Database.getConnection(); PreparedStatement ps = con.prepareStatement( "INSERT INTO gameserver (id, authData, allowBans) VALUES (?, ?, ?)"); ps.setInt(1, reg.getId()); ps.setString(2, reg.getAuth()); ps.setBoolean(3, reg.isTrusted()); ps.executeUpdate(); ps.close(); _log.info("Registered game server on ID " + reg.getId()); _log.info("The authorization string is:"); _log.info(reg.getAuth()); _log.info("Use it when registering this login server."); _log.info("If you need a legacy hexid file, use the 'hexid' command."); } catch (SQLException e) { _log.error("Could not register gameserver!", e); } finally { L2Database.close(con); } reg.setState(RegistrationState.INITIAL_CHOICE); } catch (IllegalArgumentException e) { _log.info("[y/n]?"); } break; default: if (line.equals("list")) { Connection con = null; try { con = L2Database.getConnection(); PreparedStatement ps = con.prepareStatement("SELECT id, allowBans FROM gameserver"); ResultSet rs = ps.executeQuery(); while (rs.next()) _log.info("ID: " + rs.getInt("id") + ", trusted: " + rs.getBoolean("allowBans")); rs.close(); ps.close(); } catch (SQLException e) { _log.error("Could not register gameserver!", e); } finally { L2Database.close(con); } reg.setState(RegistrationState.INITIAL_CHOICE); } else if (line.equals("reg")) { _log.info("Enter the desired ID:"); reg.setState(RegistrationState.GAMESERVER_ID); next = RegistrationState.ALLOW_BANS; } else if (line.equals("rem")) { _log.info("Enter game server ID:"); reg.setState(RegistrationState.GAMESERVER_ID); next = RegistrationState.REMOVE; } else if (line.equals("hexid")) { _log.info("Enter game server ID:"); reg.setState(RegistrationState.GAMESERVER_ID); next = RegistrationState.GENERATE; } 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 { Class.forName(DB_DRIVER);/*from w w w. j av a 2 s .com*/ Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD); PreparedStatement preparedStatementInsert = null; PreparedStatement preparedStatementUpdate = null; String insertTableSQL = "INSERT INTO Person" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" + "(?,?,?,?)"; String updateTableSQL = "UPDATE Person SET USERNAME =? " + "WHERE USER_ID = ?"; java.util.Date today = new java.util.Date(); dbConnection.setAutoCommit(false); preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL); preparedStatementInsert.setInt(1, 9); preparedStatementInsert.setString(2, "101"); preparedStatementInsert.setString(3, "system"); preparedStatementInsert.setTimestamp(4, new java.sql.Timestamp(today.getTime())); preparedStatementInsert.executeUpdate(); preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL); preparedStatementUpdate.setString(1, "new string"); preparedStatementUpdate.setInt(2, 999); preparedStatementUpdate.executeUpdate(); dbConnection.commit(); dbConnection.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { String url = "jdbc:mysql://localhost/testdb"; String username = "root"; String password = ""; Class.forName("com.mysql.jdbc.Driver"); Connection conn = null; try {// ww w. j ava 2 s . c om conn = DriverManager.getConnection(url, username, password); conn.setAutoCommit(false); Statement st = conn.createStatement(); st.execute("INSERT INTO orders (username, order_date) VALUES ('java', '2007-12-13')", Statement.RETURN_GENERATED_KEYS); ResultSet keys = st.getGeneratedKeys(); int id = 1; while (keys.next()) { id = keys.getInt(1); } PreparedStatement pst = conn.prepareStatement( "INSERT INTO order_details (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)"); pst.setInt(1, id); pst.setString(2, "1"); pst.setInt(3, 10); pst.setDouble(4, 100); pst.execute(); conn.commit(); System.out.println("Transaction commit..."); } catch (SQLException e) { if (conn != null) { conn.rollback(); System.out.println("Connection rollback..."); } e.printStackTrace(); } finally { if (conn != null && !conn.isClosed()) { 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')"); 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 {// w ww . j a v a 2 s . com 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); String paramTypeName = paramMetaData.getParameterTypeName(param); System.out.println("param SQL type name=" + paramTypeName); } } pstmt.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')"); 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 {// w w w .j a va 2 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); String paramClassName = paramMetaData.getParameterClassName(param); System.out.println("param class name=" + paramClassName); } } pstmt.close(); conn.close(); }