为什么在执行预编译语句时会出现java.sql.SQLSyntaxErrorException？

huangapple
go评论62阅读模式
英文:

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&lt;Person&gt; people;
public Database() {
people = new LinkedList&lt;Person&gt;();
}
private Connection con;
public void connect() throws Exception{
if(con != null) return;
try {
Class.forName(&quot;com.mysql.cj.jdbc.Driver&quot;);
} catch (ClassNotFoundException e) {
throw new Exception(&quot;Driver not found&quot;);
}
String Url = &quot;jdbc:mysql://localhost:3306/jdbc&quot;;
Connection con;
con = DriverManager.getConnection(Url, &quot;root&quot;, &quot;089abc2019&quot;);
System.out.println(&quot;Connected: &quot; + con);
}
public void disconnect() {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
System.out.println(&quot;Can&#39;t close connection&quot;);
}
}
}
public void save() throws SQLException {
String checkSql = &quot;Select count * from jdbc where id=?&quot;;
String Url = &quot;jdbc:mysql://localhost:3306/jdbc&quot;;
Connection con = DriverManager.getConnection(Url, &quot;root&quot;, &quot;089abc2019&quot;);
if (con==null) {
fail(&quot;Connection couldn&#39;t be established&quot;);
}
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(&quot;Count for person wiht ID: &quot; + id + &quot;is &quot; + 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&lt;Person&gt; 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(&quot;Running database test&quot;);
Database db = new Database(); 
try {
db.connect();
} catch (Exception e) {
e.printStackTrace();
}
db.addPerson(new Person(&quot;Joe&quot;, &quot;Builder&quot;, AgeCategory.adult, EmploymentCategory.emloyed, &quot;654321&quot;, true, Gender.Male));
db.addPerson(new Person(&quot;Sue&quot;, &quot;Athlete&quot;, AgeCategory.adult, EmploymentCategory.self_Employed, &quot;652321&quot;, 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 &#39;from jdbc where id=0&#39; 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=?

huangapple
  • 本文由 发表于 2020年3月16日 12:30:46
  • 转载请务必保留本文链接：https://go.coder-hub.com/60700352.html
  • java
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定