mysql - understanding Java JDBC error -
i hoping have assistance in understanding error. making simple connection database, , reason doesn't input, failing understand error occurring. assistance 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 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 mytable_transactions (account_id,transaction_type,amount) values ('john adams', 'initial', 100.00)"; stmt.executeupdate(sql); sql = "insert mytable_transactions (account_id,transaction_type,amount) values ('benjamin franklin', 'initial', 200.00)"; stmt.executeupdate(sql); sql = "insert mytable_transactions (account_id,transaction_type,amount) values ('george washington', 'initial', 300.00)"; stmt.executeupdate(sql); sql = "insert mytable_transactions (account_id,transaction_type,amount) values ('thomas jefferson', 'initial', 400.00)"; stmt.executeupdate(sql); sql = "select * 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 connection here stmt.close(); } catch (exception e) { e.printstacktrace(); } }// end of create table method // 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 mytable_transactions (account_id,transaction_type,amount) values (name, action, amount)"; stmt.executeupdate(sql); sql = "select * 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 connection here stmt.close(); } catch (exception e) { e.printstacktrace(); } }//********end of deposit method // 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 mytable_transactions (account_id,transaction_type,amount) values (name, action, amount)"; stmt.executeupdate(sql); sql = "select * 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 connection here stmt.close(); } catch (exception e) { e.printstacktrace(); }//********end of withdrawal method } public static void main(string[] args) { //prompt user- do? // withdrawal / deposit / view balance string name; string action; double amount; createtable(); system.out.println("please enter name of account: "); scanner input = new scanner( system.in ); name = input.nextline(); system.out.println("welcome " + name); system.out.println("what do? "); //deposit, withdrawal, check balance system.out.println("d= deposit, w = withdrawal, b = show balance "); action = input.nextline(); //note: fix letter inserts entire word table system.out.println("you chose " + action); switch (action) { case "d": system.out.println("how deposit?"); amount = input.nextdouble(); system.out.println("you selected " + amount); makedeposit(name, action, amount); break; case "w": system.out.println("how withdraw? "); amount = input.nextdouble(); system.out.println("you selected " + amount); makewithdrawal(name, action, amount); break; case "b": system.out.println("you chose " + action); // print method here using break; }// end of switch //something bring top } } and 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 name of account: george washington welcome george washington do? d= deposit, w = withdrawal, b = show balance d chose d how deposit? 10.00 selected 10.0 com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: unknown column 'name' in 'field list' @ sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method) @ sun.reflect.nativeconstructoraccessorimpl.newinstance(unknown source) @ sun.reflect.delegatingconstructoraccessorimpl.newinstance(unknown source) @ java.lang.reflect.constructor.newinstance(unknown source) @ com.mysql.jdbc.util.handlenewinstance(util.java:406) @ com.mysql.jdbc.util.getinstance(util.java:381) @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:1030) @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:956) @ com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3558) @ com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3490) @ com.mysql.jdbc.mysqlio.sendcommand(mysqlio.java:1959) @ com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio.java:2109) @ com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2642) @ com.mysql.jdbc.statementimpl.executeupdate(statementimpl.java:1647) @ com.mysql.jdbc.statementimpl.executeupdate(statementimpl.java:1566) @ db.bankaccount.makedeposit(bankaccount.java:100) @ db.bankaccount.main(bankaccount.java:187)
string sql = "insert mytable_transactions (account_id,transaction_type,amount) values ('" + name + "', '" + action + "', " + amount + ")"; you need single quotes name , action columns since of type varchar, , need actual values need build query '+' sign.
/nick
Comments
Post a Comment