Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Created on Apr 11, 2016
- *
- */
- package oracle;
- import java.sql.*;
- import java.util.Scanner;
- /**
- * Class Description: This is an exercise for practicing basics of database connection
- *
- * @author Yasaman
- * @version 1.0
- *
- */
- public class JDBCStuff {
- // Attributes
- private Connection conn;
- private Statement stmt;
- private ResultSet rs;
- public void initializeConnection(String username, String password) throws Exception{
- CredentialStore cd = new CredentialStore(username, password);
- DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
- String url = "jdbc:oracle:thin:@" + CredentialStore.HOSTNAME + ":" + CredentialStore.PORT; // + ":"
- // + CredentialStore.SID;
- // jdbc:oracle:thin:@HOSTNAME:PORT:SID
- conn = DriverManager.getConnection(url, cd.getUsername(), cd.getPassword());
- }
- public void close() {
- try {
- stmt.close();
- rs.close();
- conn.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- public boolean checkPayroll() {
- try {
- stmt = conn.createStatement();
- String query = "SELECT payroll FROM PAYROLL_PROCESSING";
- rs = stmt.executeQuery(query);
- while (rs.next()) {
- if (rs.getString("payroll").equals("Y"))
- return true;
- }
- } catch (Exception e) {
- }
- return false;
- }
- public boolean export(String alias, String directory) {
- try {
- // Set the directory alias
- String query = "CREATE OR REPLACE DIRECTORY ? AS ?";
- PreparedStatement pStat = conn.prepareStatement(query);
- pStat.setString(1, alias);
- pStat.setString(2, directory);
- int rowCount = pStat.executeUpdate();
- System.out.println("row Count = " + rowCount);
- pStat.close();
- // Populate the delimited file
- return true;
- } catch (SQLException e) {
- }
- return false;
- }
- //----------------------------------------------------------
- //-------------- QUERY WITH STATEMENT-------------------
- //----------------------------------------------------------
- // public void selectUser() {
- // try {
- // stmt = conn.createStatement();
- // String query = "SELECT * FROM CUSTOMERS";
- // rs = stmt.executeQuery(query);
- // while (rs.next()) {
- // System.out.println(rs.getString("FIRSTNAME") + " " + rs.getString("LASTNAME"));
- // }
- // } catch (SQLException e) {
- // e.printStackTrace();
- // }
- // }
- //
- // public void insertUser() {
- // try {
- // stmt = conn.createStatement();
- // String insert = "INSERT INTO CUSTOMERS (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)"
- // + "values (1021, 'WHITE', 'NORA', '123 WOW STREET', 'CALGARY', 'AB', '76138', '','SE', 'wow@wow.com')";
- // int rowCount = stmt.executeUpdate(insert);
- // System.out.println("row Count = " + rowCount);
- // } catch (SQLException e) {
- // e.printStackTrace();
- // }
- // }
- //
- // public void deleteUser() {
- // try {
- // stmt = conn.createStatement();
- // String insert = "DELETE FROM CUSTOMERS WHERE FIRSTNAME = 'NORA' AND LASTNAME = 'WHITE'";
- // int rowCount = stmt.executeUpdate(insert);
- // System.out.println("row Count = " + rowCount);
- // } catch (SQLException e) {
- // e.printStackTrace();
- // }
- // }
- //
- // public void validateLogin(String username, String password) {
- // try {
- // stmt = conn.createStatement();
- // String query = "SELECT * FROM users WHERE username = '" + username + "' and password ='" + password + "'";
- // rs = stmt.executeQuery(query);
- // if (rs.next()) {
- // System.out.println("User is logged in");
- // } else {
- // System.out.println("Invalid Username and Password");
- // }
- // } catch (SQLException e) {
- // e.printStackTrace();
- // }
- //
- // }
- //----------------------------------------------------------
- //---------------PREPARED STATEMENT---------------------
- //----------------------------------------------------------
- // public void selectUserPreparedStatement() {
- // try {
- // String query = "SELECT * FROM CUSTOMERS WHERE FIRSTNAME = ? and LASTNAME = ?";
- // PreparedStatement pStat = conn.prepareStatement(query);
- // pStat.setString(1, "NORA");
- // pStat.setString(2, "WHITE");
- // rs = pStat.executeQuery();
- // while (rs.next()) {
- // System.out.println(rs.getString("CUSTOMER#") + " " + rs.getString("FIRSTNAME") + " " + rs.getString("LASTNAME"));
- // }
- // pStat.close();
- // } catch (SQLException e) {
- // e.printStackTrace();
- // }
- // }
- //
- // public void insertUserPreparedStatement() {
- // try {
- // String query = "INSERT INTO CUSTOMERS (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)"
- // + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
- // PreparedStatement pStat = conn.prepareStatement(query);
- // pStat.setInt(1, 1023);
- // pStat.setString(2, "WHITE");
- // pStat.setString(3, "NORA");
- // pStat.setString(4, "123 WOW STREET");
- // pStat.setString(5, "CALGARY");
- // pStat.setString(6, "AB");
- // pStat.setString(7, "76138");
- // pStat.setString(8, "");
- // pStat.setString(9, "SE");
- // pStat.setString(10, "wow@wow.com");
- // int rowCount = pStat.executeUpdate();
- // System.out.println("row Count = " + rowCount);
- // pStat.close();
- // } catch (SQLException e) {
- // e.printStackTrace();
- // }
- // }
- //
- // public void validateLoginPreparedStatement(String username, String password) {
- // try {
- // stmt = conn.createStatement();
- // String query = "SELECT * FROM users where username = ? and password =?";
- // PreparedStatement pStat = conn.prepareStatement(query);
- // pStat.setString(1, username);
- // pStat.setString(2, password);
- // rs = pStat.executeQuery();
- // if (rs.next()) {
- // System.out.println("User is logged in");
- // } else {
- // System.out.println("Invalid Username and Password");
- // }
- // } catch (SQLException e) {
- // e.printStackTrace();
- // }
- //
- // }
- //
- // private void simulateLoginPage() {
- // Scanner sc = new Scanner(System.in);
- // String username = "";
- // String password = "";
- // System.out.println("Please enter your username:");
- // username = sc.nextLine();
- // System.out.println("Please enter your password:");
- // password = sc.nextLine();
- // validateLogin(username, password);
- //// validateLoginPreparedStatement(username, password);
- // sc.close();
- // }
- // public static void main(String[] args) {
- // MyJDBCApp app = new MyJDBCApp();
- // app.initializeConnection();
- //// app.insertUser();
- //// app.selectUser();
- //// app.deleteUser();
- // app.selectUserPreparedStatement();
- //// app.insertUserPreparedStatement();
- //// app.simulateLoginPage(); // invalidUser� or 1=1 --
- // }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement