how to insert into mysql database with java

Question

i want to insert inputs i take from user into mysql database the connection is right but the insertion gives me error

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''u_fname', 'u_lname', 'u_uname', 'u_pass', 'u_age', 'u_adderess') values('20','o' at line 1

My code is:

public void adduser(User s) {
   try {
        sql = "insert into users ('u_fname', 'u_lname', 'u_uname', 'u_pass', 'u_age', 'u_adderess')"
                + "values('" + s.getFirstname() + "','" + s.getLastname()
                + "','" + s.getUsername() + "','" + s.getPassword() + "','" + s.getAge() + "','" + s.getAdderss() + "')";
        stmt = conn.createStatement();
        int i = stmt.executeUpdate(sql);
        if (i > 0) {
            System.out.println("ROW INSERTED");
        } else {
            System.out.println("ROW NOT INSERTED");
        }
    } catch (Exception e) {
        System.out.println(e);
    }

}

You need to learn prepared statements. docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html. And SQL, too: column names must not be quoted. — JB Nizet
– JB Nizet, Commented Dec 2, 2019 at 23:10

Kum · Accepted Answer · 2019-12-03 10:20:58Z

6

To insert into mysql, follow these steps-

Create a Java Connection to our example MySQL database. I believe you already took care of it. It will be something like this-

  String myDriver = "org.gjt.mm.mysql.Driver";
  String myUrl = "jdbc:mysql://localhost/test";
  Class.forName(myDriver);
  Connection conn = DriverManager.getConnection(myUrl, "root", "");

Create a SQL INSERT statement, using the Java PreparedStatement syntax. Your PreparedStatement SQL statement will be as following this format-
```
  String sql = " insert into users (first_name, last_name, date_created, is_admin, num_points)"
    + " values (?, ?, ?, ?, ?)";
```

Set the fields on our Java PreparedStatement object. It will done as-

  PreparedStatement preparedStmt = conn.prepareStatement(sql);
  preparedStmt.setString (1, s.first_name);
  preparedStmt.setString (2, s.last_name);
  preparedStmt.setDate   (3, s.date_created);
  preparedStmt.setBoolean(4, s.is_admin);
  preparedStmt.setInt    (5, s.num_points);

Execute a Java PreparedStatement.
```
  preparedStmt.execute();
```
Close our Java MYSQL database connection.
```
  conn.close();
```

Catch any SQL exceptions that may come up during the process.

  catch (Exception e)
  {
  System.err.println("Got an exception!");
  // printStackTrace method 
  // prints line numbers + call stack
  e.printStackTrace();
  // Prints what exception has been thrown 
  System.out.println(e); 
  }

edited Dec 3, 2019 at 10:20

answered Dec 3, 2019 at 0:01

Kum

3157 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Omar Mohamed Over a year ago

i followed your steps but i got an exception "null" i cant figure out what is the problem in my insertion code exactly

The Impaler Over a year ago

Instead of printing the exception message, you better print the exception itself. Some exceptions don't have good messages. Anyway +1

Kum Over a year ago

@OmarMohamed....that is why I have written "any SQL exceptions that may come up". I am editing my answer.

Joop Eggen Over a year ago

executeUpdate() would be a tick better.

Collectives™ on Stack Overflow

how to insert into mysql database with java

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related