Shulou(Shulou.com)06/01 Report--

Use SQL statement to import pictures from the computer to the database, the specific code is as follows:

-- create picture table CREATE TABLE W_PIC (ID INT,-- number WPATH VARCHAR (80),-- full path PIC VARCHAR (80),-- picture name Without suffix img image-picture content)-insert data INSERT INTO W_PIC (ID,WPATH,PIC) SELECT 1,'C:\ Users\ w\ Desktop\ product picture\ "processing drawing 34-C dedicated .jpg', 'processing drawing 34-C dedicated' jpg','C:\ Users\ w\ Desktop\ product picture\ 129.jpg product picture-create Vernier DECLARE CUR_PIC CURSOR FOR SELECT ID,WPATH PIC FROM W_PIC DECLARE @ ID int, @ PATH VARCHAR (80), @ PIC VARCHAR (80), @ STR VARCHAR (100); OPEN CUR_PIC;FETCH NEXT FROM CUR_PIC INTO @ ID, @ PATH, @ PIC;WHILE @ @ FETCH_STATUS=0BEGIN SET @ STR=STR (@ ID);-- insert the image data EXECUTE ('UPDATE W_PIC SET img= (SELECT * FROM OPENROWSET (BULK Numeric statistics and path matching pictures, SINGLE_BLOB) AS Photo) WHERE ID='+@STR) FETCH NEXT FROM CUR_PIC INTO @ ID, @ PATH, @ PIC;ENDCLOSE CUR_PIC;DEALLOCATE CUR_PIC

Knowledge point expansion:

Reading pictures from and inserting pictures into the MySQL database

There is a data type in the MySQL database that is a Blob type, which is a binary file type. The following is the code for reading pictures from the MySQL database and inserting pictures into the database, omitting some database connection and JDBC code.

Package com.an.jdbc.bean; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import org.junit.Test; import com.an.jdbc.utils.JDBCUtils; public class TestBlob {/ / insert a picture @ Test public void test1 () throws Exception {String sql = "update beauty set photo =? Where id =? "; Connection connection = JDBCUtils.getConnection (); PreparedStatement ps = connection.prepareStatement (sql); FileInputStream fis = new FileInputStream (" C:\\ Users\\ Administrator\\ Desktop\\ 1.jpg "); ps.setBlob (1, fis); ps.setInt (2,2); int update = ps.executeUpdate (); System.out.println (update > 0?" success ":" failure "); JDBCUtils.closeConnection (null, ps, connection) } / / read a picture @ Test public void test2 () throws Exception {String sql = "select photo from beauty where id=?"; Connection connection = JDBCUtils.getConnection (); PreparedStatement ps = connection.prepareStatement (sql); ps.setInt (1,2); ResultSet set = ps.executeQuery (); if (set.next ()) {InputStream inputStream = set.getBinaryStream (1) FileOutputStream fos = new FileOutputStream ("src\\ copy.jpg"); byte [] b = new byte [1024]; int len=-1; while ((len=inputStream.read (b))! =-1) {fos.write (b, 0, len);} fos.close (); inputStream.close ();} JDBCUtils.closeConnection (null, ps, connection);}}

Summary

The above is the editor to introduce to you the use of SQL sentences to import pictures from the computer to the database, I hope it will be helpful to you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.