两个数据单元类
student和grade
public class student {String name;String phoneNumber;String gender;String studentId;public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPhoneNumber() {return phoneNumber;}public void setPhoneNumber(String phoneNumber) {this.phoneNumber = phoneNumber;}public String getStudentId() {return studentId;}public void setStudentId(String studentId) {this.studentId = studentId;}@Overridepublic String toString() {return "student{" +"gender='" + gender + '\'' +", name='" + name + '\'' +", phoneNumber='" + phoneNumber + '\'' +", studentId='" + studentId + '\'' +'}';}public student(String studentId, String name, String gender,String phoneNumber ) {this.gender = gender;this.name = name;this.phoneNumber = phoneNumber;this.studentId = studentId;}public student(){}
}
public class grade{String studentId;String name;double chinese;double math;double english;public grade(String studentId,String name,double chinese,double math, double english) {this.chinese = chinese;this.english = english;this.math = math;this.name = name;this.studentId = studentId;}public grade(){}public double getChinese() {return chinese;}public void setChinese(double chinese) {this.chinese = chinese;}public double getEnglish() {return english;}public void setEnglish(double english) {this.english = english;}public double getMath() {return math;}public void setMath(double math) {this.math = math;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getStudentId() {return studentId;}public void setStudentId(String studentId) {this.studentId = studentId;}}
一个表接口类
tableOperate<E>,E为数据单元类
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;public interface tableOperate<E> {void add(Connection conn);void delete(Connection conn);ArrayList<E> outDatabase(Connection conn,String tableName) throws SQLException;void modify(Connection conn);
}
/*这个接口是每个表里面的操作方法
* 所有的表都应该继承这个接口*/
两个表类
students表和grades表
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Scanner;public class students implements tableOperate<student>{@Overridepublic void add(Connection conn) {Scanner scanner =new Scanner(System.in);student stu = new student();System.out.println("输入学号");stu.setStudentId(scanner.next());System.out.println("输入姓名");stu.setName(scanner.next());System.out.println("输入性别");stu.setGender(scanner.next());System.out.println("输入手机号码");stu.setPhoneNumber(scanner.next());String insert_sql = "insert into students(studentId,name,gender,phoneNumber)values(?,?,?,?)";try {PreparedStatement pstmt = conn.prepareStatement(insert_sql);pstmt.setString(1, stu.getStudentId());pstmt.setString(2, stu.getName());pstmt.setString(3, stu.getGender());pstmt.setString(4, stu.getPhoneNumber());pstmt.executeUpdate();pstmt.close();} catch (Exception e) {e.printStackTrace();} finally {}}@Overridepublic void delete(Connection conn) {Scanner scanner =new Scanner(System.in);System.out.println("输入要删除的学生学号");String studentId = scanner.next();int flag = 0;String delete_sql = "DELETE FROM students WHERE studentId = ?;";int count = 0;try {PreparedStatement pstmt = conn.prepareStatement(delete_sql);pstmt.setString(1, studentId);count = pstmt.executeUpdate();} catch (SQLException e) {throw new RuntimeException(e);}if (count > 0) {System.out.println("删除成功");} else {System.out.println("删除失败");}}@Overridepublic ArrayList<student> outDatabase(Connection conn, String tableName) throws SQLException {ArrayList<student> list=new ArrayList<>();String sql= "select* from "+tableName;Statement stmt= conn.createStatement();ResultSet infos=stmt.executeQuery(sql);while(infos.next()){String studentId = infos.getString("studentId");String name = infos.getString("name");String gender = infos.getString("gender");String phoneNumber = infos.getString("phoneNumber");student stu = new student(studentId, name, gender, phoneNumber);list.add(stu);}return list;}@Overridepublic void modify(Connection conn) {System.out.println("请输入要修改的学生的学号");Scanner scanner =new Scanner(System.in);String studentID=scanner.next();System.out.println("请输入要修改的字段信息");System.out.println("有四个可修改的字段,分别为gender,name,phoneNumber");String field=scanner.next();System.out.println("输入修改后的信息");String newInfos=scanner.next();String sql="UPDATE students SET "+field+" = ? where studentId = ?";try {PreparedStatement pstmt= conn.prepareStatement(sql);pstmt.setString(1,newInfos);pstmt.setString(2,studentID);int count=pstmt.executeUpdate();if(count>0){System.out.println("修改成功");}else{System.out.println("修改失败");}} catch (SQLException e) {System.out.println("出现错误");throw new RuntimeException(e);}}}
import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;public class grades implements tableOperate<grade>{@Overridepublic void add(Connection conn) {Scanner scanner =new Scanner(System.in);System.out.println("请输入学号");String studentId=scanner.next();System.out.println("请输入姓名");String name=scanner.next();System.out.println("请输入语文成绩");double chinese=scanner.nextDouble();System.out.println("请输入数学成绩");double math=scanner.nextDouble();System.out.println("请输入英语成绩");double english=scanner.nextDouble();String sql="insert into grades(studentId,name,chinese,math,english)values(?,?,?,?,?)";try {PreparedStatement pstmt=conn.prepareStatement(sql);pstmt.setString(1,studentId);pstmt.setString(2,name);pstmt.setDouble(3,chinese);pstmt.setDouble(4,math);pstmt.setDouble(5,english);pstmt.executeUpdate();} catch (SQLException e) {throw new RuntimeException(e);}}@Overridepublic void delete(Connection conn) {Scanner scanner =new Scanner(System.in);System.out.println("输入要删除的学生学号");String studentId = scanner.next();int flag = 0;String delete_sql = "DELETE FROM grades WHERE studentId = ?;";int count = 0;try {PreparedStatement pstmt = conn.prepareStatement(delete_sql);pstmt.setString(1, studentId);count = pstmt.executeUpdate();} catch (SQLException e) {throw new RuntimeException(e);}if (count > 0) {System.out.println("删除成功");} else {System.out.println("删除失败");}}@Overridepublic ArrayList<grade> outDatabase(Connection conn, String tableName) throws SQLException {ArrayList<grade> list=new ArrayList<>();String sql= "select* from "+tableName;Statement stmt= conn.createStatement();ResultSet infos=stmt.executeQuery(sql);while(infos.next()){String studentId = infos.getString("studentId");String name = infos.getString("name");double chinese = infos.getDouble("chinese");double math = infos.getDouble("math");double english = infos.getDouble("english");grade stucg = new grade(studentId, name, chinese, math,english);list.add(stucg);}return list;}@Overridepublic void modify(Connection conn) {System.out.println("请输入要修改的学生的学号");Scanner scanner =new Scanner(System.in);String studentID=scanner.next();System.out.println("请输入要修改的字段信息");System.out.println("有四个可修改的字段,分别为name,chinese,math,english");String field=scanner.next();System.out.println("输入修改后的信息");if(field.equals(("name"))){String newInfos=scanner.next();String sql="UPDATE grades SET "+field+" = ? where studentId = ?";try {PreparedStatement pstmt=conn.prepareStatement(sql);pstmt.setString(1,newInfos);pstmt.setString(2,studentID);int count=pstmt.executeUpdate();if(count>0){System.out.println("修改成功");}else{System.out.println("修改失败");}} catch (SQLException e) {throw new RuntimeException(e);}}else{double newInfos=scanner.nextDouble();String sql="UPDATE grades SET "+field+" = ? where studentId = ?";try {PreparedStatement pstmt=conn.prepareStatement(sql);pstmt.setDouble(1,newInfos);pstmt.setString(2,studentID);int count=pstmt.executeUpdate();if(count>0){System.out.println("修改成功");}else{System.out.println("修改失败");}} catch (SQLException e) {throw new RuntimeException(e);}}}
}
一个表的操作类
operateInterFace,
<E> void operate(Connection conn, tableOperate<E> table) throws SQLException
接受一个接口实现类来实现对不同表的操作
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;public class operateInterFace {public static <E> void operate(Connection conn, tableOperate<E> table) throws SQLException {int choice=-1;while(choice!=0){System.out.println("1.往表内添加信息");System.out.println("2.往表内删除信息");System.out.println("3.将表内信息导入到一个容器内");System.out.println("4.修改表中字段下的信息");System.out.println("0.退出");Scanner scanner=new Scanner(System.in);choice= scanner.nextInt();if(choice==1){table.add(conn);}else if(choice==2){table.delete(conn);}else if(choice==3){ArrayList<E> list=table.outDatabase(conn,table.getClass().getName());}else if(choice==4){table.modify(conn);}else if(choice==0){break;}else {System.out.println("无效操作,请重试");}}}
}
/*这个类是实现每一个表的操作交互的,可以将表传入参数当中*/
数据库操作类
里面包含对数据库表的操作
主方法在这个类里面
import java.lang.reflect.Constructor;
import java.sql.*;
import java.util.Scanner;public class dataBases {String databasesName;String user;String password;public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getDatabasesName() {return databasesName;}public void setDatabasesName(String databasesName) {this.databasesName = databasesName;}public String getUser() {return user;}public void setUser(String user) {this.user = user;}public dataBases(String databasesName,String user,String password) {this.password = password;this.databasesName = databasesName;this.user = user;}//获取一个名字为databasesName的数据库信息,包含账户名称和密码public static Connection connectionUser(dataBases db) throws Exception {Class.forName("com.mysql.cj.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/"+db.getDatabasesName(),db.getUser(),db.getPassword());if (conn != null) {System.out.println("连接成功");}else{System.out.println("连接失败");}return conn;}//连接到db数据库public static void showTables(Connection conn,String dbName) throws SQLException {String sql="show tables";Statement stmt= conn.createStatement();ResultSet res=stmt.executeQuery(sql);while(res.next()){String table_name=res.getString("Tables_in_"+dbName);System.out.println(table_name);}}//展示db数据库的表public static void creteTables(Connection coon, String tableName) throws SQLException {Scanner scanner =new Scanner(System.in);System.out.println("输入创建的字段名称和类型,中间用逗号隔开,并且最后没有逗号");String dataType_Name=scanner.nextLine();String sql= "CREATE TABLE "+tableName+"("+dataType_Name+")";Statement stmt= coon.createStatement();stmt.execute(sql);}//创建一个表public static void deleteTables(Connection conn,String tableName){String sql="drop table "+tableName;try {Statement stmt= conn.createStatement();stmt.execute(sql);} catch (SQLException e) {System.out.println("删除失败");throw new RuntimeException(e);}}//删除一个表public static void selectTables(Connection conn,String tableName){String sql="select* from "+tableName;try {Statement stmt = conn.createStatement();ResultSet res=stmt.executeQuery(sql);ResultSetMetaData metaData =res.getMetaData();//获取结果集的元数据int columnCount=metaData.getColumnCount();//获取结果集中的列数// 打印列名for (int i = 1; i <= columnCount; i++) {System.out.print(metaData.getColumnName(i) + "\t");}System.out.println();while (res.next()) {// 打印每一列的值for (int i = 1; i <= columnCount; i++) {System.out.print(res.getString(i) + "\t");}System.out.println(); // 换行}System.out.println();while(res.next()){}} catch (SQLException e) {throw new RuntimeException(e);}}//查询表中的内容public static void changeTableName(Connection conn) throws Exception{Scanner scanner=new Scanner(System.in);System.out.println("请输入原来的表名");String oldTableName= scanner.next();System.out.println("请输入修改后的表名");String newTableName=scanner.next();String sql="RENAME TABLE "+oldTableName+" TO "+newTableName;Statement stmt= conn.createStatement();stmt.execute(sql);}//更改表名public static void userInterFace(Connection conn,dataBases db) throws Exception {String choice;Scanner scanner=new Scanner(System.in);while(true){System.out.println("1.创建表");System.out.println("2.展示所有表");System.out.println("3.删除表");System.out.println("4.操作表");System.out.println("5.修改表名");System.out.println("6.展示表的内容");System.out.println("0.退出");System.out.println("请输入你的选项");choice= scanner.next();if(choice.equals("1")){System.out.println("按0返回到上一步");System.out.println("请输入要创建的表的名称");String tableName=scanner.next();if(tableName.equals("0")){continue;}try {creteTables(conn,tableName);} catch (SQLException e) {throw new RuntimeException(e);}}else if(choice.equals("2")){System.out.println("展示已连接到的数据库的所有表");showTables(conn,db.getDatabasesName());}else if(choice.equals("3")){System.out.println("按0返回到上一步");System.out.println("请输入要删除的表的名称");String tableName=scanner.next();if(tableName.equals("0")){continue;}deleteTables(conn,tableName);}else if(choice.equals("4")) {System.out.println("以下是数据库中所有的表");showTables(conn,db.getDatabasesName());System.out.println("按0返回到上一步");System.out.println("请输入要操作的表的名称");String tableName = scanner.next();if(tableName.equals("0")){continue;}Class<?> tableClass = Class.forName(tableName);Constructor<?> constructor = tableClass.getDeclaredConstructor();//获取无参构造器Object tableObject=constructor.newInstance();//获取一个object实例tableOperate tableInstance=(tableOperate)tableObject;//转化为继承接口类类型operateInterFace.operate(conn,tableInstance);/* tableOperate instance= (tableOperate) tableClass.newInstance();if(instance==null){System.out.println("没有该表");break;}operateInterFace.operate(conn,instance);*//* if (tableName.equals("students")) {students students = new students();operateInterFace.operate(conn, students);} else if (tableName.equals("t1")) {System.out.println("无法操作");} else if (tableName.equals("phone_table")) {System.out.println("无法操作");} else {System.out.println("没有该表");break;}*/}else if(choice.equals("5")){changeTableName(conn);}else if(choice.equals("6")){System.out.println("请输入要查询的表的内容");String tableName=scanner.next();selectTables(conn,tableName);}else if(choice.equals("0")){break;} else{System.out.println("无效操作,请重试");}}}//数据库操作交互界面public static void showdataBases(Connection conn) throws Exception {String sql="show databases";Statement stmt =conn.createStatement();ResultSet res=stmt.executeQuery(sql);while(res.next()){System.out.println(res.getString("Database"));}}public static Connection driverConnection() throws Exception {Scanner scanner=new Scanner(System.in);Class.forName("com.mysql.cj.jdbc.Driver");System.out.println("请输入用户名");String user=scanner.next();System.out.println("请输入密码");String password=scanner.next();Connection conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/",user,password);if(conn!=null){System.out.println("连接成功!");}return conn;}//驱动连接public static void main(String[] args) throws Exception {Scanner scanner=new Scanner(System.in);while(true){String choice;System.out.println("1.连接到数据库");System.out.println("2.创建数据库");System.out.println("3.删除数据库");System.out.println("4.展示所有数据库");System.out.println("0.退出");System.out.println("请输入你的选项");choice=scanner.next();if(choice.equals("1")){System.out.println("输入要连接的数据库的名称");String databaseName=scanner.next();System.out.println("输入用户名称");String user=scanner.next();System.out.println("输入密码");String password=scanner.next();dataBases db=new dataBases(databaseName,user,password);//初始化数据库和连接信息Connection conn=connectionUser(db);//与初始化过后的数据库db进行连接userInterFace(conn,db);}else if(choice.equals("2")){Connection conn=driverConnection();System.out.println("请输入要创建的数据库的名称");String databaseName=scanner.next();String sql="CREATE DATABASE " +databaseName+" CHARACTER SET=utf8";Statement stmt=conn.createStatement();stmt.execute(sql);}else if(choice.equals("3")){Connection conn = driverConnection();System.out.println("请输入要删除的数据库的名称");String databaseName=scanner.next();String sql="drop database "+databaseName;Statement stmt=conn.createStatement();stmt.executeUpdate(sql);}else if(choice.equals("0")){break;}else if (choice.equals("4")){Connection conn=driverConnection();showdataBases(conn);}}}
}