MySQL 语法错误 Java Memo 应用程序

Question

我目前正在为学校开发一个 Java 备忘录应用程序项目。它在应用程序的模型视图控制器方法上运行，因此有许多文件，每个文件处理某些任务。当我运行 createMemos 类时，出现 sql 语法错误。内容如下...您发现任何语法错误吗？

run:
Model used mysql
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ')' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
at models.Memos.createTable(Memos.java:49)
at guimemos.CreateMemos.main(CreateMemos.java:15)
BUILD SUCCESSFUL (total time: 1 second)

我的备忘录课程如下。 。

public class Memos {

    private String table = "memos";
    private String props_file = "models" + java.io.File.separator + table + ".properties";
    private DB db;

    public Memos() throws Exception {
        db = new DB();
    }

    public void createTable() throws Exception {

        Properties table_props = Util.loadFile(props_file);

        String property = DB.getModel() + "." + table;
        String table_def = table_props.getProperty(property);
        if (table_def == null) {
            throw new Exception("no such property: " + property);
        }
        Connection cx = db.connect();

        String sql_op;
        Statement st = cx.createStatement();

        sql_op = "drop table if exists " + table;
        st.executeUpdate(sql_op);

        sql_op = "create table " + table + "(" + table_def + ")";

        st.executeUpdate(sql_op);
    }

    public int insert(Memo memo) throws Exception {
        Connection cx = db.connect();

        PreparedStatement st = cx.prepareStatement(
                "insert into " + table + "(title,timeStamp,content) values (?, ?, ?)");
        st.setString(1, memo.getTitle());
        st.setTimestamp(2, memo.getTimeStamp());
        // st.setString(2, memo.getTimeStamp().toString());
        st.setString(3, memo.getContent());
        st.executeUpdate();

        st = cx.prepareStatement("select max(id) from " + table);
        ResultSet rs = st.executeQuery();
        rs.next();
        int new_id = rs.getInt(1);
        return new_id;
    }

    // fetch all memos
    public List<Memo> fetchAll() throws Exception {
        Connection cx = db.connect();

        String sql_op = "select * from " + table;
        Statement st = cx.createStatement();

        ResultSet rs = st.executeQuery(sql_op);

        List<Memo> L = new LinkedList<Memo>();
        while (rs.next()) {
            int id = rs.getInt("id");
            String title = rs.getString("title");
            String content = rs.getString("content");
            Timestamp timeStamp = rs.getTimestamp("timeStamp");



            Memo memo = new Memo(id, title, timeStamp, content);
            L.add(memo);
        }
        return L;
    }

    // fetch one memo
    public Memo fetch(int id) throws Exception {
        Connection cx = db.connect();
        PreparedStatement st = cx.prepareStatement(
                "select * from " + table + " where id=?");
        st.setInt(1, id);
        ResultSet rs = st.executeQuery();
        if (!rs.next()) {
            return null;
        }
        String title = rs.getString("title");
        String content = rs.getString("content");
        Timestamp timeStamp = rs.getTimestamp("timeStamp");


        Memo memo = new Memo(id, title, timeStamp, content);
        return memo;
    }

    // remove one memo
    public boolean remove(int id) throws Exception {
        Connection cx = db.connect();
        PreparedStatement st = cx.prepareStatement(
                "delete from " + table + " where id=?");
        st.setInt(1, id);
        int num = st.executeUpdate();
        return (num != 0);
    }

    // update one memo
    public void modify(Memo memo) throws Exception {
        Connection cx = db.connect();

        int id = memo.getId();

        PreparedStatement st = cx.prepareStatement("update " + table
                + " set title=?, timeStamp=?, content=?" + " where id=?");

        st.setString(1, memo.getTitle());
        st.setTimestamp(2, memo.getTimeStamp());
        st.setString(3, memo.getContent());
        st.setInt(4, id);
        st.executeUpdate();
    }
}

这是属性文件：

mysql.memos=id integer auto_increment primary key not null,\
title varchar(80) not null,
timeStamp datetime not null,\
content text

sqlite.memos=id integer primary key not null,\
title text not null,\
timeStamp int not null,\
content text

添加建议后出错

Model used mysql
Attempting to execute SQL: drop table if exists memos
Attempting to execute SQL: create table memos(id integer auto_increment primary key     not null,title varchar(80) not null,)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ')' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at   sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39    )
at   sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
at models.Memos.createTable(Memos.java:50)
at guimemos.CreateMemos.main(CreateMemos.java:15)

Answer 1

看起来这个语句中的 table_def 有问题：

sql_op = "create table " + table + "(" + table_def + ")";
st.executeUpdate(sql_op);

不过你是从属性文件中读取它，所以我们需要实际查看它才能知道出了什么问题。我会尝试将其更改为：

sql_op = "create table " + table + "(" + table_def + ")";
System.out.println("Attempting to execute SQL: " + sql_op);
st.executeUpdate(sql_op);

然后让我们知道输出是什么。

编辑：问题在于本节：

mysql.memos=id integer auto_increment primary key not null,\
title varchar(80) not null,
timeStamp datetime not null,\
content text

请注意第二行缺少 \ 。这会导致您的属性被切断，因此您会丢失时间戳和内容行，并且这是一个语法错误，因为有一个尾随 ,。

尝试改成这样：

mysql.memos=id integer auto_increment primary key not null,\
title varchar(80) not null,\
timeStamp datetime not null,\
content text