import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class UserFrontend { public static void main(String[] agrs) { String dbname = ""; // Input your UiO-username String user = ""; // Input your UiO-username + _priv String pwd = ""; // Input the password for the _priv-user you got in a mail // Connection details String connectionStr = "user=" + user + "&" + "port=5432&" + "password=" + pwd + ""; String host = "jdbc:postgresql://dbpg-ifi-kurs03.uio.no"; String connectionURL = host + "/" + dbname + "?sslmode=require&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&" + connectionStr; try { // Load driver for PostgreSQL Class.forName("org.postgresql.Driver"); // Create a connection to the database //Connection connection = DriverManager.getConnection(); Connection connection = DriverManager.getConnection(host + "/" + dbname + "?" + connectionStr); String username = null; while (username == null) { System.out.println("-- USER FRONTEND --"); System.out.println("Please choose an option:\n 1. Register\n 2. Login\n 3. Exit"); int ch = getIntFromUser("Option: ", false); if (ch == 1) { register(connection); // Register new user } else if (ch == 2) { username = login(connection); // Login existing user } else if (ch == 3) { return; // Exit program } } // Once logged in, allow user to search for products search(connection, username); } catch (SQLException|ClassNotFoundException ex) { System.err.println("Error encountered: " + ex.getMessage()); } } private static void register(Connection connection) throws SQLException { System.out.println(" -- REGISTER NEW USER --"); // Get credentials for new user String username = getStrFromUser("Username: "); String password = getStrFromUser("Password: "); String name = getStrFromUser("Name: "); String address = getStrFromUser("Address: "); // To execute queries, we need a PreparedStatement object, created by calling // the prepareStatement()-method with a query as argument. // We can use ? as a place holder for a value in PreparedStatements, // and then set them using setString(int, String), setInt(int, int), etc. // where the first argument is the index of the value to set, starting with 1. // If we do not use these placeholders, the program is susceptible to SQL injection attacks // More about this on next lecture. PreparedStatement statement = connection.prepareStatement("INSERT INTO ws.users(name, username, password, address) VALUES (?, ?, ?, ?);"); statement.setString(1, name); statement.setString(2, username); statement.setString(3, password); // NOTE: NEVER store passwords in plain text for an actual application!!! statement.setString(4, address); // To execute the query we have made above, simply call the execute()-method statement.execute(); System.out.println("New user " + username + " added!"); } private static String login(Connection connection) throws SQLException { System.out.println(" -- LOGIN --"); // Get login details String username = getStrFromUser("Username: "); String password = getStrFromUser("Password: "); PreparedStatement statement = connection.prepareStatement("SELECT username, password, name FROM ws.users WHERE username = ? AND password = ?;"); statement.setString(1, username); statement.setString(2, password); // To execute the SELECT-query, we can call executeQuery() on the // statement-object. This will return a ResultSet, an iterator-like object over the results. // A ResultSet has a pointer to one row of the result ResultSet rows = statement.executeQuery(); // However, the ResultSet does not point to a row initially // By calling next() we move the pointer to the next row in the // result, and to the first row if not called before // The next() method returns a boolean which is false if there is no more // rows in the result, and true otherwise if (!rows.next()) { // The query returned no results, thus the user-password pair does not exist in the DB System.out.println("Incorrect username or password."); return null; } else { // Query returned a result, thus correct username and password System.out.println("Welcome " + username); return username; } } private static void search(Connection connection, String username) throws SQLException { // We start by gathering input from user, defining the search System.out.println(" -- SEARCH --"); String name = getStrFromUser("Search: "); String category = getStrFromUser("Category: "); // We will now construct the search query based on the user's input String q = "SELECT p.pid, p.name, p.price, c.name AS category, p.description " + "FROM ws.products AS p INNER JOIN ws.categories AS c USING (cid)" + "WHERE p.name LIKE ?"; if (!category.equals("")) { q += " AND c.name = ?"; } q += ";"; PreparedStatement statement = connection.prepareStatement(q); statement.setString(1, '%' + name + '%'); if (!category.equals("")) { statement.setString(2, category); } // We can now execute the query using the executeQuery()-method (described above) ResultSet rows = statement.executeQuery(); if (!rows.next()) { // No row to move to, thus empty result set System.out.println("No results."); return; } // The user should be able to pick which product to order based on the product's pid System.out.println(" -- RESULTS --\n"); do { // To get values from the current row in the ResultSet // we use getString(int) for Strings, getFloat(int) for floats, etc. // The argument int denotes which column to get a value from, starting from index 1 System.out.println("===" + rows.getString(2) + "===\n" + "Product ID: " + rows.getInt(1) + "\n" + "Price: " + rows.getFloat(3) + "\n" + "Category: " + rows.getString(4)); if (!rows.getString(3).equals("NULL")) { System.out.println("Description: " + rows.getString(5)); } System.out.print("\n"); } while (rows.next()); // Now that we have made a search, we will allow the user to order products from the // search result orderProducts(connection, username); } private static void orderProducts(Connection connection, String username) { } /** * Utility method that gets an int as input from user * Prints the argument message before getting input * If second argument is true, the user does not need to give input and can leave * the field blank (resulting in a null) */ private static Integer getIntFromUser(String message, boolean canBeBlank) { while (true) { String str = getStrFromUser(message); if (str.equals("") && canBeBlank) { return null; } try { return Integer.valueOf(str); } catch (NumberFormatException ex) { System.out.println("Please provide an integer or leave blank."); } } } /** * Utility method that gets a String as input from user * Prints the argument message before getting input */ private static String getStrFromUser(String message) { Scanner s = new Scanner(System.in); System.out.print(message); return s.nextLine(); } }