Complete implementation of JdbcUtils, A jdbc framework class encapsulated by Java for MySql (including addition, deletion, modification, query, and JavaBean reflection principles, with source code)

Recently I watched the old Luo video, followed by a framework class JdbcUtils. Java that uses java to operate MySql databases, to complete addition, deletion, modification, and query of databases. Query this part, including common query and UtilizationReflectionThe completed query mainly includes the following function interfaces:

1. public Connection getConnection () to obtain the database Connection

2. public boolean updateByPreparedStatement (String SQL, List <Object> params) throws SQLException to update the database, including adding, deleting, and modifying a record.

3. public Map <String, Object> findSimpleResult (String SQL, List <Object> params) throws SQLException queries a single record, which is passed in as a placeholder for filling in the List <Object> parameter, the returned Map <String, Object>. A Map corresponds to a complete record, a String corresponds to the attribute name, and an Object is the attribute value.

4. public List <Map <String, Object> findModeResult (String SQL, List <Object> params) throws SQLException queries multiple records and stores them in List.

The above four functions have included all MySQl operations, fully meeting the needs of use. In the video, Luo also extended two reflection functions for query.

5. public <T> T findSimpleRefResult (String SQL, List <Object> params,

6. public <T> List <T> findMoreRefResult (String SQL, List <Object> params,

The complete code is provided below:

JdbcUtils. java

Package com. jdbc. dbutils; import java. lang. reflect. field; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; import java. util. arrayList; import java. util. hashMap; import java. util. list; import java. util. map; import domain. userInfo; public class JdbcUtils {// database username private static Final String USERNAME = "root"; // Database PASSWORD private static final String PASSWORD = "yanzi"; // DRIVER information private static final String DRIVER = "com. mysql. jdbc. driver "; // database address private static final String URL =" jdbc: mysql: // localhost: 3306/mydb "; private Connection connection; private PreparedStatement pstmt; private ResultSet resultSet; public JdbcUtils () {// TODO Auto-generated constructor stubtry {Class. forName (DRIV ER); System. out. println ("database connection successful! ");} Catch (Exception e) {}}/*** get database Connection * @ return */public connection getConnection () {try {Connection = DriverManager. getConnection (URL, USERNAME, PASSWORD);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return connection ;} /*** add, delete, and modify * @ param SQL * @ param params * @ return * @ throws SQLException */public boolean updateByPreparedStatement (String SQL, List <Object> p Arams) throws SQLException {boolean flag = false; int result =-1; pstmt = connection. prepareStatement (SQL); int index = 1; if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {pstmt. setObject (index ++, params. get (I) ;}} result = pstmt.exe cuteUpdate (); flag = result> 0? True: false; return flag;}/*** query a single record * @ param SQL * @ param params * @ return * @ throws SQLException */public Map <String, object> findSimpleResult (String SQL, List <Object> params) throws SQLException {Map <String, Object> map = new HashMap <String, Object> (); int index = 1; pstmt = connection. prepareStatement (SQL); if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {pstmt. setObject (index ++, params. get (I) ;}} resultSet = pstmt.exe cuteQuery (); // return the query result ResultSetMetaData metaData = resultSet. getMetaData (); int col_len = metaData. getColumnCount (); while (resultSet. next () {for (int I = 0; I <col_len; I ++) {String cols_name = metaData. getColumnName (I + 1); Object cols_value = resultSet. getObject (cols_name); if (cols_value = nul L) {cols_value = "";} map. put (cols_name, cols_value) ;}} return map ;} /** query multiple records * @ param SQL * @ param params * @ return * @ throws SQLException */public List <Map <String, Object> findModeResult (String SQL, list <Object> params) throws SQLException {List <Map <String, Object> list = new ArrayList <Map <String, Object> (); int index = 1; pstmt = connection. prepareStatement (SQL); if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {pstmt. setObject (index ++, params. get (I) ;}} resultSet = pstmt.exe cuteQuery (); ResultSetMetaData metaData = resultSet. getMetaData (); int cols_len = metaData. getColumnCount (); while (resultSet. next () {Map <String, Object> map = new HashMap <String, Object> (); for (int I = 0; I <cols_len; I ++) {String cols_name = metaData. getColumnName (I + 1); Object cols_value = ResultSet. getObject (cols_name); if (cols_value = null) {cols_value = "";} map. put (cols_name, cols_value);} list. add (map);} return list ;} /** query a single record through the reflection mechanism * @ param SQL * @ param params * @ param cls * @ return * @ throws Exception */public <T> T findSimpleRefResult (String SQL, list <Object> params, Class <T> cls) throws Exception {T resultObject = null; int index = 1; pstmt = connection. prepareStatement (SQL); if (par Ams! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {pstmt. setObject (index ++, params. get (I) ;}} resultSet = pstmt.exe cuteQuery (); ResultSetMetaData metaData = resultSet. getMetaData (); int cols_len = metaData. getColumnCount (); while (resultSet. next () {// use the reflection mechanism to create an instance resultObject = cls. newInstance (); for (int I = 0; I <cols_len; I ++) {String cols_name = metaData. getColumnName (I + 1); Object cols_value = resultS Et. getObject (cols_name); if (cols_value = null) {cols_value = "";} Field field = cls. getDeclaredField (cols_name); field. setAccessible (true); // open the access permission field for javabean. set (resultObject, cols_value) ;}return resultObject ;} /** query multiple records through the reflection mechanism * @ param SQL * @ param params * @ param cls * @ return * @ throws Exception */public <T> List <T> findMoreRefResult (String SQL, list <Object> params, Class <T> cls) throws ti On {List <T> list = new ArrayList <T> (); int index = 1; pstmt = connection. prepareStatement (SQL); if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {pstmt. setObject (index ++, params. get (I) ;}} resultSet = pstmt.exe cuteQuery (); ResultSetMetaData metaData = resultSet. getMetaData (); int cols_len = metaData. getColumnCount (); while (resultSet. next () {// create an instance through the reflection mechanism T resultObject = cls. newInstance (); for (int I = 0; I <cols_len; I ++) {String cols_name = metaData. getColumnName (I + 1); Object cols_value = resul TSet. getObject (cols_name); if (cols_value = null) {cols_value = "";} Field field = cls. getDeclaredField (cols_name); field. setAccessible (true); // open the access permission field for javabean. set (resultObject, cols_value);} list. add (resultObject);} return list;}/*** release database connection */public void releaseConn () {if (resultSet! = Null) {try {resultSet. close ();} catch (SQLException e) {e. printStackTrace () ;}}/ *** @ param args */public static void main (String [] args) throws SQLException {// TODO Auto-generated method stubJdbcUtils jdbcUtils = new JdbcUtils (); jdbcUtils. getConnection (); ******************* ** // * String SQL = "insert into userinfo (username, pswd) values (?, ?), (?, ?), (?, ?) "; List <Object> params = new ArrayList <Object> (); params. add ("James"); params. add ("123 xiaoming"); params. add ("Zhang San"); params. add ("zhangsan"); params. add ("Li Si"); params. add ("lisi000"); try {boolean flag = jdbcUtils. updateByPreparedStatement (SQL, params); System. out. println (flag);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} ***************** * *** // delete a note named John /* String SQL = "delete from userinfo where username =? "; List <Object> params = new ArrayList <Object> (); params. add ("James"); boolean flag = jdbcUtils. updateByPreparedStatement (SQL, params ); * // ******************* change ***************** * *** // change the password named Li Si to/* String SQL = "update userinfo set pswd =? Where username =? "; List <Object> params = new ArrayList <Object> (); params. add ("lisi88888"); params. add ("Li Si"); boolean flag = jdbcUtils. updateByPreparedStatement (SQL, params); System. out. println (flag ); ***************** * *** // query multiple records without reflection/* String sql2 = "select * from userinfo "; list <Map <String, Object> list = jdbcUtils. findModeResult (sql2, null); System. out. println (list); * // query a single record Using Reflection String SQL = "Select * from userinfo where username =? "; List <Object> params = new ArrayList <Object> (); params. add ("Li Si"); UserInfo userInfo; try {userInfo = jdbcUtils. findSimpleRefResult (SQL, params, UserInfo. class); System. out. print (userInfo);} catch (Exception e) {// TODO Auto-generated catch blocke. printStackTrace ();}}}

+ ---------- + ------------- + ------ + ----- + --------- + ---------------- +

Nvicat is created in advance:

Because two interfaces use reflection, the corresponding JavaBean UserInfo. java code is as follows:

package domain;import java.io.Serializable;public class UserInfo implements Serializable{/** *  */private static final long serialVersionUID = 1L;private int id;private String username;private String pswd;public UserInfo() {// TODO Auto-generated constructor stub}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPswd() {return pswd;}public void setPswd(String pswd) {this.pswd = pswd;}@Overridepublic String toString() {return "UserInfo [id=" + id + ", username=" + username + ", pswd="+ pswd + "]";}}

Note:

1. After installing mysql-connector-java-gpl-5.1.26.exe, you will find that the jar package cannot be found. In fact, the jar file is in the C: \ Program Files \ MySQL Connector J directory and has two jar packages:

Which one is OK. Create a new folder libs in the Java project, copy the mysql-connector-java-5.1.26-bin.jar, right-click add to build path and OK.

2.Aside from this framework class JdbcUtils. java, the general steps for database operations are as follows:

(1) connect to the database and load the DRIVER: Class. forName (DRIVER); Driver = "com. mysql. jdbc. DRIVER"; this is reflection !!

(2) Using the user name and password and the database name to connect, this step is the real connection:

Connection = DriverManager. getConnection (URL, USERNAME, PASSWORD );

Where: String URL = "jdbc: mysql: // localhost: 3306/mydb ";

(3) Compile an SQL statement. Which of the following parameters is used? And then write the parameters to the List.

Run pstmt = connection. prepareStatement (SQL). Then, extract the parameter from the list and fill it in pstmt.

(4) For addition, deletion, and modification: result = pstmt.exe cuteUpdate (); the result indicates the number of rows in the affected database, that is, several records. If the query is executed: resultSet = pstmt.exe cuteQuery (); The returned type is ResultSet. After that, the resultSet is converted to Map or List <Map> and passed to the queryer.

3. For the query operation, use getMetaData to obtain the structure information of the table after the resultSet is obtained, such as how many columns are obtained by getColumnCount. String cols_name = metaData. getColumnName (I + 1); get the attribute name of each column, such as id, username, or pswd. and then from the Object cols_value = resultSet. getObject (cols_name); get it and put it in Map or List <Map>.

4. Perform the reflection operations in the query as follows:

(1) T resultObject = cls. newInstance (); Use the newInstance () method of the class file to create an instance.

(2) After obtaining the number of columns through getColumnCount (), enter the loop,

String cols_name = metaData. getColumnName (I + 1 );

Read the attribute names and values of each column. Reflection by attribute name cols_name: Field field = cls. getDeclaredField (cols_name. setAccessible (true); // enable the access permission of javabean. In the set method, assign the cols_value obtained from the database to the set Method of the JavaBean class, that is, the defined UserInfo. Field. set (resultObject, cols_value );

5.In general, the following steps are required to use Java reflection:

(1) load the Class Object. There are two methods: Class cls1 = UserInfo. class or

Class cls2 = Class. forName ("domain. UserInfo") the latter uses the package name + Class name method.

(2) what will happen after reflecting out the Class? A ClassConstructor,Member variables,Member Functions. So you can do these three things after getting the Class.

A,ConstructorTo obtain the Constructor, there are four methods:

Constructor getConstructor (Class [] params)

Constructor [] getConstructors ()

Constructor getDeclaredConstructor (Class [] params)

Constructor [] getDeclaredConstructors ()

If no parameter is set, all constructors are obtained and a set is obtained. If a specific parameter is input, the system looks for this specific constructor. If Declared is not included, the public is obtained, and if Declared is included, the private constructor can be obtained. After obtaining the constructor, you can use reflection to create an instance:

Constructor con1 [] = cls1.getDeclaredConstructors ();

B. AboutMember variablesThere are also four methods:

Public Field getDeclaredField (String name) gets a member of any specified name

The JdbcUtils class encapsulated in this article is to use this method to operate private member variables in the class. Remember to enable setAccessible. As follows:

Field field = cls. getDeclaredField (cols_name );

C. AboutMember FunctionsThere are also four methods:

Public Method [] getMethods () gets a set of all common methods

Parameter 1: method name parameter 2: Set of parameter types

The following is a completed reflection example written using the UserInfo class in this article. The setUsername (String username) method is obtained and then reflected. Get the getUsername () method, then reflect it, and print the result:

Class clcs = UserInfo. class;

Method f = clcs. getDeclaredMethod ("setUsername", String. class); the type of input parameters in the original function. class. for example, if the original setXXX parameter needs to be input, the String is written during reflection. class.

6. JavaBean is a type of reflection. reflection has no requirements on constructor and so on. JavaBean requires that this class must inherit Serializable to be Serializable. In addition, the constructor must be public.In addition, JavaBean can directly call set and get after obtaining a field, without the need to obtain the method and then execute it.

Finally, reflection is performed when the program is running, not during compilation !!!

Reference: link 1 link 2 link 3

Download link: http://download.csdn.net/detail/yanzi1225627/7398533