Environment: MYSQL+SQLYOG+J2SE+JDBC
Store text with longtext type
Storing pictures with blob type
1, first build the table
CREATE TABLE t_t (
ID int (+) not NULL auto_increment,
Longtext Longtext,
Picture Blob,
PRIMARY KEY (' id ')
) ;
' Longtext ' longtext,//text txt
' Picture ' blob,//pic
Database is t_test, table is t_t
2. JDBC operation, open eclipse for J2SE
String jdbcname= "Com.mysql.jdbc.Driver"; String dburl= "Jdbc:mysql://localhost:3306/t_test"; String dbusername= "root"; String dbpassword= "123456"; Class.forName (jdbcname);//Load Driver Connection conn=drivermanager.getconnection (Dburl,dbusername,dbpassword);//connection// Constructs the first SQL statement, regardless of the picture, first insert the text string sql= "insert into t_t values (null,?, null)";/? For the first pit PreparedStatement pst=conn.preparestatement (SQL);
Find Material:
Create two TXT documents on e-disk
Write some words casually, without considering Chinese.
File Context=new ("E:/h01.txt");//file files InputStream inputstream=new fileinputstream (context);// Use Flow Pst.setasciistream (1,inputstream,context.length ());//fill in the first pit int result=pst.executeupdate ();//Execute SQL statement if (result= =1) {//Hint information System.out.println ("Insert succeeded");} Else{system.out.println ("Insert Failed");}
Some exceptions are thrown with throws (throws ClassNotFoundException, SQLException, FileNotFoundException)
Go to the database and see that a text is inserted
Remove the text,
String sql= "SELECT * from t_t where id=?"; /Dig a hole? PreparedStatement pst=conn.preparestatement (SQL);p St.setint (1, 1);//The first 1 is filled with the first pit, the second 1 is the record with ID 1 in the database resultset rs= Pst.executequery (), if (Rs.next ()) {Clob C=rs.getclob ("Longtext");//Use clobstring str=c.getsubstring (1, (int) c.length ());//Assign a value to the string System.out.println (str);//Output text}
Run as follows:
Results are consistent with h01.txt results
Storage of picture pictures
Preparing a picture on the E-disk (002.jpg)
Do not consider txt (set to NULL) only consider jpgstring sql= "insert into t_t values (null,null,?)"; /Dig Pit PreparedStatement pst=conn.preparestatement (SQL); File Pic=new file ("E:/002.jpg"), InputStream inputstream=new fileinputstream (pic);p St.setbinarystream (1, InputStream , Pic.length ());//pits int result=pst.executeupdate (); if (result==1) {//Prompt message System.out.println ("Insert succeeded");} Else{system.out.println ("Insert Failed");}
Eclipse Insert succeeded, look at the database.
002.jpg has been inserted into the database
Next
Take out the picture to the F drive
String sql= "Select Picture from t_t where id=?"; /Dig Pit PreparedStatement pst=conn.preparestatement (SQL);p St.setint,//Pits, 2 represents the id=2 of the database resultset rs= Pst.executequery (), if (Rs.next ()) {Blob B=rs.getblob ("Picture"),//output to F-drive and named 003.jpgFileOutputStream Out=new FileOutputStream (New File ("F:/003.jpg")), Out.write (B.getbytes (1, (int) b.length ())); Out.close (); Conn.close ();
Run successfully, the F-drive is a 003.jpg and 002.jpg the same
The database stores txt text and JPG images