DBUtils.java
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DBUtils {
private static String USERNAME = "root";
private static String PASSWORD = "123456";
private static String DRIVER = "com.mysql.jdbc.Driver";
private static String URL = "jdbc:mysql://10.1.236.51:3306/test";
private static DBUtils per = null;
private Connection con = null;
private PreparedStatement pstmt = null;
private ResultSet resultSet = null;
private DBUtils() {
}
public static DBUtils getInstance() {
if (per == null) {
per = new DBUtils();
per.registeredDriver();
}
return per;
}
private void registeredDriver() {
// TODO Auto-generated method stub
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Connection getConnection() {
try {
con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("connect success！");
return con;
}
public boolean executeUpdate(String sql, List<Object> params)
throws SQLException {
boolean flag = false;
int result = -1;
pstmt = con.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
int index = 1;
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, i);
}
}
result = pstmt.executeUpdate();
flag = result > 0 ? true : false;
return flag;
}
public List<Map<String, Object>> executeQuery(String sql,
List<Object> params) throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int index = 1;
pstmt = con.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<String, Object> map = new HashMap<String, Object>();
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);
}
return list;
}
public <T> List<T> executeQueryByRef(String sql, List<Object> params,
Class<T> cls) throws Exception {
List<T> list = new ArrayList<T>();
int index = 1;
pstmt = con.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()) {
T resultObject = cls.newInstance();
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 = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true);
field.set(resultObject, cols_value);
}
list.add(resultObject);
}
return list;
}
public void closeDB() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
