将用户表类实现的增删改查等方法抽取出来封装到工具类里面,使得调用起来更方便,将Statement改成封装后的PreparedStatement,使得调用时写的 sql 语句更清晰且不易搞错,也避免了sql注入的问题,使用Statement的话在地址栏后面加上:'or' 1 ‘=’ 1就可以轻易登录成功,而PreparedStatement封装了sql语句,故巧妙解决了此问题。具体见下述代码,先看目录结构:
User类代码如下:
package com.yibin.cn.dao.entity;
import java.util.Date;
public class User implements java.io.Serializable{
private Integer id;
private String name;
private String password;
private String sex;
private Date birthday;
private String address;
private String picturePath;
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(Integer id, String name, String password, String sex,
Date birthday, String address, String picturePath) {
super();
this.id = id;
this.name = name;
this.password = password;
this.sex = sex;
this.birthday = birthday;
this.address = address;
this.picturePath = picturePath;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getPicturePath() {
return picturePath;
}
public void setPicturePath(String picturePath) {
this.picturePath = picturePath;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", password=" + password
+ ", sex=" + sex + ", birthday=" + birthday + ", address="
+ address + ", picturePath=" + picturePath + "]";
}
}
接口类 IUserDao 代码如下:
package com.yibin.cn.dao.entity;
import java.util.List;
import utils.Page;
public interface IUserDao {
/***
* 根据用户名和密码查询一个用户信息,用于验证登录
*
* @param user
* @return
*/
public User getUserByNameAndPassword(User user) ;
/***
* 方法功能说明:添加用户
*
* @param user
* @return
*/
public int save(User user) ;
/***
* 方法功能说明:删除用户
*
* @param user
* @return
*/
public int delete(User user) ;
/***
* 方法功能说明:修改用户
*
* @param user
* @return
*/
public int update(User user) ;
//
public List<User> listAll() ;
public User getUserById(int id);
/***
* 查询总记录条数
* @return 查询获取的记录条数,无则为0
*/
public int getCount();
/***
* 查询某一页的详细数据
* @param currentPage 要查询的页码
* @param pageSize 每页记录数
* @return 成功则返回一页详细数据的集合,失败则返回空(null)
*/
public List<User> getOnePageInfo(int currentPage,int pageSize);
/***
* 方法功能说明:把一页的全部信息都封装起来
* @param currentPage 要查询的页码
* @param pageSize 每页记录数
* @return
*/
public Page<User> getPage(int currentPage,int pageSize);
}
接口实现类 UserDaoImpl 如下:
package com.yibin.cn.dao.entity.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import utils.Page;
import com.yibin.cn.dao.entity.IUserDao;
import com.yibin.cn.dao.entity.User;
import com.yibin.cn.dao.utils.jdbcUtil;
public class UserDaoImpl implements IUserDao{
/***
* 根据用户名和密码查询一个用户信息,用于验证登录
*
* @param user
* @return
*/
public User getUserByNameAndPassword(User user) {
User resUser = null;
Connection con = null;// 局部变量要初始化才能被使用。
PreparedStatement ps = null;
ResultSet res = null;
try {
con = jdbcUtil.getConnection();
//SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user` WHERE `name`='yi' AND `password`=666;
String loginCheckSql = "SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user` WHERE `name`=? AND `password`=?";
ps = con.prepareStatement(loginCheckSql);
ps.setObject(1, user.getName());
ps.setObject(2, user.getPassword());
res = ps.executeQuery();
while (res.next()) {
// 封装到一个user对象中
resUser = new User();
resUser.setId(res.getInt("id"));
resUser.setSex(res.getString("sex"));
resUser.setName(res.getString("name"));
resUser.setBirthday(res.getDate("birthday"));
resUser.setPassword(res.getString("password"));
resUser.setAddress(res.getString("address"));
resUser.setPicturePath(res.getString("picture_path"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
jdbcUtil.closeAll(con,ps,res);
}
return resUser;
}
/***
* 方法功能说明:添加用户
*
* @param user
* @return
*/
public int save(User user) {
//insertSql:INSERT INTO `user` VALUES (1,'男','保罗','19920809','123','美国洛杉矶','paul.jpg');
String insertSql = "INSERT INTO `user` VALUES (?,?,?,?,?,?,?)";
Object[] objects = {
user.getId(),user.getSex(),
user.getName(),user.getBirthday(),
user.getPassword(),user.getAddress(),
user.getPicturePath()
};
return jdbcUtil.executeUpdate(insertSql, objects);
}
/***
* 方法功能说明:删除用户
*
* @param user
* @return
*/
public int delete(User user) {
String deleteSql = "DELETE FROM `user` where id=?";
Object[] objects = {user.getId()};
return jdbcUtil.executeUpdate(deleteSql, objects);
}
/***
* 方法功能说明:修改用户
*
* @param user
* @return
*/
public int update(User user) {
// updateSql:UPDATE `user` SET `sex`='男',`name`='dd',`birthday`='1994-06-06',`password`='666',`address`='广东',`picture_path`='yi.jpg' WHERE id=2;
//Object[] objects = {};
if(user.getPicturePath()==""||user.getPicturePath()==null){
String updateSql = "UPDATE `user` SET `sex`=?,`name`=?,`birthday`=?,`password`=?,`address`=? WHERE id=?";
Object[] objects = {
user.getSex(),user.getName(),
user.getBirthday(),user.getPassword(),
user.getAddress(),user.getId()
};
return jdbcUtil.executeUpdate(updateSql, objects);
}else{
String updateSql = "UPDATE `user` SET `sex`=?,`name`=?,`birthday`=?,`password`=?,`address`=?,`picture_path`=? WHERE id=?";
Object[] objects = {
user.getSex(),user.getName(),
user.getBirthday(),user.getPassword(),
user.getAddress(),user.getPicturePath(),
user.getId()
};
return jdbcUtil.executeUpdate(updateSql, objects);
}
}
//
public List<User> listAll() {
List<User> users = new ArrayList<User>();
Connection con = null;// 局部变量要初始化才能被使用。
PreparedStatement ps = null;
ResultSet res = null;
try {
con = jdbcUtil.getConnection();
//sqlAll:SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user`
String sqlAll = "SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user`";
ps = con.prepareStatement(sqlAll);
res = ps.executeQuery();
while (res.next()) {
// 封装到一个user对象中
User user = new User();
user.setId(res.getInt("id"));
user.setSex(res.getString("sex"));
user.setName(res.getString("name"));
user.setBirthday(res.getDate("birthday"));
user.setPassword(res.getString("password"));
user.setAddress(res.getString("address"));
user.setPicturePath(res.getString("picture_path"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
jdbcUtil.closeAll(con, ps, res);
}
return users;
}
public User getUserById(int id) {
User resUser = null;
Connection con = null;// 局部变量要初始化才能被使用。
PreparedStatement ps = null;
ResultSet res = null;
try {
con = jdbcUtil.getConnection();
//SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user` WHERE id=2.
String selectUserByIdSql = "SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user` WHERE id=?";
ps = con.prepareStatement(selectUserByIdSql);
ps.setObject(1, id);
res = ps.executeQuery();
while (res.next()) {
// 封装到一个user对象中
resUser = new User();
resUser.setId(res.getInt("id"));
resUser.setSex(res.getString("sex"));
resUser.setName(res.getString("name"));
resUser.setBirthday(res.getDate("birthday"));
resUser.setPassword(res.getString("password"));
resUser.setAddress(res.getString("address"));
resUser.setPicturePath(res.getString("picture_path"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
jdbcUtil.closeAll(con, ps, res);
}
return resUser;
}
/***
* 查询总记录条数
* @return 查询获取的记录条数,无则为0
*/
public int getCount(){
int count = 0;
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
try {
conn = jdbcUtil.getConnection();
//SELECT COUNT(1) FROM `user`
//SELECT COUNT(1) AS total FROM `user`
String sqlCount = "SELECT COUNT(1) FROM `user`";//比COUNT(*)效率高。
//String sqlCount = "SELECT COUNT(1) AS total FROM `user`";//别名total。
ps = conn.prepareStatement(sqlCount);
res = ps.executeQuery();
if(res.next()){
count = res.getInt(1);//第一列
//count = res.getInt("total");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbcUtil.closeAll(conn, ps, res);
}
return count;
}
/***
* 查询某一页的详细数据
* @param currentPage 要查询的页码
* @param pageSize 每页记录数
* @return 成功则返回一页详细数据的集合,失败则返回空(null)
*/
public List<User> getOnePageInfo(int currentPage,int pageSize){
if(currentPage<1) {
System.out.println("注意页码是从1开始的!");
return null;
}
if(pageSize<1) {
System.out.println("注意每页显示条数是从1开始的!");
return null;
}
List<User> users = new ArrayList<User>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
//sqlPageInfo:SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user` LIMIT (currentPage-1)*pageSize,pageSize.
//sqlPageInfo:SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user` LIMIT 1,2.
String sqlPageInfo = "SELECT id,`sex`,`name`,`birthday`,`password`,`address`,`picture_path` FROM `user` LIMIT ?,?";
try {
conn = jdbcUtil.getConnection();
ps = conn.prepareStatement(sqlPageInfo);
ps.setObject(1, (currentPage-1)*pageSize);
ps.setObject(2, pageSize);
res = ps.executeQuery();
while(res.next()){
User user = new User();
user.setId(res.getInt("id"));
user.setName(res.getString("name"));
user.setPassword(res.getString("password"));
user.setSex(res.getString("sex"));
user.setBirthday(res.getDate("birthday"));
user.setAddress(res.getString("address"));
user.setPicturePath(res.getString("picture_path"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbcUtil.closeAll(conn, ps, res);
}
return users;
}
/***
* 方法功能说明:把一页的全部信息都封装起来
* @param currentPage 要查询的页码
* @param pageSize 每页记录数
* @return
*/
public Page<User> getPage(int currentPage,int pageSize){
int count = this.getCount();
List<User> pageList = this.getOnePageInfo(currentPage, pageSize);
return new Page<User>(currentPage, count, pageList, pageSize);
}
}
抽取的 jdbcUtil 工具类代码如下:
package com.yibin.cn.dao.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/* 工具类的抽取 */
public class jdbcUtil {
public static Connection conn = null;
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/helloworld", "root", "123456");
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeAll(Connection conn,Statement state,ResultSet res){
if (res != null) {
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/***
* PreparedStatement与通用的增删改操作
*/
public static int executeUpdate(String sql,Object[] objects){
int row = -1;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = jdbcUtil.getConnection();
ps = conn.prepareStatement(sql);//准备sql
if(objects!=null){
for(int i=0;i<objects.length;i++){//给sql中问号处设置参数
ps.setObject(i+1, objects[i]);
}
}
row = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
jdbcUtil.closeAll(conn, ps, null);
}
return row;
}
}
示例代码演示调用(方便许多):
IUserDao iUserDao = new UserDaoImpl();
User user = new User();
user.setName("yibin");
user.setPassword("444");
User resUser = iUserDao.getUserByNameAndPassword(user);