求一个好的连接数据库的Bean,如果好一共赠送200分。 求一个好的连接数据库的Bean,如果好一共赠送200分。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 楼主何谓好、不好?我认为可以使用连接池来简化链接,或者把链接的代码封到一个类似ConnectionFactory的类里啊。===========================================================package pkg;import java.sql.*;import javax.sql.*;import javax.naming.InitialContext;import javax.naming.NamingException;public class ConnectionFactory { private static DataSource dataSource = null; private static ConnectionFactory ref = new ConnectionFactory(); private String jndiName = "java:comp/env/jdbc/webQuery"; private ConnectionFactory() { loadDBPoolDriver(); //loadDBNormalDriver(); } /** * 使用DataSource * */ private void loadDBPoolDriver(){ try{ InitialContext ic = new InitialContext(); dataSource = (DataSource) ic.lookup(jndiName); }catch(NamingException ne){ ne.printStackTrace(); } } /** * 一般注册 * */ private void loadDBNormalDriver(){ try{ Class.forName(DatabaseNames.DBDRIVER); }catch(ClassNotFoundException cnfe){ cnfe.printStackTrace(); cnfe.printStackTrace(); } } /** * 连接方式选择(Pool,Normal) * * @param isPoolConnect boolean * @throws SQLException * @return Connection */ public static Connection getConnection(boolean isPoolConnect) throws SQLException{ if(isPoolConnect){ return dataSource.getConnection(); } else{ return DriverManager.getConnection(DatabaseNames.DBURL, DatabaseNames.DBUSER, DatabaseNames.DBPWD); } }//................................} 两个类package com.webrm.database;public interface DBResource { String url="jdbc:oracle:thin:@localhost:1521:app97"; String username="develop"; String password="oracle817"; String driver="oracle.jdbc.driver.OracleDriver"; String myurl="jdbc:mysql://127.0.0.1:3306/netoa"; String myusername="root"; String mypassword=""; String mydriver="org.gjt.mm.mysql.Driver";} package com.webrm.database;import java.sql.DriverManager; //java基础类包中的类 import java.sql.SQLException; import java.sql.Connection; import java.sql.Statement; import java.sql.ResultSet; import java.sql.PreparedStatement;import java.util.List;import java.sql.CallableStatement;public class OpenDB implements DBResource{private static Connection conn=null;private Statement st=null;private ResultSet rs=null;private PreparedStatement ps=null;private CallableStatement cs=null;private String type="";// String url="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=newnetoa";// String username="sa";// String password="123456";// String driver="com.microsoft.jdbc.sqlserver.SQLServerDriver";//public OpenDB()//{// try{// Class.forName(driver).newInstance();// }catch (Exception ex){// ex.printStackTrace();// }//}public OpenDB(String dbtype){ this.type=dbtype; try{ if(type.equals("sqlserver")){ Class.forName(driver).newInstance(); }else if(type.equals("mysql")){ Class.forName(mydriver).newInstance(); }else if(type.equals("oracle")){ } }catch (Exception ex){ ex.printStackTrace(); }}//负责在驱动的基础上建立连接public void getConn(){ try{ if(type.equals("sqlserver")) conn = DriverManager.getConnection(url, username, password); else if(type.equals("mysql")) conn = DriverManager.getConnection(myurl, myusername, mypassword); else if(type.equals("oracle")){ } //conn.setAutoCommit(false); //这样一设置就会由我人工判断什么时候该提交 }catch (SQLException ex) { ex.printStackTrace(); }}//建立状态通道public void getStatement(){ try{ if(conn==null||conn.isClosed()) getConn(); st = conn.createStatement(); }catch (SQLException ex){ ex.printStackTrace(); }}//执行查询public ResultSet DoQuery(String sql){ try { getStatement(); rs = st.executeQuery(sql); }catch (SQLException ex){ ex.printStackTrace(); } return rs;}//执行新增(其实也可以执行修改和删除)public boolean DoInsert(String sql){ boolean isCorrect=false; try{ getStatement(); boolean is=st.execute(sql);//它的返回值是boolean,如果sql是select查询语句则返回true表示,如果sql是增删改则返回false System.out.println(is);// rs=st.getResultSet();// while(rs.next())// {// System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"----"+rs.getInt(3)+"----"+rs.getString(4));// } isCorrect=true; conn.commit(); }catch(SQLException e){ e.printStackTrace(); }finally{ close(); return isCorrect; }} //执行修改和删除(也可以执行新增) public boolean DoUpdateOrDelete(String sql) { boolean isCorrect = false; try { getStatement(); int number=st.executeUpdate(sql); System.out.println(number); isCorrect = true; } catch (SQLException e) { e.printStackTrace(); } finally { close(); return isCorrect; } } //通过传递的sql得到总记录数 public int getCount(String sql) { ResultSet rs=DoQuery(sql); int count=0; try{ if(rs.next()) { count = rs.getInt(1); } }catch (SQLException ex){ ex.printStackTrace(); }finally{ close(); return count; } }//***************************************************preparedstatement********************************* //通过预状态通道来操作数据库(此方法并不是通用的) public void getPreparedStatement() { String sql="select * from dept where deptname=? and deptnum=?"; try { if(conn==null||conn.isClosed()) this.getConn(); ps = conn.prepareStatement(sql); ps.setString(1,"行政部"); ps.setString(2,"22"); rs=ps.executeQuery(); while(rs.next()) { System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"----"+rs.getInt(3)+"---"+rs.getString(4)); } }catch(SQLException ex){ ex.printStackTrace(); } close(); } //通过预备状态通道执行查询 public ResultSet getQueryPS(String sql,String[] str) { try{ if(conn==null||conn.isClosed()) { this.getConn(); } ps=conn.prepareStatement(sql); for(int i=0;i<str.length;i++) { ps.setString(i+1,str[i]); } rs=ps.executeQuery(); }catch(Exception e){ e.printStackTrace(); } return rs; } //通过预备状态通道执行新增、修改、删除 public boolean getSaveOrUpdate(String sql,String[] str) { boolean isCorrect=false; try{ if(conn==null||conn.isClosed()) { this.getConn(); } ps=conn.prepareStatement(sql); for(int i=0;i<str.length;i++) { ps.setString(i+1,str[i]); } ps.executeUpdate();//执行预通道当中的完整的这个sql isCorrect=true; }catch(Exception e){ e.printStackTrace(); } close(); return isCorrect; } //成批处理新增、修改、删除等这些sql public boolean DoBatch(List list) { boolean isCorrect=false; try{ getStatement(); for(int i=0;i<list.size();i++) { st.addBatch(list.get(i).toString()); //把list容器中的sql循环往st中放 } st.executeBatch(); // conn.commit(); //手动提交 isCorrect=true; }catch(Exception e){ e.printStackTrace();// try{// conn.rollback();// }catch (SQLException ex){// ex.printStackTrace();// } } close(); return isCorrect; } //怎样调用存储过程 public void callprocedure(){ try{ if(conn==null||conn.isClosed()) { getConn(); } //cs=conn.prepareCall("{call insertDept(?,?,?,?)}"); cs=conn.prepareCall("{call updateDept(?,?,?,?)}"); cs.setString(1,"15"); cs.setString(2,"安保部8"); cs.setString(3,"28"); cs.setString(4,"安全保卫部8"); //cs.executeUpdate(); cs.execute(); }catch (SQLException ex){ ex.printStackTrace(); } } public ResultSet callselectprocedure(){ try{ if(conn==null||conn.isClosed()) { getConn(); } //cs=conn.prepareCall("{call insertDept(?,?,?,?)}"); cs=conn.prepareCall("{call selectDept()}"); //cs.executeUpdate(); //cs.execute(); rs=cs.executeQuery();// while(rs.next())// {// System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"----"+rs.getInt(3)+"----"+rs.getString(4));// } }catch (SQLException ex){ ex.printStackTrace(); }finally{ return rs; } } public String getMaxId(String tname,String cname) { String maxid=""; try{ if(conn == null||conn.isClosed()) { getConn(); } cs=conn.prepareCall("{call maxId(?,?,?)}"); cs.setString(1,tname); cs.setString(2,cname); cs.registerOutParameter(3,java.sql.Types.VARCHAR); cs.executeUpdate(); maxid=cs.getString(3); }catch (SQLException ex){ ex.printStackTrace(); }finally{ close(); return maxid; } } //通用的调用(实现增删改)存储过程的方法 public boolean callProcedure(String sql,String str[]) { boolean isCorrect=false; try{ if(conn == null || conn.isClosed()){ getConn(); } cs=conn.prepareCall(sql); for(int i=0;i<str.length;i++) { cs.setString(i+1,str[i]); } cs.execute(); isCorrect=true; }catch(SQLException ex){ ex.printStackTrace(); }finally{ close(); return isCorrect; } }public void close(){ try{ if(rs!=null) rs.close(); if(st!=null) st.close(); if(ps!=null) ps.close(); if(cs!=null) cs.close(); if(conn!=null) conn.close(); }catch(Exception e){ e.printStackTrace(); }}public static void main(String[] args) throws Exception{ try{//块(block) //1、导入连接数据库的相应驱动程序 //Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); Class.forName("org.gjt.mm.mysql.Driver").newInstance(); //2、在驱动的基础上建立到数据库的连接 Connection conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/netoa","root",""); //3、在连接的基础上建立状态通道 Statement st=conn.createStatement(); //4、在状态通道的基础上可以执行相应的sql ResultSet rs=st.executeQuery("select * from dept"); while(rs.next()) { System.out.println(rs.getInt(1)+"----"+rs.getString(2)+"----"+rs.getInt(3)+"----"+rs.getString(4)); } }catch (ClassNotFoundException ex) { ex.printStackTrace(); }catch (IllegalAccessException ex) { ex.printStackTrace(); }catch (InstantiationException ex) { ex.printStackTrace(); }catch(SQLException e1){ e1.printStackTrace(); } }} package dbaccess; import Java.sql.*; import Java.util.*; import Java.io.*; public class DBConnBean implements Serializable{ private String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; private String DBHost = "127.0.0.1"; private String DBName = "demo"; private String conp = "jdbc:odbc:db_demo"; private String username = ""; private String password = ""; private boolean xdebug = true; public Connection con = null; public String sql = null; Statement stmt = null; public ResultSet result = null; private int affectedRows = 0; public DBConnBean() { xdebug = true; con = null; sql = null; } public Connection Connect() throws Exception { String msg = null; try { Class.forName(DBDriver).newInstance(); } catch(Exception e) { msg = "加载数据库驱动失败"; if (xdebug) msg += "(驱动´"+DBDriver+"´)"; throw new Exception(msg); } try { String conStr = conp; con = DriverManager.getConnection(conStr,username,password); } catch(SQLException e) { msg = "!!数据库连接失败"; if (xdebug) { msg += "(错误信息=´" + e.getMessage()+"´ SQL状态值=´" + e.getSQLState()+"´ 错误代码=´" + e.getErrorCode()+"´)"; } throw new Exception(msg); } return con; } protected void finalize() throws Throwable { super.finalize(); if (stmt != null) stmt.close(); if (result != null) result.close(); } //最近一次对数据库查询受影响的行数 public int getAffectedRows() { return affectedRows; } public Connection getCon() { return con; } public String getConp() { return conp; } public String getDBDriver() { return DBDriver; } public String getDBName() { return DBName; } public boolean getDebug() { return xdebug; } public String getPassword() { return password; } public ResultSet getResult() { return result; } public String getSql() { return sql; } public String getUsername() { return username; } public void over() throws Throwable { finalize(); } public ResultSet query() throws Exception { result = null; affectedRows = 0; if (con == null) Connect(); if (stmt == null) stmt = con.createStatement(); if (sql.substring(0,6).equalsIgnoreCase("select")) { result = stmt.executeQuery(sql); } else { affectedRows = stmt.executeUpdate(sql); } return result; } public ResultSet query(String s) throws Exception { sql = s; return query(); } public void setDBDriver(String s) { DBDriver = s; } public void setDebug(boolean b) { xdebug = b; } public void setgetConp(String s) { conp = s; } public void setgetDBName(String s) { DBName = s; } public void setgetUsername(String s) { username = s; } public void setPassword(String s) { password = s; } public void setSql(String s) { sql = s; } } ② DBQueryBean.Java的源代码如下所示: package dbaccess; import Java.sql.*; import Java.util.*; import Java.io.*; import Java.lang.reflect.*; public class DBQueryBean implements Serializable { DBConnBean dbc; String sql = null; int rowcount = 0; int colcount = 0; // int limitcount = 0; Vector result = null; public String _WATCH = ""; public DBQueryBean() { dbc = new DBConnBean(); try { dbc.Connect(); } catch(Exception e) { handleException(e); } } protected void finalize() throws Throwable { super.finalize(); if (dbc != null) dbc.over(); if (result != null) result.removeAllElements(); } public String get(int row, int col) { if (result==null || row >= result.size()) return null; String r[] = (String[])result.elementAt(row); if (col >= Java.lang.reflect.Array.getLength(r)) return null; return r[col]; } public int getAffRows() { return dbc.getAffectedRows(); } public int getColumncount() { return colcount; } public String[] getRow(int row) { if (result==null || row >= result.size()) return null; return (String [])result.elementAt(row); /*String ret[] = new String[colcount]; Vector r = (Vector)result.elementAt(row); for (int i=0; i<colcount; i++) ret[i] = (String)r.elementAt(i); return ret;*/ } public int getRowcount() { return rowcount; } public void handleException(Exception e) { _WATCH = e.getMessage(); } public void init() { rowcount = 0; colcount = 0; // limitcount = 0; result = null; } public void over() throws Throwable { finalize(); } public int query(String sql) { result = new Vector(); int ret = 0; try { ResultSet rs = dbc.query(sql); if (rs == null) { ret = dbc.getAffectedRows(); } else { ResultSetMetaData rm = rs.getMetaData(); colcount = rm.getColumnCount(); while (rs.next()) { String row[] = new String[colcount]; for (int i=0; i<colcount; i++) row[i] = rs.getString(i+1); result.addElement(row); rowcount++; } rs.close(); // to release the resource. ret = result.size(); } } catch(Exception e) { handleException(e); return -1; } return ret; } } 这个好么? 连接数据库的类还是越简单越好import java.io.FileNotFoundException;import java.io.PrintStream;import java.sql.*;import java.util.*;import javax.naming.Context;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpSession;import javax.sql.DataSource;public class Tdb{ private Connection con=null;//连接 private Statement st=null;//执行SQL语句的对象 private ResultSet rs=null;//记录集对象 public Connection getConnection() { try { Context initCtx = new javax.naming.InitialContext(); Context envCtx = (Context)initCtx.lookup("java:comp/env"); DataSource ds = (DataSource)envCtx.lookup("tel"); con=ds.getConnection(); //测试连接 if(con!=null) { System.out.println("ok"); } return con; } catch(Exception e) { e.printStackTrace(); return null; } } public ResultSet executeQuery(String sql){ try{ st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); rs = st.executeQuery(sql); System.out.println("11"); } catch(SQLException ex){ ex.printStackTrace(); } return rs; } public int executeUpdate(String sql){ int result = 0; try{ st = con.createStatement(); result = st.executeUpdate(sql); } catch(SQLException ex){ System.err.println(ex.getMessage()); } return result; } public void close(){ if (con!=null){ try{ con.close(); con=null; } catch(SQLException ex){ System.err.println(ex.getMessage()); } } }} 我写的bean//mysqltest.javapackage news;import java.sql.*;public final class mysqltest{ //protected static mysqltest inst = new mysqltest(); private static final String _url = "proxool.xml-jlpd"; //proxool SourceName nickName Connection conn = null; Statement stmt = null; PreparedStatement pstmt = null; ResultSet rs = null; //getConn public Connection getConnection() throws SQLException { try { if(conn == null || conn.isClosed()) { conn = DriverManager.getConnection(_url); } } catch (SQLException e) { System.out.println(e); } return conn; } //mysqltest public mysqltest() { //inst.getConnection(); } //pstmt query public PreparedStatement execute(String sql) { try { pstmt = getConnection().prepareStatement(sql); } catch(SQLException ex) { System.out.println(ex); //System.err.println("sql_data.pstmt:"+ex.getMessage()); } return pstmt; } //query public ResultSet executeQuery(String sql) { try { stmt = getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(sql); } catch(SQLException ex) { //System.err.println("sql_data.executeQuery:"+ex.getMessage()); System.out.println(ex); } return rs; } //insert public void executeInsert(String sql) { try { stmt = getConnection().createStatement(); stmt.executeUpdate(sql); stmt.close(); stmt = null; } catch(SQLException ex) { System.out.println(ex); } finally { try { if (stmt != null) { stmt.close(); stmt = null; } } catch(SQLException e) { System.out.println(e); } } } //update public void executeUpdate(String sql) { try { stmt = getConnection().createStatement(); stmt.executeUpdate(sql); stmt.close(); stmt = null; } catch(SQLException ex) { System.out.println(ex); } finally { try { if (stmt != null) { stmt.close(); stmt = null; } } catch(SQLException e) { System.out.println(e); } } } //delete public void executeDelete(String sql) { try { stmt = getConnection().createStatement(); stmt.executeUpdate(sql); stmt.close(); stmt = null; } catch(SQLException ex) { System.out.println(ex); } finally { try { if (stmt != null) { stmt.close(); stmt = null; } } catch(SQLException e) { System.out.println(e); } } } //close public void closeDB() { try { if(rs != null) { rs.close(); rs = null; } if(pstmt != null) { pstmt.close(); pstmt = null; } if(stmt != null) { stmt.close(); stmt = null; } if(conn != null) { conn.close(); conn = null; } } catch(SQLException e) { System.out.println(e); } finally { try { if(rs != null) { rs.close(); rs = null; } if(pstmt != null) { pstmt.close(); pstmt = null; } if(stmt != null) { stmt.close(); stmt = null; } if(conn != null) { conn.close(); conn = null; } } catch(SQLException e) { System.out.println(e); } } }}楼上的给改改吧。gongyali2005(JAVA 民工) suncheng_hong(亮) 帮忙每页都关闭了,一直按刷新还是断开。 部署SSH项目到Weblogic的怪异问题 急!急!jsp问题,jsp高手麻烦帮看看,谢谢; web项目中文件下载问题 获取不到SESSION,麻烦给看看,急~~ 镜子网站怎么实现的,是CSS做的吗?例如 度百 如何在JSP页面中获得当前登录用户名和用户id? 问一个关于多行文本的简单的问题 怎样获取记录集的最大行? ^_^关于flash mx 2004和java server的通信问题(讨论)。 struts之ActionServlet深入探讨 jsp整型判断大小 求分页的代码 最好是用Hibernate+jsp来实现
import javax.sql.*;
import javax.naming.InitialContext;
import javax.naming.NamingException;public class ConnectionFactory {
private static DataSource dataSource = null;
private static ConnectionFactory ref = new ConnectionFactory();
private String jndiName = "java:comp/env/jdbc/webQuery"; private ConnectionFactory() {
loadDBPoolDriver();
//loadDBNormalDriver();
} /**
* 使用DataSource
*
*/
private void loadDBPoolDriver(){
try{
InitialContext ic = new InitialContext();
dataSource = (DataSource) ic.lookup(jndiName);
}catch(NamingException ne){
ne.printStackTrace();
}
} /**
* 一般注册
*
*/
private void loadDBNormalDriver(){
try{
Class.forName(DatabaseNames.DBDRIVER);
}catch(ClassNotFoundException cnfe){
cnfe.printStackTrace();
cnfe.printStackTrace();
}
} /**
* 连接方式选择(Pool,Normal)
*
* @param isPoolConnect boolean
* @throws SQLException
* @return Connection
*/
public static Connection getConnection(boolean isPoolConnect) throws SQLException{
if(isPoolConnect){
return dataSource.getConnection();
}
else{
return DriverManager.getConnection(DatabaseNames.DBURL,
DatabaseNames.DBUSER,
DatabaseNames.DBPWD);
}
}
//................................
}
package com.webrm.database;public interface DBResource {
String url="jdbc:oracle:thin:@localhost:1521:app97";
String username="develop";
String password="oracle817";
String driver="oracle.jdbc.driver.OracleDriver"; String myurl="jdbc:mysql://127.0.0.1:3306/netoa";
String myusername="root";
String mypassword="";
String mydriver="org.gjt.mm.mysql.Driver";
}
import java.sql.DriverManager; //java基础类包中的类
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.List;
import java.sql.CallableStatement;public class OpenDB implements DBResource{
private static Connection conn=null;
private Statement st=null;
private ResultSet rs=null;private PreparedStatement ps=null;
private CallableStatement cs=null;
private String type="";// String url="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=newnetoa";
// String username="sa";
// String password="123456";
// String driver="com.microsoft.jdbc.sqlserver.SQLServerDriver";//public OpenDB()
//{
// try{
// Class.forName(driver).newInstance();
// }catch (Exception ex){
// ex.printStackTrace();
// }
//}public OpenDB(String dbtype)
{
this.type=dbtype;
try{
if(type.equals("sqlserver")){
Class.forName(driver).newInstance();
}else if(type.equals("mysql")){
Class.forName(mydriver).newInstance();
}else if(type.equals("oracle")){ }
}catch (Exception ex){
ex.printStackTrace();
}
}
//负责在驱动的基础上建立连接
public void getConn()
{
try{
if(type.equals("sqlserver"))
conn = DriverManager.getConnection(url, username, password);
else if(type.equals("mysql"))
conn = DriverManager.getConnection(myurl, myusername, mypassword);
else if(type.equals("oracle")){ } //conn.setAutoCommit(false); //这样一设置就会由我人工判断什么时候该提交
}catch (SQLException ex) {
ex.printStackTrace();
}
}
//建立状态通道
public void getStatement(){
try{
if(conn==null||conn.isClosed())
getConn();
st = conn.createStatement();
}catch (SQLException ex){
ex.printStackTrace();
}
}
//执行查询
public ResultSet DoQuery(String sql)
{
try {
getStatement();
rs = st.executeQuery(sql);
}catch (SQLException ex){
ex.printStackTrace();
}
return rs;
}
//执行新增(其实也可以执行修改和删除)
public boolean DoInsert(String sql)
{
boolean isCorrect=false;
try{
getStatement();
boolean is=st.execute(sql);//它的返回值是boolean,如果sql是select查询语句则返回true表示,如果sql是增删改则返回false
System.out.println(is);
// rs=st.getResultSet();
// while(rs.next())
// {
// System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"----"+rs.getInt(3)+"----"+rs.getString(4));
// }
isCorrect=true;
conn.commit();
}catch(SQLException e){
e.printStackTrace();
}finally{
close();
return isCorrect;
}}
//执行修改和删除(也可以执行新增)
public boolean DoUpdateOrDelete(String sql)
{
boolean isCorrect = false;
try {
getStatement();
int number=st.executeUpdate(sql);
System.out.println(number);
isCorrect = true;
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
close();
return isCorrect;
}
}
//通过传递的sql得到总记录数
public int getCount(String sql)
{
ResultSet rs=DoQuery(sql);
int count=0;
try{
if(rs.next())
{
count = rs.getInt(1);
}
}catch (SQLException ex){
ex.printStackTrace();
}finally{
close();
return count;
} }
//***************************************************preparedstatement*********************************
//通过预状态通道来操作数据库(此方法并不是通用的)
public void getPreparedStatement()
{
String sql="select * from dept where deptname=? and deptnum=?";
try {
if(conn==null||conn.isClosed())
this.getConn();
ps = conn.prepareStatement(sql);
ps.setString(1,"行政部");
ps.setString(2,"22");
rs=ps.executeQuery();
while(rs.next())
{
System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"----"+rs.getInt(3)+"---"+rs.getString(4));
}
}catch(SQLException ex){
ex.printStackTrace();
}
close();
}
//通过预备状态通道执行查询
public ResultSet getQueryPS(String sql,String[] str)
{
try{
if(conn==null||conn.isClosed())
{
this.getConn();
}
ps=conn.prepareStatement(sql);
for(int i=0;i<str.length;i++)
{
ps.setString(i+1,str[i]);
}
rs=ps.executeQuery(); }catch(Exception e){
e.printStackTrace();
}
return rs;
}
public boolean getSaveOrUpdate(String sql,String[] str)
{
boolean isCorrect=false;
try{
if(conn==null||conn.isClosed())
{
this.getConn();
}
ps=conn.prepareStatement(sql);
for(int i=0;i<str.length;i++)
{
ps.setString(i+1,str[i]);
}
ps.executeUpdate();//执行预通道当中的完整的这个sql
isCorrect=true;
}catch(Exception e){
e.printStackTrace();
}
close();
return isCorrect;
}
//成批处理新增、修改、删除等这些sql
public boolean DoBatch(List list)
{
boolean isCorrect=false;
try{
getStatement();
for(int i=0;i<list.size();i++)
{
st.addBatch(list.get(i).toString()); //把list容器中的sql循环往st中放
}
st.executeBatch();
// conn.commit(); //手动提交
isCorrect=true;
}catch(Exception e){
e.printStackTrace();
// try{
// conn.rollback();
// }catch (SQLException ex){
// ex.printStackTrace();
// }
}
close();
return isCorrect;
}
//怎样调用存储过程
public void callprocedure(){
try{
if(conn==null||conn.isClosed())
{
getConn();
}
//cs=conn.prepareCall("{call insertDept(?,?,?,?)}");
cs=conn.prepareCall("{call updateDept(?,?,?,?)}");
cs.setString(1,"15");
cs.setString(2,"安保部8");
cs.setString(3,"28");
cs.setString(4,"安全保卫部8");
//cs.executeUpdate();
cs.execute();
}catch (SQLException ex){
ex.printStackTrace();
}
}
public ResultSet callselectprocedure(){
try{
if(conn==null||conn.isClosed())
{
getConn();
}
//cs=conn.prepareCall("{call insertDept(?,?,?,?)}");
cs=conn.prepareCall("{call selectDept()}");
//cs.executeUpdate();
//cs.execute();
rs=cs.executeQuery();
// while(rs.next())
// {
// System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"----"+rs.getInt(3)+"----"+rs.getString(4));
// }
}catch (SQLException ex){
ex.printStackTrace();
}finally{
return rs;
}
}
public String getMaxId(String tname,String cname)
{
String maxid="";
try{
if(conn == null||conn.isClosed())
{
getConn();
}
cs=conn.prepareCall("{call maxId(?,?,?)}");
cs.setString(1,tname);
cs.setString(2,cname);
cs.registerOutParameter(3,java.sql.Types.VARCHAR);
cs.executeUpdate();
maxid=cs.getString(3); }catch (SQLException ex){
ex.printStackTrace();
}finally{
close();
return maxid;
}
}
//通用的调用(实现增删改)存储过程的方法
public boolean callProcedure(String sql,String str[])
{
boolean isCorrect=false;
try{
if(conn == null || conn.isClosed()){
getConn();
}
cs=conn.prepareCall(sql);
for(int i=0;i<str.length;i++)
{
cs.setString(i+1,str[i]);
}
cs.execute();
isCorrect=true;
}catch(SQLException ex){
ex.printStackTrace();
}finally{
close();
return isCorrect;
}
}public void close()
{
try{
if(rs!=null)
rs.close();
if(st!=null)
st.close();
if(ps!=null)
ps.close();
if(cs!=null)
cs.close();
if(conn!=null)
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
try{//块(block)
//1、导入连接数据库的相应驱动程序
//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
//2、在驱动的基础上建立到数据库的连接
Connection conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/netoa","root","");
//3、在连接的基础上建立状态通道
Statement st=conn.createStatement();
//4、在状态通道的基础上可以执行相应的sql
ResultSet rs=st.executeQuery("select * from dept");
while(rs.next())
{
System.out.println(rs.getInt(1)+"----"+rs.getString(2)+"----"+rs.getInt(3)+"----"+rs.getString(4));
}
}catch (ClassNotFoundException ex) {
ex.printStackTrace();
}catch (IllegalAccessException ex) {
ex.printStackTrace();
}catch (InstantiationException ex) {
ex.printStackTrace();
}catch(SQLException e1){
e1.printStackTrace();
}
}}
import Java.sql.*;
import Java.util.*;
import Java.io.*;
public class DBConnBean
implements Serializable{ private String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
private String DBHost = "127.0.0.1";
private String DBName = "demo";
private String conp = "jdbc:odbc:db_demo";
private String username = "";
private String password = "";
private boolean xdebug = true; public Connection con = null; public String sql = null; Statement stmt = null;
public ResultSet result = null;
private int affectedRows = 0; public DBConnBean()
{
xdebug = true;
con = null;
sql = null;
}
public Connection Connect()
throws Exception
{
String msg = null;
try
{
Class.forName(DBDriver).newInstance();
}
catch(Exception e)
{
msg = "加载数据库驱动失败";
if (xdebug) msg += "(驱动´"+DBDriver+"´)";
throw new Exception(msg);
}
try
{
String conStr = conp;
con = DriverManager.getConnection(conStr,username,password);
}
catch(SQLException e)
{
msg = "!!数据库连接失败";
if (xdebug)
{
msg += "(错误信息=´" + e.getMessage()+"´ SQL状态值=´" + e.getSQLState()+"´ 错误代码=´" + e.getErrorCode()+"´)";
}
throw new Exception(msg);
}
return con;
}
protected void finalize()
throws Throwable
{
super.finalize();
if (stmt != null) stmt.close();
if (result != null) result.close();
}
//最近一次对数据库查询受影响的行数
public int getAffectedRows()
{
return affectedRows;
}
public Connection getCon()
{
return con;
}
public String getConp()
{
return conp;
}
public String getDBDriver()
{
return DBDriver;
}
public String getDBName()
{
return DBName;
}
public boolean getDebug()
{
return xdebug;
}
public String getPassword()
{
return password;
}
public ResultSet getResult()
{
return result;
}
public String getSql()
{
return sql;
}
public String getUsername()
{
return username;
}
public void over()
throws Throwable
{
finalize();
}
public ResultSet query()
throws Exception
{
result = null;
affectedRows = 0;
if (con == null)
Connect();
if (stmt == null)
stmt = con.createStatement();
if (sql.substring(0,6).equalsIgnoreCase("select"))
{
result = stmt.executeQuery(sql);
}
else
{
affectedRows = stmt.executeUpdate(sql);
}
return result;
}
public ResultSet query(String s)
throws Exception
{
sql = s;
return query();
}
public void setDBDriver(String s)
{
DBDriver = s;
}
public void setDebug(boolean b)
{
xdebug = b;
}
public void setgetConp(String s)
{
conp = s;
}
public void setgetDBName(String s)
{
DBName = s;
}
public void setgetUsername(String s)
{
username = s;
}
public void setPassword(String s)
{
password = s;
}
public void setSql(String s)
{
sql = s;
}
}
② DBQueryBean.Java的源代码如下所示:
package dbaccess;
import Java.sql.*;
import Java.util.*;
import Java.io.*;
import Java.lang.reflect.*; public class DBQueryBean
implements Serializable
{
DBConnBean dbc;
String sql = null;
int rowcount = 0;
int colcount = 0;
// int limitcount = 0;
Vector result = null;
public String _WATCH = "";
public DBQueryBean()
{
dbc = new DBConnBean();
try {
dbc.Connect();
} catch(Exception e) {
handleException(e);
}
}
protected void finalize()
throws Throwable
{
super.finalize();
if (dbc != null) dbc.over();
if (result != null) result.removeAllElements();
}
public String get(int row, int col)
{
if (result==null || row >= result.size()) return null;
String r[] = (String[])result.elementAt(row);
if (col >= Java.lang.reflect.Array.getLength(r)) return null;
return r[col];
}
public int getAffRows() { return dbc.getAffectedRows(); }
public int getColumncount() {
return colcount;
}
public String[] getRow(int row)
{
if (result==null || row >= result.size()) return null;
return (String [])result.elementAt(row);
/*String ret[] = new String[colcount];
Vector r = (Vector)result.elementAt(row);
for (int i=0; i<colcount; i++)
ret[i] = (String)r.elementAt(i);
return ret;*/
}
public int getRowcount() {
return rowcount;
}
public void handleException(Exception e)
{
_WATCH = e.getMessage();
}
public void init()
{
rowcount = 0;
colcount = 0;
// limitcount = 0;
result = null;
}
public void over()
throws Throwable
{
finalize();
}
public int query(String sql)
{
result = new Vector();
int ret = 0;
try {
ResultSet rs = dbc.query(sql);
if (rs == null)
{
ret = dbc.getAffectedRows();
}
else
{
ResultSetMetaData rm = rs.getMetaData();
colcount = rm.getColumnCount();
while (rs.next())
{
String row[] = new String[colcount];
for (int i=0; i<colcount; i++)
row[i] = rs.getString(i+1);
result.addElement(row);
rowcount++;
}
rs.close(); // to release the resource.
ret = result.size();
}
}
catch(Exception e)
{
handleException(e);
return -1;
} return ret;
}
}
这个好么?
import java.io.FileNotFoundException;
import java.io.PrintStream;
import java.sql.*;
import java.util.*;import javax.naming.Context;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
public class Tdb
{
private Connection con=null;//连接
private Statement st=null;//执行SQL语句的对象
private ResultSet rs=null;//记录集对象 public Connection getConnection()
{
try {
Context initCtx = new javax.naming.InitialContext();
Context envCtx = (Context)initCtx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtx.lookup("tel");
con=ds.getConnection();
//测试连接
if(con!=null)
{
System.out.println("ok");
}
return con;
}
catch(Exception e)
{
e.printStackTrace();
return null;
}
}
public ResultSet executeQuery(String sql){
try{
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = st.executeQuery(sql);
System.out.println("11");
}
catch(SQLException ex){
ex.printStackTrace();
}
return rs;
}
public int executeUpdate(String sql){
int result = 0;
try{
st = con.createStatement();
result = st.executeUpdate(sql);
}
catch(SQLException ex){
System.err.println(ex.getMessage());
}
return result;
}
public void close(){
if (con!=null){
try{
con.close();
con=null;
}
catch(SQLException ex){
System.err.println(ex.getMessage());
}
}
}
}
//mysqltest.java
package news;
import java.sql.*;
public final class mysqltest
{
//protected static mysqltest inst = new mysqltest();
private static final String _url = "proxool.xml-jlpd"; //proxool SourceName nickName
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null; //getConn
public Connection getConnection() throws SQLException
{
try
{
if(conn == null || conn.isClosed())
{
conn = DriverManager.getConnection(_url);
}
}
catch (SQLException e)
{
System.out.println(e);
}
return conn;
} //mysqltest
public mysqltest()
{
//inst.getConnection();
} //pstmt query
public PreparedStatement execute(String sql)
{
try
{
pstmt = getConnection().prepareStatement(sql);
}
catch(SQLException ex)
{
System.out.println(ex);
//System.err.println("sql_data.pstmt:"+ex.getMessage());
}
return pstmt;
}
//query
public ResultSet executeQuery(String sql)
{
try
{
stmt = getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(sql);
}
catch(SQLException ex)
{
//System.err.println("sql_data.executeQuery:"+ex.getMessage());
System.out.println(ex);
} return rs;
} //insert
public void executeInsert(String sql)
{
try
{
stmt = getConnection().createStatement();
stmt.executeUpdate(sql);
stmt.close();
stmt = null;
}
catch(SQLException ex)
{
System.out.println(ex);
}
finally
{
try
{
if (stmt != null)
{
stmt.close();
stmt = null;
}
}
catch(SQLException e)
{
System.out.println(e);
}
}
}
//update
public void executeUpdate(String sql)
{
try
{
stmt = getConnection().createStatement();
stmt.executeUpdate(sql);
stmt.close();
stmt = null;
}
catch(SQLException ex)
{
System.out.println(ex);
}
finally
{
try
{
if (stmt != null)
{
stmt.close();
stmt = null;
}
}
catch(SQLException e)
{
System.out.println(e);
}
}
} //delete
public void executeDelete(String sql)
{
try
{
stmt = getConnection().createStatement();
stmt.executeUpdate(sql);
stmt.close();
stmt = null;
}
catch(SQLException ex)
{
System.out.println(ex);
}
finally
{
try
{
if (stmt != null)
{
stmt.close();
stmt = null;
}
}
catch(SQLException e)
{
System.out.println(e);
}
}
}
//close
public void closeDB()
{
try
{
if(rs != null)
{
rs.close();
rs = null;
} if(pstmt != null)
{
pstmt.close();
pstmt = null;
} if(stmt != null)
{
stmt.close();
stmt = null;
} if(conn != null)
{
conn.close();
conn = null;
}
}
catch(SQLException e)
{
System.out.println(e);
} finally
{
try
{
if(rs != null)
{
rs.close();
rs = null;
} if(pstmt != null)
{
pstmt.close();
pstmt = null;
} if(stmt != null)
{
stmt.close();
stmt = null;
} if(conn != null)
{
conn.close();
conn = null;
}
}
catch(SQLException e)
{
System.out.println(e);
}
}
}}
楼上的给改改吧。
gongyali2005(JAVA 民工) suncheng_hong(亮) 帮忙每页都关闭了,一直按刷新还是断开。