JAVA (JDBC) Universal Query and java jdbc Universal Query

JAVA (JDBC) Universal Query and java jdbc Universal Query

For the first time I wrote a programming blog, I hope I can correct the shortcomings.

When I recently studied mysql databases, I also looked at the java and python code. First, we can see that the java code cannot be universal. After comparing the java and python code, I think that although it cannot be as simple as python, can it be universal? Since it was not long to learn java, I checked the code written by others on the Internet. I first saw "JDBC (6) Compiling common update and query methods" on csdn. However, this method can only return one record, so you cannot return multiple or whole tables at a time. It is found that most of them are similar (or the same). Some adopt formatting and create a formatting class separately. A large number of iterations are added, and the type is still limited. Therefore, I spent more than half a day working on it on the basis of my predecessors. I am not familiar with some of them. I think things can be done together and I will share it with you. Please point out the problem. The key is to solve the problem that the key of hashmap cannot be repeated. Nothing else. The previous modification is attached to the end.

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 <T> List <T> query (Class <T> clazz, String SQL, Object... args) {T entity = null;
// Mariadb installed by the author's Virtual Machine by default. The established connection is similar to mysql, and the connection encapsulation will not be sent. MariaDBConn maria = null; PreparedStatement pstmt = null; ResultSet resultSet = null; List <T> list = new ArrayList <T> (); int colLen = 0; try {maria = new MariaDBConn (); pstmt = maria. getConn (). prepareStatement (SQL); int argsLen = args. length; for (int I = 0; I <argsLen; I ++) {pstmt. setObject (I + 1, args [I]);} resultSet = pstmt.exe cuteQuery (); ResultSetMetaData resultSetMetaData = resultSet. getMetaData (); // Ensure the sorted Map of the set <String, Object> values = new LinkedHashMap <String, Object> (); while (resultSet. next () {// use a random number to make the key unique double plus = Math. random (); colLen = resultSetMetaData. getColumnCount (); for (int I = 0; I <colLen; I ++) {String columnLable = resultSetMetaData. getColumnLabel (I + 1); Object columnValue = resultSet. getObject (I + 1); values. put (columnLable + plus, columnValue) ;}} if (values. size ()> 0) {Iterator <Entry <String, Object> it = values. entrySet (). iterator (); while (it. hasNext () {int I = 0; entity = clazz. newInstance (); while (I <colLen) {Entry <String, Object> entry = it. next (); // strip the random number and restore to normal key String fieldName = entry. getKey (). toString (). split ("\ d") [0]; Object value = null; if (entry. getValue () instanceof Long) {value = Integer. parseInt (entry. getValue (). toString ();} else {val Ue = 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 ;}// test: public static void main (String [] args) {List <Student> 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 ));}}}

Test class:

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 = female "+"] ";} else {return" [Id = "+ id +", name = "+ name +", birthday = "+ birthday + ", gender = male "+"] ";}}

Query results:

[Id = 1, name = alice, birthday = 00:00:00. 0, gender = female] [Id = 2, name = jake, birthday = 00:00:00. 0, gender = male] [Id = 3, name = zero, birthday = 00:00:00. 0, gender = male] [Id = 4, name = luffy, birthday = 00:00:00. 0, gender = male] [Id = 5, name = mike, birthday = 00:00:00. 0, gender = male] [Id = 6, name = James, birthday = 00:00:00. 0, gender = male] [Id = 7, name = Ace, birthday = 00:00:00. 0, gender = male]

 

Code before modification:

Public <T> T query (Class <T> clazz, String SQL, Object... args) {T entity = null; Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try {// get resultSet connection = JDBCUtils. getConnection (); preparedStatement = connection. prepareStatement (SQL); for (int I = 0; I <args. length; I ++) {preparedStatement. setObject (I + 1, args [I]);} resultSet = preparedStatement.exe cuteQuery (); // obtain resultSetMetaData ResultSetMetaData resultSetMetaData = resultSet. getMetaData (); // create a MAP key: the alias of the column obtained by the SQL query. the alias corresponds to the attribute name of the javaBean: the value of the corresponding column Map <String, object> values = new HashMap <String, Object> (); // process the result set and use resultSetMetaData to fill the 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) ;}// if map is not empty, use reflection to create the object if (values. size ()> 0) {// reflection creates object entity = clazz. newInstance (); // traverses the Map and assigns a value to the attribute Using Reflection (Map. entry <String, Object> entry: values. entrySet () {String fieldName = entry. getKey (); Object value = null; // because the data types in java and mysql are inconsistent // int in mysql corresponds to long in JAVA, therefore, 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 ;}