2 Replies - 244 Views - Last Post: 07 November 2012 - 12:54 PM Rate Topic: -----

#1 golominator  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 1
  • Joined: 07-November 12

Java SQL statements are being stored into database instead of values

Posted 07 November 2012 - 06:50 AM

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 I mean to say is, 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 is the "user" table structure >>

COLUMNNAME TYPE
-------------------
User_ID Text
full_name Text
last_name Text
email_address Text
phone Text
User_ID has been set as primary key

Here is the "priority" table structure >>

COLUMNNAME TYPE
----------------
User_ID Text
Book1 Text
Book2 Text
Book3 Text
Book4 Text
User_ID has been set as primary key

Here are my codes
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 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");

                       //username &password not being used
                       Connection con = DriverManager.getConnection(database ,"",""); 
                       Statement s = con.createStatement(
                                          ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                          ResultSet.CONCUR_READ_ONLY);

                       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);
                   }
              }
          }
      }   

}




Is This A Good Question/Topic? 1
  • +

Replies To: Java SQL statements are being stored into database instead of values

#2 g00se  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2735
  • View blog
  • Posts: 11,521
  • Joined: 20-September 08

Re: Java SQL statements are being stored into database instead of values

Posted 07 November 2012 - 10:24 AM

Unfortunately you're using a PreparedStatement where you can't use one (you can't parameterise a LIKE statement) and failing to use one where you can (the INSERT)

You need to use a normal Statement with String.format for the LIKE statement and you need to parameterise the INSERT
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5848
  • View blog
  • Posts: 12,707
  • Joined: 16-October 07

Re: Java SQL statements are being stored into database instead of values

Posted 07 November 2012 - 12:54 PM

If you have an email address as your key to get a user id, then that email should be unique. Why would you use a like?!?

You have filename defined, but never use it, so your connection is probably wrong.

I'd have a separate class for database access. It will keep you from stepping on yourself.

e.g.
class Handleraddclassroom implements ActionListener {
	private class Db {
		private final String database = "jdbc:odbc:booking";
		public Db() throws ClassNotFoundException {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		}
		
		private Connection getConn() throws SQLException { 
			return DriverManager.getConnection(database, "", "");
		}
		
		public void addPrority(String userId, String book1, String book2, String book3, String book4 ) throws SQLException { 
			// your code here
		}
		
		public String getUserIdForEmail(String email) throws SQLException { 
			// your code here
		}
	}
	
	private final Db db;
	public Handleraddclassroom() throws ClassNotFoundException {
		this.db = new Db();
	}
		

	public void actionPerformed(ActionEvent event) {
		String name = ((JButton) event.getSource()).getText();
		if (name.equals("Confirm")) {
			try { 
				addPriority();
			} catch (SQLException ex) { 
				ex.printStackTrace();
			}
		}
	}

	private void addPriority() throws SQLException { 
		String email = txt_mail.getText();
		if (email.equals("")) {
			JOptionPane.showMessageDialog( this, "Your Username Field cannot be blank");
		} else {
			String userId = db.getUserIdForEmail(email);
			if (userId==null) {
				// do something, bad email
			} else {
				addPriority(userId);
			}
		}
	}
	
	private void addPriority(String userId) throws SQLException { 
		db.addPrority(userId, 
			txt_book1.getText(), txt_book2.getText(), 
			txt_book3.getText(),txt_book4.getText()
		);
		JOptionPane.showMessageDialog(this, "Choice Records Saved.");
	}
}


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1