用数据库实现增删改查
druid.properties文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
username=root password=123456 initialSize=5 maxActive=10 maxWait=3000
pom.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<!--https://mvnrepository.com/-->
<groupId>org.example</groupId>
<artifactId>a20220927</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
<scope>provided</scope>
</dependency>
</dependencies>
</project>
用户类:
package a1.entity;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
//DTO 数据传输对象
@Data
public class User implements Serializable {
private Long id;
private String username;
private transient String password;
private Date birth;
private Boolean sex;
private Double salary;
}
工具类:
Jdbc工具类
package a1.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
public final class JdbcUtil {
private JdbcUtil() {
}
private static DataSource dataSource;
static {
try {
Properties pp = new Properties();
pp.load(JdbcUtil.class.getResourceAsStream("druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(pp);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws Exception {
return dataSource.getConnection();
}
public static PreparedStatement creatPreparedStatement(Connection connection, String sql, Object... params) throws Exception {
PreparedStatement ps = null;
if (connection != null && StringUtil.isNotBlank(sql)) {
ps = connection.prepareStatement(sql);
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
}
return ps;
}
public static ResultSet executeQuery(Connection connection, String sql, Object... params) throws Exception {
ResultSet rs = null;
PreparedStatement ps = creatPreparedStatement(connection, sql, params);
if (ps != null)
rs = ps.executeQuery();
return rs;
}
public static int executeUpdate(Connection connection, String sql, Object... params) throws Exception {
int res = -1;
PreparedStatement ps = creatPreparedStatement(connection, sql, params);
if (ps != null)
res = ps.executeUpdate();
return res;
}
public static void close(ResultSet rs, PreparedStatement ps, Connection con) throws Exception {
try {
if (rs != null)
rs.close();
} finally {
try {
if (ps != null)
ps.close();
} finally {
if (con != null)
con.close();
}
}
}
}
String工具类
package a1.util;
import java.security.MessageDigest;
import java.util.Base64;
public final class StringUtil {
private StringUtil(){}
public static boolean isBlank(String str){
return str==null || str.trim().length()<1;
}
public static boolean isNotBlank(String str){
return !isBlank(str);
}
public static String toMd5(String source,String salt){
try {
MessageDigest md = MessageDigest.getInstance("md5");
source+=salt;
byte[] target=md.digest(source.getBytes());
return Base64.getEncoder().encodeToString(target);
} catch (Exception e){
e.printStackTrace();
}
return "";
}
}
DaoFactory工具类
package a1.util;
import a1.dao.IUserDao;
import a1.dao.UserDaoImpl;
public class DaoFactory {
private DaoFactory(){}
private static IUserDao userDao= new UserDaoImpl();
public static IUserDao getUserDao(){
return new UserDaoImpl();
}
}
Dao
接口类
package a1.dao;
import a1.entity.User;
import java.util.List;
public interface IUserDao {
public User login(User user) throws Exception;
public boolean add(User user);
public boolean delete(String username);
public boolean modify(User user);
public List<User> search();
public boolean isExistsName(String username);
}
实现类
package a1.dao;
import a1.entity.User;
import a1.util.DaoFactory;
import a1.util.JdbcUtil;
import a1.util.StringUtil;
import lombok.SneakyThrows;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl implements IUserDao {
@Override
public User login(User user) throws Exception {
User user1 = null;
if (user == null || StringUtil.isBlank(user.getUsername()) || StringUtil.isBlank(user.getPassword()))
throw new IllegalArgumentException("参数错误");
String sql = "select * from tb_users where username = ? and password = ?";
List<Object> list = new ArrayList<>();
list.add(user.getUsername());
list.add(StringUtil.toMd5(user.getPassword(), user.getUsername()));
//list.add(user.getPassword());
Connection con = null;
ResultSet rs = null;
try {
con = JdbcUtil.getConnection();
rs = JdbcUtil.executeQuery(con, sql, list.toArray());
if (rs.next()) {
user1 = getObject(rs);
}
} finally {
JdbcUtil.close(rs, null, con);
}
return user1;
}
@SneakyThrows
public boolean isExistsName(String username) {
boolean res = true;
if (StringUtil.isBlank(username))
throw new IllegalArgumentException("参数错误!");
String sql = "select * from tb_users where username=?";
Connection connection = null;
ResultSet resultSet = null;
try {
connection = JdbcUtil.getConnection();
resultSet = JdbcUtil.executeQuery(connection, sql, username);
if (!resultSet.next())
res = false;
} finally {
JdbcUtil.close(resultSet, null, connection);
}
return res;
}
@SneakyThrows
@Override
public boolean add(User user) {
if (user == null || user.getUsername() == null || user.getPassword() == null)
throw new IllegalArgumentException("用户不能为空");
int res = -1;
List<Object> list = new ArrayList<>();
StringBuilder ss1 = new StringBuilder("insert into tb_users(username,password");
StringBuilder ss2 = new StringBuilder(")values(?,?");
list.add(user.getUsername());
list.add(user.getPassword());
if (user.getBirth() != null) {
ss1.append(",birth");
ss2.append(",?");
list.add(new java.sql.Date(user.getBirth().getTime()));
}
if (user.getSex() != null) {
ss1.append(",sex");
ss2.append(",?");
list.add(user.getSex());
}
if (user.getSalary() != null) {
ss1.append(",salary");
ss2.append(",?");
list.add(user.getSalary());
}
String sql = ss1.toString() + ss2.toString() + ")";
Connection con = null;
try {
con = JdbcUtil.getConnection();
res = JdbcUtil.executeUpdate(con, sql, list.toArray());
} finally {
JdbcUtil.close(null, null, con);
}
return res > 0;
}
@SneakyThrows
@Override
public boolean delete(String username) {
int res = 0;
if (StringUtil.isBlank(username))
throw new IllegalArgumentException("没有此用户!");
Connection con = null;
String sql = "delete from tb_users where username = ?";
try {
con = JdbcUtil.getConnection();
res = JdbcUtil.executeUpdate(con, sql, username);
} finally {
JdbcUtil.close(null, null, con);
}
return res > 0;
}
@SneakyThrows
@Override
public boolean modify(User user) {
int res = -1;
if (user == null || user.getId() == null)
throw new IllegalArgumentException("用户不能为空");
List<Object> list = new ArrayList<>();
StringBuilder ss = new StringBuilder("update tb_users set id = ? ");
list.add(user.getId());
if (StringUtil.isNotBlank(user.getUsername())) {
ss.append(",username = ?");
list.add(user.getUsername());
}
if (StringUtil.isNotBlank(user.getPassword())) {
ss.append(",password = ?");
list.add(StringUtil.toMd5(user.getPassword(), user.getUsername()));
}
if (user.getBirth() != null) {
ss.append(",birth = ?");
list.add(new java.sql.Date(user.getBirth().getTime()));
}
if (user.getSex() != null) {
ss.append(",sex = ?");
list.add(user.getSex());
}
if (user.getSalary() != null) {
ss.append(",salary = ?");
list.add(user.getSalary());
}
ss.append(" where id = " + user.getId());
Connection con = null;
try {
con = JdbcUtil.getConnection();
res = JdbcUtil.executeUpdate(con, ss.toString(), list.toArray());
} finally {
JdbcUtil.close(null, null, con);
}
return res > 0;
}
@SneakyThrows
@Override
public List<User> search() {
String sql = "select * from tb_users";
Connection con = null;
ResultSet rs = null;
List<User> list = new ArrayList<>();
try {
con = JdbcUtil.getConnection();
rs = JdbcUtil.executeQuery(con, sql, null);
while (rs.next()) {
User user = getObject(rs);
list.add(user);
}
} finally {
JdbcUtil.close(rs, null, con);
}
return list;
}
protected User getObject(ResultSet rs) throws Exception {
User user = new User();
user.setId(rs.getLong("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setBirth(rs.getDate("birth"));
user.setSex(rs.getBoolean("sex"));
user.setSalary(rs.getDouble("salary"));
return user;
}
public static void main(String[] args) {
IUserDao userDao = DaoFactory.getUserDao();
User user = new User();
// user.setId(11L);
// user.setBirth(new Date());
// user.setUsername("AAA");
// user.setSalary(9000.);
// boolean res = userDao.modify(user);
// System.out.println(res);
// user.setBirth(new Date());
// user.setUsername("AAA1");
// user.setPassword("123456");
// user.setSalary(9000.);
// boolean res = userDao.add(user);
// System.out.println(res);
// userDao.delete("AAA1");
userDao.search().forEach(System.out::println);
}
}
主方法
package a1.app;
import a1.dao.IUserDao;
import a1.entity.User;
import a1.util.DaoFactory;
import a1.util.StringUtil;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Main {
private static User sessionUser = null;
private static BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
private static IUserDao userDao = DaoFactory.getUserDao();
public static void main(String[] args) throws Exception {
while (true) {
menu();
String inputNum = br.readLine();
switch (inputNum) {
case "1":
while (true) {
System.out.println("请登录系统");
if (sessionUser == null) {
String username = inputString("用户名称");
String password = inputString("用户口令");
// System.out.println(username+"::"+password);
User user = new User();
user.setUsername(username);
user.setPassword(password);
user = userDao.login(user);
if (user != null) {
sessionUser = user;
System.out.println("登录成功!");
break;
} else
System.out.println("登录失败!请重新登录");
}
}
break;
case "2":
User user = new User();
while (true) {
String username = inputString("用户名称");
String password = inputString("用户口令");
String repassword = inputString("确认口令");
Date birth = inputDate("出生日期");
String ssex = inputString("性别");
boolean sex = "男".equals(ssex);
Double salary = inputDouble("就业薪资");
if (!password.equals(repassword)) {
System.out.println("两次输入口令必须一致 ");
} else {
if (userDao.isExistsName(username)) {
System.out.println("用户名称已经被占用!");
} else {
user.setUsername(username);
user.setPassword(StringUtil.toMd5(user.getPassword(), user.getUsername()));
user.setSex(sex);
user.setSalary(salary);
user.setBirth(birth);
break;
}
}
}
userDao.add(user);
System.out.println("新增用户成功!");
break;
case "0":
System.out.println("0");
System.exit(0);
break;
case "3":
User user1 = modifyUser();
boolean res1 = userDao.modify(user1);
System.out.println(res1 ? "修改成功" : "修改失败");
break;
case "4":
System.out.println("请输入要删除的用户名称:");
String username = br.readLine();
boolean res = userDao.delete(username);
System.out.println(res ? "删除成功" : "删除失败");
break;
case "5":
if (userDao.search() != null) {
userDao.search().forEach(System.out::println);
}
break;
case "6":
sessionUser = null;
break;
default:
break;
}
}
}
public static User modifyUser() throws Exception {
User res = new User();
// System.out.println("请输入要修改的用户信息");
System.out.println("请输入id:");
Long id = Long.parseLong(br.readLine());
String username = inputString("用户名称", 2);
String password = inputString("用户密码", 6, 20);
String repasswprd = inputString("确认密码", 6, 20);
Date birth = inputDate("生日");
String ssex = inputString("性别");
boolean sex = "男".equals(ssex);
Double salary = inputDouble("就业薪资");
if (!password.equals(repasswprd))
System.out.println("两次输入的密码不一致!!!");
else if (userDao.isExistsName(username))
System.out.println("该用户名已存在");
else {
res.setId(id);
res.setUsername(username);
res.setPassword(password);
res.setBirth(birth);
res.setSex(sex);
res.setSalary(salary);
}
return res;
}
// private static void inputLoginInfo(BufferedReader br) throws Exception {
// User user = new User();
// String username = inputString("用户名称", 2, 10);
// String password = inputString("密码", 6, 20);
// }
public static void menu() {
System.out.println("欢迎使用本系统...");
System.out.println("=============================================================");
System.out.println("1----登录系统 2----注册新用户");
if (sessionUser != null) {
System.out.println("3----修改用户信息 4----删除用户信息");
System.out.println("5----查看所有用户信息 6----退出登录");
}
//当用户登录成功,显示所有用户信息、修改指定编号的用户信息、删除指定编号的用户信息所有用户信息
System.out.println("0----退出系统");
System.out.println("=============================================================");
if (sessionUser == null) System.out.println("请选择(0-2)");
else System.out.println("请选择(0-6)");
}
public static String inputString(String labelName, int... params) {
String res = null;
while (true) {
System.out.println("请输入" + labelName + ":");
try {
res = br.readLine();
} catch (IOException e) {
e.printStackTrace();
}
if (res != null && res.trim().length() > 0) {
if (params != null && params.length > 0) {
if (params.length <= 1) {
if (res.length() >= params[0])
break;
} else if (params.length >= 2) {
if (res.length() >= params[0] && res.length() <= params[1])
break;
}
} else
break;
}
System.out.println("您输入的数据不合法,请重新输入!");
}
return res;
}
public static Date inputDate(String labelName) {
Date res = null;
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
while (true) {
String ss = null;
System.out.println("请输入" + labelName + ":");
try {
ss = br.readLine();
res = df.parse(ss);
break;
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("您输入的数据不合法,请重新输入!");
}
return res;
}
public static Double inputDouble(String labelName) {
Double res = null;
while (true) {
System.out.println("请输入" + labelName + ":");
try {
res = Double.valueOf(String.valueOf(br.readLine()));
break;
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("您输入的数据不合法,请重新输入!");
}
return res;
}
}
此系统实现了对于数据库的CRUD功能