利用JDBC实现java与MySQL数据库的连接及相关操作

利用JDBC实现java和MySQL数据库的连接，并实现增删改查等操作。
具体步骤分为：
一、将JDBC的驱动包添加到项目的classpath中
下载地址：http://mirrors.ibiblio.org/pub/mirrors/maven2/mysql/mysql-connector-java/5.0.8/mysql-connector-java-5.0.8.jar
二、实现具体的代码操作，打通数据库
1、 加载驱动程序
Class.forName(“com.mysql.jdbc.Driver”);
2、获取数据库连接
System.out.println(“Connecting to database…”);
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
System.out.println(“Connected database successfully…”);
3、操作数据库,实现增删改查。
Statement stmt = conn.createStatement();
三、数据库基本操作
String sql = “此处添加SQL语句”;
stmt.executeUpdate(sql);//利用executeUpdate()方法执行。

以下代码示例中实现了数据库的创建、删除；表的创建、删除、插入、查询、更新以及where、like子句的使用。基本语句都一致，只是迭代过程。

package com.helen.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author helen
 * 2015-9-1下午2:54:38
 *
 */
public class JdbcRoad1 {
    private static final String URL = "jdbc:mysql://localhost:3306/students?characterEncoding=gbk";
    private static final String USER = "root";
    private static final String PASSWORD = "901230";

    public static void main(String[] args) throws Exception{
        //1、加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2、获取数据库连接
        System.out.println("Connecting to database...");
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        System.out.println("Connected database successfully...");
        //3、操作数据库,实现增删改查
        Statement stmt = conn.createStatement();
        //JDBC创建数据库
            /*System.out.println("Creating database...");
            String create = "CREATE DATABASE STUDENTS";
            stmt.executeUpdate(create);
            System.out.println("Database created successfully...");*/
        //JDBC选择特定数据库：在URL中直接指定！
        //JDBC删除数据库
            /*String drop = "DROP DATABASE STUDENTS";
            stmt.executeUpdate(drop);
            System.out.println("Database deleted successfully...");*/
        //JDBC创建表
            /*String sql = "CREATE TABLE REGISTRATION " +
                "(id INTEGER not NULL, " +
                " first VARCHAR(255), " + 
                " last VARCHAR(255), " + 
                " age INTEGER, " + 
                " PRIMARY KEY ( id ))"; 
            stmt.executeUpdate(sql);
            System.out.println("Created table in given database...");*/
        //JDBC删除表
            /*String sql = "DROP TABLE REGISTRATION ";
            stmt.executeUpdate(sql);
            System.out.println("Table  deleted in given database...");*/
        //JDBC插入记录
            /*String sql = "INSERT INTO Registration " +
                    "VALUES (100, 'Zara', 'Ali', 18)";
            stmt.executeUpdate(sql);
            sql = "INSERT INTO Registration " +
                    "VALUES (101, 'Mahnaz', 'Fatma', 25)";
            stmt.executeUpdate(sql);
            sql = "INSERT INTO Registration " +
                    "VALUES (102, 'Zaid', 'Khan', 30)";
            stmt.executeUpdate(sql);
            sql = "INSERT INTO Registration " +
                    "VALUES(103, 'Sumit', 'Mittal', 28)";
            stmt.executeUpdate(sql);
            System.out.println("Inserted records into the table...");*/
        //JDBC查询表记录
            /*String sql = "SELECT id, first, last, age FROM Registration";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                //Retrieve by column name
                int id  = rs.getInt("id");
                int age = rs.getInt("age");
                String first = rs.getString("first");
                String last = rs.getString("last");
                //Display values
                System.out.print("ID: " + id);
                System.out.print(", Age: " + age);
                System.out.print(", First: " + first);
                System.out.println(", Last: " + last);
            }
            rs.close();*/
        //JDBC更新表记录
            /*String sql = "UPDATE Registration " +
                       "SET age = 30 WHERE id in (100, 101)";
            stmt.executeUpdate(sql);*/
        //JDBC删除表记录
            /*String sql = "DELETE FROM Registration " +
                "WHERE id = 101";
            stmt.executeUpdate(sql);*/
        //JDBC WHERE字句
            /*System.out.println("Fetching records with condition...");
            sql = "SELECT id, first, last, age FROM Registration" +
                           " WHERE id >= 101 ";
            ResultSet rs = stmt.executeQuery(sql);
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                 //Retrieve by column name
                 int id  = rs.getInt("id");
                 int age = rs.getInt("age");
                 String first = rs.getString("first");
                 String last = rs.getString("last");
                 //Display values
                 System.out.print("ID: " + id);
                 System.out.print(", Age: " + age);
                 System.out.print(", First: " + first);
                 System.out.println(", Last: " + last);
             }
             rs.close();    */
        //JDBC LIKE子句
             /*System.out.println("Fetching records with condition...");
             String sql = "SELECT id, first, last, age FROM Registration" +
                           " WHERE first LIKE '%za%' ";
             ResultSet rs = stmt.executeQuery(sql);
             rs = stmt.executeQuery(sql);

             while(rs.next()){
                 //Retrieve by column name
                 int id  = rs.getInt("id");
                 int age = rs.getInt("age");
                 String first = rs.getString("first");
                 String last = rs.getString("last");

                 //Display values
                 System.out.print("ID: " + id);
                 System.out.print(", Age: " + age);
                 System.out.print(", First: " + first);
                 System.out.println(", Last: " + last);
             }
             rs.close();*/
        //JDBC排序
            /*System.out.println("Fetching records in ascending order...");
            String sql = "SELECT id, first, last, age FROM Registration" +
                           " ORDER BY first DESC";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next()){
                 //Retrieve by column name
                 int id  = rs.getInt("id");
                 int age = rs.getInt("age");
                 String first = rs.getString("first");
                 String last = rs.getString("last");

                 //Display values
                 System.out.print("ID: " + id);
                 System.out.print(", Age: " + age);
                 System.out.print(", First: " + first);
                 System.out.println(", Last: " + last);
             }
            rs.close();*/

    }
}

笔者在创建数据库连接时，尝试多次都未成功，先期是驱动包未正确导入，解决以后又花费了一个来小时力气，最后查明原因，是数据库的用户名不正确。。。。所以说一定要仔细，要不会带来很大麻烦。