(1)jdbc執行預處理

PReparedStatment預備語句

eg:String sql="insert into user(id,name,birthday,money) values(5,'administrator',?,'1000000')";

PreparedStatement stmt=conn.prepareStatment(sql);

stmt.setTimestamp(1,new Timestamp(System.currentTimeMillis()));

stmt.execute();

(2)調用存儲過程

對于標量函數 其調用字符串為{fn functionname(?,?)}/{fn functionname()}

對于存儲過程其調用字符串為{call proc(?,?)}/{call proc}/{call ?=proc(?,?)}

CallableStatement

無參數，無返回值的存儲過程

String sql="{call test1()}";

CallableStatment stmt=conn.prepareCall(sql);

stmt.executeUpdate();

有參數，有返回值的存儲過程

String sql="{call ?=test1(?)}"; //方式1

CallableStatment stmt=conn.prepareCall(sql);

stmt.setString(2,"test");

stmt.registerOutParameter(1,Type.Interger);

stmt.execute();

int result=stmt.getInt(1);

String sql="{call test1(?,?)}"; //方式2

CallableStatment stmt=conn.prepareCall(sql);

stmt.setString(1,"test");

stmt.registerOutParameter(2,Type.Interger);

stmt.execute();

int result=stmt.getInt(2);

(3)jdbc數據批處理

String sql="insert into user(id,name,birthday,money) values(?,?,?,?)";

PreparedStatement stmt=conn.prepareStatement(sql);

for (int i=1;i<1000;i++){

stmt.setInt(1,i);

stmt.setString(2,"test");

stmt.setDate(3,new Date(System.currentTimeMillis()));

stmt.setFloat(4,1000);

stmt.addBatch(sql);

}

int[] result=stmt.executeBatch();

(4)LOB字段(BLOB,CLOB)處理

處理常用類與方法

java.sql.ResultSet

Blob getBlob(int columnIndex);

Blob getBlob(string columnLabel);

Clob getClob(int columnIndex);

Clob getClob(string columnLabel);

-----------------------------------------------------------------

java.sql.Blob

long length();

byte[] getBytes(long startPosition,long length);

InputStream getBinaryStream();

InputStream getBinaryStream(long startPosition,long length);

OutputStream setBinaryStream(long startPosition);

java.sql.Clob

long length();

byte[] getSubString(long startPosition,long length);

Reader getCharacterStream();

ReadergetCharacterStream(long startPosition,long length);

Writer setCharacterStream(long startPosition);

-----------------------------------------------------------------

java.sql.Connection

Blob createBlob();

Clob createClob();

----------------------------------------------------------------

eg:

PreparedStament stmt=conn.prepareStatement("select pic from book where bokkid='1'");

ResultSet rt=stmt.executeQuery();

if(rt.next()){

Blob blob=rt.getBlob(1);

Image img=ImageIO.read(blob.getInputStream());

}

--------------------------------------------------------------------------------------------

Blob blob=conn.createBlob();

int offset=0;

OutputStream out=blob.setBinaryStream(offset);

ImageIO.write(img,"PNG",out);

PreparedStament stmt=conn.prepareStatement("insert into book values(?,?)");

stmt.setInt(1,1);

stmt.setBlob(2,blob);

stmt.executeUpdate();

--------------------------------------------------------------------------------------------

PreparedStament stmt=conn.prepareStatement("insert into book values(?,?)");

stmt.setInt(1,1);

File file=new File("E://text.txt");

FileReader reader=new FileReader(file);

BufferReader buffer=new BufferReader(reader);

stmt.setCharacterStream(2,buffer,3);

stmt.execute();

--------------------------------------------------------------------------------------------

PreparedStament stmt=conn.prepareStatement("select pic from book where id=1");

ResuleSet rs=stmt.exexcuteQuery();

if(rs.next()){

　 String strTmp="";

Reader read=rs.getCharacterStream(1);

BufferReader br=new BufferReader(read);

File file=new File("E://text.txt");

FileWriter fw=new FileWriter(f);

BufferWriter bw=new BufferWriter(fw);

while((strTmp=br.readLine())!=null){

bw.writer(strTmp+"/n");

bw.flush();

}

bw.close();

fw.close();

br.close();

}