Forum Stats

  • 3,726,735 Users
  • 2,245,248 Discussions
  • 7,852,382 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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.