Scroll To Top

Using JDBC to establish a database connection – MySQL

Posted in JAVA, Programming4 years ago • Written by Clara RoseNo Comments

Using JDBC to establish a database connection – MySQL

Everybody knows that now days a communication with a database is a must, but how can you do this, if you are using JAVA and MySQL?

Don’t worry! Once again we are here to help!

For this tutorial we assume that you have basic java knowledge.

Ok, let’s get things in order. You will need to download the MySQL connector from HERE

Once you have the connector – you need to include it to your project and once this is done here is our Connector class:

Connector.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connector {
// Using JDBC to establish a database connection - MySQL
    public Connection conn = null;
    
    private String dbName = "database_name";
    private String dbUsername = "database_user";
    private String dbPassword = "database_password";

    public Connector() {
    }

    public Connection connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance(); // Do not forget it!
            conn = DriverManager.getConnection("jdbc:mysql://your_website_or_server_IP_here:3306/"+dbName, dbUsername, dbPassword);
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException ex) {
            logger.log(ex.toString());
        }
        return conn;
    }

    public void disconnect() {
        try {
            conn.close();
        } catch (Exception e) {
            logger.log(e.toString());
        }
    }
}

 

The above code is fairly simple – it just creates a connection to our database. It also contains a class that we will use to close the database connection.

So, what is next? How are we going to implement this class and the contained methods? Let’s try it in our main method!

First of all let’s assume that we have a database table named “users” containing 3 fields – “id”,  “first_name”, “last_name” and we would like to select all the records. I will use JSON for this example, as I find it the easiest way to manipulate data.

Here is how to do this:

Main.java

//Using JDBC to establish a database connection - MySQL
public class Main {
     public static void main(String[] args) throws Exception {
         private final Connector connector = new Connector(); // Connector instance
         private PreparedStatement preparedStatement = null; // Prepared statement declaration
         private ResultSet resultSet = null; // Result set declaration

         final Connection connection = connector.connect(); // Here you are creating the connection
         JSONArray result = new JSONArray();

         final String query = "SELECT id, first_name, last_name FROM users";

        try {
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.execute();
            resultSet = preparedStatement.getResultSet();

            while (resultSet.next()) {
                JSONObject singleRowObj = new JSONObject();
                singleRowObj.put("id", resultSet.getString(1));
                singleRowObj.put("first_name", resultSet.getString(2));
                singleRowObj.put("last_name", resultSet.getString(3));

                result.put(singleRowObj);
            }

        } catch (SQLException | JSONException ex) {
             // Ignore, or do something
        } finally {
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }
         System.out.println(result); // Print, or return the result
        }
}
// Using JDBC to establish a database connection - MySQL

 

Congratulations! Now you can select data from your database! Can we try to insert a row in the same table? Yes we can!

I will be using a prepared statement, as it is taking care of the SQL injection threats.

Here is a class that will help you out:

// Using JDBC to establish a database connection - MySQL
public boolean insertNewRow(String id, String first_name, String last_name) {
        boolean isInserted = false; // This little fellow will let us know, if everything went well
        final Connection connection = connector.connect();

        try {
            preparedStatement = connection.prepareStatement(newRommQuery); // Prepare a statement and set the variables
            preparedStatement.setString(1, id);
            preparedStatement.setString(2, first_name);
            preparedStatement.setString(3, last_name);

            preparedStatement.execute(); // execute it

            isInserted = true; // everything is ok
        } catch (Exception e) {
             // Something went wrong
        }
        return isInserted; // Let your method know, if the query is executed
    }

// Using JDBC to establish a database connection - MySQL

 

In this tutorial you learned how to create 2 methods that can be used anywhere to insert, or fetch data from your database. In the next tutorial I will teach you how to delete and update a row.

 

Article publié pour la première fois le 14/09/2015

Leave a Reply

Your email address will not be published. Required fields are marked *

Design Racy
DesignRacy is an online design blog focussing on design, web development, wordpress and photoshop.

For the latest articles, subscribe to our feed and like us on Facebok.



Copyright 2015 - DesignRacy.com

Recent Posts

daily November 2, 2019, Comments Off on daily
designblog November 2, 2019, Comments Off on designblog
Shares

WAIT BEFORE YOU GO
GET FREE THEMES AND DESIGN

SUBSCRIBE TO OUR MAILING LIST AND GET NOTIFIED EVERY MONTH WHEN NEW FREEBIES AND TUTORIALS BECOME AVAILABLE.