jdbcutil.java:251_jdbc的封装（使用参数文件）

借鉴原CSDN作者yanzi1225627的一篇：http://blog.csdn.net/yanzi1225627/article/details/26950615

作者东西写的很好，自己用的时候进行了一点修改，修改的地方是参数文件和关闭连接函数.修改部分会用红色标记

JdbcUtils.java

修改后代码如下：

/**

* Created by Administrator on 2017-12-08.

*/

import java.io.IOException;

import java.io.InputStream;

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.*;

public class JdbcUtils {

//数据库用户名

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;

//无参数构造函数,参数由配置文件中出

public JdbcUtils() {

//使用参数文件方式进行对数据库连接的判断，config.properties文件在JdbcUtil.java文件的同目录下，比如我的java文件在src文件夹里，config.properties也在src文件夹中

InputStream inputStream = getClass().getResourceAsStream("config.properties");

Properties p = new Properties();

try {

p.load(inputStream);

} catch (IOException e1) {

e1.printStackTrace();

}

DRIVER = p.getProperty("DRIVER");

URL = p.getProperty("URL");

USERNAME = p.getProperty("USERNAME");

PASSWORD = p.getProperty("PASSWORD");

// System.out.println("Url:"+p.getProperty("URL")+",USERNAME:"+p.getProperty("USERNAME")+",PASSWORD:"+p.getProperty("PASSWORD"));

try{

Class.forName(DRIVER);

System.out.println("数据库连接成功！");

}catch(Exception e){

}

}

/**

* 获得数据库的连接

* @return

*/

public Connection getConnection(){

try {

connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return connection;

}

/**

* 增加、删除、改

* @param sql

* @param params

* @return

* @throws SQLException

*/

public boolean updateByPreparedStatement(String sql, Listparams)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

pstmt.setObject(index++, params.get(i));

}

}

result = pstmt.executeUpdate();

flag = result > 0 ? true : false;

return flag;

}

/**

* 查询单条记录

* @param sql

* @param params

* @return

* @throws SQLException

*/

public Map findSimpleResult(String sql, List params) throws SQLException{

Map map = new HashMap();

int index = 1;

pstmt = connection.prepareStatement(sql);

if(params != null && !params.isEmpty()){

for(int i=0; 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

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);

}

}

return map;

}

/**查询多条记录

* @param sql

* @param params

* @return

* @throws SQLException

*/

public List> findModeResult(String sql, List params) throws SQLException{

List> list = new ArrayList>();

int index = 1;

pstmt = connection.prepareStatement(sql);

if(params != null && !params.isEmpty()){

for(int i = 0; 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 HashMap();

for(int i=0; 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;

}

/**通过反射机制查询单条记录

* @param sql

* @param params

* @param cls

* @return

* @throws Exception

*/

public T findSimpleRefResult(String sql, List params,

Class cls )throws Exception{

T resultObject = null;

int index = 1;

pstmt = connection.prepareStatement(sql);

if(params != null && !params.isEmpty()){

for(int i = 0; i

pstmt.setObject(index++, params.get(i));

}

}

resultSet = pstmt.executeQuery();

ResultSetMetaData metaData = resultSet.getMetaData();

int cols_len = metaData.getColumnCount();

while(resultSet.next()){

//通过反射机制创建一个实例

resultObject = cls.newInstance();

for(int i = 0; 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); //打开javabean的访问权限

field.set(resultObject, cols_value);

}

}

return resultObject;

}

/**通过反射机制查询多条记录

* @param sql

* @param params

* @param cls

* @return

* @throws Exception

*/

public List findMoreRefResult(String sql, List params,

Class cls )throws Exception {

List list = new ArrayList();

int index = 1;

pstmt = connection.prepareStatement(sql);

if(params != null && !params.isEmpty()){

for(int i = 0; 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

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); //打开javabean的访问权限

field.set(resultObject, cols_value);

}

list.add(resultObject);

}

return list;

}

/**

* 释放数据库连接

*/

public void releaseConn(){

if(resultSet != null){

try{

resultSet.close();

}catch(SQLException e){

e.printStackTrace();

}

}

}

/**

* 关闭所有资源，原代码中的releaseConn中关闭了结果集对象，对prepareStatement和connection没有释放。

*/

private void closeAll() {

// 关闭结果集对象

if (resultSet != null) {

try {

resultSet.close();

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

// 关闭PreparedStatement对象

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

// 关闭Connection 对象

if (connection != null) {

try {

connection.close();

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

}

/**

* @param args

*/

public static void main(String[] args) throws SQLException {

// TODO Auto-generated method stub

JdbcUtils jdbcUtils = new JdbcUtils();

jdbcUtils.getConnection();

jdbcUtils.closeAll();

/*******************增*********************/

/* String sql = "insert into userinfo (username, pswd) values (?, ?), (?, ?), (?, ?)";

List params = new ArrayList();

params.add("小明");

params.add("123xiaoming");

params.add("张三");

params.add("zhangsan");

params.add("李四");

params.add("lisi000");

try {

boolean flag = jdbcUtils.updateByPreparedStatement(sql, params);

System.out.println(flag);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}*/

/*******************删*********************/

//删除名字为张三的记录

/* String sql = "delete from userinfo where username = ?";

List params = new ArrayList();

params.add("小明");

boolean flag = jdbcUtils.updateByPreparedStatement(sql, params);*/

/*******************改*********************/

//将名字为李四的密码改了

/* String sql = "update userinfo set pswd = ? where username = ? ";

List params = new ArrayList();

params.add("lisi88888");

params.add("李四");

boolean flag = jdbcUtils.updateByPreparedStatement(sql, params);

System.out.println(flag);*/

/*******************查*********************/

//不利用反射查询多个记录

/* String sql2 = "select * from userinfo ";

List> list = jdbcUtils.findModeResult(sql2, null);

System.out.println(list);*/

}

}

config.properties文件如下，修改成自己想要连接的库参数就可以了。

DRIVER = com.mysql.jdbc.Driver

URL = jdbc:mysql://localhost:3307/test

USERNAME = Main

PASSWORD = qwertasdfg

有不懂或写错的地方请联系我。q1163949417