求一个数据库封装好的类,内容越丰富越稳定越好 rt 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 ConnectionPreparedStatementResultSet三个类够你学不? dbutils apache dbuitls hibernate. package cn.ccsu.common;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import javax.naming.Context;import javax.naming.InitialContext;import javax.sql.DataSource;public class DBUtil { public static Connection getDBConnection() { Connection conn = null; try { Context initContext = new InitialContext(); Context envContext = (Context)initContext.lookup("java:/comp/env"); DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle"); conn = ds.getConnection(); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void closeDBResources(Connection conn, PreparedStatement pstmt, ResultSet rs) { try { if (conn != null) { conn.close(); } if (pstmt != null) { pstmt.close(); } if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } }} 数据库的连接是服务器负责管理的 不过要在服务器上进行配置 jndi(Java Naming and Directory Interface) import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.DbUtils;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.MapHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import com.hxw.simple.light.mvc.exception.SlightException;import com.hxw.simple.light.mvc.jdbc.simple.SimpleCallableStatementCallbackImpl;import com.hxw.simple.light.mvc.jdbc.simple.SimpleCallableStatementCreatorImpl;import com.hxw.simple.light.mvc.jdbc.util.QueryRunnerTool;public class QueryTemplate { protected QueryRunner queryRunner = null; protected String properties = ""; public QueryTemplate() { super(); } public QueryTemplate(String properties) { super(); this.properties = properties; } public QueryRunner getQueryRunner() { return this.queryRunner; } public void setQueryRunner(QueryRunner queryRunner) { this.queryRunner = queryRunner; } public String getProperties() { return this.properties; } public void setProperties(String properties) { this.properties = properties; } public int queryForInt(String sql) { return ((Long) this.queryForObject(sql, null)).intValue(); } public int queryForInt(String sql, Object[] params) { return ((Long) this.queryForObject(sql, params)).intValue(); } public Object queryForObject(String sql) { return this.queryForObject(sql, null); } public Object queryForObject(String sql, Object[] params) { return this.queryForObjectByColumnIndex(sql, 1, params); } public Object queryForObjectByColumnIndex(String sql) { return this.queryForObjectByColumnIndex(sql, 1, null); } public Object queryForObjectByColumnIndex(String sql, Object[] params) { return this.queryForObjectByColumnIndex(sql, 1, params); } public Object queryForObjectByColumnIndex(String sql, int columnIndex, Object[] params) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); Object object = null; try { if (params == null) { object = this.queryRunner.query(sql, new ScalarHandler( columnIndex)); } else { object = this.queryRunner.query(sql, params, new ScalarHandler( columnIndex)); } } catch (SQLException e) { throw new SlightException("查询结果失败。", e); } finally { QueryRunnerTool.removeQueryRunner(); } return object; } public Object queryForObjectByColumnName(String sql, String columnName) { return this.queryForObjectByColumnName(sql, columnName); } public Object queryForObjectByColumnName(String sql, String columnName, Object[] params) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); Object object = null; try { if (params == null) { object = this.queryRunner.query(sql, new ScalarHandler( columnName)); } else { object = this.queryRunner.query(sql, params, new ScalarHandler( columnName)); } } catch (SQLException e) { throw new SlightException("查询结果失败。", e); } finally { QueryRunnerTool.removeQueryRunner(); } return object; } public Map<String, Object> queryForMap(String sql) { return this.queryForMap(sql, null); } @SuppressWarnings("unchecked") public Map<String, Object> queryForMap(String sql, Object[] params) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); Map<String, Object> map = null; try { if (params == null) { map = (Map<String, Object>) this.queryRunner.query(sql, new MapHandler()); } else { map = (Map<String, Object>) this.queryRunner.query(sql, params, new MapHandler()); } } catch (SQLException e) { throw new SlightException("查询映射表结果失败。", e); } finally { QueryRunnerTool.removeQueryRunner(); } return map; } public List<Map<String, Object>> queryForMapList(String sql) { return this.queryForMapList(sql, null); } @SuppressWarnings("unchecked") public List<Map<String, Object>> queryForMapList(String sql, Object[] params) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); try { if (params == null) { list = (List<Map<String, Object>>) this.queryRunner.query(sql, new MapListHandler()); } else { list = (List<Map<String, Object>>) this.queryRunner.query(sql, params, new MapListHandler()); } } catch (SQLException e) { throw new SlightException("查询映射列表结果失败。", e); } finally { QueryRunnerTool.removeQueryRunner(); } return list; } public <T> T queryEntityBean(String sql, Class<T> entityClass) { return this.queryEntityBean(sql, null, entityClass); } @SuppressWarnings("unchecked") public <T> T queryEntityBean(String sql, Object[] params, Class<T> entityClass) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); Object object = null; try { if (params == null) { object = this.queryRunner.query(sql, new BeanHandler( entityClass)); } else { object = this.queryRunner.query(sql, params, new BeanHandler( entityClass)); } } catch (SQLException e) { throw new SlightException("查询实体结果失败。", e); } finally { QueryRunnerTool.removeQueryRunner(); } return (T) object; } public <T> List<T> queryForList(String sql, Class<T> entityClass) { return this.queryForList(sql, null, entityClass); } @SuppressWarnings("unchecked") public <T> List<T> queryForList(String sql, Object[] params, Class<T> entityClass) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); List<T> list = new ArrayList<T>(); try { if (params == null) { list = (List<T>) this.queryRunner.query(sql, new BeanListHandler(entityClass)); } else { list = (List<T>) this.queryRunner.query(sql, params, new BeanListHandler(entityClass)); } } catch (SQLException e) { throw new SlightException("查询实体列表结果失败。", e); } finally { QueryRunnerTool.removeQueryRunner(); } return list; } /** * 执行更新sql语句,无法保证事务不推荐使用 * * @deprecated **/ public int update(String sql) { return this.update(sql, null); } /** * 执行更新sql语句,无法保证事务不推荐使用 * * @deprecated **/ public int update(String sql, Object[] params) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); int affectedRows = 0; try { if (params == null) { affectedRows = this.queryRunner.update(sql); } else { affectedRows = this.queryRunner.update(sql, params); } } catch (SQLException e) { throw new SlightException("更新数据操作结果失败。", e); } finally { QueryRunnerTool.removeQueryRunner(); } return affectedRows; } /** * 执行更新批处理sql语句,无法保证事务不推荐使用 * * @deprecated **/ public int[] batchUpdate(String sql, Object[][] params) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); int affectedRows[] = null; try { if (params == null) { int i = this.update(sql); return new int[] { i }; } else { affectedRows = this.queryRunner.batch(sql, params); } } catch (SQLException e) { throw new SlightException("数据操作结果失败。", e); } finally { QueryRunnerTool.removeQueryRunner(); } return affectedRows; } public int transactionUpdate(String sql) { return this.transactionUpdate(sql, null, null); } public int transactionUpdate(String sql, Object[] params, int[] types) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); int affectedRows = 0; Connection conn = null; PreparedStatement pstmt = null; try { conn = this.queryRunner.getDataSource().getConnection(); pstmt = conn.prepareStatement(sql); conn.setAutoCommit(false); this.addParameters(pstmt, params, types); affectedRows = pstmt.executeUpdate(); conn.commit(); } catch (SQLException e) { try { conn.rollback(); } catch (SQLException er) { throw new SlightException("回滚异常。", er); } throw new SlightException("数据操作失败,执行回滚。", e); } finally { try { DbUtils.close(pstmt); DbUtils.close(conn); } catch (SQLException e) { throw new SlightException("关闭资源出错。", e); } QueryRunnerTool.removeQueryRunner(); } return affectedRows; } public int[] transactionBachUpdate(String sql, List<Object[]> listParams, int[] types) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); int affectedRows[] = null; Connection conn = null; PreparedStatement pstmt = null; try { conn = this.queryRunner.getDataSource().getConnection(); pstmt = conn.prepareStatement(sql); conn.setAutoCommit(false); this.addBatchParameters(pstmt, listParams, types); affectedRows = pstmt.executeBatch(); conn.commit(); } catch (SQLException e) { try { conn.rollback(); } catch (SQLException er) { throw new SlightException("回滚异常。", er); } throw new SlightException("批处理数据操作结果失败,执行回滚。", e); } finally { try { DbUtils.close(pstmt); DbUtils.close(conn); } catch (SQLException e) { throw new SlightException("关闭资源出错。", e); } QueryRunnerTool.removeQueryRunner(); } return affectedRows; } public boolean executeCallStatement(String sql) { return this.executeCallStatement(sql, new SimpleCallableStatementCreatorImpl(sql), new SimpleCallableStatementCallbackImpl()); } @SuppressWarnings("unchecked") public <T> T executeCallStatement(String sql, CallableStatementCreator csc, CallableStatementCallback<T> action) { this.queryRunner = QueryRunnerTool.getQueryRunner(properties); CallableStatement cs = null; Connection con = null; Object result = null; try { con = this.queryRunner.getDataSource().getConnection(); cs = con.prepareCall(sql); cs = csc.createCallableStatement(cs); result = action.doInCallableStatement(cs); } catch (SQLException e) { throw new SlightException("执行存储过程出错。", e); } QueryRunnerTool.removeQueryRunner(); return (T) result; } private void addBatchParameters(PreparedStatement pstmt, List<Object[]> listParams, int[] types) throws SQLException { for (Object[] o : listParams) { this.addParameters(pstmt, o, types); pstmt.addBatch(); } } private void addParameters(PreparedStatement pstmt, Object[] params, int[] types) throws SQLException { if (params != null && params.length >= 1) { int i = 1; for (int j = 0; j < params.length; j++) { pstmt.setObject(i, params[j], types[j]); i++; } } }} form的onsubmit总是提交,高手进来 hibernate中Criteria查询中的问题?? 求一HQL或实现思路 求助!如何读写另一台机器上的文件? doubleselect 从数据库读出来的! javamail 与 mcafee 关于对文件加锁。 为何建立连接不对呢? ****@应用javamail创建邮件服务器,HELP****@ 解析文件目录,寻求帮助 source not found SpringMVC与structs2优缺点讨论
PreparedStatement
ResultSet
三个类够你学不?
hibernate.
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;public class DBUtil {
public static Connection getDBConnection() {
Connection conn = null;
try {
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
} public static void closeDBResources(Connection conn, PreparedStatement pstmt,
ResultSet rs) {
try {
if (conn != null) {
conn.close();
}
if (pstmt != null) {
pstmt.close();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;import com.hxw.simple.light.mvc.exception.SlightException;
import com.hxw.simple.light.mvc.jdbc.simple.SimpleCallableStatementCallbackImpl;
import com.hxw.simple.light.mvc.jdbc.simple.SimpleCallableStatementCreatorImpl;
import com.hxw.simple.light.mvc.jdbc.util.QueryRunnerTool;public class QueryTemplate { protected QueryRunner queryRunner = null;
protected String properties = ""; public QueryTemplate() {
super();
} public QueryTemplate(String properties) {
super();
this.properties = properties;
} public QueryRunner getQueryRunner() {
return this.queryRunner;
} public void setQueryRunner(QueryRunner queryRunner) {
this.queryRunner = queryRunner;
} public String getProperties() {
return this.properties;
} public void setProperties(String properties) {
this.properties = properties;
} public int queryForInt(String sql) {
return ((Long) this.queryForObject(sql, null)).intValue();
} public int queryForInt(String sql, Object[] params) {
return ((Long) this.queryForObject(sql, params)).intValue();
} public Object queryForObject(String sql) {
return this.queryForObject(sql, null);
} public Object queryForObject(String sql, Object[] params) {
return this.queryForObjectByColumnIndex(sql, 1, params);
} public Object queryForObjectByColumnIndex(String sql) {
return this.queryForObjectByColumnIndex(sql, 1, null);
} public Object queryForObjectByColumnIndex(String sql, Object[] params) {
return this.queryForObjectByColumnIndex(sql, 1, params);
} public Object queryForObjectByColumnIndex(String sql, int columnIndex,
Object[] params) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
Object object = null;
try {
if (params == null) {
object = this.queryRunner.query(sql, new ScalarHandler(
columnIndex));
} else {
object = this.queryRunner.query(sql, params, new ScalarHandler(
columnIndex));
}
} catch (SQLException e) {
throw new SlightException("查询结果失败。", e);
} finally {
QueryRunnerTool.removeQueryRunner();
}
return object;
} public Object queryForObjectByColumnName(String sql, String columnName) {
return this.queryForObjectByColumnName(sql, columnName);
} public Object queryForObjectByColumnName(String sql, String columnName,
Object[] params) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
Object object = null;
try {
if (params == null) {
object = this.queryRunner.query(sql, new ScalarHandler(
columnName));
} else {
object = this.queryRunner.query(sql, params, new ScalarHandler(
columnName));
}
} catch (SQLException e) {
throw new SlightException("查询结果失败。", e);
} finally {
QueryRunnerTool.removeQueryRunner();
}
return object;
} public Map<String, Object> queryForMap(String sql) {
return this.queryForMap(sql, null);
} @SuppressWarnings("unchecked")
public Map<String, Object> queryForMap(String sql, Object[] params) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
Map<String, Object> map = null;
try {
if (params == null) {
map = (Map<String, Object>) this.queryRunner.query(sql,
new MapHandler());
} else {
map = (Map<String, Object>) this.queryRunner.query(sql, params,
new MapHandler());
}
} catch (SQLException e) {
throw new SlightException("查询映射表结果失败。", e);
} finally {
QueryRunnerTool.removeQueryRunner();
}
return map;
} public List<Map<String, Object>> queryForMapList(String sql) {
return this.queryForMapList(sql, null);
} @SuppressWarnings("unchecked")
public List<Map<String, Object>> queryForMapList(String sql, Object[] params) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
try {
if (params == null) {
list = (List<Map<String, Object>>) this.queryRunner.query(sql,
new MapListHandler());
} else {
list = (List<Map<String, Object>>) this.queryRunner.query(sql,
params, new MapListHandler());
}
} catch (SQLException e) {
throw new SlightException("查询映射列表结果失败。", e);
} finally {
QueryRunnerTool.removeQueryRunner();
}
return list;
} public <T> T queryEntityBean(String sql, Class<T> entityClass) {
return this.queryEntityBean(sql, null, entityClass);
} @SuppressWarnings("unchecked")
public <T> T queryEntityBean(String sql, Object[] params,
Class<T> entityClass) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
Object object = null;
try {
if (params == null) {
object = this.queryRunner.query(sql, new BeanHandler(
entityClass));
} else {
object = this.queryRunner.query(sql, params, new BeanHandler(
entityClass));
}
} catch (SQLException e) {
throw new SlightException("查询实体结果失败。", e);
} finally {
QueryRunnerTool.removeQueryRunner();
}
return (T) object;
} public <T> List<T> queryForList(String sql, Class<T> entityClass) {
return this.queryForList(sql, null, entityClass);
} @SuppressWarnings("unchecked")
public <T> List<T> queryForList(String sql, Object[] params,
Class<T> entityClass) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
List<T> list = new ArrayList<T>();
try {
if (params == null) {
list = (List<T>) this.queryRunner.query(sql,
new BeanListHandler(entityClass));
} else {
list = (List<T>) this.queryRunner.query(sql, params,
new BeanListHandler(entityClass));
}
} catch (SQLException e) {
throw new SlightException("查询实体列表结果失败。", e);
} finally {
QueryRunnerTool.removeQueryRunner();
}
return list;
} /**
* 执行更新sql语句,无法保证事务不推荐使用
*
* @deprecated
**/
public int update(String sql) {
return this.update(sql, null);
} /**
* 执行更新sql语句,无法保证事务不推荐使用
*
* @deprecated
**/
public int update(String sql, Object[] params) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
int affectedRows = 0;
try {
if (params == null) {
affectedRows = this.queryRunner.update(sql);
} else {
affectedRows = this.queryRunner.update(sql, params);
}
} catch (SQLException e) {
throw new SlightException("更新数据操作结果失败。", e);
} finally {
QueryRunnerTool.removeQueryRunner();
}
return affectedRows;
} /**
* 执行更新批处理sql语句,无法保证事务不推荐使用
*
* @deprecated
**/
public int[] batchUpdate(String sql, Object[][] params) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
int affectedRows[] = null;
try {
if (params == null) {
int i = this.update(sql);
return new int[] { i };
} else {
affectedRows = this.queryRunner.batch(sql, params);
}
} catch (SQLException e) {
throw new SlightException("数据操作结果失败。", e);
} finally {
QueryRunnerTool.removeQueryRunner();
}
return affectedRows;
} public int transactionUpdate(String sql) {
return this.transactionUpdate(sql, null, null);
}
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
int affectedRows = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = this.queryRunner.getDataSource().getConnection();
pstmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
this.addParameters(pstmt, params, types);
affectedRows = pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException er) {
throw new SlightException("回滚异常。", er);
}
throw new SlightException("数据操作失败,执行回滚。", e);
} finally {
try {
DbUtils.close(pstmt);
DbUtils.close(conn);
} catch (SQLException e) {
throw new SlightException("关闭资源出错。", e);
}
QueryRunnerTool.removeQueryRunner();
}
return affectedRows;
} public int[] transactionBachUpdate(String sql, List<Object[]> listParams,
int[] types) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
int affectedRows[] = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = this.queryRunner.getDataSource().getConnection();
pstmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
this.addBatchParameters(pstmt, listParams, types);
affectedRows = pstmt.executeBatch();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException er) {
throw new SlightException("回滚异常。", er);
}
throw new SlightException("批处理数据操作结果失败,执行回滚。", e);
} finally {
try {
DbUtils.close(pstmt);
DbUtils.close(conn);
} catch (SQLException e) {
throw new SlightException("关闭资源出错。", e);
}
QueryRunnerTool.removeQueryRunner();
}
return affectedRows;
} public boolean executeCallStatement(String sql) {
return this.executeCallStatement(sql,
new SimpleCallableStatementCreatorImpl(sql),
new SimpleCallableStatementCallbackImpl());
} @SuppressWarnings("unchecked")
public <T> T executeCallStatement(String sql, CallableStatementCreator csc,
CallableStatementCallback<T> action) {
this.queryRunner = QueryRunnerTool.getQueryRunner(properties);
CallableStatement cs = null;
Connection con = null;
Object result = null;
try {
con = this.queryRunner.getDataSource().getConnection();
cs = con.prepareCall(sql);
cs = csc.createCallableStatement(cs);
result = action.doInCallableStatement(cs);
} catch (SQLException e) {
throw new SlightException("执行存储过程出错。", e);
}
QueryRunnerTool.removeQueryRunner();
return (T) result;
} private void addBatchParameters(PreparedStatement pstmt,
List<Object[]> listParams, int[] types) throws SQLException {
for (Object[] o : listParams) {
this.addParameters(pstmt, o, types);
pstmt.addBatch();
}
} private void addParameters(PreparedStatement pstmt, Object[] params,
int[] types) throws SQLException {
if (params != null && params.length >= 1) {
int i = 1;
for (int j = 0; j < params.length; j++) {
pstmt.setObject(i, params[j], types[j]);
i++;
}
}
}
}