oracle：
https://blog.csdn.net/pengpengpeng85/article/details/78756914
这两个是必须启动的
image.png
连接到Oracle：
image.png
还要注意这里面的端口号和service_name要保持一致
image.png
image.png
image.png
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
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
image.png
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
