本文介绍了一个基于Druid数据库连接池的学生管理系统实现。该系统涵盖了学生信息的增删查改等功能，并提供了登录和退出操作。代码示例详细展示了如何使用Druid进行数据库连接管理和操作。
基于Druid数据库连接池#学生管理系统
正文
这是一个用druid数据库连接池写的学生管理系统模板，并细化了每一个模块
包括：增删查改登录退出，登陆退出是为了后续用多线程并结合操作系统模拟死锁的互斥访问问题。
这个模板也算是麻雀虽小五脏俱全，非常适合新手入门的时候学习，如果直接复制代码，直接把所有的代码放到一个包就行，配置文件随便放一个包，命名随意，但是要properties格式就行SQL代码需要放到数据库。
不足之处欢迎大家指正
学生管理系统主方法代码
import com.sun.org.apache.xpath.internal.operations.Bool;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
public class StudentSystem {
public static void main(String[] args) {
//登录页面
while (true) {
System.out.println("please input the username you have");
Scanner scanner = new Scanner(System.in);
String username = scanner.nextLine();
System.out.println("please input the password you have");
Scanner scanner1 = new Scanner(System.in);
String password = scanner1.nextLine();
if (login(username, password) == 1) {
show();
} else {
System.out.println("sorry");
}
}
}
//学生管理系统增删查改测试。
//功能展示模块
public static void show() {
while (true) {
System.out.println("please select the function you wanna:");
System.out.println("1 for add");
System.out.println("2 for delete");
System.out.println("3 for select");
System.out.println("4 for set");
Scanner scanner = new Scanner(System.in);
int button = scanner.nextInt();
switch (button) {
case 1:
// add function();
add(createStudent());
break;
case 2:
// delete function();
showIdAndName(2);
break;
case 3:
// select function();
showIdAndName(3);
break;
case 4:
// set function();
showIdAndName(4);
break;
}
}
}
//创建学生
public static Student createStudent() {
// 为了减少add方法的长度，把添加的学生对象独立成一个方法，通过控制台输入学生信息之后，将学生对象返回给add方法
Student student = new Student();
student.setMoney(BigDecimal.valueOf(3.53));
student.setLogin_date(LocalDateTime.now());
student.setIslogin(false);
System.out.println("please input the username");
Scanner scanner = new Scanner(System.in);
String username = scanner.nextLine();
student.setUsername(username);
System.out.println("please input the password");
Scanner scanner1 = new Scanner(System.in);
String password = scanner1.nextLine();
student.setPassword(password);
System.out.println("please input the name");
Scanner scanner2 = new Scanner(System.in);
String name = scanner2.nextLine();
student.setName(name);
System.out.println("please input the dept_id");
Scanner scanner3 = new Scanner(System.in);
Long dept_id = scanner3.nextLong();
student.setDeptId(dept_id);
System.out.println("please input the age");
Scanner scanner4 = new Scanner(System.in);
int age = scanner4.nextInt();
student.setAge(age);
System.out.println("please input the sex");
Scanner scanner5 = new Scanner(System.in);
Boolean sex = scanner5.nextBoolean();
student.setSex(sex);
System.out.println("please input the height");
Scanner scanner6 = new Scanner(System.in);
int height = scanner6.nextInt();
student.setHeight(height);
// 返回学生对象，并被add方法接收
return student;
}
//展示学生id及其姓名，方便删除和搜索
public static void showIdAndName(int num) {
System.out.println("id" + "\t" + "name");
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select id,name from tb_students_info";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(
resultSet.getInt(1) + "\t"
+ resultSet.getString(2) + "\t"
);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(resultSet, preparedStatement, connection);
}
switch (num) {
case 2:
System.out.println("please input the id you wanna delete");
Scanner scanner = new Scanner(System.in);
delete(scanner.nextInt());
break;
case 3:
System.out.println("please input the id you wanna select");
Scanner scanner1 = new Scanner(System.in);
select(scanner1.nextInt());
break;
case 4:
System.out.println("please select the id you wanna");
Scanner scanner2=new Scanner(System.in);
int band=scanner2.nextInt();
update(createStudent(),band);
System.out.println("set successfully");
break;
}
}
//登
public static int login(String username, String password) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String loginsql = "update tb_students_info set islogin=true where username=? and password=?";
preparedStatement = connection.prepareStatement(loginsql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
int a = preparedStatement.executeUpdate();
return a;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(resultSet, preparedStatement, connection);
}
return 0;
}
public static void setUtil(Student student,int and){
update(student,and);
}
//学生管理系统增删查改测试。
//增
public static int add(Student student) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
String sql = "INSERT INTO tb_students_info VALUE(?,?,?,?,?,?,?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, null);
preparedStatement.setString(2, student.getUsername());
preparedStatement.setString(3, student.getPassword());
preparedStatement.setString(4, student.getName());
preparedStatement.setLong(5, student.getDeptId());
preparedStatement.setInt(6, student.getAge());
preparedStatement.setBoolean(7, student.getSex());
preparedStatement.setInt(8, student.getHeight());
preparedStatement.setBigDecimal(9, student.getMoney());
java.sql.Date loginDate = new java.sql.Date(Date.from(student.getLogin_date().atZone(ZoneOffset.ofHours(8)).toInstant()).getTime());
preparedStatement.setDate(10, loginDate);
preparedStatement.setBoolean(11, false);
int i = preparedStatement.executeUpdate();
return i;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(null, preparedStatement, connection);
}
return 0;
}
// 删
public static void delete(int id) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "delete from tb_students_info where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
int i = preparedStatement.executeUpdate();
System.out.println(i);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(resultSet, preparedStatement, connection);
}
}
//查
public static void select(int num) {
System.out.println("id" + "\t" + "username" + " " + "password" + " " + "name" + " " + "dept_id" + " " + "age" + " " + "sex" + " " + "height" + " " + "money" + " " + "login_data");
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select * from tb_students_info where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, num);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(
resultSet.getInt(1) + "\t"
+ resultSet.getString(2) + "\t "
+ resultSet.getString(3) + "\t"
+ resultSet.getString(4) + "\t "
+ resultSet.getInt(5) + "\t "
+ resultSet.getInt(6) + "\t "
+ resultSet.getInt(7) + "\t "
+ resultSet.getInt(8) + "\t "
+ resultSet.getBigDecimal(9) + "\t"
+ resultSet.getDate(10)+ "\t"
);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(resultSet, preparedStatement, connection);
}
}
//改
public static void update(Student student,int num) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "update tb_students_info set username=?,password=?,name=?,dept_id=?,age=?,sex=?,height=? where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, student.getUsername());
preparedStatement.setString(2,student.getPassword());
preparedStatement.setString(3,student.getName());
preparedStatement.setLong(4,student.getDeptId());
preparedStatement.setInt(5,student.getAge());
preparedStatement.setBoolean(6,student.getSex());
preparedStatement.setInt(7,student.getHeight());
preparedStatement.setInt(8,num);
int i = preparedStatement.executeUpdate();
System.out.println(i);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(resultSet, preparedStatement, connection);
}
}
}
Druid工具类代码
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.support.spring.stat.annotation.Stat;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
//druid工具类
public class JDBCUtils {
//1.定义成员变量 DateSource
private static DataSource dataSource;
static {
try {
//1.加载配置文件
Properties properties = new Properties(); properties.load(DRUIDDateSource.class.getClassLoader().getResourceAsStream("druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
Connection connection = null;
try {
connection = dataSource.getConnection();
return connection;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
//获取链接池的方法
public static DataSource getDataSource() {
return dataSource;
}
//关闭数据库连接的封装，通过动态传参，将resultSet,preproStatements,connection按照顺序传进来
public static void closeAll(AutoCloseable... closeables) {
for (AutoCloseable closeable : closeables) {
if (closeable != null) {
try {
closeable.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
student类代码
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Date;
public class Student {
private Long id;
private String username;
private String password;
private String name;
private Long deptId;
private int age;
private Boolean sex;
private int height;
private BigDecimal money;
private LocalDateTime login_date;
private boolean islogin;
public Student() {
}
public Student(Long id, String username, String password, String name, Long deptId, int age, Boolean sex, int height, BigDecimal money, LocalDateTime login_date,Boolean islogin) {
this.id = id;
this.username = username;
this.password = password;
this.name = name;
this.deptId = deptId;
this.age = age;
this.sex = sex;
this.height = height;
this.money = money;
this.login_date = login_date;
this.islogin=islogin;
}
public BigDecimal getMoney() {
return money;
}
public void setMoney(BigDecimal money) {
this.money = money;
}
public Long getId() {
return id;
}
public void setId(Long 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;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Long getDeptId() {
return deptId;
}
public void setDeptId(Long deptId) {
this.deptId = deptId;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Boolean getSex() {
return sex;
}
public void setSex(Boolean sex) {
this.sex = sex;
}
public int getHeight() {
return height;
}
public void setHeight(int height) {
this.height = height;
}
public LocalDateTime getLogin_date() {
return login_date;
}
public void setLogin_date(LocalDateTime login_date) {
this.login_date = login_date;
}
public boolean isIslogin() {
return islogin;
}
public void setIslogin(boolean islogin) {
this.islogin = islogin;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
", deptId=" + deptId +
", age=" + age +
", sex=" + sex +
", height=" + height +
", money=" + money +
", login_date=" + login_date +
", islogin=" + islogin +
'}';
}
}
Druid的配置文件
我的命名格式是druid.properties
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=30000
spring.datasource.maxEvictableIdleTimeMillis=180000
#驱动加载
driverClassName=com.mysql.jdbc.Driver
#注册驱动
url=jdbc:mysql://localhost:3306/db9?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8
#连接数据库的用户名
username=root
#连接数据库的密码
password=123456
#初始化时池中建立的物理连接个数。
initialSize=2
#最大的可活跃的连接池数量
maxActive=5
#获取连接时最大等待时间，单位毫秒，超过连接就会失效。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降， 如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
maxWait=3000
SQL表信息
/*
Navicat MySQL Data Transfer
Source Server : fiveGods
Source Server Version : 80016
Source Host : localhost:3306
Source Database : db9
Target Server Type : MYSQL
Target Server Version : 80016
File Encoding : 65001
Date: 2021-12-19 09:14:27
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_students_info
-- ----------------------------
DROP TABLE IF EXISTS `tb_students_info`;
CREATE TABLE `tb_students_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id\r\n',
`username` varchar(255) NOT NULL DEFAULT '' COMMENT '用户名',
`password` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
`name` varchar(255) NOT NULL DEFAULT '' COMMENT '姓名',
`dept_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
`age` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`sex` bit(1) NOT NULL DEFAULT b'0' COMMENT '性别 0 男 1 女',
`height` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '身高',
`money` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '存款',
`login_date` datetime DEFAULT NULL COMMENT '登陆时间',
`islogin` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_students_info
-- ----------------------------
INSERT INTO `tb_students_info` VALUES ('3', '2019002', '2019002', 'Henry', '1', '1', '\0', '185', '0.00', '2021-12-08 23:27:08', '0');
INSERT INTO `tb_students_info` VALUES ('4', '2019003', '2019003', 'Jane', '1', '22', '\0', '162', '100.00', '2021-12-08 23:27:44', '0');
INSERT INTO `tb_students_info` VALUES ('5', '2019004', '2019004', 'Jim', '6', '21', '\0', '175', '-50.00', '2021-12-08 23:27:47', '0');
INSERT INTO `tb_students_info` VALUES ('6', '2019005', '2019005', 'John', '5', '25', '\0', '172', '200.00', '2021-12-04 23:27:52', '0');
INSERT INTO `tb_students_info` VALUES ('7', '2019006', '2019006', 'Lily', '1', '0', '\0', '165', '0.00', '2021-11-30 23:27:57', '0');
INSERT INTO `tb_students_info` VALUES ('8', '2019007', '2019007', 'Susan', '1', '20', '\0', '170', '0.00', '2021-11-02 23:28:01', '0');
INSERT INTO `tb_students_info` VALUES ('9', '2019008', '2019008', 'Thomas', '4', '35', '\0', '178', '0.00', '2021-12-03 23:28:06', '0');
INSERT INTO `tb_students_info` VALUES ('10', '2019009', '2019009', 'Tom', '3', '15', '\0', '165', '0.00', '2021-12-26 23:28:10', '0');
INSERT INTO `tb_students_info` VALUES ('11', '2019010', '2019010', 'Jerry', '1', '15', '\0', '170', '0.00', '2021-12-08 23:27:47', '0');
INSERT INTO `tb_students_info` VALUES ('22', '2019012', '2019012', 'wei', '1', '18', '\0', '178', '0.00', '2021-12-11 00:00:00', '0');
INSERT INTO `tb_students_info` VALUES ('23', '2019012', '2019012', '王五', '1', '18', '\0', '178', '100.00', '2021-12-11 00:00:00', '0');
INSERT INTO `tb_students_info` VALUES ('25', '2019002', '2019002', 'Henry', '1', '1', '\0', '185', '3.53', '2021-12-13 00:00:00', '0');
INSERT INTO `tb_students_info` VALUES ('26', '2019002', '2019002', 'Henry', '1', '1', '\0', '185', '3.53', '2021-12-13 00:00:00', '0');
INSERT INTO `tb_students_info` VALUES ('27', '2019002', '2019002', 'Henry', '1', '1', '\0', '185', '3.53', '2021-12-13 00:00:00', '0');
扫一扫
4388
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
