![]() As an example, suppose that you have a text file called data.txt in the /tmp directory. If you have a plain text file that contains data, the LOAD DATA INFILE statement can be used to import it into MySQL. How do I import data into MySQL from a simple text file?.} NOTE: If the max_allowed_packet is already configured in the my.ini file, then the file will take precedence. Statement stSetLimit = conn.createStatement() String querySetLimit = "SET GLOBAL max_allowed_packet=104857600 " // 10 MB That statement becomes effective until the server restarts.For example, the following program sends a query to set new value for max_allowed_packet variable before inserting the file: package We can also configure the max_allowed_packet variable from the client by sending the following SQL statement before inserting the file: Open my.ini file and append the following line at the end: There are two common ways for setting this limit: You can change this value on the server by setting the max_allowed_packet' variable.Because 1 MB limit is quite small for binary file, so we usually have to set a higher value when working with blob type. If we are trying to store a file whose size exceeds this limit, MySQL will throw this error: : Packet for query is too large (4384068 > 1048576). This limit is 1MB and can be configured via a property called max_allowed_packet. ![]() MySQL’s limitation on packet sizeBy default, MySQL sets a limit on the amount of data can be sent in a query (including both the file data and other query’s data). This technique is specific to MySQL only. }In this case, the file must reside in the same machine as MySQL server. String sql = "INSERT INTO person (photo) values (LOAD_FILE(?))" LOAD_FILE( file_path)For example, the following program inserts a record into the person table with only the image file: package Insert file using specific MySQL syntaxBeside using JDBC’s method setBlob() of the PreparedStatement interface, we can also use MySQL syntax to achieve the same thing, with the LOAD_FILE() command: ![]() This technique is database independent because almost database engines support blob type and the JDBC driver takes care the data transfer transparently. }In this case, the file’s content is transferred from client computer to the MySQL server. ("A contact was inserted with photo image.") PreparedStatement statement = conn.prepareStatement(sql) String sql = "INSERT INTO person (first_name, last_name, photo) values (?, ?, ?)" String url = "jdbc:mysql://localhost:3306/contactdb" Ĭonnection conn = DriverManager.getConnection(url, user, password) ![]() Where connection is a database connection represented by a object.The following program connects to a MySQL database called contactdb and inserts a record with an image file into the table person: package PreparedStatement statement = connection.prepareStatement(sql) String sql = "INSERT INTO person (photo) values (?)" InputStream inputStream = new FileInputStream(new File(filePath)) For example: String filePath = "D:/Photos/Tom.jpg" Void setBlob(int parameterIndex, InputStream inputStream)And we have to supply an input stream of the file to be stored. Insert file using standard JDBC API (database independent)To store content of a file (binary data) into the table, we can use the following method defined by the interface : You can choose which blob type, depending on your need.Now we discuss some techniques used to insert content of a file into the table person, using JDBC. That means the photo column can store a file up to 16 MB. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1We can notice that the column photo has type of mediumblob - which is one of four MySQL’s blob types: `person_id` int(11) NOT NULL AUTO_INCREMENT, Assuming we have a MySQL table called person which is created by the following SQL script: CREATE TABLE `person` ( In this JDBC tutorial, you will learn how to insert binary data (or file data) into database.You know, to store a file into a database table, the table must have a column whose data type is BLOB ( Binary Large OBject).
0 Comments
Leave a Reply. |