understanding Java JDBC error

Question

I was hoping to have some assistance in understanding this error. I am making a simple connection to a database, and for some reason it doesn't like my input, but I am failing to understand where the error is actually occurring. Any assistance would be appreciated- thanks.

My code:

  package db;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;


public class BankAccount {

private static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static String DB_URL = "jdbc:mysql://localhost:3306/cs565";
private static String DB_USERNAME = "cs";
private static String DB_PASSWORD = "java";
//  public String name;
//  public String action;
//  public double amount;

// create the table
public static void createTable(){

    try
    {
        Class.forName(JDBC_DRIVER);
        Connection conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        Statement stmt = conn.createStatement();

        String sqlDropStatement = "DROP TABLE MYTABLE_TRANSACTIONS";
        try {
            stmt.executeUpdate(sqlDropStatement);
        }
        catch (Exception e)
        {
            System.out.println("No old data found");
        }
        String sqlCreateStatement = "CREATE TABLE MYTABLE_TRANSACTIONS" +
                "(TRANSACTION_ID integer auto_increment primary key," +
                "ACCOUNT_ID varchar(32)," +
                "TRANSACTION_TYPE varchar(32)," + "AMOUNT double)";

        stmt.executeUpdate(sqlCreateStatement);

        String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values ('John Adams', 'initial', 100.00)";
        stmt.executeUpdate(sql);
        sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values ('Benjamin Franklin', 'initial', 200.00)";
        stmt.executeUpdate(sql);
        sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values ('George Washington', 'initial', 300.00)";
        stmt.executeUpdate(sql);
        sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values ('Thomas Jefferson', 'initial', 400.00)";
        stmt.executeUpdate(sql);

        sql = "Select * from MYTABLE_TRANSACTIONS";
        ResultSet rs = stmt.executeQuery(sql);

        //iterate through rows & print
        while (rs.next())
        {
            System.out.printf("TRANSACTION_ID=%2d, ACCOUNT_ID=%8s, TRANSACTION_TYPE=%8s, AMOUNT=%8s\n",
                    rs.getInt("TRANSACTION_ID"),
                    rs.getString("ACCOUNT_ID"),
                    rs.getString("TRANSACTION_TYPE"),
                    rs.getDouble("AMOUNT"));
        }
        //close the connection here 
        stmt.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}// end of create table method




// the deposit method
public static void makeDeposit(String name, String action, double amount){

    try
    {
        Class.forName(JDBC_DRIVER);
        Connection conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        Statement stmt = conn.createStatement();

        String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values (name, action, amount)";
        stmt.executeUpdate(sql);

        sql = "Select * from MYTABLE_TRANSACTIONS";
        ResultSet rs = stmt.executeQuery(sql);

        //iterate through rows & update
        while (rs.next())
        {
            System.out.printf("TRANSACTION_ID=%2d, ACCOUNT_ID=%8s, TRANSACTION_TYPE=%8s, AMOUNT=%8s\n",
                    rs.getInt("TRANSACTION_ID"),
                    rs.getString("ACCOUNT_ID"),
                    rs.getString("TRANSACTION_TYPE"),
                    rs.getDouble("AMOUNT"));
        }
        //close the connection here 
        stmt.close();

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

}//********end of deposit method

// the withdrawal method
public static void makeWithdrawal(String name, String action, double amount){
    try
    {
        Class.forName(JDBC_DRIVER);
        Connection conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        Statement stmt = conn.createStatement();

        String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values (name, action, amount)";
        stmt.executeUpdate(sql);

        sql = "Select * from MYTABLE_TRANSACTIONS";
        ResultSet rs = stmt.executeQuery(sql);

        //iterate through rows & update
        while (rs.next())
        {
            System.out.printf("TRANSACTION_ID=%2d, ACCOUNT_ID=%8s, TRANSACTION_TYPE=%8s, AMOUNT=%8s\n",
                    rs.getInt("TRANSACTION_ID"),
                    rs.getString("ACCOUNT_ID"),
                    rs.getString("TRANSACTION_TYPE"),
                    rs.getDouble("AMOUNT"));
        }
        //close the connection here 
        stmt.close();

    } catch (Exception e) {
        e.printStackTrace();
    }//********end of withdrawal method         

}

public static void main(String[] args) {
    //Prompt the user- what would you like to do?
    // withdrawal / deposit / view balance

    String name;
    String action;
    double amount;

    createTable();
    System.out.println("Please enter the name of your account: ");

    Scanner input = new Scanner( System.in );

    name = input.nextLine();

    System.out.println("Welcome " + name);

    System.out.println("What would you like to do? ");

    //Deposit, Withdrawal, check balance
    System.out.println("D= Deposit, W = Withdrawal, B = Show balance ");
    action = input.nextLine();
    //NOTE: fix this to where the letter inserts the entire word into the table
    System.out.println("You chose " + action);
    switch (action) {

    case "D": 

        System.out.println("How much would you like to deposit?");
        amount = input.nextDouble();
        System.out.println("You selected " + amount);

        makeDeposit(name, action, amount);

        break;


    case "W":  
        System.out.println("How much would you like to withdraw? ");
        amount = input.nextDouble();
        System.out.println("You selected " + amount);

        makeWithdrawal(name, action, amount);
        break;


    case "B":  System.out.println("You chose " + action);

    // a print method here using WHERE
    break;


    }// end of switch

    //something to bring back to the top
}
}

And the error:

TRANSACTION_ID= 1, ACCOUNT_ID=John Adams, TRANSACTION_TYPE= initial, AMOUNT=   100.0
TRANSACTION_ID= 2, ACCOUNT_ID=Benjamin Franklin, TRANSACTION_TYPE= initial,     AMOUNT=       200.0
TRANSACTION_ID= 3, ACCOUNT_ID=George Washington, TRANSACTION_TYPE= initial, AMOUNT=       300.0
TRANSACTION_ID= 4, ACCOUNT_ID=Thomas Jefferson, TRANSACTION_TYPE= initial, AMOUNT=       400.0
Please enter the name of your account: 
George Washington
Welcome George Washington
What would you like to do? 
D= Deposit, W = Withdrawal, B = Show balance 
D
You chose D
How much would you like to deposit?
10.00
You selected 10.0
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'name'     in     'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1647)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566)
at db.BankAccount.makeDeposit(BankAccount.java:100)
at db.BankAccount.main(BankAccount.java:187)

Answer 1

Change your makeDeposit() methods this line: In this case you are not paasing your variables, but they are used as simple strings.

String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values(name, action, amount)";

to

String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values ("+name+", "+action+", "+amount+")";

Answer 2

Never use plain statements with substitution. Use preparedStatement instead [ Read: When should we use a PreparedStatement instead of a Statement? ]

    String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values (?, ?, ?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);
    pstmt.setString(1, name );
    pstmt.setString(2, action );
    pstmt.setDouble(3, amount);
pstmt.executeUpdate();

Answer 3

String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values ('" + name + "', '" + action + "', " + amount + ")";

You need the single quotes for the name and action columns since they are of type varchar, and also you need the actual values so you need to build your query with '+' sign.

/Nick

Answer 4

The problem comes from the makeDeposit() method, specifically from these lines:

String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values (name, action, amount)";
stmt.executeUpdate(sql);

Those values aren't right, you probably want some variable named name, not the actual string name.

To provide a bit more insight, the error is a list of the methods that were ran until the error occurred. The one at the top is the most recent. So everything starts from the main() method, which runs the makeDeposit() method and then some methods that follow the call of stmt.executeUpdated(sql). Hope that clears it up.