package com.wzc.login.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.wzc.login.domain.User;
/**
* @description 資料庫連接與操作類用于增刪改查資料并回傳給servlet使用
* @author WANGZIC
*
*/
public class UserDao {
/**
* 新增用戶
* @param user 用戶資訊
* @return 新增是否成功
*/
public boolean insert(User user) {
Connection con = getConnection();
PreparedStatement pstmt =null;
String sql = "INSERT INTO user(username,nickname,password,gender,phone,email,address) VALUES(?,?,?,?,?,?,?)";
boolean res = false;
try {
pstmt = con.prepareStatement(sql);
pstmt.setString(1,user.getUsername());
pstmt.setString(2,user.getNickname());
pstmt.setString(3,user.getPassword());
pstmt.setString(4,user.getGender());
pstmt.setString(5,user.getPhone());
pstmt.setString(6,user.getEmail());
pstmt.setString(7,user.getAddress());
res = (pstmt.executeUpdate()==1);
}catch (SQLException e) {
if(!e.getMessage().contains("PRIMARY")){
e.printStackTrace();
}else {
System.out.println("該用戶名已存在");
return false;
}
}finally {
closeCon(con,pstmt);
}
return res;
}
/**
* 根據用戶id洗掉用戶資訊
* @param userid 用戶id
* @return 洗掉是否成功
*/
public boolean delete(Integer userid) {
Connection con = getConnection();
PreparedStatement pstmt =null;
String sql = "delete from user where userid= ?";
boolean res = false;
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,userid);
res = (pstmt.executeUpdate()==1);
}catch (SQLException e) {
e.printStackTrace();
return false;
}finally {
closeCon(con,pstmt);
}
return res;
}
/**
* 更新用戶資訊
* @param user 修改后的用戶資訊
* @return 更新是否成功
*/
public boolean update(User user) {
Connection con = getConnection();
PreparedStatement pstmt =null;
String sql = "update user set username=?,nickname=?,password=?,gender=?,phone=?,email=?,address=? where userid= ?";
boolean res = false;
try {
pstmt = con.prepareStatement(sql);
pstmt.setString(1,user.getUsername());
pstmt.setString(2,user.getNickname());
pstmt.setString(3,user.getPassword());
pstmt.setString(4,user.getGender());
pstmt.setString(5,user.getPhone());
pstmt.setString(6,user.getEmail());
pstmt.setString(7,user.getAddress());
pstmt.setInt(8,user.getUserid());
res = (pstmt.executeUpdate()==1);
}catch (SQLException e) {
e.printStackTrace();
return false;
}finally {
closeCon(con,pstmt);
}
return res;
}
/**
* 根據用戶名查找用戶密碼
* @param username 用戶名
* @return 用戶資訊
*/
public User selectByUsername(String username){
List<User> list = new ArrayList<>();
String sql = "select * from user where username=?";
Connection con =getConnection();
PreparedStatement pstmt =null;
ResultSet rs;
try {
pstmt = con.prepareStatement(sql);
pstmt.setString(1, username);
rs = pstmt.executeQuery();
list=resultSetToBean(rs);
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeCon(con,pstmt);
}
return list.isEmpty()?null:list.get(0);
}
/**
* 根據用戶id查詢用戶資訊
* @param userid 用戶id
* @return 用戶資訊
*/
public User selectByUserId(Integer userid) {
List<User> list = new ArrayList<>();
String sql = "select * from user where userid= ?";
Connection con =getConnection();
PreparedStatement pstmt =null;
ResultSet rs;
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,userid);
rs = pstmt.executeQuery();
list=resultSetToBean(rs);
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeCon(con,pstmt);
}
return list.isEmpty()?null:list.get(0);
}
/**
* 根據傳入的引數查詢用戶
* @param paramMap 引數Map
* @return 用戶串列
*/
public List<User> selectByMap(Map<String,Object> paramMap) {
List<User> list = new ArrayList<>();
StringBuilder sql =new StringBuilder("select * from user where 1=1");
List<Object> paramList = new ArrayList<>();
for(String key:paramMap.keySet()){
sql.append(" and ").append(key).append(" = ?");
paramList.add(paramMap.get(key));
}
Connection con =getConnection();
PreparedStatement pstmt =null;
try {
pstmt = con.prepareStatement(sql.toString());
for(int i=0;i<paramList.size();i++){
pstmt.setObject(i,paramList.get(i));
}
list=resultSetToBean(pstmt.executeQuery());
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeCon(con,pstmt);
}
return list;
}
/**
* 將查詢結果轉換位物件
* @param rs 查詢結果
* @return 用戶串列
* @throws SQLException
*/
private static List<User> resultSetToBean(ResultSet rs) throws SQLException {
List<User> list = new ArrayList<>();
while (rs.next()){
User user = new User();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setNickname(rs.getString("nickname"));
user.setPassword(rs.getString("password"));
user.setPhone(rs.getString("phone"));
user.setGender(rs.getString("gender"));
user.setEmail(rs.getString("email"));
user.setAddress(rs.getString("address"));
list.add(user);
}
return list;
}
/**
* 獲取JDBC連接
*/
private static Connection getConnection(){
//記得依賴mysql-jdbc驅動包
String driver ="com.mysql.jdbc.Driver";
//修改為自己的資料庫
String url ="jdbc:mysql://localhost:3306/test";
//修改未自己資料庫的用戶名密碼
String user ="root";
//修改未自己資料庫的名密碼
String password ="a13246001518";
Connection connection =null;
try {
Class.forName(driver);
connection =DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 關閉JDBC連接
* @param con 連接實體
* @param pstmt PreparedStatement實體
*/
private static void closeCon(Connection con, PreparedStatement pstmt){
try {
if(pstmt!=null) {
pstmt.close();
}
if(con!=null) {
con.close();
}
}catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 測驗資料庫連接是否正常
* @param args
*/
// public static void main(String[] args) {
// System.out.println(new UserDao().selectByUsername("123"));
// System.out.println(new UserDao().selectByMap(new HashMap<>(0)));
// }
}
uj5u.com熱心網友回復：
空指標這么簡單的問題，你自己都說是空指標了，檢查下connection =DriverManager.getConnection(url, user, password);這段代碼，對于的資料庫連接，用戶名、密碼是否正確吧，另外你自己的堆疊資訊都列印出來了，還不能看出錯誤？/**
* 獲取JDBC連接
*/
private static Connection getConnection(){
//記得依賴mysql-jdbc驅動包
String driver ="com.mysql.jdbc.Driver";
//修改為自己的資料庫
String url ="jdbc:mysql://localhost:3306/test";
//修改未自己資料庫的用戶名密碼
String user ="root";
//修改未自己資料庫的名密碼
String password ="a13246001518";
Connection connection =null;
try {
Class.forName(driver);
connection =DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return connection;
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/83525.html
標籤：Web 開發
上一篇：為什么我的JList會出現警告
下一篇：大神們停一下腳步