第一天：JDBC

最新推荐文章于 2023-02-24 16:12:37 发布

原创最新推荐文章于 2023-02-24 16:12:37 发布 · 193 阅读

0 ·

CC 4.0 BY-SA版权

ssm 专栏收录该内容

6 篇文章

订阅专栏

该博客介绍了在Java和MySQL间运用JDBC实现增删改查的过程。包括运行环境（64位Windows、MySQL 5.5.58、Java 1.8等），数据库搭建，工程文件创建，给出具体代码，展示运行结果，最后总结通过学习运用JDBC搭建了Java与MySQL的桥梁。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

学习和使用JDBC实现用户管理系统的简单的增删改查

运行环境
数据库搭建
工程文件创建
具体代码
运行结果
总结

运行环境

64位的Windows系统
Server version: 5.5.58 MySQL Community Server (GPL)
java version “1.8.0_211”
SQLyog
idea

数据库搭建

使用SQLyog，创建数据库java7，在数据库中添加表tb_user，具体设置如下：
在这里插入图片描述

工程文件创建

在创建项目时，勾选中Java EE中的Web Application选项，在项目工程文件目录中的web文件夹中的WEB-INF文件夹中创建文件夹lib，并将实现准备好的mysql-connector-java-5.0.8-bin.jar包导入到lib文件夹中，再在项目工程目录中右击该文件，选中菜单中的Add as Library…选项。
在这里插入图片描述

具体代码

DBUtil.java

package com.zhongruan;

import java.sql.*;

public class DBUtil {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/java7?useSSL=true&characterEncoding=utf-8";
    private static String user = "root";
    private static String password = "123";

    //1.获取驱动
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //2.创建连接
    public static Connection get_Conn() throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);
        //System.out.println("数据库连接成功" + conn);
        return conn;
    }

    //3.关闭连接
    public static void get_CloseConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws SQLException {
        if(rs != null){
            rs.close();
        }
        if(pstm != null){
            pstm.close();
        }
        if(conn != null){
            conn.close();
        }
    }

    //测试
//    public static void main(String[] args) {
//        try {
//            get_Conn();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }
}

User.java

package com.zhongruan;
//实体类   -   映射数据库的字段
public class User {
    //1.对属性的封装
    private int id;
    private String username;
    private String password;

    //2.get and set
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

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

    //3.构造方法
    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public User(int id, String username) {
        this.id = id;
        this.username = username;
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public User(int id) {
        this.id = id;
    }

    public User() {
    }

    //4.toString方法的重写
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

TestUser2.java

package com.zhongruan;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class TestUser2 {
    Connection conn = null;
    PreparedStatement pstm = null;
    ResultSet rs = null;
    public static int num = -1;

    //打印菜单
    public void showMenu(){
        System.out.println("-------------------");
        System.out.println("\t1.增加     ");
        System.out.println("\t2.删除     ");
        System.out.println("\t3.修改用户名     ");
        System.out.println("\t4.修改用户密码    ");
        System.out.println("\t5.查询     ");
        System.out.println("\t6.打印所有信息"  );
        System.out.println("\t0.退出     ");
        System.out.println("-------------------");
        System.out.print("\t输入：");
    }

    //功能选择
    public void selectFunction(){
        switch(num){
            case 1:
                addUser();break;
            case 2:
                delUser();break;
            case 3:
                modifyUsername();break;
            case 4:
                modifyPassword();break;
            case 5:
                queryUser();break;
            case 6:
                allUser();break;
            case 0:
                exitProgram();break;
            default:
                errorInput();

        }
    }

    //增加用户
    public void addUser(){
        try {
            //1.获取连接
            conn = DBUtil.get_Conn();

            //2.获取存放sql语句的对象
            pstm = conn.prepareStatement("insert into tb_user(username, password) values(?, ?)");

            //3.获取并填入需要增加的记录信息
            String username, password;
            Scanner Sc = new Scanner(System.in);
            System.out.print("请输入用户名：");
            username = Sc.nextLine();
            System.out.print("请输入密码：");
            password = Sc.nextLine();
            User user = new User(username,password);

            pstm.setString(1, user.getUsername());
            pstm.setString(2, user.getPassword());

            //4.执行sql语句并得到结果
            int i = pstm.executeUpdate();
            //5.判断是否添加成功
            if(i > 0){
                System.out.println("添加成功");
            }else{
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.关闭连接
            try {
                DBUtil.get_CloseConn(null, pstm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //删除用户
    public void delUser(){
        try {
            //1.获取连接
            conn = DBUtil.get_Conn();

            //2.获取存放sql语句的对象
            pstm = conn.prepareStatement("DELETE FROM tb_user WHERE id = ?");

            //3.获取并填入需要删除的记录信息
            int id;
            Scanner Sc = new Scanner(System.in);
            System.out.print("请输入用户ID：");
            id = Sc.nextInt();
            User user = new User(id);

            pstm.setInt(1,user.getId());

            //4.执行sql语句并得到结果
            int i = pstm.executeUpdate();
            //5.判断是否添加成功
            if(i > 0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.关闭连接
            try {
                DBUtil.get_CloseConn(null, pstm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //修改用户名
    public void modifyUsername(){
        try {
            //1.获取连接
            conn = DBUtil.get_Conn();

            //2.获取存放sql语句的对象
            pstm = conn.prepareStatement("update tb_user set username = ? where id = ?");

            //3.获取并填入需要修改的记录信息
            int id;
            String username;
            Scanner Sc = new Scanner(System.in);
            System.out.print("请输入用户ID：");
            id = Sc.nextInt();
            System.out.print("请输入新的用户名：");
            username = Sc.next();
            User user = new User(id, username);

            pstm.setString(1, user.getUsername());
            pstm.setInt(2,user.getId());

            //4.执行sql语句并得到结果
            int i = pstm.executeUpdate();
            //5.判断是否修改成功
            if(i > 0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.关闭连接
            try {
                DBUtil.get_CloseConn(null, pstm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //修改用户密码
    public void modifyPassword(){
        try {
            //1.获取连接
            conn = DBUtil.get_Conn();

            //2.获取存放sql语句的对象
            pstm = conn.prepareStatement("update tb_user set password = ? where id = ?");

            //3.获取并填入需要修改的记录信息
            int id;
            String password;
            Scanner Sc = new Scanner(System.in);
            System.out.print("请输入用户ID：");
            id = Sc.nextInt();
            System.out.print("请输入新的用户密码：");
            password = Sc.next();
            User user = new User(id);
            user.setPassword(password);

            pstm.setString(1, user.getPassword());
            pstm.setInt(2,user.getId());

            //4.执行sql语句并得到结果
            int i = pstm.executeUpdate();
            //5.判断是否修改成功
            if(i > 0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.关闭连接
            try {
                DBUtil.get_CloseConn(null, pstm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //查询用户
    public void queryUser(){
        try {
            //1.获取连接
            conn = DBUtil.get_Conn();

            //2.获取存放sql语句的对象
            pstm = conn.prepareStatement("select * from tb_user where username = ?");

            //3.获取并填入需要查询的记录信息的username
            String username;
            Scanner Sc = new Scanner(System.in);
            System.out.print("请输入用户名：");
            username = Sc.nextLine();
            User user = new User();
            user.setUsername(username);

            //4.执行sql语句并得到结果
            pstm.setString(1, user.getUsername());
            rs = pstm.executeQuery();

            //5.遍历结果
            System.out.println("------查询结果如下------");
            while(rs.next()){
                System.out.println("用户ID：" + rs.getInt(1));
                System.out.println("用户名：" + rs.getString(2));
                System.out.println("用户密码：" + rs.getString(3));
                System.out.println("-----------------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.关闭连接
            try {
                DBUtil.get_CloseConn(rs, pstm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //打印所有用户
    public void allUser(){
        try {
            //1.获取连接
            conn = DBUtil.get_Conn();

            //2.获取存放sql语句的对象
            pstm = conn.prepareStatement("select * from tb_user");

            //3.执行sql语句并得到结果
            rs = pstm.executeQuery();

            //4.遍历结果
            System.out.println("------查询结果如下------");
            while(rs.next()){
                System.out.println("用户ID：" + rs.getInt(1));
                System.out.println("用户名：" + rs.getString(2));
                System.out.println("用户密码：" + rs.getString(3));
                System.out.println("-----------------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.关闭连接
            try {
                DBUtil.get_CloseConn(null, pstm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //退出程序
    public void exitProgram(){
        System.out.println("\t已退出");
    }

    //输入错误
    public void errorInput(){
        System.out.println("输入错误，请重新输入");
    }

    public static void main(String[] args) {
        TestUser2 temp = new TestUser2();
        do {
            temp.showMenu();
            Scanner Sc = new Scanner(System.in);
            num = Sc.nextInt();
            temp.selectFunction();
        }while(num != 0);
    }
}