CODEWITHSUNDEEP
javamysqljdbcprepared-statement
JAN
JAN

Reputation: 21865

Trying to insert data into Mysql Table doesn't work (Java)

Given this code : 

package db;
import java.io.*;
import java.sql.*;


public class Connect 
{


    // creating a table for each type person in the bank 

    public void createTable(Statement state,String tableType) throws SQLException
    {
        state.executeUpdate (

                "CREATE TABLE IF NOT EXISTS "+ tableType +" ("
                + "FirstName CHAR(20), LastName CHAR(20),"
                + "Address CHAR(50), PhoneNumber CHAR(20),"
                + "UserName CHAR(20), Password CHAR(20))");
    }



    public void insertDataToTable(Statement statement , String table 
            ,String firstName,String lastName,String address, String phoneNumber , String userName, String password)
    {
        try
        {
            statement.executeUpdate("INSERT INTO table (`FirstName` ,`LastName` , `Address` , `PhoneNumber` , `UserName` , `Password`) " +
                    "values ( '"+firstName+"','"+lastName+ "','"+address+"','"+phoneNumber+"','"+userName+ "'," +password+")");



        }

        catch(Exception e)
        {
            System.out.println(e.toString());
        }

    }


    public void start()
    {

        System.out.println("Database creation example!");
        Connection con = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost","root","root");
            try
            {

                Statement st = con.createStatement();

                // create a database 

                st.executeUpdate("CREATE DATABASE IF NOT EXISTS Personnel"); 
                st.executeUpdate("USE Personnel");

                // create tables

                //Create a table for each user type!
                createTable(st, "ClientsTable");
                createTable(st, "ClerksTable");
                createTable(st, "ManagersTable");
                createTable(st, "AdminsTable");

                this.insertDataToTable(st, "ClientsTable", "my", "name", "is", "erl", "I", "think");

                ResultSet rs = st.executeQuery("SELECT `FirstName` FROM `ClientsTable`");
                while (rs.next() == true)
                { 
                    System.out.println(rs.getString("FirstName")); 
                }



            } // end try  

            catch (SQLException s)
            {
                System.out.println("SQL statement is not executed!");
            }


        } // end try 



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


    }  // end start



}

When I execute from my Main : 

package db;

public class Main {

    public static void main(String [ ] args)
    {

        Connect myConnection = new Connect();

        myConnection.start();
    }

}

And reach that line in start() method : 

this.insertDataToTable(st, "ClientsTable", "my", "name", "is", "erl", "I", "think");

I get the following output from the server (using the try/catch) : 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'table (`FirstName` ,`LastName` , `Address` , `PhoneNumber` , `UserName` , `Passw' at line 1

I checked the manual but I can't seem to find the source of the problem . Any idea what's wrong here ? thanks

Upvotes: 0

Views: 3850

Answers (4)

Durgadas Kamath
Durgadas Kamath

Reputation: 390

in your values , password is not enclosed within a quote. Password is a String but your code is treating it as a number.

Upvotes: 0

Kowser
Kowser

Reputation: 8261

Notice that your table name is missing in the statement.

statement.executeUpdate("INSERT INTO table (

Use following instead

statement.executeUpdate("INSERT INTO `" + table + "` (

Upvotes: 1

SJuan76
SJuan76

Reputation: 24885

You are using tilde "`" instead of quote "'" around your column names.

Since you don't have any separators in them, you can either remove all tilde / quotes from your column names or just replace all of them with quote "'"

Upvotes: 1

Shashank Kadne
Shashank Kadne

Reputation: 8101

Your table name is a variable I believe. However you have used the String "table" directly. But this isn't an issue.

Your password variable value requires a single quote.

"values ( '"+firstName+"','"+lastName+ "','"+address+"','"+phoneNumber+"','"+userName+ "','" +password+"')");

Upvotes: 1

Related Questions