I am making a registration form in Java Netbeans. I'm using the CRUD jframe and jtable functions. I also need to implement MySQL query to save the data in the MySQL Workbench. But whenever I run the project, the same error occurs that the program says the MySQL syntax is wrong.
private void JB2ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add code here:
try
{
Class.forName("com.mysql.cj.jdbc.Driver");
sqlConn = DriverManager.getConnection(data,user,password);
pst = sqlConn.prepareStatement("insert * into tracking_register_db(Name,Surname,Phone,Age,Adress,Email,"
"Temp,Symptoms,Exsposed) value(?,?,?,?,?,?,?,?,?,)");
pst.setString(1, JT1.getText());
pst.setString(2, JT2.getText());
pst.setString(3, JT3.getText());
pst.setString(4, JT4.getText());
pst.setString(5, JT5.getText());
pst.setString(6, JT6.getText());
pst.setString(7, JT7.getText());
pst.setString(8, JT8.getText());
pst.setString(9, JT9.getText());
pst.executeUpdate();
JOptionPane.showMessageDialog(this,"Record added");
upDateDB();
} catch (ClassNotFoundException ex) {
java.util.logging.Logger.getLogger(Registration.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
java.util.logging.Logger.getLogger(Registration.class.getName()).log(Level.SEVERE, null, ex);
}
This is the error I get:
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* into tracking_register_db(Name,Surname,Phone,Age,Adress,Email,Temp,Symptoms,Ex' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1098)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1046)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1371)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1031)
at Registration.JB2ActionPerformed(Registration.java:462)
at Registration.access$1000(Registration.java:28)
at Registration$11.actionPerformed(Registration.java:302)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6539)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
at java.awt.Component.processEvent(Component.java:6304)
at java.awt.Container.processEvent(Container.java:2239)
at java.awt.Component.dispatchEventImpl(Component.java:4889)
at java.awt.Container.dispatchEventImpl(Container.java:2297)
at java.awt.Component.dispatchEvent(Component.java:4711)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4904)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4535)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4476)
at java.awt.Container.dispatchEventImpl(Container.java:2283)
at java.awt.Window.dispatchEventImpl(Window.java:2746)
at java.awt.Component.dispatchEvent(Component.java:4711)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:760)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:84)
at java.awt.EventQueue$4.run(EventQueue.java:733)
at java.awt.EventQueue$4.run(EventQueue.java:731)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:730)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
CodePudding user response：
Please update MySQL insert query by removing * after insert and comma at the end. The right portion will be-
"insert into tracking_register_db(Name,Surname,Phone,Age,Adress,Email,"
"Temp,Symptoms,Exsposed) value(?,?,?,?,?,?,?,?,?)"
CodePudding user response：
and welcome
First, I dont recommend you to split your SQL into many strings, as it makes them difficult to read.
Regarding your question I see at least three errors.
- The
INSERT INTOstatement does not use * - The
INSERT INTOin MySQL uses the wordVALUES, notvalue - There is a trailing comma
","
I recommend you to first write your SQL in a place where you can excecute it (like workbench or PHPMyAdmin) and then use it in your code after you have confirm it works, because those tools will give you better insight into the errors.
For more information about the INSERT statement see https://www.w3schools.com/sql/sql_insert.asp
The fixed SQL would look something like this:
pst = sqlConn.prepareStatement("INSERT INTO tracking_register_db(Name,Surname,Phone,Age,Adress,Email,"
"Temp,Symptoms,Exsposed) VALUES (?,?,?,?,?,?,?,?,?)");
CodePudding user response：
Sql insert satement doesn't contain a star:
pst = sqlConn.prepareStatement("insert into tracking_register_db(Name,Surname,Phone,Age,Adress,Email,"
"Temp,Symptoms,Exsposed) values (?,?,?,?,?,?,?,?,?)");
CodePudding user response：
Especially the prepared statement should not have been declared as field.
Use try-with-resources which automatically close connection, statement and result set (if there were any). Even in case of exception or return.
String sql = "insert into tracking_register_db(Name,Surname,Phone,Age,Adress,Email,"
"Temp,Symptoms,Exsposed) values(?,?,?,?,?,?,?,?,?)"
try {
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection sqlConn = DriverManager.getConnection(data, user, password);
PreparedStatement pst = sqlConn.prepareStatement(sql)) {
pst.setString(1, JT1.getText());
pst.setString(2, JT2.getText());
pst.setString(3, JT3.getText());
pst.setString(4, JT4.getText());
pst.setString(5, JT5.getText());
pst.setString(6, JT6.getText());
pst.setString(7, JT7.getText());
pst.setString(8, JT8.getText());
pst.setString(9, JT9.getText());
pst.executeUpdate();
JOptionPane.showMessageDialog(this, "Record added",
JOptionPane.INFORMATION_MESSAGE);
upDateDB();
} // Closes sqlConn and pst
} catch (ClassNotFoundException | SQLException ex) {
Logger.getLogger(Registration.class.getName()).log(Level.SEVERE, "No record added", ex);
JOptionPane.showMessageDialog(this, "No record added: " ex.getMessage(),
JOptionPane.ERROR_MESSAGE);
}
Typo in Exsposed?