Example usage for java.sql DriverManager getConnection

List of usage examples for java.sql DriverManager getConnection

Introduction

In this page you can find the example usage for java.sql DriverManager getConnection.

Prototype

private static Connection getConnection(String url, java.util.Properties info, Class<?> caller)
            throws SQLException 

Source Link

Usage

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());
    }
}