Load sql file java

Insert file data into MySQL database using JDBC

You know, to store a file into a database table, the table must have a column whose data type is BLOB (Binary Large OBject). Assuming we have a MySQL table called person which is created by the following SQL script:

CREATE TABLE `person` ( `person_id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(45) DEFAULT NULL, `last_name` varchar(45) DEFAULT NULL, `photo` mediumblob, PRIMARY KEY (`person_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

We can notice that the column photo has type of mediumblob — which is one of four MySQL’s blob types:

• • TINYBLOB : 255 bytes
  • BLOB : 65,535 bytes (64 KB)
  • MEDIUMBLOB : 16,777,215 bytes (16 MB)
  • LONGBLOB : 4 GB

  Now we discuss some techniques used to insert content of a file into the table person , using JDBC.

  1. 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 java.sql.PreparedStatement :

  void setBlob(int parameterIndex, InputStream inputStream)

  And we have to supply an input stream of the file to be stored. For example:

  String filePath = "D:/Photos/Tom.jpg"; InputStream inputStream = new FileInputStream(new File(filePath)); String sql = "INSERT INTO person (photo) values (?)"; PreparedStatement statement = connection.prepareStatement(sql); statement.setBlob(1, inputStream); statement.executeUpdate();

  The following program connects to a MySQL database called contactdb and inserts a record with an image file into the table person :

  package net.codejava.jdbc; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class JdbcInsertFileOne < public static void main(String[] args) < String url = "jdbc:mysql://localhost:3306/contactdb"; String user = "root"; String password = "secret"; String filePath = "D:/Photos/Tom.png"; try < Connection conn = DriverManager.getConnection(url, user, password); String sql = "INSERT INTO person (first_name, last_name, photo) values (?, ?, ?)"; PreparedStatement statement = conn.prepareStatement(sql); statement.setString(1, "Tom"); statement.setString(2, "Eagar"); InputStream inputStream = new FileInputStream(new File(filePath)); statement.setBlob(3, inputStream); int row = statement.executeUpdate(); if (row >0) < System.out.println("A contact was inserted with photo image."); >conn.close(); > catch (SQLException ex) < ex.printStackTrace(); >catch (IOException ex) < ex.printStackTrace(); >> >

  In this case, the file’s content is transferred from client computer to the MySQL server. This technique is database independent because almost database engines support blob type and the JDBC driver takes care the data transfer transparently.

  2. Insert file using specific MySQL syntax

  Beside 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:

  LOAD_FILE(file_path)

  For example, the following program inserts a record into the person table with only the image file:

  package net.codejava.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class JdbcInsertFileTwo < public static void main(String[] args) < String url = "jdbc:mysql://localhost:3306/contactdb"; String user = "root"; String password = "secret"; String filePath = "D:/Photos/Tom.png"; try < Connection conn = DriverManager.getConnection(url, user, password); String sql = "INSERT INTO person (photo) values (LOAD_FILE(?))"; PreparedStatement statement = conn.prepareStatement(sql); statement.setString(1, filePath); int row = statement.executeUpdate(); if (row >0) < System.out.println("A contact was inserted with photo image."); >conn.close(); > catch (SQLException ex) < ex.printStackTrace(); >> >

  In this case, the file must reside in the same machine as MySQL server. This technique is specific to MySQL only.

  3. MySQL’s limitation on packet size

  By 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 limit is 1MB and can be configured via a property called max_allowed_packet . If we are trying to store a file whose size exceeds this limit, MySQL will throw this error:

  com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4384068 > 1048576). 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. There are two common ways for setting this limit:

  • Via MySQL’s configuration file my.ini: Open my.ini file and append the following line at the end: max_allowed_packet=104857600 That sets the limit for about 10 MB (104,857,600 bytes).
  • Via SQL statement:

  We can also configure the max_allowed_packet variable from the client by sending the following SQL statement before inserting the file:

  SET GLOBAL max_allowed_packet=104857600;

  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 net.codejava.jdbc; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; public class JdbcInsertFileSetLimit < public static void main(String[] args) < String url = "jdbc:mysql://localhost:3306/contactdb"; String user = "root"; String password = "secret"; String filePath = "D:/Photos/Tom.png"; try < Connection conn = DriverManager.getConnection(url, user, password); String querySetLimit = "SET GLOBAL max_allowed_packet=104857600;"; // 10 MB Statement stSetLimit = conn.createStatement(); stSetLimit.execute(querySetLimit); String sql = "INSERT INTO person (first_name, last_name, photo) values (?, ?, ?)"; PreparedStatement statement = conn.prepareStatement(sql); statement.setString(1, "Tom"); statement.setString(2, "Eagar"); InputStream inputStream = new FileInputStream(new File(filePath)); statement.setBlob(3, inputStream); int row = statement.executeUpdate(); if (row >0) < System.out.println("A contact was inserted with photo image."); >conn.close(); inputStream.close(); > catch (SQLException ex) < ex.printStackTrace(); >catch (IOException ex) < ex.printStackTrace(); >> >

  NOTE: If the max_allowed_packet is already configured in the my.ini file, then the file will take precedence.

  Other JDBC Tutorials:

  About the Author:

  

  Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

  Источник

  How to run .SQL script using JDBC?

  A database script file is a file that contains multiple SQL quries separated from each other. Usually, these files have the .sql extention.

  Running .sql script files in Java

  You can execute .sql script files in Java using the runScript() method of the ScriptRunner class of Apache iBatis. To this method you need to pass a connection object.

  Therefore to run a script file −

  • Register the MySQL JDBC Driver using the registerDriver() method of the DriverManager class.
  • Create a connection object to establish connection with the MySQL database using the getConnection() method.
  • Initialize the ScriptRunner class of the package org.apache.ibatis.jdbc.
  • Create a Reader object to read the script file.
  • Finally, execute the script using the runScript(reader) method.

  Example

  Let us create a script file with name sampleScript.sql copy the following contents init. This script creates a table with name cricketers_data in MySQL database an populates it with five records.

  CREATE DATABASE exampleDB; use exampleDB; CREATE TABLE exampleDB.cricketers_data( First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); insert into cricketers_data values('Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); insert into cricketers_data values('Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); insert into cricketers_data values('Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); insert into cricketers_data values('Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); insert into cricketers_data values('Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); select * from mydatabase.cricketers_data;

  Add the following maven dependency (for the jar file mybatis-3.4.1.jar) to your pom.xml file −

  Example

  Following JDBC program executes the sampleScript.sql file.

  import java.io.BufferedReader; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import org.apache.ibatis.jdbc.ScriptRunner; public class RunningScripts < public static void main(String args[]) throws Exception < //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/talakai_noppi"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established. "); //Initialize the script runner ScriptRunner sr = new ScriptRunner(con); //Creating a reader object Reader reader = new BufferedReader(new FileReader("E:\sampleScript.sql")); //Running the script sr.runScript(reader); >>

  Output

  Connection established. CREATE DATABASE exampleDB use exampleDB CREATE TABLE exampleDB.cricketers_data( First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ) insert into cricketers_data values('Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India') insert into cricketers_data values('Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica') insert into cricketers_data values('Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka') insert into cricketers_data values('Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India') insert into cricketers_data values('Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India') select * from mydatabase.cricketers_data First_Name Last_Name Year_Of_Birth Place_Of_Birth Country Shikhar Dhawan 1981-12-05 Delhi India Jonathan Trott 1981-04-22 CapeTown SouthAfrica Lumara Sangakkara 1977-10-27 Matale Srilanka Virat Kohli 1988-11-05 Delhi India Rohit Sharma 1987-04-30 Nagpur India

  Источник

  Русские Блоги

  java экспорт и импорт sql файла базы данных mysql
  Информация о файле конфигурации базы данных mysql в коде пропущена. Имейте в виду, что эта привычка кода нежелательна в реальной разработке проекта. Эта статья предназначена только для простого тестирования, поэтому пропустите файл конфигурации и напрямую запишите информацию о конфигурации.

  package com.fun.test; public class Test < // Экспорт базы данных (метод 1) public static void function1() < Runtime runtime = Runtime.getRuntime(); String command = getExportCommand(); // На самом деле это командная строка, выполняемая в командном окне try < Process exec = runtime.exec(command); >catch (IOException e) < // TODO Auto-generated catch block e.printStackTrace(); >> // Получить оператор командной строки экспортированных данных public static String getExportCommand() < StringBuffer command = new StringBuffer(); String username = "root"; // имя пользователя String password = "root"; // Пароль String host = "localhost"; // Хост, на котором расположена импортированная целевая база данных String port = "3306"; // Используемый номер порта String exportDatabaseName = "dataBaseName"; // Имя импортированной целевой базы данных String exportPath = "C: /mytable.sql"; // Расположение импортированного целевого файла String MysqlPath = "E: / mysql / bin /"; // Путь в mysql // обратите внимание на то, где пробелы требуются, а какие нет command.append (MysqlPath) .append ("mysqldump -u"). append (имя пользователя) .append ("-p"). append (пароль) // Пароль - это маленькое p, а порт - большое p. .append(" -h").append(host).append(" -P").append(port).append(" ").append(exportDatabaseName) .append(" -r ").append(exportPath); return command.toString(); >// Экспорт базы данных (метод 2) public static void function2() < try < Runtime rt = Runtime.getRuntime(); // вызываем команду для вызова установочного каталога mysql Process child = rt.exec("E:/mysql/bin/mysqldump -uroot -proot -hlocalhost dataBaseName"); // Установить кодировку экспорта в utf-8. Здесь должно быть UTF-8 // Записываем информацию о выводе консоли на выполнение процесса в файл .sql, и создается файл резервной копии. Примечание. Если вы не прочитаете информацию консоли, это приведет к тому, что процесс будет заблокирован и не запущен InputStream in = child.getInputStream (); // Выходная информация консоли используется в качестве входного потока InputStreamReader xx = new InputStreamReader(in, "utf-8"); // Установить кодировку выходного потока в utf-8. Здесь должно быть utf-8, иначе искаженные символы читаются из потока String inStr; StringBuffer sb = new StringBuffer(""); String outStr; // Комбинированная строка информации о выходе консоли BufferedReader br = new BufferedReader(xx); while ((inStr = br.readLine()) != null) < sb.append(inStr + "\r\n"); >outStr = sb.toString(); // Целевой файл sql для импорта: FileOutputStream fout; fout = new FileOutputStream("C:/mytable.sql"); OutputStreamWriter writer = new OutputStreamWriter(fout, "utf-8"); writer.write(outStr); writer.flush(); in.close(); xx.close(); br.close(); writer.close(); fout.close(); > catch (Exception e) < // TODO Auto-generated catch block e.printStackTrace(); >> > 

  Интеллектуальная рекомендация

  

  Backup failed Status 4277

  Недавно столкнувшись с случаем сбоя резервного копирования VMware, статус: (4277) Путь виртуальной машины содержит неподдерживаемые символы Из отчета можно увидеть, что виртуальная машина содержит неп.

  Обработка даты IOS — Дата анализа (воспроизводится)

  

  [1,1] illegal character: ‘\ufeff’ [1,10] class, interface, or enum expected

  Каждый новый класс файл новый, сообщите о некоторых ошибках при компиляции, знаете искажены? Как вы можете иметь дело с этим! Оказалось, что я случайно не буду осторожен при изменении конфигурации нас.

  Pat Class B Trotenes 1001: A + B и C

  A + B и C (15) Ограничение времени 1000 мс Лимит памяти 32768 КБ Длина кода Длина 100 КБ программа SURAME SHARGEN Тема Описание Описание ввода: Описание вывода: Кодовое шоу, как показано ниже: &.

  

  docker LVS NAT nginx

  LVS lvs ， CIP ( IP)VIP == DIP( IP) RIP( IP) lvs-nat: IP， IP DNAT （ ， ） ， （ 、 ）， VIP ， LVS（TUN） ， 。 lvs-dr: MAC （DR ） ，VIP ， IP VIP.

  Источник

  Читайте также:  Php узнать вес папки