Why the prepared statement not working in java?

Question

I am trying to get the student information by providing the roll number and medium from db. The swing application executed without any error but when I enter the roll no and medium then its going to the "Student not Found" else loop.

I guess the problems with get string or the prepared statements.kindly help me to find out the issue.

import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;

public class Searchdb extends JFrame implements ActionListener {

//Initializing Components
    JLabel lb,lbd,lb1, lb2, lb3, lb5;
    JTextField tf1, tf2,tf3,tf5,tfd;
    JButton btn;

    //Creating Constructor for initializing JFrame components
    Searchdb() {
        //Providing Title
        super("Fetching Roll Information");
        lb5 = new JLabel("Roll Number:");
        lb5.setBounds(20, 20, 100, 20);
        tf5 = new JTextField(20);
        tf5.setBounds(130, 20, 200, 20);

        lbd = new JLabel("Date:");
        lbd.setBounds(20, 50, 100, 20);
        tfd = new JTextField(20);
        tfd.setBounds(130, 50, 200, 20);


        btn = new JButton("Submit");
        btn.setBounds(50, 50, 100, 20);
        btn.addActionListener(this);

        lb = new JLabel("Fetching Student Information From Database");
        lb.setBounds(30, 80, 450, 30);
        lb.setForeground(Color.black);
        lb.setFont(new Font("Serif", Font.PLAIN, 12));
        setVisible(true);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(500, 500);

        lb1 = new JLabel("Name:");
        lb1.setBounds(20, 120, 100, 20);
        tf1 = new JTextField(50);
        tf1.setBounds(130, 120, 200, 20);
        lb2 = new JLabel("Fathername:");
        lb2.setBounds(20, 150, 100, 20);
        tf2 = new JTextField(100);
        tf2.setBounds(130, 150, 200, 20);
        lb3 = new JLabel("State:");
        lb3.setBounds(20, 180, 100, 20);
        tf3 = new JTextField(50);
        tf3.setBounds(130, 180, 200, 20);

        setLayout(null);

        //Add components to the JFrame
        add(lb5);
        add(tf5);
        add(lbd);
        add(tfd);
        add(btn);

        add(lb);
        add(lb1);
        add(tf1);
        add(lb2);
        add(tf2);
        add(lb3);
        add(tf3);


        //Set TextField Editable False
        tf1.setEditable(false);
        tf2.setEditable(false);
        tf3.setEditable(false);

    }

    public void actionPerformed(ActionEvent e) {
        //Create DataBase Coonection and Fetching Records

        try {
            String str = tf5.getText();

            Datestri = tfd.getText();//Getting the unable to convert String to Date error

            System.out.println(str);
            System.out.println(stri);

            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@//host:port/servicename","username","password");
            PreparedStatement st = con.prepareStatement("select Name,Fathername,State from student_db where roll_number=? and medium=?");
            System.out.println(st);
            st.setString(1, str);
            st.setDate(2, stri);



            //Excuting Query
            ResultSet rs = st.executeQuery();
            System.out.println(rs);

            if (rs.next()) {
                String s = rs.getString(1);
                String s1 = rs.getString(2);
                String s2 = rs.getString(3);


                //Sets Records in TextFields.
                tf1.setText(s);
                tf2.setText(s1);
                tf3.setText(s2);

            } else {
                JOptionPane.showMessageDialog(null, "Student not Found");
            }

            //Create Exception Handler
        } catch (Exception ex) {

            System.out.println(ex);
        }
    }
//Running Constructor

    public static void main(String args[]) {
        new Searchdb();
    }
}

sql query:

select Name,Fathername,State from student_db where roll_number='1441' and medium='2016-12-18';

Results:

Name Fathername State 
SA     TH        YA

Suppose if i am not passing the "Stri" variable in the query i am getting the results.

Have you tried executing the SQL statement manually using Oracle SQLDeveloper or SQL*Plus with values you know are correct? — Powerlord, Dec 28 '16 at 12:25
Are you sure you are getting the inputs from the correct text fields in the UI? `stri` is populated from a field called `tfd`, which appears to be a date field of some sort and not a field for "medium" which you're using in the query. Remember to always give your variables descriptive names. — Mick Mnemonic, Dec 28 '16 at 12:52
suppose if i use str only then i am getting the result , if i use both not working @MickMnemonic — sathya, Dec 28 '16 at 12:56
Please [edit] your question and include the SQL statement you ran on your database + result(s). Then provide a screen shot with the parameters you used in your Java program. Without that sort of detail we'll only be guessing as to the cause of your problem. — TT., Dec 28 '16 at 13:22
@TT. due to the security purpose i cant include the exact parameters so i included that in my query replacing the names. — sathya, Dec 28 '16 at 13:28
So the `medium` column consists of dates or date strings in the DB? — Mick Mnemonic, Dec 28 '16 at 13:41
`st.setString(2, stri);` >> If your second parameter is a date, please use `st.setDate(2, dt);` with dt being of type [`java.sql.Date`](https://docs.oracle.com/javase/8/docs/api/java/sql/Date.html). — TT., Dec 28 '16 at 14:03
1) Java GUIs have to work on different OS', screen size, screen resolution etc. using different PLAFs in different locales. As such, they are not conducive to pixel perfect layout. Instead use layout managers, or [combinations of them](http://stackoverflow.com/a/5630271/418556) along with layout padding and borders for [white space](http://stackoverflow.com/a/17874718/418556). 2) But this problem obviously has nothing to do with Swing. Factor the GUI out and solve it in a command line app. first. — Andrew Thompson, Dec 28 '16 at 14:40
I will share the screen resolution and other details later our of office now @Andrew Thompson — sathya, Dec 28 '16 at 15:18
Layout managers allow Java applications to be able to deal with different screen resolutions and application sizes without specifying explicit sizes of, or positions for, components. But like I said, factor out the GUI and sort out the problems with DB access in a command line app. first. — Andrew Thompson, Dec 28 '16 at 16:06
Updated as st.setDate(2, stri); but now i am getting the error in Date stri = tfd.getText(); unable to convert String to Date. — sathya, Dec 29 '16 at 04:08
@vidya You should be able to find a solution to that yourself, but anyway. Look up the [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class and how to use it. That should allow you to convert a string with a date in a certain format to a `java.util.Date` instance. From there you just need to convert to `java.sql.Date` by `java.util.Date ud = ...; java.sql.Date sd = new java.sql.Date( ud.getTime( ) );`. — TT., Dec 29 '16 at 07:47

score 0 · Accepted Answer · answered Dec 29 '16 at 13:25

I have updated the code below and its working fine, when i checked the database type for the medium column name it is varchar2(40), so i decided to use the getString only.

Getting proper response with this code,

import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;

public class Searchdb extends JFrame implements ActionListener {

//Initializing Components
    JLabel lb,lbd,lb1, lb2, lb3, lb5;
    JTextField tf1, tf2,tf3,tf5,tfd;
    JButton btn;

    //Creating Constructor for initializing JFrame components
    Searchdb() {
        //Providing Title
        super("Fetching Roll Information");
        lb5 = new JLabel("Roll Number:");
        lb5.setBounds(20, 20, 100, 20);
        tf5 = new JTextField(20);
        tf5.setBounds(130, 20, 200, 20);

        lbd = new JLabel("Date:");
        lbd.setBounds(20, 50, 100, 20);
        tfd = new JTextField(20);
        tfd.setBounds(130, 50, 200, 20);


        btn = new JButton("Submit");
        btn.setBounds(50, 50, 100, 20);
        btn.addActionListener(this);

        lb = new JLabel("Fetching Student Information From Database");
        lb.setBounds(30, 80, 450, 30);
        lb.setForeground(Color.black);
        lb.setFont(new Font("Serif", Font.PLAIN, 12));
        setVisible(true);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(500, 500);

        lb1 = new JLabel("Name:");
        lb1.setBounds(20, 120, 100, 20);
        tf1 = new JTextField(50);
        tf1.setBounds(130, 120, 200, 20);
        lb2 = new JLabel("Fathername:");
        lb2.setBounds(20, 150, 100, 20);
        tf2 = new JTextField(100);
        tf2.setBounds(130, 150, 200, 20);
        lb3 = new JLabel("State:");
        lb3.setBounds(20, 180, 100, 20);
        tf3 = new JTextField(50);
        tf3.setBounds(130, 180, 200, 20);

        setLayout(null);

        //Add components to the JFrame
        add(lb5);
        add(tf5);
        add(lbd);
        add(tfd);
        add(btn);

        add(lb);
        add(lb1);
        add(tf1);
        add(lb2);
        add(tf2);
        add(lb3);
        add(tf3);


        //Set TextField Editable False
        tf1.setEditable(false);
        tf2.setEditable(false);
        tf3.setEditable(false);

    }

    public void actionPerformed(ActionEvent e) {
        //Create DataBase Coonection and Fetching Records

        try {
            String str = tf5.getText();

            String stri = tfd.getText();

            System.out.println(str);
            System.out.println(stri);

            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@//host:port/servicename","username","password");

            String str1 ="select Name,Fathername,State from student_db where roll_number='"+str+"' and medium='"+stri+"'";

            PreparedStatement st = con.prepareStatement(str1);
            System.out.println(st);
            st.setString(1, str);
            st.setString(2, stri);



            //Excuting Query
            ResultSet rs = st.executeQuery();
            System.out.println(rs);

            if (rs.next()) {
                String s = rs.getString(1);
                String s1 = rs.getString(2);
                String s2 = rs.getString(3);


                //Sets Records in TextFields.
                tf1.setText(s);
                tf2.setText(s1);
                tf3.setText(s2);

            } else {
                JOptionPane.showMessageDialog(null, "Student not Found");
            }

            //Create Exception Handler
        } catch (Exception ex) {

            System.out.println(ex);
        }
    }
//Running Constructor

    public static void main(String args[]) {
        new Searchdb();
    }
}

The least you can do, if you're not using parameters in a prepared statement, is sanitize the input. That would mean, replacing a single quote with two single quotes. Like `str.replace("'","''")` before you insert it into a string. It's a guard against SQL Injection like that. Otherwise you'll get [bobby tabled](https://xkcd.com/327/) real soon. — TT., Dec 29 '16 at 13:55

Why the prepared statement not working in java?

1 Answers1