准备工作:
1,在MySQL中创建学生表student
2,在java项目里创建lib,将jar包拷入并且Add to build path
3,创建工具类
package com.zzu.tool.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBLink {
private Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");// 加载驱动
String url = "jdbc:mysql://127.0.0.1:3306/test";
return DriverManager.getConnection(url, "root", "root");// 获取连接
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public ResultSet select(String sql, IRowMapper rowMapper) {// 接口无法创建对象,所以rowMapper参数一定指向IRowMapper接口实现类对象
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();// 获取连接
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);// 执行sql,将查询的数据存到ResultSet类型的变量中
rowMapper.rowMapper(resultSet);// 因为rowMapper参数指向IRowMapper接口实现类对象,所以此处将调用接口实现类中所实现的rowMapper方法 多态
} catch (Exception e) {
e.printStackTrace();
} finally {
close(resultSet, statement, connection);
}
return null;
}
public boolean exist(String sql) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("select id,name,mobile,address from student");
return resultSet.next();
} catch (Exception e) {
e.printStackTrace();
} finally {
close(resultSet, statement, connection);
}
return false;
}
public boolean update(String sql) {
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
statement = connection.createStatement();
int result = statement.executeUpdate(sql);
// statement.close();//如果上面代码出现异常,则该行代码及其下面代码无法执行,比如sql语句语法错误,则statement和connection无法释放
// connection.close();
return result > 0;// 处理结果
} catch (Exception e) {
e.printStackTrace();
} finally {// 即便有异常也会执行代码
close(statement, connection);
}
return false;
}
private void close(Statement statement, Connection connection) {
try {
if (statement != null) {// 可能由于异常导致statement没有赋值,比如url出错
statement.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void close(ResultSet resultSet, Statement statement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
close(statement, connection);
}
}
package com.zzu.tool.db;
import java.sql.ResultSet;
@FunctionalInterface
public interface IRowMapper {
void rowMapper(ResultSet rs);
}
学生管理系统代码:
package com.zzu.main;
import java.sql.SQLException;
import java.util.Scanner;
import com.zzu.tool.db.DBLink;
public class Main {
private static DBLink db = new DBLink();
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、查询学生信息");
System.out.println("请输入操作,以Enter键结束:");
Scanner scanner = new Scanner(System.in);
int option = scanner.nextInt();
switch (option) {
case 1: {
System.out.println("请输入学号:");
String id = scanner.next();
String sql = "select name from student where id = '" + id + "'";
if (db.exist(sql)) {
System.out.println("学号已存在,操作中止!");
return;
}
System.out.println("请输入姓名:");
String name = scanner.next();
System.out.println("请输入手机号:");
String mobile = scanner.next();
System.out.println("请输入地址:");
String address = scanner.next();
sql = "insert into student (id,name,mobile,address) values ('" + id + "','" + name + "','" + mobile + "','" + address + "')";
if (db.update(sql)) {
System.out.println("添加成功");
return;
}
System.out.println("添加失败!");
break;
}
case 2: {
System.out.println("请输入学号:");
String id = scanner.next();
String sql = "select name from student where id = '" + id + "'";
if (db.exist(sql)) {
System.out.println("学号已找到");
return;
}
sql = "delete from student where id ='" + id + "'";
if (db.update(sql)) {
System.out.println("删除成功");
return;
}
System.out.println("删除失败!");
break;
}
case 3:{
System.out.println("请输入学号:");
String id = scanner.next();
String sql = "select name from student where id = '" + id + "'";
if (db.exist(sql)) {
System.out.println("学号已存在,操作中止!");
return;
}
System.out.println("请输入新的姓名:");
String name = scanner.next();
System.out.println("请输入新的手机号:");
String mobile = scanner.next();
System.out.println("请输入新的地址:");
String address = scanner.next();
sql="update student set name=' " + name + " ',' " + mobile + " ',' " + address + " ',where id = ' " + id + " '";
if (db.update(sql)) {
System.out.println("修改成功");
return;
}
System.out.println("修改失败!");
break;
}
case 4:{
System.out.println("请输入学号:");
String id = scanner.next();
String sql = "select name from student where id = '" + id + "'";
if (!db.exist(sql)) {
System.out.println("学号不存在,操作终止!");
return;
}
sql = "select id,name,mobile,address from student where id = ' " + id + " '";
//有名内部类
/*class RowMapper implements IRowMapper {
@Override
public void rowMapper(ResultSet rs) {
try {
if (rs.next()) {
String id = rs.getString("id");
String name = rs.getString("name");
String mobile = rs.getString("mobile");
String address = rs.getString("address");
System.out.println(id + "," + name + "," + mobile + "," + address);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
db.select(sql, new RowMapper());*/
//匿名内部类
/*db.select(sql, new IRowMapper() {
@Override
public void rowMapper(ResultSet rs) {
try {
if (rs.next()) {
String id = rs.getString("id");
String name = rs.getString("name");
String mobile = rs.getString("mobile");
String address = rs.getString("address");
System.out.println(id + "," + name + "," + mobile + "," + address);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
});*/
//Lambada表达式
db.select(sql, (rs) -> {
try {
if (rs.next()) {
String name = rs.getString("name");
String mobile = rs.getString("mobile");
String address = rs.getString("address");
System.out.println(id + "," + name + "," + mobile + "," + address);
}
} catch (SQLException e) {
e.printStackTrace();
}
});
break;
}
default:
System.out.println("I'm Sorry,there is not the " + option + " option,please try again.");
}
}
}