The JDBC Operation database D class under the idea of thinkphp large D method

Here, I encapsulated it.Dclass, is based onthinkphpin theDmethod, some of which are in and out of the way, I have made some personalized corrections, such as:thinkphpIn the operation of the database, you need to define the configuration of the database in the configuration file, here I use the external interface definition constants of the method to define, inDthe implementation of the interface in the class, the implementation of the interface constant reference, convenient configuration modification;Dclass also provides aExecutedmland theEXECUTEDQLthese two methods--IncomingSQLstatement can be more complicated to delete and change the operation。

There are many deficiencies in this category.

Dbconfig.java

Package Cn.zhku.myjdbc;public interface Dbconfig {public final String IP = "localhost";//mysql host public final int PORT = 3 306; MySQL port number public final String db_name = "mydb_329"; Database name Public final string db_username = "root";p ublic final string db_password = "";p ublic final String db_encoding = "UTF -8 "; Database encoding}

D.java

Package Cn.zhku.myjdbc;import Java.sql.connection;import Java.sql.date;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.linkedhashmap;import Java.util.List;public class D implements Dbconfig {protected String tableName = ""; Table name protected String where = ""; Conditions include placeholders, such as: id=?&name=?protected object[] wheredata;protected string[] fields; Action field protected object[] data; Pass data protected String limit = "";  The Limit statement private Connection con;private static final String insert_sql = "INSERT";p rivate static final String delete_sql = "Delete";p rivate static final String select_sql = "Select";p rivate static final String update_sql = "UPDATE";p ublic D (Str ing tableName) {//TODO auto-generated Constructor stubthis.tablename = Tablename;init ();} /** * Load Database driver, register to drive Manager * Set data connection * Create connection connection */private void init () {try {class.forname ("COM.MYSQL.JDbc. Driver ");//establish link string url =" jdbc:mysql://"+ IP +": "+ PORT +"/"+ db_name+"? user= "+ Db_username +" &password= " + Db_password + "&useunicode=true&characterencoding=" + Db_encoding;con = drivermanager.getconnection (URL);} catch (ClassNotFoundException e) {//Todo auto-generated catch Blocke.printstacktrace ();} catch (SQLException e) {//TODO Auto-generated catch Blocke.printstacktrace ();}} /** * Set Action field * @param a string array consisting of fields action field * @return returns the current object */public D field (String ... fields) {/*if (field.length = = 0) {T His.fields = "*";} else {this.fields = "'" + field[0] + "'"; for (int i = 1; i < field.length; i++) {this.fields + = ", '" + field[i] + "'"; }}*/this.fields = Fields;return This;} /** * Set Action field * @param fields pass in the string as SQL setting action field * @return */public D field (string fields) {if (' * '. Equals (Fields)} {this.fi ELDs = null;} else {this.fields = Fields.split (",");} return this;} /** * Set WHERE Condition * @param w JDBC Where condition, with placeholder * @return Returns the current object reference */public D where (string w, String... WD) {This.where = "where" + W;this.wheredata = Wd;return this;} /** * Set data data to be used when inserting data * @param @return */public D data (Object ... data) {this.data = Data;return this;} /** * Paging settings * @param cur current page * @param rows per page of records * @return */public D page (int cur, int rows) {this.limit = "limit" + (cur-1) * rows + "," + Rows;return this;}  /** * Limit restriction query * @param offset offset position * @param length gets the lengths * @return */public D limit (int offset, int length) {This.limit = "Limit" + offset + "," + Length;return this;} /** * Query Operations (you can set the fields to query by using the field method, do not use the field method to query all fields by default, you can set the criteria for a query by using the Where method) * @param field query fields */public list< Linkedhashmap<string, object>> Select () {try {String sql = Createsql (select_sql); PreparedStatement PS = this.getpreparedstatement (sql, this.wheredata); list<linkedhashmap<string, object>> list = Getres (PS); return list;} catch (SQLException e) {//TODO auto-generated catch block//e.printstacktrace (); System.out.println ("Error:select Failed");} Finally {This.reset ();} return null;} /** * Package Result set * @param PS * @return * @throws SQLException */private list<linkedhashmap<string, object>> getres ( PreparedStatement PS) throws SQLException {ResultSet rs = ps.executequery (); ResultSetMetaData RSMD = Rs.getmetadata (); Get table information int cols = Rsmd.getcolumncount (); Gets the number of columns of the table string[] columnName = new String[cols];for (int i = 0; i < cols; i++) {Columnname[i] = Rsmd.getcolumnname (i + 1);} list<linkedhashmap<string, object>> list = new arraylist<linkedhashmap<string, Object>> (); while (Rs.next ()) {linkedhashmap<string, object> m = new linkedhashmap<string, object> (); for (int i = 0; I &lt ; Cols i++) {m.put (Columnname[i], rs.getstring (i + 1));} List.add (m);} Ps.close (); Rs.close (); return list;} /** * Add action (you can set the field to be added by using the field method, do not use the field method, indicate that you need to add all fields, data that can be added by the data method, add an error without using the data method) * @return */public int add () {return Addcommon ();} /** * Add action (you can set the field you want to add by using the field method, not using the field method, which means you need to add all fields) * @param data to be added *@return returns the number of rows affected, 1 indicates that the addition of data failed */public int Add (Object ... data) {This.data = Data;return Addcommon ();} /** * Add Operation * @return returns the number of rows affected, 1 indicates failure to add data */private int Addcommon () {String sql = Createsql (insert_sql); if ("". Equals (SQL)) {This.reset (); return-1;} return executedml (SQL, this.data);} /** * Update operation (you can set the field to be updated by using the field method, update the error without using the field method, you can set the data to be updated by means of a data method, update the error without using the data method, you can set the update condition by using the Where method) * @ Return returns the number of rows affected, 1 means the update data failed */public int save () {return Savecommon ();} /** * Update action (you can set the field to update by using the field method, updating the error without using the field method; You can set the update condition by using the Where method) * @param data to set up the information to be updated * @return */public int Save ( Object ... data) {This.data = Data;return Savecommon ();} /** * Update operation * @return returns the number of rows affected, 1 means that the update data failed */private int Savecommon () {String sql = Createsql (update_sql), if ("". Equals (SQL) || This.data = = null) {This.reset (); return-1;} Merge data from This.data and this.wheredata object[] arr = new Object[this.data.length + (This.wheredata = null? 0:this.whereda Ta.length)];if (arr.length > 0) {for (int i = 0; This.data! = null &&Amp i < this.data.length; i++) {Arr[i] = this.data[i];} for (int i = this.data.length, j = 0; This.wheredata! = null && i < arr.length; i++, J + +) {Arr[i] = This.whereda TA[J];}} return executedml (SQL, arr);} /** * Delete operation * You can use the Where method to set the update condition, without deleting the condition will delete the data in the table * @return return the number of rows affected, 1 means the update data failed */public int Delete () {return Deletecommo n ();}  /** * Delete operation * @param where to set the Where condition (SQL statement, with placeholders) * @param data that is assigned to a placeholder in the wheredata where condition * @return Returns the number of rows affected, 1 means that the update data failed */public int delete (String where, Object ... wheredata) {this.where = "where" + Where;this.wheredata = Wheredata;return deletecom Mon ();} /** * Delete operation * @return returns the number of rows affected, 1 indicates failure to update data */private int Deletecommon () {String sql = Createsql (delete_sql); if (This.wheredat A = = null) {This.reset (); return-1;} return executedml (SQL, this.wheredata);} /** * Create SQL statement based on type * @param type of SQL statement * @return SQL statement */private string createsql (String type) {String sql = ""; if (INS Ert_sql.equals (type)) {String field = FormFields (); if (! "". Equals (field)) {field = "("+ field +") ";} if (this.data! = null && this.data.length > 0) {sql = "INSERT into" + this.tablename + field + "VALUES"; sql += "(?"; for (int i = 1; i < this.data.length; i++) {sql + = ",?";} SQL + = ")";} else {return "";}} else if (delete_sql.equals (type)) {SQL = "DELETE from" + This.tablename + this.where;} else if (select_sql.equals (type)) {String field = FormFields (); if (". Equals (field)) {field =" * ";} sql = "Select" + Field + "from" + this.tablename + this.where + this.limit;} else if (update_sql.equals (type)) {if (This.fields = = NULL | | this.fields.length = = 0) {return "";} else {sql = "UPDATE" + this.tablename + "SET '"; SQL + = This.fields[0] + "' =?"; for (int i = 1; i < this.fields.length; i++) {sql + = ", '" + this.fields[i] + "' =?";} SQL + = This.where;}} return SQL;} /** * The field in the SQL statement is composed of * @return */private string FormFields () {string f = ""; if (this.fields! = null) {f = "'" + this.fields[0 ] + "'"; for (int i = 1; i < this.fields.length; i++) {f + = ", '" + this.fields[i] + "'";}} return F;} /** * Create a PreparedStatement object from an SQL statement and assign a value to the parameter * @param SQL SQL statement * @param data data * @return PreparedStatement object * @throws SQLE Xception */private preparedstatement getpreparedstatement (String sql, Object ... data) throws SQLException { PreparedStatement PS = con.preparestatement (SQL), if (data = = null) return PS; There is no where in the select operation and there is no where? The following settings are not required for (int i = 0; i < data.length; i++) {Object param = data[i];if (param insta Nceof Integer) {int value = ((Integer) param). Intvalue ();p s.setint (i + 1, value);} else if (param instanceof String) {Stri ng s = (String) param;ps.setstring (i + 1, s);} else if (param instanceof double) {Double d = ((Double) param). Doublevalue ();p s.setdouble (i + 1, d);} else if (param Insta nceof float) {Float F = ((Float) param). Floatvalue ();p s.setfloat (i + 1, f);} else if (param instanceof Long) {Long L = ((l ONG) param). Longvalue ();p S.setlong (i + 1, l);} else if (param instanceof Boolean) {Boolean b = ((Boolean) param). Booleanvalue ();p S.setbooLean (i + 1, b);} else if (param instanceof date) {Date d = (date) param;ps.setdate (i + 1, (date) param);}} return PS;}  /** * Execute DML statement inset/update/delete * @param SQL SQL statement * @param arr array for SQL statement placeholder * @return * @throws SQLException */public int executedml (String sql, Object ... arr) {try {preparedstatement PS = getpreparedstatement (sql, arr); int row = Ps.execut Eupdate ();p s.close (); return row > 0? Row:-1;} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} finally {This.reset ();} return-1;} /** * Execute DQL statement SELECT * @param SQL SQL statement * @param arr array for SQL statement placeholder * @return */public list<linkedhashmap<string, Ob ject>> executedql (String sql, Object ... arr) {try {preparedstatement PS = this.getpreparedstatement (sql, arr); list<linkedhashmap<string, object>> list = Getres (PS); return list;} catch (SQLException e) {//TODO auto-generated catch block//e.printstacktrace (); System.out.println ("Error:select Failed");} finally {This.reset ();} Return null;} /** * Set the condition (where) or operation (fields) field or data to the initial state */public void Reset () {this.where = ""; this.wheredata = Null;this.fields = Nu Ll;this.data = Null;this.limit = "";} /** * Close the connection of the connection object */public void Close () {try {con.close ();} catch (SQLException e) {//TODO auto-generated catch Blo Ck//e.printstacktrace (); System.out.println ("Error:close Failed");}}

Demo class App.java

Package Cn.zhku.myjdbc;import Java.util.linkedhashmap;import Java.util.list;import Java.util.map;public class App { public static void Main (string[] args) {d d = new D ("T_user");//adddemo (d); Selectdemo (d);/*system.out.println (); Updatedemo (d); System.out.println (); Selectdemo (d); *//*deletedemo (d); System.out.println (); Selectdemo (d); *///out (D.EXECUTEDQL ("SELECT * from T_user", null));//D.EXECUTEDML ("DELETE from T _user WHERE name=? "," soy sauce 5 ");d. Close (); /** * Select method in Class D demo demo * @param d */public static void Selectdemo (d d) {//1. query all field data for all records//1.1 default query All field data for all records List<li Nkedhashmap<string, object>> s = d.select ();//1.2 uses the string "*" to achieve the same effect//list<linkedhashmap<string, Object >> s = D.field ("*"). Select ();//1.3 set field in array mode//string[] fields = {"Name", "Grade"};//list<linkedhashmap< String, object>> s = D.field (Fields). Select ();//1.4 set field as parameter//list<linkedhashmap<string, object> > s = D.field ("name", "Grade"). Select ();//2. query grade field data for all records//list<linkedhashmap<string, object>> s = D.field ("Grade"). Select (),//3. Add where Condition//list<linkedhashmap<string, object> > s = D.field ("*"). WHERE ("Name=?", "Dreamboy"). Select ();//4. Paging query (5 rows per page, check the records on page 3rd)//list<linkedhashmap< String, object>> s = d.page (3,5). Select (); Out (s); public static void Out (List<linkedhashmap<string, object>> s) {if (s = = null) {System.out.println ("Error: The result is empty! "); return;} For (linkedhashmap<string, object> map:s) {Boolean isfist = True;for (map.entry<string, object> ENTRY:MAP.E Ntryset ()) {if (isfist) {System.out.print (Entry.getvalue ()); isfist = false;} else {System.out.print (/* "Key =" + Entry.getkey () + ", Value =" +*/"" + Entry.getvalue ());}} System.out.println ();}} /** * The Add method in the Class D demo demo * @param d */public static void Adddemo (d d) {//1. Use the data method to set the inserted information for (int i = 0; i < 5; i++) {Stri ng name = "soy sauce" + i;int grade = (int) (Math.random () * 101);d. Data (name, Grade). Add (); 2. Pass to the Add method the data to be inserted, passing in for as parameter (int i = 0; i < 5; i++) {StRing name = "Soy sauce" + (i + 5); int grade = (int) (Math.random () * 101);d. Add (name, grade);}} /** * Save method in Class D demo demo * @param d */public static void Updatedemo (d d) {//1. Using the data method to pass the updated data//d.field ("Grade"). Data (in. wh) Ere ("Name=", "Dreamboy"). Save ();//2. In the Save method, the updated data D.field ("grade") is passed in. where ("Name=?", "Dreamboy"). Save (99); /** * Delete method in Class D demo demo * @param d */public static void Deletedemo (d d) {//1. Use the Where method to set the delete condition//d.where ("Name=?", "soy sauce 1"). de Lete ();//2. In the Delete method, the deletion condition D.delete ("Name=?", "Soy sauce 2");}}

The JDBC Operation database D class under the idea of thinkphp large D method