Shulou(Shulou.com)05/31 Report--

How do I store pictures in MySQL? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

First, you need to create a table in the database. I created a table called pic under a database called test. The table consists of three columns, idpic, caption and img. Where idpic is the primary key, caption is the expression of the picture, and img is the image file itself. The SQL statement for creating a table is as follows:

DROP TABLE IF EXISTS `test`.`pic`; CREATE TABLE `test`.`pic` (`idpic` int (11) NOT NULL auto_increment, `caption` varchar (45) NOT NULL default'', `img`longblob NOT NULL, PRIMARY KEY (`idpic`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Enter the above statement into the command line (if Query Brower is installed, you can follow the instructions in reference [1] to create the table, which will be more convenient. ), execution, table established successfully.

3 implement image storage class

After the table is complete, we start to write a Java class to insert the picture into the database. We know that Java connects to the database through JDBC driver. I am using the MySQL Connector/J provided on the MySQL website, and if you are using other types of driver, there may be some differences in the implementation process below.

3.1 load the JDBC driver and establish a connection

The DriverManager interface provided in JDK is used to manage the connection between Java Application and JDBC Driver. Before using this interface, DriverManager needs to know which JDBC driver to connect to. The easiest way is to register an interface class that implements java.sql.Driver with DriverManager with Class.forName (). For MySQL Connector/J, the name of this class is com.mysql.jdbc.Driver.

The following is a simple example of how to register Connector/J Driver.

Import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException; public class LoadDriver {public static void main (String [] args) {try {/ / The newInstance () call is a work around for some / / broken Java implementations Class.forName ("com.mysql.jdbc.Driver") .newInstance (); / / Connection con = DriverManager.getConnection (…) / /. } catch (Exception ex) {/ / handle the error}}

After registering the driver with DriverManager, we can get a connection to the database by calling the DriverManager.getConnection () method. In fact, there is this statement in the above example, but it has been commented out. There will be a complete example in a later implementation.

3.2 PreparedStatement

After completing the above steps, we can create a Statement interface class with the established connection to execute some SQL statements. In the following example, I use PreparedStatement, as well as CallableStatement, which can execute some stored procedures and functions, which will not be discussed here. The following code snippet inserts a record into the pic table. Among them, (1) the object con of the Connection interface gets the precompiled SQL statement (precompiled SQL statement) by calling the prepareStatement method; (2) it assigns a value to the first question mark of the insert statement, (3) assigns a value to the second, and (4) it is the third, which is also the most important step, using the method setBinaryStream (), the first parameter 3 refers to the third question mark, fis is a binary file stream, and the third parameter is the length of the file stream.

PreparedStatement ps;... Ps = con.prepareStatement ("insert into PIC values (,)"); / / (1) ps.setInt (1, id); / / (2) ps.setString (2, file.getName (); (3) ps.setBinaryStream (3, fis, (int) file.length ()); (4) ps.executeUpdate ();

3.3 complete code

The complete code is listed above.

Package com.forrest.storepic; import java.io.File;import java.io.FileInputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException; / * * This class describes how to store picture file into MySQL. * @ author Yanjiang Qian * @ version 1.0 Jan-02-2006 * / public class StorePictures {private String dbDriver; private String dbURL; private String dbUser; private String dbPassword; private Connection con; private PreparedStatement ps; public StorePictures () {dbDriver = "com.mysql.jdbc.Driver"; dbURL = "jdbc:mysql://localhost:3306/test"; dbUser = "root"; dbPassword = "admin"; initDB () } public StorePictures (String strDriver, String strURL, String strUser, String strPwd) {dbDriver = strDriver; dbURL = strURL; dbUser = strUser; dbPassword = strPwd; initDB ();} public void initDB () {try {/ / Load Driver Class.forName (dbDriver). NewInstance (); / / Get connection con = DriverManager.getConnection (dbURL, dbUser, dbPassword) } catch (ClassNotFoundException e) {System.out.println (e.getMessage ());} catch (SQLException ex) {/ / handle any errors System.out.println ("SQLException:" + ex.getMessage ()); System.out.println ("SQLState:" + ex.getSQLState ()); System.out.println ("VendorError:" + ex.getErrorCode ()) } catch (Exception e) {System.out.println (e.getMessage ());}} public boolean storeImg (String strFile) throws Exception {boolean written = false; if (con = = null) written = false; else {int id = 0; File file = new File (strFile); FileInputStream fis = new FileInputStream (file) Try {ps = con.prepareStatement ("SELECT MAX (idpic) FROM PIC"); ResultSet rs = ps.executeQuery (); if (rs! = null) {while (rs.next ()) {id = rs.getInt (1) + 1;}} else {return written } ps = con.prepareStatement ("insert" + "into PIC values"); ps.setInt (1, id); ps.setString (2, file.getName ()); ps.setBinaryStream (3, fis, (int) file.length ()); ps.executeUpdate (); written = true } catch (SQLException e) {written = false; System.out.println ("SQLException:" + e.getMessage ()); System.out.println ("SQLState:" + e.getSQLState ()); System.out.println ("VendorError:" + e.getErrorCode ()); e.printStackTrace () } finally {ps.close (); fis.close (); / / close db con con.close ();}} return written;} / * * Start point of the program * @ param args CMD line * / public static void main (String [] args) {if (args.length! = 1) {System.err.println ("java StorePictures filename") System.exit (1);} boolean flag = false; StorePictures sp = new StorePictures (); try {flag = sp.storeImg (args [0]);} catch (Exception e) {e.printStackTrace ();} if (flag) {System.out.println ("Picture uploading is successful.");} else {System.out.println ("Picture uploading is failed.") } after reading the above, have you mastered how to store pictures in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.