How do I connect my Java Application to a MySQL database server? A question i've heard all too often from Beginner Java programmers. I'll be introducing you to how to get this done in no time. To demonstrate, we'll be creating a simple login application where the user has to authenticate before being allowed access to the application. We'll be using Swing as our GUI kit for this one.
Requirements
- A database server (MYSQL)
- Your favourite text editor
- Basic Java and Swing Knowledge (Obviously)
- This post
Setup
First things first, as always, we'll need to setup out database and create tables and records. Here is the schema for the database, of course, you could create this directly from PHPMyAdmin on your browser but hey, let's be cool kids.CREATE DATABASE remotejava; -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL, `email` varchar(50) NOT NULL, `password` varchar(255) NOT NULL, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `age` int(11) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `email`, `password`, `first_name`, `last_name`, `age`) VALUES (1, 'javarepublic@java.io', 'javarepublic', 'Java', 'Republic', 1); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`);To make things easier, just save the script as a
.sql file and import from PHPMyAdmin. Yeah, I know, you're getting antsy to code, let's get to it then.
The Code
Fire up your IDE and create a new project, i'm calling mineRemoteCombo just because. Now create a Login.java class and design a simple form with a JTextField and a JPasswordField for a email and password fields respectively. If you want something a little bit fancy, here is one I designed for this post.
Login.java
package remotecombo;
import java.awt.*;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.sql.*;
import java.util.Calendar;
import net.miginfocom.swing.MigLayout;
public final class Login extends JFrame {
private final DBUtils dbUtils;
private JPanel contentPane;
public Login() {
super("Database Connection");
setUndecorated(true);
try {
UIManager.setLookAndFeel("javax.swing.plaf.nimbus.NimbusLookAndFeel");
} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | UnsupportedLookAndFeelException ex) {
}
dbUtils = new DBUtils();
createLoginUI();
}
private void createLoginUI() {
contentPane = new JPanel();
contentPane.setLayout(new BorderLayout());
contentPane.setOpaque(true);
JPanel side = new JPanel(new MigLayout("fillx, filly"));
JPanel center = new JPanel(new MigLayout("fillx, insets 100 10 10 10", "[][][]", "[][]60[]20[]20[60::100]50[]"));
center.setBackground(new Color(38, 50, 56));
side.setBackground(new Color(45, 46, 47));
side.setPreferredSize(new Dimension(250, 650));
JLabel backName = new JLabel("Java to MySQL");
backName.setFont(backName.getFont().deriveFont(Font.BOLD, 25));
backName.setForeground(Color.WHITE);
side.add(backName, "gapleft 35, center");
JLabel appLabel = new JLabel("Connect a Java App to MySQL");
JLabel message = new JLabel();
message.setText("
In order to use this app, you must login
"
+ "with your email address and password.
");
message.setFont(message.getFont().deriveFont(Font.BOLD, 12));
appLabel.setFont(new Font("Arial", Font.BOLD, 30));
appLabel.setForeground(Color.WHITE);
message.setForeground(Color.WHITE);
JTextField userEmail = new JTextField(30);
userEmail.setToolTipText("Enter your email address");
JPasswordField userPass = new JPasswordField(30);
JLabel emailLabel = new JLabel("E-Mail: ", JLabel.TRAILING);
JLabel userPassLabel = new JLabel("Password: ", JLabel.LEADING);
emailLabel.setForeground(Color.WHITE);
emailLabel.setFont(message.getFont().deriveFont(Font.BOLD, 12));
userPassLabel.setForeground(Color.WHITE);
userPassLabel.setFont(message.getFont().deriveFont(Font.BOLD, 12));
ImageIcon loginIcon = new ImageIcon(Login.class.getResource("/images/Login.png"));
ImageIcon exitIcon = new ImageIcon(Login.class.getResource("/images/Exit.png"));
JButton loginBtn = new JButton("Login", loginIcon) {
{
setPreferredSize(new Dimension(100, 40));
//setMaximumSize(getSize());
}
};
JButton exitBtn = new JButton("Exit", exitIcon) {
{
setPreferredSize(new Dimension(100, 40));
//setMaximumSize(getSize());
}
};
exitBtn.addActionListener((ActionEvent e) -> {
int exitCode = JOptionPane.showConfirmDialog(this, "Are you sure you want to exit?", "Exit Confirmation", JOptionPane.OK_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE);
if (exitCode == 0) {
System.exit(0);
}
});
loginBtn.addActionListener((ActionEvent e) -> {
String email = userEmail.getText();
String pass = new String(userPass.getPassword());
if (email.isEmpty()) {
userEmail.setBorder(BorderFactory.createLineBorder(Color.RED));
userEmail.requestFocusInWindow();
JOptionPane.showMessageDialog(this, "E-Mail address cannot be empty", "Authentication Error", JOptionPane.ERROR_MESSAGE);
return;
}
if (pass.isEmpty()) {
userPass.setBorder(BorderFactory.createLineBorder(Color.RED));
userPass.requestFocusInWindow();
JOptionPane.showMessageDialog(this, "Password cannot be empty", "Authentication Error", JOptionPane.ERROR_MESSAGE);
return;
}
try {
if (dbUtils.authenticateUser(email, pass)) {
JOptionPane.showMessageDialog(this, "Authentication Successful! Click OK to Continue", "Login Successful", JOptionPane.INFORMATION_MESSAGE);
new App(userEmail.getText());
this.setVisible(false);
} else {
JOptionPane.showMessageDialog(this, "Email or Password is Incorrect!", "Authentication Error", JOptionPane.ERROR_MESSAGE);
}
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, "Connection Error!, Please check that you have a working internet connection and try again.", "Connection Error", JOptionPane.ERROR_MESSAGE);
}
});
JLabel copyR = new JLabel();
copyR.setText("© " + Calendar.getInstance().get(Calendar.YEAR) + " Java Republic");
copyR.setFont(message.getFont().deriveFont(Font.BOLD, 12));
copyR.setForeground(Color.WHITE);
center.add(appLabel, "span, center, wrap");
center.add(message, "span, center, wrap");
center.add(emailLabel, "gapleft 35, skip, left, sg 1, split2");
center.add(userEmail, "wrap");
center.add(userPassLabel, "gapleft 35, skip, left, sg 1, split2");
center.add(userPass, "wrap");
center.add(loginBtn, "span, center, split2");
center.add(exitBtn, "wrap");
center.add(copyR, "gaptop 130, span, right, wrap");
getRootPane().setDefaultButton(loginBtn);
contentPane.add(side, BorderLayout.WEST);
contentPane.add(center, BorderLayout.CENTER);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setContentPane(contentPane);
setPreferredSize(new Dimension(800, 600));
pack();
setLocationRelativeTo(null);
setResizable(false);
setVisible(true);
}
}
Whoa! That was something. If you don't understand the code above don't worry much about it, just focus on how to get your application to interact with a database server, remote or local.
Connecting to MySQL
For us to be able to connect to MySQL Server we must first obtain a MySQL database driver (mysql-connector-java) and import it in our application's CLASSPATH (Yeah, it's not as fancy as it sounds). You can get your copy HERE and do the following to include it in your project.- Right click on your project name, in this case
RemoteComboand select properties. - Under the categories, select libraries and click on "Add JAR/folder" as seen in the image
- Once the file dialog pops up, navigate to where you saved the downloaded mysql-connector-java-x.xx file and select it
- Click OK to save the changes and return back to the project
Ok, so now that we have the MySQL database driver for Java added to our project, let's create a class where we will store our database connection and query functions. While creating a separate class for this (small projects) might be an overkill, it certainly is a must for projects where you have to execute a large amount of queries and it also promotes re-usability and I don't have to tell you about the maintenance advantage. So here is the class:
DBUtils.java
package remotecombo;
import java.sql.*;
import javax.swing.JOptionPane;
/**
*
* @author OROBOGENIUS
* Powered By: GenTech
*/
public class DBUtils {
private final String DRIVER = "com.mysql.jdbc.Driver"; //The database driver
private Connection conn; //The database connection
public Connection getConnection() {
try {
Class.forName(DRIVER).newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/remotejava", "java", "javarepublic");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "Connection Error!, Please check that you have a working internet connection and try again..\n", "Connection Error", JOptionPane.ERROR_MESSAGE);
}
return conn;
}
public boolean authenticateUser(String email, String password) throws SQLException {
ResultSet rs; //The returned result
try (PreparedStatement ptmt = getConnection().prepareStatement("SELECT * FROM users WHERE email = ? AND password = ?")){
ptmt.setString(1, email);
ptmt.setString(2, password);
rs = ptmt.executeQuery();
if (rs.next()){
return true;
}
} catch (SQLException sqlE){
JOptionPane.showMessageDialog(null, "Connection Error!, Please check that you have a working internet connection and try again..", "Connection Error", JOptionPane.ERROR_MESSAGE);
}
return false;
}
/**
* Close the connection to the database
* @throws SQLException
*/
public void closeConnection() throws SQLException{
this.conn = null;
this.conn.close();
}
}
This class helps us create and establish a connection to our database server. It has just three functions; getConnection, authenticateUser and closeConnection. Of particular interest to us are the first two functions. The first function creates and establishes a connection with the database by doing the following:
- Specify the driver to use for the connection
- Specify the connection parameters; which are, the connection URL (host name and database), the username and the password
private final String DRIVER = "com.mysql.jdbc.Driver"; //The database driver
try {
Class.forName(DRIVER).newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/remotejava", "java", "javarepublic");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "Connection Error!, Please check that you have a working internet connection and try again..\n", "Connection Error", JOptionPane.ERROR_MESSAGE);
}
Now all we need to do if we want to connect to the database is call the getConnection() function to obtain a handle to the database.
Going back to our Login.java class which is where we authenticate the user on click of the login button, we see the call to the authenticateUser() function that accepts two arguments, the email address and the password. Of course, the way you authenticate users on your application might differ but the base idea still holds.
DBUtils dbUtils = new DBUtils();
try {
if (dbUtils.authenticateUser(email, pass)) {
JOptionPane.showMessageDialog(this, "Authentication Successful! Click OK to Continue", "Login Successful", JOptionPane.INFORMATION_MESSAGE);
} else {
JOptionPane.showMessageDialog(this, "Email or Password is Incorrect!", "Authentication Error", JOptionPane.ERROR_MESSAGE);
}
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, "Connection Error!, Please check that you have a working internet connection and try again.", "Connection Error", JOptionPane.ERROR_MESSAGE);
}
We first create an object of the database utility class, DBUtils and use the object to call the authenticateUser() function with the email and password passed to it as parameters. Let's see a breakdown of the authenticateUser() function:
public boolean authenticateUser(String email, String password) throws SQLException {
ResultSet rs; //The returned result
try (PreparedStatement ptmt = getConnection().prepareStatement("SELECT * FROM users WHERE email = ? AND password = ?")){
ptmt.setString(1, email);
ptmt.setString(2, password);
rs = ptmt.executeQuery();
if (rs.next()){
return true;
}
} catch (SQLException sqlE){
JOptionPane.showMessageDialog(null, "Connection Error!, Please check that you have a working internet connection and try again..", "Connection Error", JOptionPane.ERROR_MESSAGE);
}
return false;
}
The function is a simple one, it checks if the user with the specified email address and password exists in the database and returns true or false. First we create a ResultSet object, rs, that will hold the result that will be returned from the database query. A ResultSet object maintains a cursor that points to the current row in the result set. To know more about the ResultSet interface click HERE.
We then get a connection to our database (by calling the getConnection()) method and execute our query.
PreparedStatement ptmt = getConnection().prepareStatement("SELECT * FROM users WHERE email = ? AND password = ?")
The code above gets a connection and prepares an SQL statement to be executed by the database server. You will notice we are using PreparedStatement which is a subset of the Standard SQL Statement. We choose it for security reasons.
The Connection.prepareStatement() for preparedStatements or Connection.createStatement() for plain old SQL Statements function returns a statement upon which we can execute our query. In effect, we're executing a SELECT statement in SQL and getting a ResultSet result from the query. Once we have the PreparedStatement we add the parameters, which are the email address and the password respectively to the statement before we execute it. HERE is a perfect guide on PreparedStatements and how to use them.
Next we execute the query using
ptmt.executeQuery() which returns the result from the query. We then check if the SELECT query returned any result, if it did, then the user's record exists and matches the record in the database, we return true or false otherwise.
Here is the resulting application we just created.
The complete code for this post is available on GITHUB.
EmoticonEmoticon