英文:
why is there a java.sql.SQLSyntaxErrorException when I execute the Prepared Statement?
问题
我的数据库类文件:
package Model;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
public class Database {
private LinkedList<Person> people;
public Database() {
people = new LinkedList<Person>();
}
private Connection con;
public void connect() throws Exception {
if (con != null) return;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new Exception("Driver not found");
}
String Url = "jdbc:mysql://localhost:3306/jdbc";
con = DriverManager.getConnection(Url, "root", "089abc2019");
System.out.println("Connected: " + con);
}
public void disconnect() {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.out.println("Can't close connection");
}
}
}
public void save() throws SQLException {
String checkSql = "Select count(*) from jdbc where id=?";
String Url = "jdbc:mysql://localhost:3306/jdbc";
Connection con = DriverManager.getConnection(Url, "root", "089abc2019");
if (con == null) {
fail("Connection couldn't be established");
}
PreparedStatement checkStmt = con.prepareStatement(checkSql);
for (Person person : people) {
int id = person.getId();
checkStmt.setInt(1, id);
ResultSet checkResult = checkStmt.executeQuery();
checkResult.next();
int count = checkResult.getInt(1);
System.out.println("Count for person with ID: " + id + " is " + count);
}
checkStmt.close();
}
private void fail(String string) {
System.out.println(string);
}
public void addPerson(Person person) {
people.add(person);
}
public void removePerson(int index) {
people.remove();
}
public List<Person> getPeople() {
return Collections.unmodifiableList(people);
}
public void saveToFile(File file) throws IOException {
FileOutputStream fos = new FileOutputStream(file);
ObjectOutputStream oos = new ObjectOutputStream(fos);
Person[] persons = people.toArray(new Person[people.size()]);
oos.writeObject(persons);
oos.close();
}
public void loadFromFile(File file) throws IOException {
FileInputStream fis = new FileInputStream(file);
ObjectInputStream ois = new ObjectInputStream(fis);
try {
Person[] person = (Person[]) ois.readObject();
people.clear();
people.addAll(Arrays.asList(person));
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ois.close();
}
}
我的测试数据库文件:
import java.sql.SQLException;
import Model.AgeCategory;
import Model.Database;
import Model.EmploymentCategory;
import Model.Gender;
import Model.Person;
public class TestDatabase {
public static void main(String[] args) {
System.out.println("Running database test");
Database db = new Database();
try {
db.connect();
} catch (Exception e) {
e.printStackTrace();
}
db.addPerson(new Person("Joe", "Builder", AgeCategory.adult, EmploymentCategory.emloyed, "654321", true, Gender.Male));
db.addPerson(new Person("Sue", "Athlete", AgeCategory.adult, EmploymentCategory.self_Employed, "652321", false, Gender.Female));
try {
db.save();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
db.disconnect();
}
}
控制台中的结果:
Running database test
Connected: com.mysql.cj.jdbc.ConnectionImpl@316bcf94
java.sql.SQLSyntaxErrorException: 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 'from jdbc where id=0' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
at Model.Database.save(Database.java:74)
at TestDatabase.main(TestDatabase.java:26)
英文:
My database class file:
package Model;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
public class Database {
private LinkedList<Person> people;
public Database() {
people = new LinkedList<Person>();
}
private Connection con;
public void connect() throws Exception{
if(con != null) return;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new Exception("Driver not found");
}
String Url = "jdbc:mysql://localhost:3306/jdbc";
Connection con;
con = DriverManager.getConnection(Url, "root", "089abc2019");
System.out.println("Connected: " + con);
}
public void disconnect() {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
System.out.println("Can't close connection");
}
}
}
public void save() throws SQLException {
String checkSql = "Select count * from jdbc where id=?";
String Url = "jdbc:mysql://localhost:3306/jdbc";
Connection con = DriverManager.getConnection(Url, "root", "089abc2019");
if (con==null) {
fail("Connection couldn't be established");
}
PreparedStatement checkStmt = con.prepareStatement(checkSql);
for (Person person: people) {
int id = person.getId();
checkStmt.setInt(1, id);
ResultSet checkResult = checkStmt.executeQuery();
checkResult.next();
int count = checkResult.getInt(1);
System.out.println("Count for person wiht ID: " + id + "is " + count);
}
checkStmt.close();
}
private void fail(String string) {
System.out.println(string);
}
public void addPerson(Person person) {
people.add(person);
}
public void removePerson(int index) {
people.remove();
}
public List<Person> getPeople(){
return Collections.unmodifiableList(people);
}
public void saveToFile(File file) throws IOException{
FileOutputStream fos = new FileOutputStream(file);
ObjectOutputStream oos = new ObjectOutputStream(fos);
Person[] persons = people.toArray(new Person[people.size()]);
oos.writeObject(persons);
oos.close();
}
public void loadFromFile(File file) throws IOException{
FileInputStream fis = new FileInputStream(file);
ObjectInputStream ois = new ObjectInputStream(fis);
try {
Person[] person = (Person[]) ois.readObject();
people.clear();
people.addAll(Arrays.asList(person));
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ois.close();
}
}
My testDatabsae file:
import java.sql.SQLException;
import Model.AgeCategory;
import Model.Database;
import Model.EmploymentCategory;
import Model.Gender;
import Model.Person;
public class TestDatabase {
public static void main(String[] args) {
System.out.println("Running database test");
Database db = new Database();
try {
db.connect();
} catch (Exception e) {
e.printStackTrace();
}
db.addPerson(new Person("Joe", "Builder", AgeCategory.adult, EmploymentCategory.emloyed, "654321", true, Gender.Male));
db.addPerson(new Person("Sue", "Athlete", AgeCategory.adult, EmploymentCategory.self_Employed, "652321", false, Gender.Female));
try {
db.save();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
db.disconnect();
}
}
The result in the console:
Running database test
Connected: com.mysql.cj.jdbc.ConnectionImpl@316bcf94
java.sql.SQLSyntaxErrorException: 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 'from jdbc where id=0' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
at Model.Database.save(Database.java:74)
at TestDatabase.main(TestDatabase.java:26)
答案1
得分: 1
你的 SQL 语法不正确。COUNT
是一个分组函数。应该将
Select count * from jdbc where id=?
修改为
Select count(*) from jdbc where id=?
英文:
Your sql syntax is incorrect. COUNT
is a group function. This
Select count * from jdbc where id=?
should be
Select count(*) from jdbc where id=?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论