Java SQLSyntaxError in connection

Question

I am trying to connect database with java to perform CRUD operation but every time I run its gives list of error which also include:
com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception

//Error Seems to be in my ArrayList Function
public ArrayList<BusSetGet> bussetgetList(){
ArrayList<BusSetGet> usersList=new ArrayList<>();
try{
Class.forName("com.mysql.jdbc.Driver");
String db_connURL= "jdbc:mysql://localhost:3306/buses";
Connection con=DriverManager.getConnection(db_connURL);
String query1="SELECT * FROM bus_crud";
PreparedStatement st=con.prepareStatement(query1);
ResultSet rs=st.executeQuery(query1);
BusSetGet users;
while(rs.next()){    
users=new BusSetGet(rs.getString("bus_type"),rs.getString("bus_ID"),rs.getInt("bus_no"));
usersList.add(users);
}
}catch (ClassNotFoundException | SQLException ex) {
        System.out.println("ERROR in ArrayList Method!!");
    }
return usersList;
}

  private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        // TODO add your handling code here:
        String query="insert into 
bus_crud(bus_type,bus_ID,bus_no)values('"+jComboBox1.getSelectedItem() +"','"+jTextField1.getText()+"','"+jTextField2.getText()+"')";
    sqlQuery(query,"Inserted");
}                                        

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    // TODO add your handling code here:
    String query="UPDATE `bus_crud` SET `bus_type`='"+jComboBox1.getSelectedItem() +"',`bus_ID`='"+jTextField1.getText()+"',`bus_no`='"+jTextField2.getText()+" WHERE `bus_ID = "+jTextField1.getText();
    sqlQuery(query,"Updated");       
}

//FOR SQL Query To be executed
public void sqlQuery(String query,String message)
{
Connection con=getConnection();
Statement st;
try{
st=con.createStatement();
 if(st.executeUpdate(query)==1)
 {
  //Refresh JTable
  DefaultTableModel model=(DefaultTableModel) jTable1.getModel();
  model.setRowCount(0);
  show_data_Jtable();
 JOptionPane.showMessageDialog(null,"Data"+message+"Successful");
}
else
{
 JOptionPane.showMessageDialog(null,"Data not"+message);
}
    }catch(SQLException ex) {
      Logger.getLogger(BusCRUD.class.getName()).log(Level.SEVERE, null,ex);
     }
  }

Here is the functions which possibly gives errors..

You need to do a better job of debugging your code and telling us where the problem is. I don't see anything wrong, but then again you never showed us the code for the `sqlQuery` method. — Tim Biegeleisen, Dec 30 '17 at 09:14
@TimBiegeleisen I just Edited code and posted sqlQuery Function also — John Snow, Dec 30 '17 at 09:35
@JohnSnow You cant' post stacktrace, then we can't able to tell you the exact issue. — Ravi, Dec 30 '17 at 09:42
@Ravi com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: Access Denied for user '@'localhost to databse buses — John Snow, Dec 30 '17 at 09:42
I don't understand, why are you sharing code and exception in bits and pieces ????????? are you working on any confidential project ????? If you are expecting any help, you are suppose to share all required information in your original post not in comment — Ravi, Dec 30 '17 at 09:44
Please do not concatenate values into a query string, it is unsafe as it can open you to SQL injection. Instead use prepared statements with parameters. — Mark Rotteveel, Dec 30 '17 at 10:03
@MarkRotteveel as prepareStatement is for dynamic data access as per in my knowledge, so how and where do I use prepare Statement in my code? — John Snow, Dec 30 '17 at 10:07
_"is for dynamic data access"_ what does that even mean? In any case, where you are currently doing things like `"UPDATE bus_crud SET bus_type='"+jComboBox1.getSelectedItem() +"' ..."`, you should be using `"UPDATE bus_crud SET bus_type= ? ..."` and then set the value on the prepared statement using `preparedStatement.setString(1, jComboBox1.getSelectedItem())` (and the same for the rest of the values). What your doing now is **unsafe**, you should unlearn this habit immediately. — Mark Rotteveel, Dec 30 '17 at 10:09
See also: https://stackoverflow.com/questions/9516625/prevent-sql-injection-attacks-in-a-java-program — Mark Rotteveel, Dec 30 '17 at 10:13
@MarkRotteveel thanks I just traced the error the error is in ArrayList and it catches SQLException — John Snow, Dec 30 '17 at 10:20
@Ravi I just edited the code and the problem is in my ArrayList function in while condition — John Snow, Dec 30 '17 at 10:34

score 0 · Accepted Answer · answered Dec 30 '17 at 09:50

As per you latest comment (please update your post with same), it seems like database authentication is failing. You need to understand, if you have set some username/password to access your database and you aren't providing it while creating a JDBC connection, then it will fail with

com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: Access Denied for user '@'localhost

So, you need to pass <db_user_name> and <db_user_password> to get the database connection instance.

Class.forName("com.mysql.jdbc.Driver");  
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/buses","<db_user_name>","<db_user_password>");

Please note, this is one of the issue, there are many other syntax issues in your code one of them pointed by @Anuruddha. You need to correct all SQLs. You should make practice of using PreparedStatement instead.

Thanks a lot but as prepareStatement is for dynamic data access as per in my knowledge, so how and where do I use prepare Statement in my code? — John Snow, Dec 30 '17 at 10:07
There are many tutorials available on internet use them to understand — Ravi, Dec 30 '17 at 10:12

Java SQLSyntaxError in connection

1 Answers1