JDBC 进阶:使用封装通用DML DQL 和结构分层以及at com.mysql.jdbc.PreparedStatement.setTimestamp空指针异常解决

准备：

• 数据表

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) DEFAULT NULL,
  `pwd` varchar(10) DEFAULT NULL,
  `regTime` date DEFAULT NULL,
  `lastLoginTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20227 DEFAULT CHARSET=utf8;

• jar包：
  
  注意！ 我这里使用的驱动包时5.1.48
  如果使用了5.1.47会有个setTimestamp的空指针异常，网上有大神建议回退到5.1.46；
  不过新版本出来了，我这边就使用新版本了。
  这个bug MySQL官网有说明

[6 Jul 2019 0:44] Daniel So
Posted by developer:

Added the following entry to the Connector/J 5.1.48 changelog:

"PreparedStatement.setTimestamp threw a NullPointerException if getParameterMetaData() was called before the statement was executed. This fix adds the missing null checks to getParameterMetaData() to avoid the exception."

搭建分层结构

编写配置文件

driver = com.mysql.jdbc.Driver
jdbcUrl = jdbc:mysql://localhost:3306/testjdbc?useSSL=false
username = root
userpassword = 123456

编写工具类

package com.xzlf.commons;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class JdbcUtil {

	private static String driver;
	private static String jdbcUrl;
	private static String username;
	private static String userpassword;
	static {
		// 读取properties 文件
		ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
		driver = bundle.getString("driver");
		jdbcUrl = bundle.getString("jdbcUrl");
		username = bundle.getString("username");
		userpassword = bundle.getString("userpassword");
		
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取connection 对象
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(jdbcUrl, username, userpassword);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 释放资源
	 * @param rs
	 * @param stat
	 * @param conn
	 */
	public static void closeResource(ResultSet rs, Statement stat, Connection conn) {
		try {
			if(rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			if(stat != null) {
				stat.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			if(conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

编写pojo

package com.xzlf.pojo;

import java.sql.Date;
import java.sql.Timestamp;

public class User {
	private Integer id;
	private String username;
	private String pwd;
	private Date regTime;
	private Timestamp lastLoginTime;
	
	public User() {
		// TODO Auto-generated constructor stub
	}

	public User(Integer id, String username, String pwd, Date regTime, Timestamp lastLoginTime) {
		super();
		this.id = id;
		this.username = username;
		this.pwd = pwd;
		this.regTime = regTime;
		this.lastLoginTime = lastLoginTime;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	public Date getRegTime() {
		return regTime;
	}

	public void setRegTime(Date regTime) {
		this.regTime = regTime;
	}

	public Timestamp getLastLoginTime() {
		return lastLoginTime;
	}

	public void setLastLoginTime(Timestamp lastLoginTime) {
		this.lastLoginTime = lastLoginTime;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", pwd=" + pwd + ", regTime=" + regTime
				+ ", lastLoginTime=" + lastLoginTime + "]";
	}
	
	
}

编写通用的BaseDAO接口

package com.xzlf.dao;

import java.util.List;

public interface BaseDao {
	int executeUpdate(String sql, Object[] param);
	
	public <T> List<T> find(String sql, Object[] param, Class<T> clazz);
}

编写到具体的DAO接口

package com.xzlf.dao;

import java.util.List;

import com.xzlf.pojo.User;

public interface UserDao extends BaseDao {
	int insertUser(User user);
	int updteUser(User user);
	int deleteUser(int id);
	List<User> selectUserByLikeName(String username);
}

编写通用的BaseDAO接口实现

package com.xzlf.dao.impl;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;

import com.xzlf.commons.JdbcUtil;
import com.xzlf.dao.BaseDao;

public class BaseDaoImpl implements BaseDao{

	/**
	 * 封装通用的DML操作
	 */
	@Override
	public int executeUpdate(String sql, Object[] param) {
		Connection conn = null;
		PreparedStatement ps = null;
		int rows = 0;
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			// 获取参数信息
			ParameterMetaData parameterMetaData = ps.getParameterMetaData();
			// 获取参数个数
			int count = parameterMetaData.getParameterCount();
			// 绑定参数
			for (int i = 0; i < count; i++) {
				ps.setObject(i + 1, param[i]);
			}
			rows = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closeResource(null, ps, conn);
		}
		return rows;
	}

	/**
	 * 封装通用的查询操作
	 * 注意：通用的查询方法中要求模型对象的属性名必须要和数据库表中的列名相同。
	 */
	@Override
	public <T> List<T> find(String sql, Object[] param, Class<T> clazz) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<T> list = new ArrayList<T>();
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ParameterMetaData parameterMetaData = ps.getParameterMetaData();
			int count = parameterMetaData.getParameterCount();
			for (int i = 0; i < count; i++) {
				ps.setObject(i + 1, param[i]);
			}
			rs = ps.executeQuery();
			// 获取结果集信息
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			while(rs.next()) {
				// 通过反射完成ORM 处理
				T bean = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					// 获取列名（约定和对象属性名）
					String columnName = metaData.getColumnName(i + 1);
					// 获取列值
					Object value = rs.getObject(columnName);
					// 通过BeanUtil 工具类，把值写到对象中
					BeanUtils.setProperty(bean, columnName, value);
				}
				list.add(bean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closeResource(rs, ps, conn);
		}
		return list;
	}

}

编写到具体的DAO接口实现

package com.xzlf.dao.impl;

import java.util.List;

import com.xzlf.dao.UserDao;
import com.xzlf.pojo.User;

public class UserDaoImpl extends BaseDaoImpl implements UserDao{
	// 添加用户信息
	@Override
	public int insertUser(User user) {
		String sql = "insert into t_user values(default, ?, ?, ?, ?)";
		Object[] param = {user.getUsername(), user.getPwd(), user.getRegTime(), user.getLastLoginTime()};
		
		return this.executeUpdate(sql, param);
	}

	// 更新用户信息
	@Override
	public int updteUser(User user) {
		String sql = "update t_user set username=?, pwd=?, regTime=?, LastLoginTime=? where id=?";
		Object[] param = {user.getUsername(), user.getPwd(), 
				user.getRegTime(), user.getLastLoginTime(), user.getId()};
		return this.executeUpdate(sql, param);
	}

	// 删除用户信息
	@Override
	public int deleteUser(int id) {
		String sql = "delete from t_user where id=?";
		Object[] param = {id};
		return this.executeUpdate(sql, param);
	}

	// 查询用户信息
	@Override
	public List<User> selectUserByLikeName(String username) {
		String sql = "select * from t_user where username like ?";
		Object[] param = {"%" + username + "%"};
		return this.find(sql, param, User.class);
	}

}

编写业务层接口

package com.xzlf.service;

import java.util.List;

import com.xzlf.pojo.User;

public interface UserService {
	int addUser(User user);
	int modifyUser(User user);
	int dropUser(int id);
	List<User> findUser(String username);
}

编写业务层实现

package com.xzlf.service.impl;

import java.util.List;

import com.xzlf.dao.UserDao;
import com.xzlf.dao.impl.UserDaoImpl;
import com.xzlf.pojo.User;
import com.xzlf.service.UserService;

public class UserServiceImpl implements UserService{

	private UserDao userDao = new UserDaoImpl();
	@Override
	public int addUser(User user) {
		return this.userDao.insertUser(user);
	}

	@Override
	public int modifyUser(User user) {
		return this.userDao.updteUser(user);
	}

	@Override
	public int dropUser(int id) {
		return this.userDao.deleteUser(id);
	}

	@Override
	public List<User> findUser(String username) {
		return this.userDao.selectUserByLikeName(username);
	}

}

编写视图层

……
暂无
……

编写测试类

package com.xzlf.test;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.List;

import org.junit.Test;

import com.xzlf.pojo.User;
import com.xzlf.service.UserService;
import com.xzlf.service.impl.UserServiceImpl;

public class TestApp {
	private UserService userService = new UserServiceImpl();
	@Test
	public void testAddUser() {
		User user = new User();
		user.setUsername("盖伦");
		user.setPwd("123123");
		user.setRegTime(new Date(System.currentTimeMillis() - 3600*24*1000));
		user.setLastLoginTime(new Timestamp(System.currentTimeMillis()));
		this.userService.addUser(user);
	}
	
	@Test
	public void testModifyUser() {
		User user = new User();
		user.setId(20223);
		user.setUsername("赵信");
		user.setPwd("666666");
		user.setRegTime(new Date(System.currentTimeMillis() - 3600*24*2000));
		user.setLastLoginTime(new Timestamp(System.currentTimeMillis()));
		this.userService.modifyUser(user);
	}
	
	@Test
	public void testDropUser() {
		this.userService.dropUser(20223);
	}
	
	@Test
	public void testFindUser() {
		List<User> users = this.userService.findUser("麻子");
		for (User user : users) {
			System.out.println(user);
		}
		
	}
}

查询方法打印了截个图：