用户在控制台输入desc就是降序,输入asc就是升序
import java.util.Scanner;
import java.sql.*;
public class StatementTest {
public static void main(String[] args) {
//用户在控制台输入desc就是降序,输入asc就是升序
Scanner tf = new Scanner(System.in);
System.out.println("输入desc获asc,desc表示降序,asc表示升序");
System.out.print("请输入:");
String keywords = tf.nextLine();
//执行SQL
Connection conn = null;
Statement sm = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user","root","123456");
sm = conn.createStatement();
String sql = "select loginName from user order by loginName "+keywords;
rs = sm.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getString("loginName"));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e ) {
e.printStackTrace();
} finally {
try {
if(rs != null) {
rs.close();
}
} catch(SQLException e) {
e.printStackTrace();
}
try {
if(sm != null) {
sm.close();
}
} catch(SQLException e) {
e.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch(SQLException e) {
e.printStackTrace();
}
}
/**
* 使用PreparedStatement 会报SQLSyntaxErrorException错误
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user","root","123456");
String sql = "select loginName from user order by loginName ?";
ps = conn.prepareStatement(sql);
ps.setString(1,keywords);
rs = ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getString("loginName"));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e ) {
e.printStackTrace();
} finally {
try {
if(rs != null) {
rs.close();
}
} catch(SQLException e) {
e.printStackTrace();
}
try {
if(ps != null) {
ps.close();
}
} catch(SQLException e) {
e.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch(SQLException e) {
e.printStackTrace();
}
}
*/
}
}