jsp数据库操作心得

作者：未知文章来源：网络

在JSP中数据库连接有这二种方式,直接连接的方式 :首先得有JDBC驱动(JDBC for Mysql等),下载到本磁盘解压,在环境变量classpass中加上它的地址,在jdk与服务器的lib中也加上JDBC的jar文件.好了准备工作到此,现在开始以 java直连的方式进行.

eg1:
package bean;
import java.io.*;
import java.sql.*;
import java.util.*;
public class Db{
private String url;
private Connection connection;// 数据库连接
private Statement statement;// SQL表达式
public Db()
    {
        url="jdbc:mysql://localhost:3306/databaseName?user=userNamer&password=password";
        connection=null;
        statement=null;
          }
public void connect()
{
try{Class.forName("com.mysql.jdbc.Driver").newInstance();}catch(Exception e){System.out.print("Sorry01");}
try
     {connection=DriverManager.getConnection(url);
     statement=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);}
catch (SQLException sqle){System.out.print("Sorry02");}

}
public ResultSet executeQuery(String query)
{
ResultSet rs=null;
try
{
rs=statement.executeQuery(query);
}
catch(SQLException sqle)
{System.out.print("Sorry03");}
return rs;
}

public void executeUpdate(String command)
{
try
{
statement.execute(command);
}
catch(SQLException sqle){System.out.print("Sorry04");}
}
public void closeConn()
{
try{
    connection.close();
   }

catch(SQLException sqle)
   {
   System.out.print("Sorry05");
   }
    }
    }

//Sorry01-05处为自定义错误代码...

第二种方式是采用数据库连接池技术

数据库连接池的第一步得配置数据源在TOMCAT 6.0 的Webapps 下的 conf文件夹下的context.xml文件
content.xml文件如下:

<Context path="" docBase="root"   debug="0" reloadable="true" crossContext="true">
    
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    
    
    
    
    
    
    <!-- maxWait: Maximum time to wait for a dB connection to become available

bsp;      in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded. Set to -1 to wait indefinitely.
         -->
    
    
       
<Resource name="jdbc/myweb" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="20" maxWait="80000"
               username="userName" password="passWord" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/

再在Web-inf 下的 web.xml 中加入

<display-name>Welcome to Tomcat</display-name>/
<description>
     Welcome to Tomcat
</description>
<description>MySQL myweb App</description>
<resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/myweb</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
</resource-ref>

再编写数据库连接池类:

eg2:
package bean;
import java.io.*;
import javax.naming.*;
import javax.sql.*;
import java.sql.*;

public class DBuse{
private Connection conn=null;
private Statement stmt=null;
private javax.sql.DataSource ds=null;
private Context ctx=null;
public void connect()
{
    try{
ctx=new InitialContext();
ds=(javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/myweb");
conn=ds.getConnection();
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    }catch(Exception e) {e.printStackTrace();}
}

public ResultSet executeQuery(String query)
{
ResultSet rs=null;
try
{
rs=stmt.executeQuery(query);
}
catch(SQLException sqle)
{System.out.print("Sorry03");}
return rs;
}

public void executeUpdate(String command)
{
try
{
stmt.executeUpdate(command);
}
catch(SQLException sqle){System.out.print("Sorry04");}
}

public void closeConn()
{
try{
    conn.close();
   }
catch(SQLException sqle)
   {

System.out.print("Sorry05");
}
}
}

采用数据库连接池后当数据库频繁操作时,性能和效率就体现出来.
这种两种都是所采用的连接数据库对象Statement都是常规的,还有高级的可以采用预编译prepareStatemen对象来处理,以即采用存储的方式:CallableStatement类型对象处理.以后使用了再加上.