MySQL数据库查询——JDBC与反射
- MySQL数据库查询——JDBC与反射
- 连接工具类:
- 数据库连接获取与关闭方法类:
- 数据库查询方法类:
- 数据库查询测试类:
- 与数据库emp表对应的类:
- 数据库中的emp表:
- 运行结果:
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;
字段属性
表内容:
运行结果: