jdbc BaseDao

package com.tango.conversion;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


public abstract class BaseDao {


Connection conn;
PreparedStatement pstmt;
ResultSet rs;


/**
* 得到connection
* 
* @return
*/
public Connection getConn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
try {
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/testwebsearch?user=root&password=123456&useUnicode=true&characterEncoding=utf-8");
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}

public ResultSet getResultSet(String sql){
conn = getConn();
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}


/**
* 更新操作
* 
* @param sql
* @return
*/
public int executeUpdate(String sql) {
int num = 0;
conn = getConn();
try {
pstmt = conn.prepareStatement(sql);
num = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll();
}
return num;
}


/**
* 更新操作
* 
* @param sql
* @param params
*            参数数组
* @return
*/
public int executeUpdate(String sql, String[] params) {
int num = 0;
conn = getConn();
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pstmt.setString(i+1, params[i]);
}
num = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll();
}
return num;
}


/**
* 批量更新操作
* 
* @param sql
* @param paramList
*            参数数组集合
* @return
*/
public void executeUpdate(String sql, List<String[]> paramList) {
Connection conn = getConn();
int num = 0;
try {
conn.setAutoCommit(false);
for (String[] params : paramList) {
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pstmt.setString(i+1, params[i]);
}
pstmt.executeUpdate();
num++;
}
conn.commit();
System.out.println("批量操作 --> "+num+" 条数据");
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll();
}
}


/**
* 查询操作
* 
* @param sql
* @return
*/
public List<?> executeQuery(String sql) {
conn = getConn();
List<Object> list = null;
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
list = this.createObject(rs);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}


/**
* 查询操作
* 
* @param sql
* @param params
*            参数数组
* @return
*/
public List<?> executeQuery(String sql, String[] params) {
conn = getConn();
List<Object> list = null;
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pstmt.setString(i, params[i]);
}
rs = pstmt.executeQuery();
list = this.createObject(rs);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}


/**
* 根据resultSet创建List集合
* 
* @param rs
*            结果集
* @return
*/
public abstract List<Object> createObject(ResultSet rs);


/**
* 关闭连接
*/
public void closeAll() {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}