0cZHckaC

1MySQL syntax error Java Memo application
2run:
3Model used mysql
4com.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
5at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
6at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
7at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
8at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
9at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
10at com.mysql.jdbc.Util.getInstance(Util.java:384)
11at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
12at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
13at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
14at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
15at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
16at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
17at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
18at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
19at models.Memos.createTable(Memos.java:49)
20at guimemos.CreateMemos.main(CreateMemos.java:15)
21BUILD SUCCESSFUL (total time: 1 second)
22	
23public class Memos {
24
25    private String table = "memos";
26    private String props_file = "models" + java.io.File.separator + table + ".properties";
27    private DB db;
28
29    public Memos() throws Exception {
30        db = new DB();
31    }
32
33    public void createTable() throws Exception {
34
35        Properties table_props = Util.loadFile(props_file);
36
37        String property = DB.getModel() + "." + table;
38        String table_def = table_props.getProperty(property);
39        if (table_def == null) {
40            throw new Exception("no such property: " + property);
41        }
42        Connection cx = db.connect();
43
44        String sql_op;
45        Statement st = cx.createStatement();
46
47        sql_op = "drop table if exists " + table;
48        st.executeUpdate(sql_op);
49
50        sql_op = "create table " + table + "(" + table_def + ")";
51
52        st.executeUpdate(sql_op);
53    }
54
55    public int insert(Memo memo) throws Exception {
56        Connection cx = db.connect();
57
58        PreparedStatement st = cx.prepareStatement(
59                "insert into " + table + "(title,timeStamp,content) values (?, ?, ?)");
60        st.setString(1, memo.getTitle());
61        st.setTimestamp(2, memo.getTimeStamp());
62        // st.setString(2, memo.getTimeStamp().toString());
63        st.setString(3, memo.getContent());
64        st.executeUpdate();
65
66        st = cx.prepareStatement("select max(id) from " + table);
67        ResultSet rs = st.executeQuery();
68        rs.next();
69        int new_id = rs.getInt(1);
70        return new_id;
71    }
72
73    // fetch all memos
74    public List<Memo> fetchAll() throws Exception {
75        Connection cx = db.connect();
76
77        String sql_op = "select * from " + table;
78        Statement st = cx.createStatement();
79
80        ResultSet rs = st.executeQuery(sql_op);
81
82        List<Memo> L = new LinkedList<Memo>();
83        while (rs.next()) {
84            int id = rs.getInt("id");
85            String title = rs.getString("title");
86            String content = rs.getString("content");
87            Timestamp timeStamp = rs.getTimestamp("timeStamp");
88
89
90
91            Memo memo = new Memo(id, title, timeStamp, content);
92            L.add(memo);
93        }
94        return L;
95    }
96
97    // fetch one memo
98    public Memo fetch(int id) throws Exception {
99        Connection cx = db.connect();
100        PreparedStatement st = cx.prepareStatement(
101                "select * from " + table + " where id=?");
102        st.setInt(1, id);
103        ResultSet rs = st.executeQuery();
104        if (!rs.next()) {
105            return null;
106        }
107        String title = rs.getString("title");
108        String content = rs.getString("content");
109        Timestamp timeStamp = rs.getTimestamp("timeStamp");
110
111
112        Memo memo = new Memo(id, title, timeStamp, content);
113        return memo;
114    }
115
116    // remove one memo
117    public boolean remove(int id) throws Exception {
118        Connection cx = db.connect();
119        PreparedStatement st = cx.prepareStatement(
120                "delete from " + table + " where id=?");
121        st.setInt(1, id);
122        int num = st.executeUpdate();
123        return (num != 0);
124    }
125
126    // update one memo
127    public void modify(Memo memo) throws Exception {
128        Connection cx = db.connect();
129
130        int id = memo.getId();
131
132        PreparedStatement st = cx.prepareStatement("update " + table
133                + " set title=?, timeStamp=?, content=?" + " where id=?");
134
135        st.setString(1, memo.getTitle());
136        st.setTimestamp(2, memo.getTimeStamp());
137        st.setString(3, memo.getContent());
138        st.setInt(4, id);
139        st.executeUpdate();
140    }
141}
142	
143mysql.memos=id integer auto_increment primary key not null,
144title varchar(80) not null,
145timeStamp datetime not null,
146content text
147
148sqlite.memos=id integer primary key not null,
149title text not null,
150timeStamp int not null,
151content text
152	
153Model used mysql
154Attempting to execute SQL: drop table if exists memos
155Attempting to execute SQL: create table memos(id integer auto_increment primary key     not null,title varchar(80) not null,)
156com.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
157at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
158at   sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39    )
159at   sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
160at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
161at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
162at com.mysql.jdbc.Util.getInstance(Util.java:384)
163at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
164at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
165at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
166at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
167at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
168at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
169at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
170at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
171at models.Memos.createTable(Memos.java:50)
172at guimemos.CreateMemos.main(CreateMemos.java:15)
173	
174sql_op = "create table " + table + "(" + table_def + ")";
175st.executeUpdate(sql_op);
176	
177sql_op = "create table " + table + "(" + table_def + ")";
178System.out.println("Attempting to execute SQL: " + sql_op);
179st.executeUpdate(sql_op);
180	
181mysql.memos=id integer auto_increment primary key not null,
182title varchar(80) not null,
183timeStamp datetime not null,
184content text
185	
186mysql.memos=id integer auto_increment primary key not null,
187title varchar(80) not null,
188timeStamp datetime not null,
189content text