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 { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver" ; static final String DB_URL = "jdbc:mysql://localhost/test" ; static final String USER = "scott" ; static final String PASS = "tiger" ; public static void main (String[] args) { Connection conn = null ; Statement stmt = null ; try { Class.forName(JDBC_DRIVER); System.out.println("Connecting to database..." ); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Creating statement..." ); stmt = conn.createStatement(); String sql; sql = "select id,username,passwd from userbase" ; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id" ); String username = rs.getString("username" ); String passwd = rs.getString("passwd" ); System.out.print("ID: " + id); System.out.print(", username: " + username); System.out.println(", passwd: " + passwd); } rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null ) stmt.close(); } catch (SQLException se2) { } try { if (conn != null ) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } System.out.println("Goodbye!" ); } }
右击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 { static final String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver" ; static final String DB_URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=test" ; static final String USER = "sa" ; static final String PASS = "123" ; public static void main (String[] args) { Connection conn = null ; Statement stmt = null ; try { Class.forName(JDBC_DRIVER); System.out.println("Connecting to database..." ); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Creating statement..." ); stmt = conn.createStatement(); String sql; sql = "select id,username,passwd from userbase" ; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id" ); String username = rs.getString("username" ); String passwd = rs.getString("passwd" ); System.out.print("ID: " + id); System.out.print(", username: " + username); System.out.println(", passwd: " + passwd); } rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null ) stmt.close(); } catch (SQLException se2) { } try { if (conn != null ) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } System.out.println("Goodbye!" ); } }
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 { static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver" ; static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl" ; static final String USER = "scott" ; static final String PASS = "tiger" ; public static void main (String[] args) { Connection conn = null ; Statement stmt = null ; try { Class.forName(JDBC_DRIVER); System.out.println("Connecting to database..." ); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Creating statement..." ); stmt = conn.createStatement(); String sql; sql = "select id,username,passwd from userbase" ; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id" ); String username = rs.getString("username" ); String passwd = rs.getString("passwd" ); System.out.print("ID: " + id); System.out.print(", username: " + username); System.out.println(", passwd: " + passwd); } rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null ) stmt.close(); } catch (SQLException se2) { } try { if (conn != null ) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } System.out.println("Goodbye!" ); } }
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