MySQL数据库查询——JDBC与反射

MySQL数据库查询——JDBC与反射

MySQL数据库查询方法：

连接工具类：

package com.jdbc.mysql;

public class SQLInfo {
	public static final String MYSAL_DRIVER="com.mysql.cj.jdbc.Driver";
	//MYSAL_URL中的tables是你自己的mysql数据库名称，需要根据自己的数据库名进行更换
	public static final String MYSAL_URL="jdbc:mysql://localhost:3306/tables?useSSL=false&serverTimezone=UTC";
	public static final String MYSAL_USER="root";
	public static final String MYSAL_PWD="tiger";
	
}

数据库连接获取与关闭方法类：

package com.jdbc.mysql;

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

public class MysqlConnection {
	//获取mysql连接
	public static Connection getConnection() {
		Connection conn=null;
		try {
			Class.forName(SQLInfo.MYSAL_DRIVER);
			conn=DriverManager.getConnection(SQLInfo.MYSAL_URL, SQLInfo.MYSAL_USER, SQLInfo.MYSAL_PWD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	//关闭mysql连接资源
	public static void close(Connection connection,Statement statement,ResultSet resultSet) {
		try {
			if(resultSet!=null) {
				resultSet.close();
			}
			if(statement!=null) {
				statement.close();
			}
			if(connection!=null) {
				connection.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			resultSet=null;
			statement=null;
			connection=null;
		}
	}
}

数据库查询方法类：

需要注意，查询方法内没有直接使用数据库连接方法类提供的数据库连接方法，与资源关闭方法，可根据需要自行修改

package com.jdbc.mysql;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Cool.R
 * @since 2019-8-05
 * @version 2.0
 * @apiNote 数据库查询方法，需自行构建对应接收类
 */
public class MysqlQuary {
	// 设置器名构建方法
	public static String createSettor(String columName) {
		String setor = null;
		setor = "set" + columName.substring(0, 1).toUpperCase() + columName.substring(1);
		return setor;
	}

	// 查询方法——需要提供（数据库连接、where条件语句、where参数数组、相应用于存储类的Class对象）
	public static List<?> sqlQuary(Connection connection, String whereSql, Object[] params, Class<?> clazz) {
		List<Object> list = new ArrayList<Object>();
		// 拼接sql查询语句。
		String sql = "select * from " + clazz.getSimpleName() + " " + whereSql;
		PreparedStatement ps = null;
		ResultSetMetaData rsmd = null;
		ResultSet rs = null;
		try {
			// 打开sql预处理
			ps = connection.prepareStatement(sql);
			// 开始参数绑定
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			// 获取表的列属性参数
			rsmd = ps.getMetaData();
			// 获取列数量
			int columCount = rsmd.getColumnCount();
			// 开始查询
			rs = ps.executeQuery();
			// 声明列字段名
			String columName = null;
			String settor = null;
			while (rs.next()) {
				// 构建一个实例对象
				Object instance = clazz.newInstance();
				// 遍历表每行中的列字段值
				for (int k = 1; k <= columCount; k++) {
					// 获取列的字段名
					columName = rsmd.getColumnName(k).toLowerCase();
					// 根据列字段名获取对象的相应属性
					Field field = clazz.getDeclaredField(columName);
					// 由列字段名构建对象的设置器方法名
					settor = createSettor(columName);
					// 由设置器方法名，利用反射获取类设置器方法
					Method setMethod = clazz.getDeclaredMethod(settor, field.getType());
					// 获取表行内列字段值
					Object objVal = rs.getObject(k);
					// 判断objVal的数据类型
					if (objVal instanceof Number) {
						// 判断用于存储数据的对象的属性数据类型
						if (field.getType().getSimpleName().equals("int")
								|| field.getType().equals("java.lang.Integer")) {
							setMethod.invoke(instance, ((Number) objVal).intValue());
						} else if (field.getType().getSimpleName().equals("float")
								|| field.getType().equals("java.lang.Float")) {
							setMethod.invoke(instance, ((Number) objVal).floatValue());
						} else if (field.getType().getSimpleName().equals("double")
								|| field.getType().equals("java.lang.Double")) {
							setMethod.invoke(instance, ((Number) objVal).doubleValue());
						} else {
							setMethod.invoke(instance, ((Number) objVal).intValue());
						}
					} else if (objVal == null) {
//						 setMethod.invoke(instance, null);
					} else if (objVal instanceof Date) {
						// 获取时间戳
						long dateStamp = ((Date) objVal).getTime();
						setMethod.invoke(instance, new Date(dateStamp));
					} else {
						//其他情况可直接放入设置器方法内
						setMethod.invoke(instance, objVal);
					}
					// 对象属性值设置完毕
				}
				// 存储对象
				list.add(instance);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			// 关闭数据库资源
			try {
				rs.close();
				ps.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
}

数据库查询测试类：

package com.jdbc.mysql;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

public class Test01 {
	public static void main(String[] args) {
		//where条件语句
//		String whereSQL="where deptno = ? ";
		String whereSQL="";
		//接收容器
		List<?> list=new ArrayList<Object>();
		//参数数组
		
//		Object[] params= {10};
		Object[] params= {};
		
		//数据库链接
		Connection conn=MysqlConnection.getConnection();
		list=MysqlQuary.sqlQuary(conn, whereSQL,params, EMP.class);
		//遍历结果
		System.out.println("表：emp");
		for(Object obj:list) {
			System.out.println(obj.toString());
		}
	}
}

与数据库emp表对应的类：

package com.jdbc.mysql;
import java.util.Date;
/**
 * @since 2019-8
 * @author Cool.R
 * @version 1.0
 */
public class EMP {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private double sal;
	private double comm;
	private int deptno;

	// 设置器与访问器
	public int getEmpno() {
		return empno;
	}

	public void setEmpno(int empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public int getMgr() {
		return mgr;
	}

	public void setMgr(int mgr) {
		this.mgr = mgr;
	}

	public Date getHiredate() {
		return hiredate;
	}

	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}

	public double getSal() {
		return sal;
	}

	public void setSal(double sal) {
		this.sal = sal;
	}

	public double getComm() {
		return comm;
	}

	public void setComm(double comm) {
		this.comm = comm;
	}

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	// 有参构造
	public EMP(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}

	// 无参构造
	public EMP() {
		super();
	}

	// 重写toString
	@Override
	public String toString() {
		return "EMP [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hireDate=" + hiredate
				+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}

}

数据库中的emp表：

mysql语句：

CREATE TABLE emp(
empno INT(6) PRIMARY KEY,
ename varchar(20) NOT NULL,
job varchar(20),
mgr INT(6),
hiredate DATE,
sal DOUBLE(6,2),
comm DOUBLE(6,2),
deptno INT(2)
) ENGINE=INNODB AUTO_INCREMENT =1 DEFAULT CHARSET = UTF8`tables`

SELECT * FROM emp WHERE deptno=10;

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7788, 'alex', 'sales', 6000, NOW(), 1000, null, 10);
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(8877, 'ajax', 'Boss', 5656, NOW(), 2000, null, 10);
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(5566, 'coco', 'fireMan', 3999, NOW(), null, NULL, 30);
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(6655, 'appa', 'solider', 5000, NOW(), 1000, NULL, 20);
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(9966, 'fireMan', 'Busness', 6666, NOW(), 1000, null, 10);
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(6969, 'Hello', 'hola', 3333, NOW(), 1000, NULL, 30);
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(6699, 'Hola', 'sales', 2333, NOW(), 1000, NULL, 20);
COMMIT;

字段属性

表内容：

运行结果：