部分一(util):将常用代码封装方法的类,
包括:注册、建立连接对象、数据库处理对象、对象销毁
部分二(DAO):封装增删改查
部分三(Test):测试
空数据库:
部分一代码(数据库密码打上*了):
import java.sql.*;
//部分一:注册、建立连接对象、数据库处理对象、对象销毁
public class Student_table_Util {
private Connection conn = null;
private PreparedStatement ps = null;
//注册
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接对象
public void createCon(){
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","***");
} catch (SQLException e) {
e.printStackTrace();
}
}
//获取数据库操作对象
public PreparedStatement getPre(String sql){
this.createCon();
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}
//增删改的对象销毁(无ResultSet)
public void close(){
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//查操作的对象销毁
public void close(ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
this.close();
}
}
部分二代码:
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//部分二:对表增删改查
public class Student_table_DAO {
private Student_table_Util util = new Student_table_Util();
//增添数据操作
public int insert(String no,String name,String classno){
String sql = "insert into student_table(no,name,classno) values(?,?,?)";
PreparedStatement ps = util.getPre(sql);
int chance = 0;
try {
ps.setInt(1,Integer.valueOf(no));
ps.setString(2,name);
ps.setInt(3,Integer.valueOf(classno));
chance = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally{
util.close();
return chance;
}
}
//删除数据操作
public int del(String no){
String sql = "delete from student_table where no = ?";
PreparedStatement ps = util.getPre(sql);
int chance = 0;
try{
ps.setInt(1,Integer.valueOf(no));
chance = ps.executeUpdate();
} catch (SQLException e){
e.printStackTrace();
} finally{
util.close();
return chance;
}
}
//查找操作(全部)
public List<Student> sel(){
String sql = "select * from student_table";
PreparedStatement ps = util.getPre(sql);
List<Student> result = new ArrayList<>();
ResultSet rs = null;
try {
rs = ps.executeQuery();
while(rs.next()){
Student student = new Student(rs.getInt("no"),rs.getString("name"),rs.getInt("classno"));
result.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
util.close(rs);
return result;
}
}
//改
public int Update(String no,String name,String classno){
String sql = "update student_table set name=?,classno=? where no=?";
PreparedStatement ps =util.getPre(sql);
int chance = 0;
try {
ps.setString(1,name);
ps.setInt(2,Integer.valueOf(classno));
ps.setInt(3,Integer.valueOf(no));
chance = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally{
util.close();
return chance;
}
}
}
class Student{
int no;
String name;
int classno;
public Student(int no, String name, int classno) {
this.no = no;
this.name = name;
this.classno = classno;
}
public Student() {
}
}
部分三代码:
import java.util.List;
import java.util.Scanner;
public class Test {
public static void main(String[] args) {
Student_table_DAO std = new Student_table_DAO();
Scanner s = new Scanner(System.in);
while(true){
switch(print()){
case 1:{
List<Student> list = std.sel();
for(int i=0;i<list.size();i++){
System.out.println("学号:"+list.get(i).no+" 姓名:"+list.get(i).name+" 班级编号:"+list.get(i).classno);
}
break;
}
case 2:{
System.out.println("请依次输入新同学的学号,姓名,班级编号");
String no = s.next();
String name = s.next();
String classno = s.next();
int chance = std.insert(no,name,classno);
if(chance!=0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
break;
}
case 3:{
System.out.println("请要删除数据库中的同学的学号");
String no = s.next();
int chance = std.del(no);
if(chance!=0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
break;
}
case 5:{
System.out.println("请输入要修改数据的同学的学号");
String no = s.next();
System.out.println("请依次输入新的姓名和班级编号");
String name = s.next();
String classno = s.next();
int chance = std.Update(no,name,classno);
if(chance!=0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
break;
}
case 6:{
System.exit(1);
}
}
}
}
public static int print(){
System.out.println("******欢迎来到数据库管理系统******");
System.out.println("1:查看数据库");
System.out.println("2:增加数据");
System.out.println("3:删除数据");
System.out.println("5:改动数据");
System.out.println("6:退出系统");
Scanner scanner = new Scanner(System.in);
return scanner.nextInt();
}
}
测试截图:
增:
删:
改:
查: