#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    Strange java SQL statements being inserted into database


    I have a problem with my Java application. The application is based on users who have to enter their username (their email address) so as to fill in an input form. The functionality of the input form is that the user has to enter for book names in order of priority. In the application, suppose a user's email address is "david.ferno@gmail.com"....he will have to input only "david.ferno" in the JTextBox...and a SQL LIKE statement is used check the corresponding User ID from the "bookuser" table and store it into the "priority" table along with the book priority choices.

    I have used PreparedStatement for the SQL retrieval from database but when values are being inserted to the , instead of saving User ID like "user88", it is saving the SQL statement itself inside the database. What mean to say, in the "priority" table, instead of saving user88 (as an example)....it is saving "SELECT User_ID FROM user WHERE email_address"

    Please help me to find out what is wrong with my code.

    Here are my codes>>


    Code:
    import java.awt.*;
    import java.awt.event.*;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import javax.swing.*;
    import java.sql.*;
    import javax.*;
    
    public class addpriority extends JFrame{
    public JTextField txt_email, txt_book1, txt_book2,txt_book3, txt_book4;
    public JLabel label1;
    public JButton confirm;
    
    public addpriority(){
        super("Book Choice Input Menu");
        this.setSize(506,620);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setVisible(true);
    
        Toolkit toolkit =  Toolkit.getDefaultToolkit ();
        Dimension dim = toolkit.getScreenSize();
    
        setLocation((int) (dim.width*0.25), 50);
    
        JPanel label = new JPanel();
        JLabel welcome = new JLabel("Input Book Priorities");
    
        Font f = new Font("Verdana", Font.BOLD,16);
        welcome.setForeground(Color.BLACK);
        welcome.setFont(f);
        add(label);
        label.add(welcome);
    
        GridLayout g1 = new GridLayout(15, 2, 0, 10);
        setLayout(g1);
    
        JPanel textfield1 = new JPanel();
        JLabel userid = new JLabel("Please Enter your username:");
        txt_mail = new JTextField(20);
        JLabel lect2 = new JLabel("@gmail.com");
        add(textfield1);
        textfield1.add(userid);
        textfield1.add(txt_mail);
        textfield1.add(lect2);
    
    
        JPanel textfield2 = new JPanel();
        add(textfield2);
    
        JPanel textfield3 = new JPanel();
        JLabel lbl1 = new JLabel("Set Priority by entering Books' name:");
        add(textfield3);
        textfield3.add(lbl1);
    
    
        JPanel textfield4 = new JPanel();
        JLabel lbl2 = new JLabel("1st Priority:");
        txt_module1 = new JTextField(10);
        add(textfield4);
        textfield4.add(lbl2);
        textfield4.add(txt_module1);
    
        JPanel textfield5 = new JPanel();
        JLabel lbl3 = new JLabel("2nd Priority:");
        txt_module2 = new JTextField(10);
        add(textfield5);
        textfield5.add(lbl3);
        textfield5.add(txt_module2);
    
    
        JPanel textfield6 = new JPanel();
        JLabel lbl4 = new JLabel("3rd Priority:");
        txt_module3 = new JTextField(10);
        add(textfield6);
        textfield6.add(lbl4);
        textfield6.add(txt_module3);
    
        JPanel textfield7 = new JPanel();
        JLabel lbl5 = new JLabel("4th Priority:");
        txt_module4 = new JTextField(10);
        add(textfield7);
        textfield7.add(lbl5);
        textfield7.add(txt_module4);
    
        JPanel button = new JPanel();
        confirm = new JButton("Confirm");
        confirm.setMnemonic('C');
        confirm.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e)
            {
                confirm.setEnabled(true);
                }
        });
    
    
        Handleraddclassroom h = new Handleraddclassroom();
        confirm.addActionListener(h);
    
        button.add(confirm);
        add(button);
    
    
    }
    
    
    public class Handleraddclassroom implements ActionListener{
        public void actionPerformed(ActionEvent event){
    
            String name = ((JButton)event.getSource()).getText();
    
    
            if (name.equals("Confirm")){
    
                String filename = "C:/JAVA/book.accdb";
                String database = "jdbc:odbc:booking";
    
                if(txt_mail.getText().equals("")){
                    JOptionPane.showMessageDialog(addpriority.this,"Your Username Field cannot be blank");
    
    
                }
    
                else{
    
    
                try {
                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    
                    Connection con = DriverManager.getConnection(database ,"","");  //username &password not being used
    
                    Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); //The Result set can now scroll forward and back
    
    
    
    
                String squery = "SELECT User_ID FROM bookuser WHERE email_address LIKE ? ";
                PreparedStatement pstmt = con.prepareStatement(squery);
                pstmt.setString (1, txt_mail.getText() + '*');
                                pstmt.executeQuery();
    
                String sql = "INSERT INTO priority VALUES('" 
    
                    + squery + "','"
                    + txt_book1.getText() + "','"
                    + txt_book2.getText() + "','" 
                    + txt_book3.getText() + "','"
                    + txt_book4.getText()+ "' )";
    
                JOptionPane.showMessageDialog(addpriority.this,"Choice Records Saved.");
    
                dispose();
    
                Mainmenulecturer mml = new Mainmenulecturer();
    
                s.execute(sql);
    
                s.close();
                con.close();
    
                } catch (Exception e) {System.out.print("Error: " + e);}
                }
            }
        }   
    
    
    }
  2. #2
  3. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,020
    Rep Power
    1285
    Take a look at this.

    Code:
    String sql = "INSERT INTO priority VALUES('" 
    
                    + squery + "','"
                    + txt_book1.getText() + "','"
                    + txt_book2.getText() + "','" 
                    + txt_book3.getText() + "','"
                    + txt_book4.getText()+ "' )";
    Look at what you are adding.

IMN logo majestic logo threadwatch logo seochat tools logo