怎么用java连接mysql

You can write MySQL applications in a variety of languages. Thelanguages that most people use with MySQL are PHP and Perl, but asometimes overlooked option is the MySQL Connector/J driver, whichallows you to develop Java applications that interact with yourMySQL server.

MySQL Connector/J works within the framework of the Java JDBCinterface, an API that allows Java programs to use database serversin a portable way. JDBC is based on an approach similar to thatused in the design of Perl and Ruby DBI modules, Python's DB-APImodule, and PHP's PEAR::DB class. This approach uses a two-tierarchitecture:

 

    * The toplevel is visible to application programs and presents an abstractinterface for connecting to and using database engines. Theapplication interface does not depend on details specific toparticular engines.
      * The lowerlevel consists of drivers for individual database engines. Eachdriver handles the details necessary to map the abstractapplication interface onto operations that a specific engine willunderstand.

The JDBC interface allows developers to write applications that canbe used with different databases with a minimum of porting effort.Once a driver for a given server engine is installed, JDBCapplications can communicate with any server of that type. By usingMySQL Connector/J, your Java programs can access MySQLdatabases.

Note: MySQL Connector/J is the successor to the MM.MySQL driver. Ifyou have JDBC programs written for MM.MySQL, they should work withMySQL Connector/J as well, although you may want to update thedriver class name used in your programs. Just replace instances oforg.gjt.mm.mysql in your Java source files with com.mysql.jdbc andrecompile.
Preliminary Requirements

To use Java applications with MySQL, you may need to install someadditional software:

      * If youwant to compile and run Java programs, you'll need a Java compiler(such as javac or jikes) and a runtime environment. If these arenot already installed on your system, you can get them by obtaininga Java Software Development Kit (SDK) from java.sun.com.
      * If youwant only to run precompiled applications, no compiler isnecessary, but you'll still need a Java Runtime Environment (JRE).This too may be obtained from java.sun.com.

This article assumes that you'll write and compile your ownprograms, and thus that you have a Java SDK installed. Once youcompile a Java program, however, you can deploy it to othermachines, even ones that have only a runtime environment. Thisworks even in heterogenous installations, because Java isplatform-independent. Applications compiled on one platform can beexpected to work on other platforms. For example, you can developon a Linux box and deploy on Windows.
Connecting to the MySQL Server

To connect to the MySQL server, register the JDBC driver you planto use, then invoke its getConnection() method. The following shortprogram, Connect.java, shows how to connect to and disconnect froma server running on the local host. It accesses a database namedtest, using a MySQL account with a user name and password oftestuser and testpass:

    import java.sql.*;

    public class Connect
    {
            public static void main (String[] args)
            {
                    Connection conn = null;

                    try
                    {
                            String userName = "testuser";
                            String password = "testpass";
                            String url = "jdbc:mysql://localhost/test";
                            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 ("Cannot connect to database server");
                    }
                    finally
                    {
                            if (conn != null)
                            {
                                    try
                                    {
                                            conn.close ();
                                            System.out.println ("Database connection terminated");
                                    }
                                    catch (Exception e) { }
                            }
                    }
            }
    }

Compile Connect.java to produce a class file Connect.class thatcontains executable Java code:

    % javac Connect.java

Then invoke the class file as follows and it should connect to anddisconnect from your MySQL server:

    % java Connect
    Database connectionestablished
    Database connectionterminated

If you have trouble compiling Connect.java, double check that youhave a Java Software Development Kit installed and make sure thatthe MySQL Connector/J driver is listed in your CLASSPATHenvironment variable.

The arguments to getConnection() are the connection URL and theuser name and password of a MySQL account. As illustrated byConnect.java, JDBC URLs for MySQL consist of jdbc:mysql:// followedby the name of the MySQL server host and the database name. Analternate syntax for specifying the user and password is to addthem as parameters to the end of the connection URL:

    jdbc:mysql://localhost/test?user=testuser&password=testpass

When you specify a URL using this second format, getConnection()requires only one argument. For example, the code for connecting tothe MySQL server in Connect.java could have been written likethis:

    String userName ="testuser";
    String password ="testpass";
    String url ="jdbc:mysql://localhost/test?user="
                                    + userName
                                    + "&password="
                                    + password;
    Class.forName("com.mysql.jdbc.Driver").newInstance ();
    conn =DriverManager.getConnection (url);

getConnect() returns a Connection object that may be used tointeract with MySQL by issuing queries and retrieving theirresults. (The next section describes how to do this.) When you'redone with the connection, invoke its close() method to disconnectfrom the MySQL server.

To increase the portability of your applications, you can store theconnection parameters (host, database, user name, and password) ina Java properties file and read the properties at runtime. Thenthey need not be listed in the program itself. This allows you tochange the server to which the program connects by editing theproperties file, rather than by having to recompile theprogram.
Issuing Queries

To process SQL statements in a JDBC-based application, create aStatement object from your Connection object. Statement objectssupport an executeUpdate() method for issuing queries that modifythe database and return no result set, and an executeQuery() methodfor queries that do return a result set. The query-processingexamples in this article use the following table, animal, whichcontains an integer id column and two string columns, name andcategory:

    CREATE TABLE animal
    (
            id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            name              CHAR(40),
            category      CHAR(40)
    )

id is an AUTO_INCREMENT column, so MySQL automatically assignssuccessive values 1, 2, 3, ... as records are added to thetable.
Issuing Queries That Return No Result Set

The following example obtains a Statement object from theConnection object, then uses it to create and populate the animaltable. DROP TABLE, CREATE TABLE, and INSERT all are statements thatmodify the database, so executeUpdate() is the appropriate methodfor issuing them:

    Statement s =conn.createStatement ();
    int count;
    s.executeUpdate ("DROP TABLEIF EXISTS animal");
    s.executeUpdate (
                            "CREATE TABLE animal ("
                            + "id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
                            + "PRIMARY KEY (id),"
                            + "name CHAR(40), category CHAR(40))");
    count = s.executeUpdate(
                            "INSERT INTO animal (name, category)"
                            + " VALUES"
                            + "('snake', 'reptile'),"
                            + "('frog', 'amphibian'),"
                            + "('tuna', 'fish'),"
                            + "('racoon', 'mammal')");
    s.close ();
    System.out.println (count + "rows were inserted");

The executeUpdate() method returns the number of rows affected by aquery. As shown above, the count is used to report how many rowsthe INSERT statement added to the animal table.

A Statement object may be used to issue several queries. Whenyou're done with it, invoke its close() method to dispose of theobject and free any resources associated with it.
Issuing Queries That Return a Result Set

For statements such as SELECT queries that retrieve informationfrom the database, use executeQuery(). After calling this method,create a ResultSet object and use it to iterate through the rowsreturned by your query. The following example shows one way toretrieve the contents of the animal table:

    Statement s =conn.createStatement ();
    s.executeQuery ("SELECT id,name, category FROM animal");
    ResultSet rs = s.getResultSet();
    int count = 0;
    while (rs.next ())
    {
            int idVal = rs.getInt ("id");
            String nameVal = rs.getString ("name");
            String catVal = rs.getString ("category");
            System.out.println (
                            "id = " + idVal
                            + ", name = " + nameVal
                            + ", category = " + catVal);
            ++count;
    }
    rs.close ();
    s.close ();
    System.out.println (count + "rows were retrieved");

executeQuery() does not return a row count, so if you want to knowhow many rows a result set contains, you should count them yourselfas you fetch them.

To obtain the column values from each row, invoke getXXX() methodsthat match the column data types. The getInt() and getString()methods used in the preceding example return integer and stringvalues. As the example shows, these methods may be called using thename of a result set column. You can also fetch values by position.For the result set retrieved by the SELECT query in the example,id, name, and category are at column positions 1, 2 and 3 and thuscould have been obtained like this:

    int idVal = rs.getInt(1);
    String nameVal = rs.getString(2);
    String catVal = rs.getString(3);

ResultSet objects, like Statement objects, should be closed whenyou're done with them.

To check whether or not a column value is NULL, invoke the resultset object's wasNull() method after fetching the value. Forexample, you could check for a NULL value in the name column likethis:

    String nameVal = rs.getString("name");
    if (rs.wasNull ())
            nameVal = "(no name available)";

Using Placeholders

Sometimes it's necessary to construct queries from valuescontaining characters that require special treatment. For example,in queries, string values are written enclosed within quotes, butany quote characters in the string itself should be doubled orescaped with a backslash to avoid creating malformed SQL. In thiscase, it's much easier to let JDBC handle the escaping for you,rather than fooling around trying to do so yourself. To use thisapproach, create a different kind of statement (aPreparedStatement), and refer to the data values in the querystring by means of placeholder characters. Then tell JDBC to bindthe data values to the placeholders and it will handle any specialcharacters automatically.

Suppose you have two variables nameVal and catVal from which youwant to create a new record in the animal table. To do so withoutregard to whether or not the values contain special characters,issue the query like this:

    PreparedStatement s;
    s = conn.prepareStatement(
                            "INSERT INTO animal (name, category) VALUES(?,?)");
    s.setString (1,nameVal);
    s.setString (2, catVal);
    int count = s.executeUpdate();
    s.close ();
    System.out.println (count + "rows were inserted");

The '?' characters in the query string act as placeholders--specialmarkers indicating where data values should be placed. ThesetString() method takes a placeholder position and a string valueand binds the value to the appropriate placeholder, performing anyspecial-character escaping that may be necessary. The method youuse to bind a value depends on the data type. For example,setString() binds string values and setInt() binds integervalues.
Error Handling

If you want to trap errors, execute your JDBC operations within atry block and use an exception handler to display information aboutthe cause of any problems that occur. JDBC provides getMessage()and getErrorCode() methods that may be invoked when an exceptionoccurs to obtain the error message and the numeric error code. Thefollowing example deliberately issues a malformed query. When itruns, the executeQuery() method fails and raises an exception thatis handled in the catch block:

    try
    {
            Statement s = conn.createStatement ();
            s.executeQuery ("XYZ"); // issue invalid query
            s.close ();
    }
    catch (SQLException e)
    {
            System.err.println ("Error message: " + e.getMessage ());
            System.err.println ("Error number: " + e.getErrorCode ());
    }

Resources

The following sites provide information about the tools discussedin this article:

      * Sun's Javasite is a clearinghouse for all kinds of Java-relatedinformation:

                http://java.sun.com/

          You can obtain the Java Software Development Kit or Java RuntimeEnvironment here. The specification for the JDBC API is alsoavailable on this site, should you wish to read more aboutit.
      * The MySQLConnector/J driver is supported by MySQL AB. Connector/J authorMark Matthews maintains the driver, which is available at:

                http://www.mysql.com/

      *Introductory articles describing other MySQL APIs based on anarchitecture similar to that used for JDBC may be found at:

                http://www.kitebird.com/articles/

          APIs discussed in the articles include Perl DBI, PHP PEAR::DB,Python DB-API, and Ruby DBI.