How to pass a variable's value in where clause in java?

Question

I am trying to exexcute a sql query which contains where condition in it. But whenever I execute the query, it is giving me java.lang.NullPointerException. Please help me in this!

I am using mssql and java

dbURL = "jdbc:sqlserver://ip:portnumber;databaseName=abc";
        username = "abc";
        password = "abc";

        //Load MS SQL JDBC Driver
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        con = DriverManager.getConnection(dbURL,username,password);

        //Creating statement object
        st = con.createStatement();
        selectquery = "select * from abc where TradeType = '"+tradepair+"'";
        rs = st.executeQuery(selectquery);

        while (rs.next()) {

            getpair = rs.getString("TradeType");
            dbTradePair.add(getpair);

        }
        System.out.println("Pairs :-"+dbTradePair);

When "selectquery" is executed, i get "java.lang.NullPointerException". I hope i am executing sql query in proper format.Can anyone please help me out in this.?

Use `PreparedStatement` instead of creating the statement by concatenating parts of SQL and the query value. — Jesper, Jan 23 '19 at 14:34
https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — Stavr00, Jan 23 '19 at 14:35
can you share the line throwing that exception. I can see below lines can throw that: st = con.createStatement(); // if con is null getpair = rs.getString("TradeType"); // if no TradeType column is present dbTradePair.add(getpair); // if dbTradePair is null — ygbgames, Jan 23 '19 at 14:39

JineshEP · Accepted Answer · 2019-01-23T14:54:52.527

2

Like this (Edited - close the resources properly afterwards):

//Load MS SQL JDBC Driver
    try {

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    } catch (ClassNotFoundException e) {

        System.out.println(e.getMessage());

    }

    final String dbURL = "jdbc:sqlserver://ip:portnumber;databaseName=abc";
    final String username = "abc";
    final String password = "abc";

    Connection  con = null;
    PreparedStatement preparedStatement = null;
    final String selectquery = "select * from abc where TradeType = ?";
    try {
        con = DriverManager.getConnection(dbURL,username,password);
        preparedStatement = con.prepareStatement(selectquery);
        preparedStatement.setString(1, tradepair);

        // execute select SQL stetement
        ResultSet rs = preparedStatement.executeQuery();

        while (rs.next()) {

            String tradeType = rs.getString("TradeType");

            System.out.println("tradeType : " + tradeType);
        }

    } catch (SQLException e) {

        System.out.println(e.getMessage());

    } finally {

        if (preparedStatement != null) {
            preparedStatement.close();
        }

        if (con != null) {
            con.close();
        }

    }

edited Jan 23 '19 at 14:54

answered Jan 23 '19 at 14:36

JineshEP

738
4
7

I'd suggest to use try-with-resources, instead of using the cumbersome (and error prone) null check + close in the finally block. – Mark Rotteveel Jan 23 '19 at 15:25
Agree - provided java-7 or higher. – JineshEP Jan 23 '19 at 16:03
Sure, but if people are still stuck on Java 6, they may want to consider moving companies... Java 6 was released 13 years ago and has been out of public support since 2013 and out of extended (paid) support since December last year. – Mark Rotteveel Jan 23 '19 at 16:06
@user1653941 Thank you for your solution.It worked wonder for me! – Krishna Jamadar Jan 24 '19 at 06:26
glad accept the answer if it was helpful. – JineshEP Jan 24 '19 at 07:00

How to pass a variable's value in where clause in java?

1 Answers1