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

本文主要是介绍手写JDBC：简单的连接MySQL数据库进行“增删改查”(CURD)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

大家都会用框架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;}@Overridepublic String toString() {return "InfoStudent{" +"身份证号：='" + idCode + '\'' +", 姓名：='" + name + '\'' +", 性别：='" + gender + '\'' +'}';}
}

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

public interface InfoGetDao {//加载驱动时：抛出“类不存在”的异常和连接数据库时：的“SQL”的异常//selectInfoStudent query(String  name) throws ClassNotFoundException,SQLException;//insertint insert(InfoStudent info) throws ClassNotFoundException,SQLException;//deleteint delete(String id_code)throws ClassNotFoundException,SQLException;//updateint 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 {@Overridepublic 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.sqlString 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;}@Overridepublic 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.预编译sqlPreparedStatement pr = conn.prepareStatement(sql);//5.填充占位符pr.setString(1,info.getIdCode());pr.setString(2,info.getName());pr.setString(3,info.getGender());//6.执行SQLint i = pr.executeUpdate();//7.拿到结果集//8.处理结果集//9.关闭资源pr.close();conn.close();if (i>0){return i;}else {return 0;}}@Overridepublic 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.SQLString sql="delete from infoelec where id_code=(?)";//4.预编译SQLPreparedStatement pr = conn.prepareStatement(sql);//5.填充占位符pr.setString(1,id_code);//6.执行SQLint i = pr.executeUpdate();//7.拿到结果集//8.处理结果集//9.关闭资源pr.close();conn.close();if (i>0){return i;}else {return 0;}}@Overridepublic 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 {@Overridepublic InfoStudent getInfo(String name) throws SQLException, ClassNotFoundException {InfoGetDao dao = new InfoGetDaoImpl();return dao.query(name);}@Overridepublic int add(InfoStudent info) throws SQLException, ClassNotFoundException {InfoGetDao dao =new InfoGetDaoImpl();return dao.insert(info);}@Overridepublic int remove(String id_code) throws ClassNotFoundException, SQLException {InfoGetDao dao = new InfoGetDaoImpl();return dao.delete(id_code);}@Overridepublic 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.Testpublic 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，减少连接创建成本。

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

拓展知识：

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

这篇关于手写JDBC：简单的连接MySQL数据库进行“增删改查”(CURD)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---