一、工具类
package utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
*
* 数据库帮助类
* @author 25754
*
*/
public class DbHelper {
public static String URL = "";
public static String USER = "";
public static String PWD = "";
public static Connection conn = null;
public static PreparedStatement pstm = null;
public static ResultSet rs = null;
static {
try {
Properties ps = new Properties();
InputStream is = DbHelper.class.getResourceAsStream("/db.properties");
ps.load(is);
URL = ps.getProperty("url");
USER = ps.getProperty("user");
PWD = ps.getProperty("pwd");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 连接数据库
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/
public static Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
return (Connection) DriverManager.getConnection(URL, USER, PWD);
}
/**
* 通用增删改
* @param sql
* @param params
* @return
*/
public static int excuteUpdate(String sql, Object[] params) {
try {
pstm = createPreparedStatement(sql, params);
return pstm.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeAll(conn, pstm, null);
}
return 0;
}
/**
* 查询数据
* @param sql
* @param params
* @return
*/
public static ResultSet excuteQuery(String sql, Object[] params) {
try {
pstm = createPreparedStatement(sql, params);
return pstm.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/**
* 处理数据
* @param sql
* @param params
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/
public static PreparedStatement createPreparedStatement(String sql, Object[] params) throws SQLException, ClassNotFoundException{
pstm = getConnection().prepareStatement(sql);
if(pstm != null) {
for(int i = 0; i < params.length; i++) {
pstm.setObject(i+1, params[i]);
}
}
return pstm;
}
/**
* 关闭连接
* @param conn
* @param pstm
* @param rs
*/
public static void closeAll(Connection conn, PreparedStatement pstm,ResultSet rs) {
try{
if(rs != null) {
rs.close();
}
if(pstm != null) {
pstm.close();
}
if(conn != null) {
conn.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
二、使用
import java.sql.ResultSet;
import java.sql.SQLException;
import utils.DbHelper;
public class Test1 {
public static void main(String[] args) {
try {
Object[] params = new Object[]{};
ResultSet rs = DbHelper.excuteQuery("select * from tp_admin", params);
if(rs != null) {
while(rs.next()) {
System.out.println(rs.getInt("id") + " " + rs.getString("username"));
}
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}