jdbc 练习案例2(使用Statement)

import java.util.Scanner;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Map;
import java.util.HashMap;

/**
 * 存在SQL注入问题
 */
public class Test {
	public static void main(String[] args) {	
		// 初始化界面
		initUI();
	}

	/**
	 * 初始化界面
	 */
	private static void initUI() {
		int num = 0;
		System.out.println("\n****************************");
		System.out.println("1.添加记录");
		System.out.println("2.修改记录");
		System.out.println("3.删除记录");
		System.out.println("4.查询记录");
		System.out.println("输入其它，退出程序");
		System.out.println("\n****************************");
		System.out.print("请输入操作项：");
		Scanner sc = new Scanner(System.in);
		try{
			num = sc.nextInt();
			System.out.println();
			switch(num) {
				case 1:
					// 添加记录
					add();
					break;
				case 2:
					update();
					break;
				case 3:
					del();
					break;
				case 4:
					select();
					break;
				default:

					break;
			}
		} catch(Exception e) {
			//System.out.println("输入有误");
			//e.printStackTrace();
		} finally {
			sc.close();
		}
	}

	/**
	 * 添加记录
	 */
	private static void add() {
		Scanner sc = new Scanner(System.in);
		System.out.print("请输入用户名：");
		String loginName = sc.nextLine();
		System.out.print("请输入密码：");
		String loginPwd = sc.nextLine();
		System.out.print("请输入姓名：");
		String name = sc.nextLine();
		Map<String, String> map = new HashMap<>();
		map.put("loginName", loginName);
		map.put("loginPwd", loginPwd);
		map.put("name", name);
		sc.close();
		test("add", map);
	}

	/**
	 * 修改数据
	 */
	public static void update() {
		Scanner sc = new Scanner(System.in);
		System.out.print("请输入需要用户名：");
		String loginName = sc.nextLine();
		System.out.print("请输入需要密码：");
		String loginPwd = sc.nextLine();
		System.out.print("请输入修改后姓名：");
		String name = sc.nextLine();
		Map<String, String> map = new HashMap<>();
		map.put("loginName", loginName);
		map.put("loginPwd", loginPwd);
		map.put("name", name);
		sc.close();
		test("update", map);
	}

	/**
	 * 删除数据
	 */
	public static void del() {
		Scanner sc = new Scanner(System.in);
		System.out.print("请输入需要删除用户名：");
		String loginName = sc.nextLine();
		Map<String, String> map = new HashMap<>();
		map.put("loginName", loginName);
		sc.close();
		test("del", map);
	}

	/**
	 * 查询数据
	 */
	public static void select() {
		test("select");
	}


	
	private static void test(String type, Map<String, String> map) {
		Connection conn = null;
		Statement stat = null;
		ResultSet res = null;
		try{
			// 注册驱动
			Driver driver = new com.mysql.cj.jdbc.Driver();
			DriverManager.registerDriver(driver);

			// 获取连接
			conn = DriverManager.getConnection("jdbc:mysql://ip地址:端口号/库名", "mysql用户名", "mysql密码");

			// 获取操作对象
			stat = conn.createStatement();

			String loginName = map.get("loginName");
			String loginPwd = map.get("loginPwd");
			String name = map.get("name");

			// 执行sql
			String sql = "";
			int count = 0;
			switch(type) {
				case "add":
					sql = "insert into t_user (loginName, loginPwd, name) values ('" + loginName + "', '" + loginPwd + "', '" + name + "')";
					count = stat.executeUpdate(sql);
					System.out.print(count > 0 ? "添加成功" : "添加失败");
					break;
				case "update":
					sql = "update t_user set name='" + name + "' where loginName='" + loginName + "' and loginPwd='" + loginPwd + "'";
					count = stat.executeUpdate(sql);
					System.out.print(count > 0 ? "修改成功" : "修改失败");
					break;
				case "del":
					sql = "delete from t_user where loginName='" + loginName + "'";
					count = stat.executeUpdate(sql);
					System.out.print(count > 0 ? "删除成功" : "删除失败");
					break;
				case "select":
					sql = "select loginName, loginPwd, name from t_user";
					res = stat.executeQuery(sql);
					while(res.next()) {
						System.out.println(res.getString("loginName") + "\t" + res.getString("loginPwd") + "\t" + res.getString("name"));
					}
					break;
			}
		} catch(SQLException e){
			e.printStackTrace();
		} finally{
			if(res != null){
				try{
					res.close();
				} catch(SQLException e){
					e.printStackTrace();
				}
			}
			if(stat != null){
				try{
					stat.close();
				} catch(SQLException e){
					e.printStackTrace();
				}
			}
			if(conn != null){
				try{
					conn.close();
				} catch(SQLException e){
					e.printStackTrace();
				}
			}
		}
	}


	private static void test(String type) {
		Connection conn = null;
		Statement stat = null;
		ResultSet res = null;
		try{
			// 注册驱动
			Driver driver = new com.mysql.cj.jdbc.Driver();
			DriverManager.registerDriver(driver);

			// 获取连接
			conn = DriverManager.getConnection("jdbc:mysql://106.53.237.216:3306/bztest", "root", "root.5354");

			// 获取操作对象
			stat = conn.createStatement();
			
			String sql = "select loginName, loginPwd, name from t_user";
			res = stat.executeQuery(sql);
			while(res.next()) {
				System.out.println(res.getString("loginName") + "\t" + res.getString("loginPwd") + "\t" + res.getString("name"));
			}
		} catch(SQLException e){
			e.printStackTrace();
		} finally{
			if(res != null){
				try{
					res.close();
				} catch(SQLException e){
					e.printStackTrace();
				}
			}
			if(stat != null){
				try{
					stat.close();
				} catch(SQLException e){
					e.printStackTrace();
				}
			}
			if(conn != null){
				try{
					conn.close();
				} catch(SQLException e){
					e.printStackTrace();
				}
			}
		}
	}
}