手写JDBC：简单的连接MySQL数据库进行“增删改查”(CURD)

我恨我痴心Jack

于 2024-08-25 19:17:23 发布

阅读量239

点赞数 9

文章标签：数据库 mysql java maven junit idea

本文链接：https://blog.csdn.net/m0_70630103/article/details/141532294

版权

大家都会用框架MyBtis,现在来回到我们最原始学习连接MySQL数据库的时候，对数据库里的表数据进行最简单的“增删改查”。

总体分为几个步骤：

1.加载驱动

2.拿到连接

3.编写SQL语句

4.预编译SQL

5.执行SQL

6.拿到结果集

7.处理结果集

8.资源释放

首先，去到Maven仓库找到MySQL的包：快捷链接https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.28

复制里面的坐标：

   <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.28</version>
    </dependency>

再来一个测试的包，方便我们后续进行测试：

坐标在这里

<dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

把这两个坐标导入Pom文件，这样，环境就算搭建完了。

其次是打开MySQL数据库。。。我的是8.0版本，各位看版本选择合适的坐标进行导包！！！

创建实体InfoStudent：

package Entity;
public class InfoStudent {
    private String idCode;
    private String name;
    private String gender;

    public InfoStudent(String idCode, String name, String gender) {
        this.idCode = idCode;
        this.name = name;
        this.gender = gender;
    }

    public InfoStudent() {
    }

    public String getIdCode() {
        return idCode;
    }

    public void setIdCode(String idCode) {
        this.idCode = idCode;
    }

    public String getName() {
        return name;
    }

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

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "InfoStudent{" +
                "身份证号：='" + idCode + '\'' +
                ", 姓名：='" + name + '\'' +
                ", 性别：='" + gender + '\'' +
                '}';
    }
}

创建DAO（data access object）接口：写上”增删改查“的四个方法

public interface InfoGetDao {
    //加载驱动时：抛出“类不存在”的异常和连接数据库时：的“SQL”的异常
    //select
    InfoStudent query(String  name) throws ClassNotFoundException,SQLException;
    //insert
    int insert(InfoStudent info) throws ClassNotFoundException,SQLException;
    //delete
    int delete(String id_code)throws ClassNotFoundException,SQLException;
    //update
    int update(InfoStudent info)throws ClassNotFoundException,SQLException;
}

创建DAO的实现类：继承方法，写上调用Dao功能后返回

package Dao.Impl;

import Dao.InfoGetDao;
import Entity.InfoStudent;
import java.sql.*;
public class InfoGetDaoImpl implements InfoGetDao {
    @Override
    public InfoStudent query(String name) throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/info";
        String uName = "root";
        String pwd = "123456";
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.拿链接
        Connection conn = DriverManager.getConnection(url, uName, pwd);
        //3.sql
        String sql = "select * from infoelec where name ='" + name + "'";
        //4.预编译
        PreparedStatement pr = conn.prepareStatement(sql);
        //5.拿到结果集
        ResultSet set = pr.executeQuery();
        boolean next = set.next();
        //6.处理结果集
        if (next) {
            String id_code = set.getString("id_code");
            String gender = set.getString("gender");
            InfoStudent info = new InfoStudent();
            info.setName(name);
            info.setIdCode(id_code);
            info.setGender(gender);
            return info;
        }
        pr.close();
        conn.close();
        return null;
    }

    @Override
    public int insert(InfoStudent info) throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/info";
        String uName = "root";
        String pwd = "123456";
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.拿到链接
        Connection conn = DriverManager.getConnection(url, uName, pwd);
        //3.sql
        //String sql = "insert into infoelec(id_code,name,gender) values" + " ('" + info.getIdCode() + "'," + "'" + info.getName() + "'," + "'" + info.getGender() + "')";
        String sql="insert into infoelec(id_code,name,gender) values (?,?,?)";
        //4.预编译sql
        PreparedStatement pr = conn.prepareStatement(sql);
        //5.填充占位符
        pr.setString(1,info.getIdCode());
        pr.setString(2,info.getName());
        pr.setString(3,info.getGender());
        //6.执行SQL
        int i = pr.executeUpdate();
        //7.拿到结果集
        //8.处理结果集
        //9.关闭资源
        pr.close();
        conn.close();
        if (i>0){
        return i;
        }
        else {
            return 0;
        }
    }

    @Override
    public int delete(String id_code) throws ClassNotFoundException, SQLException {
        String url="jdbc:mysql://localhost:3306/info";
        String uName="root";
        String pwd="123456";
        //1.加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.拿到链接
        Connection conn = DriverManager.getConnection(url, uName, pwd);
        //3.SQL
        String sql="delete from infoelec where id_code=(?)";
        //4.预编译SQL
        PreparedStatement pr = conn.prepareStatement(sql);
        //5.填充占位符
        pr.setString(1,id_code);
        //6.执行SQL
        int i = pr.executeUpdate();
        //7.拿到结果集
        //8.处理结果集
        //9.关闭资源
        pr.close();
        conn.close();
        if (i>0){
            return i;
        }else {
            return 0;

        }
    }

    @Override
    public int update(InfoStudent info) throws ClassNotFoundException, SQLException {
        String url="jdbc:mysql://localhost:3306/info";
        String uName="root";
        String pwd="123456";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, uName, pwd);
        String sql="update infoelec set name=(?),gender=(?) where id_code=(?)";
        PreparedStatement pr = conn.prepareStatement(sql);
        pr.setString(1,info.getName());
        pr.setString(2,info.getGender());
        pr.setString(3,info.getIdCode());
        int i = pr.executeUpdate();
        pr.close();
        conn.close();
        if (i>0){
            return i;
        }else {
            return 0;
        }
    }
}

MySQL数据库连接的链接讲解：

String url="jdbc:mysql://localhost:3306/info";

jdbc:mysql:// :协议标识其钟mysql是MySQL服务器，如果是Oracle的数据库服务器，可改成Oracle;

localhost :本机IP地址的俗称，专业名称是127.0.0.1；

:3306 :数据库的端口号，MySQL服务器默认是3306的映射端口；

/info :/数据库名称;固定的格式：“/”+“数据库名称”；

接下来是服务（Service）层的接口：对应“增删改查”的方法名称：

接口

package Service;

import Entity.InfoStudent;
import java.sql.SQLException;
public interface InfoGetService {
    InfoStudent getInfo(String name) throws SQLException, ClassNotFoundException;
    int add(InfoStudent info)throws SQLException,ClassNotFoundException;
    int remove(String id_code)throws ClassNotFoundException,SQLException;
    int getUpdate(InfoStudent info)throws ClassNotFoundException,SQLException;
}

实现

package Service.Impl;

import Dao.Impl.InfoGetDaoImpl;
import Dao.InfoGetDao;
import Entity.InfoStudent;

import java.sql.SQLException;
public class InfoGetServiceImpl implements Service.InfoGetService {
    @Override
    public InfoStudent getInfo(String name) throws SQLException, ClassNotFoundException {
        InfoGetDao dao = new InfoGetDaoImpl();
        return dao.query(name);
    }
    @Override
    public int add(InfoStudent info) throws SQLException, ClassNotFoundException {
        InfoGetDao dao =new InfoGetDaoImpl();
        return dao.insert(info);
    }

    @Override
    public int remove(String id_code) throws ClassNotFoundException, SQLException {
        InfoGetDao dao = new InfoGetDaoImpl();
        return dao.delete(id_code);
    }

    @Override
    public int getUpdate(InfoStudent info) throws ClassNotFoundException, SQLException {
        InfoGetDao dao = new InfoGetDaoImpl();
        return dao.update(info);
    }
}

开始测试：

前置条件：你数据库里有对应的数据库和与实体类对应的表乃至数据库表的字段类型要与你在Java中“增删改”传入的类型一直，及String对varchar/char或Integer对应int/Integer等等。如果类型不一致；MySQL会抛出Doule的异常错误提示：即“Data truncation: Truncated incorrect DOUBLE value”;

import Entity.InfoStudent;
import Service.Impl.InfoGetServiceImpl;
import Service.InfoGetService;
import java.sql.SQLException;

/**
 * @version 1.0.0
 * @Author Jam·Li
 * @ Date 2024/8/24 16:57
 * @ Annotation:
 */
public class Test {
    @org.junit.Test
    public void test() throws SQLException, ClassNotFoundException {
        //创建实体对象，承载数据
        InfoStudent info = new InfoStudent("520", "小美", "女");
        InfoStudent info2 = new InfoStudent("521", "小明", "男");
        //创建Service层对象
        InfoGetService service =new InfoGetServiceImpl();
        //调用service接口的增删改查;
        //增加
        int add = service.add(info);
        int add2 = service.add(info2);
        System.out.println(add);
        System.out.println(add2);
        //删除
        int remove = service.remove("521");
        System.out.println(remove);
        //修改
        InfoStudent infoChange = new InfoStudent("520", "小莉", "女");
        int change = service.getUpdate(infoChange);
        System.out.println(change);
        //查询
        InfoStudent selectInfo = service.getInfo("小莉");
        System.out.println(selectInfo);
   }
 }

测试结果：

1
1
1
1
InfoStudent{身份证号：='520', 姓名：='小莉', 性别：='女'}

数据库里的最终数据：

优化与改进：

潜在问题：

性能瓶颈：频繁的数据库连接创建与销毁开销大。

安全性风险：明文密码在URL中传递。

优化建议：

使用连接池：如HikariCP、c3p0，减少连接创建成本。

环境变量或配置文件：敏感信息不在代码中硬编码。

拓展知识：

如果本文对你有帮助，请帮忙点赞和收藏加作者关注一波；后续的作者会不断推出更精彩质量更高的文章哟！谢谢啦！

我恨我痴心Jack

关注

9
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
手写JDBC：简单的连接MySQL数据库进行“增删改查”(CURD)

JDBC，原始MySQL的单表增删改查。
复制链接

扫一扫