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
[code language=”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());
}
}
}
[/code]
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
[code language=”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
[/code]
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:
[code language=”java”]
// 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
[/code]
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.
