jsp编程心得->数据库操作

        最近用了一个月多点的时间自己完成了一个BBS论坛,采用的MVC设计模式,这一个月里也学到了不少东西,知道了分层设计模式的重要性,要提高数据库连接效率的几种方法,对MYSQL与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文件如下:
<!-- The contents of this file will be loaded for each web application -->
<Context path="" docBase="root"   debug="0" reloadable="true" crossContext="true">
    <!-- Default set of monitored resources -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->
    <!-- Uncomment this to enable Comet connection tacking (provides events
         on session expiration as well as webapp lifecycle) -->
    <!--
    <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
    -->
    <!-- maxActive: Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle
         all of your db connections. Set to 0 for no limit.
         -->
    <!-- maxIdle: Maximum number of idle dB connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->
    <!-- maxWait: Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->
    <!-- username and password: MySQL dB username and password for dB connections  -->
    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->
       <!-- url: The JDBC connection url for connecting to your MySQL dB.
         The autoReconnect=true argument to the url makes sure that the
         mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
         connection.  mysqld by default closes idle connections after 8 hours.
         -->
  <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/databaseName?autoReconnect=true"/>
</Context>

再在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类型对象处理.以后使用了再加上.