简单的JDBC增删改查及登陆小程序

1.Bean类

public class TbUser {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "TbUser{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

2.Dao类(增删改查、登陆方法)

public class TbUserDao {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    static {
        try {
            Connection conn = JDBCUtils.getConnection(JDBCUtils.qudong, JDBCUtils.url, JDBCUtils.user,JDBCUtils.password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
        public List<TbUser> findAll() {

            List<TbUser> users = new ArrayList<>();
            try {
                //1.加载驱动
//            Class.forName("com.mysql.jdbc.Driver");
                //2.创建连接
//            conn = DriverManager.getConnection("jdbc:mysql:///ningda?", "root", "root");

//            System.out.println("创建连接成功");
                //3.sql语句
                String sql = "select * from tb_user";
                //4.得到statement对象
                pstmt = conn.prepareStatement(sql);
                //5.执行sql
                rs = pstmt.executeQuery();
                //6.处理结果集
                while (rs.next()) {
                    TbUser user = new TbUser();
                    user.setId(rs.getInt(1));
                    user.setUsername(rs.getString(2));
                    user.setPassword(rs.getString(3));
                    users.add(user);
                }
                //7.关闭资源
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(rs,pstmt, conn);
            }
            return users;
        }


    public void Test2(){

            try {
                //1.加载驱动
//                Class.forName("com.mysql.jdbc.Driver");
                //2.创建连接
//                conn = DriverManager.getConnection("jdbc:mysql://localhost/ningda?", "root", "root");
//                System.out.println("创建连接成功");
                //3.sql语句
                String sql="insert into tb_user value(?,?,?) ";

                //4.得到statement对象
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1,2);
                pstmt.setString(2,"asd");
                pstmt.setString(3,"456");
                //5.执行sql
                int count = pstmt.executeUpdate();
                //6.处理结果集
                //7.关闭资源
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(rs,pstmt, conn);
            }
        }


    public void Test3(){


            try {
                //1.加载驱动
//            Class.forName("com.mysql.jdbc.Driver");
                //2.创建连接
//            conn = DriverManager.getConnection("jdbc:mysql://localhost/ningda?", "root", "root");
//            System.out.println("创建连接成功");
                //3.sql语句
                String sql = "update tb_user set password='789' where id=2";
                //4.得到statement对象
                pstmt = conn.prepareStatement(sql);
                //5.执行sql
                int count = pstmt.executeUpdate();
                //6.处理结果集
                //7.关闭资源
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(rs,pstmt, conn);
            }
        }


    public void Test4() {

            try {
                //1.加载驱动
//            Class.forName("com.mysql.jdbc.Driver");
                //2.创建连接
//            conn = DriverManager.getConnection("jdbc:mysql://localhost/ningda?", "root", "root");
//            System.out.println("创建连接成功");
                //3.sql语句
                String sql = "delete from tb_user where id=2";
                //4.得到statement对象
                pstmt = conn.prepareStatement(sql);
                //5.执行sql
                int count = pstmt.executeUpdate();
                //6.处理结果集
                //7.关闭资源
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(rs,pstmt, conn);
            }
        }
public Boolean login(String username,String password) throws Exception {
    conn = JDBCUtils.getConnection(JDBCUtils.qudong, JDBCUtils.url, JDBCUtils.user,JDBCUtils.password);
    String sql="select * from tb_user where username=? and password=?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1,username);
    pstmt.setString(2,password);
    rs=pstmt.executeQuery();
    if(rs.next())
    {
        return true;
    }
    else {
    return false;}
}
}

3.Service类

public class TbUserService {
    public static void main(String[] args) throws Exception {
        Scanner input=new Scanner(System.in);
        System.out.println();
        System.out.println("请输入账号");
        String username=input.next();
        System.out.println("请输入密码");
        String password=input.next();
        TbUserDao userDao=new TbUserDao();
        boolean flag=userDao.login(username,password);
        if (flag==true)
        {
            System.out.println("连接成功");
        }
        else{
            System.out.println("连接失败");
        }
    }
}

4.Util工具类

public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    static {
        //读取资源文件，获取值
        try {
            // 1.创建properties集合类
            Properties pro=new Properties();

            //获取src路径下的文件的方式-->ClassLoader
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL res=classLoader.getResource("jdbc.properties");
            String path=res.getPath();
            System.out.println(path);

            //2.加载文件
            // pro.load(new FileReader("src/jdbc.properties"));
            pro.load(new FileReader(path));

            //3.获取数据，赋值
            url=pro.getProperty("url");
            user=pro.getProperty("user");
            password=pro.getProperty("password");
            driver=pro.getProperty("driver");
            //4.注册驱动

            Class.forName(driver);
        }
        catch (IOException e) {
            e.printStackTrace(); }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    //return 返回连接对象
    public static Connection getConnection()throws Exception{
        return DriverManager.getConnection(url, user, password);
    }
    public static void  close(ResultSet rs, Statement stmt, Connection conn){
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    public static void  close(Statement stmt, Connection conn,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}