MySQL - Java

Accessing MySQL from Java

How to access MySQL from Java including creating a database, setting the CLASSPATH, writing a Java database class and then checking the results.

Create MySQL Database

First we need to create a database.

C:\WDev\Research\Programming\Java\Database>mysql -u root -p
 Enter password: *****
 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y
 ES)

C:\WDev\Research\Programming\Java\Database>mysql -u root -p
 Enter password: *****
 Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 26
 Server version: 5.1.59-community MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE School;
 Query OK, 1 row affected (0.00 sec)

mysql> quit

Setting CLASSPATH in Environment Variables on Your Windows System

Now, download the MYSQL JDBC Connector and set the class path.

Java Database Class

Create your Java database class.

import java.sql.*;

public class Database
 {
 public static void main (String[] args)
 {
 Connection conn = null;
 String userName = "root";
 String password = "mysql";
 try
 {

String url = "jdbc:mysql://localhost/School";
 Class.forName ("com.mysql.jdbc.Driver").newInstance ();
 conn = DriverManager.getConnection (url, userName, password);
 System.out.println ("Database connection established");

}
 catch (Exception e)
 {
 System.err.println ("Error message: " + e.getMessage ());
 }

try
 {
 Statement s = conn.createStatement ();
 int count;
 s.executeUpdate ("DROP TABLE IF EXISTS student");
 s.executeUpdate (
 "CREATE TABLE student ("
 + "id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
 + "PRIMARY KEY (id),"
 + "name CHAR(40), phone CHAR(40))");

count = s.executeUpdate (
 "INSERT INTO student (name, phone)"
 + " VALUES"
 + "('John Rhodes', '123'),"
 + "('Bob Smith', '456'),"
 + "('Larry Jones', '789'),"
 + "('Sandra Bullocks', '4444')");
 s.close ();
 System.out.println (count + " rows were inserted");
 }
 catch (Exception e)
 {
 System.err.println ("Error message: " + e.getMessage ());
 }

try
 {
 Statement s = conn.createStatement ();
 s.executeQuery ("SELECT id, name, phone FROM student");
 ResultSet rs = s.getResultSet ();
 int count = 0;
 while (rs.next ())
 {
 int idVal = rs.getInt ("id");
 String nameVal = rs.getString ("name");
 if (rs.wasNull ())
 nameVal = "(no name available)";

String catVal = rs.getString ("phone");
 System.out.println (
 "id = " + idVal
 + ", name = " + nameVal
 + ", phone = " + catVal);
 ++count;
 }
 rs.close ();
 s.close ();
 System.out.println ("count = " + count + " rows were retrieved");
 }
 catch (Exception e)
 {
 System.err.println ("Error message: " + e.getMessage ());

}

// prepared statement
 try
 {
 PreparedStatement s;
 String nameVal = "Peter";
 String phoneVal = "555";
 s = conn.prepareStatement (
 "INSERT INTO student (name, phone) VALUES(?,?)");
 s.setString (1, nameVal);
 s.setString (2, phoneVal);
 int count = s.executeUpdate ();
 s.close ();
 System.out.println (count + " rows were inserted");
 }
 catch (Exception e)
 {
 System.err.println ("Error message: " + e.getMessage ());

}

finally
 {
 if (conn != null)
 {
 try
 {
 conn.close ();
 System.out.println ("Database connection terminated");
 }
 catch (Exception e) { /* ignore close errors */ }
 }
 }
 }
 }

Executing the Code

And then watch the code in action.

 C:\WDev\Research\Programming\Java\Database>javac Database.java

C:\WDev\Research\Programming\Java\Database>java Database
 Database connection established
 4 rows were inserted
 id = 1, name = John Rhodes, phone = 123
 id = 2, name = Bob Smith, phone = 456
 id = 3, name = Larry Jones, phone = 789
 id = 4, name = Sandra Bullocks, phone = 4444
 count = 4 rows were retrieved
 1 rows were inserted
 Database connection terminated

C:\WDev\Research\Programming\Java\Database>

The post Accessing MySQL from Java appeared first on OptimusBI.