本系统采用GUI + Microsoft SQL Server 2008 R2的模式进行开发。GUI与Microsoft SQL Server 2008 R2之间通过java JDBC连接。
在SQL Server中新建University数据库,student表
系统登录流程图
eclipse项目结构
整个系统程序分为8个文件,放在同一个名为javaJDBC的包里,代码总行数为717行(包括注释),Referenced Libraries中存放sql驱动,images中存放软件图标及登录界面的图片。
源程序
SQLServerTest.java
package javaJDBC;
public class SQLServerTest {
public static void main(String[] args) {
new LoginFrame();
}
}
LoginFrame.java
package javaJDBC;
import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
public class LoginFrame extends JFrame implements ActionListener {
private static final long serialVersionUID = 1L;
JLabel userName,userPwd,tubiao;
JTextField nameField;
JPasswordField pwdField;
JButton button;
JPanel panel;
LoginFrame(){
//图片在原有基础上要调整大小,使其适应窗口
tubiao = new JLabel(new ImageIcon("images/1.jpeg"));
//设置各组件布局
add(tubiao,BorderLayout.NORTH);
userName = new JLabel("账号",JLabel.CENTER);
userPwd = new JLabel("密码",JLabel.CENTER);
nameField = new JTextField(8);
pwdField = new JPasswordField(8);
panel = new JPanel();
panel.setLayout(new GridLayout(2,2));
panel.add(userName);
panel.add(nameField);
panel.add(userPwd);
panel.add(pwdField);
add(panel,BorderLayout.CENTER);
button = new JButton("登录");
add(button,BorderLayout.SOUTH);
//事件监听
button.addActionListener(this);
//小图标
ImageIcon tubiao = new ImageIcon("images/2.jpg");
setIconImage(tubiao.getImage());
//点关闭则退出程序
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
//设置窗口可见
setVisible(true);
//不允许修改计算器窗口的大小
setResizable(false);
//设置窗口位置及大小
setBounds(400,150,450,350);
//窗口标题
setTitle("登录界面");
}
//事件处理
public void actionPerformed(ActionEvent e) {
String name,pwd;
name = nameField.getText();
//char[]转换成String
pwd = String.valueOf(pwdField.getPassword());
//动态加载驱动
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //加载数据库驱动
}
catch(ClassNotFoundException ex) {
System.out.println(ex);
}
try {
//连接数据库的语句
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=University";
//建立数据库连接
DriverManager.getConnection(url,name,pwd);
//显示提示信息
JOptionPane.showMessageDialog(this, "登录成功!","消息对话框",JOptionPane.WARNING_MESSAGE);
//清空窗口
this.dispose();
//打开新的窗口
new CommonFrame();
}
catch(SQLException exp) {
System.out.println(exp);
JOptionPane.showMessageDialog(this, "账号或者密码错误!","消息对话框",JOptionPane.WARNING_MESSAGE);
}
}
}
CommonFrame.java
package javaJDBC;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
public class CommonFrame extends JFrame implements ActionListener {
private static final long serialVersionUID = 1L;
JMenuBar bar;
JMenu menu;
JMenuItem scanItem,deleteItem,updateItem,insertItem,searchItem;
Scan scan; //查看所有学生信息
Delete delete;//删除学生信息
Update update;//更新学生信息
Insert insert;//插入学生信息
Search search;//查找学生信息
CardLayout card;
JPanel pCenter;
CommonFrame() {
//容器尺寸变化时,组件的相对位置不变,大小变化
setLayout(new GridLayout());
//创建菜单项
scanItem = new JMenuItem("浏览");
deleteItem = new JMenuItem("删除");
updateItem = new JMenuItem("修改");
insertItem = new JMenuItem("添加");
searchItem = new JMenuItem("查找");
//菜单条
bar = new JMenuBar();
//菜单
menu = new JMenu("菜单");
menu.add(scanItem);
menu.add(deleteItem);
menu.add(updateItem);
menu.add(insertItem);
menu.add(searchItem);
bar.add(menu);
//将菜单添加到容器中
setJMenuBar(bar);
scanItem.addActionListener(this);
deleteItem.addActionListener(this);
updateItem.addActionListener(this);
insertItem.addActionListener(this);
searchItem.addActionListener(this);
scan = new Scan();
update = new Update();
delete = new Delete();
insert = new Insert();
search = new Search();
//实现多个组件在同一容器区域内交替显示,一张卡片空间中只能显示一个组件
card = new CardLayout();
pCenter = new JPanel();
//以卡片布局添加各组件
pCenter.setLayout(card);
pCenter.add("scanItem",scan);
pCenter.add("deleteItem",delete);
pCenter.add("updateItem",update);
pCenter.add("insertItem",insert);
pCenter.add("searchItem",search);
add(pCenter,BorderLayout.SOUTH);
//小图标
ImageIcon tubiao = new ImageIcon("images/2.jpg");
setIconImage(tubiao.getImage());
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setVisible(true);
setBounds(400,150,1100,400);
setTitle("学生信息管理系统");
//验证布局使其有效
validate();
}
public void actionPerformed(ActionEvent e){
//菜单项按下后显示对应界面
if(e.getSource() == scanItem) {
card.show(pCenter, "scanItem");
//更新表格内容
scan.updateTable();
}
else if(e.getSource() == deleteItem)
card.show(pCenter, "deleteItem");
else if(e.getSource() == updateItem)
card.show(pCenter, "updateItem");
else if(e.getSource() == insertItem)
card.show(pCenter, "insertItem");
else if(e.getSource() == searchItem)
card.show(pCenter, "searchItem");
}
}
Scan.java
package javaJDBC;
import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.sql.*;
public class Scan extends JPanel {
private static final long serialVersionUID = 1L;
DefaultTableModel update_table;
JTable table;
Query query;
Object a[][];
String b[];
Scan() {
setLayout(new GridLayout());
query = new Query();
a = query.getRecord();
b = query.getField();
update_table = new DefaultTableModel(a, b);
table = new JTable(update_table);
//表格在滚动面板中才能显示表头
JScrollPane scrollPane = new JScrollPane(table);
scrollPane.setBounds(0,0,1000,380);
//使表格出现滑动条
table.setPreferredSize(new Dimension(scrollPane.getWidth(), scrollPane.getHeight()*2));
add(scrollPane);
}
public void updateTable() {
a = null;
b = null;
query = new Query();
a = query.getRecord();
b = query.getField();
update_table.setDataVector(a, b);
}
}
class Query {
int columnNum,rowNum;
Connection con;
public Query() {
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch(ClassNotFoundException e) {
System.out.println(e);
}
try {
String url,userName,userPwd;
url = "jdbc:sqlserver://localhost:1433;DatabaseName=University";
userName = "sa";
userPwd = "138888";
con = DriverManager.getConnection(url,userName,userPwd);
getColumnNum();
getRowNum();
}
catch(SQLException e) {
System.out.println(e);
}
}
//求表格的内容
public Object[][] getRecord(){
Object a[][] = null;
try {
a = new Object[rowNum][columnNum];
Statement sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = sql.executeQuery("select * from student");
int m = 0;
while(rs.next()) {
for(int k = 1;k <= columnNum;k++){
a[m][k-1] = rs.getString(k);
}
m++;
}
}
catch(SQLException e) {
System.out.println(e);
}
return a;
}
//获取表格中的学号
public String[] getId(){
String[] s = null;
try {
s = new String[rowNum];
Statement sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = sql.executeQuery("select 学号 from student");
int m = 0;
while(rs.next()) {
s[m] = rs.getString(1);
m++;
}
con.close();
}
catch(SQLException e) {
System.out.println(e);
}
return s;
}
//返回列的名称
public String[] getField(){
String b[]=null;
try{
DatabaseMetaData metadata = con.getMetaData();
ResultSet rs = metadata.getColumns(null, null, "student", null);
b = new String[columnNum];
int k = 0;
while(rs.next()) {
//获取列名
b[k] = rs.getString(4);
k++;
}
//断开数据库连接
con.close();
}
catch(SQLException e) {
System.out.println(e);
}
return b;
}
//求行数
public void getRowNum() {
try{
//可定位内容,但不能更新
Statement sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = sql.executeQuery("select * from student");
//将光标定位到最后一行
rs.last();
//返回当前所在行
rowNum = rs.getRow();
}
catch(SQLException e) {
System.out.println(e);
}
}
//求列数
public void getColumnNum(){
try{
DatabaseMetaData metadata = con.getMetaData();
//结果集中每行分别是各列的信息
ResultSet rs = metadata.getColumns(null, null, "student", null);
columnNum = 0;
while(rs.next())
columnNum++;
}
catch(SQLException e){
System.out.println(e);
}
}
}
Delete.java
package javaJDBC;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class Delete extends JPanel implements ActionListener {
private static final long serialVersionUID = 1L;
Box box1,box2,baseBox;
JButton button;
JTextField field;
Delete(){
//居中对齐,当容器的尺寸变化时,组件的大小不会改变,但布局会发生变化
setLayout(new FlowLayout());
//创建从上至下分布的盒容器
box1 = Box.createVerticalBox();
box2 = Box.createVerticalBox();
box1.add(new JLabel("学号"));
//设置盒内组件的上下间距
box1.add(Box.createVerticalStrut(35));
field = new JTextField(10);
box2.add(field);
box2.add(Box.createVerticalStrut(10));
box1.add(new JLabel(""));
button = new JButton("删除");
button.addActionListener(this);
box2.add(button);
//创建从左至右分布的盒容器
baseBox = Box.createHorizontalBox();
baseBox.add(box1);
//设置盒内组件的左右间距
baseBox.add(Box.createHorizontalStrut(5));
baseBox.add(box2);
add(baseBox);
}
public void actionPerformed(ActionEvent e) {
String id = field.getText();
//学号文本框输入为空
if(id.equals(""))
JOptionPane.showMessageDialog(this, "请输入学号","消息对话框",JOptionPane.WARNING_MESSAGE);
else {
//在此操作前表格内容可能已经更新,rowNum将改变,需要重新构建Query对象
Query query = new Query();
String[] s = query.getId();
int j;
for(j=0;j<query.rowNum;j++) {
if(id.equals(s[j]))
break;
}
//输入的学号在系统中不存在
if(j == query.rowNum)
JOptionPane.showMessageDialog(this, "无此人信息","消息对话框",JOptionPane.WARNING_MESSAGE);
else {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch(ClassNotFoundException exp) {
System.out.println(exp);
}
try {
String url,userName,userPwd;
url = "jdbc:sqlserver://localhost:1433;DatabaseName=University";
userName = "sa";
userPwd = "138888";
Connection con = DriverManager.getConnection(url,userName,userPwd);
//执行SQL语句
PreparedStatement sql = con.prepareStatement("DELETE FROM student WHERE 学号=?;");
//参数赋值
sql.setString(1, id);
sql.executeUpdate();
con.close();
JOptionPane.showMessageDialog(this, "删除成功","消息对话框",JOptionPane.WARNING_MESSAGE);
}
catch(SQLException ex){
System.out.println(ex);
}
}
//清空文本框,以便下一次输入
field.setText(null);
}
}
}
Update.java
package javaJDBC;
import java.awt.FlowLayout;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class Update extends JPanel implements ActionListener {
private static final long serialVersionUID = 1L;
Box box1,box2,baseBox;
JButton button;
JTextField[] field;
String[] b;
int n,mark;
Update() {
setLayout(new FlowLayout());
b = new Query().getField();
box1 = Box.createVerticalBox();
box2 = Box.createVerticalBox();
n = b.length;
field = new JTextField[n];
for(int i=0;i<n;i++) {
field[i]=new JTextField(10);
//学号标签前加*
if(b[i].equals("学号"))
box1.add(new JLabel("* " + b[i]));
else
box1.add(new JLabel(" " + b[i]));
box1.add(Box.createVerticalStrut(15));
box2.add(field[i]);
box2.add(Box.createVerticalStrut(10));
}
box1.add(new JLabel(" (带*必填)"));
button=new JButton("修改");
button.addActionListener(this);
box2.add(button);
baseBox=Box.createHorizontalBox();
baseBox.add(box1);
baseBox.add(Box.createHorizontalStrut(5));
baseBox.add(box2);
add(baseBox);
}
public void actionPerformed(ActionEvent e) {
int i,j;
//学号文本框输入为空
for(i=0;i<n;i++) {
if(b[i].equals("学号") && field[i].getText().equals("")) {
//在列名中标记学号索引
mark=i;
JOptionPane.showMessageDialog(this, "带*必须填写!","消息对话框",JOptionPane.WARNING_MESSAGE);
break;
}
}
if(i == n) {
String id = field[mark].getText();
Query query = new Query();
String[] a = query.getId();
for(j=0;j<query.rowNum;j++) {
if(id.equals(a[j]))
break;
}
//输入的学号在系统中不存在
if(j == query.rowNum)
JOptionPane.showMessageDialog(this, "无此人信息","消息对话框",JOptionPane.WARNING_MESSAGE);
else {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch(ClassNotFoundException exp) {
System.out.println(exp);
}
try {
String url,userName,userPwd;
url = "jdbc:sqlserver://localhost:1433;DatabaseName=University";
userName = "sa";
userPwd = "138888";
Connection con = DriverManager.getConnection(url,userName,userPwd);
Statement sql = con.createStatement();
String SQL;
for(int k=0;k<n;k++) {
//更新除学号外的信息,输入为空的列不更新
if(k != mark && field[k].getText().equals("") == false) {
SQL = "update student set " + b[k] + "='" + field[k].getText() + "' where 学号='" + id.toString() + "';";
sql.executeUpdate(SQL);
}
}
con.close();
JOptionPane.showMessageDialog(this, "修改成功","消息对话框",JOptionPane.WARNING_MESSAGE);
}
catch(SQLException ex){
System.out.println(ex);
}
}
//清空文本框,以便下一次输入
for(int k=0;k<field.length;k++)
field[k].setText(null);
}
}
}
Insert.java
package javaJDBC;
import java.awt.FlowLayout;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class Insert extends JPanel implements ActionListener {
private static final long serialVersionUID = 1L;
Box box1,box2,baseBox;
JButton button;
JTextField[] field;
String[] a;
int n,mark;
Insert() {
setLayout(new FlowLayout());
a = new Query().getField();
box1 = Box.createVerticalBox();
box2 = Box.createVerticalBox();
//列数
n = a.length;
field = new JTextField[n];
for(int i=0;i<n;i++){
field[i]=new JTextField(10);
if(a[i].equals("学号") == true)
box1.add(new JLabel("* "+a[i]));
else
box1.add(new JLabel(" "+a[i]));
box1.add(Box.createVerticalStrut(15));
box2.add(field[i]);
box2.add(Box.createVerticalStrut(10));
}
box1.add(new JLabel(" (带*必填)"));
button=new JButton("添加");
button.addActionListener(this);
box2.add(button);
baseBox=Box.createHorizontalBox();
baseBox.add(box1);
baseBox.add(Box.createHorizontalStrut(5));
baseBox.add(box2);
add(baseBox);
}
public void actionPerformed(ActionEvent e) {
int i;
for(i=0;i<n;i++) {
if(a[i].equals("学号") == true && field[i].getText().equals("") == true) {
mark=i;
JOptionPane.showMessageDialog(this, "带*必须填写!","消息对话框",JOptionPane.WARNING_MESSAGE);
break;
}
}
if(i == n) {
String id = field[mark].getText();
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch(ClassNotFoundException exp) {
System.out.println(exp);
}
try {
String url,userName,userPwd;
url = "jdbc:sqlserver://localhost:1433;DatabaseName=University";
userName = "sa";
userPwd = "138888";
Connection con = DriverManager.getConnection(url,userName,userPwd);
Statement sql = con.createStatement();
//添加时除学号外,其它信息输入可以为空
//先利用输入的学号添加一行信息
String SQL = "insert into student(学号) values('" + id + "');";
sql.executeUpdate(SQL);
//再利用update语句更新信息,输入为空的列不更新
for(int j=0;j<n;j++) {
if(j != mark && field[j].getText().equals("") == false) {
SQL = "update student set " + a[j] + "='" + field[j].getText() + "' where 学号='" + id + "';";
sql.executeUpdate(SQL);
}
}
con.close();
JOptionPane.showMessageDialog(this, "添加成功","消息对话框",JOptionPane.WARNING_MESSAGE);
//清空文本框,以便下一次输入
for(int j=0;j<field.length;j++)
field[j].setText(null);
}
catch(SQLException ex) {
System.out.println(ex);
}
}
}
}
Search.java
package javaJDBC;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
public class Search extends JPanel implements ActionListener {
private static final long serialVersionUID = 1L;
Box box1,box2,box3,baseBox;
JButton button;
JTextField field;
DefaultTableModel update_table;
JTable table;
Query query;
Object a[][];
String b[];
Search(){
setLayout(new GridLayout());
box1 = Box.createVerticalBox();
box2 = Box.createVerticalBox();
box1.add(new JLabel("学号"));
box1.add(Box.createVerticalStrut(35));
field = new JTextField(10);
box2.add(field);
box2.add(Box.createVerticalStrut(10));
box1.add(new JLabel(""));
button = new JButton("查找");
button.addActionListener(this);
box2.add(button);
box3 = Box.createHorizontalBox();
box3.add(box1);
box3.add(Box.createHorizontalStrut(5));
box3.add(box2);
query = new Query();
a = new Object[1][query.columnNum];
b = query.getField();
update_table = new DefaultTableModel(a, b);
table = new JTable(update_table);
JScrollPane scrollPane = new JScrollPane(table);
table.setPreferredSize(new Dimension(1100,50));
baseBox = Box.createVerticalBox();
baseBox.add(box3);
baseBox.add(Box.createVerticalStrut(10));
baseBox.add(scrollPane);
add(baseBox);
//验证表格布局使其有效
table.revalidate();
}
public void actionPerformed(ActionEvent e) {
String id = field.getText();
//学号文本框输入为空
if(id.equals(""))
JOptionPane.showMessageDialog(this, "请输入学号","消息对话框",JOptionPane.WARNING_MESSAGE);
else {
Query queryI = new Query();
String[] s = queryI.getId();
int j;
for(j=0;j<queryI.rowNum;j++) {
if(id.equals(s[j]))
break;
}
//输入的学号在系统中不存在
if(j == queryI.rowNum)
JOptionPane.showMessageDialog(this, "无此人信息","消息对话框",JOptionPane.WARNING_MESSAGE);
else {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch(ClassNotFoundException exp) {
System.out.println(exp);
}
try {
String url,userName,userPwd;
url = "jdbc:sqlserver://localhost:1433;DatabaseName=University";
userName = "sa";
userPwd = "138888";
Connection con = DriverManager.getConnection(url,userName,userPwd);
Statement sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = sql.executeQuery("select * from student where 学号='" + id + "';");
while(rs.next()) {
//表格内容更新不会改变列数
for(int k = 1;k <= query.columnNum;k++){
a[0][k-1] = rs.getString(k);
}
}
con.close();
//更新表格内容
update_table.setDataVector(a, b);
}
catch(SQLException ex){
System.out.println(ex);
}
}
//清空文本框,以便下一次输入
field.setText(null);
}
}
}
系统展示
登录界面
浏览
删除
修改
添加
查找