Inserting Values From Text File To MySQL

This tutorial shows how you can insert some data from a text file to a MySQL database. You can also extend this to work with other databases by changing the driver.

package com.kushal.utilities;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class InsertTextFromFileToMySql {

public static Connection getConnection() throws Exception {
	String driver = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://localhost/sanjaal_com";
	String username = "root";
	String password = "";

	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url,
                                  username, password);
	return conn;
}

public static void main(String[] args) throws Exception {
	String textValue = "";
	int textLength = 0;
	String category = "funny";
	String lineRead = null;
	PreparedStatement pstmt = null;

	String fileNameToInsertTextFrom = "C:/temp/smsCollection002.txt";
	FileReader fileReader = new FileReader(new File(
			fileNameToInsertTextFrom));

	Connection conn = null;
	try {
		conn = getConnection();
		conn.setAutoCommit(false);

		BufferedReader reader = new BufferedReader(fileReader);
		pstmt = conn.prepareStatement(
		"insert into smsTable(sms_text, sms_length, sms_category)"+
		"values (?, ?, ?)");

		while ((lineRead = reader.readLine()) != null) {
			try {
				textValue = lineRead.trim();
				textLength = textValue.length();

				if (textLength > 0) {

					pstmt.setString(1, textValue);
					pstmt.setInt(2, textLength);
					pstmt.setString(3, category);
					pstmt.executeUpdate();
					conn.commit();
				}

			} catch (NullPointerException npe) {
				// do nothing proceed to another line
			}
			lineRead = null;
		}

	} catch (Exception e) {
		System.err.println("Error: " + e.getMessage());
		e.printStackTrace();
	} finally {
		pstmt.close();
		conn.close();
	}
	}
}

Reading / Writing File in Java and String Manipulation

I wrote this piece of java code because I encountered a situation where I had a list of some strings from some table in database and needed to create an in clause. Since the number of items was huge, it was not possible to manually add quote the string and add a comma.

Here was my requirement.

I had string in the following format:

14685
14701
14707
14720
14738
14741
14743
14745
14747
14748
14749
14750
14751
14761
14764
14767
14768
14771
14775
14776

And I needed the string formatted something like the following so that I could put that in ‘in clause’ in one of the SQL.

‘14685’,  ‘14701’,  ‘14707’,  ‘14720’,  ‘14738’,  ‘14741’,  ‘14743’,  ‘14745’,  ‘14747’,  ‘14748’,
‘14749’,  ‘14750’,  ‘14751’,  ‘14761’,  ‘14764’,  ‘14767’,  ‘14768’,  ‘14771’,  ‘14775’,  ‘14776’

So, to save my time doing the impossible manual change (I had around 2000 of these items), I wrote this simple program. This program features File Read, File Write operation in Java and also shows simple Java String manipulation techniques.

package com.kushal.sql.utilities;
/**
 * @author Kushal Paudyal
 * Created on 10/02/2008
 * www.sanjaal.com/java
 *
 * Demonstrates the following Java features
 * --Reading a File in Java
 * --Simple String manipulations
 * --Writing to a File in Java
 */
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;

public class FormInStringForSQL {

	public static void main(String args [])
	{
		try{
			String inputFileName="C:/items_that_were_marked.txt";
			String outputFileName="C:/myoutputfile.txt";

			BufferedReader in = new BufferedReader(new FileReader(inputFileName));
			String line="";
			int count=0;
			String formattedStringForInClause="";

			/**Read the file and do the necessary formatting**/
			while ((line = in.readLine()) != null)
			{
				if(count%10>0) /**Formatting to 10 columns**/
					formattedStringForInClause+=" '"+line+"', ";
				else
					formattedStringForInClause+="n '"+line+"', ";
				count++;
			}

			/**Removing the trailing final comma (')**/
			if(formattedStringForInClause.endsWith(", "))
				formattedStringForInClause=
                                formattedStringForInClause.substring(
				0,formattedStringForInClause.length()-2);

			/**Writing the formatted string to file**/
			BufferedWriter out = new BufferedWriter(new FileWriter(outputFileName));
	        out.write(formattedStringForInClause);
	        out.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

}