How to read files/CLOB Data from MySQL database using JDBC

In previous post How to store file in MySQL database using JDBC we learnt how to save text file or CLOB(character large object) data in MYSQL database.In this post we will learn how read it back from database.

If you are using some other database like Oracle,Sybase or DB2, still process would be similar.

Make sure that you have created a folder with name DownLoadFiles in your project home directory as below screen shot.

After reading text file content from database we are going to write in DownLoadFiles folder

Project structure in eclipse:

SQL Query to create storetextfile_table and insert three rows with column name file_content which contains file text data in MySQL database (jdbcdb schema)
DB.sql

CREATE  TABLE `storetextfile_table` (
  `file_id` INT NOT NULL AUTO_INCREMENT ,
  `file_name` VARCHAR(45) NOT NULL ,
  `file_size_in_kb` BIGINT NOT NULL ,
  `file_extension` CHAR(30) NOT NULL ,
  `file_content` LONGTEXT NOT NULL ,
  PRIMARY KEY (`file_id`) );
  
INSERT INTO `storetextfile_table` (`file_name`,`file_size_in_kb`,`file_extension`,`file_content`) VALUES ('C language.txt',1,'txt','C is a general-purpose, imperative computer programming language, supporting structured programming, lexical variable scope and recursion, while a static type system prevents many unintended operations. By design, C provides constructs that map efficiently to typical machine instructions, and therefore it has found lasting use in applications that had formerly been coded in assembly language, including operating systems, as well as various application software for computers ranging from supercomputers to embedded systems.\r\n\r\nC was originally developed by Dennis Ritchie between 1969 and 1973 at Bell Labs,[5] and used to re-implement the Unix operating system. It has since become one of the most widely used programming languages of all time,with C compilers from various vendors available for the majority of existing computer architectures and operating systems. C has been standardized by the American National Standards Institute (ANSI) since 1989 (see ANSI C) and subsequently by the International Organization for Standardization (ISO).');
INSERT INTO `storetextfile_table` (`file_name`,`file_size_in_kb`,`file_extension`,`file_content`) VALUES ('C++ language.txt',1,'txt','C++ (pronounced cee plus plus /\'si? pl?s pl?s/) is a general-purpose programming language. It has imperative, object-oriented and generic programming features, while also providing facilities for low-level memory manipulation.\r\n\r\nIt was designed with a bias toward system programming and embedded, resource-constrained and large systems, with performance, efficiency and flexibility of use as its design highlights.[5] C++ has also been found useful in many other contexts, with key strengths being software infrastructure and resource-constrained applications,[5] including desktop applications, servers (e.g. e-commerce, web search or SQL servers), and performance-critical applications (e.g. telephone switches or space probes).[6] C++ is a compiled language, with implementations of it available on many platforms. Many vendors provide C++ compilers, including the Free Software Foundation, Microsoft, Intel, and IBM.\r\n\r\nC++ is standardized by the International Organization for Standardization (ISO), with the latest standard version ratified and published by ISO in December 2014 as ISO/IEC 14882:2014 (informally known as C++14).The C++ programming language was initially standardized in 1998 as ISO/IEC 14882:1998, which was then amended by the C++03, ISO/IEC 14882:2003, standard. The current C++14 standard supersedes these and C++11, with new features and an enlarged standard library. Before the initial standardization in 1998, C++ was developed by Bjarne Stroustrup at Bell Labs since 1979, as an extension of the C language as he wanted an efficient and flexible language similar to C, which also provided high-level features for program organization. The C++17 standard is due in July 2017, with the draft largely implemented by some compilers already, and C++20 is the next planned standard thereafter.');
INSERT INTO `storetextfile_table` (`file_name`,`file_size_in_kb`,`file_extension`,`file_content`) VALUES ('Java language.txt',2,'txt','Java is a general-purpose computer programming language that is concurrent, class-based, object-oriented,[14] and specifically designed to have as few implementation dependencies as possible. It is intended to let application developers \"write once, run anywhere\" (WORA),[15] meaning that compiled Java code can run on all platforms that support Java without the need for recompilation.[16] Java applications are typically compiled to bytecode that can run on any Java virtual machine (JVM) regardless of computer architecture. As of 2016, Java is one of the most popular programming languages in use,[17][18][19][20] particularly for client-server web applications, with a reported 9 million developers.[21] Java was originally developed by James Gosling at Sun Microsystems (which has since been acquired by Oracle Corporation) and released in 1995 as a core component of Sun Microsystems\' Java platform. The language derives much of its syntax from C and C++, but it has fewer low-level facilities than either of them.\r\n\r\nThe original and reference implementation Java compilers, virtual machines, and class libraries were originally released by Sun under proprietary licenses. As of May 2007, in compliance with the specifications of the Java Community Process, Sun relicensed most of its Java technologies under the GNU General Public License. Others have also developed alternative implementations of these Sun technologies, such as the GNU Compiler for Java (bytecode compiler), GNU Classpath (standard libraries), and IcedTea-Web (browser plugin for applets).\r\n\r\nThe latest version is Java 8 which is the only version currently supported for free by Oracle, although earlier versions are supported both by Oracle and other companies on a commercial basis.\r\n\r\nContents  [hide] \r\n1	History\r\n1.1	Principles\r\n1.2	Versions\r\n2	Practices\r\n2.1	Java platform\r\n2.1.1	Implementations\r\n2.1.2	Performance\r\n2.2	Automatic memory management\r\n3	Syntax\r\n4	\"Hello world\" example\r\n5	Special classes\r\n5.1	Applet\r\n5.2	Servlet\r\n5.3	JavaServer Pages\r\n5.4	Swing application\r\n5.5	Generics\r\n6	Criticism\r\n7	Use outside of the Java platform\r\n7.1	Android\r\n7.1.1	Controversy\r\n8	Class libraries\r\n9	Documentation\r\n10	Editions\r\n11	See also\r\n11.1	Comparison of Java with other languages\r\n12	Notes\r\n13	References\r\n14	External links\r\n');

CREATE TABLE `storetextfile_table` (

`file_id` INT NOT NULL AUTO_INCREMENT ,

`file_name` VARCHAR(45) NOT NULL ,

`file_size_in_kb` BIGINT NOT NULL ,

`file_extension` CHAR(30) NOT NULL ,

`file_content` LONGTEXT NOT NULL ,

PRIMARY KEY (`file_id`) );

INSERT INTO `storetextfile_table` (`file_name`,`file_size_in_kb`,`file_extension`,`file_content`) VALUES ('C language.txt',1,'txt','C is a general-purpose, imperative computer programming language, supporting structured programming, lexical variable scope and recursion, while a static type system prevents many unintended operations. By design, C provides constructs that map efficiently to typical machine instructions, and therefore it has found lasting use in applications that had formerly been coded in assembly language, including operating systems, as well as various application software for computers ranging from supercomputers to embedded systems.\r\n\r\nC was originally developed by Dennis Ritchie between 1969 and 1973 at Bell Labs,[5] and used to re-implement the Unix operating system. It has since become one of the most widely used programming languages of all time,with C compilers from various vendors available for the majority of existing computer architectures and operating systems. C has been standardized by the American National Standards Institute (ANSI) since 1989 (see ANSI C) and subsequently by the International Organization for Standardization (ISO).');

INSERT INTO `storetextfile_table` (`file_name`,`file_size_in_kb`,`file_extension`,`file_content`) VALUES ('C++ language.txt',1,'txt','C++ (pronounced cee plus plus /\'si? pl?s pl?s/) is a general-purpose programming language. It has imperative, object-oriented and generic programming features, while also providing facilities for low-level memory manipulation.\r\n\r\nIt was designed with a bias toward system programming and embedded, resource-constrained and large systems, with performance, efficiency and flexibility of use as its design highlights.[5] C++ has also been found useful in many other contexts, with key strengths being software infrastructure and resource-constrained applications,[5] including desktop applications, servers (e.g. e-commerce, web search or SQL servers), and performance-critical applications (e.g. telephone switches or space probes).[6] C++ is a compiled language, with implementations of it available on many platforms. Many vendors provide C++ compilers, including the Free Software Foundation, Microsoft, Intel, and IBM.\r\n\r\nC++ is standardized by the International Organization for Standardization (ISO), with the latest standard version ratified and published by ISO in December 2014 as ISO/IEC 14882:2014 (informally known as C++14).The C++ programming language was initially standardized in 1998 as ISO/IEC 14882:1998, which was then amended by the C++03, ISO/IEC 14882:2003, standard. The current C++14 standard supersedes these and C++11, with new features and an enlarged standard library. Before the initial standardization in 1998, C++ was developed by Bjarne Stroustrup at Bell Labs since 1979, as an extension of the C language as he wanted an efficient and flexible language similar to C, which also provided high-level features for program organization. The C++17 standard is due in July 2017, with the draft largely implemented by some compilers already, and C++20 is the next planned standard thereafter.');

INSERT INTO `storetextfile_table` (`file_name`,`file_size_in_kb`,`file_extension`,`file_content`) VALUES ('Java language.txt',2,'txt','Java is a general-purpose computer programming language that is concurrent, class-based, object-oriented,[14] and specifically designed to have as few implementation dependencies as possible. It is intended to let application developers \"write once, run anywhere\" (WORA),[15] meaning that compiled Java code can run on all platforms that support Java without the need for recompilation.[16] Java applications are typically compiled to bytecode that can run on any Java virtual machine (JVM) regardless of computer architecture. As of 2016, Java is one of the most popular programming languages in use,[17][18][19][20] particularly for client-server web applications, with a reported 9 million developers.[21] Java was originally developed by James Gosling at Sun Microsystems (which has since been acquired by Oracle Corporation) and released in 1995 as a core component of Sun Microsystems\' Java platform. The language derives much of its syntax from C and C++, but it has fewer low-level facilities than either of them.\r\n\r\nThe original and reference implementation Java compilers, virtual machines, and class libraries were originally released by Sun under proprietary licenses. As of May 2007, in compliance with the specifications of the Java Community Process, Sun relicensed most of its Java technologies under the GNU General Public License. Others have also developed alternative implementations of these Sun technologies, such as the GNU Compiler for Java (bytecode compiler), GNU Classpath (standard libraries), and IcedTea-Web (browser plugin for applets).\r\n\r\nThe latest version is Java 8 which is the only version currently supported for free by Oracle, although earlier versions are supported both by Oracle and other companies on a commercial basis.\r\n\r\nContents [hide] \r\n1 History\r\n1.1 Principles\r\n1.2 Versions\r\n2 Practices\r\n2.1 Java platform\r\n2.1.1 Implementations\r\n2.1.2 Performance\r\n2.2 Automatic memory management\r\n3 Syntax\r\n4 \"Hello world\" example\r\n5 Special classes\r\n5.1 Applet\r\n5.2 Servlet\r\n5.3 JavaServer Pages\r\n5.4 Swing application\r\n5.5 Generics\r\n6 Criticism\r\n7 Use outside of the Java platform\r\n7.1 Android\r\n7.1.1 Controversy\r\n8 Class libraries\r\n9 Documentation\r\n10 Editions\r\n11 See also\r\n11.1 Comparison of Java with other languages\r\n12 Notes\r\n13 References\r\n14 External links\r\n');

After running above DB Script storetextfile_table will look like as below:

DBUtil.java class which is responsible to connect with MySQL database.

package com.infotech.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
	private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
	private static final String DB_USERNAME="root";
	private static final String DB_PASSWORD="root";
	private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";
	
	private static Connection connection = null;
	static{
		try {
			Class.forName(DB_DRIVER_CLASS);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";

private static final String DB_USERNAME="root";

private static final String DB_PASSWORD="root";

private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";

private static Connection connection = null;

static{

try {

Class.forName(DB_DRIVER_CLASS);

connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

public static Connection getConnection(){

return connection;

}

ClientTest.java class uses to read three text files(C language.txt,C++ language.txt and Java language.txt) from database.

package com.infotech.client;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.infotech.util.DBUtil;
/**
 * @author KK JavaTutorials
 *JDBC program to read text files from database
 */
public class ClientTest {
	
	public static void main(String[] args) throws SQLException   {
		
		String SQL="SELECT *FROM storetextfile_table";
		try(Connection connection = DBUtil.getConnection();
				PreparedStatement ps = connection.prepareStatement(SQL);ResultSet 
				rs = ps.executeQuery()) {
			System.out.println("Following flies are downloaded from database..");
			while (rs.next()) {
				int fileId = rs.getInt("file_id");
				String fileName = rs.getString("file_name");
				long fileSizeInKb = rs.getLong("file_size_in_kb");
				String fileExtension = rs.getString("file_extension");
				System.out.println("File Id:"+fileId);
				System.out.println("File Name:"+fileName);
				System.out.println("File Size In KB:"+fileSizeInKb);
				System.out.println("File Extension:"+fileExtension);
				Clob clob = rs.getClob("file_content");
				InputStream inputStream = clob.getAsciiStream();
				
				System.out.println("-----------------------------------");
				Files.copy(inputStream, Paths.get("DownLoadFiles/"+fileName));
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

package com.infotech.client;

import java.io.IOException;

import java.io.InputStream;

import java.nio.file.Files;

import java.nio.file.Paths;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.infotech.util.DBUtil;

/**

* @author KK JavaTutorials

*JDBC program to read text files from database

public class ClientTest {

public static void main(String[] args) throws SQLException {

String SQL="SELECT *FROM storetextfile_table";

try(Connection connection = DBUtil.getConnection();

PreparedStatement ps = connection.prepareStatement(SQL);ResultSet

rs = ps.executeQuery()) {

System.out.println("Following flies are downloaded from database..");

while (rs.next()) {

int fileId = rs.getInt("file_id");

String fileName = rs.getString("file_name");

long fileSizeInKb = rs.getLong("file_size_in_kb");

String fileExtension = rs.getString("file_extension");

System.out.println("File Id:"+fileId);

System.out.println("File Name:"+fileName);

System.out.println("File Size In KB:"+fileSizeInKb);

System.out.println("File Extension:"+fileExtension);

Clob clob = rs.getClob("file_content");

InputStream inputStream = clob.getAsciiStream();

System.out.println("-----------------------------------");

Files.copy(inputStream, Paths.get("DownLoadFiles/"+fileName));

}

} catch (IOException e) {

e.printStackTrace();

}

After running ClientTest.java program you will look below output on eclipse console:

Following flies are downloaded from database..
File Id:1
File Name:C language.txt
File Size In KB:1
File Extension:txt
-----------------------------------
File Id:2
File Name:C++ language.txt
File Size In KB:1
File Extension:txt
-----------------------------------
File Id:3
File Name:Java language.txt
File Size In KB:2
File Extension:txt
-----------------------------------

Following flies are downloaded from database..

File Id:1

File Name:C language.txt

File Size In KB:1

File Extension:txt

-----------------------------------

File Id:2

File Name:C++ language.txt

File Size In KB:1

File Extension:txt

-----------------------------------

File Id:3

File Name:Java language.txt

File Size In KB:2

File Extension:txt

-----------------------------------

Now just refresh your project and expand DownLoadFiles folder you will be three files are downloaded from database and written in this folder as given in below screen shot.

That’s all about How to read files from MySQL database using JDBC

You May Also Like:

How to store file in MySQL database using JDBC
Writing and Reading MySQL BLOB/binary files Using JDBC
ResultSetMetaData in JDBC example
How to get primary key value (auto-generated keys) from inserted queries using JDBC?

If you have any feedback or suggestion please feel free to drop in blow comment box.

Leave a Reply Cancel reply