1.需要配置数据库相关信息--配置文件(database.properties)
# JNDI Configurations
jdbc.jndi.name.0=jdbc/gp
#jdbc.jndi.name.1=jdbc/nc
jdbc.database.type=ORACLE
#tomcat
name=tomcat
java.naming.factory.initial=org.apache.naming.java.javaURLContextFactory
java.naming.factory.url.pkgs=org.apache.naming
# database setting
jdbc.driver=${jdbc.driver}
jdbc.url=${jdbc.url}
jdbc.user=${jdbc.username}
jdbc.password=${jdbc.password}
jdbc.initialSize=${jdbc.initialSize}
jdbc.minIdle=${jdbc.minIdle}
jdbc.maxWait=${jdbc.maxWait}
jdbc.maxActive=${jdbc.maxActive}
2.公共类编写
package com.dossp.ods.database;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.io.IOUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.alibaba.druid.pool.DruidDataSource;
public class DataBase {
private final static Log log = LogFactory.getLog(DataBase.class);
private static String dataType = null;
private static DruidDataSource ds;
static {
InputStream in = DataBase.class.getClassLoader().getResourceAsStream("database.properties");
try {
Properties properties = new Properties();
properties.load(in);
dataType = properties.getProperty("jdbc.database.type");
ds = new DruidDataSource();
ds.setDriverClassName(properties.getProperty("jdbc.driver"));
ds.setUsername(properties.getProperty("jdbc.user"));
ds.setPassword(properties.getProperty("jdbc.password"));
ds.setUrl(properties.getProperty("jdbc.url"));
ds.setInitialSize(Integer.parseInt(properties.getProperty("jdbc.initialSize")));
ds.setMaxActive(Integer.parseInt(properties.getProperty("jdbc.maxActive")));
ds.setMinIdle(Integer.parseInt(properties.getProperty("jdbc.minIdle")));
ds.setMaxWait(Integer.parseInt(properties.getProperty("jdbc.maxWait")));
ds.setValidationQuery("select 'x' from dual");
//设置sql监控,防火墙,日志记录
ds.setFilters("stat,log4j");
//设置每个连接上PSCache的大小
ds.setPoolPreparedStatements(true);
ds.setMaxPoolPreparedStatementPerConnectionSize(20);
//jfinalDB(ds);
} catch(Exception e) {
log.error("找不到database.properties配置文件", e);
} finally {
IOUtils.closeQuietly(in);
}
}
// public static void jfinalDB(DruidDataSource ds){
// DruidPlugin dp = new DruidPlugin(ds.getUrl(), ds.getUsername(), ds.getPassword());
// dp.setDriverClass(ds.getDriverClassName());
// ActiveRecordPlugin arp = new ActiveRecordPlugin(dp);
// arp.setDialect(new OracleDialect());
// arp.setBaseSqlTemplatePath("/sql");
// arp.getEngine().setSourceFactory(new ClassPathSourceFactory());
// arp.addSqlTemplate("/sql/all.sql");
// dp.start();
// arp.start();
// String sq = Db.getSql("printApply.findData");
// List<Record> list =Db.find(sq);
// List<Record> list = Db.find(" select * from pcard_department ");
//
// System.out.println("===================="+list.size()+"------------------------------");
// }
/**
* 获取数据库类型 MYSQL DB2 ORACLE SQLSERVER SYSBASE KINGBASE
*
* @return String
*/
public String getDataBaseType() {
return dataType;
}
/**
* 开启事务
*/
public static void startTransaction(Connection conn) {
if (conn != null) {
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
log.error("事务开启失败", e);
}
}
}
/**
* 关闭事务
*/
public static void endTransaction(Connection conn) {
if (conn != null) {
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
log.error("事务关闭失败", e);
}
}
}
/**
* 事务结束方法,调用连接的commit方法
*/
public static void commitTransaction(Connection conn) {
if (conn != null) {
try {
conn.commit();
} catch (SQLException e) {
log.error("事务提交失败", e);
}
}
}
/**
* 事务回滚方法,调用连接的rollback方法
*/
public static void rollbackTransaction(Connection conn) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e) {
log.error("事务回滚失败", e);
}
}
}
/**
* 默认获取Druid数据源
*
* @return java.sql.Connection
*/
public Connection getConnection() {
Connection conn = null;
try {
conn = ds.getConnection();
} catch (Exception e) {
log.error("获取数据库连接失败", e);
}
return conn;
}
/**
* 默认获取一个数据源
*
* @return java.sql.Connection
*/
public Connection getConnectionNoJndi() {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@ip:1521:orcl", "数据库账号", "数据库密码");
} catch (Exception e) {
log.error("数据库连接失败");
e.printStackTrace();
}
return conn;
}
/**
* 根据数据源的名称获取 DataSource
* @return javax.sql.DataSource
*/
public DataSource getDataSource() {
return ds;
}
}
3.事务处理的调用
public int addExportApply(ExpAppEntVo vo) {
int result = 0;
DataBase db = DataBaseFactory.getDataBase();
Connection conn = db.getConnection();
try {
db.startTransaction(conn); //开启事务
具体业务处理;
db.commitTransaction(conn); //事务结束方法,调用连接的commit方法
result = 1;
} catch (Exception e) {
result = 0;
db.rollbackTransaction(conn); // 事务回滚方法,调用连接的rollback方法
log.error("录入出口申请单失败。", e);
} finally {
db.endTransaction(conn); //关闭事务
DataBaseRelease.release(conn);
}
log.info("录入出口申请单成功。");
return result;
}