mysql

StudentsDBConn.java
-----------------------------------------------------


import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StudentsDBConn {
    static final private String TABLE_NAME = "studenttable";
    static final private String FIELD_ID = "id";
    static final private String FIELD_FIRSTNAME = "fname";
    static final private String FIELD_LASTNAME = "lname";

    private Connection conn;

    public StudentsDBConn() throws SQLException{
        String driverName = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driverName);
        }
        catch(ClassNotFoundException e){
            System.out.println(e);
        }

        String url = "jdbc:mysql://eu-cdbr-sl-lhr-01.cleardb.net/ibmx_7aae882800d7265";
        String username = "b87b3718783680";
        String password = "d8e92cf6";

        Connection dbConn = DriverManager.getConnection(url,username,password);

        this.conn = dbConn;

        System.out.println("connected");

    }


    public void closeConnection() throws SQLException{
        this.conn.close();
        System.out.println("connection closed");
    }

    public void createTable() throws SQLException{
        String createString = "CREATE TABLE IF NOT EXISTS "+TABLE_NAME;
        createString += "(";
        createString += FIELD_ID+" int NOT NULL AUTO_INCREMENT PRIMARY KEY, ";
        createString += FIELD_FIRSTNAME+" varchar(255), ";
        createString += FIELD_LASTNAME+" varchar(255)";
        createString += ")";

        PreparedStatement createStatement = this.conn.prepareStatement(createString);
        createStatement.executeUpdate();

        System.out.println("table created");

    }


    public void insert(String firstname, String lastname) throws SQLException{
        String insertString = "INSERT INTO "+TABLE_NAME;
        insertString += "(";
        insertString += FIELD_FIRSTNAME+","+FIELD_LASTNAME;
        insertString += ")";
        insertString += " VALUES ";
        insertString += "(";
        insertString += "'"+firstname+"','"+lastname+"'";
        insertString += ")";

        PreparedStatement insertStatement = this.conn.prepareStatement(insertString);
        insertStatement.executeUpdate();

        System.out.println("data inserted");
    }


    public List<Map<String,String>> getAllData() throws SQLException{
        String selectString = "SELECT * FROM "+TABLE_NAME;
        PreparedStatement selectStatement = this.conn.prepareStatement(selectString);
        ResultSet response = selectStatement.executeQuery();

        Map<String, String> row;
        List<Map<String,String>> data = new ArrayList<>();

        while(response.next()){
            row = new HashMap<>();
            row.put(FIELD_ID, response.getString(FIELD_ID));
            row.put(FIELD_FIRSTNAME, response.getString(FIELD_FIRSTNAME));
            row.put(FIELD_LASTNAME, response.getString(FIELD_LASTNAME));
            data.add(row);
        }

        System.out.println("data has been received");

        return data;
    }

    public void update(String id, String firstname, String lastname) throws SQLException{
        String updateString = "UPDATE "+TABLE_NAME+" SET ";
        updateString += FIELD_FIRSTNAME+"='"+firstname+"',";
        updateString += FIELD_LASTNAME+"='"+lastname+"' ";
        updateString += "WHERE "+FIELD_ID+"="+id;
        PreparedStatement updateStatement = this.conn.prepareStatement(updateString);
        updateStatement.executeUpdate();

        System.out.println("data updated");
    }


    public void delete(String id) throws SQLException{
        String deleteString = "DELETE FROM "+TABLE_NAME+" WHERE "+FIELD_ID+"="+id;
        PreparedStatement deleteStatement = this.conn.prepareStatement(deleteString);
        deleteStatement.executeUpdate();

        System.out.println("data deleted");
    }

    public void dropTable() throws SQLException{
        String dropString = "DROP TABLE "+TABLE_NAME;
        PreparedStatement dropStatement = this.conn.prepareStatement(dropString);
        dropStatement.executeUpdate();

        System.out.println("table dropped");
    }


}



-------------------------------------------------------------


Tester.java
-------------------------------------------------------------
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class Tester {
    public static void main(String[] args){
        try {
            StudentsDBConn conn = new StudentsDBConn();

            //conn.createTable();
            //conn.insert("lau","goldman");
            //conn.update("11","tzahi","mavriz");
            //conn.delete("11");

            List<Map<String,String>> data = conn.getAllData();
            for(Map<String,String> row : data){
                for(Map.Entry<String, String> field : row.entrySet()){
                    System.out.print(field.getKey()+": "+field.getValue()+"\t");
                }
                System.out.println();
            }

            //conn.dropTable();

            conn.closeConnection();
        }
        catch(SQLException e){
            System.out.println(e);
        }
    }
}