mysql with comments

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;

    // the constructor will make a connection with the database ont he server
    public StudentsDBConn() throws SQLException{
        String driverName = "com.mysql.jdbc.Driver"; // this is the fully qualifies name of the class we want to load
        try { // Class.forName(...) throws a ClassNotFoundException so we deal with that here
            Class.forName(driverName); // loading the class we specified in driverName
            String url = "jdbc:mysql://eu-cdbr-sl-lhr-01.cleardb.net/ibmx_7aae882800d7265"; // the url of the database (on the server)
            String username = "b87b3718783680"; // the username for the database
            String password = "d8e92cf6"; // the password for the database

            Connection dbConn = DriverManager.getConnection(url,username,password); // creating a connection with the server

            this.conn = dbConn; // saving that connection as a property of the class

            System.out.println("connected");
        }
        catch(ClassNotFoundException e){ // catching the possibly thrown exception
            System.out.println(e);
        }


    }


    // method to end to connection with the database on the server
    public void closeConnection() throws SQLException{
        this.conn.close(); // closing the server connection
        System.out.println("connection closed");
    }

    // method to create a table named what we saved in TABLE_NAME with the fields in the constants FIELD_...
    public void createTable() throws SQLException{
        // creating the sql string that represents creating a table
        // syntax: CREATE TABLE IF NOT EXITS tableName (field1 data_type1, field2 data_type2,...)
        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); // creating an executable sql statement
        createStatement.executeUpdate(); // executing the sql statement

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

    }

    // method for inserting data into our table
    public void insert(String firstname, String lastname) throws SQLException{
        // creating the sql string that represents inserting into a table
        // syntax: INSERT INTO tableName (field1,field2,...) VALUES (value1,value2,...)
        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); // creating an executable sql statement
        insertStatement.executeUpdate();// executing the sql statement

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

    // method to retrieve information from our table
    public List<Map<String,String>> getAllData() throws SQLException{
        // creating the sql string that represents selecting data from a table
        // syntax: SELECT fields FROM tableName. (more on extended syntax bellow)
        String selectString = "SELECT * FROM "+TABLE_NAME;
        // there are many variations of the SELECT statement.
        // the symbol * means select all fields
        // we can have for instance SELECT fname,lname FROM ... to only get those fields (in this case excluding the id field):
        //String selectString = "SELECT fname,lname FROM "+TABLE_NAME;
        // we can limit the resutls by using LIMT for instance:
        //String selectString = "SELECT * FROM "+TABLE_NAME+" LIMIT 2"; // to only get the first 2 results of the query
        // we can order the result by usinf ORDER BY, and can get it in ascending - ASC, or descending - DESC order:
        //String selectString = "SELECT * FROM "+TABLE_NAME+" ORDER BY fname ASC"; // order by ascending order of a specified field
        //String selectString = "SELECT * FROM "+TABLE_NAME+" ORDER BY fname DESC"; // order by descending order of a specified field
        // we can limit and order but in this case we mist order first and then limit:
        //String selectString = "SELECT * FROM "+TABLE_NAME+" ORDER BY fname DESC LIMIT 2 "; // order and limit
        // we can use complex conditions in the where clause using AND, OR, NOT:
       // String selectString = "SELECT * FROM "+TABLE_NAME+" WHERE id=1 OR lname='mavriz'"
       // String selectString = "SELECT * FROM "+TABLE_NAME+" WHERE id=1 AND NOT lname='mavriz'"
        // note that we use a single = and not == like in java
        PreparedStatement selectStatement = this.conn.prepareStatement(selectString);// creating an executable sql statement
        ResultSet response = selectStatement.executeQuery();// executing the sql statement and saving the result. note that here we use executeQuery because we are expecting to get data from the table and not just do work on table

        // we want to save the information we received. we will save it in a list of maps
        Map<String, String> row; // each row of data will be saved in a map with the key being the field name and the value being the data saved for that field in the row
        List<Map<String,String>> data = new ArrayList<>();

        while(response.next()){ // response.next(moves to the next row in the data we received from the server)
            row = new HashMap<>(); // craeting a new map for the new row
            row.put(FIELD_ID, response.getString(FIELD_ID)); // saving the id field
            row.put(FIELD_FIRSTNAME, response.getString(FIELD_FIRSTNAME)); // saving the fname field
            row.put(FIELD_LASTNAME, response.getString(FIELD_LASTNAME)); // saving the lname field
            data.add(row); // adding the map we just made to the list
        }

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

        return data; // we return the data we got from the server
    }

    // method for update entries in the table
    public void update(String id, String firstname, String lastname) throws SQLException{
        // creating the sql string that represents updating data in a table
        // syntax: UPDATE tableName SET field1=value1,field2=value2,... WHERE condition
        String updateString = "UPDATE "+TABLE_NAME+" SET ";
        updateString += FIELD_FIRSTNAME+"='"+firstname+"',";
        updateString += FIELD_LASTNAME+"='"+lastname+"' ";
        updateString += "WHERE "+FIELD_ID+"="+id;
        // we can have a complex condition int the where clause
        // for instance WHERE id=3 AND fname='shahaf'
        PreparedStatement updateStatement = this.conn.prepareStatement(updateString);// creating an executable sql statement
        updateStatement.executeUpdate();// executing the sql statement

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

    // method for deleting data from a table
    public void delete(String id) throws SQLException{
        // creating the sql string that represents deleting a row from a table
        // syntax: DELETE FROM tableName WHERE condition
        String deleteString = "DELETE FROM "+TABLE_NAME+" WHERE "+FIELD_ID+"="+id;
        // here as well we can have a complex condition in the where clause
        PreparedStatement deleteStatement = this.conn.prepareStatement(deleteString);// creating an executable sql statement
        deleteStatement.executeUpdate();// executing the sql statement

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

    // method for deleting an entire talbe
    public void dropTable() throws SQLException{
        // creating the sql string that represents deleting a table
        // syntax: DROP TABLE tableName
        String dropString = "DROP TABLE "+TABLE_NAME; // in SQL drop means deleting an entire entity like a table or a whole database
        PreparedStatement dropStatement = this.conn.prepareStatement(dropString);// creating an executable sql statement
        dropStatement.executeUpdate();// executing the sql statement

        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);
        }
    }
}