oracle：
https://blog.csdn.net/pengpengpeng85/article/details/78756914
这两个是必须启动的

image.png

连接到Oracle：

image.png

还要注意这里面的端口号和service_name要保持一致

image.png

注：这里面的端口号可能有所不同我的就是1522，还有后面的数据库名称要区分大小写

导包：https://www.bilibili.com/video/BV18s411u7EH?p=18
9分30秒
JDBC连接Oracle的Demo（实现增删改查）：

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

public class JDBCDemo {
    //我的端口号为1522
    private static final String URL="jdbc:oracle:thin:@localhost:1522:orcl";
    private static final String USERNAME = "scott";
    private static final String PWD = "scott";
    
    public static void update(){
        Statement stmt = null;
        Connection connection = null;
        try {
        Class.forName("oracle.jdbc.OracleDriver");//加载具体的驱动类
        //与数据库连接
        connection = DriverManager.getConnection(URL,USERNAME,PWD);//在DriverManager里
         //发送sql执行
        stmt = connection.createStatement();
         String sql = "insert into student values(2,'zs',23,'s1')";
         int count = stmt.executeUpdate(sql);//执行sql语句 返回值为int型，表示增删改了几条数据
         if(count>0) {
             System.out.println("操作成功");
         }
         
         
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
            if(stmt!=null) stmt.close();
            if(connection!=null) connection.close();
            }
            catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void query() {
        Statement stmt = null;
        Connection connection = null;
        ResultSet rs = null;  //ResultSet为结果集
        try {
        Class.forName("oracle.jdbc.OracleDriver");//加载具体的驱动类
        //与数据库连接
        connection = DriverManager.getConnection(URL,USERNAME,PWD);//在DriverManager里
         //发送sql执行
        stmt = connection.createStatement();
         String sql = "select stuno,stuname from student";
         //增删改的函数为executeUpdate，查询为executeQuery()
         rs = stmt.executeQuery(sql);//执行sql语句 
         //处理结果
         while(rs.next()) {
             int sno = rs.getInt("stuno");
             String sname = rs.getString("stuname");
             System.out.println(sno+"  "+sname);
         }
         
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {//后开的先关，先开的后关
            if(rs!=null)    rs.close();
            if(stmt!=null) stmt.close();//如果发生异常则后面的stmt为空，因此要先判断stmt是否为空
            if(connection!=null) connection.close();
            }
            catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args){
        query() ;
    }

}

JDBC连接mysql的Demo：

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

public class JDBCDemo2MySql {
    //我的端口号为1522
    private static final String URL="jdbc:mysql://localhost:3306/qwer";
    private static final String USERNAME = "root";
    private static final String PWD = "root";
    
    public static void update(){
        Statement stmt = null;
        Connection connection = null;
        try {
        Class.forName("com.mysql.cj.jdbc.Driver");//加载具体的驱动类
        //与数据库连接
        connection = DriverManager.getConnection(URL,USERNAME,PWD);//在DriverManager里
         //发送sql执行
        stmt = connection.createStatement();
         //String sql = "insert into student values(2,'zs',23,'s1')";
         // String sql = "update student set stuname='ls' where stuno = '2'";
          String sql = "delete from student where stuno = '2'";
         int count = stmt.executeUpdate(sql);//执行sql语句 返回值为int型，表示增删改了几条数据
         if(count>0) {
             System.out.println("操作成功");
         }
         
         
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
            if(stmt!=null) stmt.close();
            if(connection!=null) connection.close();
            }
            catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args){
        update();
    }
}

对象的产生：

image.png

两种写法的区别：

 String sql = "insert into student values(?,?,?,?)";
         pstmt = connection.prepareStatement(sql);//进行预编译
         pstmt.setInt(1, 321);
         pstmt.setString(2, "zhangshuo");
         pstmt.setInt(3, 27);
         pstmt.setString(4, "s5");

stmt = connection.createStatement();
//       String sql = "insert into student values(2,'zs',23,'s1')";
//       int count = stmt.executeUpdate(sql);//执行sql语句 返回值为int型，表示增删改了几条数据

image.png

JDBC总结

a.导入驱动包（也就是jar包），加载具体的驱动类 Class.forName("com.mysql.cj.jdbc.Driver");
Oracle和MySQL都是固定的
b.与数据库建立连接
connection = DriverManager.getConnection(URL,USERNAME,PWD);
其中的url都是固定的

image.png

c.通过connection对象获取操作数据库的对象（Statement/PreparadStatement）
stmt = connection.createStatement();

d.（查询）处理结果集 rs = pstmt.executeQuery()
while(rs.next()){
rs.get..()
}

可能需要try catch
具体分为两大类：
1.驱动类不存在
catch(ClassNotFoundException e) {
e.printStackTrace();
}
2.sqlException
catch(SQLException e) {
e.printStackTrace();
}
最后可能会漏掉异常，为了保险起见一般都是
catch(Exception e) {
e.printStackTrace();
}

finally{
//打开与关闭顺序相反
rs.close()
stmt.close()
connection.close()
}

先不看 jdbc调用存储过程和存储函数：

image.png

处理CLOB/BLOB类型（Oracle数据库）:
varchar2最多4k个字节
如果是MySQL则为Text类型用法相同
CLOB：大文本数据类型（例如：把小说存进数据库）
BLOB：二进制数据类型（即所有文件例如视频音频都可以放）

image.png

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCCLOB {
    //我的端口号为1522
    private static final String URL="jdbc:oracle:thin:@localhost:1522:orcl";
    private static final String USERNAME = "scott";
    private static final String PWD = "scott";
    //通过jdbc存储大文本数据
    public static void clobDemo(){
        PreparedStatement pstmt = null;
        Connection connection = null;
        try {
        Class.forName("oracle.jdbc.OracleDriver");//加载具体的驱动类
        //与数据库连接
        connection = DriverManager.getConnection(URL,USERNAME,PWD);//在DriverManager里
        
        
         //发送sql执行
         String sql = "insert into mynovel values(?,?)";
         pstmt = connection.prepareStatement(sql);//进行预编译
         pstmt.setInt(1, 1);
         
         File file = new File("C:\\Users\\Administrator\\Desktop\\1.txt");
         InputStream in = new FileInputStream(file);
         Reader reader = new InputStreamReader(in,"UTF-8");//这里的字符编码要和Oracle的保持一致,否则就会乱码（例如GBK）
         pstmt.setCharacterStream(2, reader, (int)file.length()); //小说是通过 ‘流’来体现的  file.length()代表小说的长度
         
         
         int count = pstmt.executeUpdate();
         if(count>0) {
             System.out.println("操作成功");
         }
         
         reader.close();
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
            if(pstmt!=null) pstmt.close();
            if(connection!=null) connection.close();
            }
            catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public static void clobReadereDemo(){
        PreparedStatement pstmt = null;
        Connection connection = null;
        ResultSet rs = null; 
        try {
        Class.forName("oracle.jdbc.OracleDriver");//加载具体的驱动类
        //与数据库连接
        connection = DriverManager.getConnection(URL,USERNAME,PWD);//在DriverManager里
        
        
         //发送sql执行
         String sql = "select novel from mynovel where id = ?";
         pstmt = connection.prepareStatement(sql);//进行预编译
         pstmt.setInt(1, 1);
         rs=pstmt.executeQuery();
         
         if(rs.next()) {
             Reader reader = rs.getCharacterStream("novel");
             Writer writer = new FileWriter("src/小说.txt");
             
             char []chs = new char[100];
             int len = -1;
             while((len = reader.read(chs))!=-1) {
                 writer.write(chs,0,len);
             }
             writer.close();
             reader.close();
             
         }
         
        
         
         
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
            if(pstmt!=null) pstmt.close();
            if(connection!=null) connection.close();
            }
            catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args){
        //clobDemo();
        clobReadereDemo() ;
        
    }

}

删除数据一定要记得commit！！

image.png

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCBLOB {
    //我的端口号为1522
    private static final String URL="jdbc:oracle:thin:@localhost:1522:orcl";
    private static final String USERNAME = "scott";
    private static final String PWD = "scott";
    //通过jdbc存储二进制类文件
    //设置BLOB类型
    public static void blobDemo(){
        PreparedStatement pstmt = null;
        Connection connection = null;
        try {
        Class.forName("oracle.jdbc.OracleDriver");//加载具体的驱动类
        //与数据库连接
        connection = DriverManager.getConnection(URL,USERNAME,PWD);//在DriverManager里
        
        
         //发送sql执行
         String sql = "insert into mymusic values(?,?)";
         pstmt = connection.prepareStatement(sql);//进行预编译
         pstmt.setInt(1, 1);
         
         File file = new File("E:\\qq文件\\1683621283\\QQfiles\\web1.0\\1.0\\tongdazhisheng\\bg1.mp3");
         InputStream in = new FileInputStream(file);
         pstmt.setBinaryStream(2, in, (int)file.length()); //小说是通过 ‘流’来体现的  file.length()代表小说的长度
         
         
         int count = pstmt.executeUpdate();
         if(count>0) {
             System.out.println("操作成功");
         }
         
         in.close();
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
            if(pstmt!=null) pstmt.close();
            if(connection!=null) connection.close();
            }
            catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //读取二进制文件
    public static void blobReadereDemo(){
        PreparedStatement pstmt = null;
        Connection connection = null;
        ResultSet rs = null; 
        try {
        Class.forName("oracle.jdbc.OracleDriver");//加载具体的驱动类
        //与数据库连接
        connection = DriverManager.getConnection(URL,USERNAME,PWD);//在DriverManager里
        
        
         //发送sql执行
         String sql = "select music from mymusic where id = ?";
         pstmt = connection.prepareStatement(sql);//进行预编译
         pstmt.setInt(1, 1);
         rs=pstmt.executeQuery();
         
         if(rs.next()) {
             InputStream in = rs.getBinaryStream("music");
             OutputStream out = new FileOutputStream("src/music.mp3");
             
             byte[]chs = new byte[100];
             int len = -1;
             while((len = in.read(chs))!=-1) {
                 out.write(chs,0,len);
             }
             out.close();
             in.close();
             
         }
         
        
         
         
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
            if(pstmt!=null) pstmt.close();
            if(connection!=null) connection.close();
            }
            catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args){
        //blobDemo();
        blobReadereDemo() ;
        
    }

}

image.png

JDBC

JDBC

JDBC总结

删除数据一定要记得commit！！

相关阅读更多精彩内容

友情链接更多精彩内容