Getting started JDBC in Ubuntu with MySQL

Installation of MySQL

Install mysql client, server and the jdbc connector, either via synaptic or by using the following.

sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysql-java

Set up MySQL default password 

Set up the password for the root user as 'root' or whatever you want. The last entry is the password.

mysqladmin -u <root> password <root>

Use MySQL client with Terminal


Go to Applications --> Accessories --> Terminal

Now type: sudo mysql --user=<user> --password=<password>

Create a database 


create database <leann>; //Here "leann" is the database name. Don't forget semi-colon.

User Creation and privileges


Create a user with access to that table. Replace the items in square brackets by the database name, and the chosen user and password. Don't type in the square brackets !

grant all privileges on <database>. * to <user>@localhost identified by <password>;
flush privileges;

Setting up the user to use JDBC in ubuntu

CLASSPATH=$CLASSPATH:/usr/share/java/mysql.jar
export CLASSPATH

Alternatively, you can set it for all users, by editing /etc/environment.

CLASSPATH=".:/usr/share/java/mysql.jar"

Testing in Java

import java.sql.*;
import java.util.Properties;
/**
 * https://help.ubuntu.com
 **/
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();
    }
}