Difference between revisions of "JDBC and MySQL"

From ArchWiki
Jump to: navigation, search
m (Bot: Removing from Category:HOWTOs (English))
m (Style fixes)
(7 intermediate revisions by 5 users not shown)
Line 1: Line 1:
[[Category:Development (English)]]
+
[[Category:Development]]
=Introduction=
+
 
+
 
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 MySQL Databases can be accessed via Java programs.
  
=Installation=
+
== Installation ==
  
==Installing MySQL==
+
=== Installing MySQL ===
  
To start with, switch to the system user, then install MySQL.
+
[[pacman|Install]] a [[MySQL]] implementation.
  
su (enter your system password)
+
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:
pacman -S mysql
+
 
+
There are now two "fixes" you have to make. Firstly edit the file my.conf to allow network access
+
 
+
gedit /etc/my.cnf
+
 
+
Find the line with skip_networking in and comment it out so it looks like this
+
  
 
  #skip-networking
 
  #skip-networking
  
Close that file, and edit the /etc/hosts.allow file
+
Finally start ''mysql'' [[daemon|service]] up.
  
gedit /etc/hosts.allow
+
=== Installing JDBC ===
  
and enter on a blank line
+
Install a JDBC driver according to your MySQL variant:
 +
* {{AUR|mariadb-jdbc}} - For the Arch Linux endorsed server.
 +
* {{AUR|mysql-jdbc}} - For the Oracle variant.
  
mysqld : ALL : ACCEPT
+
You can also download the latter from http://www.mysql.com/products/connector-j/ and  
 
+
Finally start mysql up with :
+
 
+
/etc/rc.d/mysqld start
+
 
+
==Installing JDBC==
+
JDBC [[PKGBUILD]] is currently in [[AUR]] repository.
+
 
+
You can also download it 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,
+
  
mysql
+
To access MySQL's command line tool,
  
 +
$ 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 !