Tuesday, November 14, 2017

If Else in Database

Input Format

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

The TRIANGLES table is described as follows:

Sample Input

Sample Output
Isosceles
Equilateral
Scalene
Not A Triangle

SOLUTION


SELECT CASE WHEN A+B>C THEN
           CASE WHEN A=B AND B=C AND A=C THEN 'Equilateral'
                WHEN A=B OR B=C OR A=C THEN 'Isosceles'
                WHEN A!=B AND B!=C AND C!=A THEN 'Scalene'
           END
           ELSE 'Not A Triangle'
       END
FROM TRIANGLES;

Friday, November 3, 2017

Implement own Connection Pool

Connection pooling is a technique used to improve performance in applications with dynamic database-driven content. Opening and closing database connections may not seem like a costly expense, but it can add up rather quickly. Let's assume it takes 5ms to establish a connection and 5ms to execute your query (completely made up numbers), 50% of the time is establishing the connection. Extend this to thousands or tens of thousands of requests and there is a lot of wasted network time. Connection pools are essentially a cache of open database connections. Once you open and use a database connection instead of closing it you add it back to the pool. When you go to fetch a new connection, if there is one available in the pool, it will use that connection instead of establishing another.
Why Use a Connection Pool?
  • Constantly opening and closing connections can be expensive. Cache and reuse.
  • When activity spikes you can limit the number of connections to the database. This will force code to block until a connection is available. This is especially helpful in distributed environments.
  • Split out common operations into multiple pools. For instance, you can have a pool designated for OLAP connections and a pool for OLTP connections each with different configurations.
Reducing the connection pool size alone, in the absence of any other change, decreased the response times of the application from ~100ms to ~2ms -- over 50x improvement.

But why?

We seem to have understood in other parts of computing recently that less is more. Why is it that with only 4-threads an nginx web-server can substantially out-perform an Apache web-server with 100 processes? Isn't it obvious if you think back to Computer Science 101?
Even a computer with one CPU core can "simultaneously" support dozens or hundreds of threads. But we all [should] know that this is merely a trick by the operating system though the magic of time-slicing. In reality, that single core can only execute one thread at a time; then the OS switches contexts and that core executes code for another thread, and so on. It is a basic Law of Computing that given a single CPU resource, executing A and B sequentially will always be faster than executing A and B "simultaneously" through time-slicing. Once the number of threads exceeds the number of CPU cores, you're going slower by adding more threads, not faster.
That is almost true...

package connectionPooling;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/** Copyright (c), ParasChawla javdadsalgo.blogspot.in */
public class PreparedStatementUseConnectionPooling {
    public static void main(String... arg) throws SQLException {

         ConnectionPool connectionPool = new ConnectionPool("oracle.jdbc.driver.OracleDriver",
                 "jdbc:oracle:thin:@localhost:1521:orcl", "ankit", "Oracle123", 5, 10, true);

         Connection con = connectionPool.getConnection();
         System.out.println("We have got connection from ConnectionPool class");

         PreparedStatement prepStmt = con.prepareStatement("select ID, NAME from EMPLOYEE");

         ResultSet rs = prepStmt.executeQuery();
         while (rs.next()) {
             System.out.print(rs.getInt("ID") + " ");
             System.out.println(rs.getString("NAME"));
         }

         if (rs != null)
             rs.close(); // close resultSet
         if (prepStmt != null)
             prepStmt.close(); // close PreparedStatement

         connectionPool.free(con);
         System.out.println("We have free/released connection to ConnectionPool class");
    }
}

/*
 * OUTPUT
 *
 * We have got connection from ConnectionPool class
 * 7 ankit
 * 8 rohit
 * We have free/released connection to ConnectionPool class
 *
 */

Implementation of own Connection Pool 

package connectionPooling;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;

public class ConnectionPool implements Runnable {
    private String driver, url, username, password;
    private int maxConnections;
    private boolean waitIfBusy;
    public Vector<Connection> availableConnections, busyConnections;
    private boolean connectionPending = false;

    public ConnectionPool() {
    }

    /**
     * Constructor of ConnectionPool class
     */
    public ConnectionPool(String driver, String url, String username, String password, int initialConnections,
             int maxConnections, boolean waitIfBusy) throws SQLException {
         this.driver = driver;
         this.url = url;
         this.username = username;
         this.password = password;
         this.maxConnections = maxConnections;
         this.waitIfBusy = waitIfBusy;
         if (initialConnections > maxConnections) {
             initialConnections = maxConnections;
         }
         availableConnections = new Vector<Connection>(initialConnections);
         busyConnections = new Vector<Connection>();
         for (int i = 0; i < initialConnections; i++) {
             availableConnections.addElement(makeNewConnection());
         }
    }

    /**
     * Method to return Connections
     */
    public synchronized Connection getConnection() throws SQLException {
         if (!availableConnections.isEmpty()) {
             Connection existingConnection = (Connection) availableConnections.lastElement();
             int lastIndex = availableConnections.size() - 1;
             availableConnections.removeElementAt(lastIndex);
             // If connection on available list is closed (e.g.,
             // it timed out), then remove it from available list
             // and repeat the process of obtaining a connection.
             // Also wake up threads that were waiting for a
             // connection because maxConnection limit was reached.
             if (existingConnection.isClosed()) {
                 notifyAll(); // Freed up a spot for anybody waiting
                 return (getConnection());
             } else {
                 busyConnections.addElement(existingConnection);
                 return (existingConnection);
             }
         } else {

             // Three possible cases:
             // 1) You haven't reached maxConnections limit. So
             // establish one in the background if there isn't
             // already one pending, then wait for
             // the next available connection (whether or not
             // it was the newly established one).
             // 2) You reached maxConnections limit and waitIfBusy
             // flag is false. Throw SQLException in such a case.
             // 3) You reached maxConnections limit and waitIfBusy
             // flag is true. Then do the same thing as in second
             // part of step 1: wait for next available connection.

             if ((totalConnections() < maxConnections) && !connectionPending) {
                 makeBackgroundConnection();
             } else if (!waitIfBusy) {
                 throw new SQLException("Connection limit reached");
             }
             // Wait for either a new connection to be established
             // (if you called makeBackgroundConnection) or for
             // an existing connection to be freed up.
             try {
                 wait();
             } catch (InterruptedException ie) {
             }
             // Someone freed up a connection, so try again.
             return (getConnection());
         }
    }

    /**
     * You can't just make a new connection in the foreground // when none are
     * available, since this can take several // seconds with a slow network
     * connection. Instead, // start a thread that establishes a new connection,
     * // then wait. You get woken up either when the new connection // is
     * established or if someone finishes with an existing // connection.
     */
    private void makeBackgroundConnection() {
         connectionPending = true;
         try {
             Thread connectThread = new Thread(this);
             connectThread.start();
         } catch (OutOfMemoryError oome) {
             // Give up on new connection
         }
    }

    public void run() {
         try {
             Connection connection = makeNewConnection();
             synchronized (this) {
                 availableConnections.addElement(connection);
                 connectionPending = false;
                 notifyAll();
             }
         } catch (Exception e) { // SQLException or OutOfMemory
             // Give up on new connection and wait for existing one
             // to free up.
             e.printStackTrace();
         }
    }

    /**
     * // Method explicitly makes a new connection. Called in // the foreground
     * when initializing the ConnectionPool, // and called in the background
     * when running.
     */
    private Connection makeNewConnection() throws SQLException {
         try {
             // Context ctx = new InitialContext();
             // Context envContext = (Context) ctx.lookup("java:/comp/env");
             // javax.sql.DataSource ds = (javax.sql.DataSource)
             // envContext.lookup ("jdbc/connectionPool");

             // Load database driver if not already loaded
             Class.forName(driver);
             // Establish network connection to database
             Connection connection = DriverManager.getConnection(url, username, password);
             // Connection connection = ds.getConnection();

             return (connection);
         } catch (Exception cnfe) {
             // Simplify try/catch blocks of people using this by
             // throwing only one exception type.
             cnfe.printStackTrace();
             throw new SQLException("ConnectionPool:: SQLException 
encountered:: " + cnfe.getMessage());
         }
    }

    /**
     * Method to free the Connections
     */
    public synchronized void free(Connection connection) {
         busyConnections.removeElement(connection);
         availableConnections.addElement(connection);
         // Wake up threads that are waiting for a connection
         notifyAll();
    }

    public synchronized int totalConnections() {
         return (availableConnections.size() + busyConnections.size());
    }

    /**
     * Method to Close all the connections. Use with caution: be sure no
     * connections are in use before calling. Note that you are not
     * <I>required</I> to call this when done with a ConnectionPool, since
     * connections are guaranteed to be closed when garbage collected. But this
     * method gives more control regarding when the connections are closed.
     */

    public synchronized void closeAllConnections() {
         closeConnections(availableConnections);
         availableConnections = new Vector<Connection>();
         closeConnections(busyConnections);
         busyConnections = new Vector<Connection>();
    }

    private void closeConnections(Vector<Connection> connections) {
         try {
             for (int i = 0; i < connections.size(); i++) {
                 Connection connection = (Connection) connections.elementAt(i);
                 if (!connection.isClosed()) {
                      connection.close();
                 }
             }
         } catch (SQLException sqle) {
             sqle.printStackTrace();
             // Ignore errors; garbage collect anyhow
         }
    }

    /**
     * String form of ConnectionPool class.
     */
    public synchronized String toString() {
         String info = "ConnectionPool(" + url + "," + username + ")" + ", available=" + availableConnections.size()
                 + ", busy=" + busyConnections.size() + ", max=" + maxConnections;
         return (info);
    }
}