开发环境及工具:IDEA 2018 + MySQL5.5
新建项目如下:
第一步:选择Java,点击next
默认next,到如下界面,自己命名即可:
点击finish,完成新建过程。
搭建项目框架如下:
注意点:out文件夹不用自己新建,其他按照eclipse里面建包,建类一样。
导入mysql的jar包(去官网下或者网盘下载)
网盘链接: https://pan.baidu.com/s/1cMXavTW7F5eh18lF796TZg
提取码:k1a8
src下面新建lib文件夹,将jar包粘贴进去,,注意还未导入成功!!!!!
做如下操作:右键,选择add as library,后面点击OK即可。
成功结果如下:
数据库准备(建表):
/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.5.58 : Database - test1
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test1` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test1`;
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`uid` varchar(10) NOT NULL,
`pwd` varchar(10) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`role` int(3) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user` */
insert into `user`(`uid`,`pwd`,`name`,`role`) values ('1000','123','jack',0),('1001','123','bob',0),('1002','456','rose',1),('1005','222','nihao',0);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
封装 数据库的连接与关闭 在DBC类:
import java.sql.Connection;
import java.sql.DriverManager;
public class DBC {
private static final String sqlUser="root"; //root为用户名
private static final String sqlPassword="123456"; //123456为连接密码
private static final String databaseURL="jdbc:mysql://localhost:3306/test1"; //test1为数据库名
private static Connection conn=null;
public static Connection getConn()
{
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(databaseURL, sqlUser, sqlPassword);
if (!conn.isClosed()) {
//System.out.println("数据库连接成功");
}
return conn;
}catch (Exception e)
{
e.printStackTrace();
return null;
}
}
public static void closeConn()
{
if(conn!=null)
{
try{
conn.close();
}catch (Exception e)
{
e.printStackTrace();
conn=null;
}
}
}
}
建立Java Bean,这里为新建的User类
public class User {
private String uid; //用户id
private String pwd; //用户密码
private String name; //用户姓名
private int role; //用户权限--此系统中暂未对权限进行控制
public int getRole() {
return role;
}
public void setRole(int role) {
this.role = role;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
}
对数据库的操作,DBO类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DBO {
Connection conn;
public boolean checkUser(String uid,String pwd) //验证用户名和密码
{
try
{
conn = DBC.getConn();
PreparedStatement pstmt=conn.prepareStatement("select * from user where uid=? and pwd=?");
pstmt.setString(1, uid);
pstmt.setString(2, pwd);
ResultSet rs=pstmt.executeQuery();
if(rs.next())
return true;
return false;
}
catch(Exception e){
e.printStackTrace();
return false;
}finally{
DBC.closeConn();
}
}
public boolean insertUser(String uid,String pwd,String uname,int role) //增添用户
{
try
{
conn = DBC.getConn();
PreparedStatement pstmt=conn.prepareStatement("insert into user(uid,pwd,name,role) values(?,?,?,?)");
pstmt.setString(1, uid);
pstmt.setString(2, pwd);
pstmt.setString(3, uname);
pstmt.setInt(4, role);
pstmt.execute();
return true;
}
catch(Exception e){
e.printStackTrace();
return false;
}finally{
DBC.closeConn();
}
}
public boolean deleteUser(String uid) //删除用户
{
try{
conn=DBC.getConn();
PreparedStatement pstmt=conn.prepareStatement("delete from user where uid=?");
pstmt.setString(1, uid);
return pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
return -1;
}finally{
DBC.closeConn();
}
}
public boolean updateUser(String id,String pwd) //修改用户
{
try{
conn=DBC.getConn();
PreparedStatement pstmt=conn.prepareStatement("update user set pwd=? where uid=?");
pstmt.setString(1, pwd);
pstmt.setString(2, id);
pstmt.execute();
return true;
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
DBC.closeConn();
}
}
public User searchUser(String uid) //查询用户
{
try{
conn=DBC.getConn();
PreparedStatement pstmt=conn.prepareStatement("select * from user where uid=?");
pstmt.setString(1, uid);
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
User user = new User();
user.setUid(rs.getString(1));
user.setPwd(rs.getString(2));
user.setName(rs.getString(3));
user.setRole(rs.getInt(4));
return user;
}else
return null;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBC.closeConn();
}
}
}
显示界面菜单,即Main类
import java.util.Scanner;
public class Main {
public static void main(String[] args) {
DBO dbo = new DBO();
while(true)
{
System.out.println("********人员管理系统*******");
System.out.print("请输入用户账号:");
Scanner sc = new Scanner(System.in);
String uid = sc.nextLine();
System.out.print("请输入用户密码:");
String upwd = sc.nextLine();
//System.out.println(uid+","+upwd);
if(!dbo.checkUser(uid,upwd))
{
System.out.println("登录失败,账户或密码错误!");
continue;
}
else
{
System.out.println("登录成功!");
}
int select=0;
while(select != -1)
{
System.out.println("1.增加人员 2.删除人员 3.修改人员 4.查询人员 0.退出系统");
System.out.print("请输入选择:");
select = sc.nextInt();
switch (select)
{
case 1:
Scanner sc1 = new Scanner(System.in);
System.out.print("请输入用户账号:");
String newid = sc1.nextLine();
System.out.print("请输入用户密码:");
String newpwd = sc1.nextLine();
System.out.print("请输入用户姓名:");
String newname = sc1.nextLine();
System.out.print("请输入用户权限(0/1):");
int role = sc1.nextInt();
if(dbo.checkUser(newid,newpwd))
{
System.out.println("该账户已经存在!");
}
else
{
if(dbo.insertUser(newid,newpwd,newname,role))
{
System.out.println("添加成功!");
}
else
{
System.out.println("添加失败!");
}
}
break;
case 2:
Scanner sc2 = new Scanner(System.in);
System.out.print("请输入用户账号:");
newid = sc2.nextLine();
if(dbo.deleteUser(newid))
{
System.out.println("删除成功!");
}
else
{
System.out.println("删除失败!");
}
break;
case 3:
Scanner sc3 = new Scanner(System.in);
System.out.print("请输入用户账号:");
newid = sc3.nextLine();
System.out.print("请输入用户新密码:");
newpwd = sc3.nextLine();
if(dbo.updateUser(newid,newpwd))
{
System.out.print("修改成功!");
}
else
{
System.out.print("修改失败!");
}
break;
case 4:
Scanner sc4 = new Scanner(System.in);
System.out.print("请输入用户账号:");
newid = sc4.nextLine();
User user = dbo.searchUser(newid);
if(user!=null)
{
System.out.println("账户 密码 姓名 权限");
System.out.println(user.getUid()+" "+user.getPwd()+" "+user.getName()+" "+user.getRole());
}
else
{
System.out.println("对不起,查无此人!");
}
break;
case 0:
select = -1;
System.out.println("退出成功!");
break;
default:System.out.println("输入非法,请重新输入!");
}
}
System.out.println("***************************");
}
}
}
运行界面如下:
验证添加人员功能:
数据库中:
验证修改人员功能:
验证删除人员功能:
plus:小项目试试水,,。