JSP连接数据库方法大全

 

一、jsp连接Oracle8/8i/9i数据库（用thin模式）
testoracle.jsp如下：
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String URL="jdbc:oracle:thin:@localhost:1521:orcl";
//orcl为你的数据库的SID
String user="scott";
String password="tiger";
Connection  conn= DriverManager .getConnection (URL,user,password);
Statement  stmt =conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String SQL ="select * from test";
ResultSet rs=stmt.executeQuery(SQL );
while(rs.next()) {%>
您的第一个字段内容为：<%=rs.getString(1)%>
您的第二个字段内容为：<%=rs.getString(2)%>
<%}%>
<%out.print("数据库操作成功，恭喜你");%>
<%rs.close();
stmt.close();
conn.close();
%>
</body>
</html>
二、jsp连接SQL Server7.0/2000数据库
testSQL server.jsp如下：
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%Class.forName("com.microsoft.jdbc. sqlserver. SQL ServerDriver ").newInstance();
String URL="jdbc:microsoft: sqlserver://localhost:1433;DatabaseName=pubs";
//pubs为你的数据库的
String user="sa";
String password="";
Connection  conn= DriverManager .getConnection (URL,user,password);
Statement  stmt=conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String SQL ="select * from test";
ResultSet rs=stmt.executeQuery(SQL );
while(rs.next()) {%>
您的第一个字段内容为：<%=rs.getString(1)%>
您的第二个字段内容为：<%=rs.getString(2)%>
<%}%>
<%out.print("数据库操作成功，恭喜你");%>
<%rs.close();
stmt.close();
conn.close();
%>
</body>
</html>
三、jsp连接DB2数据库
testdb2.jsp如下：
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%Class.forName("com.ibm.db2.jdbc.app.DB2Driver").newInstance();
String URL="jdbc:db2://localhost:5000/sample";
//sample为你的数据库名
String user="admin";
String password="";
Connection  conn= DriverManager .getConnection (URL,user,password);
Statement  stmt=conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String SQL ="select * from test";
ResultSet rs=stmt.executeQuery(SQL );
while(rs.next()) {%>
您的第一个字段内容为：<%=rs.getString(1)%>
您的第二个字段内容为：<%=rs.getString(2)%>
<%}%>
<%out.print("数据库操作成功，恭喜你");%>
<%rs.close();
stmt.close();
conn.close();
%>
</body>
</html>
四、jsp连接Informix数据库
testinformix.jsp如下：
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%Class.forName("com.informix.jdbc.IfxDriver").newInstance();
String URL =
"jdbc:informix-SQL i://123.45.67.89:1533/testDB:INFORMIXSERVER=myserver;
user=testuser;password=testpassword";
//testDB为你的数据库名
Connection  conn= DriverManager .getConnection (URL);
Statement  stmt=conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String SQL ="select * from test";
ResultSet rs=stmt.executeQuery(SQL );
while(rs.next()) {%>
您的第一个字段内容为：<%=rs.getString(1)%>
您的第二个字段内容为：<%=rs.getString(2)%>
<%}%>
<%out.print("数据库操作成功，恭喜你");%>
<%rs.close();
stmt.close();
conn.close();
%>
</body>
</html>
五、jsp连接Sybase数据库
testmysql.jsp如下：
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%Class.forName("com.sybase.jdbc.SybDriver").newInstance();
String URL ="jdbc:sybase:Tds:localhost:5007/tsdata";
//tsdata为你的数据库名
Properties sysProps = System.getProperties();
SysProps.put("user","userid");
SysProps.put("password","user_password");
Connection  conn= DriverManager .getConnection (URL, SysProps);
Statement  stmt=conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String SQL ="select * from test";
ResultSet rs=stmt.executeQuery(SQL );
while(rs.next()) {%>
您的第一个字段内容为：<%=rs.getString(1)%>
您的第二个字段内容为：<%=rs.getString(2)%>
<%}%>
<%out.print("数据库操作成功，恭喜你");%>
<%rs.close();
stmt.close();
conn.close();
%>
</body>
</html>
六、jsp连接Mysql数据库
testmysql.jsp如下：
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%Class.forName("org.gjt.mm.mysql.Driver").newInstance();
String URL ="jdbc:mysql://localhost/example?user=root&password=1234&useUnicode=true&characterEncoding=8859_1"
//example为你的数据库名
Connection  conn= DriverManager .getConnection (URL);
Statement  stmt=conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String SQL ="select * from test";
ResultSet rs=stmt.executeQuery(SQL );
while(rs.next()) {%>
您的第一个字段内容为：<%=rs.getString(1)%>
您的第二个字段内容为：<%=rs.getString(2)%>
<%}%>
<%out.print("数据库操作成功，恭喜你");%>
<%rs.close();
stmt.close();
conn.close();
%>
</body>
</html>
七、jsp连接PostgreSQL 数据库
testmysql.jsp如下：
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%Class.forName("org.postgreSQL .Driver").newInstance();
String URL ="jdbc:postgreSQL ://localhost/soft"
//soft为你的数据库名
String user="myuser";
String password="mypassword";
Connection  conn= DriverManager .getConnection (URL,user,password);
Statement  stmt=conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String SQL ="select * from test";
ResultSet rs=stmt.executeQuery(SQL );
while(rs.next()) {%>
您的第一个字段内容为：<%=rs.getString(1)%>
您的第二个字段内容为：<%=rs.getString(2)%>
<%}%>
<%out.print("数据库操作成功，恭喜你");%>
<%rs.close();
stmt.close();
conn.close();
%>
</body>
</html>  

7.3 Jsp连接mysql调用数据源方法

当jsp对于mysql数据库进行数据开发时，需要一些公用的方法，下面是jsp 调用mysql数据源的，它对mysql数据库进行查询及插入等最基本的处理。注意:你一定将mysql的jdbc的驱动程序放到服务器的类路径里。

7.3.1对mysql数据库最基本的DB操作

下面是操作mysql数据库的方法。源程序如下：

 /**

 *这是对mysql数据库最基本的DB操作

 */

package example7;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public class IpConn {

       // private static String

       // dbdriver="sun.jdbc.odbc.JDBCodbcDriver";//如果要通过odbc连接，只要把这个注释去掉

       // private static String connstr="jdbc:odbc:pubs";

       private static String dbdriver = " org.gjt.mm.mysql.Driver ";

 

       private static String connstr = "jdbc:mysql://localhost/example?user=root&password=1234&useUnicode=true&characterEncoding=8859_1";

 

       // example为你的mysql据库名，就是上面我们用工具建立的

       private static Connection conn = null;

 

       ResultSet rs = null;

 

       private static Statement stms;

 

       public IpConn() {

              try {

                     Class.forName(dbdriver).newInstance();

                     conn = DriverManager.getConnection(connstr);

                     stms = conn.createStatement();

              } catch (java.lang.ClassNotFoundException e) {

                     System.out.println("faq():" + e.toString() + e.getMessage());

              } catch (Exception e) {

                     System.out.println("faq():" + e.toString() + e.getMessage());

              }

       }

 

       /**

        * 打开数据库连接

        */

       public static Connection getConnection() throws SQLException {

              Connection conn1 = null;

              try {

                     Class.forName(dbdriver);

                     conn1 = DriverManager.getConnection(connstr, "sa", "sa");

                     stms = conn1.createStatement();

              } catch (Exception e) {

                     System.err.println("DBconn (): " + e.getMessage());

              }

              return conn1;

       }

 

       /**

        * *执行可分页的查询操作

        */

       public ResultSet executeQuery3(String SQL) {

              try {

                     stms = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

                                   ResultSet.CONCUR_UPDATABLE);

                     rs = stms.executeQuery(SQL);

              } catch (Exception e2) {

                     System.out.println("errorQuery:" + e2.toString() + e2.getMessage());

              }

              return rs;

       }

 

       /**

        * 执行Select查询语句,可用于执行一般的SQL 查询操作

        *

        * @param SQL

        *            Select查询语句

        * @return ResultSet 查询结果集

        */

       public ResultSet executeQuery(String SQL) throws SQLException {

              rs = null;

              try {

                     // 取得连接对象

                     if (conn == null)

                            conn = getConnection();

                     stms = conn.createStatement();// 取得执行对象

                     rs = stms.executeQuery(SQL); // 取得结果集

              } catch (Exception ex) {

                     System.err.println("执行SQL 语句出错：" + ex.getMessage());

              }

              return rs;

       }

 

       /**

        * 执行更新操作, 执行一般的更新数据库操作

        *

        * @param SQL

        *            Select更新语句

        */

       public void updateBatch(String SQL) throws SQLException {

              try {

                     // 取得连接对象

                     if (conn == null)

                            conn = getConnection();

                     // 设置事务处理

                     conn.setAutoCommit(false);

                     String procSQL = "begin /n" + SQL + " /nend;";

                     CallableStatement cstmt = conn.prepareCall(procSQL);

                     cstmt.execute();

                     conn.commit();

                     cstmt.close();

              } catch (SQLException ex) {

                     System.err.println("执行SQL 语句出错: " + ex.getMessage());

                     try {

                            // 事务失败，回滚

                            conn.rollback();

                     } catch (Exception e) {

                     }

                     throw ex;

              }

       }// end public

 

       /**

        * 执行Insert,Update语句

        *

        * @param SQL

        *            Insert,Update语句

        * @return null SQLException

        */

       public void executeUpdate(String SQL) throws SQLException {

              try {

                     // 取得连接对象

                     if (conn == null)

                            conn = getConnection();

                     // 设置事务处理

                     conn.setAutoCommit(false);

                     stms = conn.createStatement();

                     stms.executeUpdate(SQL);

                     // stmt.close();

                     conn.commit();

                     // conn.close();

              } catch (SQLException ex) {

                     System.err.println("执行SQL 语句出错: " + ex.getMessage());

                     try {

                            // 事务失败，回滚

                            conn.rollback();

                     } catch (SQLException e) {

                     }

                     throw ex;

 

              }

       }// end public executeUpdate

 

       /**

        * 提交批SQL 语句

        */

       public boolean executeQuery(String[] SQL) throws Exception {

              boolean flag = false;

              try {

                     conn.setAutoCommit(false);

                     stms = conn.createStatement();

                     for (int k = 0; k < SQL.length; k++) {

                            if (SQL[k] != null)

                                   stms.addBatch(SQL[k]);

                     }

                     stms.executeBatch();// 提交批SQL 语句

                     conn.commit();

                     flag = true;

                     return flag;

              } catch (Exception ex) {

                     try {

                            conn.rollback();

                     } catch (Exception e) {

                     }

                     System.out.println("[LinkSQL .executeQuery(String[])] : "

                                   + ex.getMessage());

                     throw new Exception("执行SQL 语句出错: " + ex.getMessage());

              }

       }

 

       /**

        * 转换函数 转换为GBK码

        *

        * @param value

        *            被转换的字符串

        * @return String 返回转换后的字符串

        */

       public static String convert(String value) {

              try {

                     String s = new String(value.getBytes("ISO8859_1"), "GBK");

                     return s;

              } catch (Exception e) {

                     String s1 = "";

                     return s1;

              }

       }

 

       /**

        * 转换函数 转换为ISO8859码

        *

        * @param value

        *            被转换的字符串

        * @return String 返回转换后的字符串

        */

 

       public static String reconvert(String value) {

              try {

                     String s = new String(value.getBytes("GBK"), "ISO8859_1");

                     return s;

              } catch (Exception e) {

                     String s1 = "";

                     return s1;

              }

       }

 

       /**

        * 释放系统资源

        *

        * @return null

        */

       public void close() throws Exception {

              try {

                     if (rs != null)

                            rs.close();

                     if (conn != null) {

                            if (!conn.isClosed()) {

                                   if (stms != null)

                                          stms.close();

                                   conn.close();

                            }

 

                     }

              } catch (Exception e) {

                     e.printStackTrace();

                     throw e;

              } finally {

                     rs = null;

                     stms = null;

                     conn = null;

              }

       } // end public closeConn

 

       /**

        * 析构函数 释放系统资源

        *

        * @return null

        */

       public void close_1() {

              try {

                     if (conn != null) {

                            conn.close();

                     }

              } catch (Exception e) {

              }

       }

/**

   * 将Date类型日期转化成String类型"任意"格式

   * java.sql.Date,java.sql.Timestamp类型是java.util.Date类型的子类

   * @param date Date

   * @param format String

   *               "2003-01-01"格式

   *               "yyyy年M月d日"

   *               "yyyy-MM-dd HH:mm:ss"格式

   * @return String

   */

  public static String dateToString(java.util.Date date,String format) {

 

      if (date==null || format==null) {

          return null;

      }

 

      SimpleDateFormat sdf = new  SimpleDateFormat(format);

      String str = sdf.format(date);

      return str;

  }

 

  /**

   * 将String类型日期转化成java.utl.Date类型"2003-01-01"格式

   * @param str String  要格式化的字符串

   * @param format String

   * @return Date

   */

  public static java.util.Date stringToUtilDate(String str,String format) {

 

      if (str==null||format==null) {

          return null;

      }

 

      SimpleDateFormat sdf = new  SimpleDateFormat(format);

 

      java.util.Date date = null;

      try

      {

          date = sdf.parse(str);

      }

      catch(Exception e)

      {

      }

      return date;

  }

}// end function