C/C++教程

连接数据库的图书管理系统 包含简单的增删改查模糊查询以及用户登陆 大一有图书管理系统的课设在使用数据库以及JDBC编程的情况下只需要看最后一张图。

本文主要是介绍连接数据库的图书管理系统 包含简单的增删改查模糊查询以及用户登陆 大一有图书管理系统的课设在使用数据库以及JDBC编程的情况下只需要看最后一张图。,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

 

一、准备工作。

1.创建两张表,一张图书信息表,一张用户账号密码表。

 

 相关元素自己定义即可,主键自增也是选设

2.大致布局如下

 dbconfig工具类

driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8
username = root
password = 123456

图书信息User类,在这我习惯性取名为User类,在编写的时候可以改为Book类

package com.hqyj.cl.jdbc.book;

public class User {
    private String name;
    private String author;
    private Double price;
    private String country;


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    @Override
    public String toString() {
        return "  \n书名:" + name + "  作者:"+author+"   价格:"+price+"  国家:"+country+"\t";

    }
}

功能方法接口类

package com.hqyj.cl.jdbc.book;

import java.sql.SQLException;
import java.util.List;

public interface UserDaoBook {
    int addUser(String bookName,String author,Double price,String country);
    int revisePrice(String name,Double price);
    int delete(String name);
    List<User> dimQuery(String name);
    List<User> queryAll();
    Reader login(String user,String password);

}

功能实现类  依次为外加登入系统

 

package com.hqyj.cl.jdbc.book;

import com.hqyj.cl.jdbc.utils.JDBCUtils;

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 UserDaoBookImpl implements UserDaoBook {
    Connection con = null;//连接对象
    PreparedStatement pre = null;//语句对象
    ResultSet re = null;//结果集对象


    @Override
    public int addUser(String name, String author, Double price, String country) {
        try {
            con = JDBCUtils.getConnection();
            String sql = "INSERT INTO `book` VALUES(null,?,?,?,?);";
            pre = con.prepareStatement(sql);
            pre.setString(1,name);
            pre.setString(2,author);
            pre.setDouble(3,price);
            pre.setString(4,country);
            return pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeAll(re,pre,con);
            System.out.println("添加成功\n");
        }
        return 0;
    }



    @Override
    public int revisePrice(String name,Double price) {
        try {
            con = JDBCUtils.getConnection();
            String sql = "UPDATE `book` set `price` = ? WHERE `name` =?";
            pre = con.prepareStatement(sql);
            pre.setDouble(1,price);
            pre.setString(2,name);
            return pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeAll(re,pre,con);
            System.out.println("书名:"+name+"  价格已修改为"+price+"\n");
        }
        return 0;
    }

    @Override
    public int delete(String name) {
        try {
            con = JDBCUtils.getConnection();
            String sql = "delete from book where name = ?;";
            pre = con.prepareStatement(sql);
            pre.setString(1,name);
            return pre.executeUpdate();


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeAll(re,pre,con);
            System.out.println("书名:"+name+"  已删除\n");
        }
        return 0;
    }

    public List<User> dimQuery(String name1){

        List<User> list = new ArrayList<>();
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM `book` WHERE `name` LIKE concat('%',?,'%')";
            pre = con.prepareStatement(sql);
            pre.setString(1,name1);
            re = pre.executeQuery();
                while(re.next()){
                    User user = new User();
                    String name = re.getString("name");
                    user.setName(name);
                    String author = re.getString("author");
                    user.setAuthor(author);
                    Double price = re.getDouble("price");
                    user.setPrice(price);
                    String country = re.getString("country");
                    user.setCountry(country);
                    list.add(user);
                }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeAll(re,pre,con);
        }

        return list;
    }


    public List<User> queryAll(){

        List<User> list = new ArrayList<>();
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM `book`";
            pre = con.prepareStatement(sql);
            re = pre.executeQuery();
            while(re.next()){
                User user = new User();
                String name = re.getString("name");
                user.setName(name);
                String author = re.getString("author");
                user.setAuthor(author);
                Double price = re.getDouble("price");
                user.setPrice(price);
                String country = re.getString("country");
                user.setCountry(country);
                list.add(user);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeAll(re,pre,con);
        }

        return list;
    }

    @Override
    public Reader login(String user1, String password1)  {
        Reader r = new Reader();
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM `user`WHERE `user` = ? AND `password` = ?";
            pre = con.prepareStatement(sql);
            pre.setString(1,user1);
            pre.setString(2,password1);
            re = pre.executeQuery();
            if (re.next()){
                re.previous();
                while (re.next()){
                    String user = re.getString("user");
                    r.setUser(user);
                    String password = re.getString("password");
                    r.setPassword(password);

                }
                return r;
            }else {
                return null;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


}

登入校验

package com.hqyj.cl.jdbc.book;

public class Reader {
    private String user;
    private String password;

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

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

    @Override
    public String toString() {
        return "Reader{" +
                "user='" + user + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

控制台

package com.hqyj.cl.jdbc.book;

import com.hqyj.cl.jdbc.day02.dao.UserDao;
import com.hqyj.cl.jdbc.day02.dao.daoImpl.UserDaoImpl;

import java.util.List;
import java.util.Scanner;

public class UserDaoTest {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        UserDaoBook userDao = new UserDaoBookImpl();
        System.out.println("请输入用户名和密码");
        Reader r = userDao.login(sc.next(),sc.next());
        if(r!=null){
            while (true){
                System.out.println("------选择菜单--------\n-----1.增加图书-----\n-----2.修改图书-----\n-----3.删除图书-----\n-----4.查询图书-----\n-----5.查询图书列表-----\n-----6.退出系统-----");
                int a = sc.nextInt();
                switch(a){
                    case 1:
                        System.out.println("请依次输入 书名 作者 价格 出品国家");
                        userDao.addUser(sc.next(),sc.next(),sc.nextDouble(),sc.next());
                        break;
                    case 2:
                        System.out.println("请输入要修改价格的书名 以及要修改后的价格");
                        userDao.revisePrice(sc.next(),sc.nextDouble());
                        break;
                    case 3:
                        System.out.println("请输入要删除图书的书名");
                        userDao.delete(sc.next());
                        break;
                    case 4:
                        System.out.println("请输入要查询图书书名的关键字");
                        List<User> users = userDao.dimQuery(sc.next());
                        System.out.println(users);
                        break;
                    case 5:
                        System.out.println("所有图书信息如下\n");
                        System.out.println(userDao.queryAll());
                        break;
                    case 6:
                        System.out.println("bye-");
                        System.exit(0);
                        break;
                    default:
                        System.out.println("指令错误,请重新输入");

                }


            }

        }else{
            System.out.println("用户名或者密码错误");
            System.exit(0);
        }





    }
}

这篇关于连接数据库的图书管理系统 包含简单的增删改查模糊查询以及用户登陆 大一有图书管理系统的课设在使用数据库以及JDBC编程的情况下只需要看最后一张图。的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!