import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;import com.mysql.jdbc.PreparedStatement;public class Homework20100303 { public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null; try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("驱动错误!");
} try {
con = DriverManager
.getConnection("jdbc:mysql://localhost/mydata?user=root&password=sa"); String sql1 = "SELECT * FROM products ORDER BY ? DESC";
ps = (PreparedStatement) con.prepareStatement(sql1);
ps.setString(1, "id");
System.out.println(ps); // 现实这么一行“com.mysql.jdbc.JDBC4PreparedStatement@dd87b2: SELECT * FROM products ORDER BY 'id' DESC”
rs = ps.executeQuery(); while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2)
+ "\t" + rs.getString(3) + "\t" + rs.getString(4)
+ "\t" + rs.getString(5)); } } catch (SQLException e) {
System.out.println("数据库错误!");
} }}
我感觉问题出在把string带入?后,整个SQL语句是错误的。
我想得到的是 SELECT * from products order by id desc 。而却得到了 SELECT * from products order by 'id' desc 就错了啊。如何解决?谢谢
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;import com.mysql.jdbc.PreparedStatement;public class Homework20100303 { public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null; try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("驱动错误!");
} try {
con = DriverManager
.getConnection("jdbc:mysql://localhost/mydata?user=root&password=sa"); String sql1 = "SELECT * FROM products ORDER BY ? DESC";
ps = (PreparedStatement) con.prepareStatement(sql1);
ps.setString(1, "id");
System.out.println(ps); // 现实这么一行“com.mysql.jdbc.JDBC4PreparedStatement@dd87b2: SELECT * FROM products ORDER BY 'id' DESC”
rs = ps.executeQuery(); while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2)
+ "\t" + rs.getString(3) + "\t" + rs.getString(4)
+ "\t" + rs.getString(5)); } } catch (SQLException e) {
System.out.println("数据库错误!");
} }}
我感觉问题出在把string带入?后,整个SQL语句是错误的。
我想得到的是 SELECT * from products order by id desc 。而却得到了 SELECT * from products order by 'id' desc 就错了啊。如何解决?谢谢
不是的话
那就不要setString(1, "id")
setInt(1, id)啊
结构为
mysql> desc products;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| count | int(11) | YES | | NULL | |
| price | double | YES | | NULL | |
| supplier | varchar(20) | YES | | NULL | |
| birth | date | NO | | 0000-00-00 | |
| death | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
String sql = "SELECT * FROM products ORDER BY " + condition + " DESC";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2)
+ "\t" + rs.getString(3) + "\t" + rs.getString(4)
+ "\t" + rs.getString(5));}想用 预编译 的方法实现,就遇到上述问题。
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;import com.mysql.jdbc.ResultSet;public class TestPreparedStatement {
private String driverName;
private String connUrl;
private String userName;
private String password;
public TestPreparedStatement(){
driverName = "com.mysql.jdbc.Driver";
connUrl = "jdbc:mysql://localhost/test";
userName = "root";
password = "123";
}
public void test(){
Connection conn = null;
ResultSet resultSet = null;
PreparedStatement statement = null;
try {
Class.forName(driverName);
conn = DriverManager.getConnection(connUrl, userName, password);
statement = conn.prepareStatement("SELECT * FROM student ORDER BY ? DESC");
statement.setString(1, "id");
System.out.println(statement);
resultSet = (ResultSet) statement.executeQuery();
while (resultSet.next()) {
System.out.println( "编号:"+resultSet.getInt(1)+
"\t\t姓名:"+resultSet.getString(2)+
"\t\t学号:"+resultSet.getString(3)
);
} } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args){
TestPreparedStatement tps = new TestPreparedStatement();
tps.test();
}
}结果:
com.mysql.jdbc.PreparedStatement@105738: SELECT * FROM student ORDER BY 'id' DESC
编号:1 姓名:小梁 学号:07118916
编号:2 姓名:小芬 学号:07118918
编号:2 姓名:小芬 学号:07118918
编号:1 姓名:小梁 学号:07118916 才对啊。
String sql1 = "SELECT * FROM products ORDER BY "+orderBy +" DESC";
不要用pstmt的setString方法,否者就成了SELECT * FROM products ORDER BY "id" DESC