java使用Jdbc连接数据库的工具类

最近在跟着老师学习jsp、servlet技术,前段时间也在做ajax,使用json实现前后端分离。下面分享一下共用的数据库工具类,导入oracle与mysql的jabc包,只需要改掉注释的,即可完美使用。


package utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class JdbcUtils {

// public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
// public static final String URL = "jdbc:oracle:thin:@192.168.8.100:1521:orcl";
// public static final String USER_NAME = "xxx";
// public static final String PASS_WORD = "xxx";
public static final String DRIVER = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
public static final String USER_NAME = "xxx";
public static final String PASS_WORD = "xxxx";

private Connection conn;
private PreparedStatement pst;
private ResultSet rs;

static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
System.out.println("ahaha");
e.printStackTrace();
}

}

public Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(
URL, USER_NAME, PASS_WORD);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}

/**
* 查询
*/
public ResultSet executeQuery(String sql, Object[] params)
throws SQLException {
conn = getConnection();
pst = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
rs = pst.executeQuery();
return rs;

}

/**
* ͨ查询
*/
public ArrayList executeQuery2(String sql,
Object[] params)throws SQLException {
conn = getConnection();
pst = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
ResultSet rs = pst.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

int count = rsmd.getColumnCount();
ArrayList list = new ArrayList();
while (rs.next()) {
HashMap map = new HashMap();
for (int i = 0; i < count; i++) {

String columnName = rsmd.getColumnName(i+1);

String value = rs.getString(columnName);
map.put(columnName, value);
}
list.add(map);

}

return list;

}

/**
*更新
*/
public int executeUpdate(String sql, Object[] params
) throws SQLException {
conn = getConnection();
pst = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
int i = pst.executeUpdate();
return i;
}

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

if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}

发表评论

This site uses Akismet to reduce spam. Learn how your comment data is processed.