jdbc连接数据库代码笔记简单易懂niiT培训.docx

package com.niit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExample
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// 1、加载驱动程序类
new oracle.jdbc.driver.OracleDriver();
try
{
// 2、得到一个数据库的链接
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.7.99:1521:recruit", "user30",
"user30");
// 3、得到Statement，真正执行SQL语句的
stmt = conn.createStatement();
// 4、进行查询，得到结果集
rs = stmt.executeQuery("select * from country");
// 5、遍历结果集，进行显示
while (rs.next())
{
System.out.print("code:" + rs.getString(1));
System.out.print("\tcountry:" + rs.getString(2));
System.out.println();
}

}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
try
{
if (rs != null)
rs.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try
{
if (stmt != null)
stmt.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try
{
if (conn != null)
conn.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

第二种连接数据库的方式：

ODBC
JDBC
App

ODBC
Open DataBase Connectivity

JDBC
Java DataBase Connectivity

Driver 驱动类的接口
DriverManager，驱动程序管理器类。提供对数据库驱动程序的管理功能
首先准备驱动程序，加载到内存中，就会自动注册到manager内
当我们连接和操作的过程中就会自动调用底层的数据库的驱动程序
Connection接口，表示一个连接
Statement接口，实际上可以认为就是拥有真正执行SQL的功能的对象
ResultSet接口，结果集，封装了查询结果，可以对其进行遍历

JDBC连接方式四种：
1、使用相关的数据库协议直连数据库（thin）。速度快，最直接。需要一份驱动程序
2、JDBC-ODBC桥式驱动。不需要准备驱动程序，较方便。有平台依赖性。
3、Java到本地API。（OCI）
将JDBC的调用指令变换为对本地的数据库工具的API的调用
需要在本地上安装目标数据库的客户端程序
4、以与数据库无关的网络协议的方式发送JDBC调用，
数据库服务器端需要安装中间件

数据库URL
用于标识目标数据库
主协议名 ： 子协议名 ： 子名称
jdbc ：oracle:thin:@192.168.7.99：1521：recruit
jdbc : mysql ://192.168.7.99:3306/dbname

加载驱动的三种方式：

连接多个数据库驱动用冒号，下面是三种方式：

在JDBC里，数据库是默认autocommit执行的

/* 通过jdbc:odbc:drive创建一个数据库连接实例 */
public void creatConnectInstance(){
String strurl="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\pmis.mdb";
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection(strurl);
stmt = conn.createStatement();
}catch(Exception e){
System.out.println(e.getMessage());
}
}

完整实例JDBCExample.java
package com.niit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExample
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// 1、加载驱动程序类
// new oracle.jdbc.driver.OracleDriver();
try
{
// Class.forName("oracle.jdbc.driver.OracleDriver");
System.setProperty("jdbc.drivers",
"oracle.jdbc.driver.OracleDriver:"
+ "com.microsoft.jdbc.sqlserver.SQLServerDriver");
// 2、得到一个数据库的链接
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.7.99:1521:recruit", "user30",
"user30");
// 3、得到Statement，真正执行SQL语句的
stmt = conn.createStatement();
// 4、进行查询，得到结果集
rs = stmt.executeQuery("select * from country");
// 5、遍历结果集，进行显示
while (rs.next())
{
System.out.print("code:" + rs.getString(1));
System.out.print("\tcountry:" + rs.getString(2));
System.out.println();
}

}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
// catch (ClassNotFoundException e)
// {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
finally
{
try
{
if (rs != null)
rs.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try
{
if (stmt != null)
stmt.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try
{
if (conn != null)
conn.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

TestDDL.java
package com.niit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDDL
{

/**
* @param args
*/
public static void main(String[] args)
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@192.168.7.99:1521:recruit";
Connection conn = DriverManager.getConnection(url, "user30",
"user30");
Statement stmt = conn.createStatement();

stmt
.execute("create table myCountry(ccountrycode char(4),ccountry varchar2(10))");
stmt.executeUpdate("insert into mycountry values('001','中华民国')");
ResultSet rs = stmt.executeQuery("select * from mycountry");
while (rs.next())
{
System.out.print("code:" + rs.getString(1));
System.out.print("\tcountry:" + rs.getString(2));
System.out.println();
}
rs.close();
stmt.close();
conn.close();
}
catch (ClassNotFoundException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}

}

}

TestDML.java

package com.niit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDML
{
public static void main(String[] args)
{
Connection conn = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.7.99:1521:recruit";
conn = DriverManager.getConnection(url, "user30",
"user30");
Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into country values('091','魏')");
stmt.executeUpdate("insert into country values('092','蜀')");
stmt.executeUpdate("insert into country values('093','吴')");

ResultSet rs = stmt.executeQuery("select * from country");
while (rs.next())
{
System.out.print("code:" + rs.getString(1));
System.out.print("\tcountry:" + rs.getString(2));
System.out.println();
}
rs.close();
System.out.println("----------------------我是华丽的分隔线-------------------------");

conn.setAutoCommit(false);
stmt.executeUpdate(
"update country set ccountry='晋' where ccountrycode='091'");
stmt.executeUpdate(
"delete from country where ccountrycode='092' or ccountrycode='093'");
mit();
rs = stmt.executeQuery("select * from country");
while (rs.next())
{
System.out.print("code:" + rs.getString(1));
System.out.print("\tcountry:" + rs.getString(2));
System.out.println();
}
rs.close();

stmt.close();
conn.close();
}
catch (ClassNotFoundException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (SQLException e)
{
try
{
conn.rollback();
}
catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
}
}