mysql 数据库dbhelp_数据库链接助手-DBHelp

BeanPropertyRowMapper：整个单表查询

ColumnRowmapper：单列查询

MapRowMapper：多列查询

Rowmapper：结果集接口

DBHelp：数据库查询帮助类

ConnectionManager：数据库连接帮助类

1.[文件] BeanPropertyRowMapper.java ~ 1KB     下载(10)

package com.kaishengit.db.rowmapper;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

public class BeanPropertyRowMapper implements RowMapper{

private Class> clazz;

public BeanPropertyRowMapper(Class> clazz) {

this.clazz = clazz;

}

@SuppressWarnings("unchecked")

@Override

public T mapRow(ResultSet rs) throws SQLException {

ResultSetMetaData rsmd = rs.getMetaData();

Object obj = null;

try {

obj = clazz.newInstance();

for(int i = 1;i <= rsmd.getColumnCount();i++) {

String columnName = rsmd.getColumnLabel(i);

Object columnValue = rs.getObject(columnName);

setPropertyValue(obj,columnName,columnValue);

}

} catch (Exception e) {

e.printStackTrace();

}

return (T)obj;

}

private void setPropertyValue(Object obj,String columnName, Object columnValue) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {

String methodName = "set"+columnName.substring(0,1).toUpperCase()+columnName.substring(1);

Method[] methods = clazz.getMethods();

for(Method method : methods) {

if(methodName.equals(method.getName())) {

method.invoke(obj, columnValue);

break;

}

}

}

}

2.[文件] ColumnRowMapper.java ~ 312B     下载(9)

package com.kaishengit.db.rowmapper;

import java.sql.ResultSet;

import java.sql.SQLException;

public class ColumnRowMapper implements RowMapper{

@SuppressWarnings("unchecked")

@Override

public T mapRow(ResultSet rs) throws SQLException {

T t = (T) rs.getObject(1);

return t;

}

}

3.[文件] MapRowMapper.java ~ 686B     下载(9)

package com.kaishengit.db.rowmapper;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Map;

public class MapRowMapper implements RowMapper> {

@Override

public Map mapRow(ResultSet rs) throws SQLException {

Map map = new HashMap();

ResultSetMetaData rsmd = rs.getMetaData();

for(int i = 1;i <= rsmd.getColumnCount();i++) {

String columnName = rsmd.getColumnLabel(i);

Object columnValue = rs.getObject(columnName);

map.put(columnName, columnValue);

}

return map;

}

}

4.[文件] RowMapper.java ~ 192B     下载(10)

package com.kaishengit.db.rowmapper;

import java.sql.ResultSet;

import java.sql.SQLException;

public interface RowMapper {

public T mapRow(ResultSet rs) throws SQLException;

}

5.[文件] ConnectionManager.java ~ 658B     下载(13)

package com.kaishengit.db;

import java.sql.Connection;

import java.sql.DriverManager;

/**

* 关键数据库连接类

* @author kaishengit

*

*/

public class ConnectionManager {

private static final String DRIVER = "com.mysql.jdbc.Driver";

private static final String URL = "jdbc:mysql:///db_1310";

private static final String NAME = "root";

private static final String PASSWORD = "root";

public static Connection getConnection() {

Connection conn = null;

try {

Class.forName(DRIVER);

conn = DriverManager.getConnection(URL, NAME, PASSWORD);

} catch (Exception e) {

e.printStackTrace();

}

return conn;

}

}

6.[文件] DBHelp.java ~ 3KB     下载(9)

package com.kaishengit.db;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import com.kaishengit.db.rowmapper.RowMapper;

public class DBHelp {

/**

* 用来执行insert update delete语句

* @param sql

*/

public static void executeSQL(String sql,Object... params) {

Connection conn = ConnectionManager.getConnection();

PreparedStatement stat = null;

try {

stat = conn.prepareStatement(sql);

for (int i = 0; i < params.length; i++) {

stat.setObject(i+1, params[i]);

}

stat.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

} finally {

close(null,stat,conn);

}

}

/**

* 用来执行select语句，仅返回一个记录对象

* @param sql

* @param mapper

* @param params

* @return

*/

public static T queryForObject(String sql,RowMapper mapper,Object... params) {

Connection conn = ConnectionManager.getConnection();;

PreparedStatement stat = null;

ResultSet rs = null;

T obj = null;

try {

stat = conn.prepareStatement(sql);

for (int i = 0; i < params.length; i++) {

stat.setObject(i+1, params[i]);

}

rs = stat.executeQuery();

while(rs.next()) {

obj = mapper.mapRow(rs);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

close(rs,stat,conn);

}

return obj;

}

public static List queryForList(String sql,RowMapper mapper,Object... params) {

Connection conn = ConnectionManager.getConnection();;

PreparedStatement stat = null;

ResultSet rs = null;

List list = new ArrayList();

try {

stat = conn.prepareStatement(sql);

for (int i = 0; i < params.length; i++) {

stat.setObject(i+1, params[i]);

}

rs = stat.executeQuery();

while(rs.next()) {

T obj = mapper.mapRow(rs);

list.add(obj);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

close(rs,stat,conn);

}

return list;

}

private static void close(ResultSet rs,Statement stat,Connection conn) {

try {

if(rs != null) {

rs.close();

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if(stat != null) {

stat.close();

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if(conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

}