idea jdbc

এ᭄༊空旧黎梦ღꦿ࿐

已于 2022-11-05 14:25:00 修改

阅读量76

点赞数

文章标签： intellij-idea java 数据库

于 2022-10-30 13:19:06 首次发布

本文链接：https://blog.csdn.net/weixin_70675726/article/details/127598394

版权

创建MYSQL数据库新建查询

写入MYSQl代码

#设置数据的视图---使用数据库
use mydb;
#判断表存在就删除表
drop table if exists student;
#创建表
create table student
(stuId int primary key auto_increment,
stuName varchar(20),
stuSex varchar(2),
stuAge int,
stuAddr varchar(50))

#插入测试数据
insert into student(stuName,stuSex,stuAge,stuAddr) values('张三','男',20,'河南');
insert into student(stuName,stuSex,stuAge,stuAddr) values('小美','女',18,'山东');
insert into student(stuName,stuSex,stuAge,stuAddr) values('Rose','女',19,'美国');
insert into student(stuName,stuSex,stuAge,stuAddr) values('Jack','男',21,'英国');
#查询数据表
select * from student;

打开idea创建项目导入jar包

最好是与mysql版本一致

当出现为成功

链接数据库

首先确定数据库打开状态

其次配置链接参数

databaseName 指定的数据库名字

UTC现在的时间

MySQL 5.0版本：
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/databaseName?useSSL=false&serverTimezone=UTC
username=root
password=123

MySQL 8.0版本：
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/databaseName?useSSL=false&serverTimezone=UTC
username=root
password=123

创建数据库对象

代码

package com.hp.bean;

public class Book {
    //属性
    private int bookId;
    private String bookName;
    private String bookAuthor;
    private int bookNum;
    private double bookPrice;
    private String introduce;

    public Book() {
    }
//构造方法
    public Book(int bookId, String bookName, String bookAuthor, int bookNum, double bookPrice, String introduce) {
        this.bookId = bookId;
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
        this.bookNum = bookNum;
        this.bookPrice = bookPrice;
        this.introduce = introduce;
    }
//getter和setter方法
    public int getBookId() {
        return bookId;
    }

    public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }

    public int getBookNum() {
        return bookNum;
    }


    public double getBookPrice() {
        return bookPrice;
    }

    public void setBookPrice(double bookPrice) {
        this.bookPrice = bookPrice;
    }

    public String getIntroduce() {
        return introduce;
    }

    public void setIntroduce(String introduce) {
        this.introduce = introduce;
    }
//tostring方法
    @Override
    public String toString() {
        return "Book{" +
                "bookId=" + bookId +
                ", bookName='" + bookName + '\'' +
                ", bookAuthor='" + bookAuthor + '\'' +
                ", bookNum=" + bookNum +
                ", bookPrice=" + bookPrice +
                ", introduce='" + introduce + '\'' +
                '}';
    }
    //重写setter方法
    public void setBookNum(int bookNum){
        if(bookNum>0&&bookNum<100){
            this.bookNum = bookNum;
        }
    }


}

创建测试程序

注意要单独创建一个包存储测试类

全查

 /**
     * 全查
     */
    @Test
    public  void testshowAll() throws ClassNotFoundException, SQLException {
        //使用反射链接数据库
        Class.forName(driver);
        Connection con= DriverManager.getConnection(url,username,password);
        //创建sql数据库
        String sql="select * from books";
        //执行sql语句
        PreparedStatement pr=con.prepareStatement(sql);
        //数据库响应的查询结果存放在ResultSet
        ResultSet rs=pr.executeQuery();
        //创建list面向接口
        List<Book> list=new ArrayList<>();
        //循环遍历数据 next下一行(第一行不执行)
        while (rs.next()){
            //创建新的空间来进行存值
            Book book=new Book();
            //get:根据id获取 set:存入id获取的值
            book.setBookId(rs.getInt("bookId"));
            book.setBookName(rs.getString("bookName"));
            book.setBookAuthor(rs.getString("bookAuthor"));
            book.setBookNum(rs.getInt("bookNum"));
            book.setBookPrice(rs.getDouble("bookPrice"));
            book.setIntroduce(rs.getString("introduce"));
            //list.add在list接口中存值
            list.add(book);
        }
        //输出list接口
        System.out.println(list);
        //7.资源的释放
        if(rs!=null){
            rs.close();
        }
        if(pr!=null){
            pr.close();
        }
        if(con!=null){
            con.close();
        }
    }

根据书籍查询信息

 /**
     * 根据书籍名称查询信息*/
    public Book find(String bookName) throws ClassNotFoundException, SQLException {
        //定义一个空值如果无值则返回空值 有值则输出目标值
    Book book = null;
    //使用反射链接数据库
    Class.forName(driver);
    Connection con = DriverManager.getConnection(url, username, password);
    //编写条件查询语句
    String sql = "select * from books where bookName=?";
    //执行sql语句
    PreparedStatement pr = con.prepareStatement(sql);
    //第一个问号的值
    pr.setObject(1, bookName);
    //数据库响应的查询结果存放在ResultSet
    ResultSet rs = pr.executeQuery();
    //rs.next() 第一组数据不执行
    if (rs.next()) {
        book = new Book();
        //set 存值 get获取值
        book.setBookId(rs.getInt("bookId"));
        book.setBookName(rs.getString("bookName"));
        book.setBookAuthor(rs.getString("bookAuthor"));
        book.setBookNum(rs.getInt("bookNum"));
        book.setBookPrice(rs.getDouble("bookPrice"));
        book.setIntroduce(rs.getString("introduce"));
    }
    //7.资源的释放
    if (rs != null) {
        rs.close();
    }
    if (pr != null) {
        pr.close();
    }
    if (con != null) {
        con.close();
    }
    //返回结果集
    return book;
}
    @Test
    public void testFind() throws SQLException, ClassNotFoundException {
        //创建Scanner控制台
        Scanner sc=new Scanner(System.in);
        //提示用户输入
        System.out.println("(全名称)请输入你需要查询的书名");
        //获取用户输入的值并返回结果 正确则输出 无结果则返回null
        Book num=find(sc.next());
        //执行判断语句
        if (num==null){
        //结果为null 则输出
        System.out.println("查询不到此书名");
        }else {
        //结果正确 则输出
        System.out.println(num);
    }
}

模糊查询

 /**
     * 模糊查询
     */
    public List<Book> FindKwyword(String keyword) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, username, password);
        String sql = "select * from books where bookName like ?";
        PreparedStatement psm = con.prepareStatement(sql);
        psm.setObject(1, "%" + keyword + "%");
        ResultSet rs = psm.executeQuery();
        List<Book> booksList = new ArrayList<>();
        while (rs.next()) {
            Book books = new Book();
            books.setBookId(rs.getInt("bookId"));
            books.setBookName(rs.getString("bookName"));
            books.setBookAuthor(rs.getString("bookAuthor"));
            books.setBookNum(rs.getInt("bookNum"));
            books.setBookPrice(rs.getInt("bookPrice"));
            books.setIntroduce(rs.getString("introduce"));
            booksList.add(books);
        }
        System.out.println(booksList);
        if (rs != null) {
            rs.close();
        }
        if (psm != null) {
            psm.close();
        }
        if (con != null) {
            con.close();
        }
        return booksList;
    }
    @Test
    public void testFindKwyword() throws SQLException, ClassNotFoundException {
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入你需要模糊查询的名字(模糊查询)");
        FindKwyword(sc.next());
    }

删除

 /**
     * 删除
     */
    public boolean delete(int bookId) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, username, password);
        String sql = "delete from books where bookId =?";
        PreparedStatement psm = con.prepareStatement(sql);
        psm.setObject(1, bookId);
        int n = psm.executeUpdate();
        if (n > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
        if (psm != null) {
            psm.close();
        }
        if (con != null) {
            con.close();
        }

        return false;
    }
    @Test
    public  void testDelect() throws SQLException, ClassNotFoundException {
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入你需要删除的ID(删除)");
        delete(sc.nextInt());
    };

添加

 /**
 * 添加
 */
    public boolean add(Book book) throws ClassNotFoundException, SQLException {
    Class.forName(driver);
    Connection con = DriverManager.getConnection(url, username, password);
    String sql = "insert into books(bookName,bookAuthor,bookNum,bookPrice,introduce) values(?,?,?,?,?)";
    PreparedStatement psm = con.prepareStatement(sql);
    psm.setObject(1, book.getBookName());
    psm.setObject(2, book.getBookAuthor());
    psm.setObject(3, book.getBookNum());
    psm.setObject(4, book.getBookPrice());
    psm.setObject(5, book.getIntroduce());
    int n = psm.executeUpdate();
    if(n>0){
        System.out.println("插入数据成功");
    }else{
        System.out.println("插入数据失败");
    }
    //7释放资源
    if(psm!=null){
        psm.close();
    }
    if(con!=null){
        con.close(); }
    return false;
}
    @Test
    public void testadd() throws SQLException, ClassNotFoundException {
    Book book=new Book();
    Scanner sc=new Scanner(System.in);
    System.out.println("请输入书名(添加)");
    book.setBookName(sc.next());
    System.out.println("请输入作者");
    book.setBookAuthor(sc.next());
    System.out.println("请输入数量");
    book.setBookNum(sc.nextInt());
    System.out.println("请输入价格");
    book.setBookPrice(sc.nextDouble());
    System.out.println("请输入图书简介");
    book.setIntroduce(sc.next());
    add(book);
}

修改

/**
 * 修改
 */
public Boolean changePrice(String bookName,double bookprice) throws ClassNotFoundException, SQLException {
    //使用反射链接数据库
    Class.forName(driver);
    Connection con=DriverManager.getConnection(url,username,password);
    //编写修改语句
    String sql="update books set bookprice=? where bookName=?";
    //执行sql语句
    PreparedStatement pr=con.prepareStatement(sql);
    pr.setObject(1,bookprice);
    pr.setObject(2,bookName);
    int n = pr.executeUpdate();
    if (n > 0) {
        System.out.println("修改成功");
    } else {
        System.out.println("修改失败");
    }
    return false;
}
@Test
public void testChangePrice() throws SQLException, ClassNotFoundException {
    Scanner sc=new Scanner(System.in);
    System.out.println("请输入图书名称");
    String name=sc.next();
    Book book=find(name);
    System.out.println("修改前"+book);
    System.out.println("请输入修改的图书价格");
    Double price =sc.nextDouble();
    changePrice(name, price);
    System.out.println("修改后"+find(name));

};