用JDBC实现对用户管理系统的CRUD

Manage类实现，TestMenu类进行测试。 发现四个容易出问题的地方，稍作总结。

Manage类 (第二行URL处的XXX为本地mysql的数据库名称，自行更改)

import java.sql.*; import java.util.Scanner; public class Manage { Scanner sc = new Scanner(System.in); String url = "jdbc:mysql://localhost:3306/XXX"; String username = "root"; String password = "root"; public void login(){ System.out.println("请输入用户名"); String uname = sc.next(); System.out.println("请输入密码"); String pwd = sc.next(); //这里就写sql语句 String sql = "select * from users where username = ? and password = ?;"; //数据库操作 try { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection(url,username,password); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1,uname); pstmt.setString(2,pwd); ResultSet rs = pstmt.executeQuery(); if(rs.next()){ System.out.println("登陆成功"); menu();//登陆成功后进入选择界面 } else{ System.out.println("登陆失败"); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public void menu() throws SQLException { boolean flag = true; do{ System.out.println("1.查询全部 2.根据编号查询 3.添加 4.修改 5.删除 0.退出"); System.out.println("请输入你要查询的数字："); int choose = sc.nextInt(); switch(choose){ case 1: selectAll(); break; case 2: selectById(); break; case 3: Insert(); break; case 4: Update(); break; case 5: Delete(); break; case 0: System.out.println("谢谢使用"); flag = false; break; } }while(flag); } public void selectAll(){ String sql = "select * from users"; try { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection(url,username,password); PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while(rs.next()){ int id = rs.getInt(1); String name = rs.getString(2); String uname = rs.getString(3); String pwd = rs.getString(4); System.out.println(id+"\t"+name+"\t"+uname+"\t"+pwd);//""为字符串 ''为char 加上数字会进行int运算! } rs.close(); pstmt.close(); conn.close(); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public void selectById(){ String sql = "select * from users where id = ?;"; System.out.println("请输入需要查询的id"); int uid = sc.nextInt(); try { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection(url,username,password); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1,uid); ResultSet rs = pstmt.executeQuery(); if (rs.next()){//因为只查询一个人 所以不需要while遍历 只用if int id = rs.getInt(1); String name = rs.getString(2); String uname = rs.getString(3); String pwd = rs.getString(4); System.out.println(id+"\t"+name+"\t"+uname+"\t"+pwd);//""为字符串 ''为char 加上数字会进行int运算！！ } else{ System.out.print("id为"+uid+"的用户不存在"); } rs.close(); pstmt.close(); conn.close(); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public void Insert() throws SQLException { System.out.println("请输入姓名"); String name = sc.next(); System.out.println("请输入用户名"); String uname = sc.next();; System.out.println("请输入密码"); String pwd = sc.next(); //System.out.println("请输入更改的id");//传空 自增 //int uid = sc.nextInt(); String sql = "insert into users values (null,?,?,?);"; try { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection(url,username,password); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1,name); pstmt.setString(2,uname);//第一个值传null 下标1从第一个问号开始 pstmt.setString(3,pwd); int count = pstmt.executeUpdate(); if(count>0){ System.out.println("插入成功"); } else{ System.out.println("插入失败"); } pstmt.close(); conn.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public void Delete() throws SQLException { String sql = "delete from users where id = ?;"; System.out.println("请输入要删除的id："); int uid = sc.nextInt(); try { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection(url,username,password); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1,uid); int count = pstmt.executeUpdate(); if(count>0){ System.out.println("删除成功"); } else{ System.out.println("删除失败"); } pstmt.close(); conn.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public void Update(){ System.out.println("请输入姓名"); String name = sc.next(); System.out.println("请输入用户名"); String uname = sc.next();; System.out.println("请输入密码"); String pwd = sc.next(); System.out.println("请输入更改的id"); int uid = sc.nextInt(); String sql = "update users set name=?,username=?,password=? where id=?;"; try { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection(url,username,password); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1,uid); pstmt.setString(2,name); pstmt.setString(3,uname); pstmt.setString(4,pwd); int count = pstmt.executeUpdate(); if(count>0){ System.out.println("更改成功"); } else{ System.out.println("更改失败"); } pstmt.close(); conn.close(); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } }

Test Menu类

public class TestMenu { public static void main(String[] args){ Manage a = new Manage(); a.login(); } }

总结出四个需要注意的地方： 1.进行字符串拼接时，一定要用 " " 双引号括住字符或字符串串，如果用单引号会使int和char进行int运算，导致结果出错。

2.使用PreparedStatement对象pstmt进行对sql语句占位符’?‘的替换时，setXxx()方法中的编号1是从第一个’?‘开始。如我的sql语句为 第一个空为id,因为之前设置了主键自增，所以传null。这是想要设置后面三个’?'的值，setXxx的编号分别是1，2，3。 3.通过ResrultSet对象rs获取查询得到的结果集时，有两种索引方法。1) 字段索引 2) 编号索引(第一列从1开始)