Forum Stats

  • 3,733,036 Users
  • 2,246,686 Discussions
  • 7,856,489 Comments

Discussions

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

Kalpataru
Kalpataru Member Posts: 4,615 Bronze Crown
edited May 2016 in New To Java

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.

Kalpataru

Best Answer

  • Jiri.Machotka-Oracle
    Jiri.Machotka-Oracle Member Posts: 5,078
    edited May 2016 Accepted Answer

    Replace the command fwh.write(resultset1.getString(i) + "|"); with something like:


    String value = resultset1.getString(i);

    if (value != null) {

      fwh.write(value+"|");

    }

    else {

      fwh.write("|");

    }


    This will enable to check what's returned and/or replace nulls with whatever you want.

    KalpataruKalpataru

Answers

This discussion has been closed.