Example usage for java.sql Statement executeQuery

List of usage examples for java.sql Statement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. executeQuery

Introduction

In this page you can find the example usage for java.sql Statement executeQuery.

Prototype

ResultSet executeQuery(String sql) throws SQLException;

Source Link

Document

Executes the given SQL statement, which returns a single ResultSet object.

Usage

From source file:TransactionPairs.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con = null;//from ww w. j av  a2 s  . c om
    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:TransactionPairs.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con = null;/*from w w w .ja v  a 2s  .  co m*/
    Statement stmt;
    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:InsertStores.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;/*ww  w.  ja  v a2s .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();
        con.setAutoCommit(false);

        String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, "
                + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore1);

        String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, "
                + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore2);

        String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, "
                + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore3);

        String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, "
                + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore4);

        String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, "
                + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))";

        stmt.addBatch(insertStore5);

        int[] updateCounts = stmt.executeBatch();

        ResultSet rs = stmt.executeQuery("SELECT * FROM STORES");

        System.out.println("Table STORES after insertion:");
        System.out.println("STORE_NO  LOCATION          COF_TYPE     MGR");
        while (rs.next()) {
            int storeNo = rs.getInt("STORE_NO");
            Struct location = (Struct) rs.getObject("LOCATION");
            Object[] locAttrs = location.getAttributes();
            Array coffeeTypes = rs.getArray("COF_TYPE");
            String[] cofTypes = (String[]) coffeeTypes.getArray();

            Ref managerRef = rs.getRef("MGR");
            PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?");
            pstmt.setRef(1, managerRef);
            ResultSet rs2 = pstmt.executeQuery();
            rs2.next();
            Struct manager = (Struct) rs2.getObject("MANAGER");
            Object[] manAttrs = manager.getAttributes();

            System.out.print(storeNo + "   ");
            System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + "  "
                    + locAttrs[4] + " ");
            for (int i = 0; i < cofTypes.length; i++)
                System.out.print(cofTypes[i] + " ");
            System.out.println(manAttrs[1] + ", " + manAttrs[2]);

            rs2.close();
            pstmt.close();
        }

        rs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

From source file:com.mycompany.mavenproject4.NewMain.java

/**
 * @param args the command line arguments
 *///  w ww.ja  va  2  s  . co  m
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;

    }

}

From source file:com.mycompany.mavenproject4.web.java

/**
 * @param args the command line arguments
 *///w  w  w.  j av a 2 s . c o  m
public static void main(String[] args) {
    System.out.println("Enter your choice do you want to work with \n 1.PostGreSQL \n 2.Redis \n 3.Mongodb");
    InputStreamReader IORdatabase = new InputStreamReader(System.in);
    BufferedReader BIOdatabase = new BufferedReader(IORdatabase);
    String Str1 = null;
    try {
        Str1 = BIOdatabase.readLine();
    } catch (Exception E7) {
        System.out.println(E7.getMessage());
    }

    // loading data from the CSV file 

    CsvReader data = null;

    try {
        data = new CsvReader("\\data\\Consumer_Complaints.csv");
    } catch (FileNotFoundException EB) {
        System.out.println(EB.getMessage());
    }
    int noofcolumn = 5;
    int noofrows = 100;
    int loops = 0;

    try {
        data = new CsvReader("\\data\\Consumer_Complaints.csv");
    } catch (FileNotFoundException E) {
        System.out.println(E.getMessage());
    }

    String[][] Dataarray = new String[noofrows][noofcolumn];
    try {
        while (data.readRecord()) {
            String v;
            String[] x;
            v = data.getRawRecord();
            x = v.split(",");
            for (int j = 0; j < noofcolumn; j++) {
                String value = null;
                int z = j;
                value = x[z];
                try {
                    Dataarray[loops][j] = value;
                } catch (Exception E) {
                    System.out.println(E.getMessage());
                }
                // System.out.println(Dataarray[iloop][j]);
            }
            loops = loops + 1;
        }
    } catch (IOException Em) {
        System.out.println(Em.getMessage());
    }

    data.close();

    // connection to Database 
    switch (Str1) {
    // 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 = "ahkyjdavhedkzg";
        String password = "2f7c3_MBJbIy1uJsFyn7zebkhY";
        String dbUrl = "jdbc:postgresql://ec2-54-83-199-54.compute-1.amazonaws.com:5432/d2l6hq915lp9vi?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 Webdata (Id,Product,state,Submitted,Complaintid) values (' "+ Dataarray[i][0]+" ',' "+ Dataarray[i][1]+" ',' "+ Dataarray[i][2]+" ',' "+ Dataarray[i][3]+" ',' "+ Dataarray[i][4]+" ')";
         Stmt.executeUpdate(Connection_String);
                  Conn.close();
        }
        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 Webdata;";
                ResultSet objRS = Stmt.executeQuery(Connection_String);
                while (objRS.next()) {
                    System.out.println(" ID: " + objRS.getInt("ID"));
                    System.out.println("Product Name: " + objRS.getString("Product"));
                    System.out.println("Which state: " + objRS.getString("State"));
                    System.out.println("Sumbitted through: " + objRS.getString("Submitted"));
                    System.out.println("Complain Number: " + objRS.getString("Complaintid"));
                    Conn.close();
                }

            } catch (Exception E2) {
                System.out.println(E2.getMessage());
            }

            break;

        case "2":

            System.out.println("Enter Id(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 Webdata where Id=" + key + ";";
                ResultSet objRS = Stmt.executeQuery(Connection_String);
                while (objRS.next()) {
                    //System.out.println(" ID: " + objRS.getInt("ID"));
                    System.out.println("Product Name: " + objRS.getString("Product"));
                    System.out.println("Which state: " + objRS.getString("State"));
                    System.out.println("Sumbitted through: " + objRS.getString("Submitted"));
                    System.out.println("Complain Number: " + objRS.getString("Complaintid"));
                    Conn.close();
                }

            } catch (Exception E2) {
                System.out.println(E2.getMessage());
            }
            break;

        case "3":
            //String Name=null;
            System.out.println("Enter Complaintid to find the record");

            // Scanner input = new Scanner(System.in);
            //
            int Number = 0;
            try {
                InputStreamReader objname = new InputStreamReader(System.in);
                BufferedReader objbufname = new BufferedReader(objname);

                Number = Integer.parseInt(objbufname.readLine());
            } catch (Exception E10) {
                System.out.println(E10.getMessage());
            }
            //System.out.println(Name);
            try {
                Conn = DriverManager.getConnection(dbUrl, username, password);
                Stmt = Conn.createStatement();
                String Connection_String = " Select * from Webdata where complaintid=" + Number + ";";
                //2
                System.out.println(Connection_String);
                ResultSet objRS = Stmt.executeQuery(Connection_String);
                while (objRS.next()) {
                    int id = objRS.getInt("id");
                    System.out.println(" ID: " + id);
                    System.out.println("Product Name: " + objRS.getString("Product"));
                    String state = objRS.getString("state");
                    System.out.println("Which state: " + state);
                    String Submitted = objRS.getString("submitted");
                    System.out.println("Sumbitted through: " + Submitted);
                    String Complaintid = objRS.getString("complaintid");
                    System.out.println("Complain Number: " + Complaintid);

                }
                Conn.close();
            } 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-13274.us-east-1-2.1.ec2.garantiadata.com", 13274);
        jedis.auth("rfJ8OLjlv9NjRfry");
        System.out.println("Connected to Redis Database");

        // Storing values in the database 

        /* 
        for(int i=0;i<Length;i++)
        { 
         //Store data in redis 
            int j=i+1;
        jedis.hset("Record:" + j , "Product", Dataarray[i][1]);
        jedis.hset("Record:" + j , "State ", Dataarray[i][2]);
        jedis.hset("Record:" + j , "Submitted", Dataarray[i][3]);
        jedis.hset("Record:" + j , "Complaintid", Dataarray[i][4]);
                
        }
        */
        System.out.println(
                "Search by \n 11.Get records through primary key \n 22.Get Records through Complaintid \n 33.Display ");
        InputStreamReader objkey = new InputStreamReader(System.in);
        BufferedReader objbufkey = new BufferedReader(objkey);
        String str2 = null;
        try {
            str2 = objbufkey.readLine();
        } catch (IOException E) {
            System.out.println(E.getMessage());
        }
        switch (str2) {
        case "11":
            System.out.print("Enter 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("Record:" + Integer.parseInt(ID1));
            for (Map.Entry<String, String> entry : properties.entrySet()) {
                System.out.println("Product:" + jedis.hget("Record:" + Integer.parseInt(ID1), "Product"));
                System.out.println("State:" + jedis.hget("Record:" + Integer.parseInt(ID1), "State"));
                System.out.println("Submitted:" + jedis.hget("Record:" + Integer.parseInt(ID1), "Submitted"));
                System.out
                        .println("Complaintid:" + jedis.hget("Record:" + Integer.parseInt(ID1), "Complaintid"));
            }
            break;

        case "22":
            System.out.print(" Enter Complaintid  : ");
            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("Record:" + i);
                for (Map.Entry<String, String> entry : properties3.entrySet()) {
                    String value = entry.getValue();
                    if (entry.getValue().equals(ID2)) {
                        System.out
                                .println("Product:" + jedis.hget("Record:" + Integer.parseInt(ID2), "Product"));
                        System.out.println("State:" + jedis.hget("Record:" + Integer.parseInt(ID2), "State"));
                        System.out.println(
                                "Submitted:" + jedis.hget("Record:" + Integer.parseInt(ID2), "Submitted"));
                        System.out.println(
                                "Complaintid:" + jedis.hget("Record:" + Integer.parseInt(ID2), "Complaintid"));
                    }

                }
            }

            break;

        case "33":
            for (int i = 1; i < 21; i++) {
                Map<String, String> properties3 = jedis.hgetAll("Record:" + i);
                for (Map.Entry<String, String> entry : properties3.entrySet()) {

                    System.out.println("Product:" + jedis.hget("Record:" + i, "Product"));
                    System.out.println("State:" + jedis.hget("Record:" + i, "State"));
                    System.out.println("Submitted:" + jedis.hget("Record:" + i, "Submitted"));
                    System.out.println("Complaintid:" + jedis.hget("Record:" + i, "Complaintid"));

                }
            }
            break;
        }

        break;

    // mongo db 

    case "3":
        MongoClient mongo = new MongoClient(new MongoClientURI(
                "mongodb://naikhpratik:naikhpratik@ds053964.mongolab.com:53964/heroku_6t7n587f"));
        DB db;
        db = mongo.getDB("heroku_6t7n587f");
        // storing values in the database 
        /*for(int i=0;i<100;i++)
         {
             BasicDBObject document = new BasicDBObject();
            document.put("Id", i+1);
            document.put("Product", Dataarray[i][1]);
            document.put("State", Dataarray[i][2]);    
            document.put("Submitted", Dataarray[i][3]);    
            document.put("Complaintid", Dataarray[i][4]); 
            db.getCollection("Naiknaik").insert(document);
                  
         }*/
        System.out.println("Search by \n 1.Enter Primary key \n 2.Enter Complaintid \n 3.Display");
        InputStreamReader objkey6 = new InputStreamReader(System.in);
        BufferedReader objbufkey6 = new BufferedReader(objkey6);
        String str3 = null;
        try {
            str3 = objbufkey6.readLine();
        } catch (IOException E) {
            System.out.println(E.getMessage());
        }
        switch (str3) {
        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("Naiknaik").find(inQuery);
            while (cursor.hasNext()) {
                // System.out.println(cursor.next());
                System.out.println(cursor.next());
            }
            break;
        case "2":
            System.out.println("Enter the Product 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("Product", Name);
            DBCursor cursor1 = db.getCollection("Naiknaik").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("Naiknaik").find(inQuerya);
                while (cursora.hasNext()) {
                    // System.out.println(cursor.next());
                    System.out.println(cursora.next());
                }
            }
            break;
        }
        break;

    }

}

From source file:MainClass.java

public static void readDatabase() {
    String data = "jdbc:derby:presidents";
    try {// w w  w. j a v  a 2s  . co  m
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        Connection conn = DriverManager.getConnection(data, "", "");
        Statement st = conn.createStatement();
        ResultSet rec = st.executeQuery("SELECT * FROM contacts ORDER BY name");
        while (rec.next()) {
            System.out.println(
                    rec.getString("name") + "\n" + rec.getString("address1") + "\n" + rec.getString("address2")
                            + "\n" + rec.getString("phone") + "\n" + rec.getString("email") + "\n");
        }
        st.close();
    } catch (Exception e) {
        System.out.println("Error - " + e.toString());
    }
}

From source file:com.dbconnection.DataSourceConnection.java

private static String query(Connection connection) throws SQLException {

    String query = "";
    Statement statement = connection.createStatement();

    ResultSet resultSet = statement.executeQuery("SELECT * FROM BOOK");

    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String title = resultSet.getString("title");

        query = query + id + "\t" + title + "\n";
    }/*from  ww  w  .ja va2s  .c  o m*/
    return query;
}

From source file:Main.java

/**
 * /*from   w  ww .  j av  a2s .c om*/
 * @param conn
 * @param table
 * @param c_name
 */
public static void updateContentVersion(Connection conn, String table, String c_name) throws Exception {
    ResultSet rs;
    Statement s;
    int v;

    s = conn.createStatement();
    rs = s.executeQuery("SELECT c_version FROM " + table + " WHERE c_name = '" + c_name + "'");
    v = 1;

    if (rs.next()) {
        v = rs.getInt(1);
        v++;
    }

    rs.close();

    s.executeUpdate("UPDATE " + table + " SET c_version = " + v + " WHERE c_name = '" + c_name + "'");
    s.close();
}

From source file:Main.java

private static List<Person> findAll(Connection conn) throws SQLException {
    List<Person> rows = new ArrayList<Person>();
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery("select * from people;");
    while (rs.next()) {
        rows.add(new Person(rs.getString("name"), rs.getString("occupation")));
    }/*w  w w  . j a  va  2  s.c  o m*/
    close(stat);
    close(rs);
    return rows;
}

From source file:com.mum.edu.cs472.DbConnection.java

public static ResultSet getWords(String word) throws SQLException {
    try {// w  w  w  .  ja v  a 2 s  .c om
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/entries", "root", "root");

        //here sonoo is database name, root is username and password  
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM entries where lower(word)=Lower('" + word + "');");

        return rs;

    } catch (Exception e) {
        System.out.println(e);
    }

    return null;
}