DAO层的CRUD

package com.org.core.dao;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

 

import com.org.core.pojo.Users;

import com.org.core.util.SqlHelper;

 

@SuppressWarnings("all")

public class UserDaoImpl  implements UserDao {

protected static ResultSet resultset = null;

protected static PreparedStatement pstat = null;

protected static Connection conn = null;

public Users login(String username, String password) {

Users users = null;

conn = SqlHelper.getInstance().getMySqlConnection();

String sql = "select * from users where name=? and password=?";

try {

pstat = conn.prepareStatement(sql);

pstat.setString(1, username);

pstat.setString(2, password);

resultset = pstat.executeQuery();

if (resultset.next()) {

users = new Users();

users.setId(resultset.getInt("id"));

users.setName(resultset.getString("name"));

users.setPassword(resultset.getString("password"));

}

} catch (SQLException e) {

e.printStackTrace();

}

return users;

}

 

public List<Users> listUser() {

List<Users> list = new ArrayList<Users>();

Users users = null;

conn = SqlHelper.getInstance().getMySqlConnection();

String sql = "select * from users";

try {

pstat = conn.prepareStatement(sql);

resultset = pstat.executeQuery();

while (resultset.next()) {

users = new Users();

users.setName(resultset.getString("name"));

users.setPassword(resultset.getString("password"));

users.setCreateTime(resultset.getString("createTime"));

users.setExpireTime(resultset.getString("expireTime"));

users.setId(resultset.getInt("id"));

list.add(users);

}

} catch (SQLException e) {

e.printStackTrace();

}

return list;

 

}

 

public int addUser(Users user) {

int result = 0;

String sql = "Insert into Users(name,password,createTime,expireTime) values(?,?,?,?)";

conn = SqlHelper.getInstance().getMySqlConnection();

try {

pstat = conn.prepareStatement(sql);

pstat.setString(1, user.getName());

pstat.setString(2, user.getPassword());

pstat.setString(3, user.getCreateTime().toString());

pstat.setString(4, user.getExpireTime().toString());

result = pstat.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}

return result;

}

 

public int deleteUser(int userId) {

int result = 0;

String sql = "delete from users where id=?";

try {

pstat = conn.prepareStatement(sql);

pstat.setInt(1, userId);

result = pstat.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}

return result;

}

 

public int updateUser(int userId) {

Users users = new Users();

int result = 0;

String sql = "update users set name=?,password=? ,createTime=?, ExpireTime=? where id=?";

try {

pstat = conn.prepareStatement(sql);

pstat.setString(1, users.getName());

pstat.setString(2, users.getPassword());

pstat.setString(3, users.getCreateTime());

pstat.setString(4, users.getExpireTime());

pstat.setInt(5, userId);

result = pstat.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}

return result;

}

}