package com.jplus.util;
import java.io.InputStream;
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;
import java.util.Properties;
/**
* @title 链接数据库
* @author 刘康
* @Time 2016/10/19
*/
public class JdbcUtil {
private static String USERNAME;
private static String PASSWORD;
private static String DRIVER;
private static String URL;
private Connection connection;
private PreparedStatement pstmt;
private ResultSet resultSet;
static{
loadConfig();
}
/**
* 加载数据库配置信息,并给相关的属性赋值
*/
public static void loadConfig(){
try {
InputStream inStream = JdbcUtil.class
.getResourceAsStream("/jdbcinfo.properties");
Properties prop = new Properties();
prop.load(inStream);
USERNAME = prop.getProperty("jdbc.username");
PASSWORD = prop.getProperty("jdbc.password");
DRIVER = prop.getProperty("jdbc.driver");
URL = prop.getProperty("jdbc.url");
inStream.close();
} catch (Exception e) {
throw new RuntimeException("读取数据库配置文件异常!", e);
}
}
public JdbcUtil() {
}
/**
* 获取数据库链接
* @return 链接
*/
public Connection getConnection() {
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception e) {
throw new RuntimeException("get connection error!");
}
return connection;
}
/**
* 执行更新操作
* @param sql sql语句
* @param params 执行参数
* @return 执行结果
* @throws SQLException
*/
public boolean updateByPreparedStatement(String sql, List<?> params)
throws SQLException {
boolean flag = false;
int result = -1;// 用来看结果是否成功
pstmt = connection.prepareStatement(sql);
int index = 1;
// 填充sql语句中的占位符
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
// System.out.println(pstmt);//检测语句
}
}
result = pstmt.executeUpdate();
flag = result > 0 ? true : false;
return flag;//返回是否导入成功
}
/**
* 执行查询操作
* @param sql sql语句
* @param params 执行参数
* @return
* @throws SQLException
*/
public List<Map<String, Object>> findResult(String sql, List<?> params)
throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
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));
// System.out.println(pstmt);//sql语句检验
}
}
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 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();
}
}
}
public static void main(String[] args) {
JdbcUtil jdbcUtil = new JdbcUtil();
Connection conn = jdbcUtil.getConnection();
if(conn != null) {
System.out.println("链接数据库成功");
}
}
}
import java.io.InputStream;
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;
import java.util.Properties;
/**
* @title 链接数据库
* @author 刘康
* @Time 2016/10/19
*/
public class JdbcUtil {
private static String USERNAME;
private static String PASSWORD;
private static String DRIVER;
private static String URL;
private Connection connection;
private PreparedStatement pstmt;
private ResultSet resultSet;
static{
loadConfig();
}
/**
* 加载数据库配置信息,并给相关的属性赋值
*/
public static void loadConfig(){
try {
InputStream inStream = JdbcUtil.class
.getResourceAsStream("/jdbcinfo.properties");
Properties prop = new Properties();
prop.load(inStream);
USERNAME = prop.getProperty("jdbc.username");
PASSWORD = prop.getProperty("jdbc.password");
DRIVER = prop.getProperty("jdbc.driver");
URL = prop.getProperty("jdbc.url");
inStream.close();
} catch (Exception e) {
throw new RuntimeException("读取数据库配置文件异常!", e);
}
}
public JdbcUtil() {
}
/**
* 获取数据库链接
* @return 链接
*/
public Connection getConnection() {
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception e) {
throw new RuntimeException("get connection error!");
}
return connection;
}
/**
* 执行更新操作
* @param sql sql语句
* @param params 执行参数
* @return 执行结果
* @throws SQLException
*/
public boolean updateByPreparedStatement(String sql, List<?> params)
throws SQLException {
boolean flag = false;
int result = -1;// 用来看结果是否成功
pstmt = connection.prepareStatement(sql);
int index = 1;
// 填充sql语句中的占位符
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
// System.out.println(pstmt);//检测语句
}
}
result = pstmt.executeUpdate();
flag = result > 0 ? true : false;
return flag;//返回是否导入成功
}
/**
* 执行查询操作
* @param sql sql语句
* @param params 执行参数
* @return
* @throws SQLException
*/
public List<Map<String, Object>> findResult(String sql, List<?> params)
throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
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));
// System.out.println(pstmt);//sql语句检验
}
}
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 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();
}
}
}
public static void main(String[] args) {
JdbcUtil jdbcUtil = new JdbcUtil();
Connection conn = jdbcUtil.getConnection();
if(conn != null) {
System.out.println("链接数据库成功");
}
}
}