使用JDBC实现mysql数据库的增删查改
项目地址：[https://github.com/XQ-yang/JDBC)
（本文是CSDN中的博客，今天搬迁至此）
mysql连接包下载地址：https://dev.mysql.com/downloads/connector/j/
window系统的用户选择Platform Independent
创建实体类
根据你的mysql数据库中的某个表的字段定义属性，此处我根据自己的数据表定义的属性有id，username，password，sex，age。
package com.junit.dome;
/**
* @Author:yxq
* @Date: 2020/6/26 14:18
* @Tools: IntelliJ IDEA
**/
public class User {
private int id; //用户id
private String username; //用户名
private String password; //用户密码
private String sex; //性别
private int age; //年龄
}
此处我省略了getter和setter方法，这两个方法可以自动生成，在属性后右键，选择Generate或直接按快捷键Alt+Insert。
选择Getter and Setter。
你可以选择要生成Getter和Setter方法的属性，全部生成就Ctrl+A即可。
编写工具类DBUtil
实现数据库连接
package com.junit.dome;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* @Author:yxq
* @Date: 2020/6/26 14:12
* @Tools: IntelliJ IDEA
**/
public class DBUtil {
private String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC"; //test是使用到的数据库
public String username = "root"; //大部分数据库用户名都是root
public String password = "yxq"; //密码根据自己的写
/**
* 1. 实现数据库连接的方法
*/
public Connection getConn() {
Connection conn = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(url, username, password);
System.out.println("连接数据库成功!!!");
} catch (Exception e) {
System.out.println("数据库连接失败！");
e.printStackTrace();
}
return conn;
}
/**
* 2. 释放数据库连接
*/
public void closeConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws Exception {
if (rs != null) {
rs.close();
}
if (pstm != null) {
pstm.close();
}
if (conn != null) {
conn.close();
}
}
}
该工具类包含了两个方法，连接数据库和释放数据库连接资源。
若使用的数据库版本为8的，就需要在url中数据库后加上serverTimezone=UTC，否则会报错。
编写一个dao类
实现数据库增删查改功能
package com.junit.dome;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* @Author:yxq
* @Date: 2020/6/26 14:22
* @Tools: IntelliJ IDEA
**/
/*
增删查改操作
*/
public class UserDao {
DBUtil db = new DBUtil();
/*
* 插入用户信息
*/
public void insertUser(User user) throws Exception {
Connection conn = db.getConn();
PreparedStatement pstm;
String sql_insert = "insert into user values(?,?,?,?,?)"; //sql语言
pstm = conn.prepareStatement(sql_insert);
//填充sql语句中的？
pstm.setInt(1, user.getId());
pstm.setString(2, user.getUsername());
pstm.setString(3, user.getPassword());
pstm.setString(4, user.getSex());
pstm.setInt(5, user.getAge());
//使用executeUpdate函数执行sql语句
int row = pstm.executeUpdate();
if (row > 0) {
System.out.println("新增用户成功, " + row + "行受到影响");
} else {
System.out.println("新增用户失败！");
}
//释放对数据库的连接
db.closeConn(null, pstm, conn);
}
/*
* 修改用户信息
*/
public void updateUser(int id, User user) throws Exception {
Connection conn = db.getConn();
PreparedStatement pstm;
String sql_update = "update user set password=? where id=?";
pstm = conn.prepareStatement(sql_update);
// pstm.setString(1,user.getUsername()); //运行报错说与mysql关键字相同，故不修改用户名
pstm.setString(1, user.getPassword());
pstm.setInt(2, id);
int row = pstm.executeUpdate();
if (row > 0) {
System.out.println("修改用户信息成功, " + row + "行受到影响");
} else {
System.out.println("修改用户信息失败！");
}
db.closeConn(null, pstm, conn);
}
/*
* 删除用户信息
*/
public void deleteUser(int id) throws Exception {
Connection conn = db.getConn();
PreparedStatement pstm;
String sql_delete = "delete from user where id=?";
pstm = conn.prepareStatement(sql_delete);
pstm.setInt(1, id);
int row = pstm.executeUpdate();
if (row > 0) {
System.out.println("删除用户成功, " + row + "行受到影响");
} else {
System.out.println("删除用户失败！");
}
db.closeConn(null, pstm, conn);
}
/*
* 查询全部用户
*/
public void findUser() throws Exception {
Connection conn = db.getConn();
PreparedStatement pstm;
ResultSet res;
String sql_find = "select * from user ";
pstm = conn.prepareStatement(sql_find);
res = pstm.executeQuery();
System.out.println("查询用户成功!!");
System.out.println("id\tname\tpassword\tsex\t\tage");
while (res.next()) {
System.out.println(res.getInt(1) + "\t" +
res.getString(2) + "\t" + res.getString(3)
+ "\t\t\t" + res.getString(4) + "\t\t" + res.getInt(5)
);
}
db.closeConn(res, pstm, conn);
}
/*
* 根据查询用户
*/
public void findUserbyid(int id) throws Exception {
Connection conn = db.getConn();
PreparedStatement pstm;
ResultSet res;
String sql_find = "select * from user where id = ?";
pstm = conn.prepareStatement(sql_find);
pstm.setInt(1, id);
res = pstm.executeQuery();
if (res.next()) {
System.out.println("找到了该用户！");
System.out.println("id\tname\tpassword\tsex\tage");
System.out.println(res.getInt(1) + "\t" +
res.getString(2) + "\t" + res.getString(3)
+ "\t\t\t" + res.getString(4) + "\t" + res.getInt(5)
);
} else {
System.out.println("未找到该用户！");
}
db.closeConn(res, pstm, conn);
}
/*
* 统计用户
*/
public void countUser() throws Exception {
Connection conn = db.getConn();
PreparedStatement pstm;
ResultSet res;
String sql_count = "select count(*) from user";
pstm = conn.prepareStatement(sql_count);
res = pstm.executeQuery();
int count = 0;
if (res.next()) {
count = res.getInt(1);
System.out.println("用户数有" + count + "个！");
} else {
System.out.println("没有用户！");
}
db.closeConn(res, pstm, conn);
}
}
编写测试类
package com.junit.dome;
/**
* @Author:yxq
* @Date: 2020/6/26 15:02
* @Tools: IntelliJ IDEA
**/
public class Test {
public static void main(String[] args) throws Exception {
UserDao dao = new UserDao();
User user = new User();
// user.setUsername("小明");
// user.setPassword("324");
dao.countUser();
}
}
关于测试，先实例化一个user对象，通过setter方法对属性赋值，然后调用Userdao中的方法，即可实现增删改查操作。
SQL语句
最后给出数据库的sql语句
/*
Navicat Premium Data Transfer
Source Server : mysql
Source Server Type : MySQL
Source Server Version : 80018
Source Host : localhost:3306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 80018
File Encoding : 65001
Date: 02/11/2020 16:33:08
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(10) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '杨小强', '123', '男', 18);
INSERT INTO `user` VALUES (2, '王五', '951', '男', 20);
INSERT INTO `user` VALUES (3, '李四', '314', '女', 19);
SET FOREIGN_KEY_CHECKS = 1;
