数据库交互 dao层

 

UserDao.java

package com.dao;

import com.domain.User;
import com.util.DBUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
* 数据库交互 dao层
*/
public class UserDao {
public List<User> getAllUser(){
List<User> list = new ArrayList<>();
Connection conn = DBUtil.getConn();
String sql = "select * from user";

try {
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while(rs.next()){
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
list.add(user);
}
rs.close();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}

public boolean addUser(User user){
String sql = "insert into user(username, password) values(?,?)";
Connection conn = DBUtil.getConn();

try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, user.getUsername());
pst.setString(2, user.getPassword());
int count = pst.executeUpdate();
pst.close();
return count>0 ? true:false;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}

public boolean deleteUser(int id){
String sql = "delete from user where id = ?";
Connection conn = DBUtil.getConn();

try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setInt(1, id);
int count = 0;
count = pst.executeUpdate();
pst.close();
return count>0 ? true:false;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}

public boolean updateUser(User user){
String sql = "update user set username=?,password=? where id=?";
Connection conn = DBUtil.getConn();

try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1,user.getUsername());
pst.setString(2, user.getPassword());
pst.setInt(3, user.getId());
int count = pst.executeUpdate();
pst.close();
return count>0 ? true:false;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}

public User findUserById(int id){
String sql = "select * from user where id = " + id;
Connection conn = DBUtil.getConn();
User user = null;

try {
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while (rs.next()){
user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
}
rs.close();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}

测试@Test
public void test(){
User user = new User();
user.setUsername("admin1");
user.setPassword("123456");
UserDao ud = new UserDao();
boolean b = ud.addUser(user);
if(b){
System.out.println("添加数据成功");
}
}

}