stubean.java

java编写的与SQL数据连接的好例子~~功能很完善,可以做实训直接交

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package stuMS;

import java.sql.*;
//import java.util.*;
import javax.swing.*;

/**
 * 有关学生信息数据库操作的类
 * @author Administrator
 */
public class StuBean {

    String sql;
    ResultSet results = null;
    String Sno;
    String Sname;
    String Ssex;
    String Sbirth;
    String Sethnic;
    String Shometown;
    String Sdate;
    String Smajor;
    String Scollege;
    String colName;//
    String colValue;//
    String colValue2;//
    /**
     * 添加学生记录
     * @param name
     * @param sex
     * @param birth
     * @param hometown
     * @param ethnic
     * @param year
     * @param major
     * @param college
     */
    public void stuAdd(String name, String sex, String birth, String ethnic, String hometown, String year, String major, String college) {
        Database DB = new Database();

        this.Sname = name;
        this.Ssex = sex;
        this.Sbirth = birth;
        this.Sethnic = ethnic;
        this.Shometown = hometown;        
        this.Sdate = year;
        this.Smajor = major;
        this.Scollege = college;

        if (Sname == null || Sname.equals("")) {
            JOptionPane.showMessageDialog(null, "请输入学生姓名", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        } else {
            System.out.println("新学号："+this.getNewStuId());
            sql = "insert into student(Sno,Sname,Ssex,Sbirth,Sethnic,Shometown,Sdate,Smajor,Scollege) " +
                    "values ('" + getNewStuId() + "','" + Sname + "','" + Ssex + "','" + Sbirth + "','" + Sethnic + "','" + Shometown + "','" + Sdate + "','" + Smajor + "','" + Scollege + "')";
            try {
                DB.OpenConn();
                DB.executeUpdate(sql);
                JOptionPane.showMessageDialog(null, "成功添加一条新的记录！");
            } catch (Exception e) {
                System.out.println(e);
                JOptionPane.showMessageDialog(null, "保存失败", "错误", JOptionPane.ERROR_MESSAGE);
            } finally {
                DB.closeStmt();
                DB.closeConn();
            }
        }
    }

    /**
     * 更新学生信息记录
     * @param num
     * @param name
     * @param sex
     * @param birth
     * @param hometown
     * @param ethnic
     * @param year
     * @param major
     * @param college
     */
    public void stuModify(String no, String name, String sex, String birth, String ethnic, String hometown, String year, String major, String college) {

        Database DB = new Database();

        this.Sno = no;
        this.Sname = name;
        this.Ssex = sex;
        this.Sbirth = birth;
        this.Sethnic = ethnic;
        this.Shometown = hometown;
        this.Sdate = year;
        this.Smajor = major;
        this.Scollege = college;

        if (Sname == null || Sname.equals("")) {
            JOptionPane.showMessageDialog(null, "请输入学生姓名", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        } else {
            sql = "update student set Sname = '" + Sname + "', Ssex = '" + Ssex + "', Sbirth = '" + Sbirth + "', Shometown = '" + Shometown + "', Sethnic = '" + Sethnic + "', Sdate = '" + Sdate + "', Smajor = '" + Smajor + "', Scollege = '" + Scollege + "' where Sno = " + Sno;
            try {
                DB.OpenConn();
                DB.executeUpdate(sql);
                JOptionPane.showMessageDialog(null, "成功修改一条记录！");
            } catch (Exception e) {
                System.out.println(e);
                JOptionPane.showMessageDialog(null, "更新失败", "错误", JOptionPane.ERROR_MESSAGE);
            } finally {
                DB.closeStmt();
                DB.closeConn();
            }
        }
    }

    public void stuDel(String no) {

        Database DB = new Database();
        this.Sno = no;

        sql = "delete from student where Sno = " + Sno + "";
        try {
            DB.OpenConn();
            DB.executeUpdate(sql);
            JOptionPane.showMessageDialog(null, "成功删除一条记录");
        } catch (Exception e) {
            System.out.println(e);
            JOptionPane.showMessageDialog(null, "删除失败", "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            DB.closeStmt();
            DB.closeConn();
        }
    }

    /**
     * 根据学号查询学生信息
     * @param no
     * @return
     */
    public String[] stuSearch(String no) {

        Database DB = new Database();
        this.Sno = no;
        String[] stu = new String[9];
        sql = "select * from student where Sno = " + Sno + "";

        try {
            DB.OpenConn();
            results = DB.executeQuery(sql);
            if (results.next()) {
                for(int i = 0;i < 9;i++)
                    stu[i] = results.getString(i+1);
            } else {
                stu = null;
            }
        } catch (Exception e) {
        } finally {
            DB.closeStmt();
            DB.closeConn();
        }
        return stu;
    }

    /**
     * 学生信息综合查询（按一个条件进行查询）
     * @param colname
     * @param colvalue
     * @return
     */
    public String[][] stuAllSearch(String colname, String colvalue) {
        this.colName = colname;
        this.colValue = colvalue;

        Database DB = new Database();
        String[][] stu = null;
        int row = 0;
        int i = 0;

        //DB.toGBK(colvalue);

        if (colValue == null || colValue.equals("")) {
            sql = "select * from student";
        } else {
            sql = "select * from student where " + colName + " = '" + colValue + "'";
        }
        try {
            DB.OpenConn();
            results = DB.executeQuery(sql);

            if (results.last()) {
                row = results.getRow();
            }

            if (row == 0) {
                stu = null;
            } else {
                stu = new String[row][9];
                results.first();
                results.previous();
                while (results.next()) {
                    stu[i][0] = results.getString("Sno");
                    stu[i][1] = results.getString("Sname");
                    stu[i][2] = results.getString("Ssex");
                    stu[i][3] = results.getString("Sbirth");
                    stu[i][4] = results.getString("Sethnic");
                    stu[i][5] = results.getString("Shometown");
                    stu[i][6] = results.getString("Sdate");
                    stu[i][7] = results.getString("Smajor");
                    stu[i][8] = results.getString("Scollege");
                    i++;
                }
            }
        } catch (Exception e) {
        } finally {
            DB.closeStmt();
            DB.closeConn();
        }
        return stu;
    }

    /**
     * 学生信息综合查询（查询某范围内的记录）
     * @param colname
     * @param colvalue
     * @param colvalue2
     * @return
     */
    public String[][] stuAllSearch(String colname, String colvalue, String colvalue2) {
        this.colName = colname;
        this.colValue = colvalue;
        this.colValue2 = colvalue2;

        Database DB = new Database();
        String[][] stu = null;
        int row = 0;
        int i = 0;
        sql = "select * from student where " + colName + " between " + colValue + " and " + colValue2 + "";
        try {
            DB.OpenConn();
            results = DB.executeQuery(sql);

            if (results.last()) {
                row = results.getRow();
            }

            if (row == 0) {
                stu = null;
            } else {
                stu = new String[row][9];
                results.first();
                results.previous();
                while (results.next()) {
                    stu[i][0] = results.getString("Sno");
                    stu[i][1] = results.getString("Sname");
                    stu[i][2] = results.getString("Ssex");
                    stu[i][3] = results.getString("Sbirth");
                    stu[i][4] = results.getString("Sethnic");
                    stu[i][5] = results.getString("Shometown");
                    stu[i][6] = results.getString("Sdate");
                    stu[i][7] = results.getString("Smajor");
                    stu[i][8] = results.getString("Scollege");
                    i++;
                }
            }
        } catch (Exception e) {
        } finally {
            DB.closeStmt();
            DB.closeConn();
        }
        return stu;
    }

    /**
     * 添加学生信息时，获得新的学号
     * @return
     */
    public String getNewStuId() {
        Database DB = new Database();
        sql = "select Sno from student";
        int no;
        String Sid = null;
        try {
            DB.OpenConn();
            results = DB.executeQuery(sql);
            results.first();
            results.previous();
            if(results.next())
                Sid = results.getString(1);
            while (results.next()) {
                if(Integer.parseInt(results.getString(1))==Integer.parseInt(Sid)+1){
                    Sid = results.getString(1);
                }
                else break;
            }            
                no = Integer.parseInt(Sid)+1;
                if(no == 0)
                    Sno = "00000";
                if (no < 10) {
                    Sno = "0000" + String.valueOf(no);
                    System.out.println(no+"新学号:"+Sno);
                }
                if (no >= 10 && no < 100) {
                    Sno = "000" + String.valueOf(no);
                }
                if (no >= 100 && no < 1000) {
                    Sno = "00" + String.valueOf(no);
                }
                if (no >= 1000 && no < 10000) {
                    Sno = "0" + String.valueOf(no);
                }
                if (no >= 10000) {
                    Sno = String.valueOf(no);
                }
        } catch (Exception e) {
        } finally {
            DB.closeStmt();
            DB.closeConn();
        }
        return Sno;
    }
    /**
     * 获得student表中所有学号Sno
     * @return
     */
    public String[] getAllId() {
        String[] stu = null;
        int row = 0;
        int i = 0;
        Database DB = new Database();
        sql = "select sno from student";

        try {
            DB.OpenConn();
            results = DB.executeQuery(sql);
            if (results.last()) {
                row = results.getRow();
            }

            if (row == 0) {
                stu = null;
            } else {
                stu = new String[row];
                results.first();
                results.previous();
                while (results.next()) {
                    stu[i] = results.getString(1);
                    i++;
                }
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            DB.closeStmt();
            DB.closeConn();
        }
        return stu;
    }
    /**
     * 获得所有学生信息
     * @return
     */
    public String [][]getAllStuInfo() {
        int row =0,i=0,j=0;
        String[][]stu = null;
        Database DB = new Database();
        sql = "select * from student";
        try {
            DB.OpenConn();
            results = DB.executeQuery(sql);    
            if (results.last()) {
                row = results.getRow();
            }
            if (row == 0) {
                stu =null;
                JOptionPane.showMessageDialog(null, "没有记录！");
                return null;
            } else {
                stu = new String[row][9];
                results.first();
                results.previous();
                while (results.next()) {
                    for (j = 0; j < 9; j++) {
                        stu[i][j] = results.getString(j+1);
                    }                    
                    i++;
                    j = 0;
                }
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            DB.closeStmt();
            DB.closeConn();
        }
    return stu;
    }
}