JDBC and MySQL

From ArchWiki
Revision as of 13:39, 25 May 2018 by ASystemOverload (talk | contribs) (→‎Installation: Added 'bind-address = *' as an alternate to skip-networking as this is present on my brand new install of MariaDB (MySQL) and bind-address is also referenced on the main MySQL Page)
Jump to navigation Jump to search

This document describes how to set up your Arch system so that MySQL databases can be accessed via Java programs.

Installation

Installing MySQL

Install a MySQL implementation.

To allow for network access, make sure that /etc/mysql/my.cnf has the following line commented out, as shown here:

#skip-networking

Your MySQL version may use the following line instead to restrict network access:

bind-address = *

Then, start the MySQL service.

Installing JDBC

Install a JDBC driver according to your MySQL variant:

If you use the AUR packages, you will need to link the driver(s) to your JRE's external libraries directory, as follows:

For mariadb-jdbc:

# ln -s /usr/share/java/mariadb-jdbc/mariadb-java-client.jar /usr/lib/jvm/default-runtime/jre/lib/ext

For mysql-jdbc:

# ln -s /usr/share/java/mysql-jdbc/mysql-connector-java-bin.jar /usr/lib/jvm/default-runtime/jre/lib/ext

Testing

To access MySQL's command line tool, run:

$ mysql

Creating the test database

The following commands create a database test, and grant all privileges to user foo identified by password bar. Change the variables at your discretion.

create database test;
grant all privileges on test.* to user@localhost identified by "bar";
flush privileges;

Afterwards, use Ctrl + d to exit the command line tool.

Creating the test program

Use a text editor to create the file DBDemo.java with the following code in it. You will need to change the username and password accordingly.

import java.sql.*;

public class DBDemo {
  public static void main(String[] args) throws SQLException, ClassNotFoundException {
    // Load the JDBC driver
    Class.forName("org.mariadb.jdbc.Driver");
    System.out.println("Driver loaded");

    // Try to connect
    Connection connection = DriverManager.getConnection
      ("jdbc:mysql://localhost/test", "foo", "bar");

    System.out.println("It works!");

    connection.close();
  }
}

If using Oracle MySQL (as opposed to MariaDB), the above class name should be set to com.mysql.jdbc.Driver.

Running the program

To compile and run the program, execute:

You will need jre8-openjdk to execute javac located at /usr/bin/javac

$ javac DBDemo.java
$ java DBDemo

If all was configured correctly, you should see:

Driver loaded
It works!