java可视化界面操作数据库编程

天津理工大学实验报告

一、MIS系统

系统名称：学生信息管理系统

操作系统: Window8.1

Java环境; jdk 1.7.0_21 、jre 1.7.0_21

集成开发环境：MyEclipse Professional 2013

数据库软件版本：SQL Server 2010 连接驱动：jdbc-odbc

二、系统主要功能

本项目主要实现了java界面的登录功能，以及登陆后的增删改查功能，主要是对student表实现增加一条学生信息、删除一条学生信息记录、更新学生信息表，以及查询学生信息表的数据

三、数据库结构

1.数据库名称：SchoolDB

学院、成绩、家庭住址等学生基本信息，其中sname为主键且不为空。

四、关键功能界面截图

1.登陆界面

用户输入数据库中存在的用户名和密码后，便可登陆成功，否则提示密码错误

①正确的用户名，错误的密码

正确的用户名正确的密码后进入学生信息管理系统：

2.添加功能：输入界面中的所有数据之后，点击添加，即可向数据库中加

入一条学生记录

3.删除功能：输入姓名，即可把数据库中对应姓名的一条学生信息删除

4.更新功能：输入要修改学生的信息，即可修改该学生在数据库中的记录

5.查找功能：点击查找可以查询数据库中的所有学生信息，每页9条记录，其余的学生信息的点击下一页，可以查看，还可以点击上一页，来切换数据

点击下一页：

五、关键代码分析

DBConnetion用于连接数据库：

package edu;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBConnection {

public static Connection getConnection(){

Connection conn = null;

String driver = "sun.jdbc.odbc.JdbcOdbcDriver";

String url = "jdbc:odbc:schoolData";

String username = "sa";

String password = "123456";

try {

Class.forName(driver);

conn = DriverManager.getConnection(url,username,password); } catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

}

登陆的窗体类LoginFrame、JFrame:

package edu;

import java.awt.Container;

import java.awt.FlowLayout;

import java.awt.GridLayout;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.sql.Connection;

import java.sql.SQLException;

import javax.swing.JButton;

import javax.swing.JLabel;

import javax.swing.JOptionPane;

import javax.swing.JPanel;

import javax.swing.JPasswordField;

import javax.swing.JTextField;

import javax.swing.SwingUtilities;

public class Jswing implements ActionListener{

private LoginFrame frame;

private JLabel lname;

private JLabel lpassword;

private JTextField tname;

private JPasswordField tpassword;

private JButton byes;

private JButton bno;

private JPanel p1;

private JPanel p2;

private JPanel p3;

private String userName,password;

/**

* 初始化窗口数据

*/

public void initData() {

frame=new LoginFrame("登陆");

frame.setResizable(false);

frame.setLayout(new GridLayout(3, 2));

Container c=frame.getContentPane();

lname=new JLabel("用户名：");

lpassword=new JLabel("密码: ");

tname=new JTextField(8);

tpassword=new JPasswordField(8);

byes=new JButton("确定");

bno=new JButton("取消");

p1=new JPanel();

p2=new JPanel();

p3=new JPanel();

p1.add(lname);

p1.add(tname);

c.add(p1);

p2.add(lpassword);

p2.add(tpassword);

c.add(p2);

p3.add(byes);

p3.add(bno);

c.add(p3);

frame.setVisible(true);

byes.addActionListener(this);

bno.addActionListener(this);

userName=null;

password=null;

}

@Override

public void actionPerformed(ActionEvent e) {

if(e.getSource()==bno){

System.exit(1);

}else{

if(e.getSource()==byes){

userName=tname.getText().trim();

password=new String(tpassword.getPassword());

boolean result=false;

result=SchoolOperate.login(userName, password);

if(result){

frame.setVisible(false);

SwingUtilities.invokeLater(new Runnable() {

public void run() {

SchoolInfoGui application = new SchoolInfoGui();

application.getJFrame().setVisible(true);

}

});

}else{

String error=null;

if(result==false){

error="用户名或密码错误，请重试！";

}

JOptionPane.showMessageDialog(null, error);

}

}

}

}

public static void main(String[] args) {

new Jswing().initData();

}

}

package edu;

import java.awt.HeadlessException;

import javax.swing.JFrame;

public class LoginFrame extends JFrame{

public LoginFrame(String title) throws HeadlessException { super();

setTitle(title);

setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

setBounds(540,200, 280, 200);

}

}

学生信息管理系统主界面的SchoolInfoGui：

package edu;

import java.awt.Rectangle;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.util.List;

import javax.swing.JButton;

import javax.swing.JComboBox;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JOptionPane;

import javax.swing.JPanel;

import javax.swing.JScrollPane;

import javax.swing.JTable;

import javax.swing.JTextField;

import javax.swing.SwingUtilities;

public class SchoolInfoGui implements ActionListener {

private JFrame jFrame;

private JPanel jContentPane;

private JLabel lblName = null;

private JTextField tfName = null;

private JLabel lblRegion = null;

private JComboBox cbRegion = null;

private JLabel lblArea = null;

private JTextField tfArea = null;

private JLabel lblPopu = null;

private JTextField tfPopu = null;

private JLabel lblGdp = null;

private JTextField tfGDP = null;

private JButton btnAdd = null;

private JButton btnDel = null;

private JButton btnModify = null;

private JButton btnQuery = null;

private JButton btnUpPage = null;

private JButton btnDownPage = null;

private JTable table = null;

TableModel tm = null;

JScrollPane jsp = null;

private JTextField getTfName() {

if (tfName == null) {

tfName = new JTextField();

tfName.setBounds(new Rectangle(89, 17, 173, 27)); }

return tfName;

}

private JComboBox getCbRegion() {

if (cbRegion == null) {

String[] str = { "请选择", "计算机科学与技术", "信息安全", "网络工程", "信息与计算科学", "物流工程", "软件工程"};

cbRegion = new JComboBox(str);

cbRegion.setBounds(new Rectangle(360, 17, 229, 28));

}

return cbRegion;

}

private JTextField getTfArea() {

if (tfArea == null) {

tfArea = new JTextField();

tfArea.setBounds(new Rectangle(89, 61, 173, 27));

}

return tfArea;

}

private JTextField getTfPopu() {

if (tfPopu == null) {

tfPopu = new JTextField();

tfPopu.setBounds(new Rectangle(360, 61, 229, 28));

}

return tfPopu;

}

private JTextField getTfGDP() {

if (tfGDP == null) {

tfGDP = new JTextField();

tfGDP.setBounds(new Rectangle(89, 105, 173, 27));

}

return tfGDP;

}

private JButton getBtnAdd() {

if (btnAdd == null) {

btnAdd = new JButton();

btnAdd.setBounds(new Rectangle(46, 152, 71, 30));

btnAdd.addActionListener(this);

btnAdd.setText("添加");

}

return btnAdd;

}

private JButton getBtnUpPage() {

if (btnUpPage == null) {

btnUpPage = new JButton("上一页");

btnUpPage.addActionListener(this);

btnUpPage.setBounds(200, 390, 80, 30);

}

return btnUpPage;

}

private JButton getBtnDownPage() {

if (btnDownPage == null) {

btnDownPage = new JButton("下一页");

btnDownPage.addActionListener(this);

btnDownPage.setBounds(300, 390, 80, 30);

}

return btnDownPage;

}

private JButton getBtnDel() {

if (btnDel == null) {

btnDel = new JButton();

btnDel.setBounds(new Rectangle(189, 152, 71, 30));

btnDel.addActionListener(this);

btnDel.setText("删除");

}

return btnDel;

}

private JButton getBtnModify() {

if (btnModify == null) {

btnModify = new JButton();

btnModify.setBounds(new Rectangle(342, 152, 71, 30)); btnModify.addActionListener(this);

btnModify.setText("更新");

}

return btnModify;

}

private JButton getBtnQuery() {

if (btnQuery == null) {

btnQuery = new JButton();

btnQuery.setBounds(new Rectangle(484, 152, 71, 30)); btnQuery.addActionListener(this);

btnQuery.setText("查找");

}

return btnQuery;

}

JFrame getJFrame() {

if (jFrame == null) {

jFrame = new JFrame();

jFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); jFrame.setBounds(new Rectangle(450, 250, 610, 454)); jFrame.setContentPane(getJContentPane());

jFrame.setTitle("学生信息管理系统");

}

return jFrame;

}

private JPanel getJContentPane() {

if (jContentPane == null) {

lblGdp = new JLabel();

lblGdp.setBounds(new Rectangle(15, 106, 63, 27));

lblGdp.setText("家庭住址");

lblPopu = new JLabel();

lblPopu.setBounds(new Rectangle(285, 61, 63, 27));

lblPopu.setText("成绩");

lblArea = new JLabel();

lblArea.setBounds(new Rectangle(15, 61, 63, 27));

lblArea.setText("学院");

lblRegion = new JLabel();

lblRegion.setBounds(new Rectangle(285, 17, 63, 27)); lblRegion.setText("专业");

lblName = new JLabel();

lblName.setBounds(new Rectangle(15, 17, 63, 27));

lblName.setText("姓名");

jContentPane = new JPanel();

jContentPane.setLayout(null);

jContentPane.add(lblName, null);

jContentPane.add(getTfName(), null);

jContentPane.add(lblRegion, null);

jContentPane.add(getCbRegion(), null);

jContentPane.add(lblArea, null);

jContentPane.add(getTfArea(), null);

jContentPane.add(lblPopu, null);

jContentPane.add(getTfPopu(), null);

jContentPane.add(lblGdp, null);

jContentPane.add(getTfGDP(), null);

jContentPane.add(getBtnAdd(), null);

jContentPane.add(getBtnDel(), null);

jContentPane.add(getBtnModify(), null);

jContentPane.add(getBtnQuery(), null);

jContentPane.add(getBtnUpPage(), null);

jContentPane.add(getBtnDownPage(), null);

}

return jContentPane;

}

public void actionPerformed(ActionEvent e) {

if (e.getActionCommand().equals("查找")) {

selectAll();

} else if (e.getActionCommand().equals("下一页")) {

selectDownPage();

} else if (e.getActionCommand().equals("上一页")) {

selectUpPage();

} else if (e.getActionCommand().equals("添加")) {

if (tfName.getText().equals("")

|| cbRegion.getSelectedItem().equals("请选择")

|| tfArea.getText().equals("")

|| tfPopu.getText().equals("")

|| tfGDP.getText().equals("")) {

JOptionPane.showMessageDialog(jFrame, "请填写相关信息");

}else {

Student co = new Student();

co.setSname(tfName.getText());

co.setSpro((String) cbRegion.getSelectedItem());

co.setSdept(tfArea.getText());

co.setSgrade(tfPopu.getText());

co.setSaddress(tfGDP.getText());

boolean flag = SchoolOperate.add(co);

if (flag) {

JOptionPane.showMessageDialog(jFrame, "添加成功");

} else {

JOptionPane.showMessageDialog(jFrame, "添加失败");

}

}

} else if (e.getActionCommand().equals("删除")) {

if(!tfName.getText().equals("")){

String key = tfName.getText();

boolean flag = SchoolOperate.del(key);

if (flag) {

JOptionPane.showMessageDialog(jFrame, "删除成功");

} else {

JOptionPane.showMessageDialog(jFrame, "删除失败");

}

}else {

JOptionPane.showMessageDialog(jFrame, "请填写一个国家名字"); }

} else if (e.getActionCommand().equals("更新")) {

if (!tfName.getText().equals("")

&& !cbRegion.getSelectedItem().equals("请选择")

&& !tfArea.getText().equals("")

&& !tfPopu.getText().equals("")

&& !tfGDP.getText().equals("")) {

Student cou = new Student();

cou.setSname(tfName.getText());

cou.setSpro((String)cbRegion.getSelectedItem());

cou.setSdept(tfPopu.getText());

cou.setSgrade(tfArea.getText());

cou.setSaddress(tfGDP.getText());

boolean flag = SchoolOperate.modify(cou);

if(flag){

JOptionPane.showMessageDialog(jFrame, "更新成功");

}else {

JOptionPane.showMessageDialog(jFrame, "更新失败");

}

} else {

JOptionPane.showMessageDialog(jFrame, "请填写相应的信息"); }

}

}

public void setValues(Object[][] obj) {

this.tm.setDatavalues(obj);

}

public void selectAll() {

String spro=cbRegion.getSelectedItem().toString();

List list = SchoolOperate.queryFirst();

Object[][] datavalues = new Object[list.size()][5];

for (int i = 0; i < list.size(); i++) {

Student co = list.get(i);

datavalues[i][0] = co.getSname();

datavalues[i][1] = co.getSpro();

datavalues[i][2] = co.getSdept();

datavalues[i][3] = co.getSgrade();

datavalues[i][4] = co.getSaddress();

}

tm = new TableModel();

this.setValues(datavalues);

table = new JTable(tm);

table.updateUI();

JScrollPane jsp = new JScrollPane(table);

jsp.setBounds(0, 200, 600, 182);

jContentPane.add(jsp);

}

public void selectDownPage() {

List list = SchoolOperate.downPage();

if (list != null) {

Object[][] datavalues = new Object[list.size()][5];

for (int i = 0; i < list.size(); i++) {

Student co = list.get(i);

datavalues[i][0] = co.getSname();

datavalues[i][1] = co.getSpro();

datavalues[i][2] = co.getSdept();

datavalues[i][3] = co.getSgrade();

datavalues[i][4] = co.getSaddress();

}

this.setValues(datavalues);

table.updateUI();

} else {

JOptionPane.showMessageDialog(jFrame, "已经是最后一页"); }

}

public void selectUpPage() {

List list = SchoolOperate.upPage();

if (list != null) {

Object[][] datavalues = new Object[list.size()][5];

for (int i = 0; i < list.size(); i++) {

Student co = list.get(i);

datavalues[i][0] = co.getSname();

datavalues[i][1] = co.getSpro();

datavalues[i][2] = co.getSdept();

datavalues[i][3] = co.getSgrade();

datavalues[i][4] = co.getSaddress();

}

this.setValues(datavalues);

table.updateUI();

} else {

JOptionPane.showMessageDialog(jFrame, "已经是第一页"); }

}

}

针对数据库的增删改查操作方法类;

package edu;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

public class SchoolOperate {

static int i = 1;

static int x = 0;

public static List queryFirst() {

String sql = "select top 9 * from student order by sname";

List list = query(sql);

return list;

}

public static List downPage() {

int y = queryCount();

List list = null;

int count = 0; // 总页数

if (y % 5 == 0) {

count = y / 5;

} else {

count = y / 5 + 1;

}

if (i < count) {

x = ((++i) - 1) * 5;

String sql = "select top "

+ x

+ " * from (select * from student where sname not in(select top "

+ x + " sname from student order by sname)) as A ";

list = query(sql);

} else if (i >= count) {

list = null;

}

return list;

}

public static boolean login(String userName,String password){

boolean result=false;

Connection con = DBConnection.getConnection();

String sql="select passWord from login where userName=?"; PreparedStatement ps = null;

ResultSet rs = null;

try{

ps=con.prepareStatement(sql);

ps.setObject(1, userName);

rs=ps.executeQuery();

while(rs.next()){

if(password.equals(rs.getString("passWord").trim())){ result=true;

}

}

}catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

try{

if(rs!=null){

rs.close();

}

}catch(SQLException e){

e.printStackTrace();

}

try {

if(ps!=null){

ps.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

try{

if(con!=null){

con.close();

}

}catch(SQLException e){

e.printStackTrace();

}

return result;

}

public static int queryCount() {

int count = 0;

Connection conn = DBConnection.getConnection();

String sql = "select count(*) from student";

Statement stmt = null;

ResultSet rs = null;

try {

stmt = conn.createStatement();

rs = stmt.executeQuery(sql);

while (rs.next()) {

count = rs.getInt(1);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (rs != null)

rs.close();

if (stmt != null)

stmt.close();

if (conn != null)

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

return count;

}

public static List query(String sql) { Connection conn = DBConnection.getConnection(); List list = new ArrayList(); Statement stmt = null;

ResultSet rs = null;

try {

stmt = conn.createStatement();

rs = stmt.executeQuery(sql);

while (rs.next()) {

Student cou = new Student();

cou.setSname(rs.getString(1));

cou.setSpro(rs.getString(2));

cou.setSdept(rs.getString(3));

cou.setSgrade(rs.getString(4));

cou.setSaddress(rs.getString(5));

list.add(cou);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (rs != null)

rs.close();

if (stmt != null)

stmt.close();

if (conn != null)

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

return list;

}

public static List upPage() {

List list = null;

if (i > 2) {

x = ((--i) - 1) * 5;

String sql = "select top "

+ x

+ " * from (select * from student where sname not in(select top "

+ x + " sname from student order by sname)) as A ";

list = query(sql);

} else if (i == 2) {

list = queryFirst();

i--;

} else if (i < 2) {

list = null;

}

return list;

}

public static boolean add(Student cou) {

Connection conn = DBConnection.getConnection();

PreparedStatement pstmt = null;

String sql = "insert into student values(?,?,?,?,?)";

int x = 0;

try {

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, cou.getSname());

pstmt.setString(2, cou.getSpro());

pstmt.setString(3, cou.getSdept());