Error with inserting data into SQL database in Java

Question

I'm creating a sign up page for a school program using Java. I have created a table with 3 users(Admin, Student Leader & Student) into the database by default. Now any students are able to sign up with their own username & Password. I tried to insert the text input by the users into the database table but the error is "org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such column: username)", i don't really get why is there missing database. This is my code:

        JButton btnSubmit = new JButton("Submit");
    btnSubmit.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
            //declare variables
            String username = "";
            String password = "";
            String name = "";
            String AdminNo = "";

            //get values using getText() method
            username = txtUsername.getText().trim();
            password = txtPassword.getText().trim();
            name = txtName.getText().trim();
            AdminNo = txtAdminNo.getText().trim();

            if(username.equals("") || password.equals("")){
                JOptionPane.showMessageDialog(null, "Name/Password is wrong");
            }
            else //Insert whether query is running properly
            {
                Connection conn = sqliteConnection.dbConnector();

                String IQuery = "INSERT INTO Login(Username,Password,Name,AdminNo)" + "VALUES(username,password,name,AdminNo)";
                System.out.println(IQuery); //Print to console
                System.out.println("Connecting to a selected database");

                try {
                    ((Connection) conn).createStatement().execute(IQuery);
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                } // select the rows



            }

        }
    });

And this is my Database Table: Login Table

I guess you want: "INSERT INTO Login(Username,Password,Name,AdminNo)" + "VALUES('" + username + "', '" + password + "', '" + name + "', '" + AdminNo + "')";. But I suggest to read up on SQL-Injection. — D. Mika
– D. Mika, Commented Jul 22, 2017 at 16:06

janos · Accepted Answer · 2017-07-22 16:12:00Z

This is the query that you are trying to execute:

INSERT INTO Login(Username,Password,Name,AdminNo) 
VALUES(username,password,name,AdminNo)

This is invalid. The username, password, name, AdminNo variables in your Java code don't get magically inserted into the query string. You have to insert them yourself.

To do it right, you should use prepared statements, like this:

PreparedStatement statement = null;

String sql = "INSERT INTO Login (Username, Password, Name, AdminNo) VALUES (?, ?, ?, ?)";

try {
  statement = conn.prepareStatement(sql);
  statement.setString(1, username);
  statement.setString(2, password);
  statement.setString(3, name);
  statement.setString(4, adminNo);
  statement.executeUpdate();
} catch (SQLException e ) {
  e.printStackTrace();
} finally {
  if (statement != null) {
    try {
      statement.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

Small note: in the above code I renamed your AdminNo variable to adminNo, because this is the recommended naming style in Java for variable names (known as "camelCase").

Collectives™ on Stack Overflow

Error with inserting data into SQL database in Java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related