JDBC_10_使用Statement实现升序和降序

使用Statement数据库操作对象实现升序和降序

• Statement可以使用在需要SQL语句拼接的情况下，因为在这样的情况下如果使用PreparedStatement就会给需要拼接的某个SQL关键字加上单引号，那么SQL语法就会报错，例如本例子用在升序和降序上。ASC/DESC

• 代码实例：

   import java.sql.*;
   import java.util.Scanner;

   /**
    * 业务需求：用户再控制台输入ASC就是升序，输入DESC就是降序
    */

   public class JDBCTest07 {
       public static void main(String[] args) {

           System.out.println("请输入命令: DESC/ASC");
           Scanner scanner=new Scanner(System.in);
           String command=scanner.nextLine();


           //创建数据库连接所需对象
           Connection connection=null;
           Statement statement=null;
           ResultSet resultSet=null;

           try {
               //注册驱动
               Class.forName("com.mysql.cj.jdbc.Driver");

               //获取连接
               connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/employ?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai",
                       "root","123456");

               //获取数据库操作对象
               statement=connection.createStatement();

               //执行sql语句
               String sql="select * from emp order by SAL "+ command;
               resultSet=statement.executeQuery(sql);

               //处理查询结果集
               while (resultSet.next()){
                   System.out.print(resultSet.getInt("EMPNO")+"   ");
                   System.out.print(resultSet.getString("ENAME")+"   ");
                   System.out.print(resultSet.getString("JOB")+"   ");
                   System.out.print(resultSet.getInt("MGR")+"   ");
                   System.out.print(resultSet.getDate("HIREDATE")+"   ");
                   System.out.print(resultSet.getDouble("SAL")+"   ");
                   System.out.print(resultSet.getDouble("COMM")+"   ");
                   System.out.println(resultSet.getInt("DEPTNO"));
               }

           } catch (ClassNotFoundException | SQLException e) {
               e.printStackTrace();
           }finally {
               if(resultSet!=null){
                   try {
                       resultSet.close();
                   } catch (SQLException e) {
                       e.printStackTrace();
                   }
               }

               if(statement!=null){
                   try {
                       statement.close();
                   } catch (SQLException e) {
                       e.printStackTrace();
                   }
               }

               if(connection!=null){
                   try {
                       connection.close();
                   } catch (SQLException e) {
                       e.printStackTrace();
                   }
               }
           }

      }

 }