Reading tab/space delimited files & inserting data in MySql Database in Java.


This is an example code to extract tab or space delimited data from a flat file & insert into a MySql database table.
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
import java.util.regex.*; //for regular expression in java

public class Gene_to_db
{
public static void main(String[] args) throws Exception
{
Connection conn=null;
PreparedStatement gene_info_stmt=null;
PreparedStatement gene_symbol_stmt=null;
PreparedStatement interactions_stmt=null;
BufferedReader gene_info_Reader = null;
BufferedReader interactions_Reader=null;
try
{
String driver=”com.mysql.jdbc.Driver”;
conn = DriverManager.getConnection(“jdbc:mysql://localhost/gene_to_db”, “root”, “root123”);

String gene_info_path = “/home/vaibhav/Dr.Sucha’s Module/Homo_sapiens.gene_info”;
String interactions_path=”/home/vaibhav/Dr.Sucha’s Module/human_only”;

gene_info_Reader = new BufferedReader(new FileReader(gene_info_path));
interactions_Reader=new BufferedReader(new FileReader(interactions_path));

String line;
while ((line = gene_info_Reader.readLine()) != null)
{
String datavalue[] = line.split(“\t”);
String gene_id = datavalue[1];
String taxo_id = datavalue[0];
String gene_symbol = datavalue[2];
String chr_no = datavalue[6];

if(chr_no.matches(“^\\d+$”) || (chr_no.matches(“^[X]$”)) || (chr_no.matches(“^[Y]$”)))
{
gene_info_stmt = conn.prepareStatement(“INSERT INTO gene_info VALUES (?,?,?,?)”);
gene_info_stmt.setString(1,gene_id);
gene_info_stmt.setString(2,taxo_id);
gene_info_stmt.setString(3,gene_symbol);
gene_info_stmt.setString(4,chr_no);
gene_info_stmt.execute();

gene_symbol_stmt=conn.prepareStatement(“INSERT INTO gene_symbol VALUES(?,?)”);
gene_symbol_stmt.setString(1,gene_id);
gene_symbol_stmt.setString(2,gene_symbol);
gene_symbol_stmt.execute();
}
else
{
continue;
}
}

while ((line = interactions_Reader.readLine()) != null)
{
String datavalue[] = line.split(“\t”);
String tax_id1=datavalue[0];
String tax_id2=datavalue[5];
String gene_id=datavalue[1];
String interactant_id=datavalue[6];
interactions_stmt=conn.prepareStatement(“INSERT INTO interactions VALUES(?,?)”);
interactions_stmt.setString(1,gene_id);
interactions_stmt.setString(2,interactant_id);
interactions_stmt.execute();
}

}

catch(Exception ex)
{
ex.printStackTrace();
}
finally
{
try
{
gene_info_stmt.close();
gene_info_Reader.close();
interactions_Reader.close();

} catch (SQLException ex)
{
ex.printStackTrace();
}
}

}
}

About Vaibhav

Friendly & Co-operative.

One response to “Reading tab/space delimited files & inserting data in MySql Database in Java.”

  1. Saurabh says :

    please also mention about the installation of JDBC.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: