Store images in MySQL

To MySQLStore images in 1 IntroductionWhen designing a database for development, it is inevitable to insert images or audio files into the database. In general, we can insert the corresponding storage location of the image file, instead of the file itself, to avoid the trouble of directly inserting into the database. However, sometimes it is easier to insert images to MySQL for management. So how can I store it in MySQL? Reference [1] has a fairly clear example, but it is based on the query Brower, a MySQL Gui-based query tool. If your machine is not installed, it may not be very understandable. I will not go into details here. For more details, please refer to the link provided. In addition, the example in [1] only shows us the ease-of-use and power of query Brower, which is not very practical for our development. So let's use Java to write a simple instance stored in MySQL. 2. Create a tableFirst, you must create a table in the database. I created a table named PIC in the database named test. The table contains three columns: idpic, caption, and IMG. Idpic is the primary key, caption is the expression of the image, and IMG is the image file itself. The SQL statement for table creation 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; input the preceding statement to the command line (if query Brower is installed, you can follow the instructions in [1] to create a table, which is more convenient .), Run. The table is successfully created. 3. Image StorageAfter the table is complete, we start to write a Java class to insert images to the database. We know that Java and database connections are implemented through JDBC driver. I am using MySQL ctor/j provided on the MySQL website. If you are using another type of driver, there may be some differences in the following implementation process. 3.1 Load JDBC Driver to establish a connectionThe drivermanager interface provided by JDK is used to manage connections between Java application and JDBC driver. Before using this interface, drivermanager needs to know the JDBC driver to be connected. The simplest method is to use class. forname () to register with drivermanager to implement the java. SQL. Driver Interface Class. For MySQL connector/J, the class name is com. MySQL. JDBC. Driver. The following simple example shows 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 und for some // broken Java implementations class. forname ("com. mySQL. JDBC. driver "). newinstance (); // connection con = drivermanager. getconnection (......) //......} Catch (exception ex) {// handle the error} After registering a driver with drivermanager, we can call the drivermanager. getconnection () method to obtain the connection to the database. In fact, in the preceding example, this statement is commented out. A complete example will be provided in the subsequent implementation. 3.2 preparedstatementAfter 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 and callablestatement to execute some stored procedures and functions. The following code snippet inserts a record into the PIC table. Where (1) The connection interface object con obtains the pre-compiled SQL statement (precompiled SQL statement) by calling the preparestatement method; (2) is to assign a value to the first question mark of the insert statement, (3) for the second value, (4) for the third, this step is also the most mentioned, the setbinarystream () is used. The first parameter 3 refers to the third question mark. The fiis 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, FS, (INT) file. length (); (4)ps.exe cuteupdate ();... 3.3 complete codeThe 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; P Rivate 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 = ST Rurl; 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 (); syst Em. 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 FD = new fileinputstream (File); try {PS = Co N. preparestatement ("select max (idpic) from PIC"); resultset rs = ps.exe cutequery (); 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, FS, (INT) file. length (); ps.exe cuteupdate (); 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 (); FCM. close (); // Close dB 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. ");}}} 4. Summary At this point, we have finished the whole process of saving images to MySQL. This example is the simplest example. You can add other functions as needed, such as reading and deleting files, to improve the entire program. When I write this article, I mainly refer to [2]. Thank you. For more information, see [3]. It is a pretty good example. It not only saves and reads, but also has a very intuitive graphic interface. If you are interested, you can study it in depth.

References [1]Michael Zinner, Http://forums.mysql.com/read.php? 20,17671, 27914 # msg-27914. Feb. 2, 06 [2]Kasi mono, Http://www.jguru.com/forums/view.jsp? Eid = 720163. Feb. 2, 06 [3]Nils Christian svihus, Http://www.jguru.com/faq/view.jsp? Eid = 1278882. Feb. 2, 06