Connect Java To Online Databases

Connecting a program to a database is a very common task. Luckily Java includes a handy package called JDBC which allows us to do just that. This post will walk you through the process of setting up and connecting to a MySQL database from a java program. Lets get started.

• • First things first, we need to create a database to connect to. Typically this would be designed specifically for the program you are connecting to it. However for the purposes of this lesson we will create an arbitrary database we can connect to. Side note: thie post is not intended to teach you how to create a database using SQL. We will simply be using phpmyadmin. For this example I will use freemysqlhosting because it lets you create a database for free and use it for 30 days. Afterwards it is $20 per year. So go to the site and create an account.
  • Next we need to create our actual database. Simply click the button that says “Create Database”. You will want to make a note of the password you set for the database. After that click the link that says go to phpmyadmin. Go ahead and login using the server, database name, and password.

• • Now we need to create a table. This is the actual place the data will be stored.
    

    Super short MySQL introduction: Tables are made up of rows and columns. Every table must contain a column which is the “Primary Key” for that table. Primary keys MUST be unique. This is the column the table uses to lookup data. A good way to be sure the PK (primary key) is always unique is to make it an auto-incrementing integer. Meaning each item added will be assigned a value that increases for each new item added.

    Ok, go ahead and click “New” to make a new table. For this example I’m going to make a database to store price information for various cryptocurrencies. I’ll name the table “coins”. Your page should look like this:

• • We need to add a few fields to this table. The first field will be “entryID”, and we want to make this value an “INT”, set its length to 11, declare it as the primary key under the “index” drop down menu, and finally click the check box that says “A_I” (for Auto Increment). Good, we will add a few more columns before we finish here. The next columns in order will be: “uuid” (the unique identifier for each coin), “symbol” (the abbreviation of the coins name), “name”, “price” and “date”.Once you have added all the columns it should look like this:
    
    Click the button at the bottom right corner of the screen that says “go” and you should see this:
    
  • Note: There is an additional .jar file you will need to download. You can get it from this website. The file is called mysql-connector-java-xxxx-zip. You need to select “Platform Independent” from the dropdown. This gives you the option to download just the .zip file. Then you can download and extract the .jar file to a local file in your project then add it you the projects library.
  • Awesome, the table is complete. Now open up your IDE and create a new java project. Inside the “src” folder create a new java class called “ConnectToDatabase.java”. Inside this new class we’ll need a few imports:

  • import java.sql.Date;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    import java.sql.Connection;
    import java.sql.DriverManager;

     

    Inside the class we will make a private Connection variable conn and a constructor which will open a connection to the database each time this class is instantiated. Inside the constructor open up a try-catch block.

  • private Connection con;
    public ConnectToDatabase() {
    try {
    
    } catch (ClassNotFoundException | SQLException ex) {
    Logger.getLogger(ConnectToDatabase.class.getName()).log(Level.SEVERE, null, ex);
    }
    }

     

  • Now, inside the try block we need to open the connection. the basic format of the connection is this: “jdbc:mysql://server-name:port”, “database-name”, “password”.

  • try {
    Class.forName("com.mysql.jdbc.Driver");
    this.con = DriverManager.getConnection("jdbc:mysql://sql9.freemysqlhosting.net:3306/sql9364184", "sql9364184", "*********");
    System.out.println("DB connection successful");
    } catch (ClassNotFoundException | SQLException ex) {
    Logger.getLogger(ConnectToDatabase.class.getName()).log(Level.SEVERE, null, ex);
    }

     

  • This creates a connection object and stores it in the “conn” variable. Now we need a method to insert the data to the table. Create a new method called coinDatabase and add parameters for each value associated with a single cryptocurrency. Like this:

  • public void coinDatabase(String _uuid, String _symbol, String _name, String _price, Date _date) {
    
    }

    Note: It is always recommended to use prepared statements when adding data to and getting data from a database. This will clean the data of any “bad” characters preventing SQL injection attacks.

  • To implement the prepared statements we can first create an sql query with the table’s column names then use question marks “?” to represent each value we want to insert. The code looks like this:
    


  • public void coinDatabase(String _uuid, String _symbol, String _name, double_price, Date _date) {
    try {
    // Insert statement, using prepared statements
    String query = " insert into coins (uuid, symbol, name, price, date)"
    + " values (?, ?, ?, ?, ?)";
    // create the mysql insert preparedstatement
    PreparedStatement preparedStmt = this.con.prepareStatement(query);
    preparedStmt.setString(1, _uuid);
    preparedStmt.setString(2, _symbol);
    preparedStmt.setString(3, _name);
    preparedStmt.setDouble(4, newPrice);
    preparedStmt.setDate(5, _date);
    // execute the preparedstatement
    preparedStmt.execute();
    } catch (Exception ex) {
    ex.printStackTrace();
    }
    }

     

  • Great! Now we just need a method that will get data from the database. The idea is similar to the previous method. This is code to get the price of a goin if given the coins name.
    


  • public int getPriceFromName(String _name) {
    double price = 0;
    try {
    // Insert statement, using prepared statements
    String query = "SELECT * FROM coins WHERE name = ?";
    // create the mysql insert preparedstatement
    PreparedStatement preparedStmt = this.con.prepareStatement(query);
    // Add value to statement
    preparedStmt.setString(1, _name);
    // Save result
    ResultSet result = preparedStmt.executeQuery(query);
    // Loop over results
    while(result.next()) {
    price = result.getDouble("price");
    }
    } catch (SQLException ex) {
    System.out.println(ex);
    }
    return price;
    }

     

  • Thats just about it. To call the database connection class, create an instance of the class in your main method. You can then call coinDatabase() and add some data to the table. This is an example of what it looks like when its got some stuff in it:
    If you want to see the full code, check out my project called “CoinTrackPrototype” on my github page.

Thanks for reading!!