can't insert data to mysql

Question

I can connect fine in Netbeans using this:

 Connection conn = null;
 try{
     conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/librarymangementsystem","root","root");
     if(conn!= null)
     {
     System.out.println("connected");
     }
 }catch(Exception e)
             {
             System.out.println("not connected");
             }
}

But when it comes to adding data to the columns, i just cant.

try{
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/librarymangementsystem","root","root");
        Statement stmt=conn.createStatement();
        String Query = "INSERT into librarian_details(username, name, email, password) VALUES("+ uname +", "+ fname +", " + emails + ",  " + psword +")";
        stmt.executeUpdate(Query);
        JOptionPane.showMessageDialog(null, "Success!");
     }

Anyone knows the problem? Updated Problem:

String Query = "insert into book_details(Book_Name, ISBN, Author, Category, >Quantity, BookShelfNo,Row,Column) VALUES('" +bookname + "','" + ISBN + "','" + >AuthorName + "','" + Category + "','" + Quantity + "','" + ?BookShelfNo +"', '" >+ Row + "', '" + Column + "')";

I cant seem to insert any data to Row and Column using this:

String Row = jTextField9.getText(); String Column = jTextField10.getText(); Row and Column Datatype is int.

Answer 1

I guess the username, name, email and password fields are of String type and Data type used while creating the columns username, name, email and password of librarian_details is Varchar .

If so, then you need to update your query string to the below code:

String Query = "INSERT into librarian_details(username, name, email, password) 
VALUES('"+ uname +"','"+ fname +"','" + emails + "','" + psword +"')";

If your input String has an apostrophe (') character then you need to add an extra apostrophe (') character as an escape sequence.

For Example:your password is abc'aa

        String uname = "abc";
        String fname = "xyz";
        String emails = "abc@xyz.com";
        String psword = "abc''aa";//extra apostrophe (') character added
        String Query = "INSERT into librarian_details(username, name, email, password) VALUES('"+ uname+ "','"+ fname+ "','"+ emails+ "','"+ psword+ "')";

Note: Adding extra apostrophe (') to the existing (') character is different than a double quote.

Below code is for your updated query

        String bookname ="abc";
        String ISBN="qwerty123";
        String AuthorName="user3213";
        String Category="New";
        String Quantity="1";
        String BookShelfNo="5";
        int Row=1;
        int Column=5;

        String Query = "insert into book_details(Book_Name, ISBN, Author, Category, Quantity, BookShelfNo,`Row`,`Column`) VALUES('" +bookname + "','" + ISBN + "','" + AuthorName + "','" + Category + "','" + Quantity + "','" + BookShelfNo +"', " + Row + ", " + Column + ")";
        stmt.execute(Query);

Note: you are using reserved keywords of sql like Row and Column. I guess your column names in DB are Book_Name, ISBN, Author, Category, Quantity, BookShelfNo, Row and Column.

Suggestion:

• Using PreparedStatement will save your time for writing a query (no need to remember datatype of variable and columns.)
• PreparedStatement uses query caching functionality.
• Hence, execution is faster than simple statement.

Below code depicts the usage of prepared statement for your query.

        //query parameters will be dynamically set 
        String Query = "INSERT INTO book_details VALUES (?,?,?,?,?,?,?,?)";

        //create a Prepared statement using connection object.
        PreparedStatement pstmt = con.prepareStatement(Query);

        //assign the query parameter values
        pstmt.setString(1, bookname);
        pstmt.setString(2, ISBN);
        pstmt.setString(3, AuthorName);
        pstmt.setString(4, Category);
        pstmt.setString(5, Quantity);
        pstmt.setString(6, BookShelfNo);
        pstmt.setInt(7, Row);
        pstmt.setInt(8, Column);
        //display query string generated by PreparedStatement.
        System.out.println("Query: "+pstmt.toString());
        //Display result; result=1 means success.
        int result = pstmt.executeUpdate();
        System.out.println("result: "+result);

Answer 2

Concatenating values into a query is unsafe, it opens you up to SQL injection. You need to use a prepared statement instead:

try (PreparedStatement pstmt = connection.prepareStatement(
        "INSERT into librarian_details(username, name, email, password) " 
        + "VALUES(?, ?, ?, ?)")) {
    pstmt.setString(1, uname);
    pstmt.setString(2, fname);
    pstmt.setString(3, emails);
    pstmt.setString(4, psword);

    pstmt.executeUpdate();
}

Answer 3

Escape the text values or even better - use prepared statements.

String Query = "INSERT into librarian_details(username, name, email, password) 
VALUES('"+ uname +"', '"+ fname +"', '" + emails + "', '" + psword +"')";

Prepared statements:

String Query = "INSERT into librarian_details(username, name, email, password) 
VALUES(?, ?, ?, ?)";