1 配置文件
#这是一个数据库配置文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.110:3306/db01?&useSSL=false//去掉ssl警告
jdbc.username=root
jdbc.password=123456
2 连接池类
package com.yuxhu.jdbcTask;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp2.BasicDataSource;
public class DBUtilPool {
static String driver;
static String url;
static String username;
static String password;
static BasicDataSource bds;
//静态加载
static{
String accountConfig = "com/yuxhu/config/account.properties";
Properties pro = new Properties();
try {
//加载外面配置文件
pro.load(DBUtilPool.class.getClassLoader().getResourceAsStream(accountConfig));
driver = pro.getProperty("jdbc.driver");
url = pro.getProperty("jdbc.url");
username = pro.getProperty("jdbc.username");
password = pro.getProperty("jdbc.password");
//建立连接池
bds = new BasicDataSource();
bds.setDriverClassName(driver);
bds.setUrl(url);
bds.setUsername(username);
bds.setPassword(password);
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection(){
Connection conn = null;
if(bds == null){
System.out.println("连接池为空！");
return null;
}
try {
conn = bds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//关闭连接
public static void closeConnection(Connection conn){
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3 定义接口以及接口实现类
package com.yuxhu.jdbcTask;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface FunctionInterface {
public boolean Login(String username, String password); //登陆
public boolean Add(String username, String password);//增加用户
public boolean Delete(String username);//删除用户
public boolean modify(String option, String username, String newName);//修改用户
public boolean search(String username);//查找用户
}
class Function implements FunctionInterface{
Connection conn;
PreparedStatement ps;
// 登陆
public boolean Login(String username, String password) {
// 连接池拿连接
conn = DBUtilPool.getConnection();
// 准备好的声明
ps = null;
boolean result = false;
String sql = "select username, password from account where username = ?";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, username);
// 执行声明
ResultSet rs = ps.executeQuery();
// 判断是否有查询那结果
if (rs.next()) {
if (!(rs.getString(2).equals(password))) {
System.out.println("密码错误,请重新登陆！");
result = false;
}else{
System.out.println("登陆成功，请输入你的操作选项：");
result = true;
}
}else {
System.out.println("账号错误,请重新登陆！");
}
} catch (SQLException e) {
e.printStackTrace();
}
// 关闭
finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
DBUtilPool.closeConnection(conn);
}
return result;
}
// 增加用户
@Override
public boolean Add(String username, String password) {
// 连接池拿连接
conn = DBUtilPool.getConnection();
// 准备好的声明
ps = null;
boolean result = false;
String sql = "insert into account (username, password) value ( ? , ? )";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, password);
try {
// 执行声明
int rs = ps.executeUpdate();
// 判断是否有查询那结果
if (rs != 0) {
result = true;
System.out.println("添加用户成功");
} else {
throw new SQLException();
}
} catch (SQLException e) {
System.out.println("用户名已存在，添加失败，请重试");
}
} catch (SQLException e) {
e.printStackTrace();
}
// 关闭
finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
DBUtilPool.closeConnection(conn);
}
return result;
}
// 删除用户
@Override
public boolean Delete(String username) {
if(username.equals("adm")){
System.out.println("不能删除管理员！");
return false;
}
// 连接池拿连接
conn = DBUtilPool.getConnection();
// 准备好的声明
ps = null;
boolean result = false;
try {
String sql = null;
if(!(username.equals("all"))){
sql = "delete from account where username = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, username);
}else if(username.equals("all")){
sql = "delete from account where username <> ?";
ps = conn.prepareStatement(sql);
ps.setString(1, "adm");
}
try {
// 执行声明
int rs = ps.executeUpdate();
// 判断是否有查询那结果
if (rs != 0) {
result = true;
System.out.println("删除用户成功");
} else {
throw new SQLException();
}
} catch (SQLException e) {
System.out.println("用户名不存在，删除失败，请重试");
}
} catch (SQLException e) {
e.printStackTrace();
}
// 关闭
finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
DBUtilPool.closeConnection(conn);
}
return result;
}
// 修改用户
@Override
public boolean modify(String option, String username, String newName) {
// 连接池拿连接
conn = DBUtilPool.getConnection();
// 准备好的声明
ps = null;
boolean result = false;
//判断用户名是否存在
String exist = "select username from account where username = ?";
try {
ps = conn.prepareStatement(exist);
ps.setString(1, username);
ResultSet rs = ps.executeQuery();
if(!(rs.next())){
System.out.println("要修改的用户名不存在! 请重试");
return false;
}
ps.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
String sql = null;
String msg = null;
if (option.equals("1")) {
sql = "update account set username = ? where username = ?";
msg = "修改用户名成功！";
}
if (option.equals("2")) {
sql = "update account set password = ? where username = ?";
msg = "修改密码成功！";
}
if (sql != null) {
try {
ps = conn.prepareStatement(sql);
ps.setString(1, newName);
ps.setString(2, username);
try {
// 执行声明
int rs = ps.executeUpdate();
// 判断是否有查询那结果
if (rs != 0) {
result = true;
System.out.println(msg);
}
} catch (SQLException e) {
System.out.println("新用户名重名，修改失败，请重试");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
DBUtilPool.closeConnection(conn);
return result;
}
//查找用户
@Override
public boolean search(String username) {
boolean result = false;
conn = DBUtilPool.getConnection();
String sql = "select username, password from account where username like ?";
try {
if(username.equals("all")){
username = "%";
}
ps = conn.prepareStatement(sql);
ps.setString(1, username + "%");
ResultSet rs = ps.executeQuery();
if(!(rs.next())){
System.out.println("没有找到此用户！请重试！");
return false;
}else{
rs.previous();
System.out.println("你查看的用户信息如下：");
}
while (rs.next()) {
System.out.println("用户名：" + rs.getString(1) + "\t密码：" + rs.getString(2));
result = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
// 关闭
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
DBUtilPool.closeConnection(conn);
return result;
}
}
4 测试
package com.yuxhu.jdbcTask;
import java.util.Scanner;
public class LoginRegister {
public static void main(String[] args) {
Scanner input = new Scanner(System.in);
Function fun = new Function();
boolean result = false;
while(!result){
System.out.println("账号：");
String username = input.nextLine();
System.out.println("密码：");
String password = input.nextLine();
//1.登陆
result = fun.Login(username, password);
}
while(result){
System.out.println("1.增加用户");
System.out.println("2.删除用户");
System.out.println("3.修改用户");
System.out.println("4.查找用户");
System.out.println("5.退出系统");
String option = input.next();
//增加用户
if (option.equals("1")) {
boolean flag = false;
while (!flag) {
System.out.println("请输入要添加的用户名：(main 返回主菜单)");
String username = input.next();
if(username.equals("main")){
break;
}
System.out.println("请输入要添加的用户名密码：(main 返回主菜单)");
String password = input.next();
if(password.equals("main")){
break;
}
flag = fun.Add(username, password);
}
}
//删除用户
if(option.equals("2")){
boolean flag = false;
while (!flag) {
System.out.println("请输入要删除的用户名：(main 返回主菜单, all 删除全部)");
String username = input.next();
if(username.equals("main")){
break;
}
flag = fun.Delete(username);
}
}
//修改用户
if(option.equals("3")){
boolean flag = false;
while (!flag) {
System.out.println("请输入你要修改的用户名：(main 返回主菜单)");
String username = input.next();
if(username.equals("main")){
break;
}
System.out.println("请输入你要修改的选项：1.用户名 2.密码 (main 返回主菜单)");
String num = input.next();
if(num.equals("main")){
break;
}
if(num.equals("1")){
System.out.println("请输入新的用户名：(main 返回主菜单)");
String newName = input.next();
if(newName.equals("main")){
break;
}
flag = fun.modify(num,username,newName);
}
if(num.equals("2")){
System.out.println("请输入新的密码：(main 返回主菜单)");
String password = input.next();
if(password.equals("main")){
break;
}
flag = fun.modify(num,username,password);
}
}
}
//查找用户
if(option.equals("4")){
boolean flag = false;
while(!flag){
System.out.println("请输入你要查看的用户名：(all查看全部)");
String username = input.next();
flag = fun.search(username);
}
}
//退出系统
if(option.equals("5")){
System.out.println("已退出系统");
result = false;
}
}
//关闭Scanner
input.close();
}
}
