目录
在这里面可以进行基本的增删改查以及其他的查询方式,需连接数据库一起使用
核心功能
- 添加用户信息
- 删除某一个用户信息
- 删除选中的用户信息
- 分页查询所有用户信息
- 模糊查询用户信息
- 更新用户信息
1、帮助类DBHelper
package com.zking.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DBHelper {
private static String cname = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static String url = "jdbc:sqlserver://localhost:1433;DatabaseName=Student";
private static String user = "sa";
private static String upwd = "、、;
//静态代码块-加载类时自动执行
static {
try {
Class.forName(cname);
} catch (Exception e) {
e.printStackTrace();
}
}
//方法一:连接数据库
public static Connection getCon() {
Connection con = null;
try {
con = DriverManager.getConnection(url,user,upwd);
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
//方法二:关闭连接
public static void closeDb(Connection con,PreparedStatement ps,ResultSet rs) {
try {
if(con!=null) {
con.close();
}
if(ps!=null) {
ps.close();
}
if(rs!=null) {
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
2、实体类entity
package com.zking.entity;
public class Users {
private int uuid;
private String uname;
private String upwd;
private String usex;
private String ulike;
private String uaddress;
private String uinfo;
public Users() {
super();
}
public Users(String uname, String upwd, String usex, String ulike, String uaddress, String uinfo) {
super();
this.uname = uname;
this.upwd = upwd;
this.usex = usex;
this.ulike = ulike;
this.uaddress = uaddress;
this.uinfo = uinfo;
}
public Users(int uuid, String uname, String upwd, String usex, String ulike, String uaddress, String uinfo) {
super();
this.uuid = uuid;
this.uname = uname;
this.upwd = upwd;
this.usex = usex;
this.ulike = ulike;
this.uaddress = uaddress;
this.uinfo = uinfo;
}
public int getUuid() {
return uuid;
}
public void setUuid(int uuid) {
this.uuid = uuid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getUpwd() {
return upwd;
}
public void setUpwd(String upwd) {
this.upwd = upwd;
}
public String getUsex() {
return usex;
}
public void setUsex(String usex) {
this.usex = usex;
}
public String getUlike() {
return ulike;
}
public void setUlike(String ulike) {
this.ulike = ulike;
}
public String getUaddress() {
return uaddress;
}
public void setUaddress(String uaddress) {
this.uaddress = uaddress;
}
public String getUinfo() {
return uinfo;
}
public void setUinfo(String uinfo) {
this.uinfo = uinfo;
}
@Override
public String toString() {
return "Users [uuid=" + uuid + ", uname=" + uname + ", upwd=" + upwd + ", usex=" + usex + ", ulike=" + ulike
+ ", uaddress=" + uaddress + ", uinfo=" + uinfo + "]";
}
}
3、功能类dao
package com.zking.dao;
import java.awt.dnd.DnDConstants;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import com.zking.entity.Users;
import com.zking.util.DBHelper;
public class UsersDao {
//添加用户-成功返回1,否则返回null
public int addUs(String uname,String upwd,String usex,String ulike,String uaddress,String uinfo) {
int i = 0;
Connection con = null;
PreparedStatement ps = null;
try {
con = DBHelper.getCon();
String sql = "insert into Users values(?,?,?,?,?,?)";
ps = con.prepareStatement(sql);
ps.setString(1, uname);
ps.setString(2, upwd);
ps.setString(3,usex);
ps.setString(4, ulike);
ps.setString(5, uaddress);
ps.setString(6, uinfo);
i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDb(con, ps, null);
}
return i;
}
//删除用户-成功返回1,否则返回0
public int delById(int uuid) {
int i = 0;
Connection con = null;
PreparedStatement ps = null;
try {
con = DBHelper.getCon();
String sql = "delete Users where uuid=?";
ps = con.prepareStatement(sql);
ps.setInt(1, uuid);
i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
DBHelper.closeDb(con, ps, null);
}
return i;
}
//修改用户-成功返回1,否则返回0
public int updateUs(int uuid,String uname,String upwd,String usex,String ulike,String uaddress,String uinfo) {
int i= 0;
Connection con = null;
PreparedStatement ps = null;
try {
//连接数据库
con = DBHelper.getCon();
String sql = "update Users set uname=?,upwd=?,usex=?,ulike=?,uaddress=?,uinfo=? where uuid="+uuid;
ps = con.prepareStatement(sql);
ps.setString(1, uname);
ps.setString(2, upwd);
ps.setString(3,usex);
ps.setString(4, ulike);
ps.setString(5, uaddress);
ps.setString(6, uinfo);
i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDb(con, ps, null);
}
return i;
}
//查询单个-查到则返回用户对象,否则就返回null
public Users getByID(int uuid) {
Users us = null;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DBHelper.getCon();
String sql = "select * from Users where uuid=?";
ps = con.prepareStatement(sql);
ps.setInt(1, uuid);
rs = ps.executeQuery();
if(rs.next()) {
us = new Users();
us.setUuid(rs.getInt(1));
us.setUname(rs.getString(2));
us.setUpwd(rs.getString(3));
us.setUsex(rs.getString(4));
us.setUlike(rs.getString(5));
us.setUaddress(rs.getString(6));
us.setUinfo(rs.getString(7));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDb(con, ps, rs);
}
return us;
}
//模糊查询-
public ArrayList<Users> getMh(String uname,String name){
ArrayList<Users> s = new ArrayList<>();
Users us = null;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DBHelper.getCon();
String sql = "select * from Users where "+uname+" like '%"+name+"%'";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
us = new Users(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),rs.getString(7));
s.add(us);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDb(con, ps, rs);
}
return s;
}
}
4、测试类
package com.zking.ui;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.List;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JRadioButton;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import com.zking.dao.UsersDao;
import com.zking.entity.Users;
public class MyTest extends JFrame{
public MyTest() {
// 设置标题
this.setTitle("用户管理系统");
// 设置大小
this.setSize(500, 500);
// 设置居中
this.setLocationRelativeTo(null);
// 设置关闭
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
// 设置画布
JPanel jp = new JPanel();
// 编号
JLabel bh = new JLabel("编号:");
jp.add(bh);
// 编号框
JTextField jbh = new JTextField(10);
jp.add(jbh);
// 文本-用户名
JLabel jlname = new JLabel("用户名:");
jp.add(jlname);
// 文本框
JTextField jluname = new JTextField(10);
jp.add(jluname);
// 文本-密码
JLabel up = new JLabel("密码:");
jp.add(up);
// 密码框
JPasswordField jpf = new JPasswordField(10);
jp.add(jpf);
//文本-性别
JLabel sex = new JLabel("性别:");
jp.add(sex);
//单选按钮
JRadioButton boy = new JRadioButton("男");
jp.add(boy);
JRadioButton girl = new JRadioButton("女");
jp.add(girl);
//设置选项默认为女生
girl.setSelected(true);
//文本-爱好
JLabel like = new JLabel("爱好:");
jp.add(like);
//多选按钮
JCheckBox a = new JCheckBox("敲代码");
jp.add(a);
JCheckBox b = new JCheckBox("打豆豆");
jp.add(b);
JCheckBox c = new JCheckBox("运动");
jp.add(c);
JCheckBox d = new JCheckBox("学习");
jp.add(d);
JCheckBox p = new JCheckBox("睡觉");
jp.add(p);
//文本-地址
JLabel sdd = new JLabel("地址:");
jp.add(sdd);
//下拉框
JComboBox<String> com = new JComboBox();
jp.add(com);
//给下拉框添值
com.addItem("郴州");
com.addItem("上海");
com.addItem("深圳");
com.addItem("长沙");
com.addItem("广州");
jp.add(com);
//文本-个人说明
JLabel sm = new JLabel("个人说明:");
jp.add(sm);
//文本域
JTextArea jt = new JTextArea(5,10);
jp.add(jt);
//滚动面板
JScrollPane jsp = new JScrollPane(jt);
jp.add(jsp);
// 按钮
JButton bu = new JButton("登录");
jp.add(bu);
JButton bu1 = new JButton("注册");
jp.add(bu1);
JButton bu2 = new JButton("删除");
jp.add(bu2);
JButton bu3 = new JButton("修改");
jp.add(bu3);
JButton bu4 = new JButton("查询");
jp.add(bu4);
JButton bu5 = new JButton("查询");
jp.add(bu5);
//增
bu1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String uname = jluname.getText();
String upwd = jpf.getText();
String usex = girl.getText();
String ulike = "";
if(a.isSelected()) {
ulike+="敲代码";
}
if(b.isSelected()) {
ulike+="打豆豆";
}
if(c.isSelected()) {
ulike+="运动";
}
if(d.isSelected()) {
ulike+="学习";
}
if(p.isSelected()) {
ulike+="睡觉";
}
String uaddress = com.getSelectedItem().toString();
String uinfo = jt.getText();
UsersDao us = new UsersDao();
int u = us.addUs(uname, upwd, usex, ulike, uaddress, uinfo);
if (u>0) {
JOptionPane.showMessageDialog(null, "增加成功");
} else {
JOptionPane.showMessageDialog(null, "增加失败");
}
}
});
//删
bu2.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
//强转
String uuid = jbh.getText();
int u = Integer.parseInt(uuid);
UsersDao zc = new UsersDao();
int i = zc.delById(u);
if (i > 0) {
JOptionPane.showMessageDialog(null, "删除成功");
} else {
JOptionPane.showMessageDialog(null, "删除失败");
}
}
});
//改
bu3.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String uuid = jbh.getText();
int u1 = Integer.parseInt(uuid);
String uname = jluname.getText();
String upwd = jpf.getText();
String usex = boy.getText();
String ulike = "";
if(a.isSelected()) {
ulike+="敲代码";
}
if(b.isSelected()) {
ulike+="打豆豆";
}
if(c.isSelected()) {
ulike+="运动";
}
if(d.isSelected()) {
ulike+="学习";
}
if(p.isSelected()) {
ulike+="睡觉";
}
String uaddress = com.getSelectedItem().toString();
String uinfo = jt.getText();
UsersDao xg = new UsersDao();
int g = xg.updateUs(u1,uname, upwd, usex, ulike, uaddress, uinfo);
if (g>0) {
JOptionPane.showMessageDialog(null, "修改成功");
} else {
JOptionPane.showMessageDialog(null, "修改失败");
}
}
});
//根据编号查询
bu4.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
//强转
String uuid = jbh.getText();
int u2 = Integer.parseInt(uuid);
UsersDao zc = new UsersDao();
Users i = zc.getByID(u2);
if (i!=null) {
JOptionPane.showMessageDialog(null, "查询成功");
} else {
JOptionPane.showMessageDialog(null, "查询失败");
}
}
});
// 把画布添加到窗体
this.getContentPane().add(jp);
// 设置显示窗体
this.setVisible(true);
}
public static void main(String[] args) {
// 构造一个对象
new MyTest();
}
}