使用JDBC实现smbms(超市订单管理)数据库,smbms_user(用户表)的CRUD操作。
时间: 2024-03-17 12:43:46 浏览: 63
好的,以下是使用JDBC实现smbms_user表的CRUD操作的示例代码。在这个示例中,我们假设您已经创建了一个名为smbms的数据库,并且已经为其创建了一个名为smbms_user的表,它有以下列:
- id (int, 主键, 自增长)
- user_code (varchar(20), 不可空)
- user_name (varchar(20), 不可空)
- user_password (varchar(20), 不可空)
- gender (int, 不可空)
- birthday (date)
- phone (varchar(20))
- address (varchar(200))
- user_role (int, 不可空)
首先,我们需要创建一个名为JdbcUtil的类来管理数据库连接,如下所示:
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcUtil {
private static final String URL = "jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf-8";
private static final String USERNAME = "root";
private static final String PASSWORD = "123456";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
```
接下来,我们可以创建一个名为UserDao的类来执行CRUD操作,如下所示:
```java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
public void addUser(User user) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("INSERT INTO smbms_user (user_code, user_name, user_password, gender, birthday, phone, address, user_role) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
ps.setString(1, user.getUserCode());
ps.setString(2, user.getUserName());
ps.setString(3, user.getUserPassword());
ps.setInt(4, user.getGender());
ps.setDate(5, user.getBirthday());
ps.setString(6, user.getPhone());
ps.setString(7, user.getAddress());
ps.setInt(8, user.getUserRole());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn, ps, null);
}
}
public void updateUser(User user) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("UPDATE smbms_user SET user_code=?, user_name=?, user_password=?, gender=?, birthday=?, phone=?, address=?, user_role=? WHERE id=?");
ps.setString(1, user.getUserCode());
ps.setString(2, user.getUserName());
ps.setString(3, user.getUserPassword());
ps.setInt(4, user.getGender());
ps.setDate(5, user.getBirthday());
ps.setString(6, user.getPhone());
ps.setString(7, user.getAddress());
ps.setInt(8, user.getUserRole());
ps.setInt(9, user.getId());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn, ps, null);
}
}
public void deleteUser(int id) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("DELETE FROM smbms_user WHERE id=?");
ps.setInt(1, id);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn, ps, null);
}
}
public User getUserById(int id) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("SELECT * FROM smbms_user WHERE id=?");
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
user = new User();
user.setId(rs.getInt("id"));
user.setUserCode(rs.getString("user_code"));
user.setUserName(rs.getString("user_name"));
user.setUserPassword(rs.getString("user_password"));
user.setGender(rs.getInt("gender"));
user.setBirthday(rs.getDate("birthday"));
user.setPhone(rs.getString("phone"));
user.setAddress(rs.getString("address"));
user.setUserRole(rs.getInt("user_role"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn, ps, rs);
}
return user;
}
public List<User> getUserList(String userName) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<User> userList = new ArrayList<>();
try {
conn = JdbcUtil.getConnection();
StringBuilder sql = new StringBuilder("SELECT * FROM smbms_user");
if (userName != null && !userName.isEmpty()) {
sql.append(" WHERE user_name LIKE ?");
}
ps = conn.prepareStatement(sql.toString());
if (userName != null && !userName.isEmpty()) {
ps.setString(1, "%" + userName + "%");
}
rs = ps.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUserCode(rs.getString("user_code"));
user.setUserName(rs.getString("user_name"));
user.setUserPassword(rs.getString("user_password"));
user.setGender(rs.getInt("gender"));
user.setBirthday(rs.getDate("birthday"));
user.setPhone(rs.getString("phone"));
user.setAddress(rs.getString("address"));
user.setUserRole(rs.getInt("user_role"));
userList.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn, ps, rs);
}
return userList;
}
}
```
在上述示例中,我们定义了一个名为UserDao的类,它有以下方法:
- addUser(User user):添加一个用户
- updateUser(User user):更新一个用户
- deleteUser(int id):删除一个用户
- getUserById(int id):根据ID获取一个用户
- getUserList(String userName):获取用户列表,可以通过指定userName参数来过滤结果
最后,我们可以编写一个简单的测试程序来测试UserDao的功能,如下所示:
```java
public class UserDaoTest {
public static void main(String[] args) {
UserDao userDao = new UserDao();
// 添加用户
User user1 = new User();
user1.setUserCode("001");
user1.setUserName("Tom");
user1.setUserPassword("123456");
user1.setGender(1);
user1.setBirthday(Date.valueOf("2000-01-01"));
user1.setPhone("12345678900");
user1.setAddress("Beijing");
user1.setUserRole(1);
userDao.addUser(user1);
// 更新用户
User user2 = userDao.getUserById(1);
user2.setUserName("Jerry");
userDao.updateUser(user2);
// 删除用户
userDao.deleteUser(1);
// 获取用户
List<User> userList = userDao.getUserList("J");
for (User user : userList) {
System.out.println(user);
}
}
}
```
在上述示例中,我们创建了一个名为UserDaoTest的类来测试UserDao的功能。我们首先添加了一个名为Tom的用户,然后更新了它的名字为Jerry,然后删除了该用户,最后获取了所有名字中包含字母J的用户。