Java Advanced (25) Java connection MySQL database (bottom-level implementation)

Java Advanced (25) Java connection MySQL database (underlying implementation) preface

For a long time there was no systematic use of Java to do projects. Now you need to complete an experiment with Java, which involves the Java connection database. Let yourself write, there is no search in memory. A simple summary of the methods used earlier. It is also possible to understand the specific steps of connecting to a database at the bottom.

Realize

First you need to import the relevant jar package, I am using: Mysql-connector-java-5.1.7-bin.jar.

Here's a look at the database connection method Class I'm using:

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 {//user name for the definition database private final static String USERNAME = const.username;//defines the password for the database private final static  String PASSWORD = const.password;//defines the driver information for the database private final String DRIVER = const.driver;//Defines the address of the Access database private final static String URL = const.url;//defines the link to the database private static Connection connection;//defines the execution object of the SQL statement private static PreparedStatement PS tmt;//can execute SQL statement//definition query returned result collection private static ResultSet ResultSet only after obtaining statement; Public Mysqlutil () {try {class.forname (DRIVER);//explicitly load the JDBC driver System.out.println ("Registration driver succeeded! ");} catch (Exception e) {System.out.println ("Registration driver failed!") ");}}  /** * Defines a link to the database to attempt to establish a connection to a given database URL * * @return connection */public static connection getconnection () {try {connection = Drivermanager.getconnection (URL, USERNAME, PASSWORD); SYSTEM.OUT.PRINTLN ("Database connection succeeded!");} catch (Exception e) {System.out.println ("Database connection failed!");} return connection;} /** * Complete Add, delete, update operation to database table * * @param SQL * @param params * @return flag * @throws SQLException */public Static Boolean upd Atebypreparedstatement (String sql, list<object> params)//The second parameter is a placeholder for the transport throws SQLException {//LOAD database-driven new Mysqlutil ();//Connect database Mysqlutil.getconnection (); Boolean flag = false; Represents the number of rows in the database that are affected when the user executes the add, delete, modify, int result = -1;try {pstmt = connection.preparestatement (sql),} catch (Exception e) {SYSTEM.O Ut.println ("Error in updatebypreparedstatement!");} Represents the first position of a placeholder int index = 1;  Determines whether the filled placeholder has a value, the standard way to judge the set if (params! = null &&!params.isempty ()) {for (int i = 0; i < params.size (); i++) {// System.out.println (i+ "" +params.get (i));//sets the value of the specified parameter with the given object. The second parameter must be an OBJect type Pstmt.setobject (index++, Params.get (i));}} result = Pstmt.executeupdate ();//For executing DML statements-Returns an integer representing the number of record bars affected by the SQL statement flag = result > 0? True:false; SYSTEM.OUT.PRINTLN ("Execute SQL statement affects the number of record bars:" + result);//Close Database Mysqlutil.releaseconn (); return flag;} /** * Query returns a single record * * @param SQL * @param params * @return map * @throws SQLException */public static map<string, object& Gt Findsimpleresult (String sql, list<object> params) throws SQLException {//Load Database driver new Mysqlutil ();// Connection database mysqlutil.getconnection ();//diamond syntax in Java7 and later available map<string, object> map = new hashmap<string, object> (); Represents the first position of a placeholder int index = 1; This sentence is important and needs to be precompiled pstmt = connection.preparestatement (sql); Determines whether the filled placeholder has a value, the standard way to judge the set if (params! = null &&!params.isempty ()) {for (int i = 0; i < params.size (); i++) {/ /The first one refers to the number of arguments in your SQL statement, the second is the value to set Pstmt.setobject (index++, Params.get (i));}} Returns the query result resultset = Pstmt.executequery (); Gets the number, type, and property of the column for this ResultSet object. Java.sql.ResultSetMetaData Metdata = Resultset.getmetaData (); Gets 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++) {//Gets the name of the specified column string cols_name = metdata.getcolumnname (i + 1); Gets the property value of the specified column by the column name Object cols_value = Resultset.getobject (Cols_name), if (Cols_value = = null) {///Thus, the field value in the datasheet cannot be empty Cols_ Value = "";} Associates the specified value with the specified key in this map (optional operation). Map.put (Cols_name, Cols_value);}} Close database Mysqlutil.releaseconn (); return map;} /** * Through the reflection mechanism to access the database, query the contents of the previous pages * * @param SQL * @param params * @param cls * @return * @throws Exception */public Static < T> list<t> Queryevamall (String sql, list<object> params,class<t> cls) throws Exception {// Load the database driver new Mysqlutil ();//Connect to Database Mysqlutil.getconnection ();//construct an empty list with an initial capacity of 10. list<t> list = new arraylist<t> (); Represents the first position of a placeholder int index = 1; pstmt = connection.preparestatement (sql); Determines whether the filled placeholder has a value, and the standard way to judge the set if (params! = null &&!params.isempty (){for (int i = 0; i < params.size (); i++) {//sets the value of the specified parameter with the given object. The second parameter must be an object type Pstmt.setobject (index++, Params.get (i)); }}//return query result resultset = Pstmt.executequery (); Gets information about the column java.sql.ResultSetMetaData metdata = Resultset.getmetadata (); Gets the number of columns int col_lenth = Metdata.getcolumncount (); while (Resultset.next ()) {//Create an instance by reflection mechanism t Resultobject = Cls.newinstanc E (); 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 = "";} Get reflection By Column Name field field = Cls.getdeclaredfield (Cols_name); Open JavaBean Private access rights field.setaccessible (true); Field.set (Resultobject, cols_value);} List.add (resultobject);} Close database Mysqlutil.releaseconn (); return list;} /** * Query returns multiple query records * * @param SQL * @param params * @return * @throws SQLException */public static list<map<string, O Bject>> Findmoreresult (String sql,list<object> params) throws SQLException {//Load Database driver new Mysqlutil ();// Connecting to a databaseMysqlutil.getconnection (); System.out.println ("JJ"); list<map<string, object>> list = new arraylist<map<string, object>> ();//Represents the first position of a placeholder int index = 1;pstmt = connection.preparestatement (sql);//Determine if the placeholder is filled with a value; Determine the standard way to set if (params! = null &&!params.isempty ()) { for (int i = 0; i < params.size (); i++) {Pstmt.setobject (index++, Params.get (i));//sets the value of the specified parameter with the given object. The second parameter must be A//object type}}try {resultset = Pstmt.executequery ();//return query result} catch (Exception e) {System.out.println ("error1!"); Debug with}resultsetmetadata Metdata = Resultset.getmetadata ();//Get the information about the column int col_lenth = Metdata.getcolumncount ();// Gets the number of columns System.out.println ("Number of data table columns:" + col_lenth);//debug with while (Resultset.next ()) {map<string, object> Map = new  Hashmap<string, object> (); for (int i = 0; i < Col_lenth; i++) {String cols_name = metdata.getcolumnname (i + 1);// Gets the name of the column, starting with 1 object cols_value = Resultset.getobject (Cols_name), if (Cols_value = = null) {Cols_value = "";} Map.put (Cols_name, Cols_value);}list.add (map);} Close database Mysqlutil.releaseconn (); return list;}  /** * JDBC Encapsulation can use the reflection mechanism to encapsulate the use of generic methods * * @param SQL * @param params * @param cls * @return * @throws Exception */public static <T> T Findsimplerefresult (String sql, list<object> params,class<t> cls) throws Exception {// Load Database driver new Mysqlutil ();//Connect database mysqlutil.getconnection (); T resultobject = null; Represents the first position of a placeholder int index = 1;PSTMT = connection.preparestatement (sql); Determines whether the filled placeholder has a value, the standard way to judge the set if (params! = null &&!params.isempty ()) {for (int i = 0; i < params.size (); i++) {/ /The first one refers to the number of arguments in your SQL statement, the second is the value to set Pstmt.setobject (index++, Params.get (i));}} Returns the query result resultset = Pstmt.executequery (); Gets information about the column java.sql.ResultSetMetaData metdata = Resultset.getmetadata (); Gets the number of columns int col_lenth = Metdata.getcolumncount (); while (Resultset.next ()) {//Create an instance through reflection mechanism 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 JavaBean Private access rights field.setaccessible (true); Field.set (Resultobject, cols_value);} }//Close Database Mysqlutil.releaseconn (); return resultobject;} /** * Access database through reflection mechanism * * @param SQL * @param params * @param cls * @return * @throws Exception */public static <T> Lis t<t> Findmorerefresult (String sql, list<object> params, class<t> cls) throws Exception {//Load database driver new M Ysqlutil ();//Connect database mysqlutil.getconnection ();//construct an empty list with an initial capacity of 10. list<t> list = new arraylist<t> (); Represents the first position of a placeholder int index = 1; pstmt = connection.preparestatement (sql); System.out.println ("Mysqlutil:" + params);//Determine if the placeholder is filled with a value; Determine the standard way to set if (params! = null &&!params.isempty ()) { for (int i = 0; i < params.size (); i++) {//sets the value of the specified parameter with the given object. The second parameter must be an object type Pstmt.setobject (index++, Params.get (i)); }}//returns the query result System.out.println ("Shq"); resultset = Pstmt.executequery ();//Gets the information about the column JAVA.SQL.REsultsetmetadata metdata = Resultset.getmetadata (); Gets the number of columns int col_lenth = Metdata.getcolumncount (); while (Resultset.next ()) {//Create an instance by reflection mechanism t Resultobject = Cls.newinstanc E (); 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 = "";} Get reflection By Column Name field field = Cls.getdeclaredfield (Cols_name); Open JavaBean Private access rights field.setaccessible (true); Field.set (Resultobject, cols_value);} List.add (resultobject);} Close database Mysqlutil.releaseconn (); return list;} /** * Close Database link * */public static void Releaseconn () {if (resultset! = null) {try {resultset.close ();} catch (Sqlexceptio n 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 ();}}} /** * Query returns total pages * * @param SQL * @param params * @return int * @throws SQLException */public STATic int clupage (String sql, list<object> params) throws SQLException {//store total pages int countpage = 0;//Set the number of records displayed per page int size = 10;//Load Database driver new Mysqlutil ();//Connection Database mysqlutil.getconnection ();//Represents the first position of the placeholder int index = 1;//This sentence is important and needs to be precompiled pstmt = Connection.preparestatement (SQL);//Determine if the placeholder is filled with a value; Determine the standard way to set if (params! = null &&!params.isempty ()) {for (int i = 0; I < params.size (); i++) {//first refers to the number of arguments in your SQL statement, the second is the value to set Pstmt.setobject (index++, Params.get (i));}} Returns the query result resultset = Pstmt.executequery (); if (Resultset.next ()) {int total = Resultset.getint ("total"); Countpage = ( Total%size = = 0? Total/size:total/size + 1);} Close database Mysqlutil.releaseconn (); System.out.println ("Total pages:" + countpage); return countpage;} /** * Test Module * * @param args */public static void main (string[] args) {User user = new user () User.setuid ("18353102068"); us Er.setlogin_time ("1"); User.setout_time ("1"); User.setlast_time (10); System.out.println (New Userdao (). Add (user));}}

The above describes the database connection methods and common query operations.

The constants are defined as follows:

Const.java

Package cn.edu.ujn.base; public class Const {//defines the ippublic final static string IP = "localhost" of the database,//defines the port of the database public final static string dbport = "3308";//define the name of the database public final static string DBNAME = "Lab";//define the user name of the database public final static string USERNAME = "Lmapp";//fixed The password for the Righteous database public final static string PASSWORD = "Lmapp";//define the driver information for the database public final static string DRIVER = "Com.mysql.jdbc.Driv ER ";//define the address of the Access database public final static String URL =" jdbc:mysql://"+ IP +": "+ Dbport +"/"+ DBNAME;}

The above is just the implementation of Java connection to the database, to achieve data insertion and other operations, but 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 (?,?,?,?)"; list<object> params = new arraylist<object> ();p Arams.add (User.getuid ());p Arams.add (User.getlogin_time ( ));p Arams.add (User.getout_time ());p Arams.add (User.getlast_time ()), Return Mysqlutil.updatebypreparedstatement ( SQL, params)? 1:0;} catch (SQLException e) {return 0;}}}

Here is just a description of the method of data addition, database crud three other methods of operation, please the reader to practice their own.

Summarize

Compared to the framework implementation of the database operation, the method described here does need to complete more code. But for beginners, it is recommended that this method be used to complete the operation of the database. This gives you more insight into the underlying operational behavior of database operations. Learn to pay attention to "know it, but also know the reason why."

American and American Pictures

Java Advanced (25) Java connection MySQL database (bottom-level implementation)