Java database Query tool class.

Import java.util.List;
Import Java.util.Map;

/**
* Database query tool class.
*
*/
public class Querytool {

/**
* Checks if is condition.
*
* @param obj The obj
* @return True, if is condition
*/
public static Boolean iscondition (Object obj) {
if ("". Equals (getString (obj)))
return false;
Else
return true;
}

/**
* Gets the string from the Object,null value into an empty string.
*
* @param obj The obj
* @return The string
*/
public static String getString (Object obj) {
return obj! = null? Obj.tostring (): "";
}

/**
* Stitching SQL in condition.
*
* @param columnkey the column key
* @param columnvaluelist the column value list
* @param joinop the Join OP
* @param sb the SB
*/
public static void Appendconditionin (String columnkey, list<string> columnvaluelist,
String Joinop, StringBuffer sb) {
if (Querytool.iscondition (columnvaluelist)) {
Sb.append ("+ Joinop +" "+ Columnkey +" in "+ Querytool.listtosqlin (columnvaluelist));
}
}

/**
* Stitching SQL conditions.
*
* @param columnkey the column key
* @param columnvalue the column value
* @param joinop the Join OP
* @param op the OP
* @param sb the SB
* @param paramslist the params list
*/
public static void Appendcondition (String columnkey, Object Columnvalue,
String Joinop, String op, StringBuffer sb, list<object> paramslist) {
if (Querytool.iscondition (Columnvalue)) {
Sb.append ("+ Joinop +" "+ Columnkey +" "+ op +"? ");
Paramslist.add (Columnvalue);
}
}

/**
* Production of paged SQL.
*
* @param sql The SQL
* @param pageIndex the page index
* @param pageSize the page size
* @param paramslist the params list
* @return The string
*/
public static string Buildpagesql (String sql, int pageIndex, long pageSize,
List<object> paramslist) {
Long Fromrownum = PageSize * (pageIndex-1) + 1;
Long Torownum = pageSize * PAGEINDEX;
StringBuffer sbnewsql = new StringBuffer ();

Sbnewsql.append ("select x.*");
Sbnewsql.append ("From", "Select RowNum x_rownum,o.* from ("). Append (SQL)
. Append (") o) x");
Sbnewsql.append ("where x_rownum>=?"). Append ("and x_rownum<=?");
Sbnewsql.append ("ORDER by X.x_rownum");
Paramslist.add (Fromrownum);
Paramslist.add (Torownum);

return sbnewsql.tostring ();
}

/**
* Generate statistical SQL.
*
* @param sql The SQL
* @return The string
*/
public static string Buildcountsql (String sql) {
Return "SELECT COUNT (*) from (" + SQL + ")";
}

/**
* Obtain the corresponding data in the map, null or NULL to convert to default values.
*
* @param map the map
* @param name The name
* @param replace the Replace
* @return The property
*/
public static string GetProperty (Map<string,?> Map, string name,
String Replace) {
return Map.get (name)! = NULL
&& map.get (name). ToString (). Trim (). Length () > 0? Map.get (name)
. toString (): replace;
}

/**
* List to SQL in.
*
* @param stringlist the string list
* @return The string
*/
public static String Listtosqlin (list<string> stringlist) {
StringBuilder sb = new StringBuilder ();

if (stringlist = = NULL | | stringlist.size () = = 0)
Return "";

for (String string:stringlist) {

if (sb.length () = = 0)
Sb.append ("(')"). Append (String). Append ("'");
Else
Sb.append (","). Append (String). Append ("'");
}

Sb.append (")");

return sb.tostring ();
}

/**
* Obtain gender according to * * *.
*
* @param val the Val
* @return the sex from ID
*/
public static Boolean Getsexfromid (String val) {
Boolean sex = false;
15 Digit * * * number
if (= = Val.length ()) {
if (integer.valueof (Val.charat)/2) * 2! = Val.charat (14))
Sex = true;
Else
sex = false;
}
18 Digit * * * number
if (= = = Val.length ()) {
if (integer.valueof (Val.charat)/2) * 2! = Val.charat (16))
Sex = true;
Else
sex = false;
}
return sex;
}

/**
* * * * # 15 ext. 18 bit
*
* @author CJJ 2010-5-13 01:04:53
* @param ID
* @return
*/
public static final String Getneweighteenid (string id) {

Final int[] W = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2,
1};
Final string[] A = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3",
"2"};
int I, j, s = 0;
String newid;
NEWID = ID;
NEWID = newid.substring (0, 6) + "+" + newid.substring (6, Id.length ());
for (i = 0; i < newid.length (); i++) {
j = Integer.parseint (newid.substring (i, i + 1)) * W[i];
s = s + j;
}
s = s% 11;
NEWID = newid + a[s];

return newid;

}

/**
* * * * # 18 ext. 15 bit
*
* @author CJJ 2010-5-13 02:06:51
* @param ID
* @return
*/
public static string Getnewfifteenid (string id) {
String newid = id.substring (0, 6) + id.substring (8, Id.length ()-1);
return newid;
}

/**
* * * * Number 15, 18-bit interchange.
*
* @param ID
* @return the exchange p ID
*/
public static string Getexchangepid (string id) {
if (Id.length () ==15)
return Getneweighteenid (ID);
else if (id.length () ==18)
return Getnewfifteenid (ID);
Else
return ID;
}

}

Use:

Public Page findloginrecordlist (String machinecode,string pageindex,string pagesize) {
int pageindexint=integer.valueof (PAGEINDEX);
int pagesizeint=integer.valueof (pagesize);
list<object> paramslist = new arraylist<object> (); Defines a collection of stitching parameters
StringBuffer s = new StringBuffer ();
S.append ("Select T.certno, T.client,t.type, To_char (T.inouttime, ' Mm/dd hh24:mi:ss ') as Inouttime from Dc_loginrecord t where 1=1 ");
Querytool.appendcondition ("T.machinecode", Machinecode, "and", "=", S, paramslist);//stitching conditions
S.append ("ORDER by t.inouttime Desc");
String sql = Querytool.buildcountsql (s.tostring ()); SQL for Total queries
Long totalsize = This.queryforint (Sql,paramslist.toarray (New Object[paramslist.size ())); JDBC Query
if (TotalSize > 0) {
String SL = Querytool.buildpagesql (s.tostring (), Pageindexint,pagesizeint, paramslist);//Splicing Paging
list<map<string,object>> list = This.queryforlist (Sl,paramslist.toarray (New Object[paramslist.size ())) ); JDBC Query
int start = Pagesizeint * (pageIndexInt-1) +1; Where the data from this page starts in the database
return new Page (Pagesizeint * (start-1) +1, totalsize, Pagesizeint, list);
}else{
return new Page (0, 0, ten, New ArrayList ());
}
}

Note:

Class for pagination:

Import java.io.Serializable;
Import java.util.ArrayList;

/**
* Paging object. Contains data and paging information.
*/
public class Page implements Serializable {

static private int default_page_size = 20;

/**
* Number of records per page
*/
private int pageSize = Default_page_size;

/**
* Current Page The position of the first data in list, starting from 0
*/
private int start;

/**
* Records stored in the current page, type is generally list
*/
Private Object data;

/**
* Total Record Count
*/
Private long totalcount;

/**
* Construction method, only empty pages are constructed
*/
Public Page () {
This (0, 0, default_page_size, New ArrayList ());
}

/**
* Default Constructor method
*
* @param start
* The starting position of this page data in the database
* @param totalsize
* Total number of records in the database
* @param pageSize
* Volume on this page
* @param data
* The data contained on this page
*/
Public Page (int start, long totalsize, int pageSize, Object data) {
This.pagesize = pageSize;
This.start = start;
This.totalcount = totalsize;
This.data = data;
}

/**
* Fetch the total number of records contained in the database
*/
Public long Gettotalcount () {
return this.totalcount;
}

/**
* Total Pages taken
*/
Public long Gettotalpagecount () {
if (totalcount% pageSize = = 0)
return totalcount/pagesize;
Else
return totalcount/pagesize + 1;
}

/**
* Data Capacity per page
*/
public int getpagesize () {
return pageSize;
}

/**
* Records in the current page
*/
Public Object GetResult () {
return data;
}

/**
* Take the current page number, page number starting from 1
*/
public int Getcurrentpageno () {
return start/pagesize + 1;
}

/**
* Do you have the next page?
*/
public Boolean hasnextpage () {
Return This.getcurrentpageno () < This.gettotalpagecount ()-1;
}

/**
* Do you have a previous page?
*/
public Boolean haspreviouspage () {
return This.getcurrentpageno () > 1;
}

/**
* Get the position of the first data on any page, using default values per page count
*/
protected static int getstartofpage (int pageno) {
Return Getstartofpage (PageNo, default_page_size);
}

/**
* Get the position of the first data on any page, startindex starting from 0
*/
public static int Getstartofpage (int pageno, int pageSize) {
Return (pageNo-1) * pageSize;
}
}

Java database Query tool class.