JAVA通过JDBC连接SQL Server数据库并且实现增、删、减、查等功能。
设置图形界面GUI访问数据库。
package Test1;
import java.sql.*;
import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.event.*;
import javax.swing.*;
public class JDBC {
static Connection conn =null;
static Statement stmt =null;
static ResultSet rs =null;
static PreparedStatement prestmt=null;
//接收查询结果
static String no;
static String name;
static int age_1;
static String dept;
static String []No=new String[100];
static String []Name=new String[100];
static int []Age=new int[100];
static String []Dept=new String[100];
static int Len=0;
static int NPC=0;
static int flag=0; //判断是否连接
private static void creatAndShowGUI() {
JFrame f = new JFrame("SQL Server");
f.setLayout(new FlowLayout(FlowLayout.LEFT, 400, 30));
f.setSize(900, 500);
f.setLocation(300, 200);
f.setVisible(true);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JButton btn0 = new JButton("连接");
JButton btn1 = new JButton("插入");
JButton btn2 = new JButton("修改");
JButton btn3 = new JButton("删除");
JButton btn4 = new JButton("查询");
JButton btn5 = new JButton("输出");
JButton btn6 = new JButton("退出");
f.add(btn0);
f.add(btn1);
f.add(btn2);
f.add(btn3);
f.add(btn4);
f.add(btn5);
f.add(btn6);
// ============================连接SQL
btn0.addActionListener(e -> {
if (flag == 0) {
try {
Lianjie();
flag = 1;// 记录已经连接
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 连接数据库成功！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} else {
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(250, 150);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 数据库已经连接了，不要重复操作！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});// ============================连接SQL
// ===============================插入
btn1.addActionListener(e -> {
if (flag == 1) {
JFrame tf = new JFrame("插入");
tf.setSize(500, 300);
tf.setLocation(300, 200);
tf.setVisible(true);
tf.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
JPanel panel = new JPanel(new GridLayout(6, 2, 0, 10));
JTextField Sno = new JTextField(20);
JLabel label_Sno = new JLabel("Sno");
JTextField Sname = new JTextField(20);
JLabel label_Sname = new JLabel("Sname");
JTextField Sage = new JTextField(20);
JLabel label_Sage = new JLabel("Sage");
JTextField Sdept = new JTextField(20);
JLabel label_Sdept = new JLabel("Sdept");
JButton btn = new JButton("确定");
btn.addActionListener(o -> {
String sno = Sno.getText();
String sname = Sname.getText();
String sage = Sage.getText();
String sdept = Sdept.getText();
if (sno != null && !sno.trim().equals("")) {
int age;
if (sage != null && !sage.trim().equals("")) {
age = Integer.valueOf(sage);
} else {
age = -1;// 表示年龄未知
}
try {
Insert(sno, sname, age, sdept);
JDialog dialog = new JDialog(tf, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 插入成功！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} else {
JDialog dialog = new JDialog(tf, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 主码不能为空！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});
panel.add(label_Sno);
panel.add(Sno);
panel.add(label_Sname);
panel.add(Sname);
panel.add(label_Sage);
panel.add(Sage);
panel.add(label_Sdept);
panel.add(Sdept);
panel.add(btn);
tf.add(panel, BorderLayout.PAGE_END);
} else {
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 需要先连接！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});// ===============插入
// ==================修改名字
btn2.addActionListener(e -> {
if (flag == 1) {
JFrame tf = new JFrame("修改");
tf.setSize(500, 300);
tf.setLocation(300, 200);
tf.setVisible(true);
tf.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
JPanel panel = new JPanel(new GridLayout(3, 2, 0, 70));
JTextField Sno = new JTextField(20);
JLabel label_Sno = new JLabel("要修改的Sno:");
JTextField Sname = new JTextField(20);
JLabel label_Sname = new JLabel("修改后的Sname:");
JButton btn = new JButton("提交");
// 提交
btn.addActionListener(o -> {
String sno = Sno.getText();
String sname = Sname.getText();
if (sno != null && !sno.trim().equals("")) {
JFrame dialog = new JFrame("提示");
dialog.setLayout(new FlowLayout(FlowLayout.LEFT, 65, 30));
dialog.setSize(300, 200);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 确定修改吗？");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
JButton yes = new JButton("Yes");
JButton no = new JButton("no");
dialog.add(yes);
dialog.add(no);
// 确定修改
yes.addActionListener(q -> {
dialog.dispose();
try {
Update(sno, sname); // 将上一个弹出窗口自动关闭
if (NPC == 1) {
JDialog dialog_1 = new JDialog(tf, "提示", true);
dialog_1.setSize(200, 100);
dialog_1.setLocation(500, 300);
JLabel label_1 = new JLabel(" 修改成功！");
dialog_1.add(label_1);
dialog_1.setVisible(true);
dialog_1.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
NPC = 0;
} else {
JDialog dialog_1 = new JDialog(tf, "提示", true);
dialog_1.setSize(200, 100);
dialog_1.setLocation(500, 300);
JLabel label_1 = new JLabel(" 学号不存在！");
dialog_1.add(label_1);
dialog_1.setVisible(true);
dialog_1.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
});
// 不修改
no.addActionListener(q -> {
dialog.dispose();
});
} else {
JDialog dialog = new JDialog(tf, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 主码不能为空！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});
panel.add(label_Sno);
panel.add(Sno);
panel.add(label_Sname);
panel.add(Sname);
panel.add(btn);
tf.add(panel, BorderLayout.PAGE_END);
} else {
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 需要先连接！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});// =============================修改
// =================================按学号删除
btn3.addActionListener(e -> {
if (flag == 1) {
JFrame tf = new JFrame("删除");
tf.setSize(500, 300);
tf.setLocation(300, 200);
tf.setVisible(true);
tf.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
JPanel panel = new JPanel(new GridLayout(2, 2, 0, 100));
JTextField Sno = new JTextField(20);
JLabel label_Sno = new JLabel("要删除的Sno:");
JButton btn = new JButton("提交");
// 提交
btn.addActionListener(o -> {
String sno = Sno.getText();
if (sno != null && !sno.trim().equals("")) {
JFrame dialog = new JFrame("提示");
dialog.setLayout(new FlowLayout(FlowLayout.LEFT, 65, 30));
dialog.setSize(300, 200);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 确定删除吗？");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
JButton yes = new JButton("Yes");
JButton no = new JButton("no");
dialog.add(yes);
dialog.add(no);
// 确定修改
yes.addActionListener(q -> {
dialog.dispose();
try {
Delete(sno); // 将上一个弹出窗口自动关闭
if (NPC == 1) {
JDialog dialog_1 = new JDialog(tf, "提示", true);
dialog_1.setSize(200, 100);
dialog_1.setLocation(500, 300);
JLabel label_1 = new JLabel(" 删除成功！");
dialog_1.add(label_1);
dialog_1.setVisible(true);
dialog_1.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
NPC = 0;
} else {
JDialog dialog_1 = new JDialog(tf, "提示", true);
dialog_1.setSize(200, 100);
dialog_1.setLocation(500, 300);
JLabel label_1 = new JLabel(" 该学生不存在！");
dialog_1.add(label_1);
dialog_1.setVisible(true);
dialog_1.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
});
// 不修改
no.addActionListener(q -> {
dialog.dispose();
});
} else {
JDialog dialog = new JDialog(tf, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 删除不能为空！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});
panel.add(label_Sno);
panel.add(Sno);
panel.add(btn);
tf.add(panel, BorderLayout.PAGE_END);
} else {
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 需要先连接！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});// =================================删除
// ======================================查询
btn4.addActionListener(e -> {
if (flag == 1) {
JFrame tf = new JFrame("查询");
tf.setSize(500, 300);
tf.setLocation(300, 200);
tf.setVisible(true);
tf.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
JTextArea showarea = new JTextArea(12, 34);
JScrollPane scrollpane = new JScrollPane(showarea);
showarea.setEditable(false);
JPanel panel = new JPanel();
JTextField Sno = new JTextField(20);
JLabel label_Sno = new JLabel("Sno");
JButton btn = new JButton("查询");
showarea.append("学号\t姓名\t年龄\t系别\n");
btn.addActionListener(o -> {
String sno = Sno.getText();
if (sno != null && !sno.trim().equals("")) {
try {
findUser(sno);
if (NPC == 1) {
String age = Integer.toString(age_1);
showarea.append(no);
showarea.append("\t");
showarea.append(name);
showarea.append("\t");
showarea.append(age);
showarea.append("\t");
showarea.append(dept);
showarea.append("\n");
NPC = 0;
} else {
JDialog dialog_1 = new JDialog(tf, "提示", true);
dialog_1.setSize(200, 100);
dialog_1.setLocation(500, 300);
JLabel label_1 = new JLabel(" 该学生不存在！");
dialog_1.add(label_1);
dialog_1.setVisible(true);
dialog_1.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} else {
JDialog dialog = new JDialog(tf, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 主码不能为空！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});
panel.add(label_Sno);
panel.add(Sno);
panel.add(btn);
tf.add(scrollpane, BorderLayout.PAGE_START);
tf.add(panel, BorderLayout.PAGE_END);
} else {
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 需要先连接！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});// ======================查询
// ============================输出
btn5.addActionListener(e -> {
if (flag == 1) {
JFrame tf = new JFrame("SQL Server");
tf.setSize(500, 300);
tf.setLocation(300, 200);
tf.setVisible(true);
tf.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
JTextArea showarea = new JTextArea(12, 34);
JScrollPane scrollpane = new JScrollPane(showarea);
showarea.setEditable(false);
tf.add(scrollpane);
try {
Print();
showarea.setText("");
showarea.append("学号\t\t姓名\t年龄\t系别\n");
for (int i = 0; i < Len; i++) {
String age = Integer.toString(Age[i]);
showarea.append(No[i]);
showarea.append("\t\t");
showarea.append(Name[i]);
showarea.append("\t");
showarea.append(age);
showarea.append("\t");
showarea.append(Dept[i]);
showarea.append("\n");
}
Len = 0;
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} else {
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 需要先连接！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});
// ============================断开SQL
btn6.addActionListener(e -> {
if (flag == 1) {
try {
Close();
flag = 0;// 记录已经连接
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(200, 100);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 断开数据库成功！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} else {
JDialog dialog = new JDialog(f, "提示", true);
dialog.setSize(250, 150);
dialog.setLocation(500, 300);
JLabel label = new JLabel(" 数据库已经断开了，不要重复操作！");
dialog.add(label);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
}
});// ============================断开SQL
}// ===============================GUI
//连接SQL服务器
public static void Lianjie() throws SQLException{
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=Student";
String username = "sa";
String password = "qqy.2520";
conn=DriverManager.getConnection(url,username,password);
stmt = conn.createStatement();
}
//输出全部信息
public static boolean Print() throws SQLException {
try {
String sql="select * from Student";
rs=stmt.executeQuery(sql);
while(rs.next()) {
No[Len]=rs.getString("Sno");
Name[Len]=rs.getString("Sname");
Age[Len]=rs.getInt("Sage");
Dept[Len]=rs.getString("Sdept");
Len++;
}
}catch(Exception e) {
e.printStackTrace();
}
return false;
}
//按学号查找信息
public static boolean findUser(String Sno) throws SQLException{
try {
String sql="select * from Student where Sno=?";
prestmt = conn.prepareStatement(sql);
prestmt.setString(1,Sno);
rs=prestmt.executeQuery();
if(rs.next()) {
NPC=1; //学号存在
no=rs.getString("Sno");
name=rs.getString("Sname");
age_1=rs.getInt("Sage");
dept=rs.getString("Sdept");
//System.out.println(Sno1+"\t\t"+Sname+"\t\t"+Sage+"\t\t"+Sdept);
}
}catch(Exception e) {
e.printStackTrace();
}
return false;
}
//修改
public static boolean Update(String Sno,String name) throws SQLException{
try {
String sql="update Student set Sname=? where Sno=? ";
prestmt = conn.prepareStatement(sql);
prestmt.setString(1,name);
prestmt.setString(2,Sno);
int a= prestmt.executeUpdate(); //修改数据库，并且记录修改的条数
if(a>0) {
NPC=1;
System.out.println("修改成功");
}
else
System.out.println("修改失败");
}catch(Exception e) {
e.printStackTrace();
}
return false;
}
//插入
public static boolean Insert(String Sno,String Sname,int Sage,String Sdept) throws SQLException{
try {
String sql="insert Student values(?,?,?,?)";
prestmt = conn.prepareStatement(sql);
prestmt.setString(1,Sno);
prestmt.setString(2,Sname);
prestmt.setInt(3,Sage);
prestmt.setString(4,Sdept);
int a= prestmt.executeUpdate(); //修改数据库，并且记录修改的条数
if(a>0) {
System.out.println("插入成功");
NPC=1;
}
else
System.out.println("插入修改失败");
}catch(Exception e) {
e.printStackTrace();
}
return false;
}
//删除
public static boolean Delete(String Sno) throws SQLException{
try {
String sql="delete from Student where Sno=?";
prestmt = conn.prepareStatement(sql);
prestmt.setString(1,Sno);
int a= prestmt.executeUpdate(); //修改数据库，并且记录修改的条数
if(a>0) {
System.out.println("删除成功");
NPC=1;
}
else
System.out.println("删除失败");
}catch(Exception e) {
e.printStackTrace();
}
return false;
}
//关闭数据库，关闭连接
public static void Close() throws SQLException {
if(rs!=null) {rs.close();}
if(prestmt!=null) {stmt.close();}
if(conn!=null) {conn.close();}
}
public static void main(String[] args) throws SQLException {
SwingUtilities.invokeLater(JDBC::creatAndShowGUI);
//Close();
}
}效果图：
这里是主界面，执行操作时需要【连接】数据库。
1.插入界面：
2.查询界面：
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
