connection usage

  public void createTable() throws SQLException {
    String createString =
      "create table COFFEES " + "(COF_NAME varchar(32) NOT NULL, " +
      "SUP_ID int NOT NULL, " + "PRICE numeric(10,2) NOT NULL, " +
      "SALES integer NOT NULL, " + "TOTAL integer NOT NULL, " +
      "PRIMARY KEY (COF_NAME), " +
      "FOREIGN KEY (SUP_ID) REFERENCES SUPPLIERS (SUP_ID))";
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate(createString);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public void populateTable() throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian', 00101, 7.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast', 00049, 8.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Espresso', 00150, 9.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian_Decaf', 00101, 8.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }


  public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString =
      "update COFFEES " + "set SALES = ? where COF_NAME = ?";

    String updateStatement =
      "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?";

    try {
      con.setAutoCommit(false);
      updateSales = con.prepareStatement(updateString);
      updateTotal = con.prepareStatement(updateStatement);

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch (SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    } finally {
      if (updateSales != null) { updateSales.close(); }
      if (updateTotal != null) { updateTotal.close(); }
      con.setAutoCommit(true);
    }
  }

  public void modifyPrices(float percentage) throws SQLException {
    Statement stmt = null;
    try {
      stmt =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

      while (uprs.next()) {
        float f = uprs.getFloat("PRICE");
        uprs.updateFloat("PRICE", f * percentage);
        uprs.updateRow();
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }


  public void modifyPricesByPercentage(String coffeeName, float priceModifier,
                                       float maximumPrice) throws SQLException {
    con.setAutoCommit(false);

    Statement getPrice = null;
    Statement updatePrice = null;
    ResultSet rs = null;
    String query =
      "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE COF_NAME = '" +
      coffeeName + "'";

    try {
      Savepoint save1 = con.setSavepoint();
      getPrice =
          con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      updatePrice = con.createStatement();

      if (!getPrice.execute(query)) {
        System.out.println("Could not find entry for coffee named " +
                           coffeeName);
      } else {
        rs = getPrice.getResultSet();
        rs.first();
        float oldPrice = rs.getFloat("PRICE");
        float newPrice = oldPrice + (oldPrice * priceModifier);
        System.out.println("Old price of " + coffeeName + " is " + oldPrice);
        System.out.println("New price of " + coffeeName + " is " + newPrice);
        System.out.println("Performing update...");
        updatePrice.executeUpdate("UPDATE COFFEES SET PRICE = " + newPrice +
                                  " WHERE COF_NAME = '" + coffeeName + "'");
        System.out.println("\nCOFFEES table after update:");
        CoffeesTable.viewTable(con);
        if (newPrice > maximumPrice) {
          System.out.println("\nThe new price, " + newPrice +
                             ", is greater than the maximum " + "price, " +
                             maximumPrice +
                             ". Rolling back the transaction...");
          con.rollback(save1);
          System.out.println("\nCOFFEES table after rollback:");
          CoffeesTable.viewTable(con);
        }
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (getPrice != null) { getPrice.close(); }
      if (updatePrice != null) { updatePrice.close(); }
      con.setAutoCommit(true);
    }
  }


  public void insertRow(String coffeeName, int supplierID, float price,
                        int sales, int total) throws SQLException {
    Statement stmt = null;
    try {
      stmt =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

      uprs.moveToInsertRow();

      uprs.updateString("COF_NAME", coffeeName);
      uprs.updateInt("SUP_ID", supplierID);
      uprs.updateFloat("PRICE", price);
      uprs.updateInt("SALES", sales);
      uprs.updateInt("TOTAL", total);

      uprs.insertRow();
      uprs.beforeFirst();

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public void batchUpdate() throws SQLException {

    Statement stmt = null;
    try {

      this.con.setAutoCommit(false);
      stmt = this.con.createStatement();

      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Amaretto', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Hazelnut', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

      int[] updateCounts = stmt.executeBatch();
      this.con.commit();

    } catch (BatchUpdateException b) {
      JDBCTutorialUtilities.printBatchUpdateException(b);
    } catch (SQLException ex) {
      JDBCTutorialUtilities.printSQLException(ex);
    } finally {
      if (stmt != null) { stmt.close(); }
      this.con.setAutoCommit(true);
    }
  }
 
  public static void viewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);

      while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
        System.out.println(coffeeName + ", " + supplierID + ", " + price +
                           ", " + sales + ", " + total);
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public static void alternateViewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString(1);
        int supplierID = rs.getInt(2);
        float price = rs.getFloat(3);
        int sales = rs.getInt(4);
        int total = rs.getInt(5);
        System.out.println(coffeeName + ", " + supplierID + ", " + price +
                           ", " + sales + ", " + total);
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }


Reference-1 : http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

JDBC - PreparedStatement usage

PreparedStatement pstmt = null;
try {
    // Prepare a statement to insert a record
    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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    pstmt = connection.prepareStatement(sql);

    // Set the values
    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()));

    // Set the ascii stream
    File file = new File("infilename1");
    FileInputStream is = new FileInputStream(file);
    pstmt.setAsciiStream(13, is, (int)file.length());

    // Set the binary stream
    file = new File("infilename2");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(14, is, (int)file.length());

    // Set the blob
    file = new File("infilename3");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(15, is, (int)file.length());

    // Insert the row
    pstmt.executeUpdate();
} catch (SQLException e) {
} catch (FileNotFoundException e) {
}
finally {
    try {
        if (pstmt != null)
            pstmt.close();
        if (connection != null)
            connection.close();
    }
    catch (Exception ignore) {}
}


  public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString =
      "update COFFEES " + "set SALES = ? where COF_NAME = ?";

    String updateStatement =
      "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?";

    try {
      con.setAutoCommit(false);
      updateSales = con.prepareStatement(updateString);
      updateTotal = con.prepareStatement(updateStatement);

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch (SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    } finally {
      if (updateSales != null) { updateSales.close(); }
      if (updateTotal != null) { updateTotal.close(); }
      con.setAutoCommit(true);
    }
  }

Reference-1 : http://www.exampledepot.com/egs/java.sql/InsertPs.html
Reference-2 : http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

Advanced SQL Queries

For Tables Defined in : http://dbms101.blogspot.com/2012/01/tutorial-db.html

1. Get list of customers who have not placed an order yet.
SQL Query:
-------------
select *
from customers left outer join orders on customers.customer_id = orders.customer_id
where orders.order_id is NULL ;

1    Robin Smith    100 Oracle Plaza    Sunnyvale    CA           
2    John Lincoln    200 Oracle Plaza    Sunnyvale    CA           
4    David Crow    400 Oracle Plaza    Sunnyvale    CA           
5    Test User1    500 Oracle Plaza    Santa Clara    CA           
7    Test User3    700 Oracle Plaza    Palo Alto    CA           
8    Test User4    800 Oracle Plaza    Palo Alto    CA          

Analysis:
---------
When you just do
select *
from customers left outer join orders on customers.customer_id = orders.customer_id;
- We get all customer rows.
By adding condition where orders.order_id is NULL, we get the solution.

REFERENCES : http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=27

How to write a SQL - Query

REFERENCE : http://www.ifadey.com/2010/11/best-way-to-write-sql-query/

 

Clause Order

Thinking and writing SQL query clauses in particular order (in which database engine executes the query) matters a lot because problem (getting required data) gets much simplified by creating query in such an order. Actually the problem is automatically divided and simplified when writing query in execution order. Let’s take a look how the database engine executes the query.

1. The Source of Data

The first clause which gets executed is FROM clause. It means the first step when you write a query is to know FROM which table(s) you want to get the data. If the required data is in multiple tables, then think about the JOINs in first step. When FROM clause gets executed, all rows are fetched from the tables specified. Let’s call them Individual Rows.
Click here to read more about SQL JOINs.

2. Filter Individual Rows

Second step is to think about WHERE clause which is used to filter Individual Rows on particular condition(s).

3. Grouping of Individual Rows

GROUP BY is the third clause which gets executed. So think about it in the third step when writing a query. This clause group Individual Rows (fetched using FROM and filtered using WHERE) on the basis and order of columns specified.
Click here to read more about SQL GROUP BY.

4. Filter Group Rows

HAVING clause is used to filter Group Rows based on the condition(s) specified in it. It’s fourth one which gets executed.

5. Filter Columns

The fifth clause which gets executed is SELECT. This clause is used to filter columns which are not required.

6. Ordering of Rows

The final step in execution of query is to order the rows based on specified column(s).

DataSource Connection for Pooling - XE

Data Source for Pooled Connection and Distributed Transactions.

Tutorial-1: http://www.java2s.com/Code/Java/Database-SQL-JDBC/OracleConnectionPoolDataSource.htm
Tutorial-2: http://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html
Tutorial-3:  http://docs.oracle.com/cd/B14117_01/java.101/b10979/urls.htm
Tutorial-4: http://www.orafaq.com/wiki/JDBC



package com.struts1.tutorials.datasource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.PooledConnection;

import oracle.jdbc.pool.OracleConnectionPoolDataSource;

public class DataSourceConnPoolTest {
   
    private static final String SQL_QUERY1 = "SELECT * fROM customers";
    private static final String QUERY_FIELD1 = "CUSTOMER_NAME";
    private static final String SQL_QUERY2 = "SELECT count(*) FROM v$session WHERE username = 'SYS'";
    public static void main(String[] args) {
        PooledConnection pc_1 = null;
        PooledConnection pc_2 = null;
        Connection conn_1 = null;
        Connection conn_2 = null;
        Connection conn_3 = null;
        Statement stmt = null;
        ResultSet rset = null;
       
        try {           
            OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
            ocpds.setURL("jdbc:oracle:thin:@localhost:1521:XE");
            ocpds.setUser("lakshman01");
            ocpds.setPassword("passwd");
           
            pc_1 = ocpds.getPooledConnection();
            conn_1 = pc_1.getConnection();
            stmt = conn_1.createStatement();
           
            rset = stmt.executeQuery(SQL_QUERY1);
            rset.next();
            String msg = "Data = ";
            System.out.println(msg + "pc-1 - conn_1: " + rset.getString(QUERY_FIELD1));
           
            if (stmt != null) {
                stmt.close();
            }
            if (rset != null) {
                rset.close();
            }
           
           
            conn_2 = pc_1.getConnection();
            stmt = conn_2.createStatement();
            rset = stmt.executeQuery(SQL_QUERY1);
            rset.next();
           
            System.out.println(msg + "pc-1 - conn_2: " + rset.getString(QUERY_FIELD1));
            if (stmt != null) {
                stmt.close();
            }
            if (rset != null) {
                rset.close();
            }

            pc_2 = ocpds.getPooledConnection();
            conn_3 = pc_2.getConnection();
            stmt = conn_3.createStatement();
           
            rset = stmt.executeQuery(SQL_QUERY1);
            rset.next();
            System.out.println(msg + "pc_2 - conn_3: " + rset.getString(QUERY_FIELD1));           
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            }
            catch (Exception ignore) {}
            try {
                if (rset != null) {
                    rset.close();
                }               
            }
            catch (Exception ignore) {}
            try {
                if (conn_1 != null)
                    conn_1.close();
            }
            catch (Exception ignore) {}
            try {
                if (conn_2 != null)
                    conn_2.close();
            }
            catch (Exception ignore) {}
            try {
                if (conn_3 != null)
                    conn_3.close();
            }
            catch (Exception ignore) {}
            try {
                if (pc_1 != null)
                    pc_1.close();
            }
            catch (Exception ignore) {}
            try {
                if (pc_2 != null)
                    pc_2.close();
            }
            catch (Exception ignore) {}           
        }
    }
}


Sample Code to use OracleDataSource instead of old DriverManager.
(Remember this is not pooled.)
************


package com.struts1.tutorials.datasource;

import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.*;

import oracle.jdbc.pool.OracleDataSource;

public class DataSourceTest {

    private static final String SQL_QUERY1 = "SELECT * fROM customers";
   
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
       
        try {
            OracleDataSource ods = new OracleDataSource();
            ods.setDriverType("thin");           
            ods.setDatabaseName("XE");
            ods.setServerName("localhost");
            ods.setPortNumber(1521);
            ods.setUser("lakshman01");
            ods.setPassword("passwd");
           
/*           
 *             Not an EJB - App. So don't bother about context
            Context ctx = new InitialContext();
            ctx.bind("jdbc/tutorialdb", ods);           
            OracleDataSource odsconn = (OracleDataSource)ctx.lookup("jdbc/sampledb");
*/

            conn = ods.getConnection();
           
            stmt = conn.createStatement();
            rset = stmt.executeQuery(SQL_QUERY1);
            while (rset.next()) {
                System.out.println ("Customer ID = " + rset.getInt("CUSTOMER_ID"));
                System.out.println ("Customer Name = " + rset.getString("CUSTOMER_NAME"));
            }                       

        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            try {
                if (rset != null)
                    rset.close();
            }
            catch (Exception ignore) {}
            try {
                if (stmt != null)
                    stmt.close();
            }
            catch (Exception ignore) {}
            try {
                if (conn != null)
                    conn.close();
            }
            catch (Exception ignore) {}           
        }       
    }
}

How to get Oracle DB name using SQL

select ora_database_name from dual

Oracle XE Connection - Sample

package com.struts1.tutorials.ejb.orders;

import java.sql.*;

public class TestDB {
   
    private static final String SQL_QUERY1 = "SELECT * FROM customers";
   
    private static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver";
    private static final String DB_URL = "jdbc:oracle:thin:@//localhost:1521/xe";   
    private static final String DB_USR = "lakshman01";
    private static final String DB_PWD = "passwd";
   
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
       
        try {
            Class.forName (JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, DB_USR, DB_PWD);
           
            stmt = conn.createStatement();
            rset = stmt.executeQuery(SQL_QUERY1);
            while (rset.next()) {
                System.out.println ("Customer ID = " + rset.getInt("CUSTOMER_ID"));
                System.out.println ("Customer Name = " + rset.getString("CUSTOMER_NAME"));
            }                       
        }
        catch (Exception ex) {
            ex.printStackTrace();           
        }
        finally {
            try {
                if (rset != null)
                    rset.close();
            } catch (Exception ignore) {}
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception ignore) {}
            try {
                if (conn != null)
                    conn.close();
            } catch (Exception ignore) {}           
        }       
    }
}