JDBC performs preprocessing, batching, LOB field processing, calling stored procedures

(1) JDBC performs preprocessing

Preparedstatment Prep Statement

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) calling a stored procedure

For scalar functions its invocation string is {fn functionname (?,?)} /{FN functionname ()}

The calling string for the stored procedure is {call proc (?,?)} /{call proc}/{call? =proc (?,?)}

CallableStatement

No parameter, no return value of stored procedure

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

Callablestatment stmt=conn.preparecall (SQL);

Stmt.executeupdate ();

Stored procedure with parameters and return values

String sql= "{call =test1 (?)}"; Mode 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 (?,?)}"; Mode 2

Callablestatment stmt=conn.preparecall (SQL);

Stmt.setstring (1, "Test");

Stmt.registeroutparameter (2,type.interger);

Stmt.execute ();

int Result=stmt.getint (2);

(3) JDBC Data batch processing

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 field (BLOB,CLOB) processing

Handling common classes and methods

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 ();

Reader Getcharacterstream (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 (a);

Stmt.setblob (2,BLOB);

Stmt.executeupdate ();

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

JDBC performs preprocessing, batching, LOB field processing, calling stored procedures