为了方便静态SQL语句进行增删改查的操作，编写了一个工具类进行操作。
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map;
public class JdbcUtil { // 定义数据库驱动 private static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
// 获取URL private static final String URL = "jdbc:oracle:thin:@172.22.246.26:1521:orcl";
// 获取用户名 private static final String USER = "java1";
// 获取密码 private static final String PASSWORD = "java1";
static { try { // 初始化JDBC驱动，并让驱动加载到JVM中 Class.forName(DRIVER_NAME); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
/* * 获取数据库连接对象 */ public static Connection getConnection() { Connection conn = null; try { // 获取数据库连接 conn = DriverManager.getConnection(URL, USER, PASSWORD); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; }
/* * 更新数据库 */ public void update(String sql) { Connection conn = getConnection(); Statement stmt = null; if (conn != null) { try { stmt = conn.createStatement(); stmt.executeUpdate(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } close(stmt); close(conn); }
/* * 数据查询 */ public List<Map<String, Object>> query(String sql) { List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Connection conn = getConnection(); Statement stmt = null; ResultSet rs = null; if (conn != null) { try { stmt = conn.createStatement(); rs = stmt.executeQuery(sql); // 获取结果集的列数， 为下面的for循环做准备 int count = rs.getMetaData().getColumnCount();
while (rs.next()) { // 创建map，将我们每一行的值以键值对的形式（列-值）存储 Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= count; i++) { // 循环获取列名 String columnName = rs.getMetaData().getColumnName(i); // 获取值，由于值的多样性，我们无法确定它是什么类型。 Object value = rs.getObject(i); map.put(columnName, value); } list.add(map); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } close(rs); close(stmt); close(conn); return list; }
/* * 关闭对象 */ public static void close(Object o) { if (o == null) { return; }
if (o instanceof ResultSet) { try { ((ResultSet) o).close(); } catch (SQLException e) { e.printStackTrace(); } } else if (o instanceof Statement) { try { ((Statement) o).close(); } catch (SQLException e) { e.printStackTrace(); } } else if (o instanceof Connection) { try { ((Connection) o).close(); } catch (SQLException e) { e.printStackTrace(); } } } }
