JAVA to SQL

JAVA to SQL

package abhijeet;

import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Locale;

import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.WorkbookSettings; import jxl.read.biff.BiffException;

public class Abhijeet {

public void init(String filePath) {
    File fileXYZ = new File(filePath);
    FileInputStream fs = null;
    try {
        fs = new FileInputStream(fileXYZ);
        contentReading(fs);
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            fs.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

//Returns the Headings used inside the excel sheet
public void getHeadingFromXlsFile(Sheet sheet) {
    int columnCount = sheet.getColumns();
    for (int i = 0; i < columnCount; i++) {
        System.out.println("Coloumn Count : "+sheet.getCell(i, 0).getContents());
    }
}

public void contentReading(InputStream fileInputStream) {
    WorkbookSettings ws = null;
    Workbook workbook = null;
    Sheet s = null;
    Cell rowData[] = null;
    int rowCount = 0;
    int columnCount = 0;
    int totalSheet = 0;

    try {
        ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        workbook = Workbook.getWorkbook(fileInputStream, ws);

        totalSheet = workbook.getNumberOfSheets();
        if(totalSheet > 0) {
            System.out.println("Total Sheet Found:" + totalSheet);
            for(int j=0;j<totalSheet ;j++) {
                System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
            }
        }


        s = workbook.getSheet(0);


        System.out.println("Total Rows inside Sheet:" + s.getRows());
        rowCount = s.getRows();


        System.out.println("Total Column inside Sheet:" + s.getColumns());
        columnCount = s.getColumns();

        ArrayList arrMain = new ArrayList();
        for (int i = 0; i < rowCount; i++) {

            rowData = s.getRow(i);

            if (rowData[0].getContents().length() != 0) { // the first date column must not null
                for (int j = 0; j < columnCount ;j++) 
                {

                    ArrayList<String> arrData=new ArrayList();

                        switch (j) {

                    case 0:
                    {
                        System.out.println("AP Code: "+rowData[j].getContents());
                        arrData.add(rowData[j].getContents());
                    }

                    break;
                    case 1:
                    {
                        System.out.println("AP Name: "+rowData[j].getContents());
                        arrData.add(rowData[j].getContents());
                    }
                    break;
                    case 2:

                    {
                        System.out.println("City: "+rowData[j].getContents());
                        arrData.add(rowData[j].getContents());
                    }
                    break;
                    case 3:

                    {
                        System.out.println("Country: "+rowData[j].getContents());
                        arrData.add(rowData[j].getContents());
                    }





                            }
                        arrMain.add(arrData);
                        }


                }


            {
                workbook.close();       

        }


    }
    }
    catch (IOException e)
    {

        e.printStackTrace();

    }

    catch (BiffException e)
    {

        e.printStackTrace();
    }
}

    public int arrMain() {
    // TODO Auto-generated method stub
    return 0;
}

    public static void main(String[] args) {
    try {
        Abhijeet xlReader = new Abhijeet();
        String fileN = "D:/ProjectExcel/First_Project.xls"; 
        Connection conn =getSimpleConnection();
        Statement stmt = conn.createStatement();
        String InstQuery="Insert into First_Project (Airport_code,Airport_name,City,Country) values (arrData.add(Airport_code),arrData.add(Airport_name),arrData.add(City),arrData.add(Country))";
        xlReader.init(fileN);
        ArrayList arrMain = null;
        stmt.execute(InstQuery);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private static void ArrayList() { // TODO Auto-generated method stub

    }

static Connection getSimpleConnection() { //See your driver documentation for the proper format of this string : String DBCONNSTRING = "jdbc:mysql://localhot:3306/mysql"; //Provided by your driver documentation. In this case, a MySql driver is used : String DRIVERCLASSNAME = "com.mysql.jdbc.Driver"; String USER_NAME = "navin"; String PASSWORD = "n@v!n";

            Connection conn = null;

         /*   try {
               Class.forName(DRIVER_CLASS_NAME).newInstance();
            }
            catch (Exception ex){
               log("Check classpath. Cannot load db driver: " + DRIVER_CLASS_NAME);
            }

            try {
              conn = (Connection) DriverManager.getConnection(DB_CONN_STRING, USER_NAME, PASSWORD);
            }
            catch (SQLException e)
            {
               log( "Driver loaded, but cannot connect to db: " + DB_CONN_STRING);
            }*/
            return conn;
          }

}

plzzz help mw wid dis...

1: i hav read and Excel file in java and printed its contents

2: Now i want to redirect dat oupput to MYSQL manager 2005 so i have connectivity code wid but i m not getting n e error nor i m getting any output so plzzz kindly help me

View Answers

May 24, 2011 at 5:01 PM

import java.io.*;
import java.sql.*;
import java.util.*;
import jxl.Cell; 
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings; 
import jxl.read.biff.BiffException;

public class ReadExcelFile{
ArrayList<String> list1=new ArrayList<String>();
ArrayList<String> list2=new ArrayList<String>();
ArrayList<String> list3=new ArrayList<String>();
ArrayList<String> list4=new ArrayList<String>();

public void getHeadingFromXlsFile(Sheet sheet) {
    int columnCount = sheet.getColumns();
    for (int i = 0; i < columnCount; i++) {
        System.out.println("Coloumn Count : "+sheet.getCell(i, 0).getContents());
    }
}

public void contentReading() {
    WorkbookSettings ws = null;
    Workbook workbook = null;
    Sheet s = null;
    Cell rowData[] = null;
    int rowCount = 0;
    int columnCount = 0;
    int totalSheet = 0;

    try{
        ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        workbook = Workbook.getWorkbook(new File("C:/data.xls"), ws);

        totalSheet = workbook.getNumberOfSheets();
        if(totalSheet > 0) {
            System.out.println("Total Sheet Found:" + totalSheet);
            for(int j=0;j<totalSheet ;j++) {
            System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
            }
        }
        s = workbook.getSheet(0);
        System.out.println("Total Rows inside Sheet:" + s.getRows());
        rowCount = s.getRows();
        System.out.println("Total Column inside Sheet:" + s.getColumns());
        columnCount = s.getColumns();
        for(int i = 1; i < rowCount; i++){
            rowData = s.getRow(i);
            if (rowData[0].getContents().length() != 0) { 
                for(int j = 0; j < columnCount ;j++){
                 switch (j) {
                    case 0:
                       System.out.println("Name: "+rowData[j].getContents());
                       list1.add(rowData[j].getContents());
                       break;
                    case 1:
                      System.out.println("Address: "+rowData[j].getContents());
                      list2.add(rowData[j].getContents());
                      break;
                    case 2:
                      System.out.println("Contact: "+rowData[j].getContents());
                      list3.add(rowData[j].getContents());
                      break;
                    case 3:
                      System.out.println("Email: "+rowData[j].getContents());
                     list4.add(rowData[j].getContents());
                     break;
                     }
                       }
                }
    }
    workbook.close(); 
    try{
   Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/roseindia", "root", "root");
         Statement st=conn.createStatement();
         for(int i=0;i<list1.size();i++){
             String name=list1.get(i).toString();
             String address=list2.get(i).toString();
             String contact=list3.get(i).toString();
             String email=list4.get(i).toString();
         st.executeUpdate("insert into employee(name,address,contactNo,email) values('"+name+"','"+address+"','"+contact+"','"+email+"')");
         }
        System.out.println("Inserted successfully!");
    }
    catch(Exception e){
    System.out.println(e);
    }
    }
    catch (Exception e){
        e.printStackTrace();
    }
}
    public static void main(String[] args) {
    try {
        ReadExcelFile xlReader = new ReadExcelFile();
        xlReader.contentReading();
            } catch (Exception e) {
        e.printStackTrace();
    }
}
}









Related Tutorials/Questions & Answers:
Java and SQL - SQL
Java and SQL  i have a problem with regard to connecting my java code in getting a data to SQL. my problem is on how to come up with a pop up alert message in the user interface where in the message will be coming from the SQL
java,sql
java,sql  Hello sir,iam sireesha studying MCA.i want a project on educational details of a student by using jdbc,odbc connection including sql queries.please send me the reply.  Hi, Please visit http://roseindia.net
Advertisements
Java sql Exception
Java sql Exception  difference between sql exception class and sql warning class
sql,java
sql,java   i am writing some code in java.that values are stored in database.plz send me one example for this for example taking the fields as s.no,category,name of the candidate,age,title of the project,video uploading,photo
java - SQL
java  hi . i am asifa and i for the last few days i was trying to display the backend fields to the frontend ,am using sql as the backend am using ecclipseeuropa IDE .can u please send the code for displaying the backend fields
sql connectivity through java
sql connectivity through java  i want an example for java db through sql connection. Thanks in advance
java .sql
java .sql  where are the interfaces of java.sql like STATEMENT,CONNECTION are defined
java,sql
java,sql  Hello sir,iam an MCA student.I want a project code of jdbc,odbc connection for educational details of a student.please send me the code.  you din't send me the reply,please send it fastly sir
JAVA to SQL
JAVA to SQL  package abhijeet; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import...; } } plzzz help mw wid dis... 1: i hav read and Excel file in java
java,sql
java,sql  Hello sir,iam sireesha studying MCA.I want a project code on JDBC,ODBC connection to display student administration system (or) student information system (or) college administraion system.Please send me the code sir
java-sql - JDBC
java-sql  how to convert excel file into sql database usin java programming
Import Data to SQL database in Java
Import Data to SQL database in Java  Hi, I'm a beginner and I'm wondering how to Import Data to SQL database in Java? Thanks (Platform: Eclipse/SQL
sql
difference between sql and oracle  what is the difference between sql and oracle
sql
sql  how to get first row in sql with using where condition in sql? how to get last row in sql with using where condition in sql
sql
sql  I want interview sql questions   Please visit the following link: SQL Tutorials
Sql
Sql  how to find maximum of a101,a102,a103 from a table in sql
Java to SQL Server Connectivity
Java to SQL Server Connectivity  Hi, heres my code private void bookedButton() { try { Class.forName... with sql server connectivity at the background to save the data. Now, after
SQL
SQL  hii What is sql?   hello,ADS_TO_REPLACE_1 SQL, which stands for Structured Query Language, is a special-purpose language used to define, access, and manipulate data. SQL is non procedural, meaning
sql
sql  returning value from a store procedure in sql with example   Please visit the following links: http://www.roseindia.net/mysql/mysql5/stored-procedures-and-functions.shtml http://www.roseindia.net/sql/create
SQL
SQL  In my computer i have microsoft sql 2008.how can i set that in the cmd.i want to use that in cmd.what is the default username and password
SQl
SQl  . Given two tables: table1(player, groundname, numcenturies); table2(ground_name, country); Write and SQL query for the following: " Select all the players from table1 who has
SQL
SQL   1)How to Store 1000 records in Oracle object. 2)Write a query for calculate highest, 3rd Highest & 10th highest salary from emp teble. 3)What is Sequence. 4)How to use rowid in Sql. 5)What is Views. 6)How
Java Compilation error - SQL
Java Compilation error  ava.lang.NullPointerException at beanUtils.SaleHeaderUtility.getFullRemainPatientList(SaleHeaderUtility.java:754) at forms.ProcessDialog.updatePatientBalances(ProcessDialog.java:119
java,sql - JSP-Servlet
java,sql  Hi Amardeep, Thank you from the bottom of my heart for giving me some excellent well thought out code. Can you please... any appreciable result. Unfortunately, my java coding skills are not as sharp
java sql - JSP-Servlet
java sql  I need to diplay only 10 records per page from my resultset. while(rs.next())loops through entire rs. How can I use a for loop in conjunction with the while loop to display only 10 records per page and then continue
SQL
SQL  how to get ( 15 march 2011) and (15/03/2011) output using SQL   Use the following queries to get the data from database in the given format. For (15 march 2011) format: SELECT DATE_FORMAT(dob, '%d %M %Y') FROM
SQL
SQL  how to get ( 15 march 2011) and (15/03/2011) output using SQL   Use the following queries to get the data from database in the given format. For (15 march 2011) format: SELECT DATE_FORMAT(dob, '%d %M %Y') FROM
SQL
SQL Trigger query  Why we use the Trigger and what it's uses?   A database trigger is procedural code that is automatically executed..., user events, and SQL statements to subscribing applications 10)Restrict DML
SQL
tables EMP, and DEPT, the structure for which are given above. Write SQL queries
SQL
events, user events, and SQL statements to subscribing applications 10)Restrict DML
SQL
, user events, and SQL statements to subscribing applications 10)Restrict DML
SQL
events, and SQL statements to subscribing applications 10)Restrict DML
DataBase connectivity in java with Microsoft sql server2000 - JDBC
DataBase connectivity in java with Microsoft sql server2000  How i can achieve database connectivity with Microsoft sql server2000 in my java source code
Storing Multiple image in sql using java
Storing Multiple image in sql using java  Hi, How to store and retrieve a multiple image in sql using java but already i have created sql table if i want to insert a image while runtime execution. Can anyone tell me solution
sql - SQL
order by SQL Query  What is order by in SQL and when this query is used in SQL?Thanks!  Hi,In case of mysql you can user following query.Select salary from salary_table order by salary desc limit 3,1Thanks
Java servlet with jsp on sql server
Java servlet with jsp on sql server  How to delete a user by an admin with check box in Java Servlet with jsp on Sql Server?   Here is a jsp example that can delete the multiple record from the database by selecting
sql - SQL
want query? 4.what is the diffrence between sql and plsql?  Hi Friend... HAVING n>1; Difference between SQL and PL/SQL: SQL is a structured query...(select,insert,update etc.), manipulate objects(DDL) and data(DML). PL/SQL
sql - SQL
sql  write a dyamic sql program to display the name of a employee whose salary is <5000  Hi Friend, Use the following query... the following link: http://www.roseindia.net/sql/ Thanks
Executing Set of SQL statements in Java
Executing Set of SQL statements in Java  Hi, I am trying to execute a procedure which is stored in MS SQL server's database. I have configured the driver with ther server name, database name and uid/pwd using a callable
java and sql server NOT Mysql - JSP-Servlet
java and sql server NOT Mysql  Hi guys. Thank you all for the various helps you render to everyone. Please the question I asked the last time... image from MS SQL server NOT MySQL. Or is it not possible to do it in MS SQL server
What should I learn first SQL or Java?
What should I learn first SQL or Java?  Hi, I am total beginner... in programming particularly Java. What should I learn first SQL or Java? Thanks  ... and after that you can learn SQL. Learning Java first is important its the base
Java servlet sql connectivity error - JSP-Servlet
Java servlet sql connectivity error  Hi, I have been trying to connect to sql database via the servlet program in java.I have not been successful so far. However when i try to connect to sql through a normal java program
SQL - SQL
SQL  Jus c da case we hve certain images in some folder or some area in sql can we create trigger or procedure so dat in front like in asp page while clicking image it will be showed in page ...... ...I mean writing code for each
SQL - SQL
SQL  How to combine 'LIKE' and 'IN' operators in sql query?   select name from tab1 where name like 'R%' AND city in('bangalore','mumbai'); Table Name: tab1 FieldName ===================== | name | city
java oracle connection problem - SQL
java oracle connection problem  I have successfully made connection with the oracle10g database that i am using and also able to put in data using JSP. But the codes that i have tried for fetching the data are not working even
sql - SQL
sql functions with examples  I need sql functions with examples to learn fast.  Hi friend,<%@ page language="java" import...("SQL statement is not executed!"); } } catch (Exception e
Maven dependency for commons-sql - commons-sql version 1.0-dev.20021010.032657 is released. Learn to use commons-sql version 1.0-dev.20021010.032657 in Maven based Java projects
; commons-sql - commons-sql version 1.0-dev.20021010.032657 java library... of commons-sql released The developers of   commons-sql - commons-sql project have released the latest version of this library on 10 Aug 2016
Maven dependency for commons-sql - commons-sql version 1.0-dev.20020916.024425 is released. Learn to use commons-sql version 1.0-dev.20020916.024425 in Maven based Java projects
of commons-sql released The developers of   commons-sql - commons-sql project have released the latest version of this library on 10 Aug 2016, the released version of  commons-sql - commons-sql library
SQL injunctions - SQL
SQL injunctions  Could you please tell me how to handle the sql injunctions?  Hi friend, SQL injection is a technique that exploits.... Prevention against SQL injection, User input must not directly be embedded in SQL
netbeans ddl and dml sql statements - Java Beginners
netbeans ddl and dml sql statements  Kindly send me the ddl,dml and tcl commands while in netbeans. wilson

Ads