CODEWITHSUNDEEP
javamysqlswing
user1907971
user1907971

Reputation: 51

How to insert value into MySql table from user input JTextField

I've tried to insert value into a table from user input in JTextField. The code runs with an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

Can anyone help me solve this problem? Thanks!

Here is my code.

import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.BorderLayout;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTextField;

class InputRoute
{

JTextField text1;
JTextField text2;
JTextField text3;
String c;
Float d;

public void inputRoute() 
{

    Connection conn = null;
    String url = "jdbc:mysql://localhost:3306/";
    String dbName = "YarraTram";
    String driver = "com.mysql.jdbc.Driver";
    String userName = "root";
    String password = "abc123";

    try 
    {
        Class.forName(driver).newInstance();
        conn = DriverManager.getConnection(url + dbName, userName, password);

        PreparedStatement statement = conn.prepareStatement("INSERT INTO  ('route', 'price') VALUES ('"+c+"', '"+d+"')");
        statement.executeQuery();

    } 
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

 public void createAndShowGUI() 
{

    final JFrame frame = new JFrame("Yarra Tram Route Finder(New Route)");

    frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    frame.setLayout(new FlowLayout());

    JLabel label1 = new JLabel("From: ");
    JLabel label2 = new JLabel("To: ");
    JLabel label3 = new JLabel("Price: ");

    text1 = new JTextField(20);
    text2 = new JTextField(20);
    text3 = new JTextField(20);

    JButton button1 = new JButton("Add");
    JButton button2 = new JButton("Close");

    frame.add(label1, BorderLayout.WEST);
    frame.add(text1, BorderLayout.EAST);
    frame.add(label2, BorderLayout.WEST);
    frame.add(text2, BorderLayout.EAST);
    frame.add(label3, BorderLayout.WEST);
    frame.add(text3, BorderLayout.EAST);
    frame.add(button1);
    frame.add(button2);

    button2.addActionListener(new ActionListener() 
    {
        @Override
        public void actionPerformed(ActionEvent e) 
        {
            String a = text1.getText();
            String b = text2.getText();
            d = Float.parseFloat(text3.getText());

            c = a + " - " + b;

            inputRoute();
        }
    });
    button2.addActionListener(new ActionListener() 
    {
        @Override
        public void actionPerformed(ActionEvent e) 
        {
            frame.dispose();
        }
    });

    frame.setSize( 500,120 );
    frame.setLocationRelativeTo( null );
    frame.pack();
    frame.setVisible(true);
} 
}

Here is my MySQL table

 CREATE TABLE `route` (
 `rid` int(11) NOT NULL AUTO_INCREMENT,
 `route` varchar(100) ,
 `price` decimal(5,2) ,
 PRIMARY KEY (`rid`)
 )

Upvotes: 0

Views: 13428

Answers (4)

Fahim Parkar
Fahim Parkar

Reputation: 31637

Point 1

You are missing table name

PreparedStatement statement = conn.prepareStatement("INSERT INTO tableName ('route', 'price') VALUES ('"+c+"', '"+d+"')");
                                                                 ^^^^^^^^^

Point 2

The way you are dealing with prepared statement is not right way. Always have like below.

PreparedStatement statement = conn.prepareStatement("INSERT INTO tableName (route, price) VALUES (?, ?)");
statement.setString(1, c);
statement.setFloat(2, d);

Point 3

Also I think 'route', 'price' will not work. I feel you wanted to use ` (backtick) instead of single quote '


So, your final statement should be

PreparedStatement statement = conn.prepareStatement("INSERT INTO tableName
(route, price) VALUES (?, ?)");
statement.setString(1, c);
statement.setFloat(2, d);

Upvotes: 0

Vallabh Patade
Vallabh Patade

Reputation: 5110

You are missing the table name in your SQL query. You need not to put the column names in single quotes. Only non-numeric values need to be place in single quotes.

As you are going with prepared statement then why your not setting the parameters by using PreparedStatement#setParamater(). By this current code I don't think if you taking PreparedStatement's full advantage. Prepared statements have their own set of advantages. First of all it help to avoid SQL injection and then improves Query performance. You can google the further details.

String c = <your_route>;
float d = <your_price>;      
PreparedStatement statement = conn.prepareStatement("INSERT INTO TABLE_NAME('route', 'price') VALUES (?, ?)");
statement.setString(1,c);
statement.setFloat(2,d);
statement.executeQuery();

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

First, you are missing the table name in:

... ("INSERT INTO  ('route', 'price') VALUES ...
                 /\
                  here

second, you shouldn't use the colons ' with columns' names. Use the backtick instead like so:

... ("INSERT INTO  `route` (`route`, `price`) VALUES ...

The colons are used to pass literal values.

Upvotes: 1

user527
user527

Reputation: 205

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

Upvotes: 1

Related Questions