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();
	}
	}
}

SQL Example To Create Table On MySql DB

This is a very basic script that creates a table in the database. This has been tested to work with mysql, but similar format can be applied to other databases such as MS SQL Server, Oracle, IBM DB2 etc.

Here is the database schema that we want to create:

The code to create this table is:

CREATE TABLE KP_BANK_PYMT_INFO(
	INFO_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	BANK_NAME VARCHAR(100),
	CURRENT_BAL DECIMAL(5,2),
	PYMT_DUE_DT TIMESTAMP DEFAULT NOW()
	LAST_PYMT_AMT DECIMAL(5,2).
	LAST_PAID_DT TIMESTAMP,
	APR DECIMAL (3,2),
	REMARKS VARCHAR (100)
)

Utility Class For Getting SQL Connection and Closing It

The following is a sample class which you can use as an utility in getting the mysql connections
and closing it (rather than writing the same code again and again to get and close the connection)

/* @author Kushal Paudyal*/
package com.ghumti.kschool.core.database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.ghumti.fw.domain.KSEntity;

public class KSDBConnection extends KSEntity {
	private String dbUserName = "root";
	private String dbPassword = "";
	private String dbURL = "jdbc:mysql://localhost:3306/";
	private String database = "kschool";
	private String driver = "com.mysql.jdbc.Driver";

	public Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(getDbURL() + getDatabase(),
					getDbUserName(), getDbPassword());

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return conn;
		}

	}

	public void closeConnection(Connection dbConnection, String className) {
		try {
			dbConnection.close();
		} catch (SQLException e1) {
			System.out.println(className
					+ ": Could not close database connection");
			e1.printStackTrace();
		}

	}

	/*
	 * Usage Example
	 */
	public static void main(String args[]) throws Exception {
		KSDBConnection ksdb = new KSDBConnection();
		Connection conn = ksdb.getConnection();

		String sqlQuery = "Select * from myTable";
		Statement st = conn.createStatement();
		st.execute(sqlQuery);

		/*
		 * Finally Close the connection The second parameter is just used for
		 * error printing
		 */
		ksdb.closeConnection(conn, KSDBConnection.class.getName());

	}

}