List of usage examples for java.sql DriverManager getConnection
private static Connection getConnection(String url, java.util.Properties info, Class<?> caller) throws SQLException
From source file:CreateSuppliers.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;//from w w w . j ava 2 s .c o m String createString; createString = "create table SUPPLIERS " + "(SUP_ID int, " + "SUP_NAME varchar(40), " + "STREET varchar(40), " + "CITY varchar(20), " + "STATE char(2), ZIP char(5))"; Statement stmt; 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"); stmt = con.createStatement(); stmt.executeUpdate(createString); stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); } }
From source file:InsertRowBug.java
public static void main(String args[]) { String url;//from w w w.j a va 2 s.c o m // url = "jdbc:odbc:SQL Anywhere 5.0 Sample"; // url = "jdbc:oracle:thin:@server:1521:db570"; url = "jdbc:odbc:RainForestDSN"; String driver; //driver = "oracle.jdbc.driver.OracleDriver"; driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String user, pass; user = "student"; pass = "student"; Connection con; Statement stmt; ResultSet uprs; try { Class.forName(driver); } catch (java.lang.ClassNotFoundException e) { System.err.println(e); return; } try { con = DriverManager.getConnection(url, user, pass); stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); uprs = stmt.executeQuery("SELECT * FROM Music_Recordings"); // Check the column count ResultSetMetaData md = uprs.getMetaData(); System.out.println("Resultset has " + md.getColumnCount() + " cols."); int rowNum = uprs.getRow(); System.out.println("row1 " + rowNum); uprs.absolute(1); rowNum = uprs.getRow(); System.out.println("row2 " + rowNum); uprs.next(); uprs.moveToInsertRow(); uprs.updateInt(1, 150); uprs.updateString(2, "Madonna"); uprs.updateString(3, "Dummy"); uprs.updateString(4, "Jazz"); uprs.updateString(5, "Image"); uprs.updateInt(6, 5); uprs.updateDouble(7, 5); uprs.updateInt(8, 15); uprs.insertRow(); uprs.close(); stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); } }
From source file:CreateStores.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/*from www . j a v a2 s . c o m*/ String createTable; String createArray; createArray = "CREATE TYPE COF_ARRAY AS ARRAY(10) OF VARCHAR(40)"; createTable = "CREATE TABLE STORES ( " + "STORE_NO INTEGER, LOCATION ADDRESS, " + "COF_TYPES COF_ARRAY, MGR REF MANAGER )"; Statement stmt; 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"); stmt = con.createStatement(); stmt.executeUpdate(createArray); stmt.executeUpdate(createTable); stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); } }
From source file:PrimaryKeysSuppliers.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/*ww w . j a v a 2 s. c om*/ String createString = "create table SUPPLIERSPK " + "(SUP_ID INTEGER NOT NULL, " + "SUP_NAME VARCHAR(40), " + "STREET VARCHAR(40), " + "CITY VARCHAR(20), " + "STATE CHAR(2), " + "ZIP CHAR(5), " + "primary key(SUP_ID))"; Statement stmt; 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"); stmt = con.createStatement(); stmt.executeUpdate(createString); DatabaseMetaData dbmd = con.getMetaData(); ResultSet rs = dbmd.getPrimaryKeys(null, null, "SUPPLIERSPK"); while (rs.next()) { String name = rs.getString("TABLE_NAME"); String columnName = rs.getString("COLUMN_NAME"); String keySeq = rs.getString("KEY_SEQ"); String pkName = rs.getString("PK_NAME"); System.out.println("table name : " + name); System.out.println("column name: " + columnName); System.out.println("sequence in key: " + keySeq); System.out.println("primary key name: " + pkName); System.out.println(""); } rs.close(); con.close(); } catch (SQLException ex) { System.err.print("SQLException: "); System.err.println(ex.getMessage()); } }
From source file:ForeignKeysCoffees.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/* ww w . j a v a 2s . com*/ String createString = "create table COFFEESFK " + "(COF_NAME varchar(32) NOT NULL, " + "SUP_ID int, " + "PRICE float, " + "SALES int, " + "TOTAL int, " + "primary key(COF_NAME), " + "foreign key(SUP_ID) references SUPPLIERSPK)"; Statement stmt; 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"); stmt = con.createStatement(); stmt.executeUpdate(createString); DatabaseMetaData dbmd = con.getMetaData(); ResultSet rs = dbmd.getImportedKeys(null, null, "COFFEESFK"); while (rs.next()) { String pkTable = rs.getString("PKTABLE_NAME"); String pkColName = rs.getString("PKCOLUMN_NAME"); String fkTable = rs.getString("FKTABLE_NAME"); String fkColName = rs.getString("FKCOLUMN_NAME"); short updateRule = rs.getShort("UPDATE_RULE"); short deleteRule = rs.getShort("DELETE_RULE"); System.out.println("primary key table name : " + pkTable); System.out.print("primary key column name : "); System.out.println(pkColName); System.out.println("foreign key table name : " + fkTable); System.out.print("foreign key column name : "); System.out.println(fkColName); System.out.println("update rule: " + updateRule); System.out.println("delete rule: " + deleteRule); System.out.println(""); } rs.close(); stmt.close(); con.close(); } catch (SQLException ex) { System.err.print("SQLException: "); System.err.println(ex.getMessage()); } }
From source file:CreateUDTs.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/*from www . ja v a2 s . c o m*/ Statement stmt; String createAddress = "CREATE TYPE ADDRESS (NUM INTEGER, " + "STREET VARCHAR(40), CITY VARCHAR(40), " + "STATE CHAR(2), ZIP CHAR(5))"; String createManager = "CREATE TYPE MANAGER (MGR_ID INTEGER, " + "LAST_NAME VARCHAR(40), FIRST_NAME VARCHAR(40), " + "PHONE char(10))"; 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"); stmt = con.createStatement(); stmt.executeUpdate(createAddress); stmt.executeUpdate("CREATE TYPE PHONE_NO AS CHAR(10)"); stmt.executeUpdate(createManager); stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("-----SQLException-----"); System.err.println("SQLState: " + ex.getSQLState()); System.err.println("Message: " + ex.getMessage()); System.err.println("Vendor: " + ex.getErrorCode()); } }
From source file:CreateNewType.java
public static void main(String[] args) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;// w w w.j a v a 2 s.c o m Statement stmt; 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"); stmt = con.createStatement(); String typeToCreate = null; String prompt = "Enter 's' to create a structured type " + "or 'd' to create a distinct type\n" + "and hit Return: "; do { typeToCreate = getInput(prompt) + " "; typeToCreate = typeToCreate.toLowerCase().substring(0, 1); } while (!(typeToCreate.equals("s") || typeToCreate.equals("d"))); Vector dataTypes = getDataTypes(con, typeToCreate); String typeName; String attributeName; String sqlType; prompt = "Enter the new type name and hit Return: "; typeName = getInput(prompt); String createTypeString = "create type " + typeName; if (typeToCreate.equals("d")) createTypeString += " as "; else createTypeString += " ("; String commaAndSpace = ", "; boolean firstTime = true; while (true) { System.out.println(""); prompt = "Enter an attribute name " + "(or nothing when finished) \nand hit Return: "; attributeName = getInput(prompt); if (firstTime) { if (attributeName.length() == 0) { System.out.print("Need at least one attribute;"); System.out.println(" please try again"); continue; } else { createTypeString += attributeName + " "; firstTime = false; } } else if (attributeName.length() == 0) { break; } else { createTypeString += commaAndSpace + attributeName + " "; } String localTypeName = null; String paramString = ""; while (true) { System.out.println(""); System.out.println("LIST OF TYPES YOU MAY USE: "); boolean firstPrinted = true; int length = 0; for (int i = 0; i < dataTypes.size(); i++) { DataType dataType = (DataType) dataTypes.get(i); if (!dataType.needsToBeSet()) { if (!firstPrinted) System.out.print(commaAndSpace); else firstPrinted = false; System.out.print(dataType.getSQLType()); length += dataType.getSQLType().length(); if (length > 50) { System.out.println(""); length = 0; firstPrinted = true; } } } System.out.println(""); int index; prompt = "Enter an attribute type " + "from the list and hit Return: "; sqlType = getInput(prompt); for (index = 0; index < dataTypes.size(); index++) { DataType dataType = (DataType) dataTypes.get(index); if (dataType.getSQLType().equalsIgnoreCase(sqlType) && !dataType.needsToBeSet()) { break; } } localTypeName = null; paramString = ""; if (index < dataTypes.size()) { // there was a match String params; DataType dataType = (DataType) dataTypes.get(index); params = dataType.getParams(); localTypeName = dataType.getLocalType(); if (params != null) { prompt = "Enter " + params + ": "; paramString = "(" + getInput(prompt) + ")"; } break; } else { // use the name as given prompt = "Are you sure? " + "Enter 'y' or 'n' and hit Return: "; String check = getInput(prompt) + " "; check = check.toLowerCase().substring(0, 1); if (check.equals("n")) continue; else { localTypeName = sqlType; break; } } } createTypeString += localTypeName + paramString; if (typeToCreate.equals("d")) break; } if (typeToCreate.equals("s")) createTypeString += ")"; System.out.println(""); System.out.print("Your CREATE TYPE statement as "); System.out.println("sent to your DBMS: "); System.out.println(createTypeString); System.out.println(""); stmt.executeUpdate(createTypeString); stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); } }
From source file:TransactionPairs.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con = null;//ww w . j a va 2 s .co m Statement stmt; 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:Main.java
public static void main(String[] args) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;// w ww .jav a 2 s . c o m Statement stmt; 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"); stmt = con.createStatement(); Vector dataTypes = getDataTypes(con); String tableName; String columnName; String sqlType; String prompt = "Enter the new table name and hit Return: "; tableName = getInput(prompt); String createTableString = "create table " + tableName + " ("; String commaAndSpace = ", "; boolean firstTime = true; while (true) { System.out.println(""); prompt = "Enter a column name " + "(or nothing when finished) \nand hit Return: "; columnName = getInput(prompt); if (firstTime) { if (columnName.length() == 0) { System.out.print("Need at least one column;"); System.out.println(" please try again"); continue; } else { createTableString += columnName + " "; firstTime = false; } } else if (columnName.length() == 0) { break; } else { createTableString += commaAndSpace + columnName + " "; } String localTypeName = null; String paramString = ""; while (true) { System.out.println(""); System.out.println("LIST OF TYPES YOU MAY USE: "); boolean firstPrinted = true; int length = 0; for (int i = 0; i < dataTypes.size(); i++) { DataType dataType = (DataType) dataTypes.get(i); if (!dataType.needsToBeSet()) { if (!firstPrinted) System.out.print(commaAndSpace); else firstPrinted = false; System.out.print(dataType.getSQLType()); length += dataType.getSQLType().length(); if (length > 50) { System.out.println(""); length = 0; firstPrinted = true; } } } System.out.println(""); int index; prompt = "Enter a column type " + "from the list and hit Return: "; sqlType = getInput(prompt); for (index = 0; index < dataTypes.size(); index++) { DataType dataType = (DataType) dataTypes.get(index); if (dataType.getSQLType().equalsIgnoreCase(sqlType) && !dataType.needsToBeSet()) { break; } } localTypeName = null; paramString = ""; if (index < dataTypes.size()) { // there was a match String params; DataType dataType = (DataType) dataTypes.get(index); params = dataType.getParams(); localTypeName = dataType.getLocalType(); if (params != null) { prompt = "Enter " + params + ": "; paramString = "(" + getInput(prompt) + ")"; } break; } else { // use the name as given prompt = "Are you sure? " + "Enter 'y' or 'n' and hit Return: "; String check = getInput(prompt) + " "; check = check.toLowerCase().substring(0, 1); if (check.equals("n")) continue; else { localTypeName = sqlType; break; } } } createTableString += localTypeName + paramString; } createTableString += ")"; System.out.println(""); System.out.print("Your CREATE TABLE statement as "); System.out.println("sent to your DBMS: "); System.out.println(createTableString); System.out.println(""); stmt.executeUpdate(createTableString); stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); } }
From source file:CreateRef.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/*from ww w .j a va 2 s . c om*/ Statement stmt; 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()); } }