dbhelper连接mysql_DBHelper（Web链接数据库）

package com.rz;

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

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class DBHelper {

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

private static final String URL="jdbc:mysql://localhost:3306/property?useUnicode=true&characterEncoding=UTF-8";//property数据库名称

private static final String USER="root";//链接数据库的账号

private static final String PASSWORD="123456";//链接数据库的密码

public Connection getConnection(){

Connection conn=null;

try {

Class.forName(DRIVER);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn=DriverManager.getConnection(URL, USER, PASSWORD);//建立数据库连接，获取连接对象conn

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

//关闭数据库

public void closeAll(ResultSet rs,PreparedStatement pstmt,Connection conn){

try {

if(rs!=null){

rs.close();

}

if(pstmt!=null){

pstmt.close();

}

if(conn!=null){

conn.close();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

//添加记录,也可以修改

public boolean excuteSql(String sql,List params){

int res=0;

Connection conn=null;

PreparedStatement pstmt = null;

ResultSet rs=null;

try {

conn=getConnection();//连接数据库，获取连接对象conn

pstmt=conn.prepareStatement(sql);//创建prepareStatement对象

if(params!=null){

for(int i=0;i

pstmt.setObject(i+1, params.get(i));//params不为空时，为每一个问号赋值

}

}

res=pstmt.executeUpdate();//执行sql语句

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

closeAll(rs, pstmt, conn);//关闭数据库

}

return res>0?true:false;

}

//查询多条记录

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

Connection conn=null;

PreparedStatement pstmt = null;

ResultSet rs=null;

conn=getConnection();

pstmt=conn.prepareStatement(sql);

List> list = new ArrayList>();

int index = 1;

pstmt = conn.prepareStatement(sql);

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

for(int i = 0; i

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

}

}

rs = pstmt.executeQuery();

ResultSetMetaData metaData = rs.getMetaData();

int cols_len = metaData.getColumnCount();

while(rs.next()){

Map map = new HashMap();

for(int i=0; i

String cols_name = metaData.getColumnName(i+1);

Object cols_value = rs.getObject(cols_name);

if(cols_value == null){

cols_value = "";

}

map.put(cols_name, cols_value);

}

list.add(map);

}

return list;

}

//查询一条记录

public Map getSingleObject(String sql,List params){

Map map = new HashMap();

Connection conn=null;

PreparedStatement pstmt = null;

ResultSet rs=null;

conn=getConnection();

try {

pstmt=conn.prepareStatement(sql);

int index = 1;

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

for(int i = 0; i

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

}

}

rs = pstmt.executeQuery();

ResultSetMetaData metaData = rs.getMetaData();

int cols_len = metaData.getColumnCount();

while(rs.next()){

for(int i=0; i

String cols_name = metaData.getColumnName(i+1);

Object cols_value = rs.getObject(cols_name);

if(cols_value == null){

cols_value = "";

}

map.put(cols_name, cols_value);

}

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

closeAll(rs,pstmt,conn);

}

return map;

}

public List executeQuery(String sql,List params,Class cls) throws Exception{

Connection conn=null;

PreparedStatement pstmt = null;

ResultSet rs=null;

List data=new ArrayList();

try {

conn=getConnection();

pstmt=conn.prepareStatement(sql);

if(params!=null){

for(int i=0;i

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

}

}

rs=pstmt.executeQuery();

ResultSetMetaData rsd=rs.getMetaData();

while(rs.next()){

T m=cls.newInstance();

for(int i=0;i

String col_name=rsd.getColumnName(i+1);

Object value=rs.getObject(col_name);

Field field=cls.getDeclaredField(col_name);

field.setAccessible(true);

field.set(m, value);

}

data.add(m);

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

closeAll(rs, pstmt, conn);

}

return data;

}

}