[Java] implement fuzzy query of Mysql Data Using JDBC

Document directory

• Core Methods
• Requirements
• Query Analysis
• Create a two-dimensional array in jtable
• Add a two-dimensional array to the jtable
• Refresh results

Core Methods

select * from AAA where A like ‘%a%’ and B like '%b%' and C like '%c%' and...

In the preceding formula, AAA indicates the table name, a, B, c,..., and column name.

That is, to query records in column A containing a character, column B containing B character, and column C containing c character in column AAA ..

Requirements

For example, you need to query the flight information according to any one or more conditions, such as the flight schedule, departure station, and destination station.

Query Analysis

For the "flight frequency" input box, to ensure the rationality and validity of the query:

(1) If the input result is null, the query statement should be... where fname like '% '...

(2) If the input result is not empty, the query statement is... where fname like '% query content % '...

Therefore, we can use the three-object operator to represent the content after like:

String string1 = (jTextField1.getText().trim().isEmpty()) ? ("'%'"): ("'%" + jTextField1.getText() + "%'");

Create a two-dimensional array in jtable

Through core methods and query analysis, you can use SQL statements to fuzzy query the database content and convert it into a two-dimensional array.

private Object[][] tableObjects() {String string1 = (jTextField1.getText().trim().isEmpty()) ? ("'%'"): ("'%" + jTextField1.getText() + "%'");String string2 = (jTextField2.getText().trim().isEmpty()) ? ("'%'"): ("'%" + jTextField2.getText() + "%'");String string3 = (jTextField3.getText().trim().isEmpty()) ? ("'%'"): ("'%" + jTextField3.getText() + "%'");String string = "select fid,fname,ftype,"+ "fstart,fend,fstarttime,fendtime,fprice,fdiscount,"+ "fnumber,fnote from ticket_flight " + "WHERE fname LIKE "+ string1 + " and fstart LIKE " + string2 + " and fend LIKE "+ string3;Object[] objects = {};return fDao.resultSetToObjectArray(fDao.getResultSet(string, objects));}

The getresultset method is as follows:

/** Query and return record set */Public resultset getresultset (string SQL, object [] objarr) {getconn (); try {PS = Conn. preparestatement (SQL, resultset. type_scroll_insensitive, resultset. concur_read_only); If (objarr! = NULL & objarr. length> 0) {for (INT I = 0; I <objarr. length; I ++) {ps. setobject (I + 1, objarr [I]) ;}} rs = ps.exe cutequery ();} catch (sqlexception e) {e. printstacktrace ();} finally {// close ();} Return Rs ;}

The resultsettoobjectarray method is as follows:

/** Convert the result set to object [] [] */public object [] [] resultsettoobjectarray (resultset RS) {object [] [] DATA = NULL; try {Rs. last (); int rows = Rs. getrow (); Data = new object [rows] []; resultsetmetadata MD = Rs. getmetadata (); // get the metadata int columncount = md of the record set. getcolumncount (); // Number of columns Rs. beforefirst (); int K = 0; while (RS. next () {object [] ROW = new object [columncount]; for (INT I = 0; I <columncount; I ++) {row [I] = Rs. getObject (I + 1 ). tostring () ;}data [k] = row; k ++ ;}} catch (exception e) {}finally {close () ;}return data ;}

Add a two-dimensional array to the jtable

For more information, see blog: http://blog.csdn.net/jueblog/article/details/9635527.

Set the listener for the query button:

Private void jbutton4actionreceivmed (Java. AWT. event. actionevent EVT) {// todo add your handling code here: Query jtable1.setmodel (New defaulttablemodel (tableobjects (), tablestrings ));}

Tablestrings is the header string:

String [] tablestrings = {"flight ID", "flights", "aircraft model", "departure city", "fly to city", "departure time ", "arrival time", "air ticket price", "lowest discount", "Total number of air tickets", "Remarks "};

For example, if you query a flight with "3" in "flight number", the result is as follows:

On this basis, add query conditions: the number of flights with a "Door" on the target station. The result is as follows:

Refresh results

For more convenient use, you can refresh the button to display all flights and clear the information in the Three query input boxes:

Set the listener for the "refresh" key as follows:

Private void jbutton5actionreceivmed (Java. AWT. event. actionevent EVT) {// todo add your handling code here: refresh flightrefresh ();}

The flightrefresh method is as follows:

/** Refresh flight */Public void flightrefresh () {jtextfield1.settext (null); jtextfield2.settext (null); jtextfield3.settext (null); object [] [] dataobjects = fdao. resultsettoobjectarray (fdao. getresultset ("select FID, fname, FTYPE," + "fstart, fend, fstarttime, fendtime, fprice, fdiscount," + "fnumber, fnote from ticket_flight", null )); jtable1.setmodel (New defaulttablemodel (dataobjects, tablestrings ));}