使用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下载即可

解压后得到可以得到jar包

法一：

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

法二：

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

代码：

1. 先加载驱动程序

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

2. 连接数据库todolist

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

select：

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

String sql = "select * from task";

2. 定义PreparedStatement用于执行SQL语句

PreparedStatement preparedStatement = conn.prepareStatement(sql);

3. 使用ResultSet接收select得到的结果

ResultSet resultSet = preparedStatement.executeQuery();

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

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;     } }    }}