List of usage examples for java.sql Connection createStatement
Statement createStatement() throws SQLException;
Statement object for sending SQL statements to the database. From source file:CreateRef.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con; Statement stmt;// ww w . j a v a 2 s . com try { Class.forName("myDriver.ClassName"); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { String createManagers = "CREATE TABLE MANAGERS OF MANAGER " + "(OID REF(MANAGER) VALUES ARE SYSTEM GENERATED)"; String insertManager1 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " + "(000001, 'MONTOYA', 'ALFREDO', '8317225600')"; String insertManager2 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " + "(000002, 'HASKINS', 'MARGARET', '4084355600')"; String insertManager3 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " + "(000003, 'CHEN', 'HELEN', '4153785600')"; con = DriverManager.getConnection(url, "myLogin", "myPassword"); stmt = con.createStatement(); stmt.executeUpdate(createManagers); con.setAutoCommit(false); stmt.addBatch(insertManager1); stmt.addBatch(insertManager2); stmt.addBatch(insertManager3); int[] updateCounts = stmt.executeBatch(); con.commit(); System.out.println("Update count for: "); for (int i = 0; i < updateCounts.length; i++) { System.out.print(" command " + (i + 1) + " = "); System.out.println(updateCounts[i]); } stmt.close(); con.close(); } catch (BatchUpdateException b) { System.err.println("-----BatchUpdateException-----"); System.err.println("Message: " + b.getMessage()); System.err.println("SQLState: " + b.getSQLState()); System.err.println("Vendor: " + b.getErrorCode()); System.err.print("Update counts for successful commands: "); int[] rowsUpdated = b.getUpdateCounts(); for (int i = 0; i < rowsUpdated.length; i++) { System.err.print(rowsUpdated[i] + " "); } System.err.println(""); } catch (SQLException ex) { System.err.println("------SQLException------"); System.err.println("Error message: " + ex.getMessage()); System.err.println("SQLState: " + ex.getSQLState()); System.err.println("Vendor: " + ex.getErrorCode()); } }
From source file:com.quest.orahive.HiveJdbcClient.java
public static void main(String[] args) { long mainStartTime = System.nanoTime(); Configuration conf = new Configuration(); Options options = new Options(); parseGeneralOptions(options, conf, args); //<- log4j will now be configured. showWelcomeMessage();/*from www.j a va 2s .co m*/ if (args.length == 0 || userWantsToSeeHelp(args)) { printCommandLineHelp(options); System.exit(0); } checkConfiguration(conf); OraHiveOptions opts = getOraHiveOptions(conf); OraHiveCounters counters = new OraHiveCounters(); try { Connection hiveConnection = createHiveJdbcConnection(opts.hiveJdbcUrl, opts.hiveUserName, opts.hivePassword); try { Connection oracleConnection = createOracleJdbcConnection(opts.oracleJdbcUrl, opts.oracleUserName, opts.oraclePassword); try { initializeOracleSession(oracleConnection, opts); Statement statement = hiveConnection.createStatement(); LOG.info("Running: " + opts.hql); // Execute Hive Query... long start = System.nanoTime(); ResultSet hiveResultSet = statement.executeQuery(opts.hql); counters.hiveQueryTimeNanoSec = System.nanoTime() - start; // Get column definitions from the Hive resultset... List<OracleTableColumn> oracleColumns = getOracleTableColumnsForHiveResults(hiveResultSet); if (opts.exportMode.equals(Constants.ExportMode.CREATE)) { // Create an Oracle table based on the columns in the Hive resultset... createOracleTableWithRetry(opts, oracleColumns, oracleConnection); //<- Lets the user retry this if it fails. } // Generate the Oracle insert statement... String insertSql = generateOracleInsertStatement(opts, oracleColumns); // Insert the Hive data into Oracle... insertHiveResultsIntoOracleTable(opts, insertSql, oracleColumns, oracleConnection, hiveResultSet, counters); //hiveResultSet.close(); //<- Not required/supported statement.close(); } finally { oracleConnection.close(); } } finally { hiveConnection.close(); } } catch (SQLException ex) { LOG.error(String.format("An error occurred in %s.", Constants.ORAHIVE_PRODUCT_NAME), ex); } LOG.info(String.format("\n\n********************************************************************\n" + "\tTotal time : %s sec.\n" + "\tNumber of records processed : %s\n" + "\tTime spent executing HQL statement: %s sec.\n" + "\tTime spent fetching Hive data : %s sec.\n" + "\tTime spent inserting into Oracle : %s sec.", (System.nanoTime() - mainStartTime) / Math.pow(10, 9), counters.rowsProcessed, counters.hiveQueryTimeNanoSec / Math.pow(10, 9), counters.hiveFetchTimeNanoSec / Math.pow(10, 9), counters.oracleInsertTimeNanoSec / Math.pow(10, 9))); }
From source file:com.ingby.socbox.bischeck.test.JDBCtest.java
static public void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException { CommandLineParser parser = new GnuParser(); CommandLine line = null;//from w w w .j a v a2 s .c om // create the Options Options options = new Options(); options.addOption("u", "usage", false, "show usage."); options.addOption("c", "connection", true, "the connection url"); options.addOption("s", "sql", true, "the sql statement to run"); options.addOption("m", "meta", true, "get the table meta data"); options.addOption("C", "columns", true, "the number of columns to display, default 1"); options.addOption("d", "driver", true, "the driver class"); options.addOption("v", "verbose", false, "verbose outbout"); try { // parse the command line arguments line = parser.parse(options, args); } catch (org.apache.commons.cli.ParseException e) { System.out.println("Command parse error:" + e.getMessage()); HelpFormatter formatter = new HelpFormatter(); formatter.printHelp("JDBCtest", options); Util.ShellExit(1); } if (line.hasOption("verbose")) { verbose = true; } if (line.hasOption("usage")) { HelpFormatter formatter = new HelpFormatter(); formatter.printHelp("Bischeck", options); Util.ShellExit(0); } String driverclassname = null; if (!line.hasOption("driver")) { System.out.println("Driver class must be set"); Util.ShellExit(1); } else { driverclassname = line.getOptionValue("driver"); outputln("DriverClass: " + driverclassname); } String connectionname = null; if (!line.hasOption("connection")) { System.out.println("Connection url must be set"); Util.ShellExit(1); } else { connectionname = line.getOptionValue("connection"); outputln("Connection: " + connectionname); } String sql = null; String tablename = null; if (line.hasOption("sql")) { sql = line.getOptionValue("sql"); outputln("SQL: " + sql); } if (line.hasOption("meta")) { tablename = line.getOptionValue("meta"); outputln("Table: " + tablename); } int nrColumns = 1; if (line.hasOption("columns")) { nrColumns = new Integer(line.getOptionValue("columns")); } long execStart = 0l; long execEnd = 0l; long openStart = 0l; long openEnd = 0l; long metaStart = 0l; long metaEnd = 0l; Class.forName(driverclassname).newInstance(); openStart = System.currentTimeMillis(); Connection conn = DriverManager.getConnection(connectionname); openEnd = System.currentTimeMillis(); if (tablename != null) { ResultSet rsCol = null; metaStart = System.currentTimeMillis(); DatabaseMetaData md = conn.getMetaData(); metaEnd = System.currentTimeMillis(); rsCol = md.getColumns(null, null, tablename, null); if (verbose) { tabular("COLUMN_NAME"); tabular("TYPE_NAME"); tabular("COLUMN_SIZE"); tabularlast("DATA_TYPE"); outputln(""); } while (rsCol.next()) { tabular(rsCol.getString("COLUMN_NAME")); tabular(rsCol.getString("TYPE_NAME")); tabular(rsCol.getString("COLUMN_SIZE")); tabularlast(rsCol.getString("DATA_TYPE")); outputln("", true); } } if (sql != null) { Statement stat = conn.createStatement(); stat.setQueryTimeout(10); execStart = System.currentTimeMillis(); ResultSet res = stat.executeQuery(sql); ResultSetMetaData rsmd = res.getMetaData(); execEnd = System.currentTimeMillis(); if (verbose) { for (int i = 1; i < nrColumns + 1; i++) { if (i != nrColumns) tabular(rsmd.getColumnName(i)); else tabularlast(rsmd.getColumnName(i)); } outputln(""); } while (res.next()) { for (int i = 1; i < nrColumns + 1; i++) { if (i != nrColumns) tabular(res.getString(i)); else tabularlast(res.getString(i)); } outputln("", true); } stat.close(); res.close(); } conn.close(); // Print the execution times outputln("Open time: " + (openEnd - openStart) + " ms"); if (line.hasOption("meta")) { outputln("Meta time: " + (metaEnd - metaStart) + " ms"); } if (line.hasOption("sql")) { outputln("Exec time: " + (execEnd - execStart) + " ms"); } }
From source file:my.yelp.populate.java
public static void main(String[] args) throws FileNotFoundException, ParseException, IOException, java.text.ParseException { try {//from w w w. j ava2 s . c o m DbConnection A1 = new DbConnection(); Connection con = A1.getConnection(); JSONParser jsonParser; jsonParser = new JSONParser(); Object obj1 = jsonParser.parse(new FileReader("C:\\Users\\Sanjay Desai\\Desktop\\yelp_user.json")); Object obj2 = jsonParser.parse(new FileReader("C:\\Users\\Sanjay Desai\\Desktop\\yelp_business.json")); Object obj3 = jsonParser.parse(new FileReader("C:\\Users\\Sanjay Desai\\Desktop\\yelp_review.json")); Object obj4 = jsonParser.parse(new FileReader("C:\\Users\\Sanjay Desai\\Desktop\\yelp_checkin.json")); JSONArray jsonArray1; jsonArray1 = (JSONArray) obj1; JSONArray jsonArray2; jsonArray2 = (JSONArray) obj2; JSONArray jsonArray3; jsonArray3 = (JSONArray) obj3; JSONArray jsonArray4; jsonArray4 = (JSONArray) obj4; // yelp_user String yelping_since, name1, user_id, type1; Long review_count1, fans; Double average_stars; Statement stmt; stmt = con.createStatement(); stmt.executeUpdate("Delete from N_User"); for (int i = 0; i < (jsonArray1.size()); i++) { JSONObject jsonObject = (JSONObject) jsonArray1.get(i); yelping_since = (String) jsonObject.get("yelping_since") + "-01"; JSONArray friends = (JSONArray) jsonObject.get("friends"); int friends_size = friends.size(); review_count1 = (Long) jsonObject.get("review_count"); name1 = (String) jsonObject.get("name"); user_id = (String) jsonObject.get("user_id"); fans = (Long) jsonObject.get("fans"); average_stars = (Double) jsonObject.get("average_stars"); type1 = (String) jsonObject.get("type"); try (PreparedStatement pstmt1 = con.prepareStatement( "Insert INTO N_User(yelping_since,friends_size,review_count,name,user_id,fans,average_stars,type) VALUES(?,?,?,?,?,?,?,?)")) { SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); java.util.Date myDate = format.parse(yelping_since); pstmt1.setDate(1, new java.sql.Date(myDate.getTime())); pstmt1.setInt(2, friends_size); pstmt1.setLong(3, review_count1); pstmt1.setString(4, name1); pstmt1.setString(5, user_id); pstmt1.setLong(6, fans); pstmt1.setDouble(7, average_stars); pstmt1.setString(8, type1); pstmt1.executeUpdate(); } catch (java.text.ParseException ex) { Logger.getLogger(populate.class.getName()).log(Level.SEVERE, null, ex); } } //yelp_business String business_id, address, city, state, name, type_business; Double stars; for (int i = 0; i < jsonArray2.size(); i++) { JSONObject jsonObject = (JSONObject) jsonArray2.get(i); business_id = (String) jsonObject.get("business_id"); address = (String) jsonObject.get("full_address"); city = (String) jsonObject.get("city"); state = (String) jsonObject.get("state"); name = (String) jsonObject.get("name"); stars = (Double) jsonObject.get("stars"); type_business = (String) jsonObject.get("type"); try (PreparedStatement pstmt2 = con.prepareStatement( "Insert INTO N_Business(business_id,address,city,state,name,stars,type_business) VALUES(?,?,?,?,?,?,?)")) { pstmt2.setString(1, business_id); pstmt2.setString(2, address); pstmt2.setString(3, city); pstmt2.setString(4, state); pstmt2.setString(5, name); pstmt2.setDouble(6, stars); pstmt2.setString(7, type_business); pstmt2.executeUpdate(); pstmt2.close(); } } //Category Table String[] categories = { "Active Life", "Arts & Entertainment", "Automotive", "Car Rental", "Cafes", "Beauty & Spas", "Convenience Stores", "Dentists", "Doctors", "Drugstores", "Department Stores", "Education", "Event Planning & Services", "Flowers & Gifts", "Food", "Health & Medical", "Home Services", "Home & Garden", "Hospitals", "Hotels & travel", "Hardware stores", "Grocery", "Medical Centers", "Nurseries & Gardening", "Nightlife", "Restaurants", "Shopping", "Transportation" }; JSONArray category; String[] individual_category = new String[100]; int count = 0, flag = 0, m = 0, n = 0; String[] business_category = new String[50]; String[] subcategory = new String[50]; for (int i = 0; i < jsonArray2.size(); i++) { JSONObject jsonObject3 = (JSONObject) jsonArray2.get(i); String business_id2 = (String) jsonObject3.get("business_id"); category = (JSONArray) jsonObject3.get("categories"); for (int j = 0; j < category.size(); j++) { individual_category[j] = (String) category.get(j); count = count + 1; } for (int k = 0; k < count; k++) { for (String categorie : categories) { if (individual_category[k].equals(categorie)) { flag = 1; break; } } if (flag == 1) { business_category[m] = individual_category[k]; m = m + 1; flag = 0; } else { subcategory[n] = individual_category[k]; n = n + 1; } } for (int p = 0; p < m; p++) { for (int q = 0; q < n; q++) { try (PreparedStatement pstmt3 = con.prepareStatement( "INSERT INTO N_Category(business_id,category,subcategory) VALUES(?,?,?)")) { pstmt3.setString(1, business_id2); pstmt3.setString(2, business_category[p]); pstmt3.setString(3, subcategory[q]); pstmt3.executeUpdate(); } } } count = 0; m = 0; n = 0; } //yelp_review String user_id3, review_id, type3, business_id3, text, text1, review_date; Long stars3; int votes = 0; Integer no_votes; JSONObject votes_info; Set<String> keys; for (int i = 0; i < jsonArray3.size(); i++) { JSONObject jsonObject = (JSONObject) jsonArray3.get(i); votes_info = (JSONObject) jsonObject.get("votes"); keys = votes_info.keySet(); for (String r_key : keys) { votes = (int) (votes + (Long) votes_info.get(r_key)); } no_votes = toIntExact(votes); user_id3 = (String) jsonObject.get("user_id"); review_id = (String) jsonObject.get("review_id"); business_id3 = (String) jsonObject.get("business_id"); review_date = (String) jsonObject.get("date"); text1 = (String) jsonObject.get("text"); text = text1.substring(0, Math.min(1000, text1.length())); stars3 = (Long) jsonObject.get("stars"); type3 = (String) jsonObject.get("type"); try (PreparedStatement pstmt4 = con.prepareStatement( "Insert INTO N_Review(no_votes,user_id,review_id,business_id,review_date,text,stars,type) VALUES(?,?,?,?,?,?,?,?)")) { SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); java.util.Date myDate = format.parse(review_date); pstmt4.setInt(1, no_votes); pstmt4.setString(2, user_id3); pstmt4.setString(3, review_id); pstmt4.setString(4, business_id3); pstmt4.setDate(5, new java.sql.Date(myDate.getTime())); pstmt4.setString(6, text); pstmt4.setLong(7, stars3); pstmt4.setString(8, type3); pstmt4.executeUpdate(); pstmt4.close(); } } //Checkin_Info JSONObject checkin_info; String business_id4; Long check_in_count; Set<String> keys1; String[] timing = new String[10]; int n1 = 0, time, hour; //Inserting into checkin_info for (int i = 0; i < jsonArray4.size(); i++) { JSONObject jsonObject4 = (JSONObject) jsonArray4.get(i); checkin_info = (JSONObject) jsonObject4.get("checkin_info"); business_id4 = (String) jsonObject4.get("business_id"); keys1 = checkin_info.keySet(); for (String key : keys1) { check_in_count = (Long) checkin_info.get(key); for (String x : key.split("-")) { timing[n1] = x; n1 = n1 + 1; } n1 = 0; hour = Integer.parseInt(timing[0]); time = Integer.parseInt(timing[1]); try (PreparedStatement pstmt5 = con.prepareStatement( "INSERT INTO check_info(business_id,hour,day,check_in_count)VALUES(?,?,?,?)")) { pstmt5.setString(1, business_id4); pstmt5.setInt(2, hour); pstmt5.setInt(3, time); pstmt5.setLong(4, check_in_count); pstmt5.executeUpdate(); } } } con.close(); } catch (SQLException ex) { Logger.getLogger(populate.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:TransactionPairs.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con = null; Statement stmt;//from w w w.j ava 2 s.co m PreparedStatement updateSales; PreparedStatement updateTotal; String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; String updateStatement = "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?"; String query = "select COF_NAME, SALES, TOTAL from COFFEES"; 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"); updateSales = con.prepareStatement(updateString); updateTotal = con.prepareStatement(updateStatement); int[] salesForWeek = { 175, 150, 60, 155, 90 }; String[] coffees = { "Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf" }; int len = coffees.length; con.setAutoCommit(false); for (int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); updateTotal.setInt(1, salesForWeek[i]); updateTotal.setString(2, coffees[i]); updateTotal.executeUpdate(); con.commit(); } con.setAutoCommit(true); updateSales.close(); updateTotal.close(); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String c = rs.getString("COF_NAME"); int s = rs.getInt("SALES"); int t = rs.getInt("TOTAL"); System.out.println(c + " " + s + " " + t); } stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); if (con != null) { try { System.err.print("Transaction is being "); System.err.println("rolled back"); con.rollback(); } catch (SQLException excep) { System.err.print("SQLException: "); System.err.println(excep.getMessage()); } } } }
From source file:TransactionPairs.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con = null; Statement stmt;//from w w w .j a v a 2s. c o m PreparedStatement updateSales; PreparedStatement updateTotal; String updateString = "update COFFEES " + "set SALES = ? where COF_NAME like ?"; String updateStatement = "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME like ?"; String query = "select COF_NAME, SALES, TOTAL from COFFEES"; 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"); updateSales = con.prepareStatement(updateString); updateTotal = con.prepareStatement(updateStatement); int[] salesForWeek = { 175, 150, 60, 155, 90 }; String[] coffees = { "Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf" }; int len = coffees.length; con.setAutoCommit(false); for (int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); updateTotal.setInt(1, salesForWeek[i]); updateTotal.setString(2, coffees[i]); updateTotal.executeUpdate(); con.commit(); } con.setAutoCommit(true); updateSales.close(); updateTotal.close(); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String c = rs.getString("COF_NAME"); int s = rs.getInt("SALES"); int t = rs.getInt("TOTAL"); System.out.println(c + " " + s + " " + t); } stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); if (con != null) { try { System.err.print("Transaction is being "); System.err.println("rolled back"); con.rollback(); } catch (SQLException excep) { System.err.print("SQLException: "); System.err.println(excep.getMessage()); } } } }
From source file:ExecuteSQL.java
public static void main(String[] args) { Connection conn = null; // Our JDBC connection to the database server try {/*from w w w .j ava2s . c o m*/ String driver = null, url = null, user = "", password = ""; // Parse all the command-line arguments for (int n = 0; n < args.length; n++) { if (args[n].equals("-d")) driver = args[++n]; else if (args[n].equals("-u")) user = args[++n]; else if (args[n].equals("-p")) password = args[++n]; else if (url == null) url = args[n]; else throw new IllegalArgumentException("Unknown argument."); } // The only required argument is the database URL. if (url == null) throw new IllegalArgumentException("No database specified"); // If the user specified the classname for the DB driver, load // that class dynamically. This gives the driver the opportunity // to register itself with the DriverManager. if (driver != null) Class.forName(driver); // Now open a connection the specified database, using the // user-specified username and password, if any. The driver // manager will try all of the DB drivers it knows about to try to // parse the URL and connect to the DB server. conn = DriverManager.getConnection(url, user, password); // Now create the statement object we'll use to talk to the DB Statement s = conn.createStatement(); // Get a stream to read from the console BufferedReader in = new BufferedReader(new InputStreamReader(System.in)); // Loop forever, reading the user's queries and executing them while (true) { System.out.print("sql> "); // prompt the user System.out.flush(); // make the prompt appear now. String sql = in.readLine(); // get a line of input from user // Quit when the user types "quit". if ((sql == null) || sql.equals("quit")) break; // Ignore blank lines if (sql.length() == 0) continue; // Now, execute the user's line of SQL and display results. try { // We don't know if this is a query or some kind of // update, so we use execute() instead of executeQuery() // or executeUpdate() If the return value is true, it was // a query, else an update. boolean status = s.execute(sql); // Some complex SQL queries can return more than one set // of results, so loop until there are no more results do { if (status) { // it was a query and returns a ResultSet ResultSet rs = s.getResultSet(); // Get results printResultsTable(rs, System.out); // Display them } else { // If the SQL command that was executed was some // kind of update rather than a query, then it // doesn't return a ResultSet. Instead, we just // print the number of rows that were affected. int numUpdates = s.getUpdateCount(); System.out.println("Ok. " + numUpdates + " rows affected."); } // Now go see if there are even more results, and // continue the results display loop if there are. status = s.getMoreResults(); } while (status || s.getUpdateCount() != -1); } // If a SQLException is thrown, display an error message. // Note that SQLExceptions can have a general message and a // DB-specific message returned by getSQLState() catch (SQLException e) { System.err.println("SQLException: " + e.getMessage() + ":" + e.getSQLState()); } // Each time through this loop, check to see if there were any // warnings. Note that there can be a whole chain of warnings. finally { // print out any warnings that occurred SQLWarning w; for (w = conn.getWarnings(); w != null; w = w.getNextWarning()) System.err.println("WARNING: " + w.getMessage() + ":" + w.getSQLState()); } } } // Handle exceptions that occur during argument parsing, database // connection setup, etc. For SQLExceptions, print the details. catch (Exception e) { System.err.println(e); if (e instanceof SQLException) System.err.println("SQL State: " + ((SQLException) e).getSQLState()); System.err.println( "Usage: java ExecuteSQL [-d <driver>] " + "[-u <user>] [-p <password>] <database URL>"); } // Be sure to always close the database connection when we exit, // whether we exit because the user types 'quit' or because of an // exception thrown while setting things up. Closing this connection // also implicitly closes any open statements and result sets // associated with it. finally { try { conn.close(); } catch (Exception e) { } } }
From source file:SetSavepoint.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; try {/*from w w w . j a v a 2 s . c om*/ Class.forName("myDriver.className"); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { Connection con = DriverManager.getConnection(url, "myLogin", "myPassword"); con.setAutoCommit(false); String query = "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE TOTAL > ?"; String update = "UPDATE COFFEES SET PRICE = ? " + "WHERE COF_NAME = ?"; PreparedStatement getPrice = con.prepareStatement(query); PreparedStatement updatePrice = con.prepareStatement(update); getPrice.setInt(1, 7000); ResultSet rs = getPrice.executeQuery(); Savepoint save1 = con.setSavepoint(); while (rs.next()) { String cof = rs.getString("COF_NAME"); float oldPrice = rs.getFloat("PRICE"); float newPrice = oldPrice + (oldPrice * .05f); updatePrice.setFloat(1, newPrice); updatePrice.setString(2, cof); updatePrice.executeUpdate(); System.out.println("New price of " + cof + " is " + newPrice); if (newPrice > 11.99) { con.rollback(save1); } } getPrice = con.prepareStatement(query); updatePrice = con.prepareStatement(update); getPrice.setInt(1, 8000); rs = getPrice.executeQuery(); System.out.println(); Savepoint save2 = con.setSavepoint(); while (rs.next()) { String cof = rs.getString("COF_NAME"); float oldPrice = rs.getFloat("PRICE"); float newPrice = oldPrice + (oldPrice * .05f); updatePrice.setFloat(1, newPrice); updatePrice.setString(2, cof); updatePrice.executeUpdate(); System.out.println("New price of " + cof + " is " + newPrice); if (newPrice > 11.99) { con.rollback(save2); } } con.commit(); Statement stmt = con.createStatement(); rs = stmt.executeQuery("SELECT COF_NAME, " + "PRICE FROM COFFEES"); System.out.println(); while (rs.next()) { String name = rs.getString("COF_NAME"); float price = rs.getFloat("PRICE"); System.out.println("Current price of " + name + " is " + price); } con.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.ipinfodb.IpInfoDbClient.java
public static void main(String[] args) throws IOException { if (args.length < 3 || args.length > 4) { System.out.println("Usage: org.ipinfodb.IpInfoDbClient MODE API_KEY [IP_ADDRESS]\n" + "MODE can be either 'ip-country' or 'ip-city'.\n" + "If you don't have an API_KEY yet, you can get one for free by registering at http://www.ipinfodb.com/register.php."); return;/*from ww w. ja v a 2s . c o m*/ } //Code for Reading the multiple ip addresses from a given txt file. File inputLogFilePath = new File(args[1]); FileReader fr = new FileReader(inputLogFilePath); BufferedReader br = new BufferedReader(fr); String logLine; /*File outputFile=new File("C:/Users/Intelligrape/Desktop/New_Sample_29042014.txt"); // if file doesnt exists, then create it if (!outputFile.exists()) { outputFile.createNewFile(); } FileWriter fw = new FileWriter(outputFile,true); BufferedWriter bw = new BufferedWriter(fw); bw.write("S.no, IPAddress, Country, Region, City, Zipcode"); bw.newLine();*/ try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("Driver found"); } catch (ClassNotFoundException e) { System.out.println("Driver not found: " + e); } String url_db = "jdbc:mysql://mysqldb.c7zitrf2gguq.us-east-1.rds.amazonaws.com/matse?user=shagun&password=shagun001"; Connection con = null; int insertCounter = 0; int count = 1; while ((logLine = br.readLine()) != null) { String mode = args[2]; String apiKey = args[3]; String url = "http://api.ipinfodb.com/v3/" + mode + "/?format=json&key=" + apiKey; String[] split = logLine.split(","); String ip = split[3]; url += "&ip=" + ip; try { HttpGet request = new HttpGet(url); HttpResponse response = HTTP_CLIENT.execute(request, new BasicHttpContext()); if (response.getStatusLine().getStatusCode() != HttpStatus.SC_OK) { throw new RuntimeException("IpInfoDb response is " + response.getStatusLine()); } String responseBody = EntityUtils.toString(response.getEntity()); IpCityResponse ipCityResponse = MAPPER.readValue(responseBody, IpCityResponse.class); if ("OK".equals(ipCityResponse.getStatusCode())) { //System.out.print(count+", "+ip+", "+ipCityResponse.getCountryCode() + ", " + ipCityResponse.getRegionName() + ", " + ipCityResponse.getCityName() + ", " + ipCityResponse.zipCode+ ", " + ipCityResponse.countryName + ", " + ipCityResponse.latitude + ", " + ipCityResponse.longitude + ", " + ipCityResponse.timeZone); //bw.write(count+", "+ip+", "+ipCityResponse.getCountryCode() + ", " + ipCityResponse.getRegionName() + ", " + ipCityResponse.getCityName() + ", " + ipCityResponse.zipCode + ", " + ipCityResponse.countryName + ", " + ipCityResponse.latitude + ", " + ipCityResponse.longitude + ", " + ipCityResponse.timeZone); try { con = DriverManager.getConnection(url_db); //System.out.println("Connected successfully"); Statement stmt = con.createStatement(); //System.out.println(logLine); //System.out.println(split.length); int result = stmt.executeUpdate("INSERT INTO LOGDATA1 VALUES('" + split[0] + "','" + split[1].trim() + "','" + split[2] + "','" + split[3] + "','" + split[4] + "','" + ipCityResponse.statusCode + "','" + ipCityResponse.countryCode + "','" + ipCityResponse.countryName + "','" + ipCityResponse.regionName + "','" + ipCityResponse.cityName + "','" + ipCityResponse.zipCode + "','" + ipCityResponse.latitude + "','" + ipCityResponse.longitude + "','" + ipCityResponse.timeZone + "')"); ++insertCounter; con.close(); System.out.println(insertCounter); } catch (SQLException e) { System.out.println("something wrong in the connection string: " + e); } } else { System.out.print("API status message is '" + ipCityResponse.getStatusMessage() + "'"); } } finally { //HTTP_CLIENT.getConnectionManager().shutdown(); } ++count; } HTTP_CLIENT.getConnectionManager().shutdown(); }
From source file:com.mycompany.mavenproject4.NewMain.java
/** * @param args the command line arguments *//*from w w w . ja va 2s . c om*/ public static void main(String[] args) { System.out.println("Enter your choice do you want to work with 1.postgre 2.Redis 3.Mongodb"); InputStreamReader IORdatabase = new InputStreamReader(System.in); BufferedReader BIOdatabase = new BufferedReader(IORdatabase); String databasechoice = null; try { databasechoice = BIOdatabase.readLine(); } catch (Exception E7) { System.out.println(E7.getMessage()); } // loading data from the CSV file CsvReader Employees = null; try { Employees = new CsvReader("CSVFile\\Employees.csv"); } catch (FileNotFoundException EB) { System.out.println(EB.getMessage()); } int ColumnCount = 3; int RowCount = 100; int iloop = 0; try { Employees = new CsvReader("CSVFile\\Employees.csv"); } catch (FileNotFoundException E) { System.out.println(E.getMessage()); } String[][] Dataarray = new String[RowCount][ColumnCount]; try { while (Employees.readRecord()) { String v; String[] x; v = Employees.getRawRecord(); x = v.split(","); for (int j = 0; j < ColumnCount; j++) { String value = null; int z = j; value = x[z]; try { Dataarray[iloop][j] = value; } catch (Exception E) { System.out.println(E.getMessage()); } // System.out.println(Dataarray[iloop][j]); } iloop = iloop + 1; } } catch (IOException Em) { System.out.println(Em.getMessage()); } Employees.close(); // connection to Database switch (databasechoice) { // postgre code goes here case "1": Connection Conn = null; Statement Stmt = null; URI dbUri = null; String choice = null; InputStreamReader objin = new InputStreamReader(System.in); BufferedReader objbuf = new BufferedReader(objin); try { Class.forName("org.postgresql.Driver"); } catch (Exception E1) { System.out.println(E1.getMessage()); } String username = "ldoiarosfrzrua"; String password = "HBkE_pJpK5mMIg3p2q1_odmEFX"; String dbUrl = "jdbc:postgresql://ec2-54-83-53-120.compute-1.amazonaws.com:5432/d6693oljh5cco4?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory"; // now update data in the postgress Database // for(int i=0;i<RowCount;i++) // { // try // { // Conn= DriverManager.getConnection(dbUrl, username, password); // Stmt = Conn.createStatement(); // String Connection_String = "insert into EmployeeDistinct (name,jobtitle,department) values (' "+ Dataarray[i][0]+" ',' "+ Dataarray[i][1]+" ',' "+ Dataarray[i][2]+" ')"; // Stmt.executeUpdate(Connection_String); // // } // catch(SQLException E4) // { // System.out.println(E4.getMessage()); // } // } // Quering with the Database System.out.println("1. Display Data "); System.out.println("2. Select data based on primary key"); System.out.println("3. Select data based on Other attributes e.g Name"); System.out.println("Enter your Choice "); try { choice = objbuf.readLine(); } catch (IOException E) { System.out.println(E.getMessage()); } switch (choice) { case "1": try { Conn = DriverManager.getConnection(dbUrl, username, password); Stmt = Conn.createStatement(); String Connection_String = " Select * from EmployeeDistinct;"; ResultSet objRS = Stmt.executeQuery(Connection_String); while (objRS.next()) { System.out.println("Employee ID: " + objRS.getInt("EmployeeID")); System.out.println("Employee Name: " + objRS.getString("Name")); System.out.println("Employee City: " + objRS.getString("Jobtitle")); System.out.println("Employee City: " + objRS.getString("Department")); } } catch (Exception E2) { System.out.println(E2.getMessage()); } break; case "2": System.out.println("Enter the Primary Key"); InputStreamReader objkey = new InputStreamReader(System.in); BufferedReader objbufkey = new BufferedReader(objkey); int key = 0; try { key = Integer.parseInt(objbufkey.readLine()); } catch (IOException E) { System.out.println(E.getMessage()); } try { Conn = DriverManager.getConnection(dbUrl, username, password); Stmt = Conn.createStatement(); String Connection_String = " Select * from EmployeeDistinct where EmployeeID=" + key + ";"; ResultSet objRS = Stmt.executeQuery(Connection_String); while (objRS.next()) { System.out.println("Employee Name: " + objRS.getString("Name")); System.out.println("Employee City: " + objRS.getString("Jobtitle")); System.out.println("Employee City: " + objRS.getString("Department")); } } catch (Exception E2) { System.out.println(E2.getMessage()); } break; case "3": String Name = null; System.out.println("Enter Name to find the record"); InputStreamReader objname = new InputStreamReader(System.in); BufferedReader objbufname = new BufferedReader(objname); try { Name = (objbufname.readLine()).toString(); } catch (IOException E) { System.out.println(E.getMessage()); } try { Conn = DriverManager.getConnection(dbUrl, username, password); Stmt = Conn.createStatement(); String Connection_String = " Select * from EmployeeDistinct where Name=" + "'" + Name + "'" + ";"; ResultSet objRS = Stmt.executeQuery(Connection_String); while (objRS.next()) { System.out.println("Employee ID: " + objRS.getInt("EmployeeID")); System.out.println("Employee City: " + objRS.getString("Jobtitle")); System.out.println("Employee City: " + objRS.getString("Department")); } } catch (Exception E2) { System.out.println(E2.getMessage()); } break; } try { Conn.close(); } catch (SQLException E6) { System.out.println(E6.getMessage()); } break; // Redis code goes here case "2": int Length = 0; String ID = null; Length = 100; // Connection to Redis Jedis jedis = new Jedis("pub-redis-18017.us-east-1-4.6.ec2.redislabs.com", 18017); jedis.auth("7EFOisRwMu8zvhin"); System.out.println("Connected to Redis"); // Storing values in the database // System.out.println("Storing values in the Database might take a minute "); // for(int i=0;i<Length;i++) // { // Store data in redis // int j=i+1; // jedis.hset("Employe:" + j , "Name", Dataarray[i][0]); // jedis.hset("Employe:" + j , "Jobtitle ", Dataarray[i][1]); // jedis.hset("Employe:" + j , "Department", Dataarray[i][2]); // } System.out.println("Search by 1.primary key,2.Name 3.display first 20"); InputStreamReader objkey = new InputStreamReader(System.in); BufferedReader objbufkey = new BufferedReader(objkey); String interimChoice1 = null; try { interimChoice1 = objbufkey.readLine(); } catch (IOException E) { System.out.println(E.getMessage()); } switch (interimChoice1) { case "1": System.out.print("Get details using the primary Key"); InputStreamReader IORKey = new InputStreamReader(System.in); BufferedReader BIORKey = new BufferedReader(IORKey); String ID1 = null; try { ID1 = BIORKey.readLine(); } catch (IOException E3) { System.out.println(E3.getMessage()); } Map<String, String> properties = jedis.hgetAll("Employe:" + Integer.parseInt(ID1)); for (Map.Entry<String, String> entry : properties.entrySet()) { System.out.println("Name:" + jedis.hget("Employee:" + Integer.parseInt(ID1), "Name")); System.out.println("Jobtitle:" + jedis.hget("Employee:" + Integer.parseInt(ID1), "Jobtitle")); System.out .println("Department:" + jedis.hget("Employee:" + Integer.parseInt(ID1), "Department")); } break; case "2": System.out.print("Get details using Department"); InputStreamReader IORName1 = new InputStreamReader(System.in); BufferedReader BIORName1 = new BufferedReader(IORName1); String ID2 = null; try { ID2 = BIORName1.readLine(); } catch (IOException E3) { System.out.println(E3.getMessage()); } for (int i = 0; i < 100; i++) { Map<String, String> properties3 = jedis.hgetAll("Employe:" + i); for (Map.Entry<String, String> entry : properties3.entrySet()) { String value = entry.getValue(); if (entry.getValue().equals(ID2)) { System.out.println("Name:" + jedis.hget("Employee:" + i, "Name")); System.out.println("Jobtitle:" + jedis.hget("Employee:" + i, "Jobtitle")); System.out.println("Department:" + jedis.hget("Employee:" + i, "Department")); } } } //for (Map.Entry<String, String> entry : properties1.entrySet()) //{ //System.out.println(entry.getKey() + "---" + entry.getValue()); // } break; case "3": for (int i = 1; i < 21; i++) { Map<String, String> properties3 = jedis.hgetAll("Employe:" + i); for (Map.Entry<String, String> entry : properties3.entrySet()) { // System.out.println(jedis.hget("Employee:" + i,"Name")); System.out.println("Name:" + jedis.hget("Employee:" + i, "Name")); System.out.println("Jobtitle:" + jedis.hget("Employee:" + i, "Jobtitle")); System.out.println("Department:" + jedis.hget("Employee:" + i, "Department")); } } break; } break; // mongo db code goes here case "3": MongoClient mongo = new MongoClient( new MongoClientURI("mongodb://root2:12345@ds055564.mongolab.com:55564/heroku_766dnj8c")); DB db; db = mongo.getDB("heroku_766dnj8c"); // storing values in the database // for(int i=0;i<100;i++) // { // BasicDBObject document = new BasicDBObject(); // document.put("_id", i+1); // document.put("Name", Dataarray[i][0]); // document.put("Jobtitle", Dataarray[i][1]); // document.put("Department", Dataarray[i][2]); // db.getCollection("EmployeeRaw").insert(document); // } System.out.println("Search by 1.primary key,2.Name 3.display first 20"); InputStreamReader objkey6 = new InputStreamReader(System.in); BufferedReader objbufkey6 = new BufferedReader(objkey6); String interimChoice = null; try { interimChoice = objbufkey6.readLine(); } catch (IOException E) { System.out.println(E.getMessage()); } switch (interimChoice) { case "1": System.out.println("Enter the Primary Key"); InputStreamReader IORPkey = new InputStreamReader(System.in); BufferedReader BIORPkey = new BufferedReader(IORPkey); int Pkey = 0; try { Pkey = Integer.parseInt(BIORPkey.readLine()); } catch (IOException E) { System.out.println(E.getMessage()); } BasicDBObject inQuery = new BasicDBObject(); inQuery.put("_id", Pkey); DBCursor cursor = db.getCollection("EmployeeRaw").find(inQuery); while (cursor.hasNext()) { // System.out.println(cursor.next()); System.out.println(cursor.next()); } break; case "2": System.out.println("Enter the Name to Search"); InputStreamReader objName = new InputStreamReader(System.in); BufferedReader objbufName = new BufferedReader(objName); String Name = null; try { Name = objbufName.readLine(); } catch (IOException E) { System.out.println(E.getMessage()); } BasicDBObject inQuery1 = new BasicDBObject(); inQuery1.put("Name", Name); DBCursor cursor1 = db.getCollection("EmployeeRaw").find(inQuery1); while (cursor1.hasNext()) { // System.out.println(cursor.next()); System.out.println(cursor1.next()); } break; case "3": for (int i = 1; i < 21; i++) { BasicDBObject inQuerya = new BasicDBObject(); inQuerya.put("_id", i); DBCursor cursora = db.getCollection("EmployeeRaw").find(inQuerya); while (cursora.hasNext()) { // System.out.println(cursor.next()); System.out.println(cursora.next()); } } break; } break; } }