CREATE PROC P_SHOW @ querystr nvarchar (4000), - Table name, view name, query statement @PageSize int = 10, ?? - Size (number of lines) @PAGECURRENT INT = 1, ?? To display Page @fdshow nvarchar (4000) = '' ,- To display the list of fields, if the query result is identified by the identity field, you need to specify this value without including the identification field @fdorder nvarchar (1000) = '- Sort field List AsDeclare @fdname nvarchar (250) - The primary key or table in the table, the identity column name in the temporary table? @ Id1 varchar (20), @ id2 varchar (20) - start and end record number?, @ @ @ @ @ I Obj_id int ??? - Object ID - Table Handling Declare @strfd nvarchar (2000) - Composite primary key list?, @ Strjoin nvarchar (4000) - Connection field ?, @ Strwhere Nvarchar (2000) --Query conditions
SELECT @ obj_id = Object_ID (@querystr)? @ fdshow = case isnull (@fdshow, '') when '' t '*' else '' @ fdshow end?, @ fdorder = case isnull (@fdorder, '' ) when '' TEN 'ELSE' ORDER BY ' @ fdorder end?, @ querystr = case when @obj_id is not null the' ' @ querystr else' (' @ querystr ') a 'end
- If you display the first page, you can use TOP to complete if @PageCurrent = 1 begin? Select @ id1 = CAST (@PAGESIZE AS VARCHAR (20))? EXEC ('SELECT TOP' @ ID1 @ fdshow 'from' @ Querystr @ fdorder)? Returnend
- If it is a table, check if there is a logo or the primary key if @obj_id is not null and objectprproperty (@ obj_id, 'istable') = Cast (@pagesize as varchar (20))? , @ Id2 = cast ((@ pagecurrent-1) * @ PageSize As Varchar (20))
? Select @ fdname = name from syscolumns where id = @ obj_id and status = 0x80? if @@ rowcount = 0 ?? - If there is no identity column in the table, check the table with the primary key? begin? if not exists (SELECT 1 from sysobjects where parent_obj = @ obj_id and xtype = 'pk') ?? goto lbuseTemp? - If there is no primary key in the table, use a temporary table
? Select @ FdName = name from syscolumns where id = @ Obj_ID and colid in (?? select colid from sysindexkeys where @ Obj_ID = id and indid in (??? select indid from sysindexes where @ Obj_ID = id and name in (?? ?? SELECT NAME from sysobjects where xtype = 'pk' and pent_obj = @ obj_id ??)))? @@ rowcount> 1? - Check if the primary key in the table is a composite primary key? Begin ?? SELECT @ strfd = ' ', @ Strjoin =' ', @ Strwhere =' '?? SELECT @ strfd = @ Strfd ', [' Name '] '???, @ Strjoin = @ Strjoin ' and a. [' name '] = b. [' Name '] '???, @ Strwhere = @ Strwhere ' and b. [' Name '] is null '??? from syscolumns where id = @ Obj_id and color in (??? Select Colid from sysindexkeys where @ obj_ID = id and indid in (???? select indid from sysindexes where @ obj_ID = id and name in (????? select name from sysobjects where xtype = 'PK' and parent_obj = @ obj_ID ?? ?)) ?? SELECT @ strfd = substring (@ strjoin = substring (@ strjoin, 5,4000) ???, @ Strwhere = Substring (@ Strwhere, 5,4000 ) ?? goto lbusepk? End? Endendelse? Goto lbuseTemp
/ * - Use the identity column or primary key to process method for a single field - * / lbuseidentity:? EXEC ('SELECT TOP' @ id1 @ fdshow 'from' @ querystr? 'Where' @ fdname 'Not in (SELECT TOP '? @ ID2 ' ' @ fdname ' from ' @ querystr @ fdorder? ') ' @ fdorder?)? Return
/ * - Treatment method for composite primary keys in the table - * / lbusepk :?? EXEC ('SELECT' @ fdshow 'from (SELECT TOP' @ ID1 a. * From? (SELECT TOP 100 PERCENT * FROM ' @ Querystr @ fdorder ') a? Left join (SELECT TOP ' @ ID2 ' @ strfd '? From' @ querystr @ fdorder ') b on' @ Strjoin '? Where' @ strjoin ') A '?)? Return / * - Method for Temporary Table - * / LbuseTemp: "SELECT @FDNAME =' [ID _ ' CAST (NEWID () AS VARCHAR (40)) '] '?, @ ID1 = CAST (@PageSize * (@Pagecurrent-1) as varchar (20))?, @ Id2 = cast (@ PageSize * @ pagecurrent-1 as varchar (20))
Exec ('SELECT' @ fdname '= Id, 0, 1),' @ fdshow 'INTO #TB from' @ querystr @ fdorder '? Select' @ fdshow 'from #tb where' @ FDNAME 'BETWEEN'? @ Id1 'and' @ id2?) Go
2.java2.javaPackage net.db; import java.sql. *; Import javax.sql.datasource; import javax.naming. *;
import javax.naming.InitialContext; import javax.naming.Context; import javax.naming.NamingException;.?? import javax.sql * ;? public class DBPool {protected DataSource ds = null ;? protected Context ic = null ;? protected • connect curp = null ;? protected? Statement stmt = null ;? protected resultset = null ;? public dbpool () {???? try {? ?? IC = new initialContext (); ?????? DS = (Datasource) Ic.lookup ("java: comp / env / jdbc / testdb"); ??? ?????? conn? = Ds.getConnection (); ???} ??? Catch (javax. Naming.namingexception e) {????? system.err.println ("db_link (): E.GetMessage ()); ???} ??? catch (SQLEXCEPTION EX) {????? system .err.println ("SQL.ExecuteQuery:" EX.GetMessage ()); ???}?}? ?? / ** ?? * Execute a SELECT statement ?? * @Param SQL? Query statement ?? * @ RETURN JAVA.SQL.RESULTSET? Return Result Set ?? * @throws java.sql.sqlexception ?? * /? public resultSet ExecuteQuery (STRING SQL) THROWS JAVA.SQL.SQLEXCEPTION? {??? Try {????? // conn = drivermanager.getConnection (strconne, password); ????? //conn.setautocommit (true); ????? STMT = conn .createstatement (); ????? rs = stmt.executeQuery (SQL); ???} catch (sqlexception ex) {?? EX.PrintStackTrace (); ????? throw new sqlexception ("Linksql.executeQuery Err: '" ex.getMessage () " "); ????? //system.rr.println (" Execute SQL statement error: " ex.getMessage ()); ???}? ?? Return Rs ;?} // public resultset ExecuteQuery
???? / ** ??? ** Perform a pager ??? * /? Public resultSet ExecuteQuery3 (String SQL) {?? try {??? {???? {{?? R et etet etet; ? stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ??? rs = stmt.executeQuery (sql); ??} catch (java.sql.SQLException e2) {??? System.out.println ( "ERRORQUERY:" E2.TOSTRING () E2.GetMessage ()); ??} ?? Return RS;?}? / **? * Execute the processing statement of the stored procedure? * @ param sql? * @ Return Boolean Execute a success mark? * /? Public boolean execute (string sql) {? Boolean flag = false; ??? Try {?????? stmt = conn.createstatement (); ??? stmt.execute (SQL); / / Submit the SQL statement of the stored procedure ??? conn.commit (); ?????? flag = true; ???} ??? Catch (SQLEXCEPTION EX) {?????? // system. Err.Println (ex.getMessage ()); ???} finally {? // close (); ??????? Return flag; ??????} // end?}? public void ExecuteUpdate (STRING SQL) THROWS Exception {??? statement stmt = null; ??? // rs = null; ??? Try {??? stmt = conn.createstatement (); ??? stmt.executeUpdate (SQL) ; ???} ??? catch (SQLEXCEPTION EX) {????? throw new exception ("SQL.exe CuteQuery: " ex.getMessage ()); ???} ??? finally ??? {???? try {if (stmt! = null) stmt.close ();} catch (exception e) {} ???}?}
PUBLIC VOID CloseStMt () {???? stmt.close (); ????} ??? Catch (SQLEXCEPTION E) {????? E.PrintStackTrace (); ????} * /?}? public void closeconn () {????? conn.close (); ????} ??? catch (SQLEXCEPTION E) {?? ??? E.PrintStackTrace (); ????} ???? * / ???? close () ;?}
PUBLIC VOID Close ()? {??? try {??? if (rs! = null) rs.close (); ??? f (conn! = null) ??? {???? ing (! Conn.isclosed ()) ???? {????? IF (stmt! = null) stmt.close (); ????? conn.close (); ?????} ???? ????} ??} catch (sqlexception e) {???? E.PrintStackTrace (); ??? //? throw e; ??} ?? finally ?? {??? rs = null;? ?? STMT = NULL; ??? conn = null; ???}?}? // end public closeconn}?
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?
4.Servelt.xml?
Add the following: ??
Debug = "5" reloadable = "true" crossContext = "true">
??
Prefix = "localhost_dbtest_log." suffix = ". TXT" TimeStamp = "true" />
Auth = "Container" type = "javax.sql.datasource" />
Factory
Org.apache.commons.dbcp.basicDataSourceFactory
Maxactive
100
MaxIdle
30
Maxwait
10,000
Username
SA
Password
SA
Driverclassname
com.microsoft.jdbc.sqlserver.sqlserverdriver
URL
JDBC: Microsoft: SQLServer: // localhost: 1433; DatabaseName = Northwind
