经常使用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;
}