jdbc数据库操作工具类--DBUtil

经常使用jdbc连接数据库，总结其使用方法方便日后查阅。

一、DBUtils类如下：

package com.test.utils;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class DBUtils {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private static String USERNAME = "username";
	private static String PASSWORD = "password";
	
	
	/**
	 * 获取连接
	 * @return
	 */
	public Connection getConnection(){
		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection(url,USERNAME,PASSWORD);
		}catch (Exception e) {
			System.out.println("DBUtils.getConnection---获取连接失败！");
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 执行查询操作
	 * @param sql
	 * @return 返回值是一个结果集
	 */
	public ResultSet executeQuery(String sql){
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			rs =  stmt.executeQuery(sql);
		} catch (SQLException e) {
			System.out.println("DBUtils.executeQuery---执行查询操作失败！");
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 执行带参数的查询操作
	 * @param sql sql语句
	 * @param obj Object数组
	 * @return
	 */
	public ResultSet executeQuery(String sql, Object[] obj){
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sql);
			preparStateSql(obj, obj.length);
			rs =  ps.executeQuery(sql);
		} catch (SQLException e) {
			System.out.println("DBUtils.executeQuery---执行查询操作失败！");
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 执行更新、插入、删除等操作
	 * @param sql
	 * @param obj
	 * @return 返回值是操作影响的行数(即更新、插入或删除了几条数据)
	 */
	public int executeUpdate(String sql, Object[] obj){
		int count = 0;
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql);
			preparStateSql(obj, obj.length);
			count = ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println("DBUtils.executeUpdate---执行更新、插入、删除等操作失败！");
			e.printStackTrace();
		} finally {
			close();
		}
		return count;
	}
	
	/**
	 * 装载PreparedStatement
	 * @param obj
	 * @param length
	 * @throws SQLException
	 */
	private void preparStateSql(Object[] obj, int length) throws SQLException {
		for(int i=0;i<length;i++)
		{
			if(obj[i]==null){
				obj[i]=false;
			}
			if(obj[i].getClass()==String.class){
				ps.setString(i+1, obj[i].toString());
			}else if(obj[i].getClass()==Integer.class){
				ps.setInt(i+1, (Integer)obj[i]);
			}else if(obj[i].getClass()==Double.class){
				ps.setDouble(i+1, (Double)obj[i]);
			}else if(obj[i].getClass()==Date.class)//java.sql.Date
			{
				ps.setDate(i+1, (Date)obj[i]);
			}else if(obj[i].getClass()==ByteArrayInputStream.class){
				ps.setBinaryStream(i+1, (ByteArrayInputStream)obj[i]);
			}else if(obj[i].getClass()==FileInputStream.class){
				ps.setBinaryStream(i+1, (FileInputStream)obj[i]);
			}else{
				ps.setObject(i+1, null);
			}
		}
	}
	
	/**
	 * 关闭数据库连接
	 */
	public void close(){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

二、调用方法

1.查询

public boolean hasVote(String fromUserName) {
		boolean result = false;
		String sql = "select * from vote where from_user ='"+fromUserName+"'";
		DBUtils db = new DBUtils();
		ResultSet rs = db.executeQuery(sql);
		try {
			if(rs.next()){
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return result;
	}

对查询的结果集ResultSet可以通过rs.getString("colomName");来获取本行该列的值。

2.保存

public boolean saveVote(Vote vote) {
		boolean result = false;
		String sql = "insert into vote (from_user,vote_person_id,vote_time) values (?,?,?)";
		Object obj[] = new Object[]{vote.getFromUser(),vote.getVotePersonId(),vote.getVoteTime()};
		DBUtils db = new DBUtils();
		int count = db.executeUpdate(sql, obj);
		db.close();
		if(count > 0){
			result = true;
		}
		return result;
	}