java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
//////////////////////////////////////////////////////////////////////////////////
// 注意jar包版本,新版(应该是6.0以上)的driverClass格式不一样 //
// old :com.mysql.jdbc.Driver //
// 6.0+:com.mysql.cj.jdbc.Driver //
// url连接中需要带上serverTimezone=UTC //
//////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////
// jdbc_driver=com.mysqljdbc.Driver //
// jdbc_url=jdbc:mysql://localhost:3306/log?characterEncoding=utf-8 //
// jdbc_username=root //
// jdbc_password=admin //
//////////////////////////////////////////////////////////////////////////////////
/**
* @author lnexin@aliyun.com
*
* <li>
* 1. 执行单条: executeStat(String sql) /executePstamt(String sql, List params)----:return boolean
* <li>
* 2. 批量执行: batchExecutePstamt(String sql, List<List> lists)----:return boolean 可以更改为受影响数量
* <li>
* 3. 单条记录查询 : selectOne(String sql, List params)----:return map 一条记录的集合
* <li>
* 4. 多条查询 : selectMore(String sql, List params)----:return List(map);
* <li>
* 5. 列名查询: selectColumns(String tanleN)----:return list(string);
* <li>
* 6. 建表语句 : createTableSql(String tableName, List<String> fields, String engine)----:return string;
* <li>
* 7. 插入语句 : getInsertSql(String tableName, List<String> fields)----:return string;
* <li>
* 8. 验证表是否存在: validateTableExist(String tableName)----:return boolean
* <li>
* 9. 关闭连接: releaseConn()
*/
public class DataBaseUtil {
// 数据库配置文件地址
private static final String CONFIG_PATH = "config.properties"
;
private static final String DB_DRIVER;
private static final String DB_URL;
private static final String DB_USERNAME;
private static final String DB_PASSWORD;
private Connection connection;
private Statement statement;
private PreparedStatement pstmt;
private ResultSet resultSet;
static {
Properties p =
new Properties();
try {
// 两种方式任选其一
// InputStream u = DataBaseUtil.class.getResourceAsStream("config.properties");
// p.load(u);
p.load(
new FileInputStream(
new File(CONFIG_PATH)));
} catch (FileNotFoundException e) {
System.err.println("Not Database configuration files !"
);
e.printStackTrace();
} catch (IOException e) {
System.err.println("Read configuration file failure!"
);
e.printStackTrace();
}
DB_DRIVER = p.getProperty("jdbc_driver"
);
DB_URL = p.getProperty("jdbc_url"
);
DB_USERNAME = p.getProperty("jdbc_username"
);
DB_PASSWORD = p.getProperty("jdbc_password"
);
}
public DataBaseUtil() {
}
/**
* 使用配置文件中的连接信息
*
* @return
*/
public Connection getConn() {
try {
Class.forName(DB_DRIVER);
connection =
DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
} catch (SQLException e) {
System.err.println("Get connection failure！" +
e);
} catch (ClassNotFoundException e) {
System.err.println("Load DB Driver failure!" +
e);
}
return connection ==
null ? connection :
null;
}
/////////////////////////////////////////////////////////////////////////////////////
/**
* 使用非配置文件的连接信息
*
* @param driver
* 数据路驱动
* @param url
* 数据库连接
* @param username
* 数据库用户名
* @param password
* 数据库密码
* @return
*/
public Connection getConn(String driver, String url, String username, String password) {
try {
Class.forName(driver);
connection =
DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
System.err.println("Get connection failure！" +
e);
releaseConn();
} catch (ClassNotFoundException e) {
System.err.println("Load DB Driver failure!" +
e);
}
return connection ==
null ? connection :
null;
}
//////////////////////////////////////////////////////////////////////////////////////
public boolean executeStat(String sql)
throws SQLException {
boolean flag =
false;
statement =
connection.createStatement();
flag =
statement.execute(sql);
closeStmt();
return flag;
}
public boolean executePstamt(String sql, List params)
throws SQLException {
boolean flag =
false;
int result = -1
;
pstmt =
connection.prepareStatement(sql);
int index = 1
;
if (params !=
null && !
params.isEmpty()) {
for (
int i = 0; i < params.size(); i++
) {
pstmt.setObject(index++
, params.get(i));
}
}
result =
pstmt.executeUpdate();
closePstmat();
flag = result > 0 ?
true :
false;
return flag;
}
public boolean batchExecutePstamt(String sql, List<List> lists)
throws SQLException {
connection.setAutoCommit(false);
boolean flag =
false;
int resultNum = 0
;
pstmt =
connection.prepareStatement(sql);
if (lists !=
null && !
lists.isEmpty()) {
for (List<Object>
cList : lists) {
if (cList ==
null || cList.isEmpty())
continue;
for (
int i = 0; i < cList.size(); i++
) {
pstmt.setObject(i + 1
, cList.get(i));
}
pstmt.addBatch();
}
int[] resNum =
pstmt.executeBatch();
connection.commit();
resultNum +=
resNum.length;
}
closePstmat();
flag = resultNum > 0 ?
true :
false;
return flag;
}
public Map selectOne(String sql, List params)
throws SQLException {
Map map =
new LinkedHashMap();
int index = 1
;
pstmt =
connection.prepareStatement(sql);
if (params !=
null && !
params.isEmpty()) {
for (
int i = 0; i < params.size(); i++
) {
pstmt.setObject(index++
, params.get(i));
}
}
resultSet =
pstmt.executeQuery();
ResultSetMetaData metaData =
resultSet.getMetaData();
int col_len =
metaData.getColumnCount();
while (resultSet.next()) {
for (
int i = 0; i < col_len; i++
) {
String cols_name = metaData.getColumnName(i + 1
);
Object cols_value =
resultSet.getObject(cols_name);
if (cols_value ==
null) {
cols_value = ""
;
}
map.put(cols_name, cols_value);
}
}
closeResultSet();
closePstmat();
return map;
}
public List<Map> selectMore(String sql, List params)
throws SQLException {
List<Map> list =
new ArrayList<Map>
();
int index = 1
;
pstmt =
connection.prepareStatement(sql);
if (params !=
null && !
params.isEmpty()) {
for (
int i = 0; i < params.size(); i++
) {
pstmt.setObject(index++
, params.get(i));
}
}
resultSet =
pstmt.executeQuery();
ResultSetMetaData metaData =
resultSet.getMetaData();
int cols_len =
metaData.getColumnCount();
while (resultSet.next()) {
Map map =
new LinkedHashMap();
for (
int i = 0; i < cols_len; i++
) {
String cols_name = metaData.getColumnName(i + 1
);
Object cols_value =
resultSet.getObject(cols_name);
if (cols_value ==
null) {
cols_value = ""
;
}
map.put(cols_name, cols_value);
}
list.add(map);
}
closeResultSet();
closePstmat();
return list;
}
public List<String> selectColumns(String tanleN)
throws SQLException {
DatabaseMetaData data;
List<String> result =
new ArrayList<>
();
data =
connection.getMetaData();
ResultSet columns = data.getColumns(
null,
null, tanleN,
null);
while (columns.next()) {
result.add(columns.getString("COLUMN_NAME"
));
}
columns.close();
return result;
}
public static String getInsertSql(String tableName, List<String>
fields) {
StringBuffer sb =
new StringBuffer();
sb.append("INSERT INTO ").append(tableName).append(" ("
);
// 加入ignore 用来避免插入重复记录
// sb.append("INSERT INTO IGNORE ").append(tableName).append(" (");
StringBuffer field =
new StringBuffer(" ("
);
StringBuffer value =
new StringBuffer(" ("
);
int sign = 0
;
for (String f : fields) {
if (sign > 0
) {
field.append(","
);
value.append(","
);
}
field.append("`").append(f).append("`"
);
value.append("?"
);
}
field.append(") "
);
value.append(") "
);
sb.append(field).append(" values ").append(value).append(";"
);
return sb.toString();
}
/**
*
* @param tableName
* @param fields
* Map<String,String> 结构为[字段名,字段数据类型],如:[{name=varchar(64),lastname=varchar(64)}]
* @param engine
* :InnoDB/MyISAM
* @return create sql
*/
public static String createTableSql(String tableName, Map<String, String>
fields, String engine) {
StringBuffer sb =
new StringBuffer();
sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` ("
);
int sign = 0
;
for (Entry<String, String>
entry : fields.entrySet()) {
String field =
entry.getKey();
String type =
entry.getValue();
if (sign > 0) sb.append(","
);
sb.append("`").append(field).append("` "
);
sb.append(type).append(" null default null"
);
}
sb.append(")"
);
sb.append(" COLLATE=‘utf8_general_ci‘ ").append(" ENGINE=").append(engine).append(";"
);
return sb.toString();
}
/**
*
* @param tableName
* @param fields
* 一个list<string> 的字段列表
* @param engine
* 数据库引擎类型
* @param defaultFieldType
* 默认数据字段的类型,如:varchar(64)
* @return
*/
public static String createTableSql(String tableName, List<String>
fields, String engine, String defaultFieldType) {
StringBuffer sb =
new StringBuffer();
sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` ("
);
int sign = 0
;
for (String field : fields) {
if (sign > 0) sb.append(","
);
sb.append("`").append(field).append("` "
);
sb.append(defaultFieldType).append(" null default null"
);
}
sb.append(")"
);
sb.append(" COLLATE=‘utf8_general_ci‘ ").append(" ENGINE=").append(engine).append(";"
);
return sb.toString();
}
public boolean validateTableExist(String tableName) {
boolean flag =
false;
try {
DatabaseMetaData meta =
connection.getMetaData();
String type[] = { "TABLE"
};
ResultSet rs = meta.getTables(
null,
null, tableName, type);
flag =
rs.next();
} catch (SQLException e) {
System.err.println("ERROR! validateTableExist failure! msg:[" + e + "]"
);
e.printStackTrace();
}
return flag;
}
public void releaseConn() {
if (resultSet !=
null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt !=
null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection !=
null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private void closeResultSet() {
if (resultSet !=
null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private void closePstmat() {
if (pstmt !=
null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private void closeStmt() {
if (statement !=
null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Mysqlutil.JDBCutil.Dtabaseutil数据库操作工具类[批量操作]
标签：exec hash 执行 zone last lex java ali param
