package download;import java.sql.*;public class UDBConnection
{
Connection UDBConn = null;
Statement UDBstmt = null;
ResultSet UDBrs = null;
String UDBString = "jdbc:odbc:account";
//建立用户数据库连接
public UDBConnection()
{
} private void UDBConnect()
{
/*数据库驱动*/
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//System.err.println("驱动成功!(for Access)");
}
catch(ClassNotFoundException ei)
{
System.err.println("发生错误:数据库驱动程序不存在(for Access)!");
}
} //关闭用户数据库
public void UDBClose()
{
try
{
UDBstmt.close();
UDBConn.close();
}
catch(SQLException e)
{
System.err.println("数据库&执行关闭操作失败(for Access)!");
}
} //返回用户数据库记录集
public ResultSet executeQuery(String sql){
UDBConnect();
try
{
//Connection odbConn = DriverManager.getConnection("jdbc:odbc:DBpath");
UDBConn = DriverManager.getConnection(UDBString);
//System.err.println("连接用户数据库成功(for Access)success");
UDBstmt = UDBConn.createStatement();
UDBrs = UDBstmt.executeQuery(sql);
}
catch(SQLException e)
{
System.err.println("用户数据库查询操作失败(for Access)!");
}
return UDBrs;
} //用户数据库插入更新操作
public boolean executeUpdate(String sql){
UDBConnect();
try
{
//Connection odbConn = DriverManager.getConnection("jdbc:odbc:DBpath");
UDBConn = DriverManager.getConnection(UDBString);
System.err.println("连接用户数据库成功(for Access)");
Statement UDBstmt = UDBConn.createStatement();
UDBstmt.executeUpdate(sql);
UDBstmt.close();
return true;
}
catch(SQLException e)
{
System.err.println("用户数据库执行插入/更新操作失败(for Access)!");
return false;
}
}
}我这么写的,先看一下,保安赶人走了
{
Connection UDBConn = null;
Statement UDBstmt = null;
ResultSet UDBrs = null;
String UDBString = "jdbc:odbc:account";
//建立用户数据库连接
public UDBConnection()
{
} private void UDBConnect()
{
/*数据库驱动*/
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//System.err.println("驱动成功!(for Access)");
}
catch(ClassNotFoundException ei)
{
System.err.println("发生错误:数据库驱动程序不存在(for Access)!");
}
} //关闭用户数据库
public void UDBClose()
{
try
{
UDBstmt.close();
UDBConn.close();
}
catch(SQLException e)
{
System.err.println("数据库&执行关闭操作失败(for Access)!");
}
} //返回用户数据库记录集
public ResultSet executeQuery(String sql){
UDBConnect();
try
{
//Connection odbConn = DriverManager.getConnection("jdbc:odbc:DBpath");
UDBConn = DriverManager.getConnection(UDBString);
//System.err.println("连接用户数据库成功(for Access)success");
UDBstmt = UDBConn.createStatement();
UDBrs = UDBstmt.executeQuery(sql);
}
catch(SQLException e)
{
System.err.println("用户数据库查询操作失败(for Access)!");
}
return UDBrs;
} //用户数据库插入更新操作
public boolean executeUpdate(String sql){
UDBConnect();
try
{
//Connection odbConn = DriverManager.getConnection("jdbc:odbc:DBpath");
UDBConn = DriverManager.getConnection(UDBString);
System.err.println("连接用户数据库成功(for Access)");
Statement UDBstmt = UDBConn.createStatement();
UDBstmt.executeUpdate(sql);
UDBstmt.close();
return true;
}
catch(SQLException e)
{
System.err.println("用户数据库执行插入/更新操作失败(for Access)!");
return false;
}
}
}我这么写的,先看一下,保安赶人走了
java.lang.NullPointerException
存在的问题实在是太多了。建议再多翻翻帖子。关于ResultSet对象的关闭我说一下,你自己看看找找毛病。通常我们不用显示的关闭 ResultSet (即ResultSet.close())。当我们关闭产生这个ResultSet的Statement对象时,这个ResultSet也会被关闭。同样,当我们再次执行这个Statement时,以前那个ResultSet也会被关闭。使用close()方法可以使我们显示的关闭ResultSet对象。这是良好变成习惯的表现。
建议在不使用ResultSet时显示的把它关闭掉。:)
import java.sql.*;
public class conn{
String dbDriver;
String connstr;
String username;
String password;
Connection conn;
ResultSet rs;
Statement stmt; public conn()
{
dbDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
connstr = "jdbc:microsoft:sqlserver://127.0.0.1:1433;databaseName=aaa";
username = "sa";
password = "";
conn = null;
rs = null;
stmt = null; try{
Class.forName(dbDriver);//.newInstance();
}catch(java.lang.ClassNotFoundException e)
{
System.err.println("sql:"+e.getMessage());
}
}
public void sqlInsert(String sql)
{
try{
conn=DriverManager.getConnection(connstr,username,password);
stmt=conn.createStatement();
stmt.executeUpdate(sql);
}catch(SQLException e)
{
System.err.println("sqlInsert Err:"+e.getMessage());
}
}
public ResultSet sqlQuery(String sql)
{
//rs=null;
try{
conn=DriverManager.getConnection(connstr,username,password);
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=stmt.executeQuery(sql);
}catch(SQLException e)
{
System.err.println("sqlQuery Err:"+e.getMessage());
}
return rs;
} public void sqlDelete(String sql)
{
try{
conn=DriverManager.getConnection(connstr,username,password);
stmt=conn.createStatement();
stmt.executeUpdate(sql);
}catch(SQLException e)
{
System.err.println("sqlDelete Err:"+e.getMessage());
}
} public void sqlUpdate(String sql)
{
try{
conn=DriverManager.getConnection(connstr,username,password);
stmt=conn.createStatement();
stmt.executeUpdate(sql);
}catch(SQLException e)
{
System.err.println("sqlUpdate Err:"+e.getMessage());
}
} public void close()
{//关闭数据,记录集,
try{
if(rs!=null){
rs.close();
}
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}catch(SQLException e)
{
System.err.println("Close Err:"+e.getMessage());
}
}
}jsp<%
conn conn=new conn();
ResultSet rs=conn.sqlQuery("select * from aaa");
while(rs.next()){
out.println(rs.getString("name"));
out.println(rs.getString("pwd"));
}
conn.close();
%>
应该有个ado类,封装所有数据访问代码,对外开放executeQuery(sql),executeNonQuery(sql)等方法
package com.asiapac.connectdb;import java.sql .Connection ;
import java.sql.Statement ;
import java.sql .DriverManager ;
import java.sql.PreparedStatement ;
import java.sql .SQLException ;
import java.sql .ResultSet ;
import java.util.*;
import java.io.*;
import com.asiapac.util.*;
public class ADO{
private Connection dbConnection;
private String queryString;
private String sqlTemp;
private PrintWriter log;
private String sDriver;
private String username;
private String passwd;
private String dburl;
private String propertiesFile;
private java.sql.PreparedStatement preStmt;
public ADO(String propertiesFile) throws Exception{
this.propertiesFile=propertiesFile;
InputStream is = getClass().getResourceAsStream("/"+this.propertiesFile);
FixProperties dbProps = new FixProperties();
try {
dbProps.load(is);
}
catch (Exception e) {
System.err.println("Can't read the properties file. " +
"Please placed db.properties in CLASSPATH specfy path");
return;
}
String logFile = dbProps.getProperty("logfile", "DBConnectionManager.log");
try {
//log = new PrintWriter(System.err);
log = new PrintWriter(new FileWriter(logFile, true), true);
}
catch (IOException e) {
System.err.println("Can't open log file: " + logFile);
log = new PrintWriter(System.err);
}
//loadDrivers(dbProps);
init(dbProps);
}
public ADO(FixProperties props) throws Exception{
init(props);
}
public static void main(String s[]){
String sql="select launchto from pagecontent where page_id=1";
try{
ADO ado=new ADO("ado.properties");
ado.executeQuery(sql);
}catch(Exception ex){
ex.printStackTrace();
} }
public ResultObject executeQuery(String sql)throws Exception {
sqlTemp=sql.toLowerCase ();
if(sqlTemp.trim ().startsWith ("select")){
this.setQueryString (sql);
return this.select();
}
return null;
}
public void executeUpdate(String sql)throws Exception {
sqlTemp=sql.toLowerCase ();
this.setQueryString (sql);
if(sqlTemp.trim ().startsWith ("insert")){
this.insert ();
}else if(sqlTemp.trim ().startsWith ("update")){
this.update ();
}else if(sqlTemp.trim ().startsWith ("delete")){
this.delete ();
}
}
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws Exception {
return this.dbConnection.prepareStatement(sql);
}
public PreparedStatement preparedStatement(String sql)throws Exception {
return this.dbConnection.prepareStatement(sql);
} public void beginTrans() throws Exception {
this.dbConnection .setAutoCommit(false);
}
public void endTrans() throws Exception {
this.dbConnection .setAutoCommit(true);
}
public void commitTrans() throws Exception {
this.dbConnection .commit();
} public void rollbackTrans() throws Exception {
this.dbConnection .rollback();
}
private ResultObject select()throws Exception {
try{
if(this.queryString ==null){
throw new Exception ("please set your queryString");
}
Statement stmt=this.dbConnection .createStatement ();
ResultSet rs=stmt.executeQuery (this.queryString );
ResultObject rso=new ResultObject(rs);
rs.close();
stmt.close();
return rso;
}catch(Exception se){
throw new SQLException("ADO.select() : "+queryString+"\n" + se.getMessage());
}
}
private void insert()throws Exception {
try{
if(this.queryString ==null){
throw new SQLException ("please set your queryString");
}
Statement stmt=this.dbConnection .createStatement ();
int low=stmt.executeUpdate (this.queryString );
if(low==0){
throw new com.asiapac.exception.InsertFailException("ADO.insert(): "+this.queryString );
}
stmt.close ();
}catch(Exception se){
throw new SQLException("ADO.insert() : " + se.getMessage());
}
}
private void update()throws Exception {
try{
if(this.queryString ==null){
throw new SQLException ("please set your queryString");
}
Statement stmt=this.dbConnection .createStatement ();
int low=stmt.executeUpdate (this.queryString );
stmt.close ();
}catch(Exception se){
throw new SQLException("ADO.update() : " +this.queryString + se.getMessage());
}
}
private void delete()throws Exception {
try{
if(this.queryString ==null){
throw new SQLException ("please set your queryString");
}
Statement stmt=this.dbConnection .createStatement ();
int low=stmt.executeUpdate (this.queryString );
stmt.close ();
}catch(Exception se){
throw new SQLException("ADO.delete() : "+this.queryString + se.getMessage());
}
} private void setQueryString(String queryString){
this.queryString =queryString;
} private Connection getDbConnection() throws Exception {
try{
//System.out.println("connectionString:"+dburl+" "+username+" "+passwd);
return DriverManager.getConnection(dburl,username,passwd);
}catch(Exception e){
throw new ConnectionException("ADO.getDbConnection() : " + e.getMessage());
}
}
public Connection getConnection(){
return this.dbConnection;
}
private void init(FixProperties dbProps) throws Exception {
loadDrivers(dbProps);
username=dbProps.getProperty("database.user");
passwd=dbProps.getProperty("database.password");
dburl=dbProps.getProperty("database.url");
this.dbConnection =this.getDbConnection();
} /**
* 装载和注册所有JDBC驱动程序
*
* @param props 属性
*/
private void loadDrivers(FixProperties props) {
String driverName = props.getProperty("database.drivers");
try {
Class.forName (driverName);
}
catch (Exception e) {
log.println ("Can't load JDBC driver: " +driverName + ", Error: " + e);
}
}
/**
* 获取指定表的字段最大值(仅限integer)
*/
public int getMaxIdFromDB(String table,String fieldname) throws Exception{
String sqlStr="SELECT MAX("+fieldname+") AS maxid"
+" FROM "+table;
ResultObject rso=this.executeQuery(sqlStr);
if (rso.next()){
try{
return rso.getInt(1);
}catch(java.lang.NumberFormatException nfe){
return 0;
}
}else{
return 0;
}
}
}