|
Using JDBC with MySQL |
http://www.mysql.com/products/connector/j/
Installing MySQL Connector/J
General installation instructions
The following statement appears in the connector documentation Section 2.2.1.
entitled Setting the CLASSPATH (For Standalone Use).
"Once you have un-archived the distribution archive, you can install the driver
in one of two ways: Either copy the "com" and "org" subdirectories and all of
their contents to anywhere you like, and put the directory holding the "com" and
"org" subdirectories in your classpath, or put mysql-connector-java-[version]-bin.jar
in your classpath, either by adding the FULL path to it to your CLASSPATH
environment variable, or by copying the .jar file to $JAVA_HOME/jre/lib/ext."
My installation
Actually, the above quotation describes three options instead of just two. To
make a long story short, I elected the third option. I extracted the jar file
named mysql-connector-java-3.0.15-ga-bin.jar from the zip file and copied it
into the folder named c:\j2sdk1.4.2\jre\lib\ext, which is the installation
directory tree for the currently installed version of Java on my computer.
The advantage of doing it this way was that I didn't have to modify the
classpath environment variable. The disadvantage is that the next time I upgrade
to a new version of Java, I must remember to save the MySQL connector jar file
and copy it into the directory tree for my new Java installation.
Critical steps in using JDBC
There are five critical steps in using JDBC to manipulate a database:
Reference to the driver class
The following statement appears in the MySQL Connector
documentation, Section 2.2.1. entitled Setting the CLASSPATH (For Standalone
Use).
"If you are going to use the driver with the JDBC DriverManager, you would use
"com.mysql.jdbc.Driver" as the class that
implements java.sql.Driver."
The URL of the database server
MySQL and other similar database engines behave as servers on a network. They
are identified by a URL much as other types of servers (such as HTTP servers and
FTP servers) are identified. The next fragment defines the URL for the MySQL
database server that I used in this sample program.
The code in Listing 5 defines the URL of the master database named mysql on the
MySQL database server residing on localhost and servicing the default port
number 3306.
(I could have omitted the default port number from the URL, but I decided to
include it to remind me to mention it. Note that the "//" characters shown to
the right of "mysql:" form part of the URL. They are not comment indicators.)
String url
="jdbc:mysql://localhost:3306/database_name";
Get a connection to the database
The code below implements the second critical step listed earlier (get a
connection object).
Listing 6 gets a connection to the database at the specified URL (mysql on
localhost port 3306) for a user named root with a blank password. As you are
already aware, this user is the default administrator having full privileges to
do anything, including creating new databases and registering new users on those
databases.
Connection con =
DriverManager.getConnection( url,"root", "");
Get a Statement object
The code below implements the third critical step listed earlier (get one or
more Statement objects).
This code invokes the createStatement method of the Connection interface
to get an object of type Statement.
stmt = con.createStatement();
Recall that con is a reference to an object of type Connection. A Connection
object defines a connection (session) with a specific database. SQL
statements are executed and results are returned within the context of a
connection.
Methods of the Statement interface
The Statement interface declares many methods that can be used to access the
database server and to manipulate the data in the database. One of those methods
is
executeUpdate,
which will be used in this program.
The executeUpdate method has a single String parameter. This parameter must be a
valid SQL command. The method is used to execute SQL INSERT, UPDATE or DELETE
statements. In addition, other SQL statements that return nothing can be
executed using this method.
import java.sql.*;
import java.awt.*;
import java.util.*;
import javax.swing.*;
public class Jdbc12 {
public static void main(String args[]){
Statement stmt;
ResultSet rs=null;
int i;
String s;
try {
//Register the JDBC driver for MySQL.
Class.forName("com.mysql.jdbc.Driver");
//Define URL of database server for database named mysql on the localhost with the default port number 3306.
String url = "jdbc:mysql://localhost:3306/test";
//Get a connection to the database for a user named root with a blank password.
// This user is the default administrator having full privileges to do anything.
Connection con = DriverManager.getConnection(url,"q", "q");
//Display URL and connection information
System.out.println("URL: " + url);
System.out.println("Connection: " + con);
//Get a Statement object
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
//Insert data to the DB
for(int k=5 ; k<10; k++)
// stmt.executeUpdate( "insert into publishers (publisherid, publishername) VALUES ("+ i+",'chapter "+i+"' ); ");
// stmt.executeUpdate ("delete from publishers where publisherid!=1 and publisherid!=2");
// Read and update data
rs = stmt.executeQuery("select * from publishers");
while (rs.next())
{
i = rs.getInt("publisherid");
s = rs.getString("publishername");
if (i==2)
{
rs.updateString("publishername","222222222222");
System.out.println("win");
}
rs.updateRow();
System.out.println("publisherID= " + i + "publishername = " + rs.getString("publishername") );
}
}
// detect problems interacting with the database
catch ( SQLException sqlException ) {
JOptionPane.showMessageDialog( null, sqlException.getMessage(),"Database Error", JOptionPane.ERROR_MESSAGE );
System.exit( 1 );
}
// detect problems loading database driver
catch ( ClassNotFoundException classNotFound ) {
JOptionPane.showMessageDialog( null, classNotFound.getMessage(),"Driver Not Found", JOptionPane.ERROR_MESSAGE );
System.exit( 1 );
}}}