没太理解,你看看这个行不?
package oajava.common.database;import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;public class OmPreparedQuery
{
public Connection connection = null; //获得数据库连接
private Statement stmt = null; //创建语句
ResultSet rsExcutQuery = null;
boolean isConnection = true;
String strDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String strUrl = "jdbc:microsoft:sqlserver://192.168.0.24:1433;DatabaseName=aaa";
String strUserName = "sa";
String strUserPassWord = "sa";
//构造函数
public OmPreparedQuery()
{
}
/**
* 功能:
* 连接数据库
* @return boolean
*/
public boolean setConnectDataBase() throws Exception
{
try
{
Class.forName(strDriver).newInstance();
connection = DriverManager.getConnection(strUrl,strUserName,strUserPassWord);
stmt = connection.createStatement();
}
catch (Exception e)
{
isConnection = false;
System.out.println("数据库连接出现如下异常:" + e.getMessage());
}
return isConnection;
}
/**
* 功能:
* 设置是否自动提交 arg = true 自动提交, arg = false 不自动提交
* @return boolean
*/
public boolean setAutoCommit(boolean arg)
{
try
{
connection.setAutoCommit(arg);
}
catch (Exception e)
{
System.out.println("设置是否自动提交出现如下异常:" + e.getMessage());
}
return isConnection;
} /**
* 功能:
* 添加
* @param strSql sql语句
* int intRow = 1; 正常 intRow = 0; 出错
* @return int
*/
public int executeInsert(String strSql)
{
int intRow = 1; //正常
try
{
intRow = stmt.executeUpdate(strSql);
}
catch (SQLException eExcutQuery)
{
intRow = 0; //出错
System.out.println("执行添加操作出现如下异常:" + eExcutQuery.getMessage());
}
return intRow;
} /**
* 功能:
* 查询
* @param strSql sql语句
* @return ResultSet
*/
public ResultSet executeQuery(String strSql)
{
try
{
rsExcutQuery = stmt.executeQuery(strSql);
}
catch (SQLException eExcutQuery)
{
rsExcutQuery = null;
System.out.println("执行查询操作出现如下异常:" + eExcutQuery.getMessage());
}
return rsExcutQuery;
} /**
* 功能:
* 更新
* @param strSql sql语句
* int intRow = 1; 正常 intRow = 0; 出错
* @return int
*/
public int executeUpdate(String strSql)
{
int intRow = 1; //正常
try
{
intRow = stmt.executeUpdate(strSql);
}
catch (SQLException eExecteUpdate)
{
System.out.println("执行更新操作出现如下异常:" + eExecteUpdate.getMessage());
intRow = 0; //出错
}
return intRow;
} /**
* 功能:
* 删除
* @param strSql sql语句
* int intRow = 1; 正常 intRow = 0; 出错
* @return int
*/
public int executeDelete(String strSql)
{
int intRow = 1; //正常
try
{
intRow = stmt.executeUpdate(strSql);
}
catch (SQLException eExecteUpdate)
{
System.out.println("执行删除操作出现如下异常:" + eExecteUpdate.getMessage());
intRow = 0; //出错
}
return intRow;
} /**
* 功能:
* 关闭所有连接
*/
public void closeAll()
{
try
{
if(rsExcutQuery != null)
{
rsExcutQuery.close();
rsExcutQuery = null;
} if(stmt!=null)
{
stmt.close();
stmt = null;
} if(connection!=null)
{
connection.close();
}
}
catch(Exception e)
{
System.out.println("关闭数据出现如下异常:" + e.getMessage());
}
} /**
* 功能:
* 提交数据
*/
public void commit()
{
try
{
connection.commit();
}
catch(Exception e)
{
System.out.println("提交数据出现如下异常:" + e.getMessage());
}
} /**
* 功能:
* 回滚数据
*/
public void rollback()
{
try
{
connection.rollback();
}
catch(Exception e)
{
System.out.println("回滚数据出现如下异常:" + e.getMessage());
}
} }
package oajava.common.database;import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;public class OmPreparedQuery
{
public Connection connection = null; //获得数据库连接
private Statement stmt = null; //创建语句
ResultSet rsExcutQuery = null;
boolean isConnection = true;
String strDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String strUrl = "jdbc:microsoft:sqlserver://192.168.0.24:1433;DatabaseName=aaa";
String strUserName = "sa";
String strUserPassWord = "sa";
//构造函数
public OmPreparedQuery()
{
}
/**
* 功能:
* 连接数据库
* @return boolean
*/
public boolean setConnectDataBase() throws Exception
{
try
{
Class.forName(strDriver).newInstance();
connection = DriverManager.getConnection(strUrl,strUserName,strUserPassWord);
stmt = connection.createStatement();
}
catch (Exception e)
{
isConnection = false;
System.out.println("数据库连接出现如下异常:" + e.getMessage());
}
return isConnection;
}
/**
* 功能:
* 设置是否自动提交 arg = true 自动提交, arg = false 不自动提交
* @return boolean
*/
public boolean setAutoCommit(boolean arg)
{
try
{
connection.setAutoCommit(arg);
}
catch (Exception e)
{
System.out.println("设置是否自动提交出现如下异常:" + e.getMessage());
}
return isConnection;
} /**
* 功能:
* 添加
* @param strSql sql语句
* int intRow = 1; 正常 intRow = 0; 出错
* @return int
*/
public int executeInsert(String strSql)
{
int intRow = 1; //正常
try
{
intRow = stmt.executeUpdate(strSql);
}
catch (SQLException eExcutQuery)
{
intRow = 0; //出错
System.out.println("执行添加操作出现如下异常:" + eExcutQuery.getMessage());
}
return intRow;
} /**
* 功能:
* 查询
* @param strSql sql语句
* @return ResultSet
*/
public ResultSet executeQuery(String strSql)
{
try
{
rsExcutQuery = stmt.executeQuery(strSql);
}
catch (SQLException eExcutQuery)
{
rsExcutQuery = null;
System.out.println("执行查询操作出现如下异常:" + eExcutQuery.getMessage());
}
return rsExcutQuery;
} /**
* 功能:
* 更新
* @param strSql sql语句
* int intRow = 1; 正常 intRow = 0; 出错
* @return int
*/
public int executeUpdate(String strSql)
{
int intRow = 1; //正常
try
{
intRow = stmt.executeUpdate(strSql);
}
catch (SQLException eExecteUpdate)
{
System.out.println("执行更新操作出现如下异常:" + eExecteUpdate.getMessage());
intRow = 0; //出错
}
return intRow;
} /**
* 功能:
* 删除
* @param strSql sql语句
* int intRow = 1; 正常 intRow = 0; 出错
* @return int
*/
public int executeDelete(String strSql)
{
int intRow = 1; //正常
try
{
intRow = stmt.executeUpdate(strSql);
}
catch (SQLException eExecteUpdate)
{
System.out.println("执行删除操作出现如下异常:" + eExecteUpdate.getMessage());
intRow = 0; //出错
}
return intRow;
} /**
* 功能:
* 关闭所有连接
*/
public void closeAll()
{
try
{
if(rsExcutQuery != null)
{
rsExcutQuery.close();
rsExcutQuery = null;
} if(stmt!=null)
{
stmt.close();
stmt = null;
} if(connection!=null)
{
connection.close();
}
}
catch(Exception e)
{
System.out.println("关闭数据出现如下异常:" + e.getMessage());
}
} /**
* 功能:
* 提交数据
*/
public void commit()
{
try
{
connection.commit();
}
catch(Exception e)
{
System.out.println("提交数据出现如下异常:" + e.getMessage());
}
} /**
* 功能:
* 回滚数据
*/
public void rollback()
{
try
{
connection.rollback();
}
catch(Exception e)
{
System.out.println("回滚数据出现如下异常:" + e.getMessage());
}
} }
Statement stmt = conn.createStatement();
stmt.execute(sql);
}不过不建议你这么做, 用PreparedStatement 会更好
例如:我传入一个sql语句,如果查询表中的几个字段,而我需要把得到的ResultSet放入到List中,我的List中就是我的VO类(表映射类),这样VO类中的属性有的就是空的了。我最后得出来的就不对了。
还有就是插入操作,我只要操作几个字段,不用插入表的全部字段,我原来是传过来一个VO类,类里就是我要插入表中的所有字段值,这样我容易写,在对PreparedStatement做set方法时,就好从类中把值取到,如果我只插入几个字段,这样VO类的属性有的为空了,也就是我插入的值为空值。字符串还好,如果是日期型怎么办?最后主要的是我传入的sql语句应该怎么写,是要像jdbc那样写吗?
例如:insert into XXX values(?,?,?,?)或是select aa,bb from XXX where aa=?吗?
to shaopin:我这样写也是为了分层啊,才用VO对象的。那我应该如何写好呢?
* DBOperation.java
*
* Created on 2004年7月19日, 上午9:52
*/package Autosale;import java.sql.*;/**
*
* @author user
*/
public class DBOperation {
/* jdbc-odbc bridge driver */
private String strDbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
/* odbc data source */
private String strConn = "jdbc:odbc:autosale";
/* connection */
private Connection conn = null;
/* data set */
private ResultSet result = null;
/* PreparedStatement */
//private PreparedStatement pst = null;
/* database user */
private String strUser = "Frank";
/* user password */
private String strPassword = "";
/**
* constructor
*/
public DBOperation() {
try{
Class.forName( strDbDriver );
} catch( ClassNotFoundException ex ) {
System.err.println( ex.getMessage() );
}
}
/**
* constructor
* @param strUser database user
* @param strPassword user password
*/
public DBOperation(String strUser, String strPassword) {
this();
this.strUser = strUser;
this.strPassword = strPassword;
}
/**
* execute query and retrive the data
* @param strSql SQL string( select )
* @return result data
*
* @throws SQLException
*/
public ResultSet executeQuery( String strSql ) throws SQLException {
// clear result set
result = null;
// get Connection
conn = DriverManager.getConnection( strConn, strUser, strPassword );
Statement st = conn.createStatement();
// query
return st.executeQuery( strSql );
}
/**
* modify the data in database
* @param strSql SQL string ( delete, insert and update )
* @return execute result
* -1: error
* other: normal(the count of the data rows modified )
*
* @throws SQLException
*/
public int executeUpdate( String strSql ) throws SQLException {
// return value
int nRetValue = -1;
// get connection
conn = DriverManager.getConnection( strConn, strUser, strPassword );
Statement st = conn.createStatement();
// update
nRetValue = st.executeUpdate( strSql );
// close Statement and Connection
st.close();
conn.close();
return nRetValue;
}
}
你可以直接调用这个函数,不过要try{}catch(){}的
应该是封装VO后,在应用层解析成sql用带参数的形式来执行
然后再把结果封装成VO后返回,这样好处就方便很多
不然你想象一下,那么多的sql在代码里,以后一旦换个db
还不改死你?
再关心细节另外:Connection是不能关的是因为使用了Connection Pool