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.

[code]C:WDevResearchProgrammingJavaDatabase>mysql -u root -p
Enter password: *****
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: Y
ES)

C:WDevResearchProgrammingJavaDatabase>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[/code]

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.

[code lang=”java”]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 */ }
}
}
}
}[/code]

Executing the Code

And then watch the code in action.

[code]
C:WDevResearchProgrammingJavaDatabase>javac Database.java

C:WDevResearchProgrammingJavaDatabase>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:WDevResearchProgrammingJavaDatabase>[/code]


0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *