jdbc 工具类的抽取

将用户表类实现的增删改查等方法抽取出来封装到工具类里面，使得调用起来更方便，将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);