DBUtil.java 3.96 KB
Newer Older
zhangxiaoyan committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89
package util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * 数据库工具类
 * @author ruijie liruijie0603@126.com
 * @date 2014-11-28
 */
public class DBUtil
{
	private final static Logger logger = LoggerFactory.getLogger(DBUtil.class);
	
	private static DBUtil dbUtil;
	
	private String driverClass = "";
	private String url = "";
	private String username = "";
	private String password = "";
	
	private DBUtil() {
		driverClass = Constant.dbdriver;
		url = Constant.dburl;
		username = Constant.dbusername;
		password = Constant.dbpassword;
	}
	
	public static DBUtil newInstance() {
		if (dbUtil == null) {
			dbUtil = new DBUtil();
		}
		return dbUtil;
	}
	
	public Connection getConn() {
		Connection conn = null;
		try {
			Class.forName(driverClass);
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			logger.error(e.getMessage(), e);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		
		return conn;
	}
	
	public void insert(String sql) {
		Connection conn = getConn();
		Statement statement = null;
		try {
			statement = conn.createStatement();
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null)
					statement.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public ResultSet query(String sql) {
		Connection conn = getConn();
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement = conn.createStatement();
			rs = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
90 91 92 93 94 95 96 97 98 99 100 101 102

	public void excute(String sql) {
		Connection conn = getConn();
		Statement statement = null;
		try {
			statement = conn.createStatement();
			boolean b = statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			release(statement,conn);
		}
	}
zhangxiaoyan committed
103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
	
	public PreparedStatement getStatement(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return pstmt;
	}
	
	public void release(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			} finally {
				try {
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
					logger.error(e.getMessage(), e);
				}
			}
		}
	}
	
	
144
	public void release(Statement pstmt, Connection conn) {
zhangxiaoyan committed
145
		try {
146 147 148
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
zhangxiaoyan committed
149 150 151 152 153
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			try {
154 155 156
				if (conn != null) {
					conn.close();
					conn = null;
zhangxiaoyan committed
157 158 159 160 161
				}
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		}
162

zhangxiaoyan committed
163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196
	}
	
	public void ExcuteNonQueryBatch(List<String> sqlList) {
		Connection conn = null;
		Statement _Statement = null;
		try {

			if (sqlList != null && sqlList.size() > 0) {
				conn = getConn();
				conn.setAutoCommit(false);
				for (int i = 0; i < sqlList.size(); i++) {
					_Statement = conn.createStatement();
					
					logger.info(sqlList.get(i));
					_Statement.executeUpdate(sqlList.get(i));
					// System.out.println(sqlList.get(i));
				}
				conn.commit();
			}

		} catch (Exception e) {

			try {
				if (_Statement != null)
					_Statement.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
}