Skip to Main Content

Oracle sql query in java program writes "null" in file

KalpataruMay 5 2016 — edited May 8 2016

Hi All,

I hope you all are doing well.

I have write a java program using sql query to write into a pipe(|) separated text file.

But by default it writes "null" into the file where the column have no values.

If i use nvl(ename,' ') in varchar2 columns it writes a space into the file.

import java.io.*;

import java.sql.*;

class employeedet {

    public employeedet() {

        recordcount = 0;

        totrecordcount = 0;

        mydt = "";

    }

    //String p_no;

    String report_date = "";

    String report_run_date = "";

    String output_dir;

    File fh;

    FileWriter fwwh;

    PrintWriter fwh;

    int recordcount;

    int totrecordcount;

    String mydt;

    public void mainCall(String args0, String args1, String args2, String args3, String args4, String args5, String args6) {

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@" + args0 + ":" + args1 + ":" + args2, args3, args4);

            PreparedStatement pstmt = connection.prepareStatement("Select to_char(TRUNC(sysdate),'dd.mm.yy') report_run_date,to_char(TRUNC(sysdate+2),'dd.mm.yy') report_date from dual");

            for (ResultSet resultset = pstmt.executeQuery(); resultset.next();) {

                report_run_date = resultset.getString(1);

                report_date = resultset.getString(2);

            }

            output_dir = "D:\\Program Files\\BATCHTEST";

            String filename1 = output_dir + "/EMP_" + report_run_date + "_" + report_date + ".txt";

            fh = new File(filename1);

            fwwh = new FileWriter(fh);

            fwh = new PrintWriter(new BufferedWriter(fwwh));

            printcol_header();

            pstmt = connection.prepareStatement("select empno,ename,job,mgr,hiredate,sal,nvl(comm,null) from scott.emp ");

            ResultSet resultset1 = pstmt.executeQuery();

            ResultSetMetaData resultsetmetadata = resultset1.getMetaData();

            String s9 = "aaaa";

            while (resultset1.next())

            {

                recordcount = recordcount + 1;

                for (int i = 1; i <= resultsetmetadata.getColumnCount(); i++) {

                    //p_no = resultset1.getString(13);

                    fwh.write(resultset1.getString(i) + "|");

                }

                fwh.write("\n");

                s9 = resultset1.getString(1);

            }

            System.out.println("No of record for Location Number" + s9 + "/" + recordcount);

            fwh.flush();

            fwh.close();

        } catch (SQLException sqlexception) {

            sqlexception.printStackTrace();

        } catch (Exception exception) {

            exception.printStackTrace();

        }

    }

    private void printcol_header() throws IOException

    {

        String as[] = {"Empno", "Ename", "Job", "Mgr", "Hiredate", "Salary", "commission"};

        for (int i = 0; i < as.length; i++) {

            fwh.write(as[i] + "|");

        }

        fwh.write("\n");

    }

    public static void main(String args[]) {

        employeedet bdl = new employeedet();

        bdl.mainCall("172.17.6.206", "1521", "ORADEV", "axsys1", "axsys45", "000002", "MAGMA FINCORP LIMITED");

    }

}

It prints like this into file while using nvl(comm,null) or comm and also not print the hire date column correctly.

       

EmpnoEnameJobMgr      Hiredate      Salary commission
7369SMITHCLERK790200:00.0800

  null

But don't want print null or space , if the column value is null then it always writes blank(not space or null) to the file.

How do i do this ?

Any help is deeply appreciated.

This post has been answered by Jiri.Machotka-Oracle on May 6 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Jun 5 2016
Added on May 5 2016
12 comments
2,357 views