数据库连接池jdbc数据库连接类--mysql
来源:互联网 发布:巨人的陨落 知乎 编辑:程序博客网 时间:2024/06/05 16:56
DBConnection.java
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 javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import com.streetlight.utils.Macro;
/**
* @author Administrator
*
* TODO 要更改此生成的类型注释的模板,请转至 窗口 - 首选项 - Java - 代码样式 - 代码模板
*/
public class DBConnection {
static Logger log = Logger.getLogger(DBConnection.class);
public static Connection getConnectionBack() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url = "jdbc:mysql://" + Macro.DATABASE_IPADDRESS
+ ":3306/" + Macro.DATABASE_SID;
String user = Macro.DATABASE_USERNAME;
String password = Macro.DATABASE_PASSWORD;
conn = DriverManager.getConnection(url, user, password);
System.out.println("连接池失败--- 数据连接成功:getConnectionBack");
} catch (Exception e) {
log.info("没有得到数据库连接:getConnectionBack");
e.printStackTrace();
return getConnectionLast();
}
return conn;
}
public static Connection getConnection() {
try {
Context ctx = new InitialContext();
if (ctx == null) {
throw new Exception("Connection: No JNDI Context!");
}
DataSource ds = (DataSource) ctx
.lookup("java:comp/env/jdbc/streetlightDB");
if (ds != null) {
log.info("从连接池中得到数据");
Connection conn = ds.getConnection();
return conn;
} else {
throw new Exception(
"*********** JNDI Datasource not found !!!***********");
}
} catch (Exception ex) {
log.info("没连接池连接数据库失败!!!:getConnection");
ex.printStackTrace();
return getConnectionBack();
}
}
public static Connection getConnectionLast() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
// 2 连接数据库
conn = DriverManager.getConnection(
"jdbc:mysql://192.168.1.8:3306/streetlight","root",
"123");
System.out.println("数据库连接成功:getConnectionLast");
} catch (Exception e) {
log.info("last没有得到数据库连接");
e.printStackTrace();
}
return conn;
}
public static void free(ResultSet rs, PreparedStatement ps, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public static void free(ResultSet rs, Statement ps, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public static void free(Statement st, Connection conn) {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
通过数据库连接类操作数据库:
UserOperationLogDao.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import com.streetlight.conn.DBConnection;
import com.streetlight.log.dto.UserOpertionDto;
import com.streetlight.utils.BaseUtils;
import com.streetlight.utils.Page;
public class UserOperationLogDao {
public UserOperationLogDao() {
}
private static Logger log = Logger.getLogger(UserOperationLogDao.class);
private Connection con = null,con1 = null;
private PreparedStatement ps = null,ps1=null;
private ResultSet rs = null,rs1 = null;
private Page page = new Page();
public boolean operationLog(UserOpertionDto dto, Connection con) {
try {
String sql = "insert into useroperationlog(op_userid,op_username,op_context,op_model,Op_ipaddress) values(?,?,?,?,?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, dto.getUserId());
ps.setString(2, dto.getUserName());
ps.setString(3, dto.getContext());
ps.setString(4, dto.getModel());
ps.setString(5, dto.getIpAddress());
ps.executeUpdate();
log.info("执行日志添加操作!");
} catch (Exception ex) {
log.info("操作useroperationlog发生错误!");
ex.printStackTrace();
}
return true;
}
public boolean operationLog(String userId, String userName, String context,
String model, String ip) {
try {
String sql = "insert into useroperationlog(op_userid,op_username,op_context,op_model,Op_ipaddress) values(?,?,?,?,?)";
con = DBConnection.getConnection();
ps = con.prepareStatement(sql);
ps.setString(1, userId);
ps.setString(2, userName);
ps.setString(3, context);
ps.setString(4, model);
ps.setString(5, ip);
ps.executeUpdate();
log.info("执行日志添加操作!");
} catch (Exception ex) {
log.info("操作useroperationlog发生错误!");
ex.printStackTrace();
} finally {
DBConnection.free(null, ps, con);
}
return true;
}
public Page selectOperationLogList(String userName, int isPage,
String pageNum) {
List<UserOpertionDto> list = new ArrayList<UserOpertionDto>();
try {
StringBuffer sql = new StringBuffer(
"select * from useroperationlog ");
if (!"".equals(userName) && userName != null) {
//查询userinfo表,并取出表中userid
String[] userId =new String[1];
con1 = DBConnection.getConnection();
ps1 = con1.prepareStatement("select userid from userinfo where username like '%"+userName+"%' or userloginname like '%"+userName+"%' ");
rs1 = ps1.executeQuery();
while (rs1.next()){userId[0]=rs1.getString("userid");}
sql.append(" where op_userid like '%").append(userId[0])
.append("%' ");
DBConnection.free(rs, ps, con);
/*
sql.append(" where op_username like '%").append(userName)
.append("%' ");
*/
}
String sqlCount=sql.toString();
sql.append(" order by op_date desc");
log.info("操作记录sql==>>"+sql);
con = DBConnection.getConnection();
if (isPage == 1) {
page
.setTotalCount(BaseUtils.getTotalCount(sqlCount,
con));
sql.append(" limit ").append(
(Integer.parseInt(pageNum) - 1) * page.getRowsPage())
.append(",").append(page.getRowsPage());
}
ps = con.prepareStatement(sql.toString());
rs = ps.executeQuery();
while (rs.next()) {
UserOpertionDto dto = new UserOpertionDto();
dto.setUserId(rs.getString("op_userid"));
dto.setContext(rs.getString("op_context"));
dto.setModel(rs.getString("op_model"));
dto.setUserName(rs.getString("op_username"));
dto.setIpAddress(rs.getString("op_ipaddress"));
dto.setOperDate(rs.getString("op_date") == null ? "" : rs
.getString("op_date").substring(0, 16));
list.add(dto);
}
page.setList(list);
page.setCurrPage(Integer.parseInt(pageNum));
} catch (Exception ex) {
log.info("查询操作记录信息错误...");
ex.printStackTrace();
} finally {
DBConnection.free(rs, ps, con);
}
return page;
}
}
- 数据库连接池jdbc数据库连接类--mysql
- JDBC mysql数据库连接池配置
- servlet JDBC mysql数据库连接池
- Jdbc-MySQL数据库连接
- jdbc数据库连接mysql
- MySQL Myeclipse jdbc数据库连接
- MySQL数据库连接JDBC
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池
- Tomcat5配置Mysql JDBC数据库连接池(转)
- Tomcat5配置Mysql JDBC数据库连接池
- check_mk linux agent安装
- 项目常见错误
- 全面分析Java的垃圾回收机制
- web安全测试方法
- (总结)Ubuntu Server 12.04高速apt-get源
- 数据库连接池jdbc数据库连接类--mysql
- 生僻字组件
- 安全漏洞分类
- ARC指南2 - ARC的开启和禁止
- C++ 解决error LNK2001: unresolved external symbol 的办法
- POJ1185——炮兵阵地(状态压缩DP)
- float与double的范围和精度
- android studio 在win 7下的安装
- Java集合简述(一)