java连接MYSQL，对数据库进行操作

 

DBUtil.java

package Stu;

import java.sql.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.lang.Exception;
import java.sql.Statement;

public class DBUtil {
    
    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    private static final String USER = "root";
    private static final String PASS = "123456";
    
         public static Connection getCon() throws ClassNotFoundException,SQLException {
                Connection con = null;

                Class.forName(DRIVER_NAME);

                con = DriverManager.getConnection(URL, USER, PASS);

                return con;
            }
         public static void close(Connection con, Statement stmt, ResultSet rs) {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (con != null) {
                        con.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
               /* StuManage s=new StuManage();
                s.menu();*/
         }
}

 

Main.java

package Stu;
//主函数测试类
public class Main {
    /**
     * @paramargs
     */
    public static void main(String[] args) {
        StuManage s=new StuManage();
        s.menu();
    }
}

 

Stu.java

package Stu;
//实体类，封装学生类数据
/**
 * @authorScatlett
 */
public class Stu {
private String no;  //学号
private String name;  //姓名
private String phone;  //手机号

//getter setter
    public String getNo() {
        return no;
    }
    public void setNo(String no) {
        this.no = no;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    //无参构造函数
    public Stu() {
        super();
        // TODO Auto-generated constructor stub
      }
    //有参构造函数
    public Stu(String no, String name, String phone) {
        super();
        this.no = no;
        this.name = name;
        this.phone = phone;
     }
}

 

 

StuManage.java

package Stu;
//学生信息管理系统的菜单选择
import java.sql.Connection;
import java.util.List;
import java.util.Scanner;

import javax.print.DocFlavor.INPUT_STREAM;

//import org.omg.CORBA.PUBLIC_MEMBER;

import studao.StuDao;

public class StuManage{
    public void  menu() {
        //1.打印菜单
        //2.输入菜单
        //3.switch菜单选择
        int choose;
        do {
            System.out.println("******************************");
            System.out.println("=======欢迎进入学生信息管理系统=======");
            System.out.println("1.新增学生");
            System.out.println("2.修改学生");
            System.out.println("3.删除学生");
            System.out.println("4.查询学生");
            System.out.println("5.退出该系统");
            System.out.println("请选择（1-5）：");
            
            Scanner scanner=new Scanner(System.in);
            choose=scanner.nextInt();
            System.out.println("******************************");
            switch (choose) {
            case 1:
                myAdd(); //菜单选择1，是新增学生
                break;
            case 2:
                myUpdate();  //菜单选择2，是修改学生
                break;
            case 3:
                myDel();  //菜单选择3，是删除学生
                break;
            case 4:
                myList();  //菜单选择4，是查询学生
                break;
            case 5:     //菜单选择5，是退出该系统
                System.out.println("您选择了退出系统，确定要退出吗？(y/n)");
                Scanner scan=new Scanner(System.in);
                String scanExit=scan.next();
                if(scanExit.equals("y")){
                System.exit(-1);
                System.out.println("您已成功退出系统，欢迎您再次使用！");
                }
                break;
            default:
                break;
            }
        } while (choose!=5);
    }
    
    //新增学生信息
    public void myAdd() {
        
        String continute;
        do {
            Scanner s=new Scanner(System.in);
            String no,name,phone;
            System.out.println("====新增学生====");
            System.out.println("学号：");
            no=s.next();
            System.out.println("姓名：");
            name=s.next();
            System.out.println("手机号：");
            phone=s.next();
            
            Stu stu=new Stu(no,name,phone);
            StuDao dao=new StuDao();
            boolean ok=dao.add(stu);
            if (ok) {
                System.out.println("保存成功！");
            }else {
                System.out.println("保存失败！");
            }
            System.out.println("是否继续添加(y/n)：");
            Scanner scanner2=new Scanner(System.in);
            continute=scanner2.next();
        } while (continute.equals("y"));
    }
    
    //删除学生信息
    public void myDel(){
        Scanner s=new Scanner(System.in);
        String no;
        System.out.println("====删除学生====");
        System.out.println("请输入要删除的学生学号：");
        no=s.next();
        System.out.println("该学生的信息如下：");
        
        StuDao stuDao=new StuDao();
        System.out.println("学生学号："+stuDao.findSomeone(no).getNo());
        System.out.println("学生姓名："+stuDao.findSomeone(no).getName());
        System.out.println("学生手机号："+stuDao.findSomeone(no).getPhone());
        
        System.out.println("是否真的删除(y/n)：");
        Scanner scanner3=new Scanner(System.in);
        String x=scanner3.next();
        if (x.equals("y")) {
            Stu stu=new Stu(no,null,null);
            StuDao dao=new StuDao();
            boolean ok=dao.del(no);
            if (ok) {
                System.out.println("删除成功！");
            }else {
                System.out.println("删除失败！");
            }
        }
    }
    //修改学生信息
    public void myUpdate(){
        Scanner s=new Scanner(System.in);
        String no;
        System.out.println("====修改学生====");
        System.out.println("请输入要修改的学生学号：");
        no=s.next();
        System.out.println("该学生的信息如下：");
        StuDao stuDao=new StuDao();
        System.out.println("学生学号："+stuDao.findSomeone(no).getNo());
        System.out.println("学生姓名："+stuDao.findSomeone(no).getName());
        System.out.println("学生手机号："+stuDao.findSomeone(no).getPhone());
        
        System.out.println("请输入新的学生信息：");
        Scanner stuUp=new Scanner(System.in);
        String name,phone;
        System.out.println("学生姓名：");
        name=stuUp.next();
        System.out.println("学生手机号：");
        phone=stuUp.next();
        Stu stu=new Stu(no,name,phone);
        StuDao dao=new StuDao();
        boolean ok=dao.update(stu);
        if (ok) {
            System.out.println("保存成功！");
        }else {
            System.out.println("保存失败！");
        }
    }
    //查询学生信息
    public void myList(){
        System.out.println("************************");
        System.out.println("====查询学生====");
        System.out.println("该学生的信息如下：");
        System.out.println("学号\t姓名\t手机号");
        StuDao stuDao=new StuDao();
        List<Stu> list=stuDao.list();
        for (Stu stuList:list) { //循环打印出查询结果
            System.out.println(stuList.getNo()+"\t"+stuList.getName()+"\t"+stuList.getPhone());
        }
        System.out.println("************************");
    }
}

 

StuDao.java

package studao;
//学生管理数据访问对象StuDao
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Stu.DBUtil;
import Stu.Stu;

public class StuDao {
    private Connection con;
    private PreparedStatement pstmt;
    private ResultSet rs;
    
    //添加学生信息
public boolean add(Stu stu) {
       String sql="insert into stu(stu_no,stu_name,phone) values(?,?,?)";
    try {
        con=DBUtil.getCon();
        pstmt=con.prepareStatement(sql);
        pstmt.setString(1, stu.getNo());
        pstmt.setString(2, stu.getName());
        pstmt.setString(3, stu.getPhone());
        pstmt.executeUpdate();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        return false;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    } finally{
        DBUtil.close(con, pstmt, rs);
    }
    return true;
}

//查看学生列表（1所有）
public List<Stu> list() {
       List<Stu> list=new ArrayList<Stu>();//是线性列表，ArrayList是
    
       String sql="select * from stu";
    
    try {
        con=DBUtil.getCon();
        pstmt=con.prepareStatement(sql);
//pstmt.executeUpdate();//用于增删改
        rs=pstmt.executeQuery();//用于查询
        while (rs.next()) {
            
//Stustu=new Stu(rs.getString("stu_no"),rs.getString("stu_name"),rs.getString("phone"));
            //上行写法亦可为：
            Stu stu=new Stu();
            stu.setNo(rs.getString("stu_no"));//取结果集里面学号这一列的值赋给
            stu.setName(rs.getString("stu_name"));
            stu.setPhone(rs.getString("phone"));
            
            list.add(stu);
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally{
        DBUtil.close(con, pstmt, rs);
    }
    return list;
}
//查看学生列表（2根据学生学号显示学生信息）
    public Stu findSomeone(String no) {
           Stu stu=null;
           String sql="select * from stu where stu_no=?";
        
        try {
            con=DBUtil.getCon();
            pstmt=con.prepareStatement(sql);
//pstmt.executeUpdate();//用于增删改
            pstmt.setString(1,no);
            rs=pstmt.executeQuery();//用于查询
            while (rs.next()) {
                
//Stustu=new Stu(rs.getString("stu_no"),rs.getString("stu_name"),rs.getString("phone"));
                //上行写法亦可为：
                stu=new Stu();
                stu.setNo(rs.getString("stu_no"));//取结果集里面学号这一列的值赋给
                stu.setName(rs.getString("stu_name"));
                stu.setPhone(rs.getString("phone"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBUtil.close(con, pstmt, rs);
        }
        return stu;
    }
//修改学生信息
public boolean update(Stu stu) {
       String sql="update stu set stu_name=?,phone=? wherestu_no=?";
    try {
        con=DBUtil.getCon();
        pstmt=con.prepareStatement(sql);
        pstmt.setString(3, stu.getNo());
        pstmt.setString(1, stu.getName());
        pstmt.setString(2, stu.getPhone());
        pstmt.executeUpdate();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        return false;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    } finally{
        DBUtil.close(con, pstmt, rs);
    }
    return true;
   }

//删除学生信息
public boolean del(String id) {
       String sql="delete from stu where stu_no=?";
    try {
        con=DBUtil.getCon();
        pstmt=con.prepareStatement(sql);
        pstmt.setString(1,id);
        
        pstmt.executeUpdate();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        return false;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    } finally{
        DBUtil.close(con, pstmt, rs);
    }
    return true;
   }
}