mysql时间封装到实体类_封装JdbcUtil工具类，实现自动封装为实体类

根据今天所学，自己写的JdbcUtil工具类。

其中update操作包含增删改，使用事务处理

查询操作分为两种：

1、需要传入sql语句(使用预编译，用？占位)、参数、需要包装的实体类的class路径；返回实体类的list(可以为单个)

2、传入sql、参数。适用于查询基本数据类型组成的list (可以为单个)

jdbcUtil.java代码：

import java.io.InputStream;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.sql.*;

import java.util.ArrayList;

import java.util.Properties;

/**

* Created by srg

*

* @date 2018/7/14

*/

public class JdbcUtil {

private static String url = null;

private static String username = null;

private static String password = null;

private static String driverClass = null;

private static Connection conn = null;

private static PreparedStatement pstm = null;

private static ResultSet rs = null;

static {

try {

Properties prop = new Properties();

InputStream in = JdbcUtil.class.getResourceAsStream("/database.properties");

prop.load(in);

url = prop.getProperty("url");

username = prop.getProperty("user");

password = prop.getProperty("password");

driverClass = prop.getProperty("driverClass");

Class.forName(driverClass);

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 获取数据库链接

* @return

*/

public static Connection getConnection(){

Connection conn = null;

try {

conn = DriverManager.getConnection(url, username, password);

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

/**

* ResultSet的关闭

*/

public static void close(Connection conn, PreparedStatement pstm, ResultSet rs){

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if(pstm != null){

try {

pstm.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if(rs != null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

/**

* 执行插入操作

* @param sql sql语句

* @param param 需要的参数

* @return

*/

public static boolean executeUpdate(String sql, ArrayList param){

boolean flag = false;

conn = JdbcUtil.getConnection();

try {

pstm = conn.prepareStatement(sql);

for (int i = 0;i < param.size();i++){

pstm.setObject(i + 1, param.get(i));

}

pstm.executeUpdate();

flag = true;

} catch (SQLException e) {

e.printStackTrace();

return flag;

} finally {

JdbcUtil.close(conn, pstm, rs);

}

return flag;

}

/**

* 执行更新批处理操作，使用事务处理，成功则提交事务，不成功则回滚

* @param sql

* @param param

* @return

*/

public static boolean executeUpdate(String[] sql, ArrayListparam){

boolean flag = false;

conn = JdbcUtil.getConnection();

try {

//设置为手动提交

conn.setAutoCommit(false);

for(int i = 0;i < sql.length;i++){

pstm = conn.prepareStatement(sql[i]);

for (int j = 0;j < param.size();j++){

pstm.setObject(j + 1, param.get(i).get(j));

}

pstm.addBatch();

}

pstm.executeBatch();

//如果执行成功了，就提交

conn.commit();

flag = true;

} catch (SQLException e) {

e.printStackTrace();

try {

//不成功，回滚

conn.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

return flag;

} finally {

JdbcUtil.close(conn, pstm, rs);

}

return flag;

}

/**

* 执行查询操作

* @param sql sql语句

* @param param 需要的参数

* @param classPath 根据传入的path,反射为实体类,封装到list中

* @return 将封装好的list返回

*/

public static ArrayList executeQuery(String sql, ArrayList param, String classPath){

ArrayList result = new ArrayList();

conn = JdbcUtil.getConnection();

try {

pstm = conn.prepareStatement(sql);

for (int i = 0;i < param.size();i++){

pstm.setObject(i + 1, param.get(i));

}

rs = pstm.executeQuery();

Class c = Class.forName(classPath);

while(rs.next()){

Object obj = c.newInstance();

Field[] fields = c.getDeclaredFields();

for (Field field : fields){

String ftype = field.getType().getSimpleName();

String fname = field.getName();

//如果不存在，继续

if(!isExists(rs, fname)){

continue;

}

Object value = null;

if("string".equalsIgnoreCase(ftype)){

value = rs.getString(fname);

} else if("int".equalsIgnoreCase(ftype) || "integer".equalsIgnoreCase(ftype)){

value = rs.getInt(fname);

} else if("float".equalsIgnoreCase(ftype)){

value = rs.getFloat(fname);

} else if("double".equalsIgnoreCase(ftype)){

value = rs.getDouble(fname);

} else if("boolean".equalsIgnoreCase(ftype)){

value = rs.getBoolean(fname);

} else if("date".equalsIgnoreCase(ftype)){

value = rs.getTimestamp(fname);

}

//使用setXXX方法

fname = fname.substring(0,1).toUpperCase() + fname.substring(1);

String mName = "set" + fname;

Method m = c.getDeclaredMethod(mName, field.getType());

m.invoke(obj, value);

}

//将封装好的数据依次加入result

result.add(obj);

}

} catch (Exception e) {

e.printStackTrace();

result = new ArrayList();

return result;

} finally {

JdbcUtil.close(conn, pstm, rs);

}

return result;

}

/**

* 查询单个数据

* @param sql sql语句

* @param param 需要的参数

* @return 返回值为基本类型或者String的list

*/

public static ArrayList executeQuery(String sql, ArrayList param){

ArrayList result = new ArrayList();

conn = JdbcUtil.getConnection();

try{

pstm = conn.prepareStatement(sql);

for (int i = 0;i < param.size();i++){

pstm.setObject(i + 1, param.get(i));

}

rs = pstm.executeQuery();

while (rs.next()){

Object object = rs.getObject(1);

result.add(object);

}

} catch (Exception e){

e.printStackTrace();

return new ArrayList();

} finally {

JdbcUtil.close(conn, pstm, rs);

}

return result;

}

public static boolean isExists(ResultSet rs,String column)

{

try {

rs.findColumn(column);

} catch (SQLException e) {

return false;

}

return true;

}

}

其中database.properties中保存数据库的信息

url=jdbc:mysql://localhost:3306/gs

user=root

password=

driverClass=com.mysql.jdbc.Driver

由于使用到了反射，所以bean(实体类)里面的set get函数必须标准命名。

例如:

user.java(仅作为例子)

public class UserBean {

private String username;

private String password;

public UserBean() {

}

public UserBean(String username, String password) {

this.username = username;

this.password = password;

}

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

}

使用实例：(我还没验证。。。瞎写一下)

public User login(String username, String password){

String sql = "select * from user where username=? and password=?";

ArrayListparam = new ArrayList<>();

param.add(username);

param.add(password);

String path = "com.gs.bean.UserBean";

ArrayList userList = JdbcUtil.executeQuery(sql,param,path);

User user = new User();

if(userList.size() > 0) {

user = (User) userList.get(0);

}

return user;

}