import java.sql.*;

class jdbcMysql {


		String url = "sql2.njit.edu";
		String ucid = "ab822";	//your ucid
		String dbpassword = "";	//your MySQL password
		Connection conn;

		public void createData() {
		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		}
		catch (Exception e) {
			System.err.println("Unable to load driver.");
			e.printStackTrace();
		}
		System.out.println("Driver loaded.");
		System.out.println("Establishing connection . . . ");
		try {


			conn = DriverManager.getConnection("jdbc:mysql://"+url+"/"+ucid+"?user="+ucid+"&password="+dbpassword);

			System.out.println("Connection established.");
			System.out.println("Creating a Statement object . . . ");

			Statement stmt = conn.createStatement();
			System.out.println("Statement object created.");
			stmt.executeUpdate("DROP TABLE IF EXISTS user");
			System.out.println("Old table dropped (if it existed).");

			System.out.println("Creating a table . . .");
			stmt.executeUpdate("CREATE TABLE user("+
				"idnum INT NOT NULL AUTO_INCREMENT, "+
				"PRIMARY KEY(idnum), "+
				"pinno VARCHAR(100) UNIQUE NOT NULL, "+
				"accountid VARCHAR(100) UNIQUE NOT NULL, "+
				"fullname VARCHAR(100) NOT NULL, "+
				"email VARCHAR(100) NOT NULL, "+
				"amount int NOT NULL");
			System.out.println("Table created.");

			System.out.println("Inserting data in table . . .");

			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
				"VALUES(\"1234\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
					"VALUES(\"2374\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
					"VALUES(\"9802\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
					"VALUES(\"8934\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
					"VALUES(\"8891\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
					"VALUES(\"9912\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
					"VALUES(\"0915\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
					"VALUES(\"0871\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
					"VALUES(\"0986\", \"AX1265\", \"john smith\", \"john@njit.edu\", 920)");
			System.out.println("Inserted data.");
		}
		catch (SQLException E) {
			System.out.println("SQLException: " + E.getMessage());
			System.out.println("SQLState:     " + E.getSQLState());
			System.out.println("VendorError:  " + E.getErrorCode());
		}
	}

		public static void main(String args[]) {
			jdbcMysql jbml =  new jdbcMysql();
			jbml.createData();
		}

	public void closeConn( ) throws SQLException {
		conn.close();
	}

	public TransactionObject dataOperations(String operation, TransactionObject tmpObj) throws SQLException {
		Statement stmt = conn.createStatement();
		switch (tmpObj.getType()) {
    	case "WITHDRAW":
    		int towithdraw = Integer.parseInt(tmpObj.getNum());
    		ResultSet rs = stmt.executeQuery("SELECT balance from user where accountID="+tmpObj.getId());
    		System.out.print(rs.getString("idnum"));
            rs.close();
    		break;
    	case "BALANCE":
    		//ResultSet rs = stmt.executeQuery("SELECT balance from user where accountid="+tmpObj.getId());
    		break;
    	case "TRANSFER":
    		//transfer checking saving
    		break;
    	}
		return tmpObj;
	}
}