使用JDBC完成一个简单的todolist数据库管理

wbl_z

已于 2022-04-13 22:22:46 修改

阅读量958

点赞数 3

分类专栏： java 文章标签： mysql java

于 2022-04-13 22:22:24 首次发布

本文链接：https://blog.csdn.net/m0_51691879/article/details/124159508

版权

java 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

使用JDBC完成一个简单的todolist数据库管理

目标：使用JDBC完成对todolist数据库的管理

todolist数据库

|	id	|	title	|	is_completed	|

create database todolist;
use todolist;
create table `task`(
	`id` int not null primary key auto_increment,
	`title` varchar(100) not null,
	`is_completed` int not null
);

前期准备

从MYSQL官网中下载了mysql-connect的jar包
https://downloads.mysql.com/archives/c-j/
在Operating System中选择Platform Independent，选择第二行的ZIP Archive下载即可
mysql
解压后得到可以得到jar包
在这里插入图片描述

法一：

在idea里面选择项目结构，点击加号，选择下载好的jar包导入即可
在这里插入图片描述

法二：

另外一种方式可以直接在项目的根目录下创建一个lib文件夹，然后将jar包放进去，再右键把lib文件夹标记成库即可

代码：

先加载驱动程序

try {
    Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

连接数据库todolist

Connection connection = null;
try {
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/todolist", "root", "root");
} catch (SQLException e) {
    e.printStackTrace();
}

select：

按照教程，定义一个SQL语句

String sql = "select * from task";

定义PreparedStatement用于执行SQL语句

PreparedStatement preparedStatement = conn.prepareStatement(sql);

使用ResultSet接收select得到的结果

ResultSet resultSet = preparedStatement.executeQuery();

遍历所有的元组，将每个元组输出

while (resultSet.next()) {
    System.out.println("id:"+resultSet.getString("id")+"\ttitle："+resultSet.getString("title")+
            "\tis_completed："+resultSet.getString("is_completed"));
}

update：

流程和select一样，但是不需要用ResultSet接收结果，并且使用preparedStatement.executeUpdata来执行SQL

public void update(Connection connection) throws SQLException {
    String id = null;
    String title = null;
    String isCompleted = null;
    try{
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(System.in));
        System.out.println("要修改的任务id");
        id = bufferedReader.readLine();
        System.out.println("新的title");
        title = bufferedReader.readLine();
        System.out.println("新的is_completed");
        isCompleted = bufferedReader.readLine();
    }catch(IOException e){
        e.printStackTrace();
    }
    String sql = "update task set `title` = ?,`is_completed`= ? where id = ?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, title);
    preparedStatement.setInt(2, Integer.parseInt(isCompleted));
    preparedStatement.setInt(3, Integer.parseInt(id));
    int flag = preparedStatement.executeUpdate();
    if (flag == 1) {
        System.out.println("更新成功");
    }
    else {
        System.out.println("更新失败");
    }
    try {// 释放preparedStatement
        if(preparedStatement != null) {
            preparedStatement.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
	}
}

delete：类似update

public void delete(Connection connection) throws SQLException {
    String id = null;
    try {
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(System.in));
        System.out.println("id");

        id = bufferedReader.readLine();
    }catch (IOException e){
        e.printStackTrace();
    }
    String sql = "delete from task where `id` = ?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1,id);
    int flag = preparedStatement.executeUpdate();
    if(flag == 1){
        System.out.println("删除成功");
    }
    else {
        System.out.println("删除失败");
    }
    try {// 释放preparedStatement
        if(preparedStatement != null) {
            preparedStatement.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
	}
}

insert：

public void insert(Connection connection) throws SQLException {
    try {
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(System.in));
        System.out.println("title");
        title = bufferedReader.readLine();
        System.out.println("is_completed");
        isCompleted = bufferedReader.readLine();
    }catch (IOException e){
        e.printStackTrace();
    }
    String sql = "insert into task(`title`,`is_completed`) values(?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1,title);
    preparedStatement.setInt(2,Integer.valueOf(isCompleted));
    int flag = preparedStatement.executeUpdate();
    if(flag == 1){
        System.out.println("添加成功");
    }
    else {
        System.out.println("添加失败");
    }
    try {// 释放preparedStatement
        if(preparedStatement != null) {
            preparedStatement.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
	}
}

close：

在结束后将连接关闭

try {
    if(connection != null) {
        connection.close();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

try {
    if(preparedStatement != null) {
        preparedStatement.close();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

try {
    if(resultSet != null) {
        resultSet.close();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

完整代码

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.Scanner;

/**
 * @author wbl
 */
public class Todolist {
    public void select(Connection connection) throws SQLException {
        System.out.println("todolist");
        String sql = "select * from task";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.println("id:"+resultSet.getString("id")+"\ttitle："+resultSet.getString("title")+
                    "\tis_completed："+resultSet.getString("is_completed"));
        }
        try {
            if(preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insert(Connection connection) throws SQLException {
        String title = null, isCompleted = null;
        try {
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("title");
            title = bufferedReader.readLine();
            System.out.println("is_completed");
            isCompleted = bufferedReader.readLine();
        }catch (IOException e){
            e.printStackTrace();
        }
        String sql = "insert into task(`title`,`is_completed`) values(?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,title);
        preparedStatement.setInt(2,Integer.valueOf(isCompleted));
        int flag = preparedStatement.executeUpdate();
        if(flag == 1){
            System.out.println("添加成功");
        }
        else {
            System.out.println("添加失败");
        }
        try {// 释放preparedStatement
            if(preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(Connection connection) throws SQLException {
        String id = null;
        try {
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("id");

            id = bufferedReader.readLine();
        }catch (IOException e){
            e.printStackTrace();
        }
        String sql = "delete from task where `id` = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,id);
        int flag = preparedStatement.executeUpdate();
        if(flag == 1){
            System.out.println("删除成功");
        }
        else {
            System.out.println("删除失败");
        }
        try {// 释放preparedStatement
            if(preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void update(Connection connection) throws SQLException {
        String id = null;
        String title = null;
        String isCompleted = null;
        try{
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("要修改的任务id");
            id = bufferedReader.readLine();
            System.out.println("新的title");
            title = bufferedReader.readLine();
            System.out.println("新的is_completed");
            isCompleted = bufferedReader.readLine();
        }catch(IOException e){
            e.printStackTrace();
        }
        String sql = "update task set `title` = ?,`is_completed`= ? where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, title);
        preparedStatement.setInt(2, Integer.parseInt(isCompleted));
        preparedStatement.setInt(3, Integer.parseInt(id));
        int flag = preparedStatement.executeUpdate();
        if (flag == 1) {
            System.out.println("更新成功");
        }
        else {
            System.out.println("更新失败");
        }
        try {// 释放preparedStatement
            if(preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Connection prepareJDBC(){
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            Connection connection = null;
            try {
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/todolist", "root", "mysql");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        return connection;
    }

    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("Welcome to Todolist");
        System.out.println("0:退出");
        System.out.println("1:查询列表");
        System.out.println("2:新增表项");
        System.out.println("3:更新表项");
        System.out.println("4:删除表项");
        Todolist todolist = new Todolist();
        Connection connection = todolist.prepareJDBC();
        while (true){
            int i = scanner.nextInt();
            switch (i){
                case 0: {
                    try {// 关闭连接
                        if(connection != null) {
                            connection.close();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    return;
                }
                case 1:todolist.select(connection);break;
                case 2:todolist.insert(connection);break;
                case 3:todolist.update(connection);break;
                case 4:todolist.delete(connection);break;
                default:break;
            }
        }

    }

}