Saturday, August 15, 2015

Executing JAR file using Batch Command(.bat)

Below is the script to run a JAR file using a .bat command, copy and paste it into the notepad and then save as batch_file_name.bat

@echo OFF
cd C:\upload
java -jar compareRun.jar
pause

line 2 is the folder path for jar file, please change as per your requirements.
line 3 is the actual execution of the jar file
line 4 would avoid closing of console, so you can view the output.
 

Friday, August 14, 2015

Excel data to Sqlite3 Database table using Java

Below is the code for inserting data from excel sheet to sqlite3 database using Java code.

1.Open new eclipse Java Project and reference to the latest Java jdbc-sqlite jar file from    below url
   https://bitbucket.org/xerial/sqlite-jdbc/downloads

2. Also reference to the Apache POI - the Java API for Microsoft Documents, which we require for reading through the excel file
    http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.12-20150511.tar.gz

3. Below is the code for the same

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.sqlite.core.DB;

public class run {
public static void main(String args[]) {
try {

            Class.forName("org.sqlite.JDBC");
Connection con = DriverManager.getConnection("jdbc:sqlite:test.db"); 
PreparedStatement pstm = null; Statement stmt = null; 
con.setAutoCommit(false);

pstm = (PreparedStatement) con.prepareStatement("DROP TABLE IF 
EXISTS table_name"); pstm.execute();

stmt = con.createStatement(); 
String sql = "CREATE TABLE table_name " + "(ID INTEGER PRIMARY KEY 
AUTOINCREMENT," + " NAME         TEXT)"; 
stmt.executeUpdate(sql);
stmt.close();


FileInputStream input = new FileInputStream("C:\\Emp.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Row row;
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
String col2 = row.getCell(1).getStringCellValue();
String sqlcommand = "INSERT INTO table_name(NAME) Values('" + col2 + "')";
pstm = (PreparedStatement) con.prepareStatement(sqlcommand);
pstm.execute();
System.out.println("Import rows " + i);
}
con.commit();
pstm.close();

con.close();
input.close();
System.out.println("Success import excel to sqlite Database");
} catch (ClassNotFoundException e) {
System.out.println(e);
} catch (SQLException ex) {
System.out.println(ex);
} catch (IOException ioe) {
System.out.println(ioe);
}
}
}