import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



public class DBConnection {

    Connection connection;
    Statement statement;
    //1. url
    String url = "jdbc:mysql://127.0.0.1:3306/world?user=root&password=root";
    String join = "SELECT city.Name, country.Name FROM city,country WHERE city.countryCode = country.code AND city.Name = '";
    public DBConnection(){
        try {
            //2. to connect to DB
            connection =(Connection) DriverManager.getConnection(url);
            //3. create statement
            statement = (Statement) connection.createStatement();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            System.out.println("Kopplingen till DB är felaktig");
        }
    }
     public void searchForCity(String name)
        {
        try {
            PreparedStatement ps = connection.prepareStatement("SELECT name, population FROM city WHERE Name = ?");
            ps.setString(1, name);
            ps.executeQuery();
            ResultSet rs = ps.executeQuery();
            //ResultSet rs = statement.executeQuery(join + name + "'");
            //4. execute Query
            //ResultSet result = statement.executeQuery("SELECT name, population FROM city WHERE Name = '" + name + "'");

            //5. while om man behöver fler satser, ingen while om vi inte har fler satser
            while(rs.next()){
                //System.out.println("The city " + result.getString(1) +  " is in the DB and the population is: " + result.getInt(2));
                //System.out.println("The city " + rs.getString(1) +  " is in country: " + rs.getString(2));

            //Alternativ sätt att skriva ovan kod
            System.out.println("The city " + rs.getString("Name") +  " is in the DB and the population is " + rs.getInt("Population"));
            }
        } catch (SQLException ex) {
            System.out.println("Fel i SQL satsen");
        }
        }