JdbcUtils.java
package com.product.dbutil.jdbc;
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.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class JdbcUtils {
// 定义数据库的用户名
private final String USERNAME = "root";
// 定义数据库的密码
private final String PASSWORD = "";
// 定义数据库的驱动
private final String DRIVER = "com.mysql.jdbc.Driver";
// 定义访问数据库的地址
private final String URL = "jdbc:mysql://localhost:3306/laoluo_web";
// 定义数据库链接
private Connection connection;
// 定义sql语句的执行对象
private PreparedStatement pstmt;
// 定义查询返回的结果
private ResultSet resultSet;
// 实现批处理操作的功能
private Statement stmt;
public JdbcUtils() {
try {
Class.forName(DRIVER);
System.out.println("注册驱动成功");
} catch (Exception e) {
// TODO: handle exception
}
}
// 定义获得数据库的链接
public Connection getConnection() {
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception e) {
// TODO: handle exception
}
return connection;
}
public boolean deleteByBatch(String[] sqls) throws SQLException{
boolean flag = false;
stmt = connection.createStatement();
if(sqls != null ){
for(int i = 0; i < sqls.length; i++){
stmt.addBatch(sqls[i]);
}
}
int[] count = stmt.executeBatch();
if (count != null) {
flag = true;
}
return flag;
}
/**
* 完成对数据库表的添加删除和修改的操作
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public boolean updateByPreparedStatement(String sql, List<Object> params)
throws SQLException {
boolean flag = false;
int result = -1; // 当用户执行添加删除和修改所影响数据库的行数
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));
}
}
result = pstmt.executeUpdate();
flag = result > 0 ? true : false;
return flag;
}
/**
* 查询返回单条记录
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public Map<String, Object> findSingleResult(String sql, List<Object> params)
throws SQLException {
Map<String, Object> map = new HashMap<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));
}
}
resultSet = pstmt.executeQuery(); // 返回查询结果
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount(); // 获得列的数量
while (resultSet.next()) {
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 == "") {
cols_value = "";
}
map.put(cols_name, cols_value);
}
}
return map;
}
/**
* 返回多条记录
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public List<Map<String, Object>> findMoreResult(String sql,
List<Object> 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));
}
}
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 == "") {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
// jdbc的封装可以用反射机制来封装
public <T> T findSingleRefResult(String sql, List<Object> params,
Class<T> cls) throws Exception {
T resultObject = null;
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));
}
}
resultSet = pstmt.executeQuery(); // 返回查询结果
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount(); // 获得列的数量
while (resultSet.next()) {
// 通过反射机制创建实例
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 == "") {
cols_value = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true); // 打开javabean的访问private权限
field.set(resultObject, cols_value);
}
}
return resultObject;
}
public <T> List<T> findMoreRefResult(String sql, List<Object> params,
Class<T> cls) throws Exception {
List<T> list = new ArrayList<T>();
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));
}
}
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 == "") {
cols_value = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true); // 打开javabean的访问private权限
field.set(resultObject, cols_value);
}
list.add(resultObject);
}
return list;
}
/**
* 关闭链接
*/
public void releaseConn() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.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 (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
*
* @param args
*/
// public static void main(String[] args) {
// JdbcUtils jdbcUtils = new JdbcUtils();
// jdbcUtils.getConnection();
// 测试增加数据
// String sql = "insert into userinfo(username,pswd) values(?,?)";
// List<Object> params = new ArrayList<Object>();
// params.add("shuting");
// params.add("520");
// try {
// boolean flag = jdbcUtils.updateByPreparedStatement(sql, params);
// System.out.println("插入一条数据结果:" + flag);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }finally{
// jdbcUtils.releaseConn();
// }
// 测试查询单条数据
// String sql = "select * from userinfo where id = ? ";
// List<Object> params = new ArrayList<Object>();
// params.add(1);
// try {
// Map<String, Object> map = jdbcUtils.findSingleResult(sql, params);
// System.out.println("测试查询单条数据");
// System.out.println(map);
// } catch (Exception e) {
// // TODO: handle exception
// }finally{
// jdbcUtils.releaseConn();
// }
// 测试查询多条数据
// String sql = "select * from userinfo";
// try {
// List<Map<String, Object>> list = jdbcUtils
// .findMoreResult(sql, null);
// System.out.println("测试查询多条数据");
// System.out.println(list);
// } catch (Exception e) {
// // TODO: handle exception
// }finally{
// jdbcUtils.releaseConn();
// }
// 测试反射方式查询单条数据
// String sql = "select * from userinfo where id = ? ";
// List<Object> params = new ArrayList<Object>();
// params.add(1);
// try {
// UserInfo userInfo = jdbcUtils.findSingleRefResult(sql, params,
// UserInfo.class);
// System.out.println("测试反射方式查询单条数据");
// System.out.println(userInfo);
// } catch (Exception e) {
// // TODO: handle exception
// } finally {
// jdbcUtils.releaseConn();
// }
// 测试反射方式查询多条数据
// String sql = "select * from userinfo";
// try {
// List<UserInfo> list = jdbcUtils.findMoreRefResult(sql, null,
// UserInfo.class);
// System.out.println("测试反射方式查询多条数据");
// System.out.println(list);
// } catch (Exception e) {
// // TODO: handle exception
// } finally {
// jdbcUtils.releaseConn();
// }
// }
}
userInfo是javabean 两个属性