java 个人jdbc_[Java教程]JAVA（JDBC）通用查询_星空网

JAVA(JDBC)通用查询

2017-09-07

0

第一次写编程类博客，不足之处望指正。

最近学mysql数据库时，同时看了看java和python代码。一看是看的java代码不能通用，对比了java和python代码，想到虽然不能像python一样简单，但能不能做成通用的呢？由于学习java时间也不长，就在网上看了看别人写的代码，先看到的是csdn上的《JDBC(六) 编写通用的更新和查询方法》。但是这个方法只能返回一条，就想能不能一次返回多条或整表，所以又看了看别人的。发现大多类似(或一样)，有的用了格式化，单独建立了个格式化类，增加大量迭代，类型还受限。所以自己用个半天多的时间，在前人的基础上搞了一个。个别类笔者也不熟，觉得东西还凑合，拿出了分享一下，欢迎指出问题。关键处理了一下hashmap的key不能重复的问题，别的也没什么，修改前的附在最后吧。import java.lang.reflect.Field;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.Iterator;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import java.util.Map.Entry;import org.apache.logging.log4j.LogManager;import org.apache.logging.log4j.Logger;import com.sql.entity.Student;public class SqlSelect1 { private static Logger logger=LogManager.getLogger(SqlSelect1.class.getName()); public static List query(Class clazz, String sql, Object... args) { T entity = null;

//笔者虚拟机默认装的mariadb,建立连接与mysql差不多，连接的封装就不发上来了。 MariaDBConn maria=null; PreparedStatement pstmt = null; ResultSet resultSet = null; List list = new ArrayList(); int colLen=0; try { maria=new MariaDBConn(); pstmt = maria.getConn().prepareStatement(sql); int argsLen=args.length; for (int i = 0; i values = new LinkedHashMap (); while (resultSet.next()) { //使用随机数使key不重复 double plus=Math.random(); colLen=resultSetMetaData.getColumnCount(); for (int i = 0; i 0) { Iterator> it = values.entrySet( ).iterator(); while(it.hasNext()){ int i=0; entity = clazz.newInstance(); while(i entry=it.next(); //剥离随机数，恢复正常key String fieldName = entry.getKey().toString().split("\\d")[0]; Object value = null; if (entry.getValue() instanceof Long) { value = Integer.parseInt(entry.getValue().toString()); } else { value = entry.getValue(); } Field field = clazz.getDeclaredField(fieldName); field.setAccessible(true); field.set(entity, value); i++; } list.add(entity); } } } catch (Exception e) { logger.error(e); }finally{ try { if(pstmt!=null){ pstmt.close(); } } catch (SQLException e) { logger.error(e); } try { if(resultSet!=null){ resultSet.close(); } } catch (SQLException e) { logger.error(e); } maria.closeConn(); } return list; } //测试： public static void main(String[] args) { List list=SqlSelect1.query(Student.class, "select id,name,birthday,gender from students"); int size=list.size(); for (int i = 0; i < size; i++) { System.out.println(list.get(i)); } }}

测试类：import java.sql.Timestamp;public class Student { private int id; private String name; private Timestamp birthday; private boolean gender; public Student() { super(); } public Student(String name, Timestamp birthday, boolean gender, boolean isDelete) { super(); this.name = name; this.birthday = birthday; this.gender = gender; this.isDelete = isDelete; } public Student(int id, String name, Timestamp birthday, boolean gender, boolean isDelete) { super(); this.id = id; this.name = name; this.birthday = birthday; this.gender = gender; this.isDelete = isDelete; } public int getId() { return id; } public String getName() { return name; } public Timestamp getBirthday() { return birthday; } public boolean isGender() { return gender; } public boolean isDelete() { return isDelete; } public void setId(int id) { this.id = id; } public void setName(String name) { this.name = name; } public void setBirthday(Timestamp birthday) { this.birthday = birthday; } public void setGender(boolean gender) { this.gender = gender; } public void setDelete(boolean isDelete) { this.isDelete = isDelete; } boolean isDelete; public String toString() { if(this.gender==false){ return "[Id=" + id + ", name=" + name + ", birthday=" + birthday + ", gender=女" + "]"; }else{ return "[Id=" + id + ", name=" + name + ", birthday=" + birthday + ", gender=男" + "]"; } }}

查询结果：[Id=1, name=alice, birthday=1972-01-02 00:00:00.0, gender=女][Id=2, name=jake, birthday=1988-04-01 00:00:00.0, gender=男][Id=3, name=zero, birthday=1987-02-01 00:00:00.0, gender=男][Id=4, name=luffy, birthday=1993-01-09 00:00:00.0, gender=男][Id=5, name=mike, birthday=1950-01-03 00:00:00.0, gender=男][Id=6, name=James, birthday=1989-01-09 00:00:00.0, gender=男][Id=7, name=Ace, birthday=1991-02-10 00:00:00.0, gender=男]

修改前代码：public T query(Class clazz, String sql, Object... args) { T entity = null; Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { // 获取resultSet connection = JDBCUtils.getConnection(); preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i + 1, args[i]); } resultSet = preparedStatement.executeQuery(); // 获取resultSetMetaData ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); // 创建一个MAP 键 :SQL查询得到的列的别名，别名和javaBean的属性名对应 值:对应列的值 Map values = new HashMap(); // 处理结果集，利用resultSetMetaData填充Map while (resultSet.next()) { for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) { String columnLable = resultSetMetaData .getColumnLabel(i + 1); Object columnValue = resultSet.getObject(i + 1); values.put(columnLable, columnValue); } } // 如果map不为空，利用反射创建对象 if (values.size() > 0) { // 反射创建对象 entity = clazz.newInstance(); // 遍历Map ,利用反射为属性赋值 for (Map.Entry entry : values.entrySet()) { String fieldName = entry.getKey(); Object value = null; // 因为java中和mysql中数据类型对应不一致 // mysql中int对应JAVA中的long，所以当查询出int型数据的时候需要转换 if (entry.getValue() instanceof Long) { value = Integer.parseInt(entry.getValue().toString()); } else { value = entry.getValue(); } // ReflectionUtils.setFieldValue(entity, fieldName, value); Field field = clazz.getDeclaredField(fieldName); field.setAccessible(true); field.set(entity, value); } } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } return entity; }

0

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：admin@shaoqun.com。