实体类(User)
package entity;
import java.sql.Date;
public class User {
private int id;
private String userName;
private String passwd;
private String nickName;
private int sex;
private String birth;
private String img;
private String phone;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public String getBirth() {
return birth;
}
public void setBirth(String birth) {
this.birth = birth;
}
public String getImg() {
return img;
}
public void setImg(String img) {
this.img = img;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
实体类(custom)
package entity;
public class Custom {
private int id;
private String name;
private int age;
private int sex;
private String phone;
private String wechat;
private String addr;
private String hoby;
private String email;
private String occupation;
private int uid;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getWechat() {
return wechat;
}
public void setWechat(String wechat) {
this.wechat = wechat;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public String getHoby() {
return hoby;
}
public void setHoby(String hoby) {
this.hoby = hoby;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getOccupation() {
return occupation;
}
public void setOccupation(String occupation) {
this.occupation = occupation;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
}
工具类(BaseDao)
package utils;
import java.sql.*;
public class BaseDAO {
//数据库连接地址
private String url ="jdbc:mysql://127.0.0.1:3306/crm?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
//数据库账号
private String user ="root";
//数据库密码
private String password="123456";
//数据库驱动路径
private String className="com.mysql.cj.jdbc.Driver";
private Connection connection; //数据库连接对象
private PreparedStatement pst; //向数据库发送SQL指令
private ResultSet rs; //结果集
/**
* 获取数据库的连接对象
*/
public Connection getConnection() {
if(connection == null) {
try {
//2.加载驱动
Class.forName(className);
//3.连接数据库,得到连接对象
connection = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
}
return connection;
}
/**
* 释放资源
*/
public void closeAll() {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(pst != null) {
pst.close();
pst = null;
}
if(connection != null) {
connection.close();
connection = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 获取PreparedStatment对象
*/
public PreparedStatement getPreparedStatement(String sql) {
if(this.pst == null) {
try {
this.pst = getConnection().prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
return this.pst;
}
/**
* 增、删、改的方法
*/
public int excuteUpdate(String sql) {
int result = 0;
try {
this.pst = getPreparedStatement(sql);
result = this.pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll();
}
return result;
}
/**
* 查询的方法
*/
public ResultSet executeQuery(String sql) {
try {
this.pst = getPreparedStatement(sql);
this.rs = this.pst.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return this.rs;
}
}
数据访问类(dao)
抽象类/方法(UserDao)
package dao;
import entity.User;
import java.util.List;
public interface UserDao {
/**
* 登录
*/
User login(String userName,String passwd);
/**
* 添加用户
*/
int insertUser(User user);
/**
* 查询全部用户
*/
List<User> selectAll();
/**
* 根据id删除用户
*/
public int deleteById(int id);
/**
* 根据id查询用户信息
* @param id
* @return
*/
User selectById(int id);
/**
* 修改用户信息
* @return
*/
int updateById(User user);
}
dao实现类
用户dao(UserDao)
package dao.impl;
import dao.UserDao;
import entity.User;
import utils.BaseDAO;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl extends BaseDAO implements UserDao {
public User login(String userName, String passwd) {
User user = null;
String sqlLogin = "select * from t_user where user_name='"+userName+"' and passwd='"+passwd+"'";
ResultSet rs = executeQuery(sqlLogin);
try {
if (rs.next()){
user = new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setPasswd(rs.getString("passwd"));
user.setNickName(rs.getString("nick_name"));
user.setSex(rs.getInt("sex"));
user.setBirth(rs.getString("birth"));
user.setImg(rs.getString("img"));
user.setPhone(rs.getString("phone"));
}
closeAll();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return user;
}
public int insertUser(User user) {
String sql = "insert into t_user (user_name,passwd,nick_name,sex,birth,img,phone) values ('"+user.getUserName()+"','123456','"+user.getNickName()+"',"+user.getSex()+",'"+user.getBirth()+"','https://img1.baidu.com/it/u=1208847068,4041283913&fm=253&fmt=auto&app=138&f=JPEG?w=500&h=500','"+user.getPhone()+"')";
int i = excuteUpdate(sql);
return i;
}
public List<User> selectAll() {
List<User> list = new ArrayList<User>();
String selectAllSql = "select * from t_user order by id desc";
ResultSet rs = executeQuery(selectAllSql);
try {
while (rs.next()){
User user = new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setPasswd(rs.getString("passwd"));
user.setNickName(rs.getString("nick_name"));
user.setSex(rs.getInt("sex"));
user.setPhone(rs.getString("phone"));
user.setImg(rs.getString("img"));
user.setBirth(rs.getString("birth"));
list.add(user);
}
closeAll();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return list;
}
public int deleteById(int id) {
String sql = "delete from t_user where id ="+id;
return excuteUpdate(sql);
}
public User selectById(int id) {
String sql = "select * from t_user where id=" + id;
ResultSet rs = executeQuery(sql);
User user = null;
try {
if (rs.next()){
user = new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setPasswd(rs.getString("passwd"));
user.setNickName(rs.getString("nick_name"));
user.setSex(rs.getInt("sex"));
user.setBirth(rs.getString("birth"));
user.setImg(rs.getString("img"));
user.setPhone(rs.getString("phone"));
}
closeAll();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return user;
}
public int updateById(User user) {
String sql = "update t_user set user_name='"+user.getUserName()+"', nick_name='"+user.getNickName()+"', sex='"+user.getSex()+"', birth='"+user.getBirth()+"', phone='"+user.getPhone()+"' where id="+user.getId();
return excuteUpdate(sql);
}
}
