Java JDBC swing DBUtils：人员管理系统，单选按钮，增删改查【诗书画唱】
我自己总结两表查询的语法：select * from 表名1 a inner join 表名2 b on a.两表共有的列名=b.两表共有的列名
create table ptype1(
p_typeid int primary key identity(1,1),
p_typename nvarchar(20) not null
)
insert into ptype1 values('管理员')
insert into ptype1 values('普通用户')
insert into ptype1 values('会员用户')
create table person1(
p_id int primary key identity(1,1),
p_name nvarchar(50) not null,
p_sex nvarchar(30) check(p_sex='男' or p_sex='女'),
p_typeid int
foreign key(p_typeid) references ptype1(p_typeid)
)
insert into person1 values('张三','男',1)
insert into person1 values('李四','男',3)
insert into person1 values('王五','女',2)
insert into person1 values('马六','男',2)
insert into person1 values('张大飞','男',1)
insert into person1 values('张大壮','女',3)
select * from ptype1
select * from person1
select * from person1 a inner join ptype1 b on a.p_typeid=b.p_typeid where 1=1;
drop table ptype1
drop table person1
package yongHuZSGC;
import java.sql.*;
public class DBUtils {
static Connection con=null;
static Statement sta=null;
static ResultSet res=null;
//在静态代码块中执行
static{
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//封装链接数据库的方法
public static Connection getCon(){
if(con==null){
try {
con=DriverManager.getConnection
("jdbc:sqlserver://localhost;databaseName=yonghu","qqq","123");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return con;
}
//查询的方法
public static ResultSet Select(String sql){
con=getCon();//建立数据库链接
try {
sta=con.createStatement();
res=sta.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return res;
}
//增删改查的方法
// 返回int类型的数据
public static boolean ZSG(String sql){
con=getCon();//建立数据库链接
boolean b=false;
try {
sta=con.createStatement();
int num=sta.executeUpdate(sql);
//0就是没有执行成功，大于0 就成功了
if(num>0){
b=true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return b;
}
}
package yongHuZSGC;
public class mains {
public static void main(String[] args) {
new ZSGC();
}
}
package yongHuZSGC;
import java.awt.Color;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
class shiJian implements MouseListener, ActionListener {
public ZSGC jieShouchuangTi = null;
public shiJian(ZSGC chuangTi) {
this.jieShouchuangTi = chuangTi;
}
@Override
public void actionPerformed(ActionEvent arg0) {
String huoQuDaoDeWenZi = arg0.getActionCommand();
if (huoQuDaoDeWenZi.equals("查询")) {
String pid = jieShouchuangTi.txt_pid.getText();
String pName = jieShouchuangTi.txt_pname.getText();
jieShouchuangTi.tushu_load(pid, pName);
} else if (huoQuDaoDeWenZi.equals("增加人员")) {
String pName = jieShouchuangTi.txt_pName.getText();
int pTypeId = jieShouchuangTi.arr_type.get(jieShouchuangTi.com_type.getSelectedIndex());
String pSex = "男";
if (jieShouchuangTi.rb2.isSelected()) {
pSex= "女";
}
String sql = "insert into person1(p_name,p_typeid,"
+ "p_sex) " + "values('" + pName + "',"
+ pTypeId + ",'" + pSex + "')";
if (DBUtils.ZSG(sql)) {
jieShouchuangTi.tushu_load(null, null);
JOptionPane.showMessageDialog(null, "人员增加成功!");
} else {
JOptionPane.showMessageDialog(null, "增加失败，请重试");
}
} else if (huoQuDaoDeWenZi.equals("修改人员")) {
String pid = jieShouchuangTi.txt_Pid.getText();
String pName = jieShouchuangTi.txt_pName.getText();
int pTypeNameId = jieShouchuangTi.arr_type
.get(jieShouchuangTi.com_type.getSelectedIndex());
String pSex = "男";
if (jieShouchuangTi.rb2.isSelected()) {
pSex= "女";
}
String sql = "update person1 set p_name='" + pName
+ "',p_typeid=" + pTypeNameId
+ ",p_sex='" + pSex + "' where p_id='" + pid
+ "'";
if (DBUtils.ZSG(sql)) {
jieShouchuangTi.tushu_load(null, null);
JOptionPane.showMessageDialog(null, "修改成功!");
} else {
JOptionPane.showMessageDialog(null, "修改失败，请重试");
}
} else if (huoQuDaoDeWenZi.equals("清空")) {
jieShouchuangTi.txt_Pid.setText("");
jieShouchuangTi.txt_pName.setText("");
String pSex = "男";
if (jieShouchuangTi.rb2.isSelected()) {
pSex= "女";
}
jieShouchuangTi.com_type.setSelectedIndex(1);
}
}
@Override
public void mouseClicked(MouseEvent arg0) {
if (arg0.getClickCount() == 2) {
int row = jieShouchuangTi.jt1.getSelectedRow();
jieShouchuangTi.txt_Pid.setText(jieShouchuangTi.jt1.getValueAt(row, 0).toString());
jieShouchuangTi.txt_pName.setText(jieShouchuangTi.jt1.getValueAt(row, 1).toString());
if (jieShouchuangTi.jt1.getValueAt(row, 2).equals("男")) {
jieShouchuangTi.rb1.setSelected(true);
}
else if(jieShouchuangTi.jt1.getValueAt(row, 2).equals("女")){
jieShouchuangTi.rb2.setSelected(true);
}
String type = jieShouchuangTi.jt1.getValueAt(row, 3).toString();
jieShouchuangTi.com_type.setSelectedItem(type);
} else if (arg0.isMetaDown()) {
int num = JOptionPane.showConfirmDialog(null, "确定要删除这条信息吗?");
if (num == 0) {
int row = jieShouchuangTi.jt1.getSelectedRow();
String pId = jieShouchuangTi.jt1.getValueAt(row, 0).toString();
String sql = "delete person1 where p_id='" + pId + "'";
if (DBUtils.ZSG(sql)) {
jieShouchuangTi.tushu_load(null, null);
JOptionPane.showMessageDialog(null, "人员删除成功!");
} else {
JOptionPane.showMessageDialog(null, "删除失败，请重试");
}
}
}
}
@Override
public void mouseEntered(MouseEvent arg0) {
}
@Override
public void mouseExited(MouseEvent arg0) {
}
@Override
public void mousePressed(MouseEvent arg0) {
}
@Override
public void mouseReleased(MouseEvent arg0) {
}
}
public class ZSGC extends JFrame {
public static ArrayList<Integer> arr_type = null;
public static JButton btn_select, btn_insert, btn_update,
btn_qingkong = null;
public static JComboBox com_type = null;
public static DefaultTableModel dtm = null;
public static JPanel jp1, jp2, jp3 = null;
public static JTable jt1 = null;
public static JLabel lbPid, lbPname = null;
public static JLabel lbPId, lbPName, lb_Ptypename,
lb_pSex = null;
public static JTextField txt_pid, txt_pname = null;
public static JTextField txt_Pid, txt_pName
= null;
public static JRadioButton rb1,rb2;
public ZSGC() {
this.setTitle("人员管理");
this.setLayout(null);
this.setSize(710, 550);
this.setLocationRelativeTo(null);
jp2 = new JPanel();
jp2.setBorder(BorderFactory.createLineBorder(Color.gray));
jp2.setLayout(null);
jp2.setBounds(470, 10, 200, 150);
lbPid = new JLabel("人员编号:");
lbPname = new JLabel("人员姓名:");
lbPid.setBounds(10, 10, 70, 30);
lbPname.setBounds(10, 50, 70, 30);
txt_pid = new JTextField();
txt_pname = new JTextField();
txt_pid.setBounds(80, 10, 100, 30);
txt_pname.setBounds(85, 50, 100, 30);
btn_select = new JButton("查询");
btn_select.setBounds(30, 90, 100, 30);
jp2.add(lbPid);
jp2.add(lbPname);
jp2.add(txt_pid);
jp2.add(txt_pname);
jp2.add(btn_select);
jp3 = new JPanel();
jp3.setBorder(BorderFactory.createLineBorder(Color.gray));
jp3.setLayout(null);
jp3.setBounds(470, 170, 200, 300);
lbPId = new JLabel("人员编号:");
lbPName = new JLabel("人员姓名:");
lb_Ptypename = new JLabel("人员类型:");
lb_pSex = new JLabel("人员性别:");
lbPId.setBounds(10, 10, 70, 30);
lbPName.setBounds(10, 50, 70, 30);
lb_Ptypename.setBounds(10, 130, 70, 30);
lb_pSex.setBounds(10, 170, 70, 30);
jp3.add(lbPId);
jp3.add(lbPName);
jp3.add(lb_Ptypename);
jp3.add(lb_pSex);
txt_Pid = new JTextField();
txt_Pid.setEditable(false);
txt_pName = new JTextField();
rb1 = new JRadioButton("男", true);
rb2 = new JRadioButton("女");
jp3.add(rb1);
jp3.add(rb2);
ButtonGroup bg = new ButtonGroup();
bg.add(rb1);
bg.add(rb2);
rb1.setBounds(80, 170, 50, 30);
rb2.setBounds(140, 170, 50, 30);
txt_Pid.setBounds(80, 10, 100, 30);
txt_pName.setBounds(80, 50, 100, 30);
com_type = new JComboBox();
String sql = "select * from ptype1";
ResultSet res_type = DBUtils.Select(sql);
arr_type = new ArrayList<Integer>();
try {
while (res_type.next()) {
com_type.addItem(res_type.getObject(2));
arr_type.add(res_type.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
com_type.setBounds(80, 130, 100, 30);
btn_insert = new JButton("增加人员");
btn_insert.setBounds(10, 210, 80, 30);
btn_update = new JButton("修改人员");
btn_update.setBounds(95, 210, 80, 30);
btn_qingkong = new JButton("清空");
btn_qingkong.setBounds(10, 250, 160, 30);
btn_select.addActionListener(new shiJian(this));
btn_insert.addActionListener(new shiJian(this));
btn_update.addActionListener(new shiJian(this));
btn_qingkong.addActionListener(new shiJian(this));
jp3.add(txt_Pid);
jp3.add(txt_pName);
jp3.add(com_type);
jp3.add(btn_insert);
jp3.add(btn_update);
jp3.add(btn_qingkong);
this.add(jp2);
this.add(jp3);
this.setVisible(true);
tushu_load(null, null);
}
public void tushu_load(String tid, String tname) {
if (jp1 != null) {
this.remove(jp1);
}
String sql = "select * from person1 a inner join ptype1 b on a.p_typeid=b.p_typeid where 1=1";
if (tid != null && tid.length() > 0) {
sql += " and p_id='" + tid + "'";
}
if (tname != null && tname.length() > 0) {
sql += " and p_name like'%" + tname + "%'";
}
Vector<Object> v_head = new Vector<Object>();
v_head.add("人员编号");
v_head.add("人员姓名");
v_head.add("人员性别");
v_head.add("人员类型");
Vector<Vector<Object>> v_body = new Vector<Vector<Object>>();
ResultSet res = DBUtils.Select(sql);
try {
while (res.next()) {
Vector<Object> v = new Vector<Object>();
v.add(res.getInt("p_id"));
v.add(res.getString("P_name"));
v.add(res.getString("P_sex"));
v.add(res.getString("P_typename"));
v_body.add(v);
}
} catch (SQLException e) {
e.printStackTrace();
}
dtm = new DefaultTableModel(v_body, v_head) {
@Override
public boolean isCellEditable(int a, int b) {
return false;
}
};
jt1 = new JTable(dtm);
jt1.addMouseListener(new shiJian(this));
JScrollPane jsp = new JScrollPane(jt1);
jsp.setBounds(0, 0, 450, 500);
jp1 = new JPanel();
jp1.setLayout(null);
jp1.setBounds(10, 10, 450, 500);
jp1.add(jsp);
this.add(jp1);
}
}
