javafx connection to mysql [closed]

we are building javafx application which will be presenting information about stocks. Here is the website: http://analiza.host-ed.me/

But we've got a huge problem. Every free hosting doesn't allow remote mysql connection. And there is my question. When our site is on the server (which i linked) is this remote connection or local connection? When we put this javafx app as a site it can't connect like it was on the local machine... Is there any solution? Thanks for help. (we need to use free hosting, because it's only a school project..)

You can access MySQL from JavaFX. But JavaFX runs on a client and something like php usually runs on a server. You will need a connection from your java app to MySQL. As your hosting provider won't allow you to directly connect to the database port from your Java Client App, you will need some other way to connect.

You could tunnel through port 80, you could run a servlet (or php server code, etc) to intercept incoming traffic and proxy database calls through a HTTP based REST interface or you could install the DB locally on the client.

I'm going to assume, for a school project, it's ok for each client machine to have it's own database. In which case, instead of using MySQL, use a lightweight Java database like H2, bundle it with your app by including it's jar as a dependent library, package the app plus DB jar as a signed WebStart application using the JavaFX packaging tools and host the files generated by the packaging tools at your hosting provider.

Update

Here is a sample application which uses a local H2 database on the client computer.

import java.sql.*;
import java.util.logging.*;
import javafx.application.Application;
import javafx.collections.*;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.layout.*;
import javafx.stage.Stage;

public class H2app extends Application {
  private static final Logger logger = Logger.getLogger(H2app.class.getName());
  private static final String[] SAMPLE_NAME_DATA = { "John", "Jill", "Jack", "Jerry" };

  public static void main(String[] args) { launch(args); }

  @Override public void start(Stage stage) {
    final ListView<String> nameView = new ListView();

    final Button fetchNames = new Button("Fetch names from the database");
    fetchNames.setOnAction(new EventHandler<ActionEvent>() {
      @Override public void handle(ActionEvent event) {
        fetchNamesFromDatabaseToListView(nameView);
      }
    });

    final Button clearNameList = new Button("Clear the name list");
    clearNameList.setOnAction(new EventHandler<ActionEvent>() {
      @Override public void handle(ActionEvent event) {
        nameView.getItems().clear();
      }
    });

    VBox layout = new VBox(10);
    layout.setStyle("-fx-background-color: cornsilk; -fx-padding: 15;");
    layout.getChildren().setAll(
      HBoxBuilder.create().spacing(10).children(
        fetchNames, 
        clearNameList    
      ).build(),      
      nameView
    );
    layout.setPrefHeight(200);

    stage.setScene(new Scene(layout));
    stage.show();
  }

  private void fetchNamesFromDatabaseToListView(ListView listView) {
    try (Connection con = getConnection()) {
      if (!schemaExists(con)) {
        createSchema(con);
        populateDatabase(con);
      }
      listView.setItems(fetchNames(con));
    } catch (SQLException | ClassNotFoundException ex) {
      logger.log(Level.SEVERE, null, ex);
    }
  }

  private Connection getConnection() throws ClassNotFoundException, SQLException {
    logger.info("Getting a database connection");
    Class.forName("org.h2.Driver");
    return DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
  }

  private void createSchema(Connection con) throws SQLException {
    logger.info("Creating schema");
    Statement st = con.createStatement();
    String table = "create table employee(id integer, name varchar(64))";
    st.executeUpdate(table);
    logger.info("Created schema");
  }

  private void populateDatabase(Connection con) throws SQLException {
    logger.info("Populating database");      
    Statement st = con.createStatement();      
    int i = 1;
    for (String name: SAMPLE_NAME_DATA) {
      st.executeUpdate("insert into employee values(i,'" + name + "')");
      i++;
    }
    logger.info("Populated database");
  }

  private boolean schemaExists(Connection con) {
    logger.info("Checking for Schema existence");      
    try {
      Statement st = con.createStatement();      
      st.executeQuery("select count(*) from employee");
      logger.info("Schema exists");      
    } catch (SQLException ex) {
      logger.info("Existing DB not found will create a new one");
      return false;
    }

    return true;
  }

  private ObservableList<String> fetchNames(Connection con) throws SQLException {
    logger.info("Fetching names from database");
    ObservableList<String> names = FXCollections.observableArrayList();

    Statement st = con.createStatement();      
    ResultSet rs = st.executeQuery("select name from employee");
    while (rs.next()) {
      names.add(rs.getString("name"));
    }

    logger.info("Found " + names.size() + " names");

    return names;
  }
}

There is a corresponding NetBeans project for this sample which will generate a deployable application. The project can be tested in webstart and applet mode.

For the sample, the database is stored on the user's computer (not the server from which the application was downloaded) and persists between application runs.

The exact location depends on the jdbc connection initialization string. In the case of my sample the database goes in the user's directory jdbc:h2:~/test, which is OS and User specific. In the case of me for Windows it ends up at C:\Users\john_smith\test.h2.db. Using a jdbc connection string such as jdbc:h2:~/test is preferable to a string such as jdbc:h2:C:\\Baza because a string with C:\\ in it is platform specific and won't work well on non-windows systems. For further information on h2 jdbc connection strings refer to the connections settings in the h2 manual.

The h2 system works such that if the database file already exists, it is reused, otherwise a new database file is created. If you modify the database, shut the application down, then load the application again a week later, it is able to read the data created the week before.