最近写了一个jdbc工具类,拿出来献丑了,最近数据库配置都喜欢放在配置文件,我的这个也是。这么做的优势就是,当你去换数据库的时候不用改动代码,例如去现场环境部署代码,和你在测试环境开发代码用的数据库肯定不是一个。
代码如下-----------------------------------
package 。。。。。。。。。。。。util;
import java.io.IOException;
import java.io.InputStream;
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 java.util.UUID;
public class JdbcConnection {
public static Connection con = null;
public static Statement st = null;
public static ResultSet rt = null;
public static PreparedStatement ps = null;
public static String URL = null;
public static String DIALECT = null;
public static String DIRVER = null;
public static String USERNAME = null;
public static String PASWORD = null;
static
{
//根目录下创建xxxx.properties文件,做数据库的配置,所有连接配置都在配置文件内
InputStream in = DataBaseInit.class.getClassLoader().getResourceAsStream("xxxx.properties");
Properties properties = new Properties();
try
{
properties.load(in);
URL = properties.getProperty("database.url");
DIRVER = properties.getProperty("database.driver");
USERNAME = properties.getProperty("database.username");
PASWORD = properties.getProperty("database.password");
DIALECT = properties.getProperty("database.dialect");
}
catch (IOException e)
{
e.printStackTrace();
}
}
/**
* 获取connection 连接
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName(DIRVER);
return DriverManager.getConnection(URL, USERNAME,PASWORD);
}
/**
* 查询(没有参数)
* 这里不能关闭流,会报错 :关闭的 Resultset: next
* 因为通过jdbc 取出来的值还在ResultSet 里,当流关闭后
* ResultSet 也会关闭,取不出值了
*/
public static ResultSet selectExecuteQuery(String sql) {
try {
con = getConnection();
st = con.createStatement();
System.out.println("sql==="+sql);
rt = st.executeQuery(sql);
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
return rt;
}
/**
* 增删改数据(无参数)
* @param sql
*/
public static int executeUpdateStatement(String sql) {
int update = -1;
try {
con = getConnection();
st = con.createStatement();
update = st.executeUpdate(sql);
con.commit();
}catch (Exception e) {
e.printStackTrace();
}finally {
closeAll();
}
return update;
}
/**
* 有参数查询 这里同样不能关闭流
* @param sql sql 语句 有?占位符
* @param params 查询条件(参数)
* @return
*/
public static ResultSet getExecuteQueryWithParams(String sql, List<String> params) {
try {
con = getConnection();
ps = con.prepareStatement(sql);
if (null != params && params.size() > 0) {
for (int i = 0; i < params.size(); i++) {
ps.setString(i + 1, params.get(i));
}
}
rt = ps.executeQuery(sql);
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
return rt;
}
/**
* 多条插入
* 第一个占位符设置uuid
* @param sql 插入sql
* @param paraList 数据
* @return
*/
public static int batchInsertWithUuid(String sql, List<List<String>> paraList) {
int affectRowCount = 0;
try {
con = getConnection();
ps = con.prepareStatement(sql);
/// 设置不自动提交,以便于在出现异常的时候数据库回滚
con.setAutoCommit(false);
System.out.println(sql);
for (int j = 0; j < paraList.size(); j++) {
//第一个占位符匹配uuid
ps.setObject(1, getUUID());
System.out.println("paraList.get(j)==" + paraList.get(j));
for (int k = 0; k < paraList.get(j).size(); k++) {
String valuesP = paraList.get(j).get(k);
// 处理整型数据(可能会出现null的字符串)
if (valuesP == null || !("null".equals(valuesP))) {
valuesP = "";
}
ps.setObject(k+2, valuesP);
}
// 添加初始化参数
ps.addBatch();
}
int[] arr = ps.executeBatch();
con.commit();
affectRowCount = arr.length;
System.out.println("成功了插入了" + affectRowCount + "行");
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}finally {
closeAll();
}
return affectRowCount;
}
/**
* 增删改 带参数
*/
public static Integer executeUpdate(String sql, List<String> params) {
Integer result = 0;
try {
con = getConnection();
ps = con.prepareStatement(sql);
if (null != params && params.size() > 0) {
for (int i = 0; i < params.size(); i++) {
ps.setString(i + 1, params.get(i));
}
}
result= ps.executeUpdate();
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}finally {
closeAll();
}
return result;
}
/**
* 执行存储过程
*/
public boolean excecuteProduce(String sql) {
boolean result = true;
try {
con = getConnection();
CallableStatement cs = con.prepareCall("{"+sql+"}");
cs.execute();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
return false;
}finally{
close();
}
return result;
}
/**
* 关闭
*/
public static void conClose() {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close() {
if (rt != null) {
try {
rt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeAll() {
close();
conClose();
}
/**
* 获取uuid
* @return
*/
public static String getUUID() {
return UUID.randomUUID().toString();
}
/**
* 返回分页sql 这里加了一个Oracle的分页
* @param sql 未分页的sql
* @param page 当前页
* @param pageSize 每页个数
* @return
*/
public static String paging(String sql,Integer page,Integer pageSize){
StringBuffer pageSql = new StringBuffer();
pageSql.append("SELECT * FROM (SELECT a.*,ROWNUM AS rn FROM ( ");
pageSql.append(sql);
pageSql.append(" ) a WHERE ROWNUM <=" +(page*pageSize)+") WHERE rn >"+((page-1)*pageSize));
return pageSql.toString();
}
}
-----------------------------
下面就是配置文件所在的目录
下面是配置文件内容
database.driver=xxxxxxxxxxx
database.url=xxxxxxxx
database.username=xxxxxxxxxxx
database.password=xxxxxxxxxxxx
database.dialect=xxxxxxxxxxxxxx