Difference between revisions of "JDBC and MySQL"

From ArchWiki
Jump to: navigation, search
(Installing MySQL)
(Cleaned up the example Java code even more for simplicity)
 
(6 intermediate revisions by 6 users not shown)
Line 1: Line 1:
 
[[Category:Development]]
 
[[Category:Development]]
This document describes how to set up your Arch system so MySQL Databases can be accessed via Java programs.
+
This document describes how to set up your Arch system so that MySQL databases can be accessed via Java programs.
  
 
== Installation ==
 
== Installation ==
  
===Installing MySQL===
+
=== Installing MySQL ===
  
[[pacman|install]] package {{pkg|mysql}} available in the [[Official Repositories]].
+
[[Install]] a [[MySQL]] implementation.
  
There are now two "fixes" you have to make. Firstly edit the file {{ic|/etc/mysql/my.cnf}} to allow network access. Find the line with skip_networking in and comment it out so it looks like this
+
To allow for network access, make sure that {{ic|/etc/mysql/my.cnf}} has the following line commented out, as shown here:
  
 
  #skip-networking
 
  #skip-networking
  
Finally start mysql up with :
+
Then, start the MySQL [[daemon|service]].
  
# /etc/rc.d/mysqld start
+
=== Installing JDBC ===
  
===Installing JDBC===
+
Install a JDBC driver according to your MySQL variant:
JDBC [[PKGBUILD]] is currently in [[AUR]] repository.
+
* {{AUR|mariadb-jdbc}} - for the Arch Linux endorsed server
 +
* {{AUR|mysql-jdbc}} - for the Oracle variant
  
You can also download it from http://www.mysql.com/products/connector-j/ and
+
You can also download the latter from http://www.mysql.com/products/connector-j/, followed by running:
  ( x=mysql-connector-java-*-bin.jar; install -D $x /opt/java/jre/lib/ext/${x##*/} )
+
  ( x=mysql-connector-java-*-bin.jar; install -D $x /usr/lib/jvm/default/jre/lib/ext/${x##*/} )
  
==Testing==
+
If you use the AUR packages, you will need to link the driver(s) to your JRE's external libraries directory, as follows:
  
To access mysql's command line tool,  
+
For mariadb-jdbc:
 +
# ln -s /usr/share/java/mariadb-jdbc/mariadb-java-client.jar /usr/lib/jvm/default/jre/lib/ext/
 +
 
 +
For mysql-jdbc:
 +
# ln -s /usr/share/java/mysql-jdbc/mysql-connector-java-bin.jar /usr/lib/jvm/default/jre/lib/ext/
 +
 
 +
== Testing ==
 +
 
 +
To access MySQL's command line tool, run:
  
 
  $ mysql
 
  $ mysql
  
===Creating the test database===
+
=== Creating the test database ===
  
The following commands create a database and allow a user 'paulr'. You will need to change the user name to whatever yours is.
+
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 emotherearth;
+
  create database ''test'';
  grant all privileges on emotherearth.* to paulr@localhost identified by "paulr";
+
  grant all privileges on ''test''.* to ''user''@localhost identified by "''bar''";
 
  flush privileges;
 
  flush privileges;
  
Now press Ctrl+D to exit the command line tool.
+
Afterwards, use {{ic|Ctrl + d}} to exit the command line tool.
 +
 
 +
=== Creating the test program ===
 +
 
 +
Use a text editor to create the file {{ic|DBDemo.java}} with the following code in it. You will need to change the username and password accordingly.
 +
 
 +
{{bc|1=
 +
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''");
  
===Creating the test program===
+
    System.out.println("It works!");
  
Use an editor to create a file DBDemo.java with the following code in it. You will need to change the username and password appropriately.
+
    connection.close();
 +
  }
 +
}
 +
}}
  
import java.sql.*;
+
If using Oracle MySQL (as opposed to MariaDB), the above class name should be set to {{ic|com.mysql.jdbc.Driver}}.
import java.util.Properties;
+
public class DBDemo
+
{
+
  // The JDBC Connector Class.
+
  private static final String dbClassName = "com.mysql.jdbc.Driver";
+
  // Connection string. emotherearth is the database the program
+
  // is connecting to. You can include user and password after this
+
  // by adding (say) ?user=paulr&password=paulr. Not recommended!
+
  private static final String CONNECTION =
+
                          "jdbc:mysql://127.0.0.1/emotherearth";
+
  public static void main(String[] args) throws
+
                            ClassNotFoundException,SQLException
+
  {
+
    System.out.println(dbClassName);
+
    // Class.forName(xxx) loads the jdbc classes and
+
    // creates a drivermanager class factory
+
    Class.forName(dbClassName);
+
    // Properties for user and password. Here the user and password are both 'paulr'
+
    Properties p = new Properties();
+
    p.put("user","paulr");
+
    p.put("password","paulr");
+
    // Now try to connect
+
    Connection c = DriverManager.getConnection(CONNECTION,p);
+
    System.out.println("It works !");
+
    c.close();
+
    }
+
}
+
  
===Running the Program===
+
=== Running the program ===
  
To compile and run the program enter
+
To compile and run the program, execute:
  
  javac DBDemo.java
+
  $ javac DBDemo.java
  java DBDemo
+
  $ java DBDemo
  
and hopefully, it should print out
+
If all was configured correctly, you should see:
  
  This is the database connection
+
  Driver loaded
com.mysql.jdbc.Driver
+
  It works!
  It works !
+

Latest revision as of 16:38, 1 October 2016

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

Then, start the MySQL service.

Installing JDBC

Install a JDBC driver according to your MySQL variant:

You can also download the latter from http://www.mysql.com/products/connector-j/, followed by running:

( x=mysql-connector-java-*-bin.jar; install -D $x /usr/lib/jvm/default/jre/lib/ext/${x##*/} )

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/jre/lib/ext/

For mysql-jdbc:

# ln -s /usr/share/java/mysql-jdbc/mysql-connector-java-bin.jar /usr/lib/jvm/default/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:

$ javac DBDemo.java
$ java DBDemo

If all was configured correctly, you should see:

Driver loaded
It works!