04_JDBC：Java数据库连接技术 - 实例一 - 《Java笔记》

需求

需求

1. 创建用户表 
2. 字段为  编号（id） 主键，姓名（uname），年龄（age），密码（password）
功能实现
1.实现登陆的功能（避免sql注入）
a)    根据姓名和密码登陆
i.    登陆成功 
1.    显示欢迎“xxx”登陆成功
2.    显示用户表中所有的数据
3.    显示变更和删除的操作
4.    根据用户的选择能够实现根据id删除
欢迎登陆xx系统
请输入用户名
请输入密码
-----如果登陆成功-----
“xxx”登陆成功
          展示所有的用户信息
1  张三  18  111111
2  里斯  19  222222
  需要删除请按 1
  请输入该用户的id
  删除成功 ｜ 失败
-----登陆失败-----
提示用户 用户名错误 还是 密码错误

dba.properties

mysqlDriver=com.mysql.cj.jdbc.Driver
mysqlUrl=jdbc:mysql://localhost:3306/bjsxt05?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
mysqlUsername=root
mysqlPwd=root

class JdbcUser

package com.bjsxt.pojoA;
public class JdbcUser {
    private int id;
    private String uname;
    private int age;
    private String password;
    public JdbcUser() {
    }
    public JdbcUser(int id, String uname, int age, String password) {
        this.id = id;
        this.uname = uname;
        this.age = age;
        this.password = password;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUname() {
        return uname;
    }
    public void setUname(String uname) {
        this.uname = uname;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    @Override
    public String toString() {
        return  "id=" + id +
                ", uname='" + uname + '\'' +
                ", age=" + age +
                ", password='" + password + '\'';
    }
}

class Login

package com.bjsxt.jdbcA;
import com.bjsxt.pojoA.JdbcUser;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Properties;
import java.util.Scanner;

public class Login {
    public static void main(String[] args) throws IOException {
        Scanner sc = new Scanner(System.in);
        System.out.println("欢迎登录xx系统");
        System.out.println("请输入用户名:");
        String na = sc.next();
        System.out.println("请输入密码：");
        String pw = sc.next();

        InputStream ras = Login.class.getClassLoader().getResourceAsStream("dba.properties");
        Properties properties = new Properties();
        properties.load(ras);
        String driver = (String) properties.get("mysqlDriver");
        String url = (String) properties.get("mysqlUrl");
        String user = (String) properties.get("mysqlUsername");
        String pwd = (String) properties.get("mysqlPwd");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.加载驱动
            Class.forName(driver);
            //2.连接mysql数据库
            connection = DriverManager.getConnection(url, user, pwd);
            String sql = "select * from jdbc_user where uname=? and password=?";
            //3.创建sql发送器，并将sql语句发送到mysql中
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,na);
            preparedStatement.setString(2,pw);
            //4.执行sql后的结果集
            ResultSet set = preparedStatement.executeQuery();
            JdbcUser jdbcUser=null;
            LinkedList<JdbcUser> list = new LinkedList<>();//存放用户数据
            //展示所有的用户信息
            while (set.next()){
                int id=set.getInt(1);
                String name=set.getString(2);
                int age=set.getInt(3);
                String password=set.getString(4);
                jdbcUser=new JdbcUser(id,name,age,password);
            }
            //jdbc有值 用户名和密码正确 没有值：用户名或者密码有误
            if (jdbcUser==null){
                String sql1 = "select * from jdbc_user where uname=?";
                PreparedStatement pss = connection.prepareStatement(sql1);
                pss.setString(1,na);
                ResultSet set2 = pss.executeQuery();
                int sum=0;
                while (set2.next()){
                    sum++;
                }
                if (sum==0){
                    System.out.println("用户名输入错误");
                }else{
                    System.out.println("密码输入错误");
                }
            }else{
                System.out.println(jdbcUser.getUname()+"登录成功");
                //登录成功后显示所有的用户信息
                System.out.println("即将展示所有的用户信息");
                PreparedStatement ps1 = connection.prepareStatement("select * from  jdbc_user");
                ResultSet set1 = ps1.executeQuery();
                while (set1.next()){
                    int id=set1.getInt(1);
                    String name=set1.getString(2);
                    int age=set1.getInt(3);
                    String password=set1.getString(4);
                    jdbcUser=new JdbcUser(id,name,age,password);
                    list.add(jdbcUser);
                }
                //用迭代器将集合中的用户数据输出
                Iterator<JdbcUser> iterator = list.iterator();
                while (iterator.hasNext()){
                    JdbcUser next = iterator.next();
                    System.out.println(next);
                }
                //删除功能
                System.out.println("需要删除请按 1");
                int i = sc.nextInt();
                if (i==1){
                    System.out.println("请输入该用户的id");
                    int i1 = sc.nextInt();
                    PreparedStatement ps2 = connection.prepareStatement("delete from jdbc_user where id=?");
                    ps2.setInt(1,i1);
                    int i2 = ps2.executeUpdate();
                    if (i2>0){
                        System.out.println("删除成功");
                    }else{
                        System.out.println("删除失败");
                    }
                }
            }

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

        }
    }
}