一个计算机技术爱好者与学习者

0%

JDBC概述

发表于 分类于 ，

1. 名词解释

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

2. 下载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

3. 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'@'%';

4. 测试连通性

以测试eclipse和MySQL连接为例。

4.1. 设置驱动

打开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。

4.2. 设置连接

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

4.3. 测试连接

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

5. 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。

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

6. 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
}

7. 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


8. 源代码分享

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

9. 小结

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

10. 参考文档

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

  • 本文作者： 好好学习的郝
  • 原文链接： https://www.voidking.com/dev-jdbc-start/
  • 版权声明： 本文采用 BY-NC-SA 许可协议，转载请注明出处！源站会即时更新知识点并修正错误，欢迎访问~

  • 微信公众号同步更新，欢迎关注~