使用JDBC实现smbms(超市订单管理)数据库，smbms_user(用户表)的CRUD操作。

好的，以下是使用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的类来管理数据库连接，如下所示：

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操作，如下所示：

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的功能，如下所示：

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的用户。