mysql util_mysql jdbcUtil

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 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 findSingleResult(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 < 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> findMoreResult(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 < 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 map = new HashMap();

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 findSingleRefResult(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 < 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 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 < 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 params = new ArrayList();

// 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 params = new ArrayList();

// params.add(1);

// try {

// Map 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> 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 params = new ArrayList();

// 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 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 两个属性