14 Replies - 3016 Views - Last Post: 25 September 2011 - 06:35 PM Rate Topic: -----

#1 alexz003  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 40
  • Joined: 08-May 10

Java MySQL Database Question

Posted 24 September 2011 - 11:05 AM

I am getting an error when i run this bit of code
	public static void searchDB(String criteria) throws SQLException {
		
		Stmt = conn.createStatement();
		
		double isNumber = 0;
		try{
		isNumber = Double.parseDouble(criteria);
		Stmt.execute("SELECT * FROM Users WHERE Phone =  " + criteria);
		}catch(Exception e) {
			System.err.println("not a phone number");
			Stmt.execute("SELECT * FROM Users WHERE First_Name = " + criteria + " OR Last_Name = " + criteria);
		}
			
        RS = Stmt.getResultSet();
        if(RS == null)
        	System.out.println("Null");
        else
        	System.out.println("Not Null");
        System.out.println("User ID     Name         Phone Number ");
        	System.out.println("======================================");
        while (RS.next()) {
        	
            System.out.printf("%-10d%-16s%-10s",RS.getInt("UserID"),RS.getString("First_Name") + " " + RS.getString("Last_Name"),RS.getString("Phone"));
            System.out.println();
       }
		
	}



Error Code
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Alex' in 'where clause'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:824)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:667)
	at Connect.searchDB(Connect.java:42)
	at Connect.main(Connect.java:71)



The problem is, I don't see where I am trying to pass "Alex" as a column.

Can anyone help me please?

Is This A Good Question/Topic? 0
  • +

Replies To: Java MySQL Database Question

#2 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8346
  • View blog
  • Posts: 31,904
  • Joined: 06-March 08

Re: Java MySQL Database Question

Posted 24 September 2011 - 01:32 PM

Add a System.out.println(criteria); when you enter your method
it has to be in it.

Better to use PreparedStatements, then System.out.printl on them, you'll know exactly what you try to SQL
Was This Post Helpful? 1
  • +
  • -

#3 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10803
  • View blog
  • Posts: 40,257
  • Joined: 27-December 08

Re: Java MySQL Database Question

Posted 24 September 2011 - 01:40 PM

PreparedStatements not only guard against SQL Injection attacks, but make it easier to bind data to parameters. I agree with pbl here.
Was This Post Helpful? 0
  • +
  • -

#4 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1156
  • View blog
  • Posts: 2,538
  • Joined: 05-May 05

Re: Java MySQL Database Question

Posted 24 September 2011 - 02:11 PM

You should be using single quotes around values. E.g.

WHERE phone = '1223243'

Was This Post Helpful? 1
  • +
  • -

#5 alexz003  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 40
  • Joined: 08-May 10

Re: Java MySQL Database Question

Posted 24 September 2011 - 03:10 PM

Am I going about searching the wrong way? I don't know much about database programming in Java could anyone suggest a tutorial in this? Sorry I have been winging most of my program.
Was This Post Helpful? 0
  • +
  • -

#6 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10803
  • View blog
  • Posts: 40,257
  • Joined: 27-December 08

Re: Java MySQL Database Question

Posted 24 September 2011 - 03:16 PM

Using PreparedStatements. Check out Oracle's tutorial on this. :)
Was This Post Helpful? 1
  • +
  • -

#7 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8346
  • View blog
  • Posts: 31,904
  • Joined: 06-March 08

Re: Java MySQL Database Question

Posted 24 September 2011 - 03:55 PM

View Postalexz003, on 24 September 2011 - 06:10 PM, said:

Am I going about searching the wrong way? I don't know much about database programming in Java could anyone suggest a tutorial in this? Sorry I have been winging most of my program.

As nothing to do with Java. SQL is SQL and that's it.

You might found a chapter about SQL programming in some Java books for surely a not a book completly dedicated to it.

The chapter in the book will talk about Statement and PreparedStatement but surely not about the SQL syntax that varies a bit between a database and another
Was This Post Helpful? 1
  • +
  • -

#8 alexz003  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 40
  • Joined: 08-May 10

Re: Java MySQL Database Question

Posted 25 September 2011 - 02:35 PM

ok i went and tried to use a Prepared statement to fix my problem but reworked my code. The error is telling me the Syntax is wrong but i am using the same syntax as the script does. Can you all help me?

public static void AddDB() throws SQLException{
		
		System.out.println("Please Enter the first name of the user:");
		String firstName = in.next();
		System.out.println("Please Enter the last name of the user:");
		String lastName = in.next();
		System.out.println("Please Enter the phone number of the user(i.e. 1234567890)");
		String phoneNumber = in.next();
		PreparedStatement Stmt = null;
		String sqlQuery = "INSERT INTO `test`.`users` (`idUsers`, `First Name`, `Last Name`, `Phone_Number`)" +
				" VALUES (5, " + firstName + ", " + lastName + ", " + phoneNumber+ ");";
		Stmt = conn.prepareStatement(sqlQuery);
		System.out.println("Executing the Update:" + sqlQuery);
		Stmt.executeUpdate();
	}


This post has been edited by alexz003: 25 September 2011 - 02:36 PM

Was This Post Helpful? 0
  • +
  • -

#9 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10803
  • View blog
  • Posts: 40,257
  • Joined: 27-December 08

Re: Java MySQL Database Question

Posted 25 September 2011 - 02:42 PM

The way a PreparedStatement works is that you don't pass the values to the query directly. Instead, you bind parameters. In this way, the PreparedStatement separates the data and sanitizes to guard against SQL Injection. It also stays on the SQL Server for easier re-use by allowing you to change the parameters and re-execute the query.
String query = "INSERT INTO users(id_users, first_name, last_name, phone_number) VALUES(?,?,?,?)";
PreparedStatement statement = conn.prepareStatement(query);



Now use the statement setInt(), setString(), etc., methods to bind the parameters.

Also, is there a reason you are manually inserting the user_id? If this is just a meaningless number, an AUTO_INCREMENT constraint would make more sense on the table design section. What is test.users as well?
Was This Post Helpful? 3
  • +
  • -

#10 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

Re: Java MySQL Database Question

Posted 25 September 2011 - 02:48 PM

Its not right in that if you were to print out your String sqlQuery it would not show the " around the firstName or lastName value, whatever that ends up being. If you were to simply print that String to the console you would see that it actually is.

Quote

INSERT INTO `test`.`users` (`idUsers`, `First_Name`, `Last_Name`, `Phone_Number`)
VALUES (5, Dane, AU, 5551234);


When you actually want

Quote

INSERT INTO `test`.`users` (`idUsers`, `First_Name`, `Last_Name`, `Phone_Number`)
VALUES (5, "Dane", "AU", 5551234);


Because you have Strings in there, subequently you dbQuery String needs to ensure that the " " surround the Strings Last_Name and First_Name

"VALUES (5," +'"'+ First_Name +'"'+ Last_Name +'"'+","+ PhoneNumber + ");";



or similar i guess

This post has been edited by DaneAU: 25 September 2011 - 02:54 PM

Was This Post Helpful? 2
  • +
  • -

#11 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10803
  • View blog
  • Posts: 40,257
  • Joined: 27-December 08

Re: Java MySQL Database Question

Posted 25 September 2011 - 02:50 PM

For a non-PreparedStatement, I agree. For a PreparedStatement, it is better to bind parameters. :)
Was This Post Helpful? 1
  • +
  • -

#12 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

Re: Java MySQL Database Question

Posted 25 September 2011 - 02:53 PM

Sure is, i concur also :)
Was This Post Helpful? 1
  • +
  • -

#13 alexz003  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 40
  • Joined: 08-May 10

Re: Java MySQL Database Question

Posted 25 September 2011 - 05:48 PM

View Postmacosxnerd101, on 25 September 2011 - 02:42 PM, said:

The way a PreparedStatement works is that you don't pass the values to the query directly. Instead, you bind parameters. In this way, the PreparedStatement separates the data and sanitizes to guard against SQL Injection. It also stays on the SQL Server for easier re-use by allowing you to change the parameters and re-execute the query.
String query = "INSERT INTO users(id_users, first_name, last_name, phone_number) VALUES(?,?,?,?)";
PreparedStatement statement = conn.prepareStatement(query);



Now use the statement setInt(), setString(), etc., methods to bind the parameters.

Also, is there a reason you are manually inserting the user_id? If this is just a meaningless number, an AUTO_INCREMENT constraint would make more sense on the table design section. What is test.users as well?


I couldn't exactly figure out how to auto-increment, however i didn't look it up. Anyhow, When using a prepared statement and i have the values put in later using setInt and setString, i tried doing this and i got the following error:
PreparedStatement Stmt = null;
		String sqlQuery = "INSERT INTO `test`.`users` (`First Name`, `Last Name`, `Phone_Number`)" +
				" VALUES (?,?,?);";
		Stmt = conn.prepareStatement(sqlQuery);
		Stmt.setString(1, firstName);
		Stmt.setString(2, lastName);
		Stmt.setString(3, phoneNumber);
		System.out.println("Executing the Update:" + Stmt.toString());
		Stmt.execute();
		conn.commit();


error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
	at Connect.AddDB(Connect.java:62)
	at Connect.main(Connect.java:69)


test.users is the database test using the table users

This post has been edited by alexz003: 25 September 2011 - 06:15 PM

Was This Post Helpful? 0
  • +
  • -

#14 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

Re: Java MySQL Database Question

Posted 25 September 2011 - 06:29 PM

Yes that will occur probably because you already have an element in the table with the primary key value of 1.

If you have run this program more than once thats what will happen as it will try to insert a new user with the primary key with value 1 when there exists a user with that key.

This feeds back into what macosxnerd101 was saying about auto-increment, this will ensure that each time a new set of fields are to be inserted that an individual key is assigned for primary key. I would suggest seriously considering altering your table to include an auto-incrementing primary key to avoid this kind of errors.

You can then adjust your statement accordingly to excluse passing a value for id.

This post has been edited by DaneAU: 25 September 2011 - 06:30 PM

Was This Post Helpful? 2
  • +
  • -

#15 alexz003  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 40
  • Joined: 08-May 10

Re: Java MySQL Database Question

Posted 25 September 2011 - 06:35 PM

View PostDaneAU, on 25 September 2011 - 06:29 PM, said:

Yes that will occur probably because you already have an element in the table with the primary key value of 1.

If you have run this program more than once thats what will happen as it will try to insert a new user with the primary key with value 1 when there exists a user with that key.

This feeds back into what macosxnerd101 was saying about auto-increment, this will ensure that each time a new set of fields are to be inserted that an individual key is assigned for primary key. I would suggest seriously considering altering your table to include an auto-incrementing primary key to avoid this kind of errors.

You can then adjust your statement accordingly to excluse passing a value for id.

Thanks i figured it out!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1