DBUtil工具类实战——实现简单学生管理系统
使用DBUtil工具类实现简单学生管理系统,要求:
1.添加学生信息,不允许添加学号相同的学生信息;
2.删除学生信息,根据学号删除学生信息;
3.修改学生信息,根据学号获取学生信息,修改后保存到集合中;
4.查询学生信息,根据学号在控制台显示学生基本信息;
一、frame
Menu
package frame;
import java.util.Scanner;
import service.AddService;
import service.DeleteService;
import service.LoginService;
import service.QueryService;
import service.ReviseService;
public class Menu {
public static void main(String[] args) {
System.out.println("*********************************");
System.out.println("*\t\t\t\t*");
System.out.println("*\t欢迎使用学生信息管理系统\t*");
System.out.println("*\t\t\t\t*");
System.out.println("*********************************");
while (true) {
menu();
}
}
static void menu() {
System.out.println("1、添加学生信息");
System.out.println("2、删除学生信息");
System.out.println("3、修改学生信息");//地址传递
System.out.println("4、查询学生信息");//name
System.out.println("请输入操作,以Enter键结束:");
Scanner scanner = new Scanner(System.in);
int option = scanner.nextInt();
switch (option) {
case 1:
if(AddService.add()) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
break;
case 2:
if(DeleteService.delete()) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
break;
case 3:
if(ReviseService.revise()) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
break;
case 4:
QueryService.query();
break;
default:
System.out.println("I'm Sorry,there is not the "+option+" option,please try again.");
}
}
}
二、model
Student类
package model;
public class Student {
private String stuNo;
private String name;
public String getStuNo() {
return stuNo;
}
public void setStuNo(String stuNo) {
this.stuNo = stuNo;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Student(String stuNo, String name) {
super();
this.stuNo = stuNo;
this.name = name;
}
}
三、service
1、AddService
package service;
import java.util.Scanner;
import util.DBUtil;
public class AddService {
public static boolean add() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入你想要添加学生的学号");
String stuNo = scanner.next();
System.out.println("请输入你想要添加学生的姓名");
String name = scanner.next();
String SQL = "select* from student where student_number=?";
if (!DBUtil.exist(SQL, stuNo)) {
String sql = "insert into student values (?,?)";
return DBUtil.update(sql, stuNo,name);
}else {
System.out.println("你想要添加的学生已存在");
return false;
}
}
}
2、DeleteService
package service;
import java.util.Scanner;
import util.DBUtil;
public class DeleteService {
public static boolean delete() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入你想要删除学生的学号");
String stuNo = scanner.next();
String SQL = "select* from student where student_number=?";
if (DBUtil.exist(SQL, stuNo)) {
String sql = "delete from student where student_number=? ";
return DBUtil.update(sql, stuNo);
}else {
System.out.println("你想要删除的学生不存在");
return false;
}
}
}
3、ReviseService
package service;
import java.util.Scanner;
import util.DBUtil;
public class ReviseService {
public static boolean revise() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入你想要修改学生的学号");
String stuNo = scanner.next();
String SQL = "select* from student where student_number=?";
if (DBUtil.exist(SQL, stuNo)) {
System.out.println("请输入该学生新的名字");
String name = scanner.next();
String sql = "update student set name=? where student_number=?";
return DBUtil.update(sql, name,stuNo);
}else {
System.out.println("你想要修改的学生不存在");
return false;
}
}
}
4、QueryService
package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import util.DBUtil;
import util.IRowMapper;
public class QueryService {
public static void query(){
Scanner scanner = new Scanner(System.in);
System.out.println("请输入你想要查询学生的学号:");
String stuNo = scanner.next();
String sql = "select* from student where student_number =?";
boolean flag = DBUtil.exist(sql, stuNo);
if(flag) {
class RowMapper implements IRowMapper{
@Override
public void RowMapper(ResultSet resultSet) {
try {
while(resultSet.next()) {
System.out.println("学号:"+resultSet.getString("student_number"));
System.out.println("姓名:"+resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
DBUtil.select(sql, rowMapper , stuNo);
}else {
System.out.println("该学生不存在!");
}
}
}
三、util
1、IRowMapper接口
package util;
import java.sql.ResultSet;
public interface IRowMapper {
void RowMapper(ResultSet resultSet);
}
2、DBUtil工具类
public class DBUtil {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}//1、加载驱动程序类
}
/**
* 获取数据库连接
*
* @author 王豪斌
*/
public static Connection getConnection() {
try {
String url = PropertiesUtil.getValue("jdbc.url");
String username = PropertiesUtil.getValue("jdbc.username");
String password = PropertiesUtil.getValue("jdbc.password");
Connection connection = DriverManager.getConnection(url, username, password);//2、获取连接
return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 修改数据
*
* @author 王豪斌
*/
public static boolean update(String sql) {
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
statement = connection.createStatement();//3、创建语句
int effectRows = statement.executeUpdate(sql);//4、执行语句
return effectRows > 0;//5、处理结果
} catch (Exception e) {
e.printStackTrace();
}finally {
close(statement,connection);
}
return false;
}
/**
* 修改数据
*
* @author 王豪斌
*/
public static boolean update(String sql,Object...prams) {
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
PreparedStatement preparedStatement= connection.prepareStatement(sql);//3、创建语句
for(int i = 1;i<=prams.length;i++) {
preparedStatement.setObject(i, prams[i-1]);
}
int effectRows = preparedStatement.executeUpdate();
return effectRows > 0;
} catch (Exception e) {
e.printStackTrace();
}finally {
close(statement,connection);
}
return false;
}
/**
* 判断数据是否存在
*
* @author 王豪斌
*/
public static boolean exist(String sql) {
class RowMapper implements IRowMapper{
boolean state;
@Override
public void RowMapper(ResultSet resultSet) {
try {
state = resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
select(sql, rowMapper);
return rowMapper.state;
}
/**
* 判断数据是否存在
*
* @author 王豪斌
*/
public static boolean exist(String sql,Object...prams) {
class RowMapper implements IRowMapper{
boolean state;
@Override
public void RowMapper(ResultSet resultSet) {
try {
state = resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
select(sql, rowMapper, prams);
return rowMapper.state;
}
/**
* 查询数据
*
* @author 王豪斌
*/
public static void select(String sql,IRowMapper rowMapper) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.createStatement();//3、创建sql语句
resultSet = statement.executeQuery(sql);//4、执行语句
rowMapper.RowMapper(resultSet);
} catch (Exception e) {
e.printStackTrace();
}finally {
close(resultSet,statement,connection);
}
}
/**
* 查询数据
*
* @author 王豪斌
*/
public static void select(String sql,IRowMapper rowMApper,Object...prams) {
Connection connection = null;//2、获得连接
Statement statement = null;//3、创建语句
ResultSet resultSet = null;
try {
connection = getConnection();
PreparedStatement preparedStatement= connection.prepareStatement(sql);//3、创建语句
for(int i = 1;i<=prams.length;i++) {
preparedStatement.setObject(i, prams[i-1]);
}
resultSet = preparedStatement.executeQuery();
rowMApper.RowMapper(resultSet);
} catch (Exception e) {
e.printStackTrace();
}finally {
close(resultSet,statement,connection);
}
}
/**
* 释放资源
*
* @author 王豪斌
*/
public static void close(Statement statement,Connection connection) {
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 释放资源
*
* @author 王豪斌
*/
public static void close(ResultSet resultSet,Statement statement,Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}