Java advanced (25) connecting Java to mysql database (underlying implementation)

Java advanced (25) connecting Java to mysql database (underlying implementation)
Java advanced (25) connecting Java to mysql database (underlying implementation) Preface

For a long time, no system has been used for java projects. Now we need to complete an experiment using java, which involves connecting to the database using java. You can write it on your own, but you cannot search it in your memory. This section briefly summarizes the previous methods. You can also understand the specific steps for connecting to the database at the underlying layer.

Implementation

First you need to import the relevant jar package, I use: mysql-connector-java-5.1.7-bin.jar.

Next let's take a look at the database connection method class I used:

MysqlUtil. java

Package cn.edu. ujn. util; 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 cn.edu. ujn. base. const; import cn.edu. ujn. dao. userDao; import cn. Edu. ujn. model. user; public class MysqlUtil {// define the database USERNAME private final static String USERNAME = Const. USERNAME; // define the Database PASSWORD private final static String PASSWORD = Const. PASSWORD; // define the DRIVER information of the database private final String DRIVER = Const. DRIVER; // defines the address for accessing the database private final static String URL = Const. URL; // defines the Connection of the database private static connection Connection; // defines the execution object of the SQL statement private static PreparedStatement pstmt; // only after obtaining S After tatement, You can execute the SQL statement // define the private static ResultSet resultset; public MysqlUtil () {try {Class. forName (DRIVER); // explicitly load the jdbc driver System. out. println ("driver registration successful! ");} Catch (Exception e) {System. out. println (" failed to register the driver! ") ;}}/*** Defines the link to obtain the database and tries to establish a connection to the given Database URL ** @ return Connection */public static connection GetConnection () {try {connection = DriverManager. getConnection (URL, USERNAME, PASSWORD); System. out. println ("database connection successful! ");} Catch (Exception e) {System. out. println (" database connection failed! ");} Return connection ;} /*** complete adding, deleting, and updating database tables ** @ param SQL * @ param params * @ return flag * @ throws SQLException */public static boolean updateByPreparedStatement (String SQL, listParams) // The second parameter is the placeholder for transmission throws SQLException {// load the database driver new MysqlUtil (); // connect to the database MysqlUtil. getConnection (); boolean flag = false; // indicates the number of rows that affect the database when the user adds, deletes, or modifies int result =-1; try {pstmt = connection. prepareStatement (SQL);} catch (Exception e) {System. out. println ("Error in updateByPreparedStatement! ") ;}/// Indicates the first position of the placeholder, int index = 1; // determines whether the filled placeholder has a value. determines the standard way of the set if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {// System. out. println (I + "" + params. get (I); // use the given object to set the value of the specified parameter. The second parameter must be Object-type pstmt. setObject (index ++, params. get (I) ;}} result = pstmt.exe cuteUpdate (); // used to execute the DML statement-return an integer, representing the number of records affected by the SQL statement flag = result> 0? True: false; System. out. println ("the number of records affected by SQL statement execution is:" + result); // close the database MysqlUtil. releaseConn (); return flag;}/*** query returns a single record ** @ param SQL * @ param params * @ return map * @ throws SQLException */public static Map
  
   
FindSimpleResult (String SQL, List
   Params) throws SQLException {// load the database driver new MysqlUtil (); // connect to the database MysqlUtil. GetConnection (); // diamond syntax available for Map in Java 7 and later
    
     
Map = new HashMap
     
      
(); // Indicates the first position of the placeholder int index = 1; // This sentence is very important and requires pre-compiling pstmt = connection. prepareStatement (SQL); // judge whether the filled placeholder has a value; Determine the standard way of the set if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {// The first parameter in your SQL statement, and the second parameter is the pstmt value to be set. setObject (index ++, params. get (I) ;}/// returns the query result resultset = pstmt.exe cuteQuery (); // gets the column numbers, types, and attributes of the ResultSet object. Java. SQL. ResultSetMetaData metdata = resultset. getMetaData (); // get the number of columns int col_lenth = metdata. getColumnCount (); boolean flag = resultset. next (); if (! Flag) {System. out. println ("Found nothing! ");} While (resultset. next () {for (int I = 0; I <col_lenth; I ++) {// get the name of the specified column String cols_name = metdata. getColumnName (I + 1); // obtain the attribute value of the specified column through the column name. Object cols_value = resultset. getObject (cols_name); if (cols_value = null) {// The Field Value in the data table cannot be blank. cols_value = "";} // associate the specified value with the specified key in the ing (optional ). Map. put (cols_name, cols_value) ;}// close MysqlUtil. releaseConn (); return map;}/*** access the database through the reflection mechanism, query the content of the first few pages ** @ param SQL * @ param params * @ param cls * @ return * @ throws Exception */public static
      
        List
       
         QueryEvamall (String SQL, List
        Params, Class
         
           Cls) throws Exception {// load the database driver new MysqlUtil (); // connect to the database MysqlUtil. GetConnection (); // construct an empty list with an initial capacity of 10. List
          
            List = new ArrayList
           
             (); // Indicates the first position of the placeholder int index = 1; pstmt = connection. prepareStatement (SQL); // judge whether the filled placeholder has a value; Determine the standard way of the set if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {// use the given object to set the value of the specified parameter. The second parameter must be Object-type pstmt. setObject (index ++, params. get (I) ;}}// return the query result resultset = pstmt.exe cuteQuery (); // obtain the column information in java. SQL. resultSetMetaData metdata = resultset. getMetaData (); // get the number of columns int col_lenth = metdata. getColumnCount (); while (resultset. next () {// create an instance through the reflection mechanism T resultObject = cls. newInstance (); for (int I = 0; I <col_lenth; I ++) {String cols_name = metdata. getColumnName (I + 1); Object cols_value = resultset. getObject (cols_name); if (cols_value = null) {cols_value = "" ;}// obtain the reflected Field field = cls through the column name. getDeclaredField (cols_name); // open the private access permission field of javabean. setAccessible (true); field. set (resultObject, cols_value);} list. add (resultObject);} // close MysqlUtil. releaseConn (); return list;}/*** query returns multiple query records ** @ param SQL * @ param params * @ return * @ throws SQLException */public static List
            
              > FindMoreResult (String SQL, List
             Params) throws SQLException {// load the database driver new MysqlUtil (); // connect to the database MysqlUtil. GetConnection (); System. out. println ("JJ"); List
              
                > List = new ArrayList
               
                 > (); // Indicates the first position of the placeholder, int index = 1; pstmt = connection. prepareStatement (SQL); // judge whether the filled placeholder has a value; Determine the standard way of the set if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {pstmt. setObject (index ++, params. get (I); // use the given object to set the value of the specified parameter. The second parameter must be of the // Object type} try {resultset = pstmt.exe cuteQuery (); // return the query result} catch (Exception e) {System. out. println ("Error1! "); // Used for debugging} ResultSetMetaData metdata = resultset. getMetaData (); // get the column information int col_lenth = metdata. getColumnCount (); // gets the number of columns: System. out. println ("data table columns:" + col_lenth); // debug with while (resultset. next () {Map
                
                  Map = new HashMap
                 
                   (); For (int I = 0; I <col_lenth; I ++) {String cols_name = metdata. getColumnName (I + 1); // obtain the column name. The column starts from 1. Object cols_value = resultset. getObject (cols_name); if (cols_value = null) {cols_value = "";} map. put (cols_name, cols_value);} list. add (map);} // close MysqlUtil. releaseConn (); return list ;} /*** the jdbc encapsulation can use the reflection mechanism to encapsulate the generic method ** @ param SQL * @ param params * @ param cls * @ return * @ throws Exception */public static
                  
                    T findSimpleRefResult (String SQL, List
                   Params, Class
                    
                      Cls) throws Exception {// load the database driver new MysqlUtil (); // connect to the database MysqlUtil. getConnection (); T resultObject = null; // indicates the first position of the placeholder int index = 1; pstmt = connection. prepareStatement (SQL); // judge whether the filled placeholder has a value; Determine the standard way of the set if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {// The first parameter in your SQL statement, and the second parameter is the pstmt value to be set. setObject (index ++, params. get (I) ;}}// return the query result resultset = pstmt.exe cuteQuery (); // obtain the column information in java. SQL. resultSetMetaData metdata = resultset. getMetaData (); // get the number of columns int col_lenth = metdata. getColumnCount (); while (resultset. next () {// use the reflection mechanism to create an instance resultObject = cls. newInstance (); for (int I = 0; I <col_lenth; I ++) {String cols_name = metdata. getColumnName (I + 1); Object cols_value = resultset. getObject (cols_name); if (cols_value = null) {cols_value = "";} Field field = cls. getDeclaredField (cols_name); // open the private access permission field of javabean. setAccessible (true); field. set (resultObject, cols_value) ;}// disable MysqlUtil. releaseConn (); return resultObject ;} /*** access the database through the reflection mechanism ** @ param SQL * @ param params * @ param cls * @ return * @ throws Exception */public static
                     
                       List
                      
                        FindMoreRefResult (String SQL, List
                       Params, Class
                        
                          Cls) throws Exception {// load the database driver new MysqlUtil (); // connect to the database MysqlUtil. GetConnection (); // construct an empty list with an initial capacity of 10. List
                         
                           List = new ArrayList
                          
                            (); // Indicates the first position of the placeholder int index = 1; pstmt = connection. prepareStatement (SQL); System. out. println ("MysqlUtil:" + params); // determines whether the filled placeholder has a value. Determine the standard way of the set if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {// use the given object to set the value of the specified parameter. The second parameter must be Object-type pstmt. setObject (index ++, params. get (I) ;}// return the query result System. out. println ("SHQ"); resultset = pstmt.exe cuteQuery (); // obtain the column information in java. SQL. resultSetMetaData metdata = resultset. getMetaData (); // get the number of columns int col_lenth = metdata. getColumnCount (); while (resultset. next () {// create an instance through the reflection mechanism T resultObject = cls. newInstance (); for (int I = 0; I <col_lenth; I ++) {String cols_name = metdata. getColumnNa Me (I + 1); Object cols_value = resultset. getObject (cols_name); if (cols_value = null) {cols_value = "" ;}// obtain the reflected Field field = cls through the column name. getDeclaredField (cols_name); // open the private access permission field of javabean. setAccessible (true); field. set (resultObject, cols_value);} list. add (resultObject);} // close MysqlUtil. releaseConn (); return list;}/*** close database link **/public static void releaseConn () {if (resultset! = Null) {try {resultset. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (pstmt! = Null) {try {pstmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (connection! = Null) {try {connection. close ();} catch (SQLException e) {e. printStackTrace ();}}} /*** total number of pages returned by the query ** @ param SQL * @ param params * @ return int * @ throws SQLException */public static int cluPage (String SQL, List
                           Params) throws SQLException {// total number of stored pages int countPage = 0; // set the number of records displayed per page int size = 10; // load the database driver new MysqlUtil (); // connect to the database MysqlUtil. getConnection (); // indicates the first position of the placeholder int index = 1; // This sentence is very important and requires pre-Compilation of pstmt = connection. prepareStatement (SQL); // judge whether the filled placeholder has a value; Determine the standard way of the set if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) {// The first parameter in your SQL statement, and the second parameter is the pstmt value to be set. setObject (index ++, params. get (I) ;}}// return the query result resultset = pstmt.exe cuteQuery (); if (resultset. next () {int total = resultset. getInt ("total"); countPage = (total % size = 0? Total/size: total/size + 1);} // close MysqlUtil. releaseConn (); System. out. println ("total page number:" + countPage); return countPage;}/*** Test Module ** @ param args */public static void main (String [] args) {User user = new User (); user. setUid ("18353102068"); user. setLogin_time ("1"); user. setOut_time ("1"); user. setLast_time (10); System. out. println (new UserDao (). add (user ));}}
                          
                         
                        
                      
                     
                    
                  
                 
                
               
              
            
           
          
         
       
      
     
    
  

This section describes the connection methods and common query operations for databases.

The constants are defined as follows:

Const. java

Package cn.edu. ujn. base; public class Const {// define the database's IPpublic final static String IP = "localhost"; // define the database Port public final static String DBPORT = "3308 "; // define the database name public final static String DBNAME = "lab"; // define the database USERNAME public final static String USERNAME = "lmapp "; // define the Database PASSWORD public final static String PASSWORD = "lmapp"; // define the database DRIVER information public final static String DRIVER = "com. mysql. jdbc. driver "; // defines the address for accessing the database public final static String URL =" jdbc: mysql: // "+ IP +": "+ DBPORT +"/"+ DBNAME ;}

 

The preceding steps only enable java connection to the database. To perform data insertion and other operations, you also need the following methods:

UserDao. java

 

Package cn.edu. ujn. dao; import java. SQL. SQLException; import java. util. arrayList; import java. util. list; import cn.edu. ujn. model. user; import cn.edu. ujn. util. mysqlUtil; public class UserDao {MysqlUtil mysqlUtil = new MysqlUtil ();/*** add user Information ** @ param User * @ return */public int add (user User) {try {String SQL = "insert into lab_static_attribute (uid, login_time, out_time, last_time) values (?,?,?,?) "; ListParams = new ArrayList(); Params. add (user. getUid (); params. add (user. getLogin_time (); params. add (user. getOut_time (); params. add (user. getLast_time (); return MysqlUtil. updateByPreparedStatement (SQL, params )? 1: 0;} catch (SQLException e) {return 0 ;}}}

Here we only introduce the data addition method and the other three CRUD operation methods of the database. Please exercise on your own.

Summary

Compared with the framework to implement database operations, the method described here does need to complete more code. However, it is recommended for beginners to perform database operations in this way. In this way, you can learn more about the underlying operations of database operations. Learn the knowledge and pay attention to "know its own, but also know its own ".

Meiwenmeitu