了解Java JDBC错误 [英] understanding Java JDBC error
本文介绍了了解Java JDBC错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我希望能帮助你理解这个错误。我正在建立一个与数据库的简单连接，由于某种原因它不喜欢我的输入，但我无法理解错误实际发生的位置。任何帮助将不胜感激 - 谢谢。
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.
我的代码：
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
}
}
错误：
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)
推荐答案
String sql = "insert into MYTABLE_TRANSACTIONS (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT) values ('" + name + "', '" + action + "', " + amount + ")";
您需要名称和操作列的单引号，因为它们是varchar类型，还有您需要实际值，因此您需要使用+符号构建查询。
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
这篇关于了解Java JDBC错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
