MVC_Database.zip_mvc 数据库

package com.markliu.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import com.markliu.db.DBMysqlUtil; import com.markliu.model.User; /** * DAO :（Data Access Objects) 数据访问对象，数据访问层。 一般我们写的DAO类都是进行数据操作的类， * 里面的代码都是对于数据库中的数据做增删改查等等.. * * @author markliu * */ public class UserDataAccess { /** * 添加用户数据 * * @param user * @throws SQLException * @throws Exception */ public void addUser(User user) throws SQLException { Connection connection = DBMysqlUtil.getMysqlConnection(); String insertsql = "INSERT INTO users "+ "(name,password,phone,address) "+ "VALUES (?,password(?),?,?)"; // 使用占位符操作 PreparedStatement prepareState = connection.prepareStatement(insertsql); prepareState.setString(1,user.getName()); prepareState.setString(2,user.getPassword()); prepareState.setString(3,user.getPhone()); prepareState.setString(4,user.getAddress()); prepareState.execute(); } /** * 根据id号删除数据 * * @param person * @throws SQLException */ public void deleteUser(Integer id) throws SQLException { Connection connection = DBMysqlUtil.getMysqlConnection(); String insertsql = "DELETE FROM users "+ "WHERE user_id=?"; // 使用占位符操作 PreparedStatement prepareState = connection.prepareStatement(insertsql); prepareState.setInt(1, id); prepareState.execute(); // 关闭资源 prepareState.close(); } /** * 更新数据 * @param user * @throws SQLException */ public void update(User user) throws SQLException { Connection connection = DBMysqlUtil.getMysqlConnection(); String sql = "UPDATE users "+ "SET name=?, password=password(?), phone=?, address=? "+ "WHERE user_id=?"; // 使用占位符操作 PreparedStatement prepareState = connection.prepareStatement(sql); prepareState.setString(1,user.getName()); prepareState.setString(2,user.getPassword()); prepareState.setString(3,user.getPhone()); prepareState.setString(4,user.getAddress()); prepareState.setInt(5,user.getUserid()); prepareState.execute(); // 关闭资源 prepareState.close(); } /** * 查找数据 * * @return * @throws SQLException */ public List<User> query() throws SQLException { List<User> users = new ArrayList<User>(); Connection connection = DBMysqlUtil.getMysqlConnection(); String insertsql = "SELECT * FROM users "; PreparedStatement prepareState = connection.prepareStatement(insertsql); java.sql.ResultSet results = prepareState.executeQuery(); User user = null; while(results.next()) { user = new User(); user.setName(results.getString("name")); user.setPassword(results.getString("password")); user.setPhone(results.getString("phone")); user.setAddress(results.getString("address")); users.add(user); } // 关闭资源 results.close(); prepareState.close(); return users; } /** * 工具id号获取用户 * @param user_id * @return * @throws SQLException */ public User getUserById(Integer user_id) throws SQLException { Connection connection = DBMysqlUtil.getMysqlConnection(); String sql = "SELECT * FROM users "+ "WHERE user_id=?"; // 使用占位符操作 PreparedStatement prepareState = connection.prepareStatement(sql); prepareState.setInt(1,user_id); java.sql.ResultSet results = prepareState.executeQuery(); /* * 即使你十分确定能搜出记录，也不可以在没有rs.next()之前直接对rs进行取值。 * 这涉及到rs对象的存储方法。里面说白了就是指针。没next，指针根本没指向对应记录 * User user = (User) results.getObject(1); // 注意此处不能这样写！ */ User user = null; while(results.next()) { user = new User(); user.setName(results.getString("name")); user.setPassword(results.getString("password")); user.setPhone(results.getString("phone")); user.setAddress(results.getString("address")); } // 关闭资源 results.close(); prepareState.close(); return user; } /** * 工具给定字段查询名字包含该字段的用户 * @param containName * @return * @throws SQLException */ public List<User> getUsersContainName(String containName) throws SQLException { List<User> users = new ArrayList<User>(); Connection connection = DBMysqlUtil.getMysqlConnection(); String sql = "SELECT * FROM users "+ "WHERE name like ?"; PreparedStatement prepareState = connection.prepareStatement(sql); prepareState.setString(1, "%"+containName+"%"); java.sql.ResultSet results = prepareState.executeQuery(); User user = null; while(results.next()) { user = new User(); user.setName(results.getString("name")); user.setPassword(results.getString("password")); user.setPhone(results.getString("phone")); user.setAddress(results.getString("address")); users.add(user); } // 关闭资源 results.close(); prepareState.close(); return users; } /** * 通过传入多个参数进行模糊胡查询。 * 使用示例： * <pre>{@code * List<Map<String, Object>> params = new ArrayList<Map<String, Object>>(); * Map<String, Object> param = new HashMap<String, Object>(); * param.put("name", "name"); * param.put("relative", "like"); * param.put("value", "'%刘%'"); * params.add(param); * List<User> users = userDao.getUsersByParams(params); * for (User user : users) { * System.out.println(user); * } * </pre> * * @param params * @return * @throws SQLException */ public List<User> getUsersByParams(List<Map<String, Object>> params) throws SQLException { if (params == null || params.size()<=0) { return null; } List<User> users = new ArrayList<User>(); Connection connection = DBMysqlUtil.getMysqlConnection(); StringBuffer sql = new StringBuffer("SELECT * FROM users WHERE 1=1"); // 小技巧，为了使and统一进行循环，此处加上1=1,记住 for(Map<String, Object> param : params) { sql.append(" and " + param.get("name") + " " + param.get("relative") + " " + param.get("value")); } System.out.println(sql); PreparedStatement prepareState = connection.prepareStatement(sql.toString()); java.sql.ResultSet results = prepareState.executeQuery(); User user = null; while(results.next()) { user = new User(); user.setUserid(results.getInt("user_id")); user.setName(results.getString("name")); user.setPassword(results.getString("password")); user.setPhone(results.getString("phone")); user.setAddress(results.getString("address")); users.add(user); } // 关闭资源 results.close(); prepareState.close(); return users; } }