Video-02 No.1 JDBC

1、三方关系：规范制定者、规范实现者、规范使用者

?

?

规范制定者：sun JDBC

规范实现者：数据库厂商

应用程序开发者：程序员、软件工程师

?

?

2、JDBC实现的接口：

?

Driver：驱动程序类Connection：与数据库建立连接 创建一条通往数据库的路Statement：执行sql语句并将数据检索到ResultSet运输数据的车ResultSet：数据库结果集数据表，通过执行sql查询得到的结果PreparedStatement：执行预编译SQL语句DriverManager：驱动管理者3、不同的数据库实现的都是JDBC的接口，面向接口编程

?

4、三种数据库连接的默认端口号

mysql：3306 ?sqlserver：1433 ?oracle：1521

连接的三种写法：

?

mysql： ? ? ? ? ?jdbc:mysql://ip:3306/dbNamesqlserver: ? ? ?jdbc:sqlserver://ip:1433;databaseName=dbName;oracle: ? ? ? ? ? jdbc:oracle:thin:@ip:1521:dbName

5、JDBC 连接MYSQ数据库

?

package com.metarnet.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class CopyOfTestJdbcForMySQL {public static void main(String[] args) {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {//加载驱动Class.forName("com.mysql.jdbc.Driver");System.out.println("驱动程序加载成功!");//得到连接conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db_eshop", "root", "root");System.out.println("连接成功！");stmt = conn.createStatement();rs = stmt.executeQuery("select * from admin");while(rs.next()) {System.out.println(rs.getString(3));}} catch (ClassNotFoundException e) {System.out.println("驱动程序加载失败");} catch (SQLException e) {System.out.println("连接失败");e.printStackTrace();} finally {try {if(rs != null) {rs.close();rs = null;}if(stmt != null) {stmt.close();stmt = null;}if(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}}}

?

?

?6、JDBC连接SQLServer数据库

?

package com.metarnet.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestJdbcForSqlServer {public static void main(String[] args) {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {//加载驱动Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");System.out.println("驱动程序加载成功!");//得到连接conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;databaseName=db_eshop", "sa", "1234");System.out.println("连接成功！");stmt = conn.createStatement();rs = stmt.executeQuery("select * from admin");while(rs.next()) {System.out.println(rs.getString(3));}} catch (ClassNotFoundException e) {System.out.println("驱动程序加载失败");} catch (SQLException e) {System.out.println("连接失败");e.printStackTrace();} finally {try {if(rs != null) {rs.close();rs = null;}if(stmt != null) {stmt.close();stmt = null;}if(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}}}

?

?

7、JDBC连接oracle数据库

?

package com.metarnet.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestJdbcForMySQLOracle {public static void main(String[] args) {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {//加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");System.out.println("驱动程序加载成功!");//得到连接conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:db_eshop", "root", "root");System.out.println("连接成功！");stmt = conn.createStatement();rs = stmt.executeQuery("select * from admin");while(rs.next()) {System.out.println(rs.getString(3));}} catch (ClassNotFoundException e) {System.out.println("驱动程序加载失败");} catch (SQLException e) {System.out.println("连接失败");e.printStackTrace();} finally {try {if(rs != null) {rs.close();rs = null;}if(stmt != null) {stmt.close();stmt = null;}if(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}}}

?

8、JDBC-ODBC桥驱动程序（如：Java与ACCESS连接，由于微软没有提供JDBC接口规范的实现，所以可以通过桥连接，转换为ODBC连接上ACCESS）

Java应用程序 ---- JDBC API ------- ?JDBC-ODBC桥 ?------------------ ?ODBC API ------ ODBC ------ 数据源

?

设置数据源：控制面板---- 管理工具---------数据源 --------添加 ------- access ------- 数据源名：mydatasource 数据库选择自己的数据库.mdb

package com.metarnet.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestJdbcToAccess {public static void main(String[] args) {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");System.out.println("加载成功！");conn = DriverManager.getConnection("jdbc:odbc:mydatasource");stmt = conn.createStatement();String sql = "select stuId, stuName, address, age from student";rs = stmt.executeQuery(sql);while(rs.next()) {System.out.println(rs.getString(2));}} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {try {if(rs != null) {rs.close();rs = null;}if(stmt != null) {stmt.close();stmt = null;}if(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}}}?

9、开发程序的三层构架：

?

表示层业务层持久化层（数据访问层DAO）----与数据库交互10、DAO模式： data access object 数据访问对象 ?------ 完成持久化层对数据库的操作
第一种写法：

package com.metarnet.daoNo1.db;public interface Config {public static final String DRIVER = "com.mysql.jdbc.Driver";public static final String URL = "jdbc:mysql://127.0.0.1:3306/";public static final String DBNAME = "wangwang";public static final String USER = "root";public static final String PWD = "root";}

package com.metarnet.daoNo1.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBManager {private Connection conn = null;private Statement stmt = null;private ResultSet rs = null;public static DBManager dbManager;private DBManager() {try {init();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static DBManager getDBManager() {if(dbManager == null) {dbManager = new DBManager();}return dbManager;}/** * 用来执行insert、delete、update语句 * @param sql 要执行的sql语句 * @return 当前所影响的行数 */public int update(String sql) {int index = 0;try {index = stmt.executeUpdate(sql);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {this.doClosed();}return index;}/** * 用来执行select语句 * @param sql 要执行的sql语句 * @return 返回ResultRet对象 */public ResultSet query(String sql) {try {rs = stmt.executeQuery(sql);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return rs;}/** * 关闭连接 */public void doClosed() {try {if(rs != null) {rs.close();rs = null;}if(stmt != null) {stmt.close();stmt = null;}if(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}/** * 创建连接的初始化设置 * @throws ClassNotFoundException * @throws SQLException */private void init() throws ClassNotFoundException, SQLException {Class.forName(Config.DRIVER);conn = DriverManager.getConnection(Config.URL + Config.DBNAME, Config.USER, Config.PWD);stmt = conn.createStatement();}/** * 判断某个表在数据库中是否存在 * @param tableName * @return * @throws SQLException */public boolean tableExist(String tableName) throws SQLException {rs = conn.getMetaData().getTables(null, null, tableName, null);if(rs.next()) {return true;} else {return false;}}}

package com.metarnet.daoNo1.dao;import java.sql.ResultSet;import java.sql.SQLException;import com.metarnet.daoNo1.db.DBManager;public class OrdersDAO {private final DBManager dbManager;public OrdersDAO() {dbManager = DBManager.getDBManager();}public void createTable(String tableName) {try {boolean isExist = dbManager.tableExist(tableName);if(!isExist) {String sql = "CREATE TABLE " + tableName +  "(" + "stuId int PRIMARY KEY," + "stuName varchar(50) NOT NULL," + "age int NULL," + "address nvarchar(50)  NULL," + "sex char(10) NOT NULL" + ")";int index = dbManager.update(sql);if(index == 0) {System.out.println(tableName + "表创建成功！");} } else {System.out.println(tableName + "已经存在");}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} }public void drop(String tableName) {try {if(dbManager.tableExist(tableName)) {System.out.println(tableName + "表存在");String sql = "drop table " + tableName;int index = dbManager.update(sql);if(index == 0) {System.out.println(tableName + "表删除成功！");} } else {System.out.println(tableName + "表不存在");}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {dbManager.doClosed();}}public void update(String tableName) {String sql = "insert into " + tableName + " values ('070101061', 'shma', '23', '芜湖', '男')";int index = dbManager.update(sql);if(index == 1) {System.out.println(tableName + "插入数据成功");} }public void delete(String tableName) {String sql = "delete from student where stuName = \"shma\" ";int index = dbManager.update(sql);if(index == 1) {System.out.println(tableName + "删除成功");} }public void findAll(String tableName) {String sql = "select stuId, stuName, age, address, sex from " + tableName;ResultSet rs = null;rs = dbManager.query(sql);try {while(rs.next()) {int i = 1;System.out.print(rs.getInt(i++) + " ");System.out.print(rs.getString(i++) + " ");System.out.print(rs.getInt(i++) + " ");System.out.print(rs.getString(i++) + " ");System.out.print(rs.getString(i++) + " ");System.out.println();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {dbManager.doClosed();if(rs != null) {try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}rs = null;}}}public void findById(String tableName, int id) {String sql = "select stuId, stuName, age, address, sex from " + tableName + " where stuId = '" + id + " '";ResultSet rs = null;rs = dbManager.query(sql);try {while(rs.next()) {int i = 1;System.out.print(rs.getInt(i++) + " ");System.out.print(rs.getString(i++) + " ");System.out.print(rs.getInt(i++) + " ");System.out.print(rs.getString(i++) + " ");System.out.print(rs.getString(i++) + " ");System.out.println();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {dbManager.doClosed();if(rs != null) {try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}rs = null;}}}}

package com.metarnet.daoNo1.test;import com.metarnet.daoNo1.dao.OrdersDAO;public class TestOrdersDAO {public static void main(String[] args) {OrdersDAO dao = new OrdersDAO();String tableName = "student";//dao.createTable(tableName);//dao.drop(tableName);//dao.update(tableName);//dao.delete(tableName);//dao.findAll(tableName);dao.findById(tableName, 70101061);}}

?

?