JDBC

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

一、数据库连接

1.加载数据库驱动类

  (1)在工程下新建lib文件夹，将 ojdbc6.jar（jar包在：E:\oracle\product\11.2.0\dbhome_1\jdbc\lib）拷贝至该目录下

  (2)右键ojdbc6.jar文件，build path --> add too build path

  (3)将驱动类加载到内存中

          Class.forName("oracle.jdbc.driver.OracleDriver");

2.JDBC连接字符串

MySQL：

String Driver="com.mysql.jdbc.Driver"; //驱动程序

String URL="jdbc:mysql://localhost:3306/db_name"; //连接的URL,db_name为数据库名

String Username="username"; //用户名

String Password="password"; //密码

Class.forName(Driver);

Connection con=DriverManager.getConnection(URL,Username,Password);

Oracle:

String Driver="oracle.jdbc.driver.OracleDriver";  //连接数据库的方法

String URL="jdbc:oracle:thin:@localhost:1521:orcl";  //orcl为数据库的SID

String Username="username"; //用户名

String Password="password"; //密码

Class.forName(Driver) ; //加载数据库驱动

Connection con=DriverManager.getConnection(URL,Username,Password); //常用数据库链接方式

3.获取数据库连接对象

  Connection conn = DriverManager.getConnection(url,user,password);

4.写sql

  String sql = "xxxxxx";

5.创建PreparedStatement对象

  PreparedStatement ps  = conn.prepareStatement(sql);

6.执行查询返回ResultSet结果集对象

  ResultSet rs = ps.executeQuery();

7.遍历结果集，获取查询结果

  while(rs.next){

          rs.getXxx()

  }

8.释放资源

  rs.close()

  ps.close()

  conn.close()

例：

package cn.mystudy;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class TestJDBC {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

String user = "SCOTT";

String password = "0128";

Connection conn = DriverManager.getConnection(url,user,password);

String sql = "select * from emp where deptno = ?";

PreparedStatement ps = conn.prepareStatement(sql);

ps.setInt(1, 20);

ResultSet rs = ps.executeQuery();

while(rs.next()){

System.out.println(rs.getInt("empno"));

      }

rs.close();

    ps.close();

    conn.close();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

二、JDBC的封装
新建一个util包，然后在其下面建立DBUtil.java

package util;


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBUtil {
    private static String DRIVER = "";
    private static String URL = "";
    private static String USER = "";
    private static String PASSWORD = "";
    static{
        try {
            InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(in);
            DRIVER = prop.getProperty("driver");
            URL = prop.getProperty("url");
            USER = prop.getProperty("user");
            PASSWORD = prop.getProperty("password");
            Class.forName(DRIVER);
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("配置文件读取失败");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
    }
    
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
        
    }
    
    public static void close(Connection conn,Statement ps) {
        if (ps !=null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }   
        }
        if (conn !=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }   
        }       
    }
    public static void close(Connection conn,Statement ps,ResultSet rs) {
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps!=null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
    }
}

在根目录下建立db.properties文件

# do not write space
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
user=SCOTT
password=0128

# mysql db
#driver=com.mysql.jdbc.Driver
#url=jdbc:mysql://localhost:3306/db_name
#user=root
#password=1234

使用DBUtil.java

package cn.mystudy;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import util.DBUtil;

public class TestJDBC2 {
    
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from emp where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 20);
            rs=ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getInt("empno"));             
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn, ps, rs);
        }
    }   
}