Advertisement
Guest User

Untitled

a guest
Dec 5th, 2016
120
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 6.59 KB | None | 0 0
  1. /**
  2.  * Created on Apr 11, 2016
  3.  *
  4.  */
  5. package oracle;
  6.  
  7. import java.sql.*;
  8. import java.util.Scanner;
  9.  
  10. /**
  11.  * Class Description: This is an exercise for practicing basics of database connection
  12.  *
  13.  * @author Yasaman
  14.  * @version 1.0
  15.  *
  16.  */
  17.  
  18. public class JDBCStuff {
  19.  
  20.     // Attributes
  21.     private Connection conn;
  22.     private Statement stmt;
  23.     private ResultSet rs;
  24.  
  25.     public void initializeConnection(String username, String password) throws Exception{
  26.         CredentialStore cd = new CredentialStore(username, password);
  27.         DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
  28.         String url = "jdbc:oracle:thin:@" + CredentialStore.HOSTNAME + ":" + CredentialStore.PORT; // + ":"
  29. //                  + CredentialStore.SID;
  30. //           jdbc:oracle:thin:@HOSTNAME:PORT:SID
  31.         conn = DriverManager.getConnection(url, cd.getUsername(), cd.getPassword());
  32.     }
  33.    
  34.     public void close() {
  35.         try {
  36.             stmt.close();
  37.             rs.close();
  38.             conn.close();
  39.         } catch (SQLException e) {
  40.             e.printStackTrace();
  41.         }
  42.     }
  43.    
  44.     public boolean checkPayroll() {
  45.         try {
  46.             stmt = conn.createStatement();
  47.             String query = "SELECT payroll FROM PAYROLL_PROCESSING";
  48.             rs = stmt.executeQuery(query);
  49.             while (rs.next()) {
  50.                 if (rs.getString("payroll").equals("Y"))
  51.                     return true;
  52.             }
  53.         } catch (Exception e) {
  54.  
  55.         }
  56.         return false;
  57.     }
  58.    
  59.     public boolean export(String alias, String directory) {
  60.         try {
  61.             // Set the directory alias
  62.             String query = "CREATE OR REPLACE DIRECTORY ? AS ?";
  63.             PreparedStatement pStat = conn.prepareStatement(query);
  64.             pStat.setString(1, alias);
  65.             pStat.setString(2, directory); 
  66.             int rowCount = pStat.executeUpdate();
  67.             System.out.println("row Count = " + rowCount);
  68.             pStat.close();
  69.            
  70.             // Populate the delimited file
  71.             return true;
  72.         } catch (SQLException e) {
  73.            
  74.         }
  75.         return false;
  76.     }
  77.  
  78. //----------------------------------------------------------
  79.     //-------------- QUERY WITH STATEMENT-------------------
  80. //----------------------------------------------------------
  81. //  public void selectUser() {
  82. //      try {
  83. //          stmt = conn.createStatement();
  84. //          String query = "SELECT * FROM CUSTOMERS";
  85. //          rs = stmt.executeQuery(query);
  86. //          while (rs.next()) {
  87. //              System.out.println(rs.getString("FIRSTNAME") + " " + rs.getString("LASTNAME"));
  88. //          }
  89. //      } catch (SQLException e) {
  90. //          e.printStackTrace();
  91. //      }
  92. //  }
  93. //
  94. //  public void insertUser() {
  95. //      try {
  96. //          stmt = conn.createStatement();
  97. //          String insert = "INSERT INTO CUSTOMERS (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)"
  98. //                  + "values (1021, 'WHITE', 'NORA', '123 WOW STREET', 'CALGARY', 'AB', '76138', '','SE', 'wow@wow.com')";
  99. //          int rowCount = stmt.executeUpdate(insert);
  100. //          System.out.println("row Count = " + rowCount);
  101. //      } catch (SQLException e) {
  102. //          e.printStackTrace();
  103. //      }
  104. //  }
  105. //
  106. //  public void deleteUser() {
  107. //      try {
  108. //          stmt = conn.createStatement();
  109. //          String insert = "DELETE FROM CUSTOMERS WHERE FIRSTNAME = 'NORA' AND LASTNAME = 'WHITE'";
  110. //          int rowCount = stmt.executeUpdate(insert);     
  111. //          System.out.println("row Count = " + rowCount);
  112. //      } catch (SQLException e) {
  113. //          e.printStackTrace();
  114. //      }
  115. //  }
  116. //
  117. //  public void validateLogin(String username, String password) {
  118. //      try {
  119. //          stmt = conn.createStatement();
  120. //          String query = "SELECT * FROM users WHERE username = '" + username + "' and password ='" + password + "'";
  121. //          rs = stmt.executeQuery(query);
  122. //          if (rs.next()) {
  123. //              System.out.println("User is logged in");
  124. //          } else {
  125. //              System.out.println("Invalid Username and Password");
  126. //          }
  127. //      } catch (SQLException e) {
  128. //          e.printStackTrace();
  129. //      }
  130. //
  131. //  }
  132.  
  133. //----------------------------------------------------------
  134.     //---------------PREPARED STATEMENT---------------------
  135. //----------------------------------------------------------   
  136. //  public void selectUserPreparedStatement() {
  137. //      try {
  138. //          String query = "SELECT * FROM CUSTOMERS WHERE FIRSTNAME = ? and LASTNAME = ?";
  139. //          PreparedStatement pStat = conn.prepareStatement(query);
  140. //          pStat.setString(1, "NORA");
  141. //          pStat.setString(2, "WHITE");
  142. //          rs = pStat.executeQuery();
  143. //          while (rs.next()) {
  144. //              System.out.println(rs.getString("CUSTOMER#") + " " + rs.getString("FIRSTNAME") + " " + rs.getString("LASTNAME"));
  145. //          }
  146. //          pStat.close();
  147. //      } catch (SQLException e) {
  148. //          e.printStackTrace();
  149. //      }
  150. //  }
  151. // 
  152. //  public void insertUserPreparedStatement() {
  153. //      try {
  154. //          String query = "INSERT INTO CUSTOMERS (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)"
  155. //              + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  156. //          PreparedStatement pStat = conn.prepareStatement(query);
  157. //          pStat.setInt(1, 1023);
  158. //          pStat.setString(2, "WHITE");
  159. //          pStat.setString(3, "NORA");
  160. //          pStat.setString(4, "123 WOW STREET");
  161. //          pStat.setString(5, "CALGARY");
  162. //          pStat.setString(6, "AB");
  163. //          pStat.setString(7, "76138");
  164. //          pStat.setString(8, "");
  165. //          pStat.setString(9, "SE");
  166. //          pStat.setString(10, "wow@wow.com");
  167. //          int rowCount = pStat.executeUpdate();
  168. //          System.out.println("row Count = " + rowCount);
  169. //          pStat.close();
  170. //      } catch (SQLException e) {
  171. //          e.printStackTrace();
  172. //      }
  173. //  }
  174. //
  175. //  public void validateLoginPreparedStatement(String username, String password) {
  176. //      try {
  177. //          stmt = conn.createStatement();
  178. //          String query = "SELECT * FROM users where username = ? and password =?";
  179. //          PreparedStatement pStat = conn.prepareStatement(query);
  180. //          pStat.setString(1, username);
  181. //          pStat.setString(2, password);
  182. //          rs = pStat.executeQuery();
  183. //          if (rs.next()) {
  184. //              System.out.println("User is logged in");
  185. //          } else {
  186. //              System.out.println("Invalid Username and Password");
  187. //          }
  188. //      } catch (SQLException e) {
  189. //          e.printStackTrace();
  190. //      }
  191. //
  192. //  }
  193. // 
  194. //  private void simulateLoginPage() {
  195. //       Scanner sc = new Scanner(System.in);
  196. //       String username = "";
  197. //       String password = "";
  198. //       System.out.println("Please enter your username:");
  199. //       username = sc.nextLine();
  200. //       System.out.println("Please enter your password:");      
  201. //       password = sc.nextLine();
  202. //       validateLogin(username, password);
  203. ////         validateLoginPreparedStatement(username, password);
  204. //       sc.close();
  205. //  }
  206.  
  207. //  public static void main(String[] args) {
  208. //      MyJDBCApp app = new MyJDBCApp();
  209. //      app.initializeConnection();
  210. ////        app.insertUser();
  211. ////        app.selectUser();
  212. ////        app.deleteUser();
  213. //      app.selectUserPreparedStatement();
  214. ////        app.insertUserPreparedStatement();
  215. ////        app.simulateLoginPage(); //       invalidUser� or 1=1 --
  216. //  }
  217.  
  218. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement