DB lab4 java handler

package de.haw.ie4lab4;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbHandler {
  /**
   * Database connection
   */
  private Connection conn;

  /**
   * The current SQL statement
   */
  private String sql;

  /**
   * Getter for the current SQL statement
   *
   * @return the SQL statement
   */
  public String getSql() {
    return sql;
  }

  /**
   * Connect to the database.
   *
   * @param driverName
   *          - name of JDBC driver class
   * @param url
   *          - JDBC URL
   * @param user
   *          - DB user name
   * @param password
   *          - DB password
   * @throws SQLException
   */

  public void connectDB(String driverName, String url, String user, String password) throws SQLException {
    System.out.println("Trying to connect to " + url);

    // TODO: connect to the DB! -> done
    conn = DriverManager.getConnection(url, user, password);

    // TODO: disable autoCommit! -> done
    conn.setAutoCommit(false);
    //
    // Print success message and some meta data:
    //
    DatabaseMetaData metaData = conn.getMetaData();
    System.out.println("Connected to DB " + metaData.getURL() + " as user " + metaData.getUserName());
    System.out.println(metaData.getDatabaseProductName() + " " + metaData.getDatabaseMajorVersion() + "."
        + metaData.getDatabaseMinorVersion());
  }

  /**
   * Close the connection
 * @throws SQLException
   */
  public void close() throws SQLException {
    /*
     * TODO: rollback the transaction (in real life, you'd want to commit -> but
     * then you cannot call insertNewCustomer() twice.)  -> done
     */
      conn.rollback();
    // TODO: close the connection (if it has been initialized) -> done
      if (conn != null) {
          conn.close();
      }
  }

  /**
   * Print the list of order numbers for the given customer
   *
   * @param customer
   *          - Name of customer
   * @throws SQLException
   */
  public void printOrderNumbers(String customer) throws SQLException {
    System.out.println(customer + "'s orders:");

    // TODO: SQL see assignment 10b-10
    sql = "SELECT orders.o_nr FROM orders" +
            "JOIN customer ON orders.c_id = customer.c_id"+
            " WHERE customer.name = ?";
    PreparedStatement statement = conn.prepareStatement(sql);
    statement.setString(1, customer);

    // cursor
    ResultSet cursor = statement.executeQuery();
    while (cursor.next()) {
        cursor.getString(cursor.getString("o_nr"));
    }
  }

  /**
   * Print an invoice for the given order. The invoice shall contain every
   * single order item and the total price.
   *
   * @param orderNumber
   *          - value for o_nr
   * @throws SQLException
   */
  public void printInvoiceForOrder(int orderNumber) throws SQLException {
    System.out.println("Invoice for order number " + orderNumber);

    // Optional: You could print customer information here!

    /*
     * TODO: For every order item, print the article name, the article's price
     * per unit, the quantity, and the price of the order item. SQL see
     * assignment 10b-14
     */
    sql = "SELECT article.name, article.price, order_item.quantity, article.price * order_item.quantity AS Total FROM order_item"+
            "JOIN orders ON order_item.o_nr = orders.o_nr"+
            "JOIN article ON order_item.a_nr = article.a_nr"+
            "WHERE order_item.o_nr = ?";
    PreparedStatement statement = conn.prepareStatement(sql);
    statement.setString(1, ""+orderNumber);

    ResultSet cursor = statement.executeQuery();
    System.out.println("article     price     quantity     Total");
    // this saves the total price of the order
    double totalPrice = 0;
    while (cursor.next()) {
        System.out.println(cursor.getString("name")+"|"+
                cursor.getString("price")+"|"+
                cursor.getString("quantity")+"|"+
                cursor.getString("Total"));

        totalPrice += Double.parseDouble(cursor.getString("Total"));
    }

    System.out.println("-----------------------");

    /*
     * TODO: Print the total price of the order. You can calculate the sum via
     * SQL (see assignment 10b-15), or in Java.
     */
    sql = "";
    System.out.println("Total price: " + totalPrice);

  }

  /**
   * Insert a new customer
   *
   * @param id
   *          - customer ID
   * @param name
   *          - customer name
   * @throws SQLException
   */
  public void insertNewCustomer(int id, String name) throws SQLException {
    System.out.println("Trying to insert new customer. id=" + id + ", name=" + name);

    // TODO: insert a new customer with the given values
    sql = "INSERT INTO customer (c_id, name) VALUES (?,?)";
    PreparedStatement statement = conn.prepareStatement(sql);
    statement.setInt(1, id);
    statement.setString(2, name);

    statement.execute();

  }

  /**
   * Change the article's price
   *
   * @param articleName
   *          - identifies the article
   * @param price
   *          - the new price
   * @throws SQLException
   */
  public void changeArticlePrice(String articleName, double price) throws SQLException {
    System.out.println("Trying to set the price of " + articleName + " to " + price);
    sql = "UPDATE article SET price = ? WHERE name = ?";
    PreparedStatement statement = conn.prepareStatement(sql);
    statement.setDouble(1, price);
    statement.setString(2, articleName);

    statement.execute();
    int n = 0;

    // TODO: change the article's price
    sql = "";

    System.out.println("Number of rows affected: " + n);
  }
}