UserDAOimplement

package dao.impl;

import bean.User;
import dao.UserDao;
import dbutils.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoimpl implements UserDao {
public User findUserPhoneNum(String phoneNum){
if (phoneNum == null) {
return null;
}
Connection conn = DBHelper.getConnection();
String sql = "select * from User WHERE phoneNum=?;";
PreparedStatement stat = null;
ResultSet rs = null;
User u = new User();
try {
stat = conn.prepareStatement(sql);
stat.setString(1, phoneNum);
rs = stat.executeQuery();
while (rs.next()) {
u.setId(rs.getString("id"));
u.setPhoneNum(rs.getString("phoneNum"));
u.setName(rs.getString("name"));
u.setPassword(rs.getString("password"));
u.setfree_time(rs.getDouble("free_time"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.closeAll(conn, stat, rs);
}
if (u.getPhoneNum().equals(" ")) {
return null;
} else {
return u;
}
}

public User findUserById(String id) {
if (id == null) {
return null;
}
Connection conn = DBHelper.getConnection();
String sql = "select * from User WHERE id=?;";
PreparedStatement stat = null;
ResultSet rs = null;
User u = new User();
try {
stat = conn.prepareStatement(sql);
stat.setString(1, id);
rs = stat.executeQuery();
while (rs.next()) {
u.setId(rs.getString("id"));
u.setPhoneNum(rs.getString("phoneNum"));
u.setName(rs.getString("name"));
u.setPassword(rs.getString("password"));
u.setfree_time(rs.getDouble("free_time"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.closeAll(conn, stat, rs);
}
if (u.getId().equals(" ")) {
return null;
} else {
return u;
}
}

public void createNewUser() {
Connection conn = DBHelper.getConnection();
String sql = "INSERT user(id,phoneNum,name,password,free_time) VALUE(null,null,null,null," +
"null);";
PreparedStatement stat = null;
ResultSet rs = null;
try {
stat = conn.prepareStatement(sql);
stat.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.closeAll(conn, stat, rs);
}
}


public void addUser(String phoneNum, String id, int serialNum) {
Connection conn = DBHelper.getConnection();
String sql = "UPDATE User SET id=?, phoneNum=? WHERE serialNum=?";
PreparedStatement stat = null;
ResultSet rs = null;
try {
stat = conn.prepareStatement(sql);
stat.setString(1, id);
stat.setString(2, phoneNum);
stat.setInt(3, serialNum);
stat.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.closeAll(conn, stat, rs);
}
}

public void upDataUser(User user) {
Connection conn = DBHelper.getConnection();
String sql = "UPDATE User SET name =?, phoneNum=?,password=?,free_time=? WHERE id=?;";
PreparedStatement stat = null;
ResultSet rs = null;
try {
stat = conn.prepareStatement(sql);
stat.setString(1, user.getName());
stat.setString(2, user.getPhoneNum());
stat.setString(3, user.getPassword());
stat.setDouble(4, user.getfree_time());
stat.setString(5, user.getId());
stat.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.closeAll(conn, stat, rs);
}
}

public int getSerial() {
int serialNum = 0;
Connection conn = DBHelper.getConnection();
String sql = "select * from User where id is null;";
PreparedStatement stat = null;
ResultSet rs = null;
try {
stat = conn.prepareStatement(sql);
rs = stat.executeQuery();
while (rs.next()) {
serialNum = rs.getInt("serialNum");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.closeAll(conn, stat, rs);
}
return serialNum;
}
}