package com.zhongruan.dao.impl;
import com.zhongruan.dao.IUserDao;
import com.zhongruan.model.User;
import com.zhongruan.util.DBUtil;
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 UserDaoImpl implements IUserDao {
@Override
public User findUserByUsername(String username) {
User user = null;
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
//3写sql
String sql = "select * from tb_user where username=?";
statement = connection.prepareStatement(sql);
statement.setString(1, username);
resultSet = statement.executeQuery();
while (resultSet.next()) {
user = new User();
user.setId(resultSet.getInt(1));
user.setUsername(username);
user.setPassword(resultSet.getString(3));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.closeAll(resultSet, statement, connection);
}
return user;
}
@Override
public List<User> findAll() {
List<User> users=new ArrayList<>();
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
try {
connection = DBUtil.getConnection();
String sql="select * from tb_user";
statement= connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()){
User user=new User();
user.setId(resultSet.getInt(1));
user.setUsername(resultSet.getString(2));
user.setPassword(resultSet.getString(3));
users.add(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.closeAll(resultSet,statement,connection);
}
return users;
}
@Override
public void delete(int id) {
Connection connection=null;
PreparedStatement statement=null;
try {
connection=DBUtil.getConnection();
statement = connection.prepareStatement("delete from tb_user where id =?");
statement.setInt(1,id);
statement.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.closeAll(null,statement,connection);
}
}
@Override
public void add(String username, String password) {
Connection connection=null;
PreparedStatement statement=null;
try {
connection=DBUtil.getConnection();
statement = connection.prepareStatement("insert into tb_user(username,password) values (?,?)");
statement.setString(1,username);
statement.setString(2,password);
statement.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.closeAll(null,statement,connection);
}
}
public User findUserById(int id){
User user = null;
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
//3写sql
String sql = "select * from tb_user where id=?";
statement = connection.prepareStatement(sql);
statement.setInt(1, id);
resultSet = statement.executeQuery();
while (resultSet.next()) {
user = new User();
user.setId(resultSet.getInt(1));
user.setUsername(resultSet.getString(2));
user.setPassword(resultSet.getString(3));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.closeAll(resultSet, statement, connection);
}
return user;
}
@Override
public void update(int id,String username,String password) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection=DBUtil.getConnection();
String sql="update tb_user set username=?,password=? where id=?";
statement=connection.prepareStatement(sql);
statement.setInt(3,id);
statement.setString(1,username);
statement.setString(2,password);
statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
finally {
DBUtil.closeAll(null,statement,connection);
}
}
@Override
public List<User> findByPage(String username,int start, int size) {
List<User> users=new ArrayList<>();
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
String sql=null;
try {
connection = DBUtil.getConnection();
if (username == null) {
sql = "select * from tb_user limit ?,?";
statement = connection.prepareStatement(sql);
statement.setInt(1, start);
statement.setInt(2, size);
} else {
sql = "select * from tb_user where username like ? limit ?,?";
statement = connection.prepareStatement(sql);
statement.setString(1, "%" + username + "%");
statement.setInt(2, start);
statement.setInt(3, size);
}
resultSet = statement.executeQuery();
while (resultSet.next()){
User user=new User();
user.setId(resultSet.getInt(1));
user.setUsername(resultSet.getString(2));
user.setPassword(resultSet.getString(3));
users.add(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.closeAll(resultSet,statement,connection);
}
return users;
}
@Override
public int selectCount() {
List<User> users=new ArrayList<>();
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
int count=0;
try {
connection = DBUtil.getConnection();
String sql="select count(*) from tb_user ";
statement= connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()){
count=resultSet.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.closeAll(resultSet,statement,connection);
}
return count;
}
}
10.9
最新推荐文章于 2024-10-09 19:21:56 发布