package Shangchuan;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.io.*;
public class Attachment {
public void insertGoods() {
Connection con = null;
Statement st = null;
//自动生成主键ID,getId()方法为自动生成主键ID的方法,可以采用其它方法替代
int Id = this.getId();
//拼SQL语句,注意,数据库的字段要对应好数据bean中的get方法
String insertSql = "insert into file12 values(" + Id
+ ",empty_blob(),empty_clob())";//输出并测试SQL语句,确保SQL语句的正确性
String sUsr = "ganli";
String sPwd = "ganli";
try {
//获取数据库连接(如果采用连接池,那么将会更改以下获取数据库连接的代码)
Class.forName("oracle.jdbc.driver.OracleDriver"); //装载数据库驱动
con = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:ora",sUsr, sPwd); //获得数据库连接
if (con == null) {
//测试,保证连接畅通有效
System.out
.println("the connection is null,please check it!!!!!!!!!!!1");
}
//通过获取的数据库连接,获得一个Statement或者PreparedStatement对象
con.setAutoCommit(false);
st = con.createStatement();
//通过上步所初始化后的Statement对象来执行第三步所拼装的SQL语句,并且获得返回值
int i = st.executeUpdate(insertSql);//返回值为执行成功的sql语句的条数
System.out.println("the return values is >>>>>>>>> " + i); //输出SQL语句执行的结果,以验证其执行结果是否符合预期
//返回值转换,如果执行返回的结果为0,意味着没有SQL语句在数据库端执行,即,方法失败,反之,成功
ResultSet rs = st.executeQuery("select FILEBLOB from file12 where id="+Id+" for update");
if (rs.next()) {
//得到java.sql.Blob对象,然后Cast为oracle.sql.BLOB
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("FILEBLOB");
//到数据库的输出流
OutputStream outStream = blob.getBinaryOutputStream();
//这里用一个文件模拟输入流
File file = new File("D://only when i sleep-李恩珠.mp3");
InputStream fin = new FileInputStream(file);
//将输入流写到输出流
byte[] b = new byte[blob.getBufferSize()];
int len = 0;
while ((len = fin.read(b)) != -1) {
outStream.write(b, 0, len);
//blob.putBytes(1,b);
}
fin.close();
outStream.flush();
outStream.close();
}
con.commit();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
//关闭数据库相关的对象和连接,先关闭Statement对象,再Connection对象
try {
st.close();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
public void findGoods(int Id) {
//定义返回值
Connection con = null;
Statement st = null;
//自动生成主键ID,getId()方法为自动生成主键ID的方法,可以采用其它方法替代
//int goodsId = this.getId();
String sUsr = "ganli";
String sPwd = "ganli";
try {
//获取数据库连接(如果采用连接池,那么将会更改以下获取数据库连接的代码)
Class.forName("oracle.jdbc.driver.OracleDriver"); //装载数据库驱动
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ora", sUsr, sPwd); //获得数据库连接
if (con == null) {
//测试,保证连接畅通有效
System.out
.println("the connection is null,please check it!!!!!!!!!!!1");
}
//通过获取的数据库连接,获得一个Statement或者PreparedStatement对象
con.setAutoCommit(false);
st = con.createStatement();
// System.out.println("the return values is >>>>>>>>> " + i); //输出SQL语句执行的结果,以验证其执行结果是否符合预期
//返回值转换,如果执行返回的结果为0,意味着没有SQL语句在数据库端执行,即,方法失败,反之,成功
ResultSet rs = st.executeQuery("select FILEBLOB from file12 where id="+Id);
if (rs.next()) {
//得到java.sql.Blob对象,然后Cast为oracle.sql.BLOB
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("FILEBLOB");
//到数据库的输入流
//OutputStream outStream = blob.getBinaryOutputStream();
InputStream ins = blob.getBinaryStream();
//这里用一个文件模拟输入流
//File file = new File("d://s.xml");
File file = new File("e://ganli12.mp3");
OutputStream fout = new FileOutputStream(file);
byte[] b = new byte[blob.getBufferSize()];
int len = 0;
while ( (len = ins.read(b)) != -1) {
fout.write(b, 0, len);
}
// 将输入流写到输出流
fout.close();
ins.close();
}
con.commit();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
//关闭数据库相关的对象和连接,先关闭Statement对象,再Connection对象
try {
st.close();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
public boolean insertGoods1() {
//定义返回值
boolean s = false;
Connection con = null;
Statement st = null;
//自动生成主键ID,getId()方法为自动生成主键ID的方法,可以采用其它方法替代
int Id = this.getId();
//拼SQL语句,注意,数据库的字段要对应好数据bean中的get方法
String insertSql = "insert into file12(id,FILEBLOB,FILECLOB) values(" + Id
+ ",empty_blob(),empty_clob())";//输出并测试SQL语句,确保SQL语句的正确性
String sUsr = "ganli";
String sPwd = "ganli";
try {
//获取数据库连接(如果采用连接池,那么将会更改以下获取数据库连接的代码)
Class.forName("oracle.jdbc.driver.OracleDriver"); //装载数据库驱动
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ora", sUsr, sPwd); //获得数据库连接
if (con == null) {
//测试,保证连接畅通有效
System.out
.println("the connection is null,please check it!!!!!!!!!!!1");
}
//通过获取的数据库连接,获得一个Statement或者PreparedStatement对象
con.setAutoCommit(false);
st = con.createStatement();
//通过上步所初始化后的Statement对象来执行第三步所拼装的SQL语句,并且获得返回值
int i = st.executeUpdate(insertSql);//返回值为执行成功的sql语句的条数
System.out.println("the return values is >>>>>>>>> " + i); //输出SQL语句执行的结果,以验证其执行结果是否符合预期
//返回值转换,如果执行返回的结果为0,意味着没有SQL语句在数据库端执行,即,方法失败,反之,成功
if (i == 0) {
s = false;
} else {
s = true;
}
ResultSet rs = st.executeQuery("select FILECLOB from file12 where id="+Id+" for update");
if (rs.next()) {
//得到java.sql.Blob对象,然后Cast为oracle.sql.BLOB
oracle.sql.CLOB blob = (oracle.sql.CLOB) rs.getClob("FILECLOB");
//到数据库的输出流
Writer outStream = blob.getCharacterOutputStream();
//这里用一个文件模拟输入流
File file = new File("d://jdbc.doc");
String data = file.toString();
char[] c = data.toCharArray();
outStream.write(c, 0, c.length);
outStream.flush();
outStream.close();
}
con.commit();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
//关闭数据库相关的对象和连接,先关闭Statement对象,再Connection对象
try {
st.close();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
return s;
}
//出库
public boolean findGoods1(int goodsId) {
//定义返回值
boolean s = false;
Connection con = null;
Statement st = null;
//自动生成主键ID,getId()方法为自动生成主键ID的方法,可以采用其它方法替代
String sUsr = "ganli";
String sPwd = "ganli";
try {
//获取数据库连接(如果采用连接池,那么将会更改以下获取数据库连接的代码)
Class.forName("oracle.jdbc.driver.OracleDriver"); //装载数据库驱动
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ora", sUsr, sPwd); //获得数据库连接
if (con == null) {
//测试,保证连接畅通有效
System.out
.println("the connection is null,please check it!!!!!!!!!!!1");
}
//通过获取的数据库连接,获得一个Statement或者PreparedStatement对象
con.setAutoCommit(false);
st = con.createStatement();
//System.out.println("the return values is >>>>>>>>> " + i); //输出SQL语句执行的结果,以验证其执行结果是否符合预期
//返回值转换,如果执行返回的结果为0,意味着没有SQL语句在数据库端执行,即,方法失败,反之,成功
ResultSet rs = st.executeQuery("select FILECLOB from file12 where id="+goodsId);
if (rs.next()) {
//得到java.sql.Blob对象,然后Cast为oracle.sql.BLOB
oracle.sql.CLOB blob = (oracle.sql.CLOB) rs.getClob("FILECLOB");
到数据库的输出流
Reader inStream = blob.getCharacterStream();
char[] c = new char[(int) blob.length()];
inStream.read(c);
//这里用一个文件模拟输入流
File file = new File("E://jdbc33333333333.doc");
String data = file.toString();
char[] cha= data.toCharArray();
data = new String(cha);
System.out.println(data);
inStream.close();
}
con.commit();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
//关闭数据库相关的对象和连接,先关闭Statement对象,再Connection对象
try {
st.close();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
return s;
}
// 自动获取一个表的ID的值
public int getId() {
int i = 1;
String sql = "select MAX(id) from file12 ";
String sUsr = "ganli";
String sPwd = "ganli";
try {
//第四步,获取数据库连接
Class.forName("oracle.jdbc.driver.OracleDriver"); //装载数据库驱动
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ora", sUsr, sPwd); //获得数据库连接
if (con == null) {
//测试,保证连接畅通有效
System.out.println("the connection is null,please check it!!!!!!!!!!!1");
}
Statement st = con.createStatement();
//第六步,通过第五步所初始化后的Statement对象来执行第三步所拼装的SQL语句,并且获得返回值
ResultSet rs = st.executeQuery(sql);
//第七步,对返回值进行转换
if (rs.next()) {
i = rs.getInt(1);
i++;
} else {
i = 1;
}
} catch (Exception se) {
se.printStackTrace();
}
return i;
}
public static void main(String[] args) {
Attachment at = new Attachment();
at.insertGoods();
//at.findGoods(7);
//at.insertGoods1();
//at.findGoods1(6);
}
}