JDBC增删查改

【用Java实现的JDBC的增删查改】

JDBC的增删查改登陆

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序，同时，JDBC也是个商标名。

增删查改

// TbuserDAO.java
import util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class TbuserDAO {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    public List<Tbuser> findAll() {
        List<Tbuser> users = new ArrayList<>();
        try {
            connection = DButil.getConnection(DButil.qudong, DButil.url);
//                    Class.forName("com.mysql.jdbc.Driver");
//                    //2.创建连接
//                    connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ningda?useSSL=true&characterEncoding=utf-8&user=root&password=123456"
//                    );
            //3.写sql

            String sql = "select * from tb_user";
            //4.得到statement对象
            preparedStatement = connection.prepareStatement(sql);
            //5.执行sql
            resultSet = preparedStatement.executeQuery();
            //6.处理结果集
            while (resultSet.next()) {
                Tbuser user = new Tbuser();
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
                user.setPassword(resultSet.getString(3));
                users.add(user);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        //7.关闭资源
        finally {
            DButil.closeAll(resultSet, preparedStatement, connection);
        }
        return users;
    }
    public void update(String username,String password,int id){
        try {
        connection=DButil.getConnection(DButil.qudong,DButil.url);
        String sql2 = "update tb_user set username=?,password=? where id=?";
        preparedStatement = connection.prepareStatement(sql2);
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);
        preparedStatement.setInt(3,id);
        preparedStatement.executeUpdate();
        System.out.println("修改成功");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DButil.closeAll(null,preparedStatement,connection);
    }
    }
    public void insert(String username,String password){
        try {
            connection=DButil.getConnection(DButil.qudong,DButil.url);
//            System.out.print("请输入要插入的信息");
//            System.out.print("\n");
//            Scanner xm = new Scanner(System.in);
//            System.out.print("请输入添加的名字");
//            System.out.print("\n");
//            String a = xm.nextLine();
//            Scanner mm = new Scanner(System.in);
//            System.out.print("请输入密码");
//            System.out.print("\n");
//            String b = mm.nextLine();
            String sql1 = "insert into tb_user(username,password) values(?,?)";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            preparedStatement.executeUpdate();
            System.out.println("添加成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DButil.closeAll(null,preparedStatement,connection);
        }
    }
    public List<Tbuser> search(String username){
        List<Tbuser> users = new ArrayList<>();
        try {
            connection=DButil.getConnection(DButil.qudong,DButil.url);
            String sql = "select * from tb_user where username like ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"%"+username+"%");
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()) {
                Tbuser user = new Tbuser();
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
                user.setPassword(resultSet.getString(3));
                users.add(user);
            }
            System.out.println("查找成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DButil.closeAll(resultSet,preparedStatement,connection);
        }
        return users;
    }
    public void delete(int id){
        try {
            connection=DButil.getConnection(DButil.qudong,DButil.url);
            String sql3 = "delete  from tb_user where id=?";
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setInt(1,id);
            preparedStatement.executeUpdate();
            System.out.println("删除成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DButil.closeAll(null,preparedStatement,connection);
        }
    }
    public boolean doLogin(String username,String password)throws SQLException{
        try {
            connection = DButil.getConnection(DButil.qudong, DButil.url);
            String sql = "select * from tb_user where username=? and password=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DButil.closeAll(resultSet, preparedStatement, connection);
        }
            return false;
    }
}

// DButil.java
package util;
import java.sql.*;
public class DButil {
    public final static String qudong="com.mysql.jdbc.Driver";
    public final static String  url="jdbc:mysql://127.0.0.1:3306/ningda?useSSL=true&characterEncoding=utf-8&user=root&password=123456";
    public static Connection getConnection(String qudong,String url) throws Exception{
        Class.forName(qudong);
        //2.创建连接
        Connection connection = DriverManager.getConnection(url);
        return connection;
    }
    public static  void closeAll(ResultSet resultSet, PreparedStatement preparedStatement,Connection connection){
        try {
            if (resultSet!=null){
            resultSet.close();}
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (preparedStatement!=null){
            preparedStatement.close();}
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (connection!=null){
            connection.close();}
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

// Tbuser.java
public class Tbuser {
    private int id;
    private String username;
    private String password;
    public int getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

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

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

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

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

}

// TbuserService.java
import java.awt.*;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.util.List;
import java.util.Scanner;
public class TbuserService {
    public static void main(String[] args) throws SQLException {

        boolean k=true;
        while(k) {
            Scanner scanner = new Scanner(System.in);
            System.out.print("----------------------欢迎登入音乐系统------------------------");
            System.out.print("\n");
            System.out.print("------------------------请输入用户名--------------------------");
            System.out.print("\n");
            String username = scanner.next();
            System.out.print("-------------------------请输入密码---------------------------");
            System.out.print("\n");
            String password = scanner.next();
            TbuserDAO usedao = new TbuserDAO();
            boolean flag = usedao.doLogin(username, password);
            if (flag) {
                System.out.print("Welcome!");
                System.out.print("\n");
                System.out.print("------------------1.音乐管理2.用户管理3.退出------------------");
                System.out.print("\n");
                int a=scanner.nextInt();
                if (a==1){
                    boolean m=true;


                }
                if (a==2) {
                    boolean n = true;
                    while (n) {
                        System.out.print("---1.显示用户2.用户修改3.用户增加4.用户删除5.查找用户6.退出---");
                        System.out.print("\n");
                        int b = scanner.nextInt();
                        if (b == 1) {
                            TbuserDAO text = new TbuserDAO();
                            List<Tbuser> users = text.findAll();
                            System.out.print(users);
                            System.out.print("\n");
                        }
                        if (b == 2) {
                            System.out.print("------------------请输入要修改的信息的id:--------------------");
                            System.out.print("\n");
                            int id=scanner.nextInt();
                            System.out.print("------------------请输入要修改的用户姓名：-------------------");
                            System.out.print("\n");
                            String username1=scanner.next();
                            System.out.print("--------------------请输入要修改的密码:----------------------");
                            System.out.print("\n");
                            String password1=scanner.next();
                            usedao.update(username1,password1,id);
                        }
                        if (b == 3) {
                            System.out.print("------------------请输入要增加的用户姓名：-------------------");
                            System.out.print("\n");
                            String username1=scanner.next();
                            System.out.print("-------------------请输入要增加的密码:----------------------");
                            System.out.print("\n");
                            String password1=scanner.next();
                            usedao.insert(username1,password1);

                        }
                        if (b == 4) {
                            System.out.print("--------------------请输入要删除的id:-----------------------");
                            System.out.print("\n");
                            int id=scanner.nextInt();
                            usedao.delete(id);
                        }
                        if (b == 5) {
                            System.out.print("---------------请输入要查找的姓名或者关键字:-----------------");
                            System.out.print("\n");
                            String username2=scanner.next();
                            TbuserDAO text = new TbuserDAO();
                            List<Tbuser> users = text.search(username2);
                            System.out.print(users);
                            System.out.print("\n");
                        }
                        if (b == 6){
                            k = false;
                            System.exit(-1);
                        }
                    }
                }
            } else {
                System.out.print("----------------------Sorry,try again!------------------------");
                System.out.print("\n");
            }
//            k=false;
        }
    }
}

该代码显示了用户登陆和用户的增删查改，以上若有不足的地方希望大家谅解