SHARE
TWEET

Untitled

a guest Dec 2nd, 2019 61 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package lab3databas;
  2. import java.sql.*;
  3.  
  4. import javax.swing.JTextField;
  5.  
  6. public class Database {
  7.     static final String DB_URL = "jdbc:mysql://localhost/lab3";
  8.     static final String DB_USR = "root";
  9.     static final String DB_PW = "erla1012";
  10.     Connection conn = null;
  11.     Boolean first = true;
  12.     public Database() {
  13.        try{
  14.           //STEP 2: Register JDBC driver
  15.           Class.forName("com.mysql.cj.jdbc.Driver");
  16.  
  17.           //STEP 3: Open a connection
  18.           System.out.println("Connecting to database...");
  19.           conn = DriverManager.getConnection(DB_URL,DB_USR,DB_PW);
  20.           //STEP 6: Clean-up environment
  21.        }catch(SQLException se){
  22.           //Handle errors for JDBC
  23.           se.printStackTrace();
  24.        }catch(Exception e){
  25.           //Handle errors for Class.forName
  26.           e.printStackTrace();
  27.        }
  28.     }
  29.     public void endSession() {
  30.         try {
  31.           this.conn.close();
  32.         }
  33.         catch(SQLException se){
  34.              se.printStackTrace();
  35.           }//end finally try
  36.           finally{
  37.               try{
  38.                  if(conn!=null)
  39.                     conn.close();
  40.               }catch(SQLException se){
  41.                      se.printStackTrace();
  42.               }//end finally try
  43.            }//end try
  44.            System.out.println("Goodbye!");
  45.     }
  46.     public void createStudent(String id, String address, String phonenumber, String emailaccount, String fullname, String programme) {
  47.         String SQL = "INSERT INTO USERS" +
  48.                      " VALUES(?, ?, 0)";
  49.           System.out.println("Creating student...");
  50.           PreparedStatement stmt;
  51.         try {
  52.             stmt = conn.prepareStatement(SQL);
  53.             stmt.setString(1, id);
  54.             if(address.equals("address") == false) stmt.setString(2, address);
  55.             else stmt.setString(2, null);
  56.             stmt.execute();
  57.            
  58.             String phoneSQL = "INSERT INTO userphonenumbers" +
  59.                               " VALUES(?, ?)";
  60.             stmt = conn.prepareStatement(phoneSQL);
  61.             stmt.setString(1, id);
  62.             if(phonenumber.equals("phonenumber") == false) stmt.setString(2, phonenumber);
  63.             else stmt.setString(2, null);
  64.             System.out.println(stmt.toString());
  65.             stmt.execute();
  66.            
  67.             String emailSQL = "INSERT INTO useremails" +
  68.                   " VALUES(?, ?)";
  69.             stmt = conn.prepareStatement(emailSQL);
  70.             stmt.setString(1, id);
  71.             if(emailaccount.equals("emailaccount") == false) stmt.setString(2, emailaccount);
  72.             else stmt.setString(2, null);
  73.             System.out.println(stmt.toString());
  74.             stmt.execute();
  75.            
  76.             String nameSQL = "INSERT INTO usernames" +
  77.                   " VALUES(?, ?)";
  78.             stmt = conn.prepareStatement(nameSQL);
  79.             stmt.setString(1, id);
  80.             if(fullname.equals("fullname") == false) stmt.setString(2, fullname);
  81.             else stmt.setString(2, null);
  82.             System.out.println(stmt.toString());
  83.             stmt.execute();
  84.            
  85.             String studentsSQL = "INSERT INTO students" +
  86.                   " VALUES(?, ?)";
  87.             stmt = conn.prepareStatement(studentsSQL);
  88.             stmt.setString(1, id);
  89.             if(programme.equals("programme") == false) stmt.setString(2, programme);
  90.             else stmt.setString(2, null);
  91.             System.out.println(stmt.toString());
  92.             stmt.execute();
  93.         } catch (SQLException e) {
  94.             // TODO Auto-generated catch block
  95.             e.printStackTrace();
  96.         }
  97.         System.out.println("Student with ID: " + id + " created");
  98.     }
  99.    
  100.     public void createAdmin(String id, String address, String phonenumber, String emailaccount, String fullname, String department, String COaddress, String bostadsaddress, String postaddress, String postnummer, String personalphone) {
  101.         String SQL = "INSERT INTO USERS" +
  102.                      " VALUES(?, ?, 0)";
  103.           System.out.println("Creating admin...");
  104.           PreparedStatement stmt;
  105.  
  106.         try {
  107.             stmt = conn.prepareStatement(SQL);
  108.             stmt.setString(1, id);
  109.             if(address.equals("address") == false) stmt.setString(2, address);
  110.             else stmt.setString(2, null);
  111.             stmt.execute();
  112.            
  113.             String phoneSQL = "INSERT INTO userphonenumbers" +
  114.                               " VALUES(?, ?)";
  115.             stmt = conn.prepareStatement(phoneSQL);
  116.             stmt.setString(1, id);
  117.             if(phonenumber.equals("phonenumber") == false) stmt.setString(2, phonenumber);
  118.             else stmt.setString(2, null);
  119.             System.out.println(stmt.toString());
  120.             stmt.execute();
  121.            
  122.             String emailSQL = "INSERT INTO useremails" +
  123.                   " VALUES(?, ?)";
  124.             stmt = conn.prepareStatement(emailSQL);
  125.             stmt.setString(1, id);
  126.             if(emailaccount.equals("emailaccount") == false) stmt.setString(2, emailaccount);
  127.             else stmt.setString(2, null);
  128.             System.out.println(stmt.toString());
  129.             stmt.execute();
  130.            
  131.             String nameSQL = "INSERT INTO usernames" +
  132.                   " VALUES(?, ?)";
  133.             stmt = conn.prepareStatement(nameSQL);
  134.             stmt.setString(1, id);
  135.             if(fullname.equals("fullname") == false) stmt.setString(2, fullname);
  136.             else stmt.setString(2, null);
  137.             System.out.println(stmt.toString());
  138.             stmt.execute();
  139.            
  140.             String adminSQL = "INSERT INTO administrators" +
  141.                   " VALUES(?, ?, ?, ?, ?, ?)";
  142.             stmt = conn.prepareStatement(adminSQL);
  143.             stmt.setString(1, id);
  144.             if(department.equals("department") == false) stmt.setString(2, department);
  145.             else stmt.setString(2, null);
  146.             if(COaddress.equals("COaddress") == false) stmt.setString(3, COaddress);
  147.             else stmt.setString(3, null);
  148.             if(bostadsaddress.equals("bostadsaddress") == false) stmt.setString(4, bostadsaddress);
  149.             else stmt.setString(4, null);
  150.             if(postaddress.equals("postaddress") == false) stmt.setString(5, postaddress);
  151.             else stmt.setString(5, null);
  152.             if(postnummer.equals("postnummer") == false) stmt.setString(6, postnummer);
  153.             else stmt.setString(6, null);
  154.             System.out.println(stmt.toString());
  155.             stmt.execute();
  156.                
  157.             String personalphoneSQL = "INSERT INTO adminpersonalphone" +
  158.                       " VALUES(?, ?)";
  159.             stmt = conn.prepareStatement(personalphoneSQL);
  160.             stmt.setString(1, id);
  161.             if(personalphone.equals("personalphone") == false) stmt.setString(2, personalphone);
  162.             else stmt.setString(2, null);
  163.             System.out.println(stmt.toString());
  164.             stmt.execute();
  165.            
  166.         } catch (SQLException e) {
  167.             // TODO Auto-generated catch block
  168.             e.printStackTrace();
  169.         }
  170.         System.out.println("Admin with ID: " + id + " created");
  171.     }
  172.    
  173.     public String searchStudent(String id, String address, String phonenumber, String emailaccount, String fullname, String programme) {
  174.         StringBuilder sb = new StringBuilder();
  175.         this.first = true;
  176.         String SQL = "SELECT students.userid, address, phonenumber, emailaccount, fullname, programme FROM students" +
  177.                      " INNER JOIN users ON students.userid = users.userid" +
  178.                      " INNER JOIN userphonenumbers ON students.userid = userphonenumbers.userid" +
  179.                      " INNER JOIN useremails ON students.userid = useremails.userid" +
  180.                      " INNER JOIN usernames ON students.userid = usernames.userid";
  181.         if(id.equals("userid") == false) {
  182.             SQL += firstCheck() + " students.userid = " + id + " AND";
  183.         }
  184.         if(address.equals("address") == false) {
  185.             SQL += firstCheck() + " users.address = " + "\"" + address + "\"" + " AND";
  186.         }
  187.         if(phonenumber.equals("phonenumber") == false) {
  188.             SQL += firstCheck() + " usersphonenumbers.phonenumber = " + "\"" + phonenumber + "\"" + " AND";
  189.         }
  190.         if(emailaccount.equals("emailaccount") == false) {
  191.             SQL += firstCheck() + " useremails.emailaccount = " + "\"" + emailaccount + "\"" + " AND";
  192.         }
  193.         if(fullname.equals("fullname") == false) {
  194.             SQL += firstCheck() + " usernames.fullname = " + "\"" + fullname + "\"" + " AND";
  195.         }
  196.         if(programme.equals("programme") == false) {
  197.             SQL += firstCheck() + " students.programme = " + "\"" + programme + "\"" + " AND";
  198.         }
  199.         if(!this.first) SQL = SQL.substring(0, SQL.length()-3);
  200.         PreparedStatement stmt;
  201.         try {
  202.             System.out.println(SQL);
  203.             stmt = conn.prepareStatement(SQL);
  204.             ResultSet r = stmt.executeQuery();
  205.            
  206.             while(r.next()) {
  207.                 sb.append("user with userid: " + r.getInt("userid") + ", ");
  208.                 sb.append("user with address: " + r.getString("address") + ", ");
  209.                 sb.append("user with phonenumber: " + r.getString("phonenumber") + ", ");
  210.                 sb.append("user with emailaccount: " + r.getString("emailaccount") + ", ");
  211.                 sb.append("user with fullname: " + r.getString("fullname") + ", ");
  212.                 sb.append("user with programme: " + r.getString("programme"));
  213.                 sb.append("\n");
  214.             }
  215.         } catch (SQLException e) {
  216.             // TODO Auto-generated catch block
  217.             e.printStackTrace();
  218.         }
  219.         return sb.toString();
  220.     }
  221.     public String searchAdmin(String id, String address, String phonenumber, String emailaccount, String fullname, String department, String COaddress, String bostadsaddress, String postaddress, String postnummer, String personalphone) {
  222.         StringBuilder sb = new StringBuilder();
  223.         this.first = true;
  224.         String SQL = "SELECT administrators.userid, address, phonenumber, emailaccount, fullname, department, coadress, bostadsadress, postadress, postnummer, privatenumber FROM administrators" +
  225.                      " INNER JOIN users ON administrators.userid = users.userid" +
  226.                      " INNER JOIN userphonenumbers ON administrators.userid = userphonenumbers.userid" +
  227.                      " INNER JOIN useremails ON administrators.userid = useremails.userid" +
  228.                      " INNER JOIN usernames ON administrators.userid = usernames.userid" +
  229.                      " INNER JOIN adminpersonalphone ON administrators.userid = adminpersonalphone.userid";
  230.                      
  231.         if(id.equals("userid") == false) {
  232.             SQL += firstCheck() + " administrators.userid = " + id + " AND";
  233.         }
  234.         if(address.equals("address") == false) {
  235.             SQL += firstCheck() + " users.address = " + "\"" + address + "\"" + " AND";
  236.         }
  237.         if(phonenumber.equals("phonenumber") == false) {
  238.             SQL += firstCheck() + " usersphonenumbers.phonenumber = " + "\"" + phonenumber + "\"" + " AND";
  239.         }
  240.         if(emailaccount.equals("emailaccount") == false) {
  241.             SQL += firstCheck() + " useremails.emailaccount = " + "\"" + emailaccount + "\"" + " AND";
  242.         }
  243.         if(fullname.equals("fullname") == false) {
  244.             SQL += firstCheck() + " usernames.fullname = " + "\"" + fullname + "\"" + " AND";
  245.         }
  246.         if(department.equals("department") == false) {
  247.             SQL += firstCheck() + " administrators.department = " + "\"" + department + "\"" + " AND";
  248.         }
  249.         if(COaddress.equals("COaddress") == false) {
  250.             SQL += firstCheck() + " administrators.coadress = " + "\"" + COaddress + "\"" + " AND";
  251.         }
  252.         if(bostadsaddress.equals("bostadsaddress") == false) {
  253.             SQL += firstCheck() + " administrators.bostadsadress = " + "\"" + bostadsaddress + "\"" + " AND";
  254.         }
  255.         if(postaddress.equals("postaddress") == false) {
  256.             SQL += firstCheck() + " administrators.postadress = " + "\"" + postaddress + "\"" + " AND";
  257.         }
  258.         if(postnummer.equals("postnummer") == false) {
  259.             SQL += firstCheck() + " administrators.postnummer = " + "\"" + postnummer + "\"" + " AND";
  260.         }
  261.         if(personalphone.equals("personalphone") == false) {
  262.             SQL += firstCheck() + " adminpersonalphone.privatenumber = " + "\"" + personalphone + "\"" + " AND";
  263.         }
  264.         if(!this.first) SQL = SQL.substring(0, SQL.length()-3);
  265.           PreparedStatement stmt;
  266.         try {
  267.             System.out.println(SQL);
  268.             stmt = conn.prepareStatement(SQL);
  269.             ResultSet r = stmt.executeQuery();
  270.            
  271.             while(r.next()) {
  272.                 sb.append("user with userid: " + r.getInt("userid") + ", ");
  273.                 sb.append("user with address: " + r.getString("address") + ", ");
  274.                 sb.append("user with phonenumber: " + r.getString("phonenumber") + ", ");
  275.                 sb.append("user with emailaccount: " + r.getString("emailaccount") + ", ");
  276.                 sb.append("user with fullname: " + r.getString("fullname") + ", ");
  277.                 sb.append("user with department: " + r.getString("department"));
  278.                 sb.append("user with COaddress: " + r.getString("coadress"));
  279.                 sb.append("user with bostadsadress: " + r.getString("bostadsadress"));
  280.                 sb.append("user with postadress: " + r.getString("postadress"));
  281.                 sb.append("user with postnummer: " + r.getString("postnummer"));
  282.                 sb.append("user with personalphone: " + r.getString("privatenumber"));
  283.                 sb.append("\n");
  284.             }
  285.         } catch (SQLException e) {
  286.             // TODO Auto-generated catch block
  287.             e.printStackTrace();
  288.         }
  289.         return sb.toString();
  290.     }
  291.     public void editStudent(String id, String editid, String editAddress, String editPhonenumber, String editEmailaccount, String editFullname, String editProgramme) {
  292.         PreparedStatement stmt = null;
  293.         try {
  294.             if(editid.equals("edituserid") == false) {
  295.                 String SQL = "UPDATE users" +
  296.                              " SET userid = " + editid +
  297.                              " WHERE userid = " + id;
  298.                     stmt = conn.prepareStatement(SQL);
  299.                     stmt.execute();
  300.                     System.out.println("update successful");
  301.             }
  302.             if(editAddress.equals("editaddress") == false) {
  303.                 String SQL = "UPDATE users" +
  304.                              " SET address = " + "\"" + editAddress + "\"" +
  305.                              " WHERE userid = " + id;
  306.                 stmt = conn.prepareStatement(SQL);
  307.                 stmt.execute();
  308.                 System.out.println("update successful");
  309.             }
  310.             if(editPhonenumber.equals("editphonenumber") == false) {
  311.                 String SQL = "UPDATE userphonenumbers" +
  312.                              " SET phonenumber = " + "\"" + editPhonenumber + "\"" +
  313.                              " WHERE userid = " + id;
  314.                 stmt = conn.prepareStatement(SQL);
  315.                 stmt.execute();
  316.                 System.out.println("update successful");
  317.             }
  318.             if(editEmailaccount.equals("editemailaccount") == false) {
  319.                 String SQL = "UPDATE useremails" +
  320.                              " SET emailaccount = " + "\"" + editEmailaccount + "\"" +
  321.                              " WHERE userid = " + id;
  322.                 stmt = conn.prepareStatement(SQL);
  323.                 stmt.execute();
  324.                 System.out.println("update successful");
  325.             }
  326.                
  327.             if(editFullname.equals("editfullname") == false) {
  328.                 String SQL = "UPDATE usernames" +
  329.                              " SET fullname = " + "\"" + editFullname + "\"" +
  330.                              " WHERE userid = " + id;
  331.                 stmt = conn.prepareStatement(SQL);
  332.                 stmt.execute();
  333.                 System.out.println("update successful");
  334.             }
  335.             if(editProgramme.equals("editprogramme") == false) {
  336.                 String SQL = "UPDATE students" +
  337.                              " SET programme = " + "\"" + editProgramme + "\"" +
  338.                              " WHERE userid = " + id;
  339.                 stmt = conn.prepareStatement(SQL);
  340.                 stmt.execute();
  341.                 System.out.println("update successful");
  342.             }
  343.         } catch (SQLException e) {
  344.             // TODO Auto-generated catch block
  345.             e.printStackTrace();
  346.         }
  347.     }
  348.     public void editAdmin(String id, String editid, String editAddress, String editPhonenumber, String editEmailaccount, String editFullname, String editDepartment, String editCOaddress, String editBostadsaddress, String editPostaddress, String editPostnummer, String editPersonalphone) {
  349.         PreparedStatement stmt = null;
  350.         try {
  351.             if(editid.equals("edituserid") == false) {
  352.                 String SQL = "UPDATE users" +
  353.                              " SET userid = " + editid +
  354.                              " WHERE userid = " + id;
  355.                     stmt = conn.prepareStatement(SQL);
  356.                     stmt.execute();
  357.                     System.out.println("update successful");
  358.             }
  359.             if(editAddress.equals("editaddress") == false) {
  360.                 String SQL = "UPDATE users" +
  361.                              " SET address = " + "\"" + editAddress + "\"" +
  362.                              " WHERE userid = " + id;
  363.                 stmt = conn.prepareStatement(SQL);
  364.                 stmt.execute();
  365.                 System.out.println("update successful");
  366.             }
  367.             if(editPhonenumber.equals("editphonenumber") == false) {
  368.                 String SQL = "UPDATE userphonenumbers" +
  369.                              " SET phonenumber = " + "\"" + editPhonenumber + "\"" +
  370.                              " WHERE userid = " + id;
  371.                 stmt = conn.prepareStatement(SQL);
  372.                 stmt.execute();
  373.                 System.out.println("update successful");
  374.             }
  375.             if(editEmailaccount.equals("editemailaccount") == false) {
  376.                 String SQL = "UPDATE useremails" +
  377.                              " SET emailaccount = " + "\"" + editEmailaccount + "\"" +
  378.                              " WHERE userid = " + id;
  379.                 stmt = conn.prepareStatement(SQL);
  380.                 stmt.execute();
  381.                 System.out.println("update successful");
  382.             }
  383.                
  384.             if(editFullname.equals("editfullname") == false) {
  385.                 String SQL = "UPDATE usernames" +
  386.                              " SET fullname = " + "\"" + editFullname + "\"" +
  387.                              " WHERE userid = " + id;
  388.                 stmt = conn.prepareStatement(SQL);
  389.                 stmt.execute();
  390.                 System.out.println("update successful");
  391.             }
  392.             if(editDepartment.equals("editdepartment") == false) {
  393.                 String SQL = "UPDATE administrators" +
  394.                              " SET department = " + "\"" + editDepartment + "\"" +
  395.                              " WHERE userid = " + id;
  396.                 stmt = conn.prepareStatement(SQL);
  397.                 stmt.execute();
  398.                 System.out.println("update successful");
  399.             }
  400.             if(editCOaddress.equals("editCOaddress") == false) {
  401.                 String SQL = "UPDATE administrators" +
  402.                              " SET COadress = " + "\"" + editCOaddress + "\"" +
  403.                              " WHERE userid = " + id;
  404.                 stmt = conn.prepareStatement(SQL);
  405.                 stmt.execute();
  406.                 System.out.println("update successful");
  407.             }
  408.             if(editBostadsaddress.equals("editbostadsaddress") == false) {
  409.                 String SQL = "UPDATE administrators" +
  410.                              " SET bostadsadress = " + "\"" + editBostadsaddress + "\"" +
  411.                              " WHERE userid = " + id;
  412.                 stmt = conn.prepareStatement(SQL);
  413.                 stmt.execute();
  414.                 System.out.println("update successful");
  415.             }
  416.             if(editPostaddress.equals("editpostaddress") == false) {
  417.                 String SQL = "UPDATE administrators" +
  418.                              " SET postadress = " + "\"" + editPostaddress + "\"" +
  419.                              " WHERE userid = " + id;
  420.                 stmt = conn.prepareStatement(SQL);
  421.                 stmt.execute();
  422.                 System.out.println("update successful");
  423.             }
  424.  
  425.             if(editPostnummer.equals("editpostnummer") == false) {
  426.                 String SQL = "UPDATE administrators" +
  427.                              " SET postnummer = " + "\"" + editPostnummer + "\"" +
  428.                              " WHERE userid = " + id;
  429.                 stmt = conn.prepareStatement(SQL);
  430.                 stmt.execute();
  431.                 System.out.println("update successful");
  432.             }
  433.             if(editPersonalphone.equals("editpersonalphone") == false) {
  434.                 String SQL = "UPDATE adminpersonalphone" +
  435.                              " SET privatenumber = " + "\"" + editPersonalphone + "\"" +
  436.                              " WHERE userid = " + id;
  437.                 stmt = conn.prepareStatement(SQL);
  438.                 stmt.execute();
  439.                 System.out.println("update successful");
  440.             }
  441.         } catch (SQLException e) {
  442.             // TODO Auto-generated catch block
  443.             e.printStackTrace();
  444.         }
  445.     }
  446.     public String firstCheck() {
  447.         if(this.first) {
  448.             this.first = false;
  449.             return " WHERE";
  450.         }
  451.         else return "";
  452.     }
  453. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top