API tools faq
paste
Guest User

Untitled

a guest
Dec 2nd, 2019
88
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 18.08 KB | None | 0 0
raw download clone embed print report
  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. }
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!