Adding timestamp to the CSV file name would be useful for the end users who will be able to differentiate each data export.The writeHeaderLine() method writes the column names in first line of the CSV file. Try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) So if the table name is Review, then the CSV file name would be Review_Export_20-52-55.csv. String csvFilePath = "Reviews-export.csv" String jdbcURL = "jdbc:mysql://localhost:3306/sales" ![]() * A simple Java program that exports data from database to CSV file. Simple Java code example to export from database to CSV fileThe following code is for a simple Java program that connects to a MySQL database reads all rows from the review table and write that data to a CSV file: package net.codejava And then I will share with you how to write general, reusable code that works with any table. And a JDBC driver library for the underlying database is necessary (MySQL is used in this post).Suppose that we have a table with the following structure: And this table contains some data like this: First, you will learn how to write Java code to export data from this table to a CSV file. We use JDBC to read data from database and use File I/O to write CSV file. In this article, I will guide you how to write Java code to read data from a database and write to a CSV file.The technique is simple. How to implement remember password featureĮxporting data from database to CSV files is a common task of any software applications.How to implement forgot password feature.How to read password-protected Excel file in Java.Java File Encryption and Decryption Example.Compile and run a Java program with Sublime Text.Compile and Run a Java Program with TextPad.File Upload to Database with Spring and Hibernate.File Upload to Database with Servlet, JSP, MySQL.Java Servlet and JSP Hello World Tutorial.To test this code, change the mark column to Varchar and add some string data to mark field. This will give us non-numeric value in mark field. SELECT name, mark FROM student WHERE CONCAT('',(mark*1)) !=mark Now we will use this concept to list out which are not matching. When a string is multiplied by 1 we get the output as zero. SELECT name, mark,(mark*1) AS mark2 FROM studentįor all numeric values in the above query, we will get both columns the same value. But there are some string data is stored in the mark column and mark column is set as Varchar field. We have stored mark in the student table. INSERT INTO `student_name` (`f_name`, `l_name`, `class`) VALUESįinding non integer values in an Integer field using concat Here is the sql dump of student_name table for your use.ĬREATE TABLE IF NOT EXISTS `student_name` ( Joining three columns with separator SELECT CONCAT ( f_name,'/',l_name,'/',class) as file_name If we require that it should ignore the null data and display only the available data then we have to use COALESCE and displayĬONCAT(COALESCE(f_name,'-',' '),' ',COALESCE(l_name,'-')) as name, Note that we have kept some null data in our records, in the above query if f_name or l_name is null then the total value after concat will be null. Now using concat we will combine data of two columns and display them as the only name.ĬONCAT(f_name,' ',l_name) as name,class FROM `student_name` In our table we have a first name and last name stored. We can use the Concat function to join two columns and display them as a single column along with other data. To find out null data we will use ifnull() UPDATE `table_name` SET dir=concat(ifnull(dir,''),'/') Combining two columns to display as one using select query If null data is present in our record, then we can convert it to empty string and they use concat to add strings. Try to understand difference between replace and concat. ![]() Try to change some part of the data by using replace command. UPDATE `table_name` SET dir=concat(dir,'/') To add / at end of the data of all records we can use like this. This will add forward slash to the columns dir for all the records (on the left side ). UPDATE `table_name` SET dir=concat('/',dir) We want to update all the records by adding forward slash to the data inside the dir name. In our table, we stored the directory name as 'html_tutorial' for all the records. ![]() If we will not use Where condition in sql then all the records will be changed with concat query.Īdding / to dir name stored in MySql table The above command will add the string site_data to add at the end of each record dtl field for which gal_id is equal to 22
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |