java mySQLDBHelper类

ConnectionManager.java

import java.sql.*;
public class ConnectionManager {
	public final static String DRIVER = "com.mysql.jdbc.Driver"; // 数据库驱动
	public final static String URL = "jdbc:mysql://192.168.xx.xxx:3306/xxx"; // URL
	public final static String DBUSER = "xxxx"; // 数据库用户名
	public final static String DBPASS = "xxxx"; // 数据库密码

	/*
	 * 得到数据库连接
	 * 
	 * @throws Exception
	 * 
	 * @return 数据库连接对象
	 */
	public static Connection getConnection() {
		Connection dbConnection = null;
		try {
			// 把JDBC驱动类装载入Java虚拟机中
			Class.forName(DRIVER);
			// 加载驱动，并与数据库建立连接
			dbConnection = DriverManager.getConnection(URL,DBUSER, DBPASS);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dbConnection;
	}

	public static void closeConnection(Connection dbConnection) {
		try {
			if (dbConnection != null && !dbConnection.isClosed()) {
				dbConnection.close();
			}
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		}
	}
	public static void closeResultSet(ResultSet res){
		try{
			if(res != null){
				res.close();
				res = null;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	public static void closeStatement(PreparedStatement pStatement){
		try{
			if(pStatement != null){
				pStatement.close();
				pStatement = null;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
}

DBHelperMySQL.java

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public final class DBHelperMySQL {
	/**
	 * 用于执行语句（eg：insert语句，update语句，delete语句）
	 * 
	 * @param SQL语句
	 * @param 参数集合
	 * @return 影响的行数
	 */
	public static int ExecuteNonQuery(String cmdtext, Object[] params)
			throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			pstmt = conn.prepareStatement(cmdtext);
			PrepareCommand(pstmt, params);
			return pstmt.executeUpdate();
		} catch (SQLException sqlE) {
			throw new Exception("ExecuteNonQuery方法出错:" + sqlE.getMessage());
		} finally {
			ConnectionManager.closeStatement(pstmt);
			ConnectionManager.closeConnection(conn);
		}
	}

	/**
	 * 用于查找单条数据（Select * from where id =0）
	 * 
	 * @param 查询语句
	 * @param 查询参数
	 * @param 返回类型
	 * @return 查询实体对象
	 * @throws 异常
	 */
	public static Object getModel(String cmdtext, Object[] params,
			Class<?> classObj) throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getConnection();
			pstmt = conn.prepareStatement(cmdtext);
			PrepareCommand(pstmt, params);
			rs = pstmt.executeQuery();
			Object obj = classObj.newInstance();
			Field[] fields = classObj.getDeclaredFields();
			if (rs.next()) {
				for (Field item : fields) {
					if (!item.getName().equals("serialVersionUID")) {
						System.out.println(rs.getString(item.getName()));
						item.setAccessible(true);
						Class<?> type = item.getType();
						if (type.isPrimitive() == true) {
							item.set(
									obj,
									convert(item.getType().toString(),
											rs.getString(item.getName())));
						} else {
							if (item.getType().getName()
									.equals("java.lang.String")) {
								item.set(obj, rs.getString(item.getName()));
							} else if (item.getType().getName()
									.equals("java.util.Date")) {
								Date date = Timestamp.valueOf(rs.getString(item
										.getName()));
								item.set(obj, date);
							} else {
								Method m = type.getMethod("valueOf",
										String.class);
								item.set(
										obj,
										m.invoke(null,
												rs.getString(item.getName())));
							}
						}
						item.setAccessible(false);
					}
				}
				return obj;
			}
			return null;
		} catch (SQLException sqlE) {
			System.out.println(sqlE.getMessage());
			throw new Exception("ExecuteNonQuery方法出错:" + sqlE.getMessage());
		} finally {
			ConnectionManager.closeResultSet(rs);
			ConnectionManager.closeStatement(pstmt);
			ConnectionManager.closeConnection(conn);
		}
	}

	/**
	 * 用于获取结果集语句（eg：selete * from table）
	 * 
	 * @param 查询语句
	 * @param 查询参数
	 * @param 返回类型
	 * @return 查询实体对象
	 * @throws 异常
	 */
	public static List<?> ExecuteReader(String cmdtext, Object[] params,
			Class<?> classObj) throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getConnection();
			pstmt = conn.prepareStatement(cmdtext);
			PrepareCommand(pstmt, params);
			rs = pstmt.executeQuery();
			@SuppressWarnings({ "rawtypes", "unchecked" })
			List<Object> list = new ArrayList();
			Field[] fields = classObj.getDeclaredFields();
			while (rs.next()) {
				Object obj = classObj.newInstance();
				for (Field item : fields) {
					if (!item.getName().equals("serialVersionUID")) {
						item.setAccessible(true);
						Class<?> type = item.getType();
						try {
							if (type.isPrimitive() == true) {
								item.set(
										obj,
										convert(item.getType().toString(),
												rs.getString(item.getName())));
							} else {
								if (item.getType().getName()
										.equals("java.lang.String")) {
									item.set(obj, rs.getString(item.getName()));
								} else if (item.getType().getName()
										.equals("java.util.Date")) {
									Date date = Timestamp.valueOf(rs
											.getString(item.getName()));
									item.set(obj, date);
								} else {
									Method m = type.getMethod("valueOf",
											String.class);
									item.set(obj, m.invoke(null,
											rs.getString(item.getName())));
								}
							}
						} catch (Exception e) {
							continue;
						}
						item.setAccessible(false);
					}
				}
				System.out.println(obj.toString());
				list.add(obj);
			}
			return list;
		} catch (SQLException sqlE) {
			System.out.println(sqlE.getMessage());
			throw new Exception("ExecuteNonQuery方法出错:" + sqlE.getMessage());
		} finally {
			ConnectionManager.closeResultSet(rs);
			ConnectionManager.closeStatement(pstmt);
			ConnectionManager.closeConnection(conn);
		}
	}

	/**
	 * 用于获取单字段值语句（用名字指定字段）
	 * 
	 * @param cmdtext
	 *            SQL语句
	 * @param name
	 *            列名
	 * @param params
	 *            OracleParameter[]
	 * @return Object
	 * @throws Exception
	 */
	public static Object ExecuteScalar(String cmdtext, String name,
			Object[] params) throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;

		try {
			conn = ConnectionManager.getConnection();

			pstmt = conn.prepareStatement(cmdtext);
			PrepareCommand(pstmt, params);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getObject(name);
			} else {
				return null;
			}
		} catch (SQLException sqlE) {
			throw new Exception("ExecuteSqlObject方法出错:" + sqlE.getMessage());
		} finally {
			ConnectionManager.closeResultSet(rs);
			ConnectionManager.closeStatement(pstmt);
			ConnectionManager.closeConnection(conn);
		}
	}

	/**
	 * 用于获取单字段值语句（用序号指定字段）
	 * 
	 * @param cmdtext
	 *            SQL语句
	 * @param index
	 *            列名索引
	 * @param params
	 *            OracleParameter[]
	 * @return Object
	 * @throws Exception
	 */
	public static Object ExecuteScalar(String cmdtext, int index,
			Object[] params) throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getConnection();
			pstmt = conn.prepareStatement(cmdtext);
			PrepareCommand(pstmt, params);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getObject(index);
			} else {
				return null;
			}
		} catch (SQLException sqlE) {
			throw new Exception("ExecuteSqlObject方法出错:" + sqlE.getMessage());
		} finally {
			ConnectionManager.closeResultSet(rs);
			ConnectionManager.closeStatement(pstmt);
			ConnectionManager.closeConnection(conn);
		}
	}

	/**
	 * 准备SQL参数
	 * 
	 * @param pstm
	 * @param params
	 */
	public static void PrepareCommand(PreparedStatement pstm, Object[] params) {
		if (params == null || params.length == 0) {
			System.out.println(pstm.toString());
			return;
		}
		try {
			for (int i = 0; i < params.length; i++) {
				int parameterIndex = i + 1;
				pstm.setString(parameterIndex, params[i].toString());
			}
			System.out.println(pstm.toString());
		} catch (Exception e) {
		}
	}

	/**
	 * 转换基础类型数据
	 * 
	 * @param 基础类型
	 * @param 要设置的值
	 * @return 值对象
	 */
	private static Object convert(String type, String value) {
		if (type.equals("int"))
			return new Integer(value);
		if (type.equals("double"))
			return new Double(value);
		if (type.equals("float"))
			return new Float(value);
		return null;
	}
}

MemberDaoImpl.java

import java.util.List;

import org.springframework.stereotype.Repository;

import com.jiayuan.ktv.common.DBHelperMySQL;
import com.jiayuan.ktv.dao.MemberDao;
import com.jiayuan.ktv.entity.Member;

@Repository("memberDao")
public class MemberDaoImpl implements MemberDao {

	/**
	 * 保存
	 * 
	 * @param member
	 */
	@Override
	public int save(Member member) {
		try {
				String sql = "INSERT INTO member(WXUserName,MemberType,Phone,Card,AddDate)"
						+ "VALUES(?,?,?,?,?)";
				String[] params = { String.valueOf(member.getWxusername()),String.valueOf(member.getMemberType()),String.valueOf(member.getPhone()),String.valueOf(member.getCard()),String.valueOf(member.getAdddate())};
				return DBHelperMySQL.ExecuteNonQuery(sql, params);
			} catch (Exception e) {
				e.printStackTrace();
				return 0;
			}
	}
	/**
	 * 修改
	 * 
	 * @param member
	 */
	@Override
	public int update(Member member) {
		try {
				String sql = "UPDATE member SET WXUserName=?,MemberType=?,Phone=?,Card=?,AddDate=? where Id=?";
				String[] params = { String.valueOf(member.getWxusername()),String.valueOf(member.getMemberType()),String.valueOf(member.getPhone()),String.valueOf(member.getCard()),String.valueOf(member.getAdddate()),String.valueOf(member.getId())};
				return DBHelperMySQL.ExecuteNonQuery(sql, params);
			} catch (Exception e) {
				e.printStackTrace();
				return 0;
			}
	}
	/**
	 * 根据主键删除一条数据
	 * 
	 * @param id
	 */
	@Override
	public int delete(int id) {
		try {
			String sql = "DELETE FROM member WHERE Id=?";
			String[] params = { String.valueOf(id) };
			return DBHelperMySQL.ExecuteNonQuery(sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}
	/**
	 * 根据主键集合删除一批数据
	 * @param ids
	 */
	@Override
	public int deleteList(String ids) {
		try {
			String sql = "Delete From member WHERE Id in (" + ids + ")";
			String[] params = { ids };
			return DBHelperMySQL.ExecuteNonQuery(sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}
	/**
	 * 通过主键获得Model
	 * @param id
	 * @return Model
	 */
	@Override
	public Member getModel(int id) {
		try {
			String sql = "SELECT * FROM member WHERE Id=?";
			String[] params = { String.valueOf(id) };
			return (Member) DBHelperMySQL.getModel(sql, params,Member.class);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 通过Sql语句获得Model
	 * @param sql
	 * @return Model
	 */
	@Override
	public Member getModel(String sql) {
		try {
			sql = "SELECT * FROM member WHERE " + sql;
			return (Member) DBHelperMySQL.getModel(sql, null,Member.class);
		} catch (Exception e) {
			return null;
		}
	}
	/**
	 * 通过Sql语句获得数据列表
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Member> getList(String sql) {
		try {
			if (sql.trim() != "")
				sql = " WHERE " + sql;
			sql = "SELECT * FROM  member " + sql;
			return (List<Member>) DBHelperMySQL.ExecuteReader(sql,null,Member.class);
		} catch (Exception e) {
			return null;
		}
	}
	/**
	 * 分页查询
	 * @param 条件语句
	 * @param 起始位置
	 * @param 查询条数
	 * @return List
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Member> getList(String sql, int startIndex, int rows) {
		try {
			if (sql.trim() != "")
				sql = " WHERE " + sql;
			sql = "SELECT * FROM member "+sql+" LIMIT " + startIndex + ","+ rows;
			return (List<Member>) DBHelperMySQL.ExecuteReader(sql,null,Member.class);
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 获得总数
	 */
	@Override
	public int getCount(String sql) {
		try {
			if (sql.trim() != "")
				sql = " WHERE " + sql;
			sql = "select count(Id) as Id from member "
					+ sql;
			Object o = DBHelperMySQL.ExecuteScalar(sql, "Id", null);
			if (o != null) {
				return Integer.parseInt(o.toString());
			} else
				return 0;
		} catch (Exception e) {
			return 0;
		}
	}
}