JSPWeb开发中一个关于数据库操作类该这么写，类中要带有查询，增删改，以及关闭数据库操作方法，请高手指

发布网友发布时间：2022-04-10 21:37

共3个回答

热心网友时间：2022-04-10 23:06

package com.oa..impl; import java.sql.Connection; </li>import java.sql.PreparedStatement; </li>import java.sql.ResultSet; </li>import java.sql.SQLException; </li>import java.sql.Statement; </li>import java.util.List; </li>import java.util.Map; </li> import org.apache.commons.logging.Log; </li>import org.apache.commons.logging.LogFactory; </li> import com.oa.core.DynaForm; </li>import com.oa..Dao; </li>import com.oa.jdbc.ConnectionFactory; </li>import com.oa.jdbc.DaoAccessException; </li>import com.oa.jdbc.DybaBean; </li>import com.oa.jdbc.JdbcPage; </li>import com.oa.jdbc.Page; </li>import com.oa.jdbc.RowsDynaClass; </li> /** </li> * 基类DAO，实现一些基本共有方法 * * @author Gao_wx Mar 26, 2008 */ public class BaseDao implements Dao { </li> protected Log logger = LogFactory.getLog(BaseDao.class); </li> // 获取数据库链接 </li> protected Connection getConnection() { </li> return ConnectionFactory.getConnection(); </li> } // 在此链接上，根据sql语句获取List </li> protected List<DybaBean> getList(Connection conn, String sql) </li> throws DaoAccessException { </li> try { </li> Statement st = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = st.executeQuery(sql); return getList(rs); </li> } catch (SQLException e) { </li> throw new DaoAccessException(sql, e); </li> } } // 根据Result获取List </li> protected List<DybaBean> getList(ResultSet rs) throws SQLException { </li> RowsDynaClass rsdc = new RowsDynaClass(rs); </li> return rsdc.getRows(); </li> } // 根据sql语句获取List </li> protected List<DybaBean> getList(String sql) throws DaoAccessException { </li> Connection conn = getConnection(); return getList(conn, sql); </li> } // 在此连接上，根据sql语句，id加载一条记录 </li> protected DybaBean loadById(Connection conn, String sql, int id) </li> throws DaoAccessException { </li> try { </li> PreparedStatement pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pst.setInt(1, id); </li> ResultSet rs = pst.executeQuery(); List list = getList(rs); if (list.size() > 1) { </li> logger.warn("加载了2条以上的记录，请检查sql：" + sql); </li> } if (list.size() == 0) { </li> logger.warn("数据库没有此记录！"); </li> return null; </li> } else { </li> return (DybaBean) list.get(0); </li> } } catch (SQLException e) { </li> throw new DaoAccessException(sql, e); </li> } } // 根据sql语句，id，加载一条记录 </li> protected DybaBean loadById(String sql, int id) throws DaoAccessException { </li> Connection conn = getConnection(); return loadById(conn, sql, id); </li> } // 在此连接上，根据Sql和id删除一条记录，返回影响行数 </li> protected int deleteById(Connection conn, String sql, int id) </li> throws SQLException { </li> PreparedStatement pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pst.setInt(1, id); </li> int del = pst.executeUpdate(); </li> return del; </li> } // 根据sql语句，id删除一条记录 </li> protected int deleteById(String sql, int id) throws DaoAccessException { </li> Connection conn = getConnection(); try { </li> int del = deleteById(conn, sql, id); </li> return del; </li> } catch (SQLException e) { </li> throw new DaoAccessException(sql, e); </li> } } protected Page getPage(DynaForm form, String sql) throws DaoAccessException { </li> Connection conn = getConnection(); Page page = getPage(conn, form, sql); return page; </li> } // 获取分页对象 </li> protected Page getPage(Connection conn, DynaForm form, String sql) </li> throws DaoAccessException { </li> int p = form.getInt("p"); </li> if (p < 1) { </li> p = 1; </li> } int size = form.getInt("size"); </li> if (size < 10) { </li> size = 10; </li> } Page page; try { </li> page = new JdbcPage(conn, sql, p, size); </li> } catch (SQLException e) { </li> throw new DaoAccessException(sql, e); </li> } return page; </li> } }

热心网友时间：2022-04-11 00:24

package com.;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class dbsql {
protected static final String drive = "com.microsoft.sqlserver.jdbc.SQLServerDriver";//SQL数据库引擎
protected static final String url = "jdbc:sqlserver://localhost:1433;DatabaseName=这里写你要连接的数据库文件名" ;//连接字符串
protected static final String user = "sa";
protected static final String pass = "123";

// 操作数据库
protected static Connection conn = null;//打开和关闭数据库
protected static PreparedStatement ps = null;
protected static ResultSet rs = null;

/**
* 打开连接
*
* @return
*/
public Connection open() {
try {
Class.forName(drive);
conn = DriverManager.getConnection(url, user, pass);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}

/**
* 关闭连接
*/
public void close() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
} else if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
} else if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

/**
* 增删改操作都调用这个方法
* 里面的代码基本跟查询的是一样的只是后面执行的不同
*/

public int execute(String sql, Object[] obj) {
int num = 0;
try {
conn = open(); // 打开数据库连接
ps = conn.prepareStatement(sql);
if (null != obj) {
for (int i = 0; i < obj.length; i++) {
ps.setObject(i + 1, obj[i]);
}
}
//执行数据库的增加删除修改的
num = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(); //关闭数据库连接
}
return num;
}

/**
* 查询操作
*/
public void executeQuerySQL(String sql,Object[] obj){
try {
conn=open(); //调用我上面些的打开数据量连接
ps=conn.prepareStatement(sql); //接收子类传进来的sql语句并且执行
if(null!=obj){//obj 是查询条件
//循环 obj
for (int i = 0; i < obj.length; i++) {
//ps.setObject 是接收参数的

ps.setObject(i+1, obj[i]);
}
}
//这个是执行数据库查询的
rs=ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("查询数据失败");
}
}
}
望采纳

热心网友时间：2022-04-11 01:59

package s2jsp.lg..impl;
import java.sql.*;public class BaseDao {
public final static String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public final static String URL = "jdbc:sqlserver://localhost:1433;DataBaseName=bbs";
public final static String DBNAME = "sa";
public final static String DBPASS = "sa";

public BaseDao() throws ClassNotFoundException, SQLException{
getCon();
}

Connection con = null;
PreparedStatement ps = null;
ResultSet res = null;

private void getCon() throws ClassNotFoundException, SQLException{
Class.forName(DRIVER);
con = DriverManager.getConnection(URL,DBNAME,DBPASS);
}

public int execUpdate(String sql,Object[] obj){
int num = 0;
try {
ps = con.prepareStatement(sql);
if(obj!=null){
for(int i=0;i<obj.length;i++){
ps.setObject(i+1, obj[i]);
}
}
num = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
closePstatement();
closeCon();
}
return num;
}

public ResultSet execQuery(String sql,Object[] obj){
ResultSet res = null;
try {
ps = con.prepareStatement(sql);
if(obj!=null){
for(int i=0;i<obj.length;i++){
ps.setObject(i+1, obj[i]);
}
}
res = ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeResult();
closePstatement();
closeCon();
}
return res;
}

public void closeCon(){
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public void closePstatement(){
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public void closeResult(){
if(res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}