Java swing圖書管理系統(tǒng)6（個人設(shè)計思考得出的全功能簡易原版）含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.存儲到數(shù)據(jù)庫里就是注冊賬號
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
// 做用戶名重復(fù)的判斷
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.去數(shù)據(jù)庫查詢用戶名是否存在
// 3.根據(jù)返回的結(jié)果提示用戶信息
}
}
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("確認(rèn)密碼:");
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;
}
}
素材：
