【无标题】

在图书信息表的基础上实现对图书信息的增删改查操作。
1.创建一个管理员信息表
2.根据管理员信息表实现登录功能
3.登录成功后跳转到循环菜单,通过功能菜单选项完成对book图书表的增删改查操作:
图书编号 图书名称 价格 作者 出版社 出版日期 数量
(1)查询所有图书
(2)根据图书编号查询
(3)添加新图书
(4)根据编号修改图书信息
(5)根据编号删除图书

package com.coffcn;

import com.coffcn.util.JDBCUtils;
import com.sun.org.apache.bcel.internal.generic.NEW;
import jdk.nashorn.internal.scripts.JD;

import javax.xml.transform.Result;
import java.sql.*;
import java.util.Scanner;

//2.根据管理员信息表实现登录功能
public class login {
   private static Scanner sc = new Scanner(System.in);
    public static void main(String[] args) throws SQLException {
        JDBCUtils utils = new JDBCUtils();
        Connection conn = utils.getConn();

        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        String sql="select * from libadmin where username=? and pws=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        //给占位符赋值
        stmt.setString(1,username);
        stmt.setString(2,password);
        //执行sql
        ResultSet rs=stmt.executeQuery();
        //判断是否有值
        if (rs.next()){
            System.out.println("登录成功");
            libEnum();
        }else{
            System.out.println("登录失败");
        }
    }
    //0.退出系统
    private static void exit(){
        System.out.println("正在退出。。。");

        try {
            Thread.sleep(2000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
 //5.删除图书
    private static void deleteBook() {
        //获取连接
        Connection conn = JDBCUtils.getConn();
        PreparedStatement stmt=null;
        System.out.println("请输入要删除的图书编号");
        int id=Integer.parseInt(sc.nextLine());
        System.out.println("是否要删除 Y/N");
        String flag = sc.nextLine();
        try {
//2:获取通道
            stmt = conn.prepareStatement("delete from book where id = ?");
                    stmt.setInt(1,id);
//3:执行sql
            if("y".equalsIgnoreCase(flag)){
                int num = stmt.executeUpdate();
//4:处理结果
                if(num > 0){
                    System.out.println("删除成功");
                }else {
                    System.out.println("删除失败");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(null,stmt,conn);
        }
    }
//4.修改图书信息
    private static void updateBook() {
        //查询需要修改图书的信息
        selectAllBook();
        //获取连接
        Connection conn = JDBCUtils.getConn();
        PreparedStatement stmt=null;
        ResultSet rs=null;
        String sql="update book set bname=?,price=?,author=?,home=?,date=?,number=? where id=?";
        try {
            stmt = conn.prepareStatement(sql);

            System.out.println("请输入图书名称");
            stmt.setString(1,sc.nextLine());

            System.out.println("请输入价格");
            stmt.setDouble(2, Double.parseDouble(sc.nextLine()));

            System.out.println("请输入作者");
            stmt.setString(3,sc.nextLine());

            System.out.println("请输入出版社");
            stmt.setString(4,sc.nextLine());

            System.out.println("请输入出版日期");
            stmt.setDate(5, Date.valueOf(sc.nextLine()));

            System.out.println("请输入数量");
            stmt.setInt(6,sc.nextInt());

            System.out.println("请输入图书编号");
            stmt.setInt(7,sc.nextInt());

            //执行sql
            int num = stmt.executeUpdate();
            if (num>0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(null,stmt,conn);
        }
    }
//3.添加新图书
    private static void addNewBook() {
        //获取连接
        Connection conn = JDBCUtils.getConn();
        PreparedStatement stmt=null;
        //图书编号\t图书名称\t价格\t作者\t出版社\t出版日期\t数量"
        String sql="insert into book values(?,?,?,?,?,?,?)";
        try {
            stmt = conn.prepareStatement(sql);

            System.out.println("请输入图书编号");
            stmt.setInt(1,sc.nextInt());

            System.out.println("请输入图书名称");
            stmt.setString(2,sc.nextLine());



            System.out.println("请输入价格");
            stmt.setDouble(3, Double.parseDouble(sc.nextLine()));

            System.out.println("请输入作者");
            stmt.setString(4,sc.nextLine());

            System.out.println("请输入出版社");
            stmt.setString(5,sc.nextLine());

            System.out.println("请输入出版日期");
            stmt.setDate(6, Date.valueOf(sc.nextLine()));

            System.out.println("请输入数量");
         stmt.setInt(7,sc.nextInt());
         //执行sql
            int num=stmt.executeUpdate();
            if (num>0){
                System.out.println("插入成功");
            }else{
                System.out.println("插入失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(null,stmt,conn);
        }
    }
//2.根据编号查询
    private static void selectOneBook() {
        selectAllBook();
        //1:获取连接
        Connection conn = JDBCUtils.getConn();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        System.out.println("请输入您要查询的编号");
        int id = Integer.parseInt(sc.nextLine());
        try {
            //2:获取通道
            stmt = conn.prepareStatement("select * from book where id=?");
            stmt.setInt(1, id);
            //3:执行sql
            rs = stmt.executeQuery();
           if(rs.next()) {
                int no = rs.getInt(1);
                String bname = rs.getString(2);
                double price = rs.getDouble(3);
                String author = rs.getString(4);
                String home = rs.getString(5);
                Date date = rs.getDate(6);
                int number = rs.getInt(7);
                System.out.println(no + "\t" + bname + "\t" + price + "\t" + author + "\t" + home + "\t" + date + "\t" + number);
            }else{
                System.err.println("查无此书");
                }
            }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(rs, stmt, conn);
        }
    }



   //1.查询所有图书
    private static void selectAllBook() {
        //1.获取连接
        Connection conn = JDBCUtils.getConn();
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            //获取通道
             stmt = conn.prepareStatement("select * from book");
            //执行sql
              rs = stmt.executeQuery();
              //处理结果
            System.out.println("图书编号\t图书名称\t价格\t作者\t出版社\t出版日期\t数量");
            while(rs.next()){
                int id = rs.getInt(1);
                String bname = rs.getString(2);
                double price = rs.getDouble(3);
                String author = rs.getString(4);
                String home = rs.getString(5);
                Date date = rs.getDate(6);
                int number = rs.getInt(7);
                System.out.println(id+"\t"+bname+"\t"+price+"\t"+author+"\t"+home+"\t"+date+"\t"+number);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(rs,stmt,conn);
        }
    }
    public static void libEnum(){
        Scanner sc1 = new Scanner(System.in);
        System.out.println("--------------欢迎来到图书管理系统---------");
        System.out.println("--------------1.查询所有图书--------------");
        System.out.println("--------------2.根据编号查询--------------");
        System.out.println("--------------3.添加新图书----------------");
        System.out.println("--------------4.修改图书信息--------------");
        System.out.println("--------------5.删除图书------------------");
        System.out.println("--------------0.退出系统------------------");
        System.out.println("请选择您需要的操作");
        String i = sc1.nextLine();
        switch (i){
            case "1":selectAllBook();libEnum();break;
            case "2":selectOneBook();libEnum();break;
            case "3":addNewBook();libEnum();break;
            case "4":updateBook();libEnum();break;
            case "5":deleteBook();libEnum();break;
            case "0":exit();libEnum();break;
        }
    }
}

上一篇:CF1406E Deleting Numbers 题解


下一篇:【MOBAN】各种平衡树模板