//创建配置文件
例如:
jdbc connection settings
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mydb2
user=root
password=123456
connection pool settings
pool.maxActive=10
pool.initSize=5
pool.maxWait=30000
public class DBUtils{
//驱动类路经
privata static String DRIVER_CLASS;
//url地址
private static String URL;
//数据库服务器登录用户名
private static String USER;
//数据库服务器登录密码
private static String PASSWORD;
static{
try {
//读取属性文件获取连接数据库相关的字符串
InputStream is = DBUtils.class.getResourceAsStream("/jdbc.properties");
//创建属性对象
Properties p = new Properties();
//加载包含属性信息的输入流
p.load(is);
//根据属性名获取属性值
DRIVER_CLASS = p.getProperty("driver");
URL = p.getProperty("url");
USER = p.getProperty("user");
PASSWORD = p.getProperty("password");
//1.加载驱动
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//获取数据库连接对象
public static synchronized Connection getConn() {
try {
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//关闭资源
public static void close(ResultSet rs, Statement stat, Connection conn) {
try {
if (rs != null)rs.close();
if (stat != null)stat.close();
if (conn != null)conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//更新数据
public static boolean exeUpdate(String sql, Object... params) {
// 获取连接
Connection conn = getConn();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
return ps.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(null, ps, conn);
}
return false;
}
//单条查询
public static <T> T queryOne(Class<T> t, String sql, Object... params) {
// 获取查询到到数据集合
List<Map<String, Object>> list = queryMaps(sql, params);
if (list.size() > 0) {
// 获取一个Map对象
Map<String, Object> map = list.get(0);
// 将map集合转换为Javabean并返回
return mapToBean(map, t);
}
return null;
}
//多条查询
public static <T> List<T> queryList(Class<T> t, String sql, Object... params) {
List<T> list = new ArrayList<T>();
// 获取所有查询的到的数据
List<Map<String, Object>> maps = queryMaps(sql, params);
// 遍历集合中每一条数据(map)
maps.forEach(m -> {
// 将map转换为Javabean
T obj = mapToBean(m, t);
// 将Javabean装入list
list.add(obj);
});
return list;
}
//将map集合转化为确定类型
private static <T> T mapToBean(Map<String, Object> map, Class<T> t) {
try {
// 根据提供的Class对象创建对应类型的Object
T obj = t.newInstance();
map.forEach((k, v) -> {
try {
// 根据Field名称获取字段对象
Field field = t.getDeclaredField(k);
// 设置字段的可访问性
field.setAccessible(true);
// 为字段设置值
field.set(obj, v);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
});
return obj;
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
// 返回对象
return null;
}
//执行相关查询返回List集合
public static List<Map<String, Object>> queryMaps(String sql, Object... params) {
// 声明动态数组用于存储每一个查询到的Map对象
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
// 获取连接
Connection conn = getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 获取预处理sql命令的对象
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
// 对指定位置的占位符填充值(预处理)
ps.setObject(i + 1, params[i]);
}
}
// 执行查询获取结果集
rs = ps.executeQuery();
// 获取结果集的元数据对象ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// 获取总查询列数
int columnCount = rsmd.getColumnCount();
// 遍历结果集
while (rs.next()) {
// 声明map集合存储每一条数据(临时缓存)
Map<String, Object> map = new HashMap<String, Object>();
// 遍历获取每一列的信息
for (int i = 1; i <= columnCount; i++) {
// 获取列名称(作为map集合的键)
String key = rsmd.getColumnName(i);
// 获取列标签
String label = rsmd.getColumnLabel(i);
// 获取列值(作为map集合的值)
Object value = rs.getObject(label);
if (Objects.nonNull(value)) {
// 将取到的每一列的列名与列值存储到map
map.put(key, value);
}
}
// 将map集合装入list
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, ps, conn);
}
return list;
}
//根据提供的查询语句以及查询参数,返回符合条件的数目
public static int queryCount(String sql, Object... params) {
Connection conn = getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
if(rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(rs, ps, conn);
}
return 0;
}
}