利用JDBC实现java与MySQL数据库的连接及相关操作_使用java的jdbc和mysql进行数据链接并使用canner语句实现一个简单的管理系统

利用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();*/

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

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