Difference between revisions of "JDBC and MySQL"

From ArchWiki
Jump to: navigation, search
m (Style fixes)
(One intermediate revision by one other user not shown)
Line 4: Line 4:
 
== Installation ==
 
== Installation ==
  
===Installing MySQL===
+
=== Installing MySQL ===
  
[[pacman|install]] package {{pkg|mysql}} available in the [[Official Repositories]].
+
[[pacman|Install]] a [[MySQL]] implementation.
  
There are now two "fixes" you have to make. Firstly edit the file {{ic|/etc/my.conf}} to allow network access. Find the line with skip_networking in and comment it out so it looks like this
+
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:
  
 
  #skip-networking
 
  #skip-networking
  
Finally start mysql up with :
+
Finally start ''mysql'' [[daemon|service]] up.
  
# /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/ and  
 
  ( x=mysql-connector-java-*-bin.jar; install -D $x /opt/java/jre/lib/ext/${x##*/} )
 
  ( x=mysql-connector-java-*-bin.jar; install -D $x /opt/java/jre/lib/ext/${x##*/} )
  
==Testing==
+
== Testing ==
  
To access mysql's command line tool,  
+
To access MySQL's command line tool,  
  
 
  $ 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 and allow a user ''user''. You will need to change the user name to whatever yours is.
  
 
  create database emotherearth;
 
  create database emotherearth;
  grant all privileges on emotherearth.* to paulr@localhost identified by "paulr";
+
  grant all privileges on emotherearth.* to ''user''@localhost identified by "''user''";
 
  flush privileges;
 
  flush privileges;
  
Now press Ctrl+D to exit the command line tool.
+
Now press {{ic|Ctrl+d}} to exit the command line tool.
  
===Creating the test program===
+
=== Creating the test program ===
  
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.
+
Use an editor to create a file {{ic|DBDemo.java}} with the following code in it. You will need to change the username and password appropriately.
  
import java.sql.*;
+
{{bc|1=
import java.util.Properties;
+
import java.sql.*;
public class DBDemo
+
import java.util.Properties;
{
+
public class DBDemo
  // The JDBC Connector Class.
+
{
  private static final String dbClassName = "com.mysql.jdbc.Driver";
+
    // The JDBC Connector Class.
  // Connection string. emotherearth is the database the program
+
    private static final String dbClassName = "com.mysql.jdbc.Driver";
  // is connecting to. You can include user and password after this
+
    // Connection string. emotherearth is the database the program
  // by adding (say) ?user=paulr&password=paulr. Not recommended!
+
    // is connecting to. You can include user and password after this
  private static final String CONNECTION =
+
    // by adding (say) ?user=paulr&password=paulr. Not recommended!
 +
    private static final String CONNECTION =
 
                           "jdbc:mysql://127.0.0.1/emotherearth";
 
                           "jdbc:mysql://127.0.0.1/emotherearth";
  public static void main(String[] args) throws
+
    public static void main(String[] args) throws
 
                             ClassNotFoundException,SQLException
 
                             ClassNotFoundException,SQLException
  {
+
    {
    System.out.println(dbClassName);
+
        System.out.println(dbClassName);
    // Class.forName(xxx) loads the jdbc classes and
+
        // Class.forName(xxx) loads the jdbc classes and
    // creates a drivermanager class factory
+
        // creates a drivermanager class factory
    Class.forName(dbClassName);
+
        Class.forName(dbClassName);
    // Properties for user and password. Here the user and password are both 'paulr'
+
        // Properties for user and password. Here the user and password are both 'paulr'
    Properties p = new Properties();
+
        Properties p = new Properties();
    p.put("user","paulr");
+
        p.put("user","paulr");
    p.put("password","paulr");
+
        p.put("password","paulr");
    // Now try to connect
+
        // Now try to connect
    Connection c = DriverManager.getConnection(CONNECTION,p);
+
        Connection c = DriverManager.getConnection(CONNECTION,p);
    System.out.println("It works !");
+
        System.out.println("It works !");
    c.close();
+
        c.close();
 
     }
 
     }
}
+
}
 +
}}
  
===Running the Program===
+
=== Running the program ===
  
To compile and run the program enter
+
To compile and run the program enter:
  
 
  javac DBDemo.java
 
  javac DBDemo.java
 
  java DBDemo
 
  java DBDemo
  
and hopefully, it should print out  
+
and hopefully, it should print out:
  
 
  This is the database connection
 
  This is the database connection
 
  com.mysql.jdbc.Driver
 
  com.mysql.jdbc.Driver
 
  It works !
 
  It works !

Revision as of 08:16, 14 November 2013

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

Installation

Installing MySQL

Install a MySQL implementation.

There are now two "fixes" you have to make. Firstly edit the file /etc/mysql/my.cnf to allow network access. Find the line with skip_networking in and comment it out so it looks like this:

#skip-networking

Finally start mysql service up.

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/ and

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

Testing

To access MySQL's command line tool,

$ mysql

Creating the test database

The following commands create a database and allow a user user. You will need to change the user name to whatever yours is.

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

Now press Ctrl+d to exit the command line tool.

Creating the test program

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.

import java.sql.*;
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

To compile and run the program enter:

javac DBDemo.java
java DBDemo

and hopefully, it should print out:

This is the database connection
com.mysql.jdbc.Driver
It works !