public Connection getConnection() throws SQLException {
try {
Class.forName(PropertiesUtil.getString("jdbc.driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url = PropertiesUtil.getString("jdbc.url");
String user = PropertiesUtil.getString("jdbc.username");
String password = PropertiesUtil.getString("jdbc.password");
Connection conn = (Connection) DriverManager.getConnection(url,user,password);
return conn;
}
/**
* 注:Oracle键必须是大写的
* @param sql
* @param params
* @return
* @throws SQLException
*/
public List<Map<String, Object>> query(String sql, Object[] params) throws SQLException {
Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject((i + 1), params[i]);
}
}
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int column_count = rsmd.getColumnCount();
List<String> fields = new ArrayList<String>();
for (int i = 1; i <= column_count; i++) {
String field = rsmd.getColumnName(i);
fields.add(field);
}
List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
while (rs.next()) {
Map<String, Object> record = new HashMap<String, Object>();
for (String field : fields) {
Object value = rs.getObject(field);
record.put(field, value);
}
result.add(record);
}
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
return result;
}
/**
*
* @param sql
* @param params
* @param alias 总数别名
* @return
* @throws Exception
*/
public int queryCount(String sql, Object[] params,String alias) throws Exception {
Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject((i + 1), params[i]);
}
}
ResultSet rs = ps.executeQuery();
int count = 0;
if (rs.next()) {
count = rs.getInt(alias);
}
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
return count;
}
public int update(String sql, Object[] params) throws SQLException {
Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject((i + 1), params[i]);
}
}
int count = ps.executeUpdate();
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
return count;
}
public int updateForID(String sql, Object[] params) throws SQLException {
Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject((i + 1), params[i]);
}
}
int count = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
int id = 0;
if (rs.next()) {
Object generatedKeys = rs.getObject(1);
id = StringUtil.toInt(generatedKeys);
}
//若插入多条,只返回第一条的id
if (rs!=null) {
rs.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
return id;
}
public void batchUpdate(String sql, List<Object[]> list) throws SQLException {
Connection connection = getConnection();
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(sql);
for (Object[] param : list) {
for (int i = 0; i < param.length; i++) {
ps.setObject((i + 1), param[i]);
}
ps.addBatch();
}
int[] counts = ps.executeBatch();
connection.setAutoCommit(true);
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
}
/**
* 查询mysql中的所有表
* @return
* @throws SQLException
*/
public List<String> queryTables() throws SQLException{
String sql = "show tables";
Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
List<String> result = new ArrayList<String>();
while (rs.next()) {
String table_name = rs.getString("Tables_in_house");
result.add(table_name);
}
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
return result;
}
public Map<String, String> queryTableColumn(String table) throws SQLException{
String sql = " select * from "+table+" where 1=2 ";
Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int column_count = rsmd.getColumnCount();
// List<String> fields = new ArrayList<String>();
Map<String, String> fields = new HashMap<String, String>();
for (int i = 1; i <= column_count; i++) {
String field = rsmd.getColumnName(i);
String type = rsmd.getColumnTypeName(i);
fields.put(field, type);
// System.out.println(field+"-"+type);
}
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
return fields;
}
<audio controls="controls" style="display: none;"></audio>