1.做個(gè)人事管理系統(tǒng)
第一個(gè)類主類
package wode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
/**
* Created by ttc on 18-1-23.
*/
public class JDBCDemoFinal {
public static int delectEmpById(int id) throws SQLException {
Connection connection=JDBCUtils.getConn();
PreparedStatement preparedStatement=connection.prepareStatement("DELETE FROM EMP WHERE sid=?");
preparedStatement.setInt(1,id);
int rows=preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement,connection);
return rows;
}
public static int update(Emp emp) throws SQLException {
Connection connection=JDBCUtils.getConn();
PreparedStatement preparedStatement=connection.prepareStatement("update emp set sname=?,passwordnum=?,email=?,permission=?,birthday=?WHERE sid=?");
preparedStatement.setString(1,emp.getSname());
preparedStatement.setInt(2,emp.getPasswordnum());
preparedStatement.setString(3,emp.getEmail());
preparedStatement.setString(4,emp.getPermission());
Date date=emp.getBirthday();
long time=date.getTime();
java.sql.Timestamp sqlDate = new java.sql.Timestamp(time);
preparedStatement.setTimestamp(5,sqlDate);
preparedStatement.setInt(6,emp.getSid());
int rows=preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement,connection);
return rows;
}
public static int AddEmp(Emp emp) throws SQLException {
Connection connection=JDBCUtils.getConn();
PreparedStatement preparedStatement=connection.prepareStatement("INSERT INTO emp (sname,passwordnum,email,permission,birthday)VALUES (?,?,?,?,?)");
preparedStatement.setString(1,emp.getSname());
preparedStatement.setInt(2,emp.getPasswordnum());
preparedStatement.setString(3,emp.getEmail());
preparedStatement.setString(4,emp.getPermission());
Date date=emp.getBirthday();
long time2=date.getTime();
java.sql.Timestamp sqlDate = new java.sql.Timestamp(time2);
preparedStatement.setTimestamp(5,sqlDate);
int rows=preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement,connection);
return rows;
}
public static Emp getEmpById(int id) throws SQLException {
Connection connection=JDBCUtils.getConn();
PreparedStatement preparedStatement=connection.prepareStatement("SELECT *FROM Emp WHERE sid=?");
preparedStatement.setInt(1,id);
ResultSet resultSet=preparedStatement.executeQuery();
Emp emp=null;
if (resultSet.next()==true)
{
int sid=resultSet.getInt("sid");
String name=resultSet.getString("sname");
int password=resultSet.getInt("passwordnum");
String email=resultSet.getString("email");
String permission=resultSet.getString("permission");
Date date=resultSet.getTimestamp("birthday");
emp=new Emp();
emp.setSid(sid);
emp.setSname(name);
emp.setPasswordnum(password);
emp.setEmail(email);
emp.setPermission(permission);
emp.setBirthday(date);
}
JDBCUtils.close(resultSet,preparedStatement,connection);
return emp;
}
public static Emp getEmpBySname(String yname) throws SQLException {
Connection connection=JDBCUtils.getConn();
PreparedStatement preparedStatement=connection.prepareStatement("SELECT *FROM Emp WHERE sname like ?");
preparedStatement.setString(1,"%"+yname+"%");
ResultSet resultSet=preparedStatement.executeQuery();
Emp emp=null;
if (resultSet.next()==true)
{
int sid=resultSet.getInt("sid");
String name=resultSet.getString("sname");
int password=resultSet.getInt("passwordnum");
String email=resultSet.getString("email");
String permission=resultSet.getString("permission");
Date date=resultSet.getTimestamp("birthday");
emp=new Emp();
emp.setSid(sid);
emp.setSname(name);
emp.setPasswordnum(password);
emp.setEmail(email);
emp.setPermission(permission);
emp.setBirthday(date);
}
JDBCUtils.close(resultSet,preparedStatement,connection);
return emp;
}
public static List<Emp> getAllEmpBySname() throws SQLException {
Connection connection=JDBCUtils.getConn();
PreparedStatement preparedStatement=connection.prepareStatement("SELECT *FROM Emp");
List<Emp>empList=new ArrayList<>();
ResultSet resultSet=preparedStatement.executeQuery();
while (resultSet.next()==true)
{
int sid=resultSet.getInt("sid");
String name=resultSet.getString("sname");
int password=resultSet.getInt("passwordnum");
String email=resultSet.getString("email");
String permission=resultSet.getString("permission");
Date date=resultSet.getTimestamp("birthday");
Emp emp=new Emp();
emp.setSid(sid);
emp.setSname(name);
emp.setPasswordnum(password);
emp.setPermission(permission);
emp.setEmail(email);
emp.setBirthday(date);
empList.add(emp);
}
JDBCUtils.close(resultSet,preparedStatement,connection);
return empList;
}
public static void main(String[] args) throws SQLException, ParseException {
// Emp emp=new Emp();
// emp.setSname("zhangsan");
// emp.setPasswordnum(123);
// emp.setEmail("zhangsan@163.com");
// emp.setPermission("管理員");
// String date1="1980-05-13";
// SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
// Date date=simpleDateFormat.parse(date1);
// emp.setBirthday(date);
// AddEmp(emp);
System.out.println("歡迎使用neusoft的用戶管理系統(tǒng)");
System.out.println("=============================");
System.out.println("用戶登錄-----------------1");
System.out.println("用戶注冊(cè)-----------------2");
System.out.println("退出程序-----------------3");
Scanner scanner=new Scanner(System.in);
while (true)
{
int command=scanner.nextInt();
if (command==2)
{
System.out.println("用戶注冊(cè)界面");
System.out.println("=============================");
System.out.println("請(qǐng)輸入你的用戶名");
String name=scanner.next();
System.out.println("請(qǐng)輸入你的密碼");
int num=scanner.nextInt();
System.out.println("請(qǐng)輸入你的郵箱");
String email=scanner.next();
System.out.println("請(qǐng)輸入你的生日");
String birthday=scanner.next();
Emp emp=new Emp();
emp.setSname(name);
emp.setPasswordnum(num);
emp.setEmail(email);
emp.setPermission("普通用戶");
String s=birthday;
SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
Date date=simpleDateFormat.parse(s);
emp.setBirthday(date);
List<Emp>list=getAllEmpBySname();
List<String>namelist=new ArrayList<>();
for (Emp emp2:list)
{
namelist.add(emp2.getSname());
}
if (namelist.contains(name))
{
System.out.println("注冊(cè)失敗，已有用戶名");
}
else
{
AddEmp(emp);
System.out.println("注冊(cè)成功");
}
}
if (command==1)
{
System.out.println("請(qǐng)輸入你的用戶名");
String name=scanner.next();
System.out.println("請(qǐng)輸入你的密碼");
int password=scanner.nextInt();
List<Emp>list=getAllEmpBySname();
for (Emp emp:list)
{
if (emp.getSname().equals(name)&&emp.getPasswordnum()==password)
{
System.out.println("登錄成功。。。");
System.out.println("=============================");
System.out.println("歡迎登錄主窗體");
System.out.println(name+"您好:"+"\t"+"您的權(quán)限是:"+emp.getPermission());
System.out.println("=============================");
if (emp.getPermission().equals("普通用戶"))
{
while (true)
{
System.out.println("修改自己的信息------------------1");
System.out.println("查詢自己的信息------------------2");
System.out.println("程序退出------------------------3");
int command1=scanner.nextInt();
if (command1==3)
{
break;
}
if (command1==1)
{
Emp emp1=getEmpBySname(name);
if (emp1!=null)
{
System.out.println("您現(xiàn)在的信息是");
System.out.println(emp1);
System.out.println("=============================");
System.out.println("請(qǐng)輸入你要修改的姓名");
String newname=scanner.next();
System.out.println("請(qǐng)輸入你要修改的密碼");
int newpassword=scanner.nextInt();
System.out.println("請(qǐng)輸入要修改的郵箱");
String newemail=scanner.next();
Emp emp2=new Emp();
emp2.setSname(newname);
emp2.setPasswordnum(newpassword);
emp2.setEmail(newemail);
emp2.setPermission(emp.getPermission());
Date date=emp.getBirthday();
emp2.setBirthday(date);
emp2.setSid(emp.getSid());
update(emp2);
System.out.println("修改成功");
}
else
{
System.out.println("不存在");
}
}
if (command1==2)
{
Emp emp1=getEmpBySname(name);
System.out.println("您現(xiàn)在的信息是");
System.out.println(emp1);
}
}
}
if (emp.getPermission().equals("管理員"))
{
while (true)
{
System.out.println("添加用戶-----------------1");
System.out.println("刪除用戶-----------------2");
System.out.println("修改用戶-----------------3");
System.out.println("查詢用戶-----------------4");
System.out.println("程序退出-----------------5");
int command2=scanner.nextInt();
if (command2==5)
{
break;
}
if (command2==4)
{
System.out.println("查詢?nèi)坑脩?---------1");
System.out.println("根據(jù)ID查詢用戶--------2");
System.out.println("根據(jù)姓名查詢用戶------3");
System.out.println("請(qǐng)輸入要做的操作");
int command3=scanner.nextInt();
if (command3==2)
{
System.out.println("請(qǐng)輸入要查詢的ID");
int id=scanner.nextInt();
Emp emp3=getEmpById(id);
System.out.println(emp3);
}
if (command3==3)
{
System.out.println("請(qǐng)輸入要查詢的用戶名(支持模糊查詢)");
String names=scanner.next();
Emp emp4=getEmpBySname(names);
System.out.println(emp4);
}
if (command3==1)
{
List<Emp>list1=getAllEmpBySname();
for (Emp emp1:list1)
{
System.out.println(emp1);
}
}
}
if (command2==2)
{
System.out.println("請(qǐng)輸入要?jiǎng)h除的用戶的ID號(hào)碼");
int num=scanner.nextInt();
delectEmpById(num);
System.out.println("刪除用戶成功");
}
if (command2==1)
{
System.out.println("請(qǐng)輸入你的用戶名");
String name3=scanner.next();
System.out.println("請(qǐng)輸入你的密碼");
int num=scanner.nextInt();
System.out.println("請(qǐng)輸入你的郵箱");
String email=scanner.next();
System.out.println("請(qǐng)輸入你的生日");
String birthday=scanner.next();
Emp emp1=new Emp();
emp1.setSname(name3);
emp1.setPasswordnum(num);
emp1.setEmail(email);
emp1.setPermission("普通用戶");
String s=birthday;
SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
Date date=simpleDateFormat.parse(s);
emp1.setBirthday(date);
AddEmp(emp1);
System.out.println("添加用戶成功");
}
if (command2==3)
{
System.out.println("請(qǐng)輸入要修改的用戶的ID號(hào)碼");
int id=scanner.nextInt();
System.out.println("請(qǐng)輸入你要修改的姓名");
String newname=scanner.next();
System.out.println("請(qǐng)輸入你要修改的密碼");
int newpassword=scanner.nextInt();
System.out.println("請(qǐng)輸入要修改的郵箱");
String newemail=scanner.next();
System.out.println("請(qǐng)輸入要修改的用戶的權(quán)限(管路員/普通用戶)");
String word=scanner.next();
Emp emp1=new Emp();
emp1.setSid(id);
emp1.setSname(newname);
emp1.setPasswordnum(newpassword);
emp1.setEmail(newemail);
Emp emp2=getEmpById(id);
emp1.setBirthday(emp2.getBirthday());
emp1.setPermission(word);
update(emp1);
System.out.println("修改成功");
}
}
}
}
}
}
}
}
}
第二個(gè)副類
package wode;
import java.util.Date;
/**
* Created by ttc on 18-1-23.
*/
public class Emp {
private int sid;
private String sname;
private int passwordnum ;
private String email;
private String permission;
private Date birthday;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public int getPasswordnum() {
return passwordnum;
}
public void setPasswordnum(int passwordnum) {
this.passwordnum = passwordnum;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPermission() {
return permission;
}
public void setPermission(String permission) {
this.permission = permission;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Emp{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", passwordnum=" + passwordnum +
", email='" + email + '\'' +
", permission='" + permission + '\'' +
", birthday=" + birthday +
'}';
}
}
第三個(gè)類
package wode;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* Created by ttc on 18-1-23.
*/
public class JDBCUtils {
private static Connection conn=null;
private static String url;
private static String username;
private static String password;
static{
InputStream in =JDBCUtils .class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
try {
Class.forName(properties.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
try {
conn= DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
}
private JDBCUtils(){}
public static Connection getConn()
{
try {
conn = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(Statement statement, Connection connection)
{
if(statement != null)
{
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null)
{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet resultSet, Statement statement, Connection connection)
{
if(resultSet != null)
{
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null)
{
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null)
{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
