jdbc-operation time and large object (CLOB/BLOB)

One, time (date, hour, Timestamp)

Java.sql.date/java.sql.time/java.sql.timestamp extends Java.util.Date

 Public classtimedata {preparedstatement pstatement=NULL; //data of the operation date type     Public voidInsertdate (Connection Connection,LongTime ) {        Try{String SQL= "INSERT into user (Regtime) VALUES (?)"; Pstatement=connection.preparestatement (SQL); //the time inserted is the current datePstatement.setdate (1,NewDate (time));        Pstatement.executeupdate (); }Catch(Exception e) {e.printstacktrace (); }finally{dbutil.getinstance (). Close (pstatement);        Dbutil.getinstance (). Close (connection); }    }    //data for the operation time type     Public voidInserttimestamp (Connection Connection,LongTime ) {        Try{String SQL= "INSERT into user (Logintime) VALUES (?)"; Pstatement=connection.preparestatement (SQL); Java.sql.Timestamp Timestamp=NewJava.sql.Timestamp (time); //the time inserted is the current timePstatement.settimestamp (1, timestamp);        Pstatement.executeupdate (); }Catch(Exception e) {e.printstacktrace (); }finally{dbutil.getinstance (). Close (pstatement);        Dbutil.getinstance (). Close (connection); }    }    /*** Batch Add data for a certain interval *@paramConnection *@paramFirst Registration time *@paramseconed Login Time *@paramRowNum The number of records added*/     Public voidInsertbatch (Connection Connection,LongFirstLongSeconed,introwNum) {        Try{String SQL= "INSERT into user (Regtime,logintime) VALUES (?,?)"; Pstatement=connection.preparestatement (SQL);  for(inti=0;i<rownum;i++){                LongRegtime=NewRandom (). Nextint (100000000) +First ; Longlogintime=seconed-NewRandom (). Nextint (10000000); Pstatement.setdate (1,Newjava.sql.Date (regtime)); Pstatement.settimestamp (2,NewTimestamp (logintime));            Pstatement.executeupdate (); }        }Catch(Exception e) {e.printstacktrace (); }finally{dbutil.getinstance (). Close (pstatement);        Dbutil.getinstance (). Close (connection); }    }    //Insert data at a specified time     Public Longstringtodate (String datestring) {Try{SimpleDateFormat SDF=NewSimpleDateFormat ("Yyyy-mm-dd hh:mm:ss"); returnSdf.parse (datestring). GetTime (); } Catch(ParseException e) {e.printstacktrace (); }        return0; }    //query date, time     Public voidquery (Connection Connection) {Statement Statement=NULL; ResultSet RS=NULL; Try{statement=connection.createstatement (); String SQL= "Select Regtime,logintime from User";            Statement.executequery (SQL); RS=statement.executequery (SQL);  while(Rs.next ()) {Date Date=rs.getdate ("Regtime"); Timestamp TS=rs.gettimestamp ("Logintime"); System.out.println (Date+"-----"+ts); }        } Catch(SQLException e) {e.printstacktrace (); }    }}

second, time Unit test

 Public classtesttimedata {Connection Connection=dbutil.getinstance (). getconnection (); Timedata Timedata=NewTimedata (); @Test Public voidinsertdate () {timedata.insertdate (Connection,system.currenttimemillis ()); } @Test Public voidInserttimestamp () {Timedata.inserttimestamp (Connection,system.currenttimemillis ()); } @Test Public voidinsertappointeddate () {//Insert Specified Time        LongT=timedata.stringtodate ("2017-01-01 15:30:00");    Timedata.insertdate (connection,t); } @Test Public voidinsertbatchdate () {//BULK Insert a time period        LongStart=timedata.stringtodate ("2016-01-01 12:50:20"); LongEnd=timedata.stringtodate ("2017-12-30 02:00:00"); Timedata.insertbatch (Connection,start,end,50); } @Test Public voidquery () {timedata.query (connection); }}

three, Big Data Objects (Clob, BLOBs)

 Public classbigdata{PreparedStatement pstatement=NULL; /*** Insert massive text by reading local files *@paramphysical path to the FilePath file *@paramConnection*/     Public voidinsertclobbyreadfile (Connection connection,string filePath) {Try{String SQL= "INSERT into user (?)"; Pstatement=connection.preparestatement (SQL); Pstatement.setclob (1,NewFileReader (NewFile (FilePath));        Pstatement.executeupdate (); }Catch(Exception e) {e.printstacktrace (); }finally{dbutil.getinstance (). Close (pstatement);        Dbutil.getinstance (). Close (connection); }    }    /*** Read massive text files stored in the database*/     Public voidQueryclob (Connection Connection,intID) {ResultSet rs=NULL; Try{String SQL= "Select resume from user where id=?"; Pstatement=connection.preparestatement (SQL); Pstatement.setint (1, id); RS=Pstatement.executequery ();  while(Rs.next ()) {Clob Clob=rs.getclob ("Resume"); Reader R=Clob.getcharacterstream (); intTemp=0;  while((Temp=r.read ())!=-1) {System.out.print (Char) temp); }            }        }Catch(Exception e) {e.printstacktrace (); }finally{dbutil.getinstance (). Close (RS);            Dbutil.getinstance (). Close (pstatement);        Dbutil.getinstance (). Close (connection); }    }    /*** Store picture files *@paramConnection *@paramImgpath The physical path of the picture*/     Public voidInsertblob (Connection connection,string imgpath) {Try{pstatement=connection.preparestatement ("INSERT into User (HEADIMG) values (?);"); //get the input stream of the picturePstatement.setblob (1,NewFileInputStream (NewFile (Imgpath));        Pstatement.execute (); }Catch(Exception e) {e.printstacktrace (); }finally{dbutil.getinstance (). Close (pstatement);        Dbutil.getinstance (). Close (connection); }    }    /*** Get picture file, and input to local: f:/hello.jpg. * @paramConnection *@paramID*/     Public voidQueryblob (Connection Connection,intID)        {ResultSet rs; Try{String SQL= "Select headimg from user where id=?"; Pstatement=connection.preparestatement (SQL); Pstatement.setint (1, id); RS=Pstatement.executequery ();  while(Rs.next ()) {//get the Blob object for the fileBlob Blob=rs.getblob ("Headimg"); //gets the input stream of the fileInputStream is=Blob.getbinarystream (); //Create an output stream for entering picture view resultsOutputStream os=NewFileOutputStream (NewFile ("F:/hello.txt")); intTemp=0;  while((Temp=is.read ())!=-1) {os.write (temp);                } os.flush ();            Os.close (); }        }Catch(Exception e) {e.printstacktrace (); }    }}

Big Data Object Unit test

 Public classTestbigdata {//Get database connection Connection Connection=dbutil.getinstance (). getconnection (); Bigdata Bigdata=NewBigdata (); @Test Public voidInsertclob () {Try{String FilePath= "F:/testclob.txt";        Bigdata.insertclobbyreadfile (Connection,filepath); } Catch(Exception e) {e.printstacktrace (); }} @Test Public voidQueryclob () {Try{Bigdata.queryclob (connection,1); } Catch(Exception e) {e.printstacktrace (); }finally{dbutil.getinstance (). Close (connection); }} @Test Public voidInsertblob () {String Imgpath= "F:/testclob.txt";    Bigdata.insertblob (Connection,imgpath); } @Test Public voidQueryblob () {Bigdata.queryblob (connection,15906); }}　　

jdbc-operation time and large object (CLOB/BLOB)