需要使用Statement的小例子

最新推荐文章于 2025-12-05 17:02:52 发布

原创最新推荐文章于 2025-12-05 17:02:52 发布 · 599 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

学习专栏收录该内容

7 篇文章

订阅专栏

此篇博客介绍了如何根据用户在控制台输入的'asc'或'desc'指令，实现Java中对SQL查询结果进行升序或降序排列的操作。通过Scanner获取用户输入，然后动态构造SQL语句，演示了Statement和PreparedStatement的使用区别。

用户在控制台输入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();
			}
		}
		*/
	}
}