豫ICP备17040950号-2

JDBC概述

设计开发
文章目录
  1. 1. 名词解释
  2. 2. 下载jar包
  3. 3. MySQL用户管理
  4. 4. 测试连通性
    1. 4.1. 设置驱动
    2. 4.2. 设置连接
    3. 4.3. 测试连接
  5. 5. MySQL demo设计
  6. 6. SQL Server demo设计
  7. 7. Oracle demo设计
  8. 8. 源代码分享
  9. 9. 小结
  10. 10. 参考文档

名词解释

JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

下载jar包

MySQL：
MySQL Connectors

Oracle：
JDBC、SQLJ、Oracle JPublisher 和通用连接池 (UCP)
JDBC and Universal Connection Pool (UCP)

SQL Server：
Microsoft JDBC Drivers 4.1 and 4.0 for SQL Server

MySQL用户管理

创建新用户

1
2
3
4
5
6
7
8
mysql -u root -p
create user 'scott'@'localhost' identified by 'tiger';//创建本地用户
create user 'scott'@'%' identified by 'tiger';//创建远程用户，可选命令
create database test;
grant all prvivileges on test.* to scott;
flush privileges;
select host,user,password from mysql.user;//查看系统有哪些用户
exit

新用户登录

1
2
3
4
5
mysql -u scott -p
show databases;
use test;
show tables;
exit

删除新用户

1
2
3
mysql -u root -p
drop user 'scott'@'localhost';
drop user 'scott';//相当于drop user 'scott'@'%';

测试连通性

以测试eclipse和MySQL连接为例。

设置驱动

打开eclipse，Window，Open Perspective，Other…，Database Development，OK。
在左侧Data Source Explorer中，右击Database Connections文件夹，New…，选中MySQL，Name随意，Description随意，Next，New Driver Definition，Name/Type中选中一个System Version，然后在JAR List中选中mysql-connector-java-*-bin.jar，Edit JAR/Zip…，然后选中刚才下载解压的jar包，OK。

设置连接

在Properties的General选项卡中，输入Database、URL、User name、Password，Save password前打钩。

测试连接

设置好连接后，点击Test Connection，即可测试连通性。会提示ping succeeded！或者ping failed！

MySQL demo设计

使用scott登录MySQL

1
2
3
4
5
use test;
create table userbase(id int,username varchar(16),passwd varchar(16));
insert into userbase values(1,'voidking','voidking');
insert into userbase values(2,'voidking2','voidking2');
insert into userbase values(3,'voidking3','voidking3');

创建jdbc工程，创建包com.voidking.jdbc，新建JdbcMySQL类，内容如下。

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
package com.voidking.jdbc;

import java.sql.*;

public class JdbcMySQL {
	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost/test";

	// Database credentials
	static final String USER = "scott";
	static final String PASS = "tiger";

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			// STEP 2: Register JDBC driver
			Class.forName(JDBC_DRIVER);

			// STEP 3: Open a connection
			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);

			// STEP 4: Execute a query
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "select id,username,passwd from userbase";
			ResultSet rs = stmt.executeQuery(sql);

			// STEP 5: Extract data from result set
			while (rs.next()) {
				// Retrieve by column name
				int id = rs.getInt("id");
				String username = rs.getString("username");
				String passwd = rs.getString("passwd");

				// Display values
				System.out.print("ID: " + id);
				System.out.print(", username: " + username);
				System.out.println(", passwd: " + passwd);

			}
			// STEP 6: Clean-up environment
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			}// nothing we can do
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}// end finally try
		}// end try
		System.out.println("Goodbye!");
	}// end main
}// end JdbcMySQL


右击JRE System Library，Build Path，Configure Build Path…，Add External JARs…，选中下载解压好的mysql-connector-java-*-bin.jar。

运行项目，即可在控制台看到输出。

SQL Server demo设计

使用sa登录SQL Server

1
2
3
4
5
6
create database test;
//切换到test数据库
create table userbase(id int,username varchar(16),passwd varchar(16));
insert into userbase values(1,'voidking','voidking');
insert into userbase values(2,'voidking2','voidking2');
insert into userbase values(3,'voidking3','voidking3');

新建JdbcSQLServer类，内容如下：

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
package com.voidking.jdbc;

import java.sql.*;

public class JdbcSQLServer {

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	static final String DB_URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=test";

	// Database credentials
	static final String USER = "sa";
	static final String PASS = "123";

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			// STEP 2: Register JDBC driver
			Class.forName(JDBC_DRIVER);

			// STEP 3: Open a connection
			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);

			// STEP 4: Execute a query
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "select id,username,passwd from userbase";
			ResultSet rs = stmt.executeQuery(sql);

			// STEP 5: Extract data from result set
			while (rs.next()) {
				// Retrieve by column name
				int id = rs.getInt("id");
				String username = rs.getString("username");
				String passwd = rs.getString("passwd");

				// Display values
				System.out.print("ID: " + id);
				System.out.print(", username: " + username);
				System.out.println(", passwd: " + passwd);

			}
			// STEP 6: Clean-up environment
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			}// nothing we can do
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}// end finally try
		}// end try
		System.out.println("Goodbye!");
	}// end main
}

Oracle demo设计

使用scott用户登录

1
2
3
4
create table userbase(id int,username varchar(16),passwd varchar(16));
insert into userbase values(1,'voidking','voidking');
insert into userbase values(2,'voidking2','voidking2');
insert into userbase values(3,'voidking3','voidking3');

新建JdbcOracle类，内容如下：

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
package com.voidking.jdbc;

import java.sql.*;

public class JdbcOracle {
	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver";
	static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";

	// Database credentials
	static final String USER = "scott";
	static final String PASS = "tiger";

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			// STEP 2: Register JDBC driver
			Class.forName(JDBC_DRIVER);

			// STEP 3: Open a connection
			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);

			// STEP 4: Execute a query
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "select id,username,passwd from userbase";
			ResultSet rs = stmt.executeQuery(sql);

			// STEP 5: Extract data from result set
			while (rs.next()) {
				// Retrieve by column name
				int id = rs.getInt("id");
				String username = rs.getString("username");
				String passwd = rs.getString("passwd");

				// Display values
				System.out.print("ID: " + id);
				System.out.print(", username: " + username);
				System.out.println(", passwd: " + passwd);

			}
			// STEP 6: Clean-up environment
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			}// nothing we can do
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}// end finally try
		}// end try
		System.out.println("Goodbye!");
	}// end main
}// end JdbcOracle


源代码分享

https://github.com/voidking/jdbc.git

小结

通过上面三个连接不同数据库的例子，我们发现，代码的差别，仅仅在于驱动包名、数据库的地址、用户名、密码。
那么，这四个信息在哪里获得呢？除了自己记忆之外，小编提供一个查询测试的方法。
Database Development，右击连接，Properties，Driver Properties。这时，已经可以看到驱动、用户名、密码。
至于驱动，请接着点开Edit Driver Definition，Properties，Driver Class后面的就是驱动包。
至此，四项信息都有了。

参考文档

JDBC快速入门教程：http://www.yiibai.com/jdbc/jdbc_quick_guide.html