2,408
社区成员
发帖
与我相关
我的任务
分享
import java.io.FileInputStream;
import java.io.IOException;
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;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
public final class DbHelper {
private static Connection conn = null;
private static String driver = "";
private static String url = "";
private static String userName = "";
private static String password = "";
private static Properties pp = null;
private static FileInputStream fis = null;
private static Logger logger = Logger.getLogger(DbHelper.class);
// 加载驱动,只需要一次
static {
try {
// 从配置文件dbinfo.properties中读取配置信息
pp = new Properties();
fis = new FileInputStream("system.properties");
pp.load(fis);
driver = pp.getProperty("driver");
url = pp.getProperty("url");
userName = pp.getProperty("userName");
password = pp.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fis != null)
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
fis = null;
}
}
private DbHelper() {}
// 此方法为获取数据库连接
public static Connection getConnection() {
try {
if (conn == null || conn.isClosed()) {
conn = DriverManager.getConnection(url, userName, password);
}
} catch (SQLException ex) {
logger.error(Common.getTrace(ex));
ex.printStackTrace();
}
return conn;
}
/**
* 增删改【Add、Del、Update】
*
* @param sql
* @return int
*/
public static int executeNonQuery(String sql) {
int result = 0;
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
result = stmt.executeUpdate(sql);
} catch (SQLException ex) {
ex.printStackTrace();
logger.error(Common.getTrace(ex) + " : " + sql);
close(null, stmt, conn);
}
return result;
}
/**
* 插入返回主键
*
* @param sql
* @param obj
* @return int
*/
public static int executeInsert(String sql, Object... obj) {
int result = 0;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < obj.length; i++) {
ps.setObject(i + 1, obj[i]);
}
result = ps.executeUpdate();
if (result == 1) {//插入成功
rs = ps.getGeneratedKeys();
if (rs.next()) {
result = rs.getInt(1);
}
}
} catch (SQLException ex) {
ex.printStackTrace();
logger.error(Common.getTrace(ex) + " : " + sql);
close(null, ps, conn);
} finally {
close(ps);
}
return result;
}
……
public TrackingNumberModel getTrackingNumber(String tracking_number) throws Exception {
TrackingNumberModel model = new TrackingNumberModel();
String sql = "SELECT * FROM tracking_number WHERE tracking_number=? LIMIT 1";
ResultSet rs = DbHelper.executeQuery(sql, tracking_number);
while (rs.next()) {
model.setCustomerId(rs.getInt("customer_id"));
model.setAdminId(rs.getInt("admin_id"));
model.setAccountStatus(rs.getInt("account_status"));
//……
}
DbHelper.close(rs);
return model;
}