Thursday, November 11, 2010

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();
    }
}

No comments:

Post a Comment