Java swing图书管理系统6（个人设计思考得出的全功能简易原版）含SQL部分，效果和素材
SQL部分：
select ts_shuliang,jnum from tushu t join jieyue j on t.ts_id=j.ts_id where j.ts_id='1'
j.ts_id='1'和j.ts_id=1等价
select ts_shuliang,jnum from tushu t join jieyue j on t.ts_id=j.ts_id where j.ts_id='1'
j.ts_id='1'和j.ts_id=1等价
select *from yonghu
drop table yonghu
create table yonghu(
yh_ID int primary key identity(1,1),
yh_Uname varchar(30) not null,
yh_Pwd varchar(30) not null,
yh_Name varchar(30) not null,
yh_Age int,
yh_Sex varchar(20),
yh_Phone varchar(100),
yh_Address varchar(200),
yh_chubanshe varchar(500),
yh_Q varchar(500),
yh_A varchar(500),
yh_type nvarchar(20) check(yh_type='普通用户' or yh_type='管理员用户'),
);
insert into yonghu values('1','11','诗书画唱1',21,'男','19999999999','江西','阳光帅气的男孩子','你爱好多吗？','多','普通用户');
insert into yonghu values('2','22','诗书画唱2',22,'男','29999999999','北京','阳光帅气的男孩子','你看过的书多吗？','多','普通用户');
insert into yonghu values('3','33','诗书画唱3',23,'男','39999999999','上海','阳光帅气的男孩子','你会的技能多吗？','多','普通用户');
insert into yonghu values('4','44','诗书画唱4',24,'男','49999999999','湖南','阳光帅气的男孩子','你喜欢的动漫吗？','多','普通用户');
insert into yonghu values('5','55','诗书画唱5',25,'男','59999999999','浙江','阳光帅气的男孩子','你喜欢的歌曲多吗？','多','管理员用户');
drop table ts_Type
create table ts_Type(
ts_leixingid int primary key identity(1,1),
ts_leixing varchar(100) not null
);
insert into ts_Type values('奇幻');
insert into ts_Type values('爱情');
insert into ts_Type values('日常');
insert into ts_Type values('科普');
drop table tushu
create table tushu(
ts_ID int primary key identity(1,1),
ts_Name varchar(100) not null,
ts_shuliang int,
--ts_leixing varchar(100) not null,
ts_chubanshe varchar(300),
ts_leixingid int
);
--insert into tushu values('《奥秘》',12,'奇幻','人民出版社',1);
--insert into tushu values('《爱你》',2,'爱情','人民出版社',2);
--insert into tushu values('《诗书》',4,'日常','人民出版社',3);
--insert into tushu values('《科普》',3,'科普','人民出版社',4);
insert into tushu values('《奥秘》',12,'人民出版社',1);
insert into tushu values('《爱你》',2,'人民出版社',2);
insert into tushu values('《诗书》',4,'人民出版社',3);
insert into tushu values('《科普》',3,'人民出版社',4);
drop table yonghu, tushu,dingdanxiangqing,ts_Type
drop table tushu
select * from yonghu
select * from yonghu where yh_name='1' and yh_pwd='1' and yh_type='管理员用户'
select * from tushu
select * from ts_Type
select * from dingdan,dingdanxiangqing
select * from dingdanxiangqing
drop table dingdan
create table dingdan(
dingdan_ID int primary key identity(1,1),
yh_ID int,
dingdanxiangqing_ID int
)
insert into dingdan values(1,1);
insert into dingdan values(1,2);
insert into dingdan values(1,3);
insert into dingdan values(2,4);
insert into dingdan values(2,5);
insert into dingdan values(2,6);
insert into dingdan values(3,7);
create table dingdanxiangqing(
dingdanxiangqing_ID int primary key identity(1,1),
sp_ID int,
sp_num int
)
insert into dingdanxiangqing values(1,3);
insert into dingdanxiangqing values(2,4);
insert into dingdanxiangqing values(1,5);
insert into dingdanxiangqing values(3,5);
insert into dingdanxiangqing values(4,2);
insert into dingdanxiangqing values(5,1);
insert into dingdanxiangqing values(3,3);
insert into dingdanxiangqing values(4,3);
select * from yonghu;
select * from shangpin;
select * from sp_Type;
drop table jieyue
create table jieyue(
jid int primary key identity(1,1),
jnum int,
jday int,
yh_id int,
ts_id int
--ALTER TABLE 表名1 add constraint 自己取的约束名 foreign key（字段) references 表名2（字段）
)
insert into jieyue values(3,3,1,1)
insert into jieyue values(2,2,2,2)
insert into jieyue values(4,5,3,3)
insert into jieyue values(5,1,1,4)
--alter table tushu add constraint tushuAndts_type foreign key(yh_leixing) references typetushu (ts_leixing)
--alter table jieyue add constraint jieyueAndyonghu foreign key (yh_id) references users(yh_id)
--alter table jieyue add constraint jieyueAndtushu foreign key(tid) references tushu
select * from tushu a inner join ts_type b on a.ts_leixingid=b.ts_leixingid
select * from ts_type
select * from jieyue a inner join yonghu b on a.yh_id=b.yh_id inner join tushu c on a.ts_id=c.ts_id
select * from jieyue a inner join yonghu b on a.yh_id=b.yh_id inner join tushu c on a.ts_id=c.ts_id
update yonghu set yh_type='管理员用户' where yh_id<4
package UI;
import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.FocusEvent;
import java.awt.event.FocusListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import Model.DBUlits;
class shijian_zhuce implements FocusListener, ActionListener {
public zhuce zz = null;
public shijian_zhuce(zhuce z) {
this.zz = z;
}
// 点击事件的方法
@Override
public void actionPerformed(ActionEvent arg0) {
// TODO Auto-generated method stub
// 干什么事
// 1.得到用户输入的用户名，密码，问题和答案
// 2.存储到数据库里就是注册账号
if (arg0.getActionCommand().equals("注册")) {
String uname = zz.txt_uname.getText().trim();
String pwd = zz.pwd1.getText().trim();
String wen = zz.txt_wen.getText().trim();
String da = zz.txt_da.getText().trim();
String sql = "insert into users(uname,upwd,utypename,uwen,uda) values('"
+ uname
+ "','"
+ pwd
+ "','普通用户','"
+ wen
+ "','"
+ da
+ "')";
if (DBUlits.zsg(sql)) {
JOptionPane.showMessageDialog(null, "用户注册成功");
return;
} else {
JOptionPane.showMessageDialog(null, "注册失败，请重试");
}
} else if (arg0.getActionCommand().equals("取消")) {
zz.setVisible(false);
}
}
@Override
public void focusGained(FocusEvent arg0) {
// TODO Auto-generated method stub
}
// 取消焦点的时候事件
@Override
public void focusLost(FocusEvent arg0) {
// TODO Auto-generated method stub
// 做用户名重复的判断
String uname = zz.txt_uname.getText().trim();
String sql = "select * from users where uname='" + uname + "'";
ResultSet res = DBUlits.select(sql);
try {
if (res.next()) {
// JOptionPane.showMessageDialog(null,"用户名已存在");
zz.lb_uname_panduan.setText("用户名已存在");
zz.lb_uname_panduan.setForeground(Color.red);
} else {
zz.lb_uname_panduan.setText("用户名可以使用");
zz.lb_uname_panduan.setForeground(Color.green);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 1.得到用户输入的用户名
// 2.去数据库查询用户名是否存在
// 3.根据返回的结果提示用户信息
}
}
public class zhuce extends JFrame {
public static JButton btn_zhuce, btn_quxiao = null;
public static JLabel lb_title, lb_uname, lb_pwd, lb_pwd1, lb_wen, lb_da,
lb_uname_panduan = null;
public static JPasswordField pwd1, pwd2 = null;
public static JTextField txt_uname, txt_wen, txt_da = null;
public zhuce() {
this.setTitle("用户注册");
this.setLayout(null);
this.setSize(500, 700);
this.setLocationRelativeTo(null);
lb_title = new JLabel("用户注册");
lb_uname = new JLabel("用户名:");
lb_pwd = new JLabel("密码:");
lb_pwd1 = new JLabel("确认密码:");
lb_wen = new JLabel("问题:");
lb_da = new JLabel("答案:");
lb_title.setBounds(130, 70, 150, 40);
lb_title.setFont(new Font("微软雅黑", 30, Font.BOLD));
lb_uname.setBounds(70, 120, 70, 30);
lb_pwd.setBounds(70, 160, 70, 30);
lb_pwd1.setBounds(70, 200, 70, 30);
lb_wen.setBounds(70, 240, 70, 30);
lb_da.setBounds(70, 280, 70, 30);
this.add(lb_title);
this.add(lb_uname);
this.add(lb_pwd);
this.add(lb_pwd1);
this.add(lb_wen);
this.add(lb_da);
txt_uname = new JTextField();
txt_uname.addFocusListener(new shijian_zhuce(this));
pwd1 = new JPasswordField();
pwd2 = new JPasswordField();
txt_wen = new JTextField();
txt_da = new JTextField();
txt_uname.setBounds(140, 120, 120, 30);
pwd1.setBounds(140, 160, 120, 30);
pwd2.setBounds(140, 200, 120, 30);
txt_wen.setBounds(140, 240, 120, 30);
txt_da.setBounds(140, 280, 120, 30);
this.add(txt_uname);
this.add(pwd1);
this.add(pwd2);
this.add(txt_wen);
this.add(txt_da);
lb_uname_panduan = new JLabel("请输入用户名");
lb_uname_panduan.setBounds(260, 120, 120, 30);
this.add(lb_uname_panduan);
btn_zhuce = new JButton("注册");
btn_quxiao = new JButton("取消");
btn_quxiao.addActionListener(new shijian_zhuce(this));
btn_zhuce.addActionListener(new shijian_zhuce(this));
btn_zhuce.setBounds(100, 320, 80, 30);
btn_quxiao.setBounds(220, 320, 80, 30);
this.add(btn_zhuce);
this.add(btn_quxiao);
this.setVisible(true);
}
}
package Model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUlits {
public static Connection con = null;
public static ResultSet res = null;
public static Statement sta = 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://DESKTOP-49FTFSP;"
+ "databaseName=yonghu", "sa", "1234abcd");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return con;
}
public static Statement getsta() {
con = getcon();
try {
sta = con.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return sta;
}
public static ResultSet select(String sql) {
con = getcon();
sta = getsta();
try {
res = sta.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return res;
}
public static boolean zsg(String sql) {
con = getcon();
sta = getsta();
boolean b = false;
try {
int num = sta.executeUpdate(sql);
if (num > 0) {
b = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return b;
}
}
素材：
