package cn.com.shxt.dbutils;
?
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
?* @author ccitKing
?* @日期 : 2012-6-13
?* @描述 : 连接数据库工具类
?* @版本 : v0.1
?*/
public class DBUtil {
?private Connection conn;
?private PreparedStatement statement;
?private Statement stmt;
?private ResultSet rs = null;
?/** 连接数据库URL thin连接方式 ： 协议＋主机地址＋资源名称 */
?private String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
?/** 用户名 */
?private String userName = "shxt";
?/** 密码 */
?private String password = "shxt";
?/**
? * 静态代码块
? */
?static {
??try {
???// 加载驱动
???Class.forName("oracle.jdbc.driver.OracleDriver");
??} catch (ClassNotFoundException e) {
???e.printStackTrace();
??}
?}
?/**
? * 获得Connection
? */
?public Connection getConn() {
??try {
???conn = DriverManager.getConnection(url, userName, password);
???return conn;
??} catch (Exception e) {
???e.printStackTrace();
???return null;
??}
?}
?/**
? * 获取执行sql的Statement对象
? * */
?public Statement getStmt() {
??try {
???conn = getConn();
???stmt = conn.createStatement();
???return stmt;
??} catch (Exception e) {
???e.printStackTrace();
???return null;
??}
?}
?/**
? * 使用Statement操作--查询
? *
? * @param sql
? * @return ResultSet
? * */
?
?public ResultSet query(String sql) {
??try {
???rs = getStmt().executeQuery(sql);
???System.out.println("---------------------1---------------");
???return rs;
??} catch (Exception e) {
???e.printStackTrace();
???System.out.println("---------------------2---------------");
???return null;
??} finally {
???System.out.println("---------------------3---------------");
???//colse();
??}
?}
?/**
? * 执行数据更新的方法
? * @param sql
? *??????????? String 的SQL语句
? * @return Integer 类型的数据 表示受影响的行数
? */
?public int update(String sql) {
??try {
???return getStmt().executeUpdate(sql);
??} catch (Exception e) {
???e.printStackTrace();
???return -1;
??} finally {
???colse();
??}
?}
?/**
? * 查询数据 返回的是一个ArrayList对象，对象中的每一个元素是一个HashMap对象
? *
? * @param sql
? *??????????? String 查询语句
? * @return ArrayList 结果集
? * @throws Exception
? */
?public List<Map<String, String>> queryByList(String sql) {
??try {
???List<Map<String, String>> list = new ArrayList<Map<String, String>>();
???rs = getStmt().executeQuery(sql);
???// 得到结果集(rs)的结构信息，比如字段数、字段名等
???ResultSetMetaData rsmd = rs.getMetaData();
???// 得到数据集的列数
???int columncount = rsmd.getColumnCount();
???while (rs.next()) {
????Map<String, String> map = new HashMap<String, String>();
????for (int i = 0; i < columncount; i++) {
?????String key = rsmd.getColumnName(i + 1);
?????String value = rs.getString(key);
?????map.put(key, value);
????}
????list.add(map);
???}
???return list;
??} catch (Exception e) {
???e.printStackTrace();
???return null;
??} finally {
???colse();
??}
?}
?/**
? * 批量更新
? *
? * @param sqls
? *??????????? String 查询语句数组
? * @return int[] 计数组成的数组
? * @throws Exception
? */
?public int[] updateBatch(String[] sqls) {
??stmt = getStmt();
??try {
???startTransaction();
???for (String sql : sqls) {
????stmt.addBatch(sql);// 将所有的SQL语句添加到Statement中
???}
???int[] batchResultList = stmt.executeBatch();
???endTransaction();
???return batchResultList; // 返回更新计数组成的数组。
??} catch (Exception e) {
???e.printStackTrace();
??} finally {
???colse();
??}
??return null;
?}
?/**
? * 预处理语句查询结果并封装到List.
? * @param
? * ?sql语句.
? * @param 可变参数.
? * @return
? * 返回List.
? * @throws SQLException when the query execution failed.
? */
?public List<Map<String, String>> queryForList(String query,String... params) throws SQLException {
??ResultSet resultSet = null;
??List<Map<String, String>> result;
??try {
???statement = getConn().prepareStatement(query);
???for (int i = 0; i < params.length; i++) {
????statement.setString(i + 1, params[i]);
???}
???resultSet = statement.executeQuery();
???result = new ArrayList<Map<String, String>>();
???while (resultSet.next()) {
????Map<String, String> rowMap = new HashMap<String, String>();
????for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
?????rowMap.put(resultSet.getMetaData().getColumnLabel(i),
???????????? resultSet.getString(i));
????}
????result.add(rowMap);
???}
??} finally {
???colse();
??}
??return result;
?}
?/**
? * 结束事务
? * */
?public void endTransaction() throws Exception {
??if (conn == null) {
???conn = getConn();
??}
??try {
???conn.commit();
???conn.close();
??} catch (Exception e) {
???conn.rollback();
??}
?}
?/**
? * 开启事务
? * */
?public void startTransaction() throws SQLException {
??if (conn == null) {
???conn = getConn();
??}
??try {
???conn.setAutoCommit(false);// 设置连接不自动提交，即用该连接进行的操作都不更新到数据库
??} catch (SQLException e) {
???e.printStackTrace();
??}
?}
?/**
? * 数据库关闭操作
? *
? * 要按照顺序关 rs-->stmt-->conn
? * */
?public void colse() {
??if (rs != null) {
???try {
????rs.close();
???} catch (Exception e) {
????System.out.println("关闭结果集对象时出错！");
????e.printStackTrace();
???}
??}
??if (stmt != null) {
???try {
????stmt.close();
???} catch (Exception e) {
????
????System.out.println("关闭语句对象时出错！");
????e.printStackTrace();
???}
??}
??if (conn != null) {
???try {
????conn.close();
???} catch (Exception e) {
????System.out.println("关闭连接对象时出错！");
????e.printStackTrace();
???}
??}
?}
?
?/**
? * 主测试方法
? * @param args
? */
?public static void main(String[] args) {
??DBUtil dbUtil=new DBUtil();
??String sql=" insert into user_info(user_id,user_name,user_password) values(user_seq.nextval,'张三,'123') ";
??int res=dbUtil.update(sql);
??System.out.println("本操作影响记录的行数："+res);
?}
?
}